How to Get SQL Query

Hello,
Just wondering if anyone knows of a way to get the SQL query that a
particular report is using. I keep getting "Unable to set commandtext" when
trying to run a dynamic query and can't figure out why. I don't believe the
query is getting to the server because I cannot see it in SQL profiler.
It would be helpful to know what query reporting services is attempting to
use.
Any help would be greatly appreciated.
Regards,
BenTry the static version of the query first and see if it returns the data
successfully. Then use the dynamic version. Can you post the query
expression?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:eSg$BqthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Just wondering if anyone knows of a way to get the SQL query that a
> particular report is using. I keep getting "Unable to set commandtext"
when
> trying to run a dynamic query and can't figure out why. I don't believe
the
> query is getting to the server because I cannot see it in SQL profiler.
> It would be helpful to know what query reporting services is attempting to
> use.
> Any help would be greatly appreciated.
> Regards,
> Ben
>|||Well I've been doing that, but unfortunately it craps out on the dynamic
portion (because there's a heck of a lot of IIF statements). Doesn't MSRS
compile the whole statement into a string first, then send it off to SQL
Server? It would be nice to get the string it compiled so I can know if I
made a typo somewhere rather than doing it the hard way (removing each IIF
statement one by one until I find the typo).
Was hoping there would be a backdoor to get this string...
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:eSX223thEHA.3944@.tk2msftngp13.phx.gbl...
> Try the static version of the query first and see if it returns the data
> successfully. Then use the dynamic version. Can you post the query
> expression?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:eSg$BqthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > Just wondering if anyone knows of a way to get the SQL query that a
> > particular report is using. I keep getting "Unable to set commandtext"
> when
> > trying to run a dynamic query and can't figure out why. I don't believe
> the
> > query is getting to the server because I cannot see it in SQL profiler.
> >
> > It would be helpful to know what query reporting services is attempting
to
> > use.
> >
> > Any help would be greatly appreciated.
> >
> > Regards,
> >
> > Ben
> >
> >
>|||What I do is to have a textbox with the expression so I can copy and paste
it into query analyzer. Try this without a dataset until you get what you
want and then go back to creating a dataset.
Bruce L-C
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:eXGcPLuhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Well I've been doing that, but unfortunately it craps out on the dynamic
> portion (because there's a heck of a lot of IIF statements). Doesn't MSRS
> compile the whole statement into a string first, then send it off to SQL
> Server? It would be nice to get the string it compiled so I can know if I
> made a typo somewhere rather than doing it the hard way (removing each IIF
> statement one by one until I find the typo).
> Was hoping there would be a backdoor to get this string...
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:eSX223thEHA.3944@.tk2msftngp13.phx.gbl...
> > Try the static version of the query first and see if it returns the data
> > successfully. Then use the dynamic version. Can you post the query
> > expression?
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > news:eSg$BqthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > Just wondering if anyone knows of a way to get the SQL query that a
> > > particular report is using. I keep getting "Unable to set
commandtext"
> > when
> > > trying to run a dynamic query and can't figure out why. I don't
believe
> > the
> > > query is getting to the server because I cannot see it in SQL
profiler.
> > >
> > > It would be helpful to know what query reporting services is
attempting
> to
> > > use.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Regards,
> > >
> > > Ben
> > >
> > >
> >
> >
>|||You can also use SQL Profiler to capture the actual statement being sent to
SQL server.
hth, Mike
"Bruce Loehle-Conger" <bruce_lc@.hotmail.com> wrote in message
news:efgDjZvhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> What I do is to have a textbox with the expression so I can copy and paste
> it into query analyzer. Try this without a dataset until you get what you
> want and then go back to creating a dataset.
> Bruce L-C
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:eXGcPLuhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > Well I've been doing that, but unfortunately it craps out on the dynamic
> > portion (because there's a heck of a lot of IIF statements). Doesn't
MSRS
> > compile the whole statement into a string first, then send it off to SQL
> > Server? It would be nice to get the string it compiled so I can know if
I
> > made a typo somewhere rather than doing it the hard way (removing each
IIF
> > statement one by one until I find the typo).
> >
> > Was hoping there would be a backdoor to get this string...
> >
> >
> > "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in
message
> > news:eSX223thEHA.3944@.tk2msftngp13.phx.gbl...
> > > Try the static version of the query first and see if it returns the
data
> > > successfully. Then use the dynamic version. Can you post the query
> > > expression?
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > > news:eSg$BqthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Hello,
> > > >
> > > > Just wondering if anyone knows of a way to get the SQL query that a
> > > > particular report is using. I keep getting "Unable to set
> commandtext"
> > > when
> > > > trying to run a dynamic query and can't figure out why. I don't
> believe
> > > the
> > > > query is getting to the server because I cannot see it in SQL
> profiler.
> > > >
> > > > It would be helpful to know what query reporting services is
> attempting
> > to
> > > > use.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Regards,
> > > >
> > > > Ben
> > > >
> > > >
> > >
> > >
> >
> >
>|||The original post mentions that the SQL statement is not showing up in
profiler.
Could you post your query expression?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Morisoli" <michael@.removeme.vieon.net> wrote in message
news:e%23$l8qwhEHA.4092@.TK2MSFTNGP10.phx.gbl...
> You can also use SQL Profiler to capture the actual statement being sent
to
> SQL server.
> hth, Mike
> "Bruce Loehle-Conger" <bruce_lc@.hotmail.com> wrote in message
> news:efgDjZvhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > What I do is to have a textbox with the expression so I can copy and
paste
> > it into query analyzer. Try this without a dataset until you get what
you
> > want and then go back to creating a dataset.
> >
> > Bruce L-C
> >
> > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > news:eXGcPLuhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > > Well I've been doing that, but unfortunately it craps out on the
dynamic
> > > portion (because there's a heck of a lot of IIF statements). Doesn't
> MSRS
> > > compile the whole statement into a string first, then send it off to
SQL
> > > Server? It would be nice to get the string it compiled so I can know
if
> I
> > > made a typo somewhere rather than doing it the hard way (removing each
> IIF
> > > statement one by one until I find the typo).
> > >
> > > Was hoping there would be a backdoor to get this string...
> > >
> > >
> > > "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in
> message
> > > news:eSX223thEHA.3944@.tk2msftngp13.phx.gbl...
> > > > Try the static version of the query first and see if it returns the
> data
> > > > successfully. Then use the dynamic version. Can you post the query
> > > > expression?
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > > > news:eSg$BqthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > > Hello,
> > > > >
> > > > > Just wondering if anyone knows of a way to get the SQL query that
a
> > > > > particular report is using. I keep getting "Unable to set
> > commandtext"
> > > > when
> > > > > trying to run a dynamic query and can't figure out why. I don't
> > believe
> > > > the
> > > > > query is getting to the server because I cannot see it in SQL
> > profiler.
> > > > >
> > > > > It would be helpful to know what query reporting services is
> > attempting
> > > to
> > > > > use.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Ben
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>