pepr pepr - 3 months ago 24
SQL Question

SQL: How to conditionally define the code that contains linked server ID only for some server instances?

I am writing a data pump as a stored procedure on SQL Server. It is expected to extract some data from Sybase, and UPDATE or INSERT the table in SQL Server database.

For development, the source table is stored in the test database on the same SQL Server as the target table is (in different database). On the production site, the Sybase server is connected as a linked server.

How can write common code (for the installation script) that would work both on the testing site, and on the production site?

See the fragment... and the details below:

CREATE PROCEDURE dbo.sp_data_pump
AS
BEGIN
SET NOCOUNT ON

IF @@servername = 'COMPUTER\SQLTESTINSTANCE' BEGIN
-- for development
MERGE dbo.sometable AS tar
USING (SELECT code, data
FROM dbtest.dbo.datapump_sometable) AS source -- !!!!
ON tar.code = source.code
WHEN MATCHED THEN
UPDATE
SET tar.data = source.data,
tar.changed = GETDATE(),
tar.changedby = 'datapump'
WHEN NOT MATCHED THEN
INSERT (data, created, createdby)
VALUES (source.code, source.data, GETDATE(), 'datapump');
END ELSE BEGIN
-- for the deployed application
MERGE dbo.sometable AS tar
USING (SELECT data
FROM OPENQUERY(LINKEDSRVID, 'SELECT * FROM xdb.schema.xxx_sometable')) AS source -- !!!!
ON tar.code = source.code
WHEN MATCHED THEN
WHEN MATCHED THEN
UPDATE
SET tar.data = source.data,
tar.changed = GETDATE(),
tar.changedby = 'datapump'
WHEN NOT MATCHED THEN
INSERT (data, created, createdby)
VALUES (source.code, source.data, GETDATE(), 'datapump');
END
END


The Sybase database contains a view to access the data; however, the view can be called only through
OPENQUERY(LINKEDSRVID, 'SELECT ....')
. Because of this, the
LINKEDSRVID
must exist. However, it is not created on the development site, therefore the code does not compile.

The core of the problem can be simplified to pseudo code:

CREATE PROCEDURE dbo.sp_data_pump
AS
BEGIN
IF @@servername = 'COMPUTER\SQLTESTINSTANCE' BEGIN
-- for development
...
SELECT * FROM dbtest.dbo.datapump_sometable) AS source
...
END ELSE BEGIN
-- for the deployed application
...
SELECT * FROM OPENQUERY(LINKEDSRVID, 'SELECT * FROM xdb.schema.xxx_sometable')) AS source
...
END
END


How the problem should be solved?

Answer

Although you could wrap the code in dynamic SQL to avoid the compile error, I suggest you create a linked server named LINKEDSRVID in dev and just include OPENQUERY in the proc. Ideally, that should point to a test Sybase instance but you could create one for the dbtest database instead if that's not an option. Just be aware that testing won't be totally valid if you don't have a Sybase test instance.