Timeout when specifying server name

[stubailey]

Hi,

I am having real problems with timeout on queries when I specify a server and database name.
i.e. INSERT .. SQLSERVER1.DATA BASE1.DBO.TABLE 1
SELECT .... FROM SQLSERVER1.DATA BASE1.DBO.TABLE X

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 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. But there must be a better way than putting a big 'IF' around this.

TIA
Stu

[radcaesar]

What is the version of ur SQL SERVER ?

Actually, i had wrote thousands of line of T-SQLs and i don't find any issues on using the server name. Yours is strange. Try execute this query remotely using the server name (Not from this box).

Let mw know if i miss something.