Posted by manstein on 05/01/07 21:13

On May 1, 5:08 pm, manstein <jkelly.ad...@gmail.com> wrote:
> On May 1, 5:04 pm, downwitch <downwi...@gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I'm using a 3rd-party app's back end which stores SQL statements in a
> > table, so I have no choice but to use dynamic SQL to call them (unless
> > someone else knows a workaround...)
>
> > Problem is, I can't get the statement to run properly, and I can't see
> > why. If I execute even a hard-coded variation like
>
> > DECLARE @sql nvarchar(MAX)
> > SET @sql ='SELECT foo FROM foostable'
> > sp_executesql @sql
>
> > I get: Incorrect syntax near 'sp_executesql'.
>
> > If I run
>
> > sp_executesql 'SELECT foo FROM foostable'
>
> > I get: Procedure expects parameter '@statement' of type 'ntext/nchar/
> > nvarchar'.
> > which I understand, as it's omitting the N converter--so if I run
>
> > sp_executesql N'SELECT foo FROM foostable'
>
> > it's fine. I don't understand why the first version fails. Is it some
> > sort of implicit conversion downgrading @sql? Every variation of CAST
> > and CONVERT I use has no effect.
>
> > This is SQL Server 2005 SP2. Thanks in advance.
>
> Try printing your @sql parameter and then firing it mannually, you
> might find that the string is not what you expect. Anyway, that is my
> standard way of debugging dynamic sql.- Hide quoted text -
>
> - Show quoted text -

as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.

[Back to original message]