crystal reports 10 incompatibility with single quotes in SP

My SP has this code but apparently Crystal Reports doesn't know how to
interpret the quotes in @.query:
fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
while @.@.FETCH_STATUS = 0
begin
select @.query = 'insert into #results_table ' +
'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
exec (@.query)
fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
end
For some reason, I can't see the fields on the crystal report if I use this
code (excluding the Fetch in the loop). When I comment this, then I can see
them. The reason I'm using it is because I can't use @.encounters_id (
consists of '(12,14,15)' ) in the regular Insert. So I need to do it like
this.
Any help is appreciated.This code is part of a stored procedure in SQL Server, right?
I know Crystal does some strange (and annoying) things with how it views
database objects, but I can't see how it would have any knowledge of this
piece of code. Crystal, as far as I know, only sees the data structure(s)
that your query returns and should be unaffected by this.
Have you confirmed that the stored procedure itself is able to execute by
running it in query analyzer?
"VMI" <vonchi_m AT yahoo DOT com> wrote in message
news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> My SP has this code but apparently Crystal Reports doesn't know how to
> interpret the quotes in @.query:
> fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
> while @.@.FETCH_STATUS = 0
> begin
> select @.query = 'insert into #results_table ' +
> 'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
> cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
> ' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
> exec (@.query)
> fetch next from c2 into @.person_Id, @.full_name, @.charge,
@.encounters_id
> end
> For some reason, I can't see the fields on the crystal report if I use
this
> code (excluding the Fetch in the loop). When I comment this, then I can
see
> them. The reason I'm using it is because I can't use @.encounters_id (
> consists of '(12,14,15)' ) in the regular Insert. So I need to do it like
> this.
> Any help is appreciated.
>|||Yes, the code is part of a stored procedure.
I do get a resultset when I run it, but I did notice now that the "Query
batch completed with errors." I had seen it before and I went through all
the messages and never saw any red text. I assumed that if I saw the result
set of the last Select statement, everything was fine. The problem was that
set nocount was off. I switched it to On and now I can see clearly that the
procedure found errors with the data being processed.
thanks again.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23R8aftQVGHA.4952@.TK2MSFTNGP09.phx.gbl...
> This code is part of a stored procedure in SQL Server, right?
> I know Crystal does some strange (and annoying) things with how it views
> database objects, but I can't see how it would have any knowledge of this
> piece of code. Crystal, as far as I know, only sees the data structure(s)
> that your query returns and should be unaffected by this.
> Have you confirmed that the stored procedure itself is able to execute by
> running it in query analyzer?
> "VMI" <vonchi_m AT yahoo DOT com> wrote in message
> news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> @.encounters_id
> this
> see
>|||The problem was the last names with quotes (O'neill, O'Sullivan, etc...)
Thanks for the help.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23R8aftQVGHA.4952@.TK2MSFTNGP09.phx.gbl...
> This code is part of a stored procedure in SQL Server, right?
> I know Crystal does some strange (and annoying) things with how it views
> database objects, but I can't see how it would have any knowledge of this
> piece of code. Crystal, as far as I know, only sees the data structure(s)
> that your query returns and should be unaffected by this.
> Have you confirmed that the stored procedure itself is able to execute by
> running it in query analyzer?
> "VMI" <vonchi_m AT yahoo DOT com> wrote in message
> news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> @.encounters_id
> this
> see
>|||VMI (vonchi_m AT yahoo DOT com) writes:
> My SP has this code but apparently Crystal Reports doesn't know how to
> interpret the quotes in @.query:
> fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
> while @.@.FETCH_STATUS = 0
> begin
> select @.query = 'insert into #results_table ' +
> 'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
> cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
> ' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
> exec (@.query)
> fetch next from c2 into @.person_Id, @.full_name, @.charge, >
@.encounters_id
> end
> For some reason, I can't see the fields on the crystal report if I use
> this code (excluding the Fetch in the loop). When I comment this, then I
> can see them. The reason I'm using it is because I can't use
> @.encounters_id ( consists of '(12,14,15)' ) in the regular Insert. So I
> need to do it like this.
No you don't! There is no need to use EXEC to handle comma-separated
list. Use a table-valued function that unpacks the list to a table.
The you don't need the dynamic SQL, and don't have to worry about
O'Neill.
See http://www.sommarskog.se/arrays-in-...ist-of-integers
for an example.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx