Timeout when specifying server name

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 2005 Forums

Transact-SQL (2005)

Timeout when specifying server name

Author

Topic

stubailey
Starting Member

3 Posts

Posted - 2008-02-18 : 05:41:01

Hi,

I am having real problems with timeout on queries when I specify a server and database name.
i.e. INSERT .. SQLSERVER1.DATABASE1.DBO.TABLE1
SELECT .... FROM SQLSERVER1.DATABASE1.DBO.TABLEX

The problem is that the stored procedure can be used over either 1 or 2 servers, when use over 2 servers as long as they are linked it is not an issue. If the server is the same then I timeout.

The query is actually run within an EXEC statement as we have written an application to integrate with another 3rd party application sql tables so the servers are passed in as parameters.

Example Query:
EXEC
(
'DECLARE @INDEXABLE as varchar(1)
DECLARE @DECLAREWHEN as datetime
SELECT @INDEXABLE = INDEXABLE, @DECLAREWHEN = DECLAREWHEN FROM ' + @sserver + '.' + @sdatabase + '.DBO.DOCS
WHERE DOC = ' + @sdocnum + '
UPDATE ' + @dserver + '.' + @ddatabase + '.DBO.DOCS
SET DECLAREWHEN = @DECLAREWHEN, INDEXABLE = @INDEXABLE
WHERE DOC = ' + @ddocnum)

If the database is running on the same server then the query is instant, but there must be a better way than putting a big 'IF' around this.

TIA
Stu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:46:13

What is the error?
What is the reason for timeout?

E 12°55'05.25"
N 56°04'39.16"

stubailey
Starting Member

3 Posts

Posted - 2008-02-18 : 06:02:44

The error is simply a timeout.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 06:09:09

It seems like there's some problem with the way linked server was added.Is the query executed in management studio itself or are you executing it from application?

stubailey
Starting Member

3 Posts

Posted - 2008-02-18 : 06:14:07

It does not matter if the query is run from application or direct from MSMS, I still get a timeout.

You say linked servers, if specifying the same server name, do I need to link the server to itself? (maybe a stupid question)

Cheers

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-18 : 12:48:03

Yes if you use four-part name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources