SQL Timeout problem- Help (crosspost)
- Thread starter Capt_Ron
- Start date
Where are you running the SP from? VB or in Query Analyzer?
In code on the command object, there should be a CommandTimeOut property which is in number of seconds. 300 = 5 minutes 600 = 10 minutes, & so on. Just set accordingly. IIR, 0 will give you an unlimited timeout period, but it isn't recommented.
In QA, under Tools -> Options... there should be a setting in there on how long to wait for something to to run.
Tg
In code on the command object, there should be a CommandTimeOut property which is in number of seconds. 300 = 5 minutes 600 = 10 minutes, & so on. Just set accordingly. IIR, 0 will give you an unlimited timeout period, but it isn't recommented.
In QA, under Tools -> Options... there should be a setting in there on how long to wait for something to to run.
Tg
Tg,
I've tried everything I could find.
I set the command timeout property of the command object to 0
I've set the connection timeout property of the connection string to 0
The SP runs fine in QA. Probably because there is no overhead.
It's like the connection is lost in the middle of the SP. But its all on the same machine.
If you want, I can post the SP and the code I'm using to call it.
Thanks
Ron
I've tried everything I could find.
I set the command timeout property of the command object to 0
I've set the connection timeout property of the connection string to 0
The SP runs fine in QA. Probably because there is no overhead.
It's like the connection is lost in the middle of the SP. But its all on the same machine.
If you want, I can post the SP and the code I'm using to call it.
Thanks
Ron
Here is a link to a forum I've been using for SQL Server. There may be info there that can help.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53895
Also it has my Stored Procedure and my VB code.
Thanks
Ron
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53895
Also it has my Stored Procedure and my VB code.
Thanks
Ron
Ok.... I've looked through that thread....
Here's my thoughts.
1) You're using dynamic SQL in the SP, defeats the purpose of having the SP in the first place.
2) Is SP in the SAME DB you are restoring? If so, that can create a problem as you can't restore a DB that's in use.
3) Don't sling together the EXE into a SQL statement. Create a command object, setting the commandtext to the SP name, then .Parameters.Add to it the parameters to pass the info it.
4) I see in the code where the command time out it set. But in one of the posts, I see that you set the Connect Timeout in the connection string. Was that a typeo? Because it's not the same thing. Connect Timeout is used to determine how long to wait when establishing the connection. I am not aware of a way to set the Command Timeout in the connection string. Next time you run it, time how long it is before the error happens. If it is aorund 3 minutes, then it's still using the default timeout spec.
5) Hey, a gnome has to sleep too you know.
6) Going back to item #1 - try rewriting the SP to use those parameters but not in a dynamic SQL sort of way.
7) Since it seems to run fine in QA, concentrate on items 2-5.
Tg
Here's my thoughts.
1) You're using dynamic SQL in the SP, defeats the purpose of having the SP in the first place.
2) Is SP in the SAME DB you are restoring? If so, that can create a problem as you can't restore a DB that's in use.
3) Don't sling together the EXE into a SQL statement. Create a command object, setting the commandtext to the SP name, then .Parameters.Add to it the parameters to pass the info it.
4) I see in the code where the command time out it set. But in one of the posts, I see that you set the Connect Timeout in the connection string. Was that a typeo? Because it's not the same thing. Connect Timeout is used to determine how long to wait when establishing the connection. I am not aware of a way to set the Command Timeout in the connection string. Next time you run it, time how long it is before the error happens. If it is aorund 3 minutes, then it's still using the default timeout spec.
5) Hey, a gnome has to sleep too you know.
6) Going back to item #1 - try rewriting the SP to use those parameters but not in a dynamic SQL sort of way.
7) Since it seems to run fine in QA, concentrate on items 2-5.
Tg
Tg,
Thanks. Here's some more info based on what you wrote:
1) That was the only way I could get the SP to use parameters. If there is a better way then please let me know. I need to be able to pass the Backup file location, The New Database name, Then new Data File name and the new Log File name. (actually I coulduse the same name for the data and log file.
2) The SP is not in the same database. For ease of use and for testing, I have the program create the SP in the master database. I was planning on moving that later to its own database.
3) OK I didn't know that.
4) That was a typo.
5) The Gnome reference was completely coincidental.
I'm sure that the issue is with the .NET SQL Client object. I'm getting nothing in a SQL Profiler trace when the program runs.
I'll try #3 and let you know if that makes any difference.
Thanks for the help
Ron.
Thanks. Here's some more info based on what you wrote:
1) That was the only way I could get the SP to use parameters. If there is a better way then please let me know. I need to be able to pass the Backup file location, The New Database name, Then new Data File name and the new Log File name. (actually I coulduse the same name for the data and log file.
2) The SP is not in the same database. For ease of use and for testing, I have the program create the SP in the master database. I was planning on moving that later to its own database.
3) OK I didn't know that.
4) That was a typo.
5) The Gnome reference was completely coincidental.
I'm sure that the issue is with the .NET SQL Client object. I'm getting nothing in a SQL Profiler trace when the program runs.
I'll try #3 and let you know if that makes any difference.
Thanks for the help
Ron.
I'm having an issue with the parameters.
Here is my code:
Here is my code:
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlCommand
[/size][size=2][color=#0000ff]With[/color][/size][size=2] cmd
.CommandText = "ws_LY"
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0[/size]
[size=2] .Connection = sqlConn
.Parameters("@RestoreFile").Value = strFileName[/size]
[size=2] .Parameters("@DB").Value = strDB[/size]
[size=2] .Parameters("@MDF").Value = strDataFile[/size]
[size=2] .Parameters("@LDF").Value = strLogFile[/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]With
[/color][/size]Try:
I know I got the items in the right order, but use the intellisense to get the right enumerated values.
The difference between what you tried & what I jsut did was that you have to add the parameter before you can set it.
There's one other alternative that I can think of, it's kinda of a brute force option, and I'm reluctant to do it because it involves using a COM object wrapped into interop.
Tg
VB.NET:
.Parameters.Add("@restoreFile",sqldatatyle.varchar,sqlparameter.direction.input,1000,strFileName)The difference between what you tried & what I jsut did was that you have to add the parameter before you can set it.
There's one other alternative that I can think of, it's kinda of a brute force option, and I'm reluctant to do it because it involves using a COM object wrapped into interop.
Tg
Thank you,
Here's what it does now. I now get something in the SQL Profiler trace. It shows all the SP statements. But it doesn't run the SP. The SP Call has all the parameters and it looks like it should run but it doesn't. SQL server does not return any errors.
Here's my code:
Thanks for the help
Ron.
Here's what it does now. I now get something in the SQL Profiler trace. It shows all the SP statements. But it doesn't run the SP. The SP Call has all the parameters and it looks like it should run but it doesn't. SQL server does not return any errors.
Here's my code:
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] sqlConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlConnection(strConnString)[/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlCommand[/size]
[size=2][color=#0000ff]With[/color][/size][size=2] cmd[/size]
[size=2] .CommandText = "ws_LY"[/size]
[size=2] .CommandType = CommandType.StoredProcedure[/size]
[size=2] .CommandTimeout = 0[/size]
[size=2] .Connection = sqlConn[/size]
[size=2] .Parameters.Add("@restoreFile", SqlDbType.VarChar, 100, strFileName)[/size]
[size=2] .Parameters.Add("@DB", SqlDbType.VarChar, 100, strDB)[/size]
[size=2] .Parameters.Add("@MDF", SqlDbType.VarChar, 100, strDataFile)[/size]
[size=2] .Parameters.Add("@LDF", SqlDbType.VarChar, 100, strLogFile)[/size]
[size=2] .Parameters("@RestoreFile").Value = strFileName[/size]
[size=2] .Parameters("@DB").Value = strDB[/size]
[size=2] .Parameters("@MDF").Value = strDataFile[/size]
[size=2] .Parameters("@LDF").Value = strLogFile[/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]With[/color][/size]
[size=2][color=#0000ff]Try[/color][/size]
[size=2] sqlConn.Open()[/size]
[size=2] cmd.ExecuteNonQuery()[/size]
[size=2] MessageBox.Show("Database has been restored!", "Restore Database", MessageBoxButtons.OK, MessageBoxIcon.Information)[/size]
[size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception[/size]
[size=2] MessageBox.Show("Error Restoring Database." & ex.Source & " : : " & ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)[/size]
[size=2][color=#0000ff]Finally[/color][/size]
[size=2] sqlConn.Close()[/size]
[size=2] sqlConn.Dispose()[/size]
[size=2] sqlConn = [/size][size=2][color=#0000ff]Nothing[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try
[/color][/size]Thanks for the help
Ron.
And Nothing is showing up in profiler?
Try adding everything from T-SQL and STored PRocedures when running the profile. I know it seems like overkill, but I've found that sometimes I find something beneficial in the data that isn't included by default.
I take it that the SP hasn't changed either, right? It's still the same dynamic sp it was right?
Tg
ps: In your Catch, you may want to change it to "Catch sqlex as SQLException" ... if you use the generic Exception object, you get the generic "There was a SQL Server error"... but if you use SQLException, then you get the actual SQL Specific error that's returned.
Try adding everything from T-SQL and STored PRocedures when running the profile. I know it seems like overkill, but I've found that sometimes I find something beneficial in the data that isn't included by default.
I take it that the SP hasn't changed either, right? It's still the same dynamic sp it was right?
Tg
ps: In your Catch, you may want to change it to "Catch sqlex as SQLException" ... if you use the generic Exception object, you get the generic "There was a SQL Server error"... but if you use SQLException, then you get the actual SQL Specific error that's returned.
HEHEHE,
I changed the exception to SQLException. But I forgot that it doesn't throw an exception right now. It does send the command and parameters to the SQL server and SQL server does show the SP it just doesn't run it.
Maybe I'll change the code back before I changed it to run everything through the command object and see what I get for an error.
Ron.
I changed the exception to SQLException. But I forgot that it doesn't throw an exception right now. It does send the command and parameters to the SQL server and SQL server does show the SP it just doesn't run it.
Maybe I'll change the code back before I changed it to run everything through the command object and see what I get for an error.
Ron.
OK,
Gnome, Did you do something to my computer this weekend?
I changed back to using a SQL statement in the command object. Not using the SP. I just typed in the whole SQL into a string and then put that into the command object.
It did NOT work before. It does now. I set the CommandTimeout to 0 and it restored the 2gb database (took about 3 minutes).
One small thing though.
Sometimes the app hangs on opening the SQL Connection. Then if I run it again, it works fine.
I'll keep testing to see if it hangs again either on the restore or on the connection.
Thanks for your help it's always appreciated.
Ron
Gnome, Did you do something to my computer this weekend?
I changed back to using a SQL statement in the command object. Not using the SP. I just typed in the whole SQL into a string and then put that into the command object.
It did NOT work before. It does now. I set the CommandTimeout to 0 and it restored the 2gb database (took about 3 minutes).
One small thing though.
Sometimes the app hangs on opening the SQL Connection. Then if I run it again, it works fine.
I'll keep testing to see if it hangs again either on the restore or on the connection.
Thanks for your help it's always appreciated.
Ron
AARGH!!!!!!!!!
It works in DEBUG but not in PRODUCTION!!!!!!!!
What gives?
I install the finished product on my machine and I get the timeout errors again. I know it's got to do with the SQL object.
Why would it lose it's connection when in production but not in debug?
GRRRRRRR!!!
Ron
It works in DEBUG but not in PRODUCTION!!!!!!!!
What gives?
I install the finished product on my machine and I get the timeout errors again. I know it's got to do with the SQL object.
Why would it lose it's connection when in production but not in debug?
GRRRRRRR!!!
Ron
