[SOLVED] SQL Server 2005 Job - Call PLSQL procedure using connection string from registry

SQL Server 2005 Job - Call PLSQL procedure using connection string from registry

I hope I am asking a reasonably easy question - just trying to save myself some time to be honest. :)

All I want is to call a PL/SQL procedure from a SQL Server job.

I already have the connection string in a registry string and I have the procedure built.

I dont know if I can just specify T-SQL and grab the registry setting somehow, but I suspect that I need an ActiveX script (if this is possible).

Does anyone have this (admittedly quite specific) code snippet handy?

Thanks in advance!

Frosty

Solution

got there in the end. This worked for me under an ActiveX job:

Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002

Dim oConnection, oRegistry, sConnectionString, sPlsqlCall, sRegistryKey, sValueName, sParam1, sParam2

' Get connection string from registry
Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
sRegistryKey = "Software\OracleConnectionString"
sValueName = "Value"
oRegistry.GetStringValue HKEY_CURRENT_USER, sRegistryKey, sValueName, sConnectionString

' Set up PLSQL query
sPlsqlCall = "my_package.my_procedure(" & sParam1 & ", " & sParam2 & ")"

' Call PLSQL
Set oConnection = CreateObject("ADODB.Connection")
oConnection.ConnectionString = sConnectionString
oConnection.Open
oConnection.Execute sPlsqlCall

oConnection.Close
Set oConnection = Nothing
Set oRegistry = Nothing