February 28, 2012 at 12:44 pm
Any help is greatly appreciated. I have a SQL2005 server that uses a "linked server" setup to an old SQL 2000 server. I am retiring this old server and setting up a new SQL 2008 server.
My developers are not sure of all the instances where they are referencing this old SQL2000 server, whether it's in a query, a stored procedure, or a scheduled job that may be calling this old server.
Is there a way to search inside of them to find any reference to the linked server name so that we can be sure we've migrated everything over properly?
Any help is greatly appreciated. Thanks!
February 28, 2012 at 2:13 pm
You should be able to see the linked servers listed in Management Studio for all instances. As for searching across them all, you have to connect to each and then search.
There are some tools, like Central Management Servers from MS, or SQL MultiScript from Red Gate, that can help automate a query across multiple servers.
You could possibly also use SQLCMD or Powershell and one of the scripts on this site that searches all objects to look for some text.
If you have the code in Source Control (and you should), usually there are global search functions in there as well.
Disclosure: I work for Red Gate
February 28, 2012 at 7:22 pm
Sorry, not sure I explained it well. In my SQL 2005 instance, there are queries and stored procedures within 2 or 3 databases. Inside these queries for example, it will call a table in the linked server (sql 2000). I'm trying to find an easy way to search across all queries (or SP's or jobs) to find any references to a database in the linked server so that we can update it with the new server name.
thanks again!!
February 28, 2012 at 8:42 pm
Try this
Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
Select name
From sys.servers
Where is_linked = 1
Open Findlinked;
Fetch next from Findlinked into @VName;
while @@FETCH_STATUS = 0
Begin
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE Definition LIKE '%'+@VName +'%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
Fetch next from Findlinked into @VName;
End
Close Findlinked
Deallocate Findlinked
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 29, 2012 at 9:37 am
that works awesome! Thanks so much! Will that find it in a scheduled job as well?
February 29, 2012 at 10:24 am
only in procs and funcs
If the scheduled job uses ad-hoc queries (not stored procedures), then the code would need modified.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 29, 2012 at 1:01 pm
Ok, thanks. I'm pretty sure they use ad-hoc queries. How would I modify it? Some jobs just use SQL queries, others I see have steps that use an SSIS package where the data sources are listed.
thanks again!
February 29, 2012 at 2:25 pm
SSIS will be something totally different. But here is the mod for the agent jobs.
Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
Select name AS name
From sys.servers
Where is_linked = 1
Open Findlinked;
Fetch next from Findlinked into @VName;
while @@FETCH_STATUS = 0
Begin
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE Definition LIKE '%'+@VName +'%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
Fetch next from Findlinked into @VName;
END
Close Findlinked
Open Findlinked;
Fetch next from Findlinked into @VName;
while @@FETCH_STATUS = 0
Begin
SELECT j.name AS JobName,js.command
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j
ON j.job_id = js.job_id
WHERE js.command LIKE '%'+@VName +'%'
Fetch next from Findlinked into @VName;
END
Close Findlinked
Deallocate Findlinked
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply