Thursday, March 29, 2012
Echo for SQL scripts
I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -
drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist
7444 rows deleted.
Commit complete.
Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?
ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -
set echo on
spool log_file.log
@.script_name.sql
spool off
the log file only had this error message -
SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -
set echo on
spool log_file.log
@.script_name.sql
spool off
the log file only had this error message -
SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.
This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:
Thursday, March 22, 2012
Easy IF statement (Beginner)
Im not really keen on If statements, so im trying to figure out how to write an if statement in a matrix. I want it to list the values that begin with 4's as 2004 the values that start with 5's as 2005 and the values that begin with 6's as 2006. How do i do this? Whats the IF statement code, i know how to put one option. but now all.
=IIF(Fields!ICNNo.Value=Left(1),2004,) ? I know thats wrong..
Please Help.
One way you could do this is using nested IIF statements. Like this:
=iif(left(Fields!ICNNo.Value, 1) = "4", "2004",
iif(left(Fields!ICNNo.Value, 1) = "5", "2005",
iif(left(Fields!ICNNo.Value, 1) = "6", "2006", "OtherValue")))
Hope this helps.
Jarret
|||It worked!!! THANKS ALOT!!!! It worked perfectly!Sunday, March 11, 2012
Dynamically Create columns
dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
creates 5 columns. Could I generate the T-SQL statements, store it in a
variable and pass the value of the variable for achieving this?
Many thanks
ShahriarDynamic SQL - http://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:MWQ5e.6009$9i7.3961@.trnddc04...
> Based on the value of a variable I want to be able to create columns
> dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
> creates 5 columns. Could I generate the T-SQL statements, store it in a
> variable and pass the value of the variable for achieving this?
> Many thanks
> Shahriar
>|||In general that's a really bad idea. Please explain why you want to do this
because I expect there is a better way.
In a well-designed application, the columns in a table should be static. To
add columns dynamically in TSQL you will need to use dynamic code, again
something that you should avoid for lots of good reasons. See the following
article:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||Hmmm...I do not understand your question completely but you can do this:
if @.n=5
select col1,col2,col3,col4,col5
from MyTable
else if @.n=6
select col1,col2,col3,col6,col7
from MyTable
else
...etc etc
OR
declare @.sqlString vachar(255),@.colNames varchar(255);
set @.sqlString = '';
set @.colNames = '';
if @.n=5
set @.colNames= ' col1,col2,col3,col4,col5 ';
else if @.n=6
set @.colNames= ' col1,col2,col3,col6,col7 ';
else
...etc etc
set @.sqlString='select '+colNames+' from MyTable';
--for non parametrized dynamic query
exec @.sqlString;
OR
--for parametrized dynamic query and better performance
sp_executesql @.sqlString
Anyway it heavily depends what you actually want to accomplish. And inline
sql query is always better solution then dynamic.
Regards,
Marko Simic
"Shahriar" wrote:
> Based on the value of a variable I want to be able to create columns
> dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
> creates 5 columns. Could I generate the T-SQL statements, store it in a
> variable and pass the value of the variable for achieving this?
> Many thanks
> Shahriar
>
>|||David
How would you suggest I should tackle this problem without being able to
create Columns Dynamically.
Lets say I have the following table:
Name Car YearBought
John BMW 2004
John FORD 2003
Mary LEXUS 2001
Harry FORD 1999
Harry BMW 2000
Harry VW 2002
Henry JEEP 2004
I want a new table that looks like this:
BMW FORD JEEP LEXUS VW
John 2004 2003
Mary 2001
Harry 2000 1999 2002
Henry 2004
Please note the sort order of columns and also be able to accomodate
inserting a person that drives a car that has not been defined previously.
i.e... the following record gets inserted (Henry JAGUAR 2005 ), as a
result my new table should be:
BMW FORD JAGUAR JEEP LEXUS VW
...
...
...
By being able to dynamically create columns, I could program this quite
easily. Any suggestion(s) is much appreciated.
Thanks
Shahriar
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:VZqdnTgfX9DqQMrfRVn-1Q@.giganews.com...
> In general that's a really bad idea. Please explain why you want to do
> this because I expect there is a better way.
> In a well-designed application, the columns in a table should be static.
> To add columns dynamically in TSQL you will need to use dynamic code,
> again something that you should avoid for lots of good reasons. See the
> following article:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>|||The answer is that you just DON'T do this. Such a table is a violation of
Normalization and other basic database design principles. So I repeat my
original question: Why would you want to?
If you just want a report, that's a different matter. A report isn't a
table. This is called a Cross Tab report and most reporting tools will
generate it automatically for you.
David Portas
SQL Server MVP
--|||Simic
Thank you. You put me in the right direction. This is what I wanted to do.
I can change the @.mysql statement and exec will take care of it. Thanks.
declare @.mysql varchar (255)
Set @.mysql='Create table x (col1 int)';
exec(@.mysql)
Shahriar
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:B79F7D5F-D6EE-426F-B963-46D1FA50842E@.microsoft.com...
> Hmmm...I do not understand your question completely but you can do this:
> if @.n=5
> select col1,col2,col3,col4,col5
> from MyTable
> else if @.n=6
> select col1,col2,col3,col6,col7
> from MyTable
> else
> ...etc etc
> OR
> declare @.sqlString vachar(255),@.colNames varchar(255);
> set @.sqlString = '';
> set @.colNames = '';
> if @.n=5
> set @.colNames= ' col1,col2,col3,col4,col5 ';
> else if @.n=6
> set @.colNames= ' col1,col2,col3,col6,col7 ';
> else
> ...etc etc
> set @.sqlString='select '+colNames+' from MyTable';
> --for non parametrized dynamic query
> exec @.sqlString;
> OR
> --for parametrized dynamic query and better performance
> sp_executesql @.sqlString
> Anyway it heavily depends what you actually want to accomplish. And inline
> sql query is always better solution then dynamic.
> Regards,
> Marko Simic
> "Shahriar" wrote:
>|||David
I am not sure why you are so persistent in this. What am I violating? I
want to create a STAND ALONE table. Normalization comes into picture if
this table will somehow relate to another table. In this case, it does not.
Purely a stand alone table. What I was looking for was something like this:
declare @.mysql varchar (255)
Set @.mysql='Create table x (col1 int)';
exec(@.mysql)
By changing my mysql statement, I can achieve what I want to accomplish.
Here is a nice sample use of the above application.
Lets say I want to create the sample table I posted earlier on a monthly
basis on a web site. I will run my application to generate the new table
and I am done. Solving this the way I did, has nothing to do with
normalization!
Regards
Shahriar
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:lfmdnZ8-ruDceMrfRVn-2g@.giganews.com...
> The answer is that you just DON'T do this. Such a table is a violation of
> Normalization and other basic database design principles. So I repeat my
> original question: Why would you want to?
> If you just want a report, that's a different matter. A report isn't a
> table. This is called a Cross Tab report and most reporting tools will
> generate it automatically for you.
> --
> David Portas
> SQL Server MVP
> --
>|||These articles may help you bit more:
http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlteam.com/item.asp?ItemID=5741
http://www.umachandar.com/technical...cripts/main.htm
Regards,
Marko Simic
"Shahriar" wrote:
> Simic
> Thank you. You put me in the right direction. This is what I wanted to d
o.
> I can change the @.mysql statement and exec will take care of it. Thanks.
> declare @.mysql varchar (255)
> Set @.mysql='Create table x (col1 int)';
> exec(@.mysql)
> Shahriar
> "Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
> news:B79F7D5F-D6EE-426F-B963-46D1FA50842E@.microsoft.com...
>
>|||No it wont.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:aZydnWgKIPm9n8XfRVn-3w@.giganews.com...
> Check out Reporting Services:
> http://www.microsoft.com/sql/reporting/default.asp
> It will do all you want without the maintenance and security implications
> of Dynamic SQL. It's also a more scalable approach than duplicating the
> data for every report in a table.
> --
> David Portas
> SQL Server MVP
> --
>
Wednesday, March 7, 2012
Dynamic where clause with if..else or case
Hello all...
I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select
i.e
if...@.where = @.where + ' llll '
if...@.where = @.where + ' llll '
select @.statement = @.statement + @.where
exec(@.statement)
I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.
Is it possible to use either statement inside a where clause?
i.e
where if a = 1 then d=e
else contains(.....)
thanks
No. You cannot use control of flow statements in the WHERE clause. CASE is an expression so you can use it in the WHERE clause. Since CONTAINS is a predicate in itself, you need to do something like:
case ... when .. then (select 1 where contains()) else ... end = 1
But it is not clear why you need to use dynamic SQL or build WHERE clause like this. Using dynamic SQL can cause performance problems and has lot of security implications. So you need to be aware of the risks while using it. CONTAINS predicate in SQL Server 2000 (from SP3 I think) and SQL Server 2005 can take a variable for the search expression so you probably don't dynamic SQL for this. If you have different checks to perform then you may actually be better off using multiple IF statements that call specific SPs with SELECT statements. This will provide the best performance advantage.
|||Thanks. I need the dynamic where based on search criteria because it was decided to make one SP compared to about 50 or more because there are possible combinations of the criteria...
The purpose of the contains is to search a text field for a value that is close to what what asked for. This is one of our slowest SPs so I thought I would give it a shot to make it quicker but I guess I will have to just be happy with what it is.
Thanks for the help.
|||Check out the article at the link below for various techniques on how to do dynamic searches using SPs.
http://www.sommarskog.se/dyn-search.html
Sunday, February 19, 2012
Dynamic SQL statements over 4000 chars
I am using SQL Server 2000.
I want to dynamically build a SQL statement for pivot query, which come out
more than the character limits for variable and sp_executesql..
What I need is: a query to return all security_level for all User_Id for all
screen_id. We have more than 280 user accounts in AcctUsers table. The
Output format need to be:
Screen User1 User2 User3 .....
Window1 0 1 2
Window2 3 2 0
I used cursor to get user_ids from AcctUsers table, then dynamically build
the SQL statment based on the User_ID. It works fine for up to about 40
user_ids. But it out off the chararter limit for variable nverachar and
sp_executesql. The way that I choose dynamical SQL because the business user
may add new user_id to AcctUsers table and then run a report, which will
call this query.
Can anyone help me?
Thanks.
Perayu
Here are the DDL:
CREATE TABLE [dbo].[AcctUsers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Security] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[screen_id] [smallint] NOT NULL ,
[security_level] [smallint] NOT NULL
) ON [PRIMARY]
GOHi!
Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
I would suggest you to read the complete article.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>|||Great article!
Here is the trick that I will try, nesting EXEC:
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
Thanks for your help!
Perayu
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:Oqh49AUPGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
> I would suggest you to read the complete article.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||If unicode is not a requirement, you can store 8000 chars in varchar. The
unicode nvarchar will only store 4000.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>
Dynamic SQL statements in a stored procedure
I have a small problem writing a stored procedure in a SQL Server 2000 database.
I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server dont generate a resulting SQL-statement.
Example:
CREATE PROCEDURE spDynStatement
AS
DECLARE @.sPartOfSQLStatement NVARCHAR(100)
-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .
-- As substitute I insert the string expression
SET @.sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''
-- SELECT @.sPartOfSQLStatement results in: '1' , '1.5'
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @.sPartOfSQLStatement ) -- does not work
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO
Thankfull for ideas on how to solve my problem,
PeterTry this
DECLARE @.sSQLStatement VARCHAR(4000)
SET @.sSQLStatement = 'SELECT * FROM BBNOrganization WHERE OrgStructureID IN(' + '''1''' + ', ' + '''1.5''' + ')'
EXEC @.sSQLStatement
Friday, February 17, 2012
dynamic sql server statements
can setup dynamic sql statments.
Basically I am going to have a sql server 2000 control table where users
can select via a web page, what columns they want to see with their own
unique (personal) sql server 2000. The control table will be setup to show
what columns
users want to display on their own individual sql server 2000 tables.
Thus basically if a user selects that they want to create a table with
columns #1, #9 and #16 from an option control table, I am wondering if there
is a way to do is a way to set this up with t-sql statements?
Thanks!There is, but there is certainly no magic way to do it. Are you talking
about loads of data? If the volume is relatively low, then I would just use
a regular proc and toss the results the user doesn't want to see.
Otherwise I would save in your settings the string value of the columns and
execute something like:
set @.query = 'select ' + @.columnList + ' from table'
And use sp_executeSQL to execute it. I expect you have a where clause too,
so you would add that.
You could then have a table like (you suggest it too):
create table userScreenPref
(
userId sysname,
screenName varchar(20),
columnList varchar(2000)
constraint PKuserScreenPref primary key (userId, columnList)
) --not exactly tested, but you get the point
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in
message news:603A78FD-D327-4263-8991-4340E6A5F9A5@.microsoft.com...
> In stright t-sql or sql server 2000 stored procdures, I was wondering if
> you
> can setup dynamic sql statments.
> Basically I am going to have a sql server 2000 control table where users
> can select via a web page, what columns they want to see with their own
> unique (personal) sql server 2000. The control table will be setup to show
> what columns
> users want to display on their own individual sql server 2000 tables.
> Thus basically if a user selects that they want to create a table with
> columns #1, #9 and #16 from an option control table, I am wondering if
> there
> is a way to do is a way to set this up with t-sql statements?
> Thanks!
Dynamic SQL reading statements from table
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.On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:
Quote:
Originally Posted by
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.|||On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:
Quote:
Originally Posted by
On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:
>
>
>
>
>
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
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...)
>
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql
>
Quote:
Originally Posted by
I get: Incorrect syntax near 'sp_executesql'.
>
Quote:
Originally Posted by
If I run
>
Quote:
Originally Posted by
sp_executesql 'SELECT foo FROM foostable'
>
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
sp_executesql N'SELECT foo FROM foostable'
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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.|||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:
Quote:
Originally Posted by
On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:
>
>
>
Quote:
Originally Posted by
On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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...)
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
Quote:
Originally Posted by
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql
>
Quote:
Originally Posted by
Quote:
Originally Posted by
I get: Incorrect syntax near 'sp_executesql'.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
If I run
>
Quote:
Originally Posted by
Quote:
Originally Posted by
sp_executesql 'SELECT foo FROM foostable'
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
Quote:
Originally Posted by
sp_executesql N'SELECT foo FROM foostable'
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
This is SQL Server 2005 SP2. Thanks in advance.
>
Quote:
Originally Posted by
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 -
>
Quote:
Originally Posted by
- 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.|||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|||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:
Quote:
Originally Posted by
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
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||manstein (jkelly.admin@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||On May 1, 6:14 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
manstein(jkelly.ad...@.gmail.com) writes:
Quote:
Originally Posted by
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.|||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:
Quote:
Originally Posted by
downwitch (downwi...@.gmail.com) writes:
Quote:
Originally Posted by
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
----
>
Quote:
Originally Posted by
So running
>
Quote:
Originally Posted by
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql
>
Quote:
Originally Posted by
I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
Quote:
Originally Posted by
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|||manstein (jkelly.admin@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx