Dynamic SQL reading statements from table

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.

Re: Dynamic SQL reading statements from table

On May 1, 5:04 pm, downwitch 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.

Re: Dynamic SQL reading statements from table

On May 1, 5:08 pm, manstein wrote:
> On May 1, 5:04 pm, downwitch 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.

Re: Dynamic SQL reading statements from table

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 wrote:
> On May 1, 5:08 pm, manstein wrote:
>
>
>
> > On May 1, 5:04 pm, downwitch 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.

Re: Dynamic SQL reading statements from table

You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.

Just try:

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
EXEC sp_executesql @sql

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Dynamic SQL reading statements from table

OK, yes, that does solve the first problem, thank you. Now for
another, related. I'm using a variation on Erland's proc here
http://www.sommarskog.se/dynamic_sql.html#quotestring
to handle nested quotes. There are none, of course, in the simple
statement, but running it through the proc causes it to fail
nonetheless.

Here's my version of the function:
-----------
CREATE FUNCTION uQuoteString(@str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @ret nvarchar(MAX),
@sq nvarchar(4)
SELECT @sq = ''''
SELECT @ret = replace(@str, @sq, @sq + @sq)
RETURN(@sq + @ret + @sq)
END
-----------

So running

DECLARE @sql nvarchar(MAX)
SET @sql ='SELECT foo FROM foostable'
SET @sql = dbo.uQuoteString(@sql)
EXEC sp_executesql @sql

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

Note that the error has changed, no longer referencing the stored proc
but instead the @sql argument.


On May 1, 5:28 pm, "Plamen Ratchev" wrote:
> You are missing EXEC... It is optional only when you execute stored
> procedures that are the first statement in the batch.
>
> Just try:
>
> DECLARE @sql nvarchar(MAX)
> SET @sql ='SELECT foo FROM foostable'
> EXEC sp_executesql @sql
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Re: Dynamic SQL reading statements from table

downwitch (downwitch@gmail.com) writes:
> Here's my version of the function:
> -----------
> CREATE FUNCTION uQuoteString(@str nvarchar(MAX)) RETURNS nvarchar(MAX)
> AS
> BEGIN
> DECLARE @ret nvarchar(MAX),
> @sq nvarchar(4)
> SELECT @sq = ''''
> SELECT @ret = replace(@str, @sq, @sq + @sq)
> RETURN(@sq + @ret + @sq)
> END
> -----------
>
> So running
>
> DECLARE @sql nvarchar(MAX)
> SET @sql ='SELECT foo FROM foostable'
> SET @sql = dbo.uQuoteString(@sql)
> EXEC sp_executesql @sql
>
> I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
> Note that the error has changed, no longer referencing the stored proc
> but instead the @sql argument.

I added a PRINT @sql to your SQL batch, and this is what I saw:

'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.

A string on its own is not legal T-SQL.

I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.

Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dynamic SQL reading statements from table

manstein (jkelly.admin@gmail.com) writes:
> as an addendum, what is your (MAX) size?

MAX implies in SQL 2005 a size of two gigabytes.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dynamic SQL reading statements from table

Read that section in Erland's article again. The use of this function is
wrap an input parameter in quotes. I do not see any parameters in your SQL
statement, so no need to use the function.

Plamen Ratchev
http://www.SQLStudio.com

Re: Dynamic SQL reading statements from table

On May 1, 6:14 pm, Erland Sommarskog wrote:
> manstein(jkelly.ad...@gmail.com) writes:
> > as an addendum, what is your (MAX) size?
>
> MAX implies in SQL 2005 a size of two gigabytes.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.

Re: Dynamic SQL reading statements from table

Perhaps I wasn't as clear as I should have been. Regardless, the
problem is solved--turns out it wasn't just "related" to the first
problem, it *was* the first problem. That's why I'd introduced the
quotestring function, actually, because when I switched from variable
to SQL (on a more complicated query, obviously, than the example I
provided, including multiple parameter values), the string failed
without doubling its parameter quotes. And then I was seeing the
quotes around the SQL string as an output result, not a part of the
string...

In short, duh on me.

RE the security risk, I'm fully aware of it. But as is often the case
with a very sensitive db, if anyone even gains access to it in the
first place there are much bigger potential headaches than whether or
not they want to drop a nasty dynamic SQL statement on it.


On May 1, 6:13 pm, Erland Sommarskog wrote:
> downwitch (downwi...@gmail.com) writes:
> > Here's my version of the function:
> > -----------
> > CREATE FUNCTION uQuoteString(@str nvarchar(MAX)) RETURNS nvarchar(MAX)
> > AS
> > BEGIN
> > DECLARE @ret nvarchar(MAX),
> > @sq nvarchar(4)
> > SELECT @sq = ''''
> > SELECT @ret = replace(@str, @sq, @sq + @sq)
> > RETURN(@sq + @ret + @sq)
> > END
> > -----------
>
> > So running
>
> > DECLARE @sql nvarchar(MAX)
> > SET @sql ='SELECT foo FROM foostable'
> > SET @sql = dbo.uQuoteString(@sql)
> > EXEC sp_executesql @sql
>
> > I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
> > Note that the error has changed, no longer referencing the stored proc
> > but instead the @sql argument.
>
> I added a PRINT @sql to your SQL batch, and this is what I saw:
>
> 'SELECT foo FROM foostable'
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'SELECT foo FROM foostable'.
>
> A string on its own is not legal T-SQL.
>
> I don't really know what you want to achieve with your quotestring
> function, but you put the entire SQL statement in quotes, which
> certainly is not the right thing. You said you were reading statements
> from a table. I don't really see why you would double any quotes in
> these statements either.
>
> Another issue is that the operation is certainly unsafe if anyone can
> put statements intos this table, and you run your process with
> heavy privs.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: Dynamic SQL reading statements from table

manstein (jkelly.admin@gmail.com) writes:
> cool thanks. BTW what other declarations allow the use of MAX for
> size? I tried char and that did not work. That being the case, isnt
> this inconsistent implemetation? MS at its best.

Since char is fixed length, char(MAX) would imply a data type which is
always 2GB in size. I suspect that such a type would do more harm than
good.

In SQL 2005 you can use varchar(MAX), nvarchar(MAX) and varbinary (MAX).
These are the successors to text, ntext and image, which now are
deprecated. The MAX types works very much like the regular
(n)varchar/binary. In difference to the old types that had lots of
limitations.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx