Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >

Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...

Easy Question on inequality

:rolleyes:

In T-SQL I could use <> or != to do an inequality comparison.

However which method would be most suited?

I and my co-workers use C# so I think I'll use !=. :cool:

This fits in with our dev language pilosophy.

Are there any reasons why I shouldn't?

Opinions welcome.

Shaun McGuile
"...you think the worms of Dune are big, wait till you see the sparrows!"Afaik there is absolutely no difference. As you, i strongly preffer using !=|||Old style syntax such as "!=" is supported by MSSQL, but not encouraged.
The familiar "<>" is more universally recognized.|||I find it strange that its old style

I would have said that <> is BASIC programming style

and

!= is more C# and Javascript style.

(from my perspective more modern).

Regards

(3 Card Brag - Rules - you can't see a blindman!). :cool:|||I think what the BM is saying is that one is MS specific and the other is in the ANSI-92 specification. If you ever need to port your app to another DB platform the less MS specific code you have the better off you will be.

who cares what is more in line with app code?

BTW, you spell sean the wrong way.|||He is using the old Ansii-standard "Shaun", while you are using the MS-specific "Sean".|||BTW, you spell sean the wrong way.

Hmm try telling that to my Welsh co-worker Sion. :D|||Or my sister-in-law, Cion

Dynamically Modify the Datepart Argument of DATEADD function in T-SQL

Is it possible to dynamically modify the Datepart argument of the DATEADD function in T-SQL 2005.

I am trying to do something like this but it's not happy with parameter 1:

DECLARE @.pp NCHAR(3)
SET @.pp = 'day'
SELECT dateadd(@.pp,3,'2007-07-14 12:00:00')

Any Ideas will be much appreciated.

Quote:

Originally Posted by kevinSQL

Is it possible to dynamically modify the Datepart argument of the DATEADD function in T-SQL 2005.

I am trying to do something like this but it's not happy with parameter 1:

DECLARE @.pp NCHAR(3)
SET @.pp = 'day'
SELECT dateadd(@.pp,3,'2007-07-14 12:00:00')

Any Ideas will be much appreciated.

put the entire select command to a variable

Dynamically Create columns

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
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
> --
>

Dynamic T-SQL steatment

How can i build a T-SQL statement dynamically?

Vivek S

Do you mean within SSIS? You should use an expression: http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=

If you mean to be used in (e.g.) a sproc then you're on the wrong forum. Try the T-SQL forum.

-Jamie

|||i have declared a variable of type string and initialised it with a T-SQL statement. The sql statement size is around 5000 characters. To have some run time modifications, i have used a script task and initialised the SQL to the variable so that it can take the changes at run time. i have used a DataflowTask within which accessing the variable for the SQL. But when i execute the ssis pkg it throws an error at the dft validation level as VS_ISBroken.|||

What type of component is using the SQL statement?

What component is failing?

What is the error?

Please supply more pertinent information otherwise its a bit dificult to be of assistance.

As an aside, you really should consider using an expression rather than a script task to dynamically build your SQL statement.

-Jamie

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>

dynamic table name (was "Please help ! SQL")

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@.cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.


create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'

DECLARE @.cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @.cur_tab_name

WHILE (@.@.FETCH_STATUS =0)
BEGIN
DECLARE @.count integer

select @.count = count(*) from @.cur_tab_name
INSERT INTO temp_tab
(@.cur_tab_name, @.count)

FETCH curREVIEW INTO @.cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEWDynamic SQL must be passed as a string to EXEC:

EXEC ('select @.count = count(*) from ' + @.cur_tab_name)

...but this still will not give you what you want, because the code will EXECute in a different scope than your @.count variable.

Try this:

Declare @.SQLString varchar(500)
Set @.SQLString = 'INSERT INTO temp_tab select ''' + @.cur_tab_name + ''', count(*) from ' + @.cur_tab_name
EXEC (@.SQLString)

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
Rea
Try using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
=========== step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam = MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql server statements

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!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 Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql = ' SELECT
...
FROM
...
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom = ' SELECT
...
FROM
... '
SET @.sqlWhere = ' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters to
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:
> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql => ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom => ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere => ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statement
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere = ' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:
>Hi
>I think you should be looking at what you are trying to achieve and possibly
>split it up, rather than trying to get a statement of over 4000 characters to
>execute. There may be scope to use views and or functions to reduce the size
>a little. As you concatenating your input variables you may still be subject
>to SQL Injection, use of the QUOTENAME function may help.
>John
>> I have created a parameterized statement in a T-SQL procedure that uses
>> sp_executesql such as the following:
>[quoted text clipped - 63 lines]
>> 2. In the Revised statement, specifically the INSERT, can the variables be
>> assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.com|||Hi
It would be the second option.
John
"cbrichards via SQLMonster.com" wrote:
> Thanks John. Given the Revised statement, would I use the QuoteName function
> within the Set Statement:
> SET @.sqlWhere => ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> >Hi
> >
> >I think you should be looking at what you are trying to achieve and possibly
> >split it up, rather than trying to get a statement of over 4000 characters to
> >execute. There may be scope to use views and or functions to reduce the size
> >a little. As you concatenating your input variables you may still be subject
> >to SQL Injection, use of the QUOTENAME function may help.
> >
> >John
> >
> >> I have created a parameterized statement in a T-SQL procedure that uses
> >> sp_executesql such as the following:
> >[quoted text clipped - 63 lines]
> >> 2. In the Revised statement, specifically the INSERT, can the variables be
> >> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql =
' SELECT
..
FROM
..
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom =
' SELECT
..
FROM
.. '
SET @.sqlWhere =
' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
Message posted via http://www.droptable.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters t
o
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:

> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql =
> ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also
am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom =
> ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere =
> ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statemen
t
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>