Job parameter in Stored procedure.

rpdeepak.kumar · Post by **rpdeepak.kumar** » Wed Aug 13, 2008 12:51 pm

Hi

I'm using Stored Procedure Stage to run a procedure which will truncate the table before loading it Target table.

Issue:
When I pass the job parameters in the fields Database , Datasource, UserId and Pssword, it is throwing a run time error(Invalid username/pwd).

Since, this is the first time I am using a stored procedure stage, can anyone please confirm the usage of job parameters in this stage. Whether it can be used or we should hard code the UID credentials.
Thanks
Deepak

chulett · Post by **chulett** » Wed Aug 13, 2008 2:30 pm

Yes, you can use job parameters for all of the connection information in that stage.

rpdeepak.kumar · Post by **rpdeepak.kumar** » Wed Aug 13, 2008 3:30 pm

Thanks for your reply

Yes we did in the connection tab which is having in the Job parameters.

example #$env_database_name$#, But Job was aborted.

If we do hard code in the connection tab ,Job was run successfully

Please help me clearly.

Thanks
Deepak

chulett · Post by **chulett** » Wed Aug 13, 2008 3:41 pm

Pass in the parameter name correctly and it should work fine. Right-click on the stage, select Grid Style and then use the Insert Job Parameter widget to populate the fields.

rpdeepak.kumar · Post by **rpdeepak.kumar** » Wed Aug 13, 2008 4:00 pm

I'm very Thankful to you .

I will try

chulett · Post by **chulett** » Wed Aug 13, 2008 4:07 pm

Hand typing leads to errors so I use the 'helper' when I can. For example, environment variables have only one dollar sign in them, not two.

chulett · Post by **chulett** » Fri Aug 15, 2008 11:11 am

Then I would assume your environment variables are not correct in the job. How exactly are they defined and how did you set them in the job?

csri · Post by **csri** » Fri Aug 15, 2008 11:17 am

as Craig mentioned are you making sure that you are not using dollar twice (#$env_database_name$#) and using it correctly like #$env_database_name#. Can you write the environment variable value to a file and see what value is being passed to the procedure?

rpdeepak.kumar · Post by **rpdeepak.kumar** » Fri Aug 15, 2008 1:09 pm

While typing i made a mistake in forum only that two $ ,but gave one $ only in the Environment variable

chulett · Post by **chulett** » Fri Aug 15, 2008 1:23 pm

chulett wrote:Then I would assume your environment variables are not correct in the job. How exactly are they defined and how did you set them in the job?

throbinson · Post by **throbinson** » Fri Aug 15, 2008 1:31 pm

Hold on there Tex. Some stages, like the Teradata API will NOT work correctly with environment variables. That is, parameters that contain dollar signs. It could be that the 8.x Stored Procedure is also such a stage. Try a parameter without the dollar sign. If it works, there you go, you can't use the Project level parameters. Politely ask IBM why and if it wouldn't be too much of a bother, would they mind too awfully, coding a friggin patch.

chulett · Post by **chulett** » Fri Aug 15, 2008 2:05 pm

That could very well be, considering it doesn't just give some sort of "invalid userid/password" response but instead falls over dead.

Keep in mind the original question was "can we use job parameters in the SP stage" which the answer is still an emphatic yes. The issue of using User Defined Environment Variable job parameters in the stage, however, is a whole 'nuther kettle of fish it seems.

toshea · Post by **toshea** » Fri Aug 15, 2008 2:30 pm

I believe there is already a patch for this issue. Look for a patch for e104775.