Differentiate sql and babelfish server

[JayakumarEMIS]

Hi @bill-ramos-rmoswi , I am trying to create procedure babelfish and sql server there might be an some query changes did specifically for bablefish this changes should be run in babelfish, for that is any way to check the query is running in babelfish or sql server, I have write below query to create it but do you have any better idea to check or below query itself is fine.

IF @@VERSION NOT LIKE 'Babelfish%' BEGIN EXEC('CREATE PROCEDURE Test AS BEGIN Print ''Sql'' END') END ELSE BEGIN EXEC('CREATE PROCEDURE Test AS BEGIN Print ''Babelfish'' END') END GO

Note: in above query is fine, i can only use dynamic sql to generate procedure is there any to do this in without dynamic sql

[staticlibs]

@JayakumarEMIS

From the personal experience I would suggest to use such version name check, but to minimize the usage of dynamic T-SQL.

With a version check function like this:

CREATE FUNCTION RunningOnBabelfish() RETURNS varchar(1)
AS
BEGIN
    DECLARE @result varchar(1)
    IF @@version LIKE 'Babelfish%' BEGIN
        SET @result = 'T'
    END
    ELSE BEGIN
        SET @result = 'F'
    END
    RETURN @result
END
GO

You can run the same T-SQL script on both MSSQL and Babelfish guarding specific parts of it by the following checks:

IF dbo.RunningOnBabelfish() = 'T'
BEGIN
    ALTER TABLE ...
END
GO

There is a catch, that the whole T-SQL batch (until the next GO) will be compiled anyway on both MSSQL and Babelfish, even if it is guarded by the check. In some cases such compilation fails, for example there is no md5() function in MSSQL. In my experience such failures are infrequent, and the following approach can be used for them:

-- Fails on MSSQL, succeeds on Babelfish
CREATE FUNCTION MangleConstraintOrIndexName(@tableName varchar(128), @icName varchar(128))
RETURNS varchar(63) AS
BEGIN
    RETURN sys.babelfish_truncate_identifier(
        sys.babelfish_truncate_identifier(LOWER(@icName)) +
        sys.babelfish_truncate_identifier(LOWER(@tableName)) +
        md5(sys.babelfish_truncate_identifier(LOWER(@icName))))
END
GO

-- Fails on Babelfish, succeeds on MSSQL
CREATE FUNCTION MangleConstraintOrIndexName(@tableName varchar(128), @icName varchar(128))
RETURNS varchar(128) AS
BEGIN
    RETURN @icName
END
GO

IF dbo.RunningOnBabelfish() = 'T' BEGIN
    PRINT 'Warning about "mangleconstraintorindexname" is expected on Babelfish and can be safely ignored.'
END
GO

It creates different versions of the same function in MSSQL and in Babelfish and prints warning/error to output on the (expected) failed batches (parts of the script between GO).

I found such "let the batch fail" approach much easier to maintain (again, all statements are always compile-checked on both DBs) than the full-scale dynamic SQL (that turns into string concat soup very quickly).

If you find that dynamic bits cannot be avoided - I would suggest to move this dynamic logic to the Python/C# script one level higher and make this script to deal with multiple static T-SQL script files (running correct ones in correct order on correct DBMS) effectively implementing a schema migration tool like (like migration tools that exist in popular web frameworks like Django or Rails).

Also, when running these static T-SQL scripts with sqlcmd it may be convenient to use in-built support for varibale substitution (-v flag with $(varname) placeholders) and error messages (for failed batches) redirection with -r switch (reference).