Check connectivity for an oracle server from with in a SQL Server Stored procedure.

Hi Folks,

For one of my requirements I need to connect to the oracle server from within a sql server stored procedure and then proceed to do some operations.
How do I check whether the oracle server is up and running? If it is running then only do the remaining operations.

Actually I am importing some data from oracle to sql server database (Both the servers are on different machines). For that I have created one DTS package. And I am running that DTS package though a stored procedure. Before executing the DTS I truncate the tables , in the sql server database, for which the data is to be imported. But if for some reasons the DTS is not run the tables remain empty. So before truncating the data I wanna check if the oracle connectivity is there.

Let me know.

Thanx in advance.

Regards,

Samir Kudav.

You could create a "Linked Server" in your SQL Server database that establishes a connection to your Oracle server. Then in your stored procedure do a query against the linked server connection, and if no errors assume the Oracle DB is ready, otherwise remedy accordingly to the error provided. The test query could be something simple like a select count(*) from linkedserver..sys.dual which should always return 1 if the connection to oracle is made.

As long as the Oracle Conectivity software is installed.

Hi Guys,

Thanx for ur replies.

ducky,

I had that thing in my mind, but was checking if there was any other better way. But none-the-less your comment has confirmed my thinking and I will go with that. Thanx for ur reply.

Regards,

Samir.