DSXchange

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

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

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!

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 ???

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

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

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

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.

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.

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!

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

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.

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