Automatically Create Linked Server?

I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of[vbcol=seagreen]
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of[vbcol=seagreen]
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connec
tion=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!