Reporting output
Reporting output
Reporting output
Reporting output
Reporting output
Reporting output Reporting output Reporting output Reporting output Reporting output Reporting output Reporting output Reporting output
Reporting output Reporting output
Reporting output
Go Back  Xtreme Visual Basic Talk > > > Reporting output


Reply
 
Thread Tools Display Modes
  #1  
Old 10-09-2012, 10:37 AM
vharcourt vharcourt is offline
Newcomer
 
Join Date: Apr 2012
Posts: 5
Question Reporting output


I have a sub that runs a couple stored procedures in SQL Server 2008. After it runs the first SP I want it to report back to the end user that that process has completed by populating a list box. Then after the second SP is ran it will report that it was completed. The list box gets populated correctly but it only shows after the entire sub has ran. How would I go about making the responses "real time" if you will.

Code:
Private Sub btnLoadFile_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadFile.Click
        Dim SQLCon As New SqlClient.SqlConnection
        Dim SQLCmd As New SqlCommand
        SQLCon.ConnectionString = My.Settings.WCAConnectionString
        SQLCon.Open()

        If File.Exists(tboxSelectFile.Text) = True Then
            '        lboxStatus.Items.Add("Loading data from source " & tboxSelectFile.Text & "...")
            Try

                SQLCmd.CommandText = "BulkLoadCPS"
                SQLCmd.CommandType = CommandType.StoredProcedure
                SQLCmd.Connection = SQLCon
                SQLCmd.Parameters.Add(New Data.SqlClient.SqlParameter("@FileName", SqlDbType.VarChar, 500)).Value = tboxSelectFile.Text
                SQLCmd.ExecuteNonQuery()
                lboxStatus.Items.Add("Records successfully loaded into temp directory.")

                SQLCmd.CommandText = "BulkLoadDupCHK"
                SQLCmd.CommandType = CommandType.StoredProcedure
                SQLCmd.Connection = SQLCon
                SQLCmd.ExecuteNonQuery()
                lboxStatus.Items.Add("Duplicate record check completed.")

            Catch ex As Exception
                MessageBox.Show(ex.Message)
                SQLCon.Close()
            End Try

        Else
            MessageBox.Show("File not found!", "Invalid File Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            tboxSelectFile.Focus()
            tboxSelectFile.SelectAll()
            Exit Sub
        End If

        SQLCon.Close()

    End Sub
Reply With Quote
  #2  
Old 10-09-2012, 05:35 PM
hDC_0Reporting output hDC_0 is offline
Contributor

* Expert *
 
Join Date: Feb 2004
Posts: 560
Default Dealing with stored procedures

Quote:
How would I go about making the responses "real time" if you will.
I'm thinking you basically need the SQL Server internal equivalent of Application.DoEvents method,
but I don't know such an equivalent exists,
(I'm not a database expert - but I'll try to provide some help in case no one else posts).

First of all have you done anything to performance tune the stored procedures themselves?

I see a lot of newbies building all their stored processes in the Query Designer
and then just pasting the SQL into stored procedures as it comes out of the designer.

Here's some SP performance tuning links (of course there are more if you look around):
SQL Server – Stored Procedure Optimization Tips – Best Practices
SQL Server Performance Tuning for Stored Procedures
Stored Procedure Performance Tuning

The other tool that might proof helpful is the SQL Server Profiler
MSDN: Introducing SQL Server Profiler
MSDN: How To: Use SQL Profiler

There is also this StackOverFlow thread about the SQL Server Profile tool but also mentions something called "extended events".

Have you done a search of the forum?
A quick search found this "waiting for db" thread that might be good to read through..

Some other off the wall suggestions..

From this off forum thread I know that:
Quote:
In general - if you're trying to create an async processing model in SQL Server, you'd want to look at Service Broker.
If you are interested in exploring the use of Service Broker her's a few links:
MSDN: SQL Server Service Broker
MSDN: SQL Server Developer Center > Learn > SQL Server Developer Technologies > SQL Server 2008 – Service Broker
Service Broker Tutorials
SQL SERVER – Introduction to Service Broker and Sample Script

That same off forum thread also mentions something called "DLL triggers".
Here's a couple DLL triggers links:
SQL Server DDL Triggers to Track All Database Changes
MSDN: Understanding DDL Triggers
Quote:
DDL triggers, like regular triggers, fire stored procedures in response to an event.
Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
So I'm guessing that if you sliced up your stored procedures then you can use DLL triggers to monitor the events and give the user some feedback.


Long ago (with SQL Server 200) there were something called Extended Procedures.
There are talked about a little in this off forum thread.

According to this MSDN page they are still supported in SQL Server 2008 but are being deprecated
(I believe it was the low level, custom C/C++ DLL way of controlling execution of stored procedures).

This sort of leads down the DLL road of:
"Using Analysis Services stored procedures for debugging stored procedures".

That's where I'll leave it for someone else to answer who is more of a database expert.

Last edited by hDC_0; 10-09-2012 at 05:46 PM.
Reply With Quote
  #3  
Old 10-12-2012, 02:08 PM
vharcourt vharcourt is offline
Newcomer
 
Join Date: Apr 2012
Posts: 5
Thumbs up

I did find that a simple DoEvent was what I was needing to get the job done.

There's a little more code than before as it is complete now but a sample of what how I used the DoEvent is below:

Code:
 Try
                SQLCmd1.CommandText = "BulkLoadCPS"
                SQLCmd1.CommandType = CommandType.StoredProcedure
                SQLCmd1.Connection = SQLCon
                SQLCmd1.Parameters.Add(New Data.SqlClient.SqlParameter("@FileName", SqlDbType.VarChar, 500)).Value = tboxSelectFile.Text
                SQLCmd1.ExecuteNonQuery()
                Application.DoEvents()
                lboxStatus.Items.Add("Records successfully loaded into temp directory.")

            Catch ex As Exception
                MessageBox.Show(ex.Message)
                lboxStatus.Items.Add("There was an ERROR loading files into the temp table.")
                SQLCon.Close()
                Cursor = Cursors.Default
                Exit Sub
            End Try
Thank you for the resources.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Reporting output
Reporting output
Reporting output Reporting output
Reporting output
Reporting output
Reporting output Reporting output Reporting output Reporting output Reporting output Reporting output Reporting output
Reporting output
Reporting output
 
Reporting output
Reporting output
 
-->