TI: Passing parameters to a stored procedure as a datasource

[MikeF]

Hi,

We're trying to use an SQL Server stored procedure as a data source but having trouble getting it to work with parameters. Currently the TI sql Query window looks like:

Code: Select all

exec wbContractForecastPMRArr '5022', 2015, 11;

This results in a very useful "SQL Statement Failed" error report.

We're able to use the same query with no parameters fine and get the desired results back.

We've tried with other known good stored procedures too in case that sp has issues, but we get the same result. Using

Code: Select all

"DatasourceQuery ="

etc to build the query string again doesn't seem to help either. No joy. Tried adding brackets here and there and that doesn't seem to help either.

Any hints or tips to get this working? If we can't get parameters to work then it's back to csv's and I'd really rather not go there.

Mike

Cognos Express 10.2.1.2

[tomok]

You don't need the "exec". You can call a stored procedure by just listing it's name as the SQL string. I think your problem is you are not separating the procedure name from the parameters. Try it like this:

Code: Select all

wbContractForecastPMRArr('5022', 2015, 11);

If that doesn't work put the "exec" back in. Also, make sure in the database the field represented by the first parameter is indeed defined as a string (varchar, nvarchar, etc.) and that fields for parameters 2 and 3 are numeric.

[MikeF]

Hi Tomok,

No, that still fails with the "SQL Statement Failed" message, both with and without the 'exec'.

The same query works from Excel as a data connection, so the parameters would appear to be in the right format.

Mike

[rmackenzie]

Have you checked from a permissions point of view? You need read, execute and, probably, modify. Depending on how you set-up your ODBC connection you should maybe check the permissions of the TM1 service account in SQL Server if you are not specifying a username and password for the TI data source.

[MikeF]

We finally got this to work.

Not sure what's up still and where the problem really is, but by dropping the string parameter and sending just the two numeric ones it works. Luckily at this stage we can hard code that string parameter in the SP.