This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Another report writer problem

lclemmons over 10 years ago

I have a custom SQL report that works fine in SQL management studio. When I copy and paste it into a custom SQL report in report writer and try to execute the query (or change to the field formatting tab which apparently triggers a query) the Querying Database box comes up but the query never completes. It runs in SQL management studio in about 5 seconds. I actually let this run in report writer while I left and worked on something else for 2 hours and it was still querying when I came back. The only things in this query that are any different from other custom reports I have are a table variable and a case clause. Does report writer have issues with these? I'm wondering if it has something to do with report writer attempting to assign default field formatting? I've noticed in previous reports it tries to assign formatting automatically and I've had to go in and change it to get the results I want. Perhaps it has trouble assigning the formatting because I'm populating the table variable and then doing my select from the table variable and it doesn't know how to deal with that? Is there a way I can prevent it from trying to automatically assign field formatting BEFORE it tries to execute a query for the first time? Or maybe I'm completely off track with that line of thinking?

Laura

Top Replies

njoylif over 10 years ago in reply to PavelSrot +1

typically, this happens when trying to use some more advanced SQL functions. I just had one where I used a CASE statement a few times. A reasonable workaround is to create the query as a stored procedure…

0 PavelSrot over 10 years ago

Hi,
Can you post your query which will never end in Orion Report Writer to be able look at it?
Cancel
Vote Up 0 Vote Down

Cancel
0 njoylif over 10 years ago in reply to PavelSrot

typically, this happens when trying to use some more advanced SQL functions.
I just had one where I used a CASE statement a few times.
A reasonable workaround is to create the query as a stored procedure and just have the report call the stored procedure.
Cancel
Vote Up +1 Vote Down

Cancel
0 lclemmons over 10 years ago in reply to njoylif

I'm betting on this. I do have a case statement in there as well as a table variable. I'll see if I can figure out the stored procedure thing. The query isn't actually finished yet. I was just testing an intermediate stage. I'm going to need a union to get the output I want I think.
Thanks for the responses.
Laura
Cancel
Vote Up 0 Vote Down

Cancel
0 lclemmons over 10 years ago in reply to lclemmons

Now that I think about it.. Isn't a table variable only local to the procedure it's in? If so, this won't work because I wouldn't be able to use a union in my final select statement. Ugh. I hate this kind of programming.
Laura
Cancel
Vote Up 0 Vote Down

Cancel
0 njoylif over 10 years ago in reply to lclemmons

download this content share for example
That might use "update" instead of create [Stored Procedure], but in SQL mgmt Studio, click on NPM DB -> programmability -> [RIGHT CLICK] stored procedures and select "new S.P."
Cancel
Vote Up 0 Vote Down

Cancel
0 lclemmons over 10 years ago in reply to njoylif

Thanks!
Cancel
Vote Up 0 Vote Down

Cancel