Setting Timeout for Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Setting Timeout for Database

Author

Topic

Jster
Starting Member

21 Posts

Posted - 2006-12-05 : 09:33:12

I'm trying to figure out how to set the Timeout of the database higher. I'm trying to use "ALTER DATABASE DB SET TIMEOUT 1800". I keep getting a timeout error when a CLR Trigger is executed.

Thanks,
Josh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:38:01

Try setting the execute timeout value in the CLR component instead.

Peter Larsson
Helsingborg, Sweden

Jster
Starting Member

21 Posts

Posted - 2006-12-05 : 09:46:20

I've set the sqlCommand commandtimeout = 1800 and the timeout expired error still persists. Did I set the correct item's timeout! Guess what I'm trying to ask is how to set the timeout for the CLR Trigger?

Thanks,
Josh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:48:22

Yes, the error will still occur, if command is not done before timeout occurs.
But you have the opportunity to deal with that in CLR code.

1800 is 30 minutes. That is a very long time. Especially for a trigger...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:48:49

You seems to be coding something wrong...
Post code here and we might be able to help you.

Peter Larsson
Helsingborg, Sweden

Jster
Starting Member

21 Posts

Posted - 2006-12-05 : 10:02:48

public static void SyncInsert()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = conn;
sqlComm.CommandTimeout = 180;
sqlComm.CommandText = "SELECT * FROM INSERTED";
ProjectLayout pl = new ProjectLayout();
ProjectLayout[] plArr = new ProjectLayout[1];
i=0
for(i=0; i<plArr.length; i++)
{
sqlComm.CommandText = "SELECT * FROM TBL WHERE ID="+plArr[i].groupID;
da.SelectCommand = sqlComm;
da.Fill(ds,"GROUP");
foreach(DataRow drGroup in ds.Tables["GROUP"].Rows)
{
sqlComm.CommandText = "SELECT * FROM TBL1 WHERE ID="+drGroup["ACCTID"];
da.SelectCommand = sqlComm;
da.Fill(ds,"ACCT");
foreach(DataRow drAcct in ds.Tables["ACCT"].Rows)
{
//do some math and configurations
}
}
}
conn.Close();
}
}

That is the gist of the code. Its a bit longer than that, and I did change the timeout to 180 instead of 1800. I was just setting it to a high number to see if i get the same error. I appreciate it.

Thanks,
Josh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 10:05:06

What are you trying to do?
Fill a grid with all inserted records?

The system built-in table INSERTED holds all columns for any inserted or updated record.

Peter Larsson
Helsingborg, Sweden

Jster
Starting Member

21 Posts

Posted - 2006-12-05 : 10:09:12

The trigger is meant to sync historical data for a budget module. Inserted isn't the "temp" table where the record is placed until the trigger executes, then it is inserted into the table?

Thanks,
Josh

Jster
Starting Member

21 Posts

Posted - 2006-12-06 : 14:28:36

Any ideas?

Thanks,
Josh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources