"Tim Bachmann" <Tim Bachmann@discussions.microsoft.com> wrote in message
news:0CAB61C6-8B0A-48E1-A876-ADC51730D08E@microsoft.com...
> I created the sql below as a stored procedure that passes in the sql
> statement as a paramater. I replace the double-quotes with single quotes
and
> then simply run the sql. I have to uses exec instead of select or else it
> doen't iniate the query.
>
> Why doesn't sql RS recognize the query? It runs fine from analyzer but RS
> will not recognize the fields in the proc. If I hard-code the sql in the
proc
> and use select instead of exec then it works just fine.
>
> .************************************************
> declare @SQL as varchar(8000)
>
> set @SQL = 'SELECT a.entry_id, a.line_wire_link_id, b.CarrierName AS
> Carrier, circuit_id AS CircuitID, b.DEAddress1 AS BEndAddress,
b.DEClient
> AS DEClient, b.DECityName AS BEndCity, RTRIM(JBA_Reference_Number) AS
> JBA_Reference_Number, RTRIM(JBA_Site_id) AS JBA_Site_id, line_lac,
> system_descriptor, COALESCE(aaa.TraderCount,0) AS TraderCount FROM
> (AFM_LINE_DS0 a LEFT JOIN AFM_LINE_DS0_Computed aaa ON a.entry_id =
> aaa.entry_id) LEFT OUTER JOIN AFM_LINE_DS0_Ext b ON a.Entry_ID =
b.Entry_ID
> WHERE 1 = 1 AND JBA_Reference_Number IN
>

>
> set @SQL = REPLACE(@SQL, '"', '''')
> exec (@SQL)
> .*****************************************

"Bruce L-C [MVP]" wrote:

> So is the below a snippet from your stored procedure?
>
> Have you tried clicking on the refresh fields button?
>
> If that doesn't do it try having the result go into a temporary table and
> then have the last statement be
> Select * from #temp
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tim Bachmann" <Tim Bachmann@discussions.microsoft.com> wrote in message
> news:0CAB61C6-8B0A-48E1-A876-ADC51730D08E@microsoft.com...
> > I created the sql below as a stored procedure that passes in the sql
> > statement as a paramater. I replace the double-quotes with single quotes
> and
> > then simply run the sql. I have to uses exec instead of select or else it
> > doen't iniate the query.
> >
> > Why doesn't sql RS recognize the query? It runs fine from analyzer but RS
> > will not recognize the fields in the proc. If I hard-code the sql in the
> proc
> > and use select instead of exec then it works just fine.
> >
> > .************************************************
> > declare @SQL as varchar(8000)
> >
> > set @SQL = 'SELECT a.entry_id, a.line_wire_link_id, b.CarrierName AS
> > Carrier, circuit_id AS CircuitID, b.DEAddress1 AS BEndAddress,
> b.DEClient
> > AS DEClient, b.DECityName AS BEndCity, RTRIM(JBA_Reference_Number) AS
> > JBA_Reference_Number, RTRIM(JBA_Site_id) AS JBA_Site_id, line_lac,
> > system_descriptor, COALESCE(aaa.TraderCount,0) AS TraderCount FROM
> > (AFM_LINE_DS0 a LEFT JOIN AFM_LINE_DS0_Computed aaa ON a.entry_id =
> > aaa.entry_id) LEFT OUTER JOIN AFM_LINE_DS0_Ext b ON a.Entry_ID =
> b.Entry_ID
> > WHERE 1 = 1 AND JBA_Reference_Number IN
> >
> ("061IPCE0","061IPCE0","071CAND0","071CAND0","071HKBR0","071HKBR0","071HKBR0
> ","071IPCA0","071IPCA0","071IPCA0","071IPCA1","071IPCC1","071IPCJ1","071IPCL
> 0","901IPCA0","901IPCX0","921IPCS0") AND Carrier = "4801" Order By
> Line_lac'
> >
> > set @SQL = REPLACE(@SQL, '"', '''')
> > exec (@SQL)
> > .*****************************************
>
>