December 3, 2002 at 1:46 pm
Does anyone know of a way to script all of the Linked Server definitions on a server? What I’m looking for is output similar to the scripts generated by Enterprise Manager via "Generate SQL Script..." menu option. If possible, I would like to run the command from an NT DOS command line.
December 3, 2002 at 2:19 pm
Try running the profiler on your login while scripting the linked server.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
December 3, 2002 at 2:29 pm
I ran Profiler on the server before making my original post. I was surprised by the number of statements that Enterprise Manager executes for a single new linked server. I was just hoping for a simpler way...something generic that I could put into a command file that would make SQL Server cough up the correct syntax automatically.
December 3, 2002 at 5:16 pm
Might want to look at DMO, it has good scripting support. A while back I looked at how to add them using DMO, might give you an idea for a start:
http://www.sqlservercentral.com/columnists/awarren/addinglinkedserversusingsqldmo.asp
We also have some other articles that discuss using DMO to script things out.
Andy
December 4, 2002 at 6:00 am
I saw this reference about DMO being phased out:
December 4, 2002 at 6:40 am
I found a reference to "Scripting XML and WMI for Microsoft(r) SQL Server 2000: Professional Developer's Guide":
Does anyone know if this book would be a good resource for someone competent in NT DOS scripting and TSQL scripting, but new to DMO or WMI?
December 4, 2002 at 7:12 am
Ok, not great. No final word on DMO, I'd be surprised if it gets phased out. Apress has a good DMO book just released.
Andy
December 4, 2002 at 1:42 pm
I think Shew01's question still remains unanswered.
Has anybody out there scripted all of the Linked Server definitions on a server? If so could you share your results either in DMO, WMI, or as osql script.
I also tried this through profiler, too many moving targets for me to get it right. I'll give it a shot again tonight.
I promise Andy, I'll try DMO!
John Zacharkan
John Zacharkan
December 4, 2002 at 4:17 pm
I build all my linked servers using scripts but have never scripted them OUT, I am interested in knowing the answer to this one as well. I get tired of writing these manually.
December 4, 2002 at 7:19 pm
I have been working on a piece that is not done. But to script out the sp_addlinkedserver I run a query like so:
select ('sp_addlinkedserver
@server = ''' + srvname + '''' +
CASE WHEN srvproduct IS NOT NULL THEN ',
@srvproduct = ''' + srvproduct + '''' ELSE '' END +
CASE WHEN catalog IS NOT NULL THEN ',
@catalog = ''' + catalog + '''' ELSE '' END +
CASE WHEN providername IS NOT NULL THEN ',
@provider = ''' + providername + '''' ELSE '' END +
CASE WHEN datasource IS NOT NULL THEN ',
@datasrc = ''' + datasource + '''' ELSE '' END +
CASE WHEN location IS NOT NULL THEN ',
@location = ''' + location + '''' ELSE '' END +
CASE WHEN providerstring IS NOT NULL THEN ',
@provstr = ''' + providerstring + '''' ELSE '' END) AS addlinkedserver
FROM
master.sysservers
where
isremote = 1
That will do the sp_addlinkedserver for all but a SQLServer defined connection setup using EM.
I am still working on the rest and have a lot figuredout but not all. Will post when get more done or anyone else can jump in.
December 9, 2002 at 8:01 am
I posted a script which scripts out all remote/linked servers with all settings.
http://www.sqlservercentral.com/scripts/contributions/620.asp
December 9, 2002 at 10:07 pm
I voted 5 star. I can't wait to examine it in detail.
December 11, 2002 at 10:41 pm
Somebody posted this sometimes back. Hope this helps.
DROP PROCEDURE dbo.sp_ServerStatus
go
CREATE PROCEDURE dbo.sp_ServerStatus
/*******************************************************************************
Written By : Simon Sabin
Date : 14 November 2002
Description : Returns the status of a SQL Server service
History
Date Change
------------------------------------------------------------------------------
14/11/2002 Created
*******************************************************************************/
(
@servername sysname
,@status int OUTPUT
,@statusText varchar(20) OUTPUT
)
AS
SET XACT_ABORT ON
declare @hr int , @object int
exec @hr = sp_OACreate 'sqldmo.sqlserver', @object OUTPUT
IF @hr<> 0
RAISERROR ('Cannot create sqldmo.sqlserver object',15,1)
exec @hr = sp_OASetProperty @object, 'Name', @servername
exec @hr = sp_OAGetProperty @object, 'Status', @status OUTPUT
SET @statusText = CASE @HR WHEN -2147221499 THEN 'Access Denied'
WHEN -2147219782 THEN 'Server does not exist'
WHEN 0 THEN CASE @status WHEN 0 THEN 'Unknown'
WHEN 5 THEN 'Stopping'
WHEN 3 THEN 'Stopped'
WHEN 4 THEN 'Starting'
WHEN 1 THEN 'Running'
WHEN 7 THEN 'Pausing'
WHEN 2 THEN 'Paused'
WHEN 6 THEN 'Continuing'
ELSE 'Unknown' END
ELSE 'Unknown error occurred' END
EXEC sp_OADestroy @object
GO
paul
paul
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply