Automatically Create Linked Server If Not Exists

I was hoping this would be simple but like everything lately there
seems to be some catch.
What I am trying to do is if one of my SP makes a request to another
system, if the Linked Server does not exists, it will create it on the
fly. However I get this error
Msg 911, Level 16, State 1, Line 11
Could not locate entry in sysdatabases for database 'EXEC master'. No
entry found with that name. Make sure that the name is entered
correctly.
I don't understand why its not executing the entire SQL string at one
time, its weird.
Any Help would be appreciated. I am also running this on SQL 2005
server.
Thanks
-Matt-
[Code]
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = 'Test'
IF NOT EXISTS (
SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = @.SQLServerName)
DECLARE @.SQLCMD NVARCHAR(MAX)
SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
@.SQLServerName + N''', @.srvproduct=N''SQL Server'''
PRINT @.SQLCMD
EXEC @.SQLCMD
[/Code]
Last line should be
EXEC (@.SQLCMD)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:
> I was hoping this would be simple but like everything lately there
> seems to be some catch.
> What I am trying to do is if one of my SP makes a request to another
> system, if the Linked Server does not exists, it will create it on the
> fly. However I get this error
> Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database 'EXEC master'. No
> entry found with that name. Make sure that the name is entered
> correctly.
> I don't understand why its not executing the entire SQL string at one
> time, its weird.
> Any Help would be appreciated. I am also running this on SQL 2005
> server.
> Thanks
> -Matt-
> [Code]
> DECLARE @.SQLServerName NVARCHAR(50)
> SET @.SQLServerName = 'Test'
> IF NOT EXISTS (
> SELECT srv.name
> FROM sys.servers srv
> WHERE srv.server_id != 0
> AND srv.name = @.SQLServerName)
> DECLARE @.SQLCMD NVARCHAR(MAX)
> SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> PRINT @.SQLCMD
> EXEC @.SQLCMD
> [/Code]
|||Oh Man, Now I do feel dumb. LOL.
Thanks
-Matt-
SQL Menace wrote:[vbcol=seagreen]
> Last line should be
> EXEC (@.SQLCMD)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Matthew wrote:
|||BTW, any reason you do need dynamic SQL?
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = N'Test'
EXEC master.dbo.sp_addlinkedserver @.server = @.SQLServerName,
@.srvproduct=N'SQL Server'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:[vbcol=seagreen]
> Oh Man, Now I do feel dumb. LOL.
> Thanks
> -Matt-
>
> SQL Menace wrote: