Execute Linked Server SP using sp_executesql

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)

Execute Linked Server SP using sp_executesql

Author

Topic

PollyMorph
Starting Member

5 Posts

Posted - 2008-07-18 : 07:35:49

Hi all.

I am having a problem executing a SP over a variable linked server. Passing parameters and returning an output parameter. Please can you let me know if this is possible at all?

My sourcecode looks like this at present.

DECLARE @SQLString AS NVARCHAR(4000)
DECLARE @ParmDefinition AS NVARCHAR(4000)

DECLARE @SQLServerName AS NVARCHAR(250)
DECLARE @DatabaseName AS NVARCHAR(250)

DECLARE @Number AS NVARCHAR(250)
DECLARE @VDNNumber AS NVARCHAR(50)
DECLARE @DatanetContactId AS NVARCHAR(50)

SET @SQLServerName = 'SQLServerName'
SET @DatabaseName = 'DatabaseName'

SET @Number = '44831234567'
SET @VDNNumber = 'BRICK'

SET @SQLString = N'EXEC @SQLServerName.@DatabaseName.dbo.Insert_IntoIncommingBucket @ContactCellNo=@Number, @vdn=@VDNNumber, @ContactId = @DatanetContactId'
SET @ParmDefinition = N'@SQLServerName AS NVARCHAR(250), @DatabaseName AS NVARCHAR(250), @Number AS NVARCHAR(50), @VDNNumber AS NVARCHAR(50), @DatanetContactId AS NVARCHAR(50) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @SQLServerName = @SQLServerName, @DatabaseName = @DataBaseName, @Number = @Number, @VDNNumber = @VDNNumber, @DatanetContactId = @DatanetContactId OUTPUT;

SELECT @DatanetContactId

My result at this stage returns a Null Value for the 'SELECT @DatanetContactId' part and returns the following message.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

(1 row(s) affected)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-18 : 12:25:38

HI,

I think you need to do this instead....


DECLARE @SQLString AS NVARCHAR(4000)
DECLARE @ParmDefinition AS NVARCHAR(4000)
DECLARE @SQLServerName AS NVARCHAR(250)
DECLARE @DatabaseName AS NVARCHAR(250)
DECLARE @Number AS NVARCHAR(250)
DECLARE @VDNNumber AS NVARCHAR(50)
DECLARE @DatanetContactId AS NVARCHAR(50)

SET @SQLServerName = 'SQLServerName'
SET @DatabaseName = 'DatabaseName'
SET @Number = '44831234567'
SET @VDNNumber = 'BRICK'

SET @SQLString = N'
EXEC ' + @SQLServerName + '.' + @DatabaseName + '.dbo.Insert_IntoIncommingBucket @ContactCellNo=@Number, @vdn=@VDNNumber, @ContactId = @DatanetContactId OUT'

SET @ParmDefinition = N'@Number AS NVARCHAR(50), @VDNNumber AS NVARCHAR(50), @DatanetContactId AS NVARCHAR(50) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Number = @Number, @VDNNumber = @VDNNumber, @DatanetContactId = @DatanetContactId OUT;

SELECT @DatanetContactId

Because before your exec string would have looked like

EXEC @SQLServerName.@DatabaseName.....

Now it will be

EXEC <value of @SQLServerName>.<value of DatabaseName>

Can't test this as I don't have your stored proc..

Hope this helps.

-------------
Charlie

PollyMorph
Starting Member

5 Posts

Posted - 2008-07-21 : 04:19:27

Thanks Charlie,

I got this to work with the string build that you suggested.. Now everything is working nice and dynamically.

Great stuff.
Polly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources