May 8, 2003 at 4:14 am
Was tryning to create a query based on several parameters:
CREATE TABLE
tempdb.##all_logins
([UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (100),
[ServerName] [varchar] (30),
)
DECLARE @server_name varchar(30) -- <---!!!
DECLARE @user_name varchar(40)
SET @user_name = 'Ivanov'
DECLARE @sys_table varchar(40)
declare @sql varchar(8000)
DECLARE server_list CURSOR
LOCAL SCROLL STATIC READ_ONLY
FOR SELECT srvname FROM master.dbo.sysservers
OPEN server_list
FETCH NEXT FROM server_list INTO @server_name
...
set @sql ='INSERT INTO ##all_logins SELECT name, @server_name FROM ' + @sys_table + ' WHERE CONTAINS(name,''' + @user_name + ''')'
print @sql
exec (@sql )
Get an error:
INSERT INTO ##all_logins SELECT name, @server_name
FROM saturn.master.dbo.sysxlogins WHERE CONTAINS(name,'Ivanov')
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@server_name'. -- <---!!!
Why? @server_name is declared!
May 8, 2003 at 4:55 am
I believe you should be doing this:
set @sql ='INSERT INTO ##all_logins SELECT name, ' + @server_name +' FROM ' + @sys_table + ' WHERE CONTAINS(name,''' + @user_name + ''')'
May 8, 2003 at 5:12 am
For this I get:
INSERT INTO ##all_logins SELECT name, saturn FROM saturn.master.dbo.sysxlogins WHERE CONTAINS(name,'Ivanov')
and an error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'saturn'.
But I need:
INSERT INTO ##all_logins SELECT name, 'saturn' FROM saturn.master.dbo.sysxlogins WHERE CONTAINS(name,'Ivanov')
How can I do this?
May 8, 2003 at 7:38 am
Try:
set @sql ='INSERT INTO ##all_logins SELECT name, ''' + @server_name +''' FROM ' + @sys_table + ' WHERE CONTAINS(name,''' + @user_name + ''')'
R David Francis
May 8, 2003 at 7:43 am
I found this out:
'INSERT INTO ##all_logins SELECT name,'''+ @server_name +''' FROM ' + @sys_table )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply