Problem in VB while trying to execute SQL Code
-
Hi there, wonder if you can help... I have a piece of VB code which uploads 2 large XMLs to SQL and then more code which allows the manipulation of the data to provide various bits of info which is then presented to Excel.. I was initially uploading each test XML file which were about 40K lines which made 16K Rows in each SQL table. I could then compare the two tables. The implementation requires XMLs of around 660K lines which, after uploading, become 144763 and 146741 rows in each table in SQL. My problem is that the statement I was using to compare these two tables, which worked with test data fails (no error is given) here is the code:
SQLStr = "Select * into Temp_Delete_Oldest from " & First & " Select * into Temp_Delete_Newest from " & Second & " DELETE Temp_Delete_Oldest FROM Temp_Delete_Oldest INNER JOIN Temp_Delete_Newest ON Temp_Delete_Newest.Fullpath = Temp_Delete_Oldest.Fullpath"This works in SQL when it is entered as a New Query but fails when implemnted from the VB code. So, does anybody know why this fails and what I can do to stop it failing, bearing in mind it works with smaller SQL tables.... Cheers nhsal69 -
Hi there, wonder if you can help... I have a piece of VB code which uploads 2 large XMLs to SQL and then more code which allows the manipulation of the data to provide various bits of info which is then presented to Excel.. I was initially uploading each test XML file which were about 40K lines which made 16K Rows in each SQL table. I could then compare the two tables. The implementation requires XMLs of around 660K lines which, after uploading, become 144763 and 146741 rows in each table in SQL. My problem is that the statement I was using to compare these two tables, which worked with test data fails (no error is given) here is the code:
SQLStr = "Select * into Temp_Delete_Oldest from " & First & " Select * into Temp_Delete_Newest from " & Second & " DELETE Temp_Delete_Oldest FROM Temp_Delete_Oldest INNER JOIN Temp_Delete_Newest ON Temp_Delete_Newest.Fullpath = Temp_Delete_Oldest.Fullpath"This works in SQL when it is entered as a New Query but fails when implemnted from the VB code. So, does anybody know why this fails and what I can do to stop it failing, bearing in mind it works with smaller SQL tables.... Cheers nhsal69Why don't you catch the exception and read what it tells you... That may help?
.: I love it when a plan comes together :. http://www.zonderpunt.nl
-
Why don't you catch the exception and read what it tells you... That may help?
.: I love it when a plan comes together :. http://www.zonderpunt.nl
-
The error generated is: A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Which is not really of any help, as this is the standard error when anything goes wrong when working with SQL from VB....
Did you use Exception.ToString() which shows ALL the available information, or just Exception.Message which only gives one line of text? :mad:
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
Hi there, wonder if you can help... I have a piece of VB code which uploads 2 large XMLs to SQL and then more code which allows the manipulation of the data to provide various bits of info which is then presented to Excel.. I was initially uploading each test XML file which were about 40K lines which made 16K Rows in each SQL table. I could then compare the two tables. The implementation requires XMLs of around 660K lines which, after uploading, become 144763 and 146741 rows in each table in SQL. My problem is that the statement I was using to compare these two tables, which worked with test data fails (no error is given) here is the code:
SQLStr = "Select * into Temp_Delete_Oldest from " & First & " Select * into Temp_Delete_Newest from " & Second & " DELETE Temp_Delete_Oldest FROM Temp_Delete_Oldest INNER JOIN Temp_Delete_Newest ON Temp_Delete_Newest.Fullpath = Temp_Delete_Oldest.Fullpath"This works in SQL when it is entered as a New Query but fails when implemnted from the VB code. So, does anybody know why this fails and what I can do to stop it failing, bearing in mind it works with smaller SQL tables.... Cheers nhsal69Check what the SQLStr evaluates to and try running that in SQL. This is building dynamically so if there was a type in the
Firstorsecondvariables then it will crash.If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
-
Check what the SQLStr evaluates to and try running that in SQL. This is building dynamically so if there was a type in the
Firstorsecondvariables then it will crash.If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
the full error is: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." This only occurs when the input files are the "large" ones, they also use the same variables "first and "second" as the small ones which complete successfully. After a bit of further investigation it appears as it is only when the VB send the calculation query to SQL that the error occurs (it creates the tables, which is the previous step)... I am attempting to increase the timeout period from default of 15 sec to more but this doesn't appear to resolve the problem... Any thoughts??? ***edit*** when stepping though the code and executing the "SQLCmd.ExecuteNonQuery()" it takes a count of approx 30 before the timeout error occurs, no matter what the "Connection Timeout=***;" in the connection string is set to...
-
the full error is: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." This only occurs when the input files are the "large" ones, they also use the same variables "first and "second" as the small ones which complete successfully. After a bit of further investigation it appears as it is only when the VB send the calculation query to SQL that the error occurs (it creates the tables, which is the previous step)... I am attempting to increase the timeout period from default of 15 sec to more but this doesn't appear to resolve the problem... Any thoughts??? ***edit*** when stepping though the code and executing the "SQLCmd.ExecuteNonQuery()" it takes a count of approx 30 before the timeout error occurs, no matter what the "Connection Timeout=***;" in the connection string is set to...
-
the full error is: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." This only occurs when the input files are the "large" ones, they also use the same variables "first and "second" as the small ones which complete successfully. After a bit of further investigation it appears as it is only when the VB send the calculation query to SQL that the error occurs (it creates the tables, which is the previous step)... I am attempting to increase the timeout period from default of 15 sec to more but this doesn't appear to resolve the problem... Any thoughts??? ***edit*** when stepping though the code and executing the "SQLCmd.ExecuteNonQuery()" it takes a count of approx 30 before the timeout error occurs, no matter what the "Connection Timeout=***;" in the connection string is set to...
A couple of possibilities worth checking. In Object Explorer right click on the SQL Server 2005 instance select Properties. On the Connections tab check the value of Remote_query timeout. On the Advanced tab check the value of Remote Login Timeout. Either of these would take precedence over your connection string settings.
If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
-
Just goes to show the importance of checking the error message fully, doesn't it? You need to set both the connection and query timeouts I suspect.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Problem resolved, here is the code for the SQL connection:
Dim mycon As New SqlConnection(conString) Dim SQLConn As New SqlConnection() 'The SQL Connection Dim SQLCmd As New SqlCommand() 'The SQL Command SQLConn.ConnectionString = myconnection 'Set the Connection String SQLConn.Open() 'Open the connection SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command SQLCmd.CommandText = SQLStr 'Sets the SQL String **SQLCmd.CommandTimeout = 60** SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only SQLConn.Close() 'Close the connection mycon.Close() Console.WriteLine("Connection Closed")The Bolded lin ei the new one which resolves my issue.. Thanks -
Problem resolved, here is the code for the SQL connection:
Dim mycon As New SqlConnection(conString) Dim SQLConn As New SqlConnection() 'The SQL Connection Dim SQLCmd As New SqlCommand() 'The SQL Command SQLConn.ConnectionString = myconnection 'Set the Connection String SQLConn.Open() 'Open the connection SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command SQLCmd.CommandText = SQLStr 'Sets the SQL String **SQLCmd.CommandTimeout = 60** SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only SQLConn.Close() 'Close the connection mycon.Close() Console.WriteLine("Connection Closed")The Bolded lin ei the new one which resolves my issue.. Thanks