Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Invalid object name ##pivot
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dsioson
Starting Member

6 Posts

Posted - 06/29/2006 :  22:17:08  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 06/30/2006 :  05:17:47  Show Profile  Visit nr's Homepage  Reply with Quote
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 call
set 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.
Go to Top of Page

dsioson
Starting Member

6 Posts

Posted - 06/30/2006 :  23:50:56  Show Profile  Reply with Quote
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,

Denver

Thanks....

Go to Top of Page

dsioson
Starting Member

6 Posts

Posted - 07/03/2006 :  05:49:29  Show Profile  Reply with Quote
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.

Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/03/2006 :  23:01:27  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000