MS_SQL Stored Procedure Best Practice Question

jpulaski · Post by **jpulaski** » Thu Oct 20, 2005 6:41 am

Greetings,

Question: I'm on Windows, DataStage 7.5, and I would like to execute a SQL-Server stored procedure. I don't need to feed any input data to the stored procedure, but would like to see if it works. I looked at the new Stored Procedure stage, but it doesn't look like it likes SQL-Server (but just DB2, Oracle, and Sybase). So, I'm wondering what is the best way for me to accomplish this.

Detail: I've searched the forum for an answer to my question above and found lots of information, but am still not sure of the best practice. So, any advice would be appreciated.

Thanks,

Jason

kcbland · Post by **kcbland** » Thu Oct 20, 2005 6:47 am

isql/osql command line script. By writing a reusable wrapper script now for executing SQL scripts or procedures, you'll enable a long term elegant solution for running scripts quite easily from a Command stage in a Sequencer, or the Command stage in Windoze Server jobs. You'll also be able to use a Batch job to directly call your script and use DS BASIC programming to interact with jobs, log messages, send emails, etc all within DS itself.

lstsaur · Post by **lstsaur** » Thu Oct 20, 2005 11:14 am

Hi Jason,
Using either isql or osql will work. However, if your SQL server is not located in the same server as your DataStage server, then you have to install the SQL Analyzer client on the server that the DataStage server resides. Second thing is that you have to hardcode the user ID and the password in your ISQL command script. Both secenarios are not good practice in the production environment. Good luck.

kcbland · Post by **kcbland** » Thu Oct 20, 2005 11:43 am

lstsaur wrote:you have to hardcode the user ID and the password in your ISQL command script

Really? A batch, perl, or java script can't fetch these values from a protected parameter file or environment variable when executing the command line call or even better use trusted connections?

lstsaur · Post by **lstsaur** » Thu Oct 20, 2005 3:29 pm

Hi Ken,
A batch script sure can retrive these values from either the job parameters or environment variables. My situation was an encrypted password retrieved from the parameters into the batch file, but the problem was that our job scheduler, Control-M, just wouldn't work with the encrypted password. However, after I hardcoded the password in the ISQL command and the whole thing worked just fine. That's why I said that you have to hardcode the password in the ISQL statement and it's not a good practice in the production environment.

kcbland · Post by **kcbland** » Thu Oct 20, 2005 7:03 pm

Have you ever tried indirection? In the Unix world, that's a common method for avoiding exposing command line options to the ps command. Your scheduler might indirect in the encrypted parameter from a hidden
file.

jpulaski · Post by **jpulaski** » Fri Oct 21, 2005 12:05 pm

i am using

osql -E /d CashRec /S AAENTSQL01 -Q "qry_electronic_Append_TRD"

where CashRec = the database name
AAENTSQL01 = the servername
qry_electronic_Append_TRD = the stored procedure name

and I am using the -E to use Windows NT authentication, so I believe it will log into the SQL database using the NT ID logged into windows. I am now wodering will this work if NO one is currently logged into the server in which the code resides?

mrlindsey · Post by **mrlindsey** » Thu Mar 23, 2006 10:49 am

What if we are running DataStage on Unix and can't call isql or osql from the command line. Is there another way to call a SQLServer stored procedure from a server job?

-Matt Lindsey

jdmiceli · Post by **jdmiceli** » Mon May 12, 2008 11:28 am

In Matt's case I have simply used the Dynamic RDBMS Stage to connect to the SQL Server instance I need, set it for User-Defined SQL and issue an 'exec sp_whatever'. The connection is already created by DS so I don't have to worry about passing authentication to the server.