August 5, 2003 at 5:40 am
Hello all,
In a Server Maintainig Procedure I use the OPENQUERY Method to put version information from any given sql server
in a temporary table. Next statements reproduce my problem.
DECLARE @sqlexec varchar(200)
SET @sqlexec = 'SELECT * FROM OPENQUERY ([localserver],''SELECT @@version'')'
CREATE TABLE #versionstring(description varchar(200))
INSERT INTO #versionstring exec (@sqlexec)
-- (= works fine for remote server fails for localserver)
This statements work fine in any linked remote server. But fails if you try to trigger the local server.
If You strip the statements to only the OPENQUERY syntax then its works
SELECT * FROM OPENQUERY ([localhost],'SELECT @@version')
-- (= works fine BUT doesn't put the information in a table)
August 5, 2003 at 5:53 am
OPENQUERY needs a linked server and there is not one for the local machine. I have not tried it but I suppose you could create an alias called LOCAL to the local machine and do it that way or you could test the server name first, eg
DECLARE @sqlexec varchar(200)
IF @server = 'LOCAL'
SET @sqlexec = 'SELECT @@version'
ELSE
SET @sqlexec = 'SELECT * FROM OPENQUERY ([localserver],''SELECT @@version'')'
CREATE TABLE #versionstring(description varchar(200))
INSERT INTO #versionstring exec (@sqlexec)
Far away is close at hand in the images of elsewhere.
Anon.
August 5, 2003 at 6:35 am
Might be that the INSERT INTO combined with the OPENQUERY creates a distributed transaction. Loopback servers can be used in distributed queries but not distributed transactions.
See "Loopback Linked Servers" in BOL.
Cheers,
- Mark
Cheers,
- Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply