executing a stored procedure

jjrbikes · Post by **jjrbikes** » Wed Dec 06, 2006 12:20 pm

I have searched and searched past posts for what I know will be a simple answer - but can't find the answer to my question. It's simple and here it is.... I'm trying to execute a stored procedure but can't quite figure out how to pass in the parameter needed by InsertErrorLog. Here's my osql command line:

[cmd ='osql -S ':SQL_Hostname: ' -d ':SQL_Database:' -U ':SQL_User:' -P ':SQL_Password: ' -Q "InsertErrorLog"]

and I've tried every variation I can think of to pass a parameter here - but nothing I've tried is working.

any suggestions?

thanks a bunch

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 1:14 pm

Where are you trying to execute this procedure?
If in datastage then in what stage?

jjrbikes · Post by **jjrbikes** » Wed Dec 06, 2006 1:31 pm

ahh - that would be helpful information now wouldn't it. Sorry.
I'm executing it in datastage but not using any of the provided stages. Due to the complexity of the job - and everything going on to trigger this Stored Procedure - I've written all my code in the JobControl section.

thanks!

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 1:35 pm

Granted you are using it in JobControl. How are you talking to your database? You need an open a connection with your database to run any query on it. Right ???

jjrbikes · Post by **jjrbikes** » Wed Dec 06, 2006 1:54 pm

Is it safe here for me to simply say "ODBC connection"?

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 2:32 pm

Not sure if its safe. Why ?
Read kcbland's reply here
You can even do this using an odbc stage in a job. It supports IN and IN/OUT parameters?
Any particular reservation why you want to do it via job control? You can even use STP stage if you have 7.5.2

jjrbikes · Post by **jjrbikes** » Wed Dec 06, 2006 2:39 pm

Hi -
Unfortunately I am not a premium member - so I can't read the bulk of your response.
As for why I'm not just calling this stored procedure utilizing one of the provided stages - it again goes back to the processing takeing place before this job and the constraints on whether or not this procedure will even be called. I have written a fair amount of code in the job control section - and whether or not this procedure is executed is determined by a simple IfThenElse statement in my code.
I guess it would be of some interest to note that I am running on the server edition of 7.5

thanks

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 2:44 pm

True, i understand your need. But did you read Ken's reply. Click the 'here' word in my previous post. He mentions a few limitations.

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 2:47 pm

Nevermind my previous post. I did not pay attention that you were using a third party tool. Did you try running the osql utility from command line? Does it work? If it does then post the part of the code that executes the stored procedure for debugging help.

jjrbikes · Post by **jjrbikes** » Tue Jan 09, 2007 8:45 am

Sorry for the delay in making this final post. I finally figured out how to pass the necessary input parameter in the command line - and it works beautifully. It was a matter of getting all the right quotes in the right places ....

[cmd ='osql -S ':SQL_Hostname: ' -d ':SQL_Database:' -U ':SQL_User:' -P ':SQL_Password: ' -Q "exec InsertErrorLog ':"'":expectedparameter:"'":'"']

where the following are all variables defined earlier in the code:
SQL_Hostname, SQL_Database, SQL_User, SQL_Password and expectedparameter

Thanks for your help everyone!

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 8:47 am

I am glad you came back with the solution. Thanks.

jjrbikes · Post by **jjrbikes** » Fri Jan 12, 2007 2:00 pm

You're Welcome!
I have to admit it is a pet peeve of mine ... when someone poses a question to the forum, banters back and forth and then replies with "I got it" - or something to that nature - but then fails to share the solution.

hope it's a great day.

DSguru2B · Post by **DSguru2B** » Fri Jan 12, 2007 2:07 pm

True. From time to time we should remind the "got it" posters to share the solution as well.