By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,639 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,639 IT Pros & Developers. It's quick & easy.

Running Stored Procedure to create temporary table

P: 6
I have been racking my brains to try to get this code working. I am trying to create an app to run a stored procedure on MS SQL server. The stored procedure is designed to delete a temoporary table and then create a replacement temporary table. It will delete the original table but it will not create the new table. Here is my code and a sample of the layout of the SP. ANY help is greatly appreciated!!! Jason.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SubSubmitBtn_Click() Handles SubmitBtn.Click
  2.         Dim myCommandSQL As New SqlClient.SqlCommand
  3.         Dim connSql As SqlClient.SqlConnection
  4.         Dim transaction As SqlClient.SqlTransaction
  5.  
  6.         Try
  7.             connSql = New SqlClient.SqlConnection("Data Source=[server];Initial Catalog=[catalog;User ID=[userid];Password=password")
  8.             connSql.Open()
  9.             transaction = connSql.BeginTransaction()
  10.             myCommandSQL.Connection = connSql
  11.             myCommandSQL.Transaction = transaction
  12.             myCommandSQL.CommandType = CommandType.StoredProcedure
  13.             myCommandSQL.CommandText = "Merc_Aut_Svcs_Temp"
  14.             myCommandSQL.ExecuteNonQuery()
  15.             transaction.Commit()
  16.             connSql.Close()
  17.             myCommandSQL.Dispose()
  18.         Catch ex As Exception
  19.             MessageBox.Show(ex.Message)
  20.         End Try
  21.     End Sub


Template of stored procedure with details removed

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE [dbo].[Merc_Aut_Svcs_Temp]
  2. AS
  3. IF Object_id('tempdb.dbo.##MERC_AUT_SVCS_TMP_TBL', 'U') IS NOT NULL
  4. BEGIN
  5. DROP TABLE ##MERC_AUT_SVCS_TMP_TBL
  6. END
  7. BEGIN
  8. SELECT DISTINCT 
  9.                          [Records]
  10. INTO ##MERC_AUT_SVCS_TMP_TBL
  11. FROM         [Tables]
  12. WHERE     [Conditions]
  13.  
  14. INSERT INTO ##MERC_AUT_SVCS_TMP_TBL
  15. ([Records])
  16. SELECT DISTINCT 
  17.             [Records]
  18. FROM         FROM         [Tables]
  19. WHERE     [Conditions]
  20. END
Oct 23 '12 #1

✓ answered by Rabbit

It's not about when the connection gets closed. It's gone as soon as the query finishes running when called through C#. There's no way for you to keep it alive. Your only option is to create it as a permanent table and then drop it when you're done with it.

Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,382
The stored procedure works when run by itself?
Oct 23 '12 #2

P: 6
@Rabbit
Yes, if I run it through MSSQL managment studio it does what I want. When I run it from the application it drops the temp table but it does not create the new table. I am using the same userID and PW in the connection string in the app that I am using to login to run the SP from managment studio.
Oct 23 '12 #3

Rabbit
Expert Mod 10K+
P: 12,382
I see no reason why it shouldn't work if it works in through the management studio.

Try adding a few debug lines into the stored procedure and have it insert a few records into a table to see how it's progressing through the sp.
Oct 23 '12 #4

P: 6
@Rabbit
I am somewhat of a newbie. What lines should I add?
Oct 23 '12 #5

Rabbit
Expert Mod 10K+
P: 12,382
Every few lines of code, add an insert statement to just output a status.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO someDebugTable VALUES ('I am at line 3.')
You can add a timestamp also if you wish.
Oct 23 '12 #6

P: 6
@Rabbit
I altered as below. I have run with debug code creating the table with the data I want in the production database and run it creating the table in the temp db (which is where I need to have the table). All of the debugging lines are produced in the correct order both ways that I run it, so I know it is going all the way through the procedure. But it just doesn't create the table when the SP is creating the table in the temp db. It does create the table if I create it in the production db.

Expand|Select|Wrap|Line Numbers
  1. PROCEDURE [dbo].[Merc_Aut_Svcs_Temp]
  2. AS
  3. IF Object_id('tempdb.dbo.##MERC_AUT_SVCS_TMP_TBL', 'U') IS NOT NULL
  4. BEGIN
  5. DROP TABLE ##MERC_AUT_SVCS_TMP_TBL
  6. END
  7. INSERT INTO APP_DEBUG_TBL VALUES ('AFTER DROP')
  8. BEGIN
  9. SELECT DISTINCT 
  10.                       [Records]
  11. INTO ##MERC_AUT_SVCS_TMP_TBL
  12. FROM         [Tables]
  13. INSERT INTO APP_DEBUG_TBL VALUES ('AFTER CREATE')
  14. INSERT INTO MERC_AUT_SVCS_TMP_TBL
  15. ([Records])
  16. SELECT DISTINCT 
  17.                       [Records]
  18. FROM         [Tables]
  19. WHERE     [Conditions]
  20. INSERT INTO APP_DEBUG_TBL VALUES ('AFTER INSERT')
  21. END
Oct 23 '12 #7

Rabbit
Expert Mod 10K+
P: 12,382
Ok, I think I know what's going on now. Temp tables only last as long as the instance that created it is still around. So when you run it through vb.net code, the instance is gone as soon as it finishes running and the temp table gets deleted. Where as if you run it through SSMS, the instance is not gone until you close that query window. Once you close that query window, the temp table will get deleted as well.
Oct 23 '12 #8

P: 6
@Rabbit
Okay, so bottom line is that I need to use the VB app to launch the Crystal Report which is based upon that data so that the connection is not closed until after the data has been used, correct? Any pointers on the code that? I have already brought the CR into the VS project.

P.S. Sooooo helpful. Thank you very much.
Oct 24 '12 #9

Rabbit
Expert Mod 10K+
P: 12,382
It's not about when the connection gets closed. It's gone as soon as the query finishes running when called through C#. There's no way for you to keep it alive. Your only option is to create it as a permanent table and then drop it when you're done with it.
Oct 24 '12 #10

P: 6
@Rabbit
Thanks for all your help Rabbit. Still workin' it!
Oct 30 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.