Timeout when specifying server

[stubailey]

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 paramters.

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 databases are on the same server and the server name is ommited then the query is instant. Running over the same server specifiying a server name times out. Running over different (linked) servers then the query is slightly slower as you would expect.

TIA
Stu

 

[ousoonerjoe]

Might want to look at OPENQUERY:

SELECT *
FROM OPENQUERY(@servername , @SelectStmt) AS d_sql
WHERE TableID = @something

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.