Stored procedure - add linked server

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)

Stored procedure - add linked server

Author

Topic

gzadro
Starting Member

4 Posts

Posted - 2010-04-15 : 02:33:13

This procedure is used to set up a linked server. What is the problem? Well, i'am getting an IP address which is x.x.x.x (four dots). Later, i will need to run some scripts from this server, and i don't know how to change name of the server. If the IP address is 192.168.0.1 i want to make server name 19216801.
Anybody?


------------------------
----Add link server-----
------------------------
DECLARE @SiteName NVARCHAR(50)
DECLARE @DatabaseNode NVARCHAR(50)

-- gets the ip address of database node--------------------
SET @DatabaseNode = (SELECT     DatabaseName.dbo.OS_NODE.NodeIP
FROM         DatabaseName.dbo.OS_SITE INNER JOIN
                      DatabaseName.dbo.OS_NODE ON DatabaseName.dbo.OS_SITE.SiteID = DatabaseName.dbo.OS_NODE.SiteID
WHERE     (DatabaseName.dbo.OS_NODE.IsDatabase = 1) AND (DatabaseName.dbo.OS_SITE.SiteName = @SiteName))
-----------------------------------------------------------

IF (SELECT COUNT (datasource) FROM sysservers WHERE datasource = @DatabaseNode) = 1
Begin
Print 'Server already defined'
end

ELSE
begin
IF (SELECT COUNT (datasource) FROM sysservers WHERE datasource = @DatabaseNode) < 10
AND
(
SELECT DatabaseName.Dbo.OS_NODE.IsDatabase FROM DatabaseName.dbo.OS_NODE
WHERE DatabaseName.Dbo.OS_NODE.IsDatabase = 1 AND DatabaseName.Dbo.OS_NODE.NodeIP = @DatabaseNode) = 1

BEGIN

EXEC master.dbo.sp_addlinkedserver @server = @DatabaseNode, @srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'rpc', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@DatabaseNode, @optname=N'rpc out', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server= @DatabaseNode, @optname=N'use remote collation', @optvalue=N'true'


USE [master]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @DatabaseNode, @locallogin = N'NT AUTHORITY\SYSTEM', @useself = N'False', @rmtuser = N'fixituser', @rmtpassword = N'tronic17m'



END

end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 03:07:36

Do you mean this?
select replace(@DatabaseNode,'.','')

No, you're never too old to Yak'n'Roll if you're too young to die.

gzadro
Starting Member

4 Posts

Posted - 2010-04-15 : 03:26:07

quote:
Originally posted by webfred

Do you mean this?
select replace(@DatabaseNode,'.','')

No, you're never too old to Yak'n'Roll if you're too young to die.

No, then my connection string wouldn't work... @DatabaseNode is variable with IP, i will declare another varialbe without dots

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 03:32:39

Yes I thought you could adapt it.

declare...

and then:
set @newvar=replace(@DatabaseNode,'.','')

No, you're never too old to Yak'n'Roll if you're too young to die.

gzadro
Starting Member

4 Posts

Posted - 2010-04-15 : 03:40:09

quote:
Originally posted by webfred

Yes I thought you could adapt it.

declare...

and then:
set @newvar=replace(@DatabaseNode,'.','')

No, you're never too old to Yak'n'Roll if you're too young to die.

Ok, but where would i put @newvar? If i replace @DatabaseNode with @newwar it still doesn't work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources