Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dsioson
Starting Member
6 Posts |
 Posted - 2006-06-29 : 22:17:08
|
| Hi,I used the code you posted on creating a dynamic cross tab and stored it in a stored procedure "ZGC_PD_PTR_CROSSTAB" then values are being passed to it by calling/executing a stored procedure "ZGC_PD_PTR_REPORT". When executing the latter stored proc in QUery Analyzer, it works fine however when I'm integrating it in the SQL Reporting Services in .Net as Dataset, I encountered the error "Invalid object name ##pivot". How do I get rid of this error.Thanks,Denver |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-30 : 05:17:47
|
| Probably this is the client trying to get the format of the resultset by calling the sp with set fmtonly on. If you use the profiler you will probably see this.This produces an error as it tries to run the sp statements without creating temp tables.You can usually get round it by including the set command wit the sp callset fmtonly off exec sp....It does mean that the sp will be executed twice so you shouldn't do it for anything that changes data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dsioson
Starting Member
6 Posts |
Posted - 2006-06-30 : 23:50:56
|
| Hi, I will be trying your suggestion on Monday since I'm not in the office right now and try to figure out if will work. However, with your suggestion of setting the command "set fmtonly off exec sp...". given info on my sp names can you tell me where should I place it in my case? Please refer below to my options:a. In my case, my sp who executes the crosstab query is stored in the "ZGC_PD_PTR_REPORT"? Here is the actual prototype code:EXEC ZGC_PD_PTR_CROSSTAB 'Select .....' , 'SUM(Quantity)', 'SIZE_CODE' , 'ZPREPACK_TYPE_RATIO'So from this statement including your suggestion will now be....SET FMTONLY OFF EXEC ZGC_PD_PTR_CROSSTAB 'Select .....' , 'SUM(Quantity)', 'SIZE_CODE' , 'ZPREPACK_TYPE_RATIO'b. Do i just type and run it in Query Analyzer?c. Do i have to place it in both sp names?Also, take note that I'm using the .NET SQL Reporting Services to define the dataset which is in my case "ZGC_PD_PTR_REPORT". I also heard that .NET has limitations on temporary tables. Is this true?Thanks,DenverThanks.... |
|
|
|
dsioson
Starting Member
6 Posts |
Posted - 2006-07-03 : 05:49:29
|
| Hi,Your suggestion did not work instead, i got an error saying "Incorrect syntax near the keyword 'END'". Amazingly, in query analyzer, it still works but loading the dataset from SQL Reporting services of .Net, the error occured. Please help me and need some approach how to get rid of this error.Thanks. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-03 : 23:01:27
|
this is syntactical error, check what is near END and see if this is correct...or you can post the entire query here and we try to identify the source of the error --------------------keeping it simple... |
|
|
|
|
|
|
|
|
Invalid object name ##pivot