Hi,
I have a custom report that I need to add a new field to the report. I've added a new column stored procedure and all works well inside sql server mgmt studio. But I'm having a problem in Infomaker. I am able to connect to the database from inside Infomaker, but not getting anything in the new field when running the report. It appears that Infomaker is not seeing the new column from the stored procedure.
If I run/preview the report in info maker, I get the following error message:
"Select Error: SQLSTATE=42000Microsoft OLEDB Provider for SQL ServerCould not find stored procedure'dbo.LP_YALE_PERFORMACE_CUMULATIVE_SALES_2'
The Stored procedure does exist and is working. One thing I noticed is when browsing the database from within Infomaker, I don't see any of the stored procedures, only system stored procedures. Is there a permissions setting in sql server that will allow Infomaker to see the procedures? Any ideas?
Thanks,
Dave
If Paul's excellent suggestions don't help, make sure that the user account being used in Infomaker for the database connection is mapped as a member of ImpUsers. If it is not, all you will see are system stored procedures.
Hope this helps!
Paul & Steven,
Thanks! I think I'm almost there! Adding 'database=impresario' to the extended properties allowed me to see the procedures and tables within Infomaker, and I'm able to run the procedure and get results (and correct results at that, the procedure is returning what I expect it to).
I may also have an issue with the data types between sql server and info maker, or somehow not mapping the fields correctly (I've appended a new row to the column specification). The column I added to the report is a decimal, and i have the result in sql server cast to decimal. But, I am getting an odd random number on the report. I tried changing the data types to strings and get the same odd value.