CODEWITHSUNDEEP
sql-serveroracle-database
Andy Entrekin
Andy Entrekin

Reputation: 83

Programmatically determine if linked server is Oracle or SQL Server

This is probably a bit of a strange question but how would I be able to determine inside a stored procedure if a linked server is Oracle or SQL server?

To give a bit of context, we have an application that in production queries a linked server (Oracle server, not ours) that contains PID (personally identifiable information) and thus we aren't allowed to use it in our dev and test environments. We unfortunately can't set up our own Oracle servers so in our environments for testing we have an SQL Server instance set up to act as the linked server with mocked up data in it.

For 99% of our queries this doesn't cause any issues except one with this line in it

WHERE LAST_MODIFIED > TO_DATE(''''' + CONVERT(nvarchar(24), @maxImageDate, 120) + ''''',''''YYYY-MM-DD HH24:MI:SS'''')

which we generally manually modify in dev and testing environments to be

WHERE LAST_MODIFIED > ''''' + CONVERT(nvarchar(24), @maxImageDate, 120) + '''''

My first idea was to attempt a query that would only work on oracle:

SELECT 1 FROM OPENQUERY(LinkedServerName, ''SELECT * FROM v$version'')

in a TRY/CATCH and run a different query if that fails but that causes the transaction to be uncommitable even after catching the error

We're starting to use octodeploy for automatic deployment and we could have the tentacle agent run an SQL script to modify the SP depending on the environment but would prefer to keep all the logic and code inside the SP and therefore in the repo but if we can't figure out another solution we'll probably go with that.

Any help would be greatly appreciated

Upvotes: 2

Views: 1234

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15251

I agree with @lad2025 and I would also use sys.servers (not sure why s/he didn't post an answer).

While you may be able to determine which servers are Oracle by the existing data there (such as the provider column) I would set the product column myself for maximum flexibility (such as when the business rules change and they have an Oracle server set up that they WANT to query from dev).

For example, when using sp_addlinkedserver, just set @srvproduct to "Oracle" or "doNotQueryFromDevOrTest" or whatever you want to use, and then put that in your proc's if-then.

Note that the docs say you can use anything in this column (up to 128 chars) when linking to Oracle. A quick test seems to verify this, even for SQL Server.

I don't know of any ANSI-SQL query that's defined to return a product name or RDBMS version.

Also, I would not recommend using try-catch for normal control flow if you have other options. Exception handling is usually expensive compared to an if-then.

Upvotes: 3

Ricardo Pontual
Ricardo Pontual

Reputation: 3757

Use this SELECT:

select product from sys.servers where srv_name='LINKED_SERVER_NAME'

Product returns MSSQL or SQL Server for Microsoft SQL Servers.

Hope it helps.

Upvotes: 0

Bruce
Bruce

Reputation: 1623

lad2025 gave you a good answer but if you don't have access to sys.servers you could try


If (Select @@SERVERNAME) = 'YourProdServerNameHere'
Begin
--  Do prod server stuff
End
Else
Begin
--  Do dev and test server stuff
End

Upvotes: 0

Related Questions