DSXchange

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

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

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

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.

I'm very Thankful to you .

I will try

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.

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?

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?

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

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.

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.

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