May 6, 2003 at 2:55 am
Have about 12 SQL 2000 servers. Try to find those, that have logins containing "Ivanov"
First create a temp table all_logins on local machine:
CREATE TABLE
tempdb.##all_logins
([UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (100))
Then try to add records to it:
SELECT name
INTO ##all_logins
FROM jupiter.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
Get an error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##all_logins' in the database.
SELECT name
INTO ##all_logins
FROM saturn.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
Get an error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##all_logins' in the database.
Why this temporary table is recreated each time I try to add records to it?
How to add server name into the record in temporary table?
Is there a better way to do this task?
Thanx.
May 6, 2003 at 3:23 am
Select into will always create a temp table.Try
CREATE TABLE
tempdb.##all_logins
([UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (100))
Then try to add records to it:
INSERT INTO ##all_logins
SELECT name
FROM jupiter.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
INSERT INTO ##all_logins
SELECT name
FROM saturn.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2003 at 6:17 am
This worked! Thanx. 🙂
But how can I insert server name where the login was found?
First change the table of course:
CREATE TABLE
tempdb.##all_logins
([UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (100)
[ServerName] [varchar] (30)
)
Then?
May 6, 2003 at 6:26 am
Since you are naming each server in turn then
INSERT INTO ##all_logins
SELECT name,'jupiter'
FROM jupiter.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
INSERT INTO ##all_logins
SELECT name,'saturn'
FROM saturn.master.dbo.sysxlogins
WHERE name like '%Ivanov%'
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply