2

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

Improve this question
3
  • 2
    Check SELECT * FROM sys.servers Commented Sep 16, 2015 at 15:52
  • 1
    how about this, create a function on your MSSQL dev servers called TO_DATE() that just returns the first parameter. Commented Sep 16, 2015 at 16:07
  • That might be just what I need lad2025. Thanks, I'll test and see when I get the chance Commented Sep 16, 2015 at 16:17

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.