473,320 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

May 1 '07 #1
11 5112
On May 1, 5:04 pm, downwitch <downwi...@gmail.comwrote:
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.

May 1 '07 #2
On May 1, 5:08 pm, manstein <jkelly.ad...@gmail.comwrote:
On May 1, 5:04 pm, downwitch <downwi...@gmail.comwrote:


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.

May 1 '07 #3
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.comwrote:
On May 1, 5:08 pm, manstein <jkelly.ad...@gmail.comwrote:
On May 1, 5:04 pm, downwitch <downwi...@gmail.comwrote:
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.

May 1 '07 #4
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

May 1 '07 #5
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" <Pla...@SQLStudio.comwrote:
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

May 1 '07 #6
downwitch (do*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 1 '07 #7
manstein (jk**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 1 '07 #8
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

May 1 '07 #9
On May 1, 6:14 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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.

May 2 '07 #10
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 <esq...@sommarskog.sewrote:
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/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

May 2 '07 #11
manstein (jk**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '07 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tom | last post by:
I would like to know if an .asp case statement can contain HTML elements. I am building an application that I would like to have dynamic choices. The dynamic part would be built in the a case...
3
by: Steve | last post by:
Hi; I would like to read a list of tables from a temp table and then do a sql statement on each table name retrieved in a loop, ie: -- snip cursor loop where cursor contains a list of...
4
by: Tim.D | last post by:
People, I've ventured into the wonderful world of Stored Procedures. My first experience has been relatively successful however I am stuck on using host variables to specifiy actualy table or...
3
by: Alexandre H. Guerra | last post by:
Hello I need to process a SQL monitoring log stored in a table to group the statements that change just the constants in it. Ex: select a,b,c from table where (a = 'xyz' and b = 123 and c !=...
6
by: MattC | last post by:
Hi, I'm implementing a new Business Layer in one of our applications. I'm toying with the idea of placing all the Create, Read, Update and Delete SQL in the object in question and build a...
3
by: sferriol | last post by:
hello is it possible with postgres 7.2 or more, to define a dynamic view. For example, i have a table with a column 'user' and i want to define a view which gives infomrations from different...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
5
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
6
by: =?ISO-8859-1?Q?Tim_B=FCthe?= | last post by:
Hi, we are building a Java webapplication using JSF, running on websphere, querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and PreparedStatements only (aka dynamic SQL). Every night,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.