January 11, 2005 at 9:10 am
The goal in a nutshell: connect to list of servers and obtain database list from those servers by querying Master.sysdatabases.
I am trying to do this in TSQL so it may end up in a script or procedure.
The overview of the steps:
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 problem with used a linked server is that I cannot get the sp_addLinkedServer to execute before the next step in the batch. If I put a GO in there, after sp_AddLinkedServer it loses all of my variables because they go out of scope.
I have been trying OpenRowSet lately, with some success, but when I attempt to dynamically construct the connection string QA tells me there is a syntax error:
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')
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
Any ideas on accomplishing the original goal will be appreciated. I am open to all TSQL methods. I these are unsuccessful I will begin trying VBScript through DTS.
Thanks,
Chris
January 12, 2005 at 2:08 am
Hi,
You can only use a fixed string value for the OPenRowset. But you can use the sp_addlinkedserver like this :
Declare @ServerName VarChar(100)
Declare @Proc VarChar(200)
Set @ServerName = 'quix'
Set @Proc = @ServerName + '...sp_helpdb'
exec sp_addlinkedserver @server = @ServerName, @srvproduct = N'SQL Server'
exec @Proc
exec sp_dropserver @server = @ServerName
January 12, 2005 at 6:55 am
The problem I encounter with sp_AddLinkedServer is I cannot get it to actually execute without a batch separator, and the batch separator kills all my my variables.
I'm beginning to think that the only way possible to accomplish this is by a DTS package, if I can store my variables as globals so that I can pass them between DTS tasks.
Thanks,
Chris
January 12, 2005 at 12:40 pm
The way we handle it:
You should have a list of servers stored in table (and a separate table for DBs going against servrid) in standalone db on a master server. And usually you add linked servers one or two at a time. Anyway: you execute the below stored proc, which create a linked server on master server:
CREATE procedure AddLinkedServer
@server varchar(30)
AS
declare @sql varchar(500)
set @sql=' IF EXISTS(SELECT * FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME='''+@server+''')
exec sp_dropserver '''+@server+''',droplogins'
print @sql
exec (@sql)
set @sql='EXEC sp_addLinkedServer '''+@server+''''
print @sql
exec (@sql)
exec (@sql)
Go
and then you run the while loop (no cursors, pls) against server table, storing the output in the database table, going thru one server at a time. See the main extract which keep you going:
set @sql='select * into #DBInfo from openquery(['+@s+'],''select name as DatabaseName,
status,
mode,
suser_sname(sid) AS OWNER,
convert(nvarchar(11), crdate)AS CREATED,
dbid,
null as dbsize,
cmptlevel
from master.dbo.sysdatabases '') where status<>32'
print @sql
Exec(@sql)
We use it as the multi-step job...
January 13, 2005 at 10:17 am
I understand the difficulty you are facing. What I have is, on the master server, I have created the linked servers and the linked servers stay there all the time. I just have to run the script on the master server when I want to. However, that is not what you are trying to do.
I would suggest breaking your process into three steps, which could be independent of each other. Or having three stored procedures (say on your local or master server). The first procedure will add the linked servers. The second process, which is isolated from the first step (or the third step) will collect all the necessary information and the third step will remove the linked servers. You could call these thre stroed procs from one proc where all the parameters are passed, so you do not loose the variables.
January 13, 2005 at 11:35 am
Unfortunately I cannot see a way to make any of these solutions work. The main problem, even with breaking the script up into steps which are procedures, is getting the procedures to execute immediately within the script. This is why it will not work now. Even if I encased the sp_AddLinkedServer into its own procedure I don't think it would execute either procedure without a batch separator, which still kills my variables in the parent script.
I have begun to write this in VBScript, which has it's own set of limitations, but is getting further, in less time,than TSQL.
I can only try to complain to MS to have them make variable scoping easier and more extensible in future versions of SQL Server.
Thanks for trying to help,
Chris
June 14, 2005 at 4:47 pm
I wonder why a global variable would go out of scope, though
declare @@myvar varchar(50)
June 14, 2005 at 10:42 pm
THE VARIABLE GOES OUT OF SCOPE WITH THE BATCH OPERATOR:
DECLARE @@MYVAR VARCHAR (50)
SET @@MYVAR='KEWL'
PRINT @@MYVAR
GO
PRINT @@MYVAR
gives the following results:
KEWL
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@@MYVAR'.
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply