query to search through different servers
-
Could someone please assist me
Is their a way to search through different servers I have a query that searches for an owner of objects through different databases in a server but i want it to run through different servers please assist
here my current query : (it works) jus need it to run through diff servers please HELP
EXEC sp_Msforeachdb @command1 = -- Looks through all databases on server
"Select o.name,u.name,o.uid
from ?..sysusers u,?..sysobjects o
where (u.name like '%nko98766'
and o.uid = u.uid)"
-
Run this
SELECT 'EXEC ['+srvname+'].master.dbo.sp_Msforeachdb @command1 =
"Select o.name,u.name,o.uid
from [?]..sysusers u,[?]..sysobjects o
where (u.name like ''%nko98766''
and o.uid = u.uid)"'
FROM master.dbo.sysservers
WHERE isremote=1 AND srvproduct='SQL Server'
paste the results in a new window and execute
Far away is close at hand in the images of elsewhere.
Anon. -
It doesnt seem to work even if i remoServer: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '%'.ve the single quotes i recieve thes errors
Have any solution
-
It doesnt seem to work even if i remoServer: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '%'.ve the single quotes i recieve thes errors
Have any solution
-
It works for me with SQL2K
Did you copy and paste the query as is?
Far away is close at hand in the images of elsewhere.
Anon. -
Pasted as is
it returns the whole select statement
-
It does go through all the servers but then returns the the entire select statement
example this is what it returns: any help
Exec [Testsrv3].Exec sp_Msforeachdb @command1 = "Select o.name,u.name,o.uid from [?]..sysusers u,[?]..sysobjects o where (u.name = "moh60108" and o.uid = u.uid)"
Exec [BAN1].Exec sp_Msforeachdb @command1 = "Select o.name,u.name,o.uid from [?]..sysusers u,[?]..sysobjects o where (u.name = "moh60108" and o.uid = u.uid)"
--------------------------------------------------------------------------
-
The query returns queries that you paste into another QA window to execute.
Sorry for any confusion
If you want the query to execute the returned queries as well then I'd use a cursor like this
DECLARE @srvname varchar(255)
DECLARE mycurs CURSOR FOR
SELECT srvname FROM master.dbo.sysservers WHERE isremote=1 AND srvproduct='SQL Server'
OPEN mycurs
FETCH NEXT FROM mycurs INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('EXEC [' + @srvname + '].master.dbo.sp_Msforeachdb @command1 =
"Select o.name,u.name,o.uid
from [?]..sysusers u,[?]..sysobjects o
where (u.name like ''%nko98766''
and o.uid = u.uid)"')
FETCH NEXT FROM mycurs INTO @srvname
END
CLOSE mycurs
DEALLOCATE mycurs
Far away is close at hand in the images of elsewhere.
Anon. -
Alternatively if you want all the results in one table then
DECLARE @srvname varchar(255)
CREATE TABLE #temp (dbname sysname,oname sysname,uname sysname,uid smallint)
DECLARE mycurs CURSOR FOR
SELECT srvname FROM master.dbo.sysservers WHERE isremote=1 AND srvproduct='SQL Server'
OPEN mycurs
FETCH NEXT FROM mycurs INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (dbname,oname,uname,uid)
EXEC('EXEC master.dbo.sp_Msforeachdb
@command1 = "CREATE TABLE #temp2 (dbname sysname,oname sysname,uname sysname,uid smallint);
INSERT INTO #temp2 (dbname,oname,uname,uid)
SELECT ''?'',o.[name],u.[name],o.uid
FROM [?].dbo.sysusers u
INNER JOIN [?].dbo.sysobjects o
ON o.uid = u.uid
WHERE u.[name] like ''%nko98766'';
SELECT dbname,oname,uname,uid FROM #temp2;DROP TABLE #temp2"')
FETCH NEXT FROM mycurs INTO @srvname
END
CLOSE mycurs
DEALLOCATE mycurs
SELECT dbname,oname,uname,uid FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
I realy apreciate you help thanks a lot i'm sure this must be becoming irritating
but although the query runs but the correct results are not displayed and their is an error :
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.sp_Msforeachdb'.
I was playing around with the query, but i end up only executing the select statment for the current sever that it is running on.
Thanks a lot for your help
if you do come right with something else please share it with me
Thanks a lot
-
Sorry my bad
Try this
DECLARE @srvname varchar(255)
CREATE TABLE #temp (srvname sysname,dbname sysname,oname sysname,uname sysname,uid smallint)
DECLARE mycurs CURSOR FOR
SELECT srvname FROM master.dbo.sysservers WHERE isremote=1 AND srvproduct='SQL Server'
OPEN mycurs
FETCH NEXT FROM mycurs INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (srvname,dbname,oname,uname,uid)
EXEC('EXEC [' + @srvname + '].master.dbo.sp_Msforeachdb
@command1 = "CREATE TABLE #temp2 (srvname sysname,dbname sysname,oname sysname,uname sysname,uid smallint);
INSERT INTO #temp2 (srvname,dbname,oname,uname,uid)
SELECT d.srvname,d.dbname,ISNULL(o.[name],''''),ISNULL(u.[name],''''),ISNULL(o.uid,0)
FROM (SELECT ''' + @srvname + ''' AS [srvname],''?'' AS [dbname]) d
LEFT OUTER JOIN [?].dbo.sysusers u
ON u.[name] like ''%nko98766''
LEFT OUTER JOIN [?].dbo.sysobjects o
ON o.uid = u.uid;
SELECT srvname,dbname,oname,uname,uid FROM #temp2;DROP TABLE #temp2"')
FETCH NEXT FROM mycurs INTO @srvname
END
CLOSE mycurs
DEALLOCATE mycurs
SELECT srvname,dbname,oname,uname,uid FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks a lot for your assistance I totally give up
all these errors are just becoming to frustrating
Thanks again!
ERROR ERROR ERROR ERROR ERROR ERROR ERROR ERROR
(Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'CREATE TABLE #temp2 (srvname sysname,dbname sysname,oname sysname,uname sysname,uid smallint);
INSERT INTO #temp2 (srvname,dbn' is too long. Maximum length is 128.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply