Re: Linked Server: How to check if server exists?
- From: "Rick Byham, \(MSFT\)" <rickbyh@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 3 Dec 2008 13:38:43 -0800
I adapted this from the object explorer scripting feature.
The root problem is described in the Books Online topic Batches.
Rules for Using Batches
The following rules apply to using batches:
- CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch.
I don't have a way around that.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.
"Axel Dahmen" <keentoknow@xxxxxxxxxxxxxxxx> wrote in message news:u3EUxdXVJHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
Thanks, Rick, for trying to help.
Unfortunately putting the Stored Procedure in a string will lose all IntelliSense features and colour highlighting, plus all strings will have to be escaped.
Do you perhaps have some other solution at hand?
TIA,
www.axeldahmen.de
Axel Dahmen
----------------------
"Rick Byham, (MSFT)" <rickbyh@xxxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im Newsbeitrag news:4A81D37A-14CD-47BB-AA01-16FB9F22FCF0@xxxxxxxxxxxxxxxx
This should get you started:
IF EXISTS (SELECT name FROM sys.servers WHERE name = N'linkedserver')
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.ExportToSMS
AS
SELECT * FROM [linkedserver].[db].[schema].[table]'
END
ELSE
PRINT 'No such linked server.'
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.
"Axel Dahmen" <keentoknow@xxxxxxxxxxxxxxxx> wrote in message
news:Oxio$AVVJHA.2928@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
in our development project not all of use have a linked server entry at our
local SQL Server installations.
At compile time we're running a couple of SQL scripts to update our local
databases.
Unfortunately the Stored Procedures containing statements addressing a
linked server don't run on those development machines not having that linked
server entry yet.
Now I'd like to know if it is possible to conditionally have the CREATE
PROCEDURE statement run, depending on a condition.
The following doesn't work:
-----------------------------------------------
IF EXISTS (SELECT NULL FROM sys.servers WHERE name = N'linkedserver')
BEGIN
CREATE PROCEDURE dbo.ExportToSMS
AS
SELECT * FROM [linkedserver].[db].[schema].[table]
END
-----------------------------------------------
even the following doesn't work:
-----------------------------------------------
CREATE PROCEDURE dbo.ExportToSMS
AS
IF EXISTS (SELECT NULL FROM sys.servers WHERE name = N'linkedserver')
BEGIN
SELECT * FROM [linkedserver].[db].[schema].[table]
END
-----------------------------------------------
Can someone please enlighten me on how to achieve a conditional CREATE
PROCEDURE?
TIA,
www.axeldahmen.de
Axel Dahmen
.
- References:
- Linked Server: How to check if server exists?
- From: Axel Dahmen
- Re: Linked Server: How to check if server exists?
- From: Rick Byham, \(MSFT\)
- Re: Linked Server: How to check if server exists?
- From: Axel Dahmen
- Linked Server: How to check if server exists?
- Prev by Date: Re: Linked Server: How to check if server exists?
- Next by Date: Re: Alias for Linked SQL Server?
- Previous by thread: Re: Linked Server: How to check if server exists?
- Next by thread: Re: Linked Server: How to check if server exists?
- Index(es):
Relevant Pages
|
