February 18, 2008 at 3:39 am
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
February 18, 2008 at 3:46 am
Your statement is a bit confusing. Do you time out when the databases are on the same server or on different servers?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
February 18, 2008 at 3:55 am
Sorry, should have said 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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply