|
|
Posted by downwitch on 05/01/07 21:16
No, I have printed it, it's fine. No truncation. Like my example
above, I can't even get a simple short statement to work (my real
example is 42 characters), and I don't see the error.
On May 1, 5:13 pm, manstein <jkelly.ad...@gmail.com> wrote:
> 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.
Navigation:
[Reply to this message]
|