Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

End If

Larry Pope

Dynamically select column

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+'='+ @.VoteID + ' WHERE (PlayerID = '+ @.PlayerID +')'
EXEC(@.SQLStatement)
GO

I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int

The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
UPDATE
 myTable
SET
 Boot3 = @.VoteID
WHERE
 PlayerID = @.PlayerID
GO

Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!Try this:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber varchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+ ' = ' + CAST(@.VoteID as VARCHAR(10)) + ' WHERE (PlayerID = '+ CAST(@.PlayerID as VARCHAR(10)) +')'
EXEC(@.SQLStatement)
GO

Casting the integers to varchars so they can be concatenated into the larger string.

Hope this helps,
John|||John:

Brilliant! Thanks; works beautifully.

JP

Dynamically creating variable names

Ok, here it goes. I'll try and explain my situation as best as I can.
I would like to dynamically create variable names. We store our data in
monthly partitioned tables.......table_200501, table_200502...
I am creating a view that is a union of all of the partitioned tables. So
in order to do this I am creating a cursor that selects all tables from
information_schema where table_name like 'table_%'
Then once I have the table name I am creating a varchar that does my select
from the table that is passed into the cursor variable. In the end I will
have a varchar that looks like this
select....................from table_200501
union all
select...................from table_200502
Over time the varchar that I have created will run out of space since it can
only hold 8000 characters. So once the size of the varchar gets near 8000 I
would like to create a new variable named sqlstringn.........with n being
the next number available.
So just for sample I tried doing this but it wont work
declare @.counter int
set @.counter = 5
declare @.SQLString + @.counter as varchar(8000)
Obviously this doesn't work, but is there any other way to dynamically
create variable names?
Any help or suggestions are very much appreciated.
ThanksI did that once (dynamically creating variables), and it required creating
dynamic SQL within dynamic SQL.
You probably don't want that in a production application.
What you can do is to allocate, say three, variables and concatenate them on
execution. (ie: Execute (@.Var1 + @.Var2 + @.Var3) I know you are doing this
in a cursor, but the techinque should be the same.
I don't mean to stray, but is it possible to modify your design from
horizontal to vertical so you don't have to deal with so many tables?
"Andy" wrote:

> Ok, here it goes. I'll try and explain my situation as best as I can.
> I would like to dynamically create variable names. We store our data in
> monthly partitioned tables.......table_200501, table_200502...
> I am creating a view that is a union of all of the partitioned tables. So
> in order to do this I am creating a cursor that selects all tables from
> information_schema where table_name like 'table_%'
> Then once I have the table name I am creating a varchar that does my selec
t
> from the table that is passed into the cursor variable. In the end I will
> have a varchar that looks like this
> select....................from table_200501
> union all
> select...................from table_200502
> Over time the varchar that I have created will run out of space since it c
an
> only hold 8000 characters. So once the size of the varchar gets near 8000
I
> would like to create a new variable named sqlstringn.........with n bei
ng
> the next number available.
> So just for sample I tried doing this but it wont work
> declare @.counter int
> set @.counter = 5
> declare @.SQLString + @.counter as varchar(8000)
> Obviously this doesn't work, but is there any other way to dynamically
> create variable names?
> Any help or suggestions are very much appreciated.
> Thanks
>

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

Dynamically configure CacheType?

I'm looking for a way to dynamically set the CacheType property of a Lookup transformation via a configuration variable. Is this possible? The CacheType does not appear to be a selectable property when choosing which properties to export to a configuration file. Can this property be manipulated through a script task inside the package?

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

|||

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.|||

Phil Brammer wrote:

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Took the words right out of my mouth Phil In case anyone is interested, expressions on data-flow components was virtually the very last feature that was added to the product prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.

In answer to the OP, CacheType of the Lookup component does not allow its value to be set by an expression, so you can't do what you want I'm afraid. If you want this behaviour changing then go to http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

That's because not all components allow you to set their properties using expressions. if they do, then those properties will show up (off the top of my head I know that the SQLCommand property of the Datareader Source component can be set this way so take a look at that).

Ravi G wrote:

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

The path syntax allows for it - as you shall see.

Sounds like good blog material

-Jamie

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]|||

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

|||

Ravi G wrote:

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

That's the name of the property. Don't worry too much about why its called that, just know that it is a custom property that can be affected with an expression. More here: http://msdn2.microsoft.com/en-us/library/ms141069.aspx

Admittedly this takes a bit of getting your head around. Basically you can set an expression using an expression. Or to put it another way, the result of your expression is, in itself, another expression.

-Jamie

|||

I've found this useful reference for all the custom properties of the stock components.

http://msdn2.microsoft.com/en-us/ms136014.aspx

-Jamie

dynamicall execute SP without dynamic sql

How would you go about dynamically executing a stored proc dependent on
a variable? I cannot use dynamic sql.Why do you want to do without dynamic sql?

Madhivanan|||You can use a parameter instead of a proc name (see EXECUTE in Books
Online):

exec @.p

But this is problematic if different procedures may require different
parameters. Or if the number of procedures is relatively small, then
you could just use IF ... ELSE ... to conditionally execute a proc.

Simon|||Use IF statements

IF @.var = 'Proc1'
EXEC Proc1
IF @.var = 'Proc2'
EXEC Proc2
...

If you create a new proc you just need to add it to the list. You could
even generate the list automatically from the info schema ROUTINES
view.

--
David Portas
SQL Server MVP
--|||That sounds like a interesting solution that would probably work. How
would you generate that list and then incorporate it into the if
statements. Thanks!|||Just query against the routine_name column and then cut and paste into
your SP. You still can't expect to automate that process entirely
without using dynamic SQL. Does that matter? Assuming you have adequate
change control procedures in place it shouldn't be a problem.

--
David Portas
SQL Server MVP
--

Dynamic WHERE statement

If I pass a variable to @.Cost_Center that is 'SMS', 'SMP' OR 'ALL'
'ALL' will return records that have either 'SMS' or 'SMP' in them.
Now I am using an if statement that has the entire SQL statement in the body
of the
conditional.
I tried this
Where
IF (@.Cost_Center = 'All')
Begin
code...
and i.cost_center in ('SMS','SMP')
End
ELSE
Begin
code...
and i.cost_center = @.Cost_Center
End
Thanks
JimYou might want to check out the following article for some ideas:
http://www.sommarskog.se/dyn-search.html
Anith|||Thank you
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23glqcpZBFHA.4072@.TK2MSFTNGP10.phx.gbl...
> You might want to check out the following article for some ideas:
> http://www.sommarskog.se/dyn-search.html
> --
> Anith
>|||SELECT ...
FROM Foobar
WHERE cost_center = @.my_cost_center
OR cost_center
IN (CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMP' ELSE '' END,
CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMS' ELSE '' END));

Dynamic Update with a sub select

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set@.SQL = @.SQL +'@.XDeliveryDate'

Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate

Any Help appreciated

Mr Tumnus wrote:

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

Any Help appreciated

I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).

|||

No change, I still get the same error

I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).

I am fairly new to SQL and would appreciate any advice.

|||

Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.

Another option is to split the operation into a batch of 2 steps like:

Code Snippet

SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'
SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'

For your @.SQL setting code.

|||Hi,

try this:

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.TABLENAME VARCHAR(100)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.TableName = 'SomeTable'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '

Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

PRINT @.SQL

UPDATE dbo.[SomeTable]

SET

UID =@.XCustomerID ,

SlotClosed=1

where WorkingSheduleID =

(

Select Max (WorkingSheduleID) From dbo.[SomeTable]

Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))

)

Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Try to change the query as follows :

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'

set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

SELECT @.XWorkingSheduleID

Dynamic update of a column

Hi,
In the code below, I am trying to update a column to '0'. But, I dont know
which column to update until my variable @. Month fetches the column name.
DECLARE @.Month Varchar(20)
SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
WHERE MD.lMonthId = DTS.JobMonth
and DTS.JobId = 2
PRINT @.Month
update FTEActualForecast set @.Month = 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
Now, the problem is
1. The column I am trying to update to '0'is determined dynamically, so I
have used a variable name called @.Month
2. But in the UPDATE statement when I use the keyword SET it sets the
variable and not the column value to ‘zero’
Is there some workaround or better way of doing this ?
Please advise.
Cheers,
Hemil.Try
Exec ('update FTEActualForecast set ' + @.Month + '= 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
"Hemil" wrote:

> Hi,
> In the code below, I am trying to update a column to '0'. But, I dont kno
w
> which column to update until my variable @. Month fetches the column name.
> DECLARE @.Month Varchar(20)
> SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
> WHERE MD.lMonthId = DTS.JobMonth
> and DTS.JobId = 2
> PRINT @.Month
> update FTEActualForecast set @.Month = 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> Now, the problem is
> 1. The column I am trying to update to '0'is determined dynamically, so I
> have used a variable name called @.Month
> 2. But in the UPDATE statement when I use the keyword SET it sets the
> variable and not the column value to ‘zero’
>
> Is there some workaround or better way of doing this ?
> Please advise.
> Cheers,
> Hemil.
>|||Thanks a lot, Tom.
It does the job.
Thanks again,
Hemil.
"Tom" wrote:
> Try
> Exec ('update FTEActualForecast set ' + @.Month + '= 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
>
> "Hemil" wrote:
>|||Please include DDL with questions like this so that we don't have to guess
what your table looks like.
Why separate columns for each month? Instead, add a DATETIME column to
record the month then you can specify which row to update with a WHERE
clause.
David Portas
SQL Server MVP
--

Dynamic Table Referencing, SP

Hey,

I'm looking at dynamically creating tables, and referencing tables, using a variable name.

An example:

A 'user' signs up. They enter their username, password, and tableName.

a SP grabs this info and does the following:

adds the usrname, pass, tableName, to a table named Users.

It then creates a table (all table details are pre-entered in a sp), but the table name is @.table (references the tableName).

I know how to send variables to a SP, and I can manipulate them, but I'm having trouble working out how to use them as a tablename reference.

The other situation (which I imagine would use the same syntax) is in a select (update etc as well...) statement.

Create StoredProcedure select_table
@.tableName <datatype?!?>
AS
Select *
From @.tableName
Go

How can I make this work? Is it possible? or do I need to generate the query some other way. If so, how can I do it? lol.

Thanks a lot for your help and any direction you can give me
-AshleighI'd do it using the sp_executesql stored proc.

Build your sql statement in your initial stored proc then build a string/varchar variable with the sql statement you want to run and then run that sql statement that you built using sp_executesql.|||Fair warning... dynamically creating a whole bunch of tables in a database in order to support a whole bunch of users is probably not the best way to handle such a requirement. Generally and broadly speaking, "lots and lotsa tables" is usually not a Desirable Thing.

Views are better.|||Yeah, I'd have to agree with that statement...

Unfortunately you can't always convince the "management" that this is the case. ;)

Not until they have had to maintain it anyways... :)|||Hey,

the example is just a simple example for what I hope to achieve.

Using dynamically created tables is the best way to manage the system I am using.

Thx though,

-Quote
I'd do it using the sp_executesql stored proc.

Build your sql statement in your initial stored proc then build a string/varchar variable with the sql statement you want to run and then run that sql statement that you built using sp_executesql.

Can you give me an example of how to do this? I have no idea (or a resource to learn it from?)

For the example, perhaps jsut for the simple case of:

Create StoredProcedure select_table
@.tableName <datatype?!?>
AS
Select *
From @.tableName
Go

Thanks
-Ashleigh|||an example,... sure...

CREATE PROCEDURE select_table
@.tableName varchar(255)
AS

Declare @.strSQL nvarchar(1000)

Select @.strSQL = 'Select * From ' + @.tableName

exec sp_executesql @.Statement = @.strSQL
GO

then your call to the stored proc is something like...

select_table @.tableName='File_Status'|||Fantastic,

That's not hard at all. I thought it was going to be something really complex and scary.

Thanks a lot for you help. Saved me hours of reading random search pages and textbooks.

-Ashleigh|||No worries, happy to be able to help out.|||"Using dynamically created tables is the best way to manage the system I am using."

Ashleigh, no offense, but never in over ten years of database design have I seen an instance where dynamically creating tables is the best way to manage a system. On the flip-side, I've had to fix or write complex code-arounds for many databases that were implemented this way. The reason you were having difficulty finding out how to do what you plan to do is, basically, because SQL Server and relational databases in general are not designed to do things the way you are planning to do them.

See if you can't add the scalability you need through one additional table or even one additional field in an existing table. It could save you hundreds of lines of code and many hours spent in performance tuning.

blindman|||Once again I'd agree with the above statement...

You have probably missed something in your data model if you need to build a database this way.

The only time I could ever see you doing something like this is if your boss (who really shouldn't be involved in the design process) tells you that this is the way he wants it built.

I assume this post links to your other post regarding the use of stored procedures and the data you have talked about in that post... if so you should be able to do everything you have talked about in the other post without having to create these tables.|||Hey,

ok well, here's what I'm doing.

I'm creating a site which creates 'pools'. These pools are created by a 'manager' using a 'managementKey'.

When the manager registers with his key, I register his information including the name of his 'pool'.

A table is then made using the 'pool' name. This table holds all the user information. MemberID, Password, and their details.

The reason we elected to do it this way was, there will be lots of instances where we will be sorting through the table, displaying results etc. and we are looking at a possible 500K-1 million users. That's a lot of data in one table.

To log in, users enter the 'pool' name, 'memberID' and 'password'. It will then look for the memberID and password in the selected field.

Another table is also created for each 'pool' named

'pool'Results. This holds information about each members settings, results, etc. This table will hold around 5 entries a week from each member of that 'pool'.

On paper, this method is much neater and easier to manage than 4-5 massivetables.

If you can think of a better method for this, I'm all ears. But from what I can see (and I'm happy to admit I'm a novice sql man) this is the best way.

-Ashleigh|||I'd stick with the 4-5 tables to be honest...

1 mil records is nothing if you have a good table structure and some decent indexes to aid your searching etc.

Think about it this way,.. if you decide to change the structure of these "pool" tables how are you going to do that if you have 200+ tables to change?

*shudder*|||hm, ok...

well I'll discuss it with the people upstairs, lol.

-Ashleigh|||Originally posted by Ashleigh
hm, ok...

well I'll discuss it with the people upstairs, lol.

-Ashleigh

I've had a chat with the man (lol).

Basically, what we concluded was. Queries won't be searching through 1 million rows. If I use 4-5 tables only, finding results (linking the tables up) will be querying through around

1million x 20 x 8, so um, 160 million rows. That's for each user, probably twice a week. That's a lot of row searching. So by dividing it all up, we thought it would make it run a lot smoother.

In terms of db updating. The db's will all be based on 3 templates. So can you use a script (in ms sql) to update them? reguardless, I don't see any reason why we'd update. Of course, those are famous last words, LOL...

-Ashleigh|||Its still better to go with 4-5 tables. You'll add one additional field to the tables to indicate the manager "pool", and then index this ID. With an index, the optimizer will be able to quickly locate the entries for a particular manager and then search through just those entries, much as if they were in a separate table.

On the plus side, you won't have to write all your code as dynamic. Dynamic SQL is difficult to debug and less efficient than direct SQL.

If this is important to you and your manager, get a database consultant to review your design. Then, if something doesn't work you have someone else to blame. This is what consultants are for. ;)

blindman|||Hey,

yeah, might be worth considering. I'm starting to feel like I could use a good consulting (lol).

On the subject of indexing tables. I'm not 100% sure what this means. Can you point me to something that will explain it?

Thanks
-Ashleigh|||http://www.sqlteam.com/store.asp|||Like blindman said, the indexes will reduce your processing/quering so you are only dealing with what you need to...

Unfortunately I haven't really seen anything online that describes indexes and how they work etc...

Perhaps another post requesting such might attract the attention of someone who has. ;)|||Did you look in BOL?

Also Ken Hendersons books are almost a must have...|||Originally posted by Brett Kaiser
Did you look in BOL?

Also Ken Hendersons books are almost a must have...

No, I havn't (either of those resources).

I'll look into them though, thanks

-Ashleigh|||Just a fair warning - if you do not know what "Indexing tables" means then you need to do your homework a little better. From what I have read on the database requirements, this design is not very complicated and might require a couple of lookup tables and a details table.

Kaiser gave you some good resources.

Good luck.|||mm. thx rnealejr, indexing tables is something I have on my list of must find out more about before I implement this database. Unfortunately, that list started out very long. Having never done database design before, I just got shafted this job so... lots of 'homework' and no real time to do it, lol. Such is life I guess.

Yea, the database itself isn't very complicated. I was just concerned about it's size. Evidently though, it's size is not a concern as long as I do good indexing.

I have since got a few good resources on indexing, and I believe I have that all under control.

Thanks to everyone for their input on good resources etc.

rnealejr i just have a small question relating to what you wrote. lookup tables and details tables.

What exactly do you mean by this? When I read it, I picture a table with just ID and names, to make searching for the ID num faster. is that what you mean?

Also, I have a very n00b question about indexes that I can't find an answer to. Do I need to change my searching at all? to use them? or is it automatic? ie I'm indexing usrname, so if I say

select *
from member
where name = @.name

that will use the name index on the table?

Also, I read something about making indexes have multi values. IE I have a db with these columns (example)

userID, username, password, groupName

if I make a unique index of username, groupName (ie both in the same index)

do I use that index simply by saying

select *
from member
where username=@.username AND groupName = @.groupName

(note, these are examples, don't worry about why I'm doing these procedures, because I'm not, lol).

-Ashleigh|||My battery is running out - so I have to make this fast - but I believe the answer to most of your questions is yes (or at least you are on the right track). When you create an index - the query optimizer picks the best path to retrieve the data. So if an index exists and the optimizer sees that it is faster with the index versus and sequential scan of the table - it will pick the index. Try it out - open query analyzer and use the "Show Execution Plan".

To answer your question about table design - yes - you want to normalize your data (check out the 1st - 3rd normal forms for reference).|||use of the indexes should happen automagically...

you can view the execution plan for any sql statement in the query analyzer to see what indexes it will use to perform the query.

if you use a composite index (eg. an index that uses more then one field) then the query/database can use part or all of the index to run it's queries (from what I remember - someone please confirm this).

best shown by an example I think eg.

your index has username and group...

if you search just using username it will use that index...

if you search just using group it won't

if you search using both it will.

HTH|||Fantastic,

there is hope for me yet, lol.

thanks a lot rnealejr, and rockslide (yet again, lol).

I almost feel like I know stuff now, woot.

Nah, this is a great resource here. I think I better spend a few hours on a flash and asp.net forum tonight. Pay back my karma debt, lol.

Thanks again.

(still trying to convince them to buy me some of those books Kaiser pointed out, lol).

BOL sounds really good as well. I'm going to put in some time trying to track that down tonight too.

-Ashleigh|||bol should be on the disks you used to install ms sql (it's one of the components).|||Originally posted by rokslide
bol should be on the disks you used to install ms sql (it's one of the components).

lol, ah ok sweet. Yea I use that a bit, but perhaps not as much as I should.

thx again,
lol

Dynamic Table Name creation...

Hi there,

I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called

@.TABLENAME VARCHAR(16)

and when i say SELECT * FROM @.TABLENAME

it is giving me an error and I think I cannot declare @.TABLENAME as a table variable because I do not want to create a temp table of sorts.

I hope I am clear.

thanks,

Murthy here

can you post the error generated by SQL server

and the code for generation of tables names , I am curious to see how we can achieve it :)

|||

Are you looking for something like this:

Declare @.tempTable table (
TableName varchar(16)
)

Insert into @.tempTable
Select 'Apples'
union
Select 'Pears'

Select * from @.tempTable

Results:
TableName
------
Apples
Pears

|||

Hi there,

I am posting a portion of my code:

SET @.TABLENAME = 'FISCURRENT.dbo.DC67TRANSCYR' + '0' + CAST((CAST((SUBSTRING(@.FISCALYEAR1,3,2)) AS INT) - 1) AS VARCHAR) + SUBSTRING(@.FISCALYEAR1,3,2)

SELECT DESCRIPTION, TRANSTYPE , BE, CATEGORY, FID , OCA, '67' + ORGL2L5 , EO, AMOUNT, GL, TRANSDATE, MACHINEDATE, 'N' AS BMS, THEYEAR
FROM @.TABLENAME
WHERE ((TRANSTYPE = '20') AND (GL = '92200')) OR
((TRANSTYPE = '21') AND (GL IN ('91100', '92100'))) OR
((TRANSTYPE = '22') AND (GL IN ('13100', '12200'))) AND (BE IN (SELECT DISTINCT BE FROM REF_BUDGETENTITY WHERE FISCALYEAR = @.FISCALYEAR))
And the error message is :

Must declare the table variable "@.TABLENAME".

thanks,

Murthy here

|||

Hi there,

I do not have to create any temp tables. Actually the tables already exist with sql server. based on 1 field from 1 table I have to select data from 1 or more different tables.

The select statement has to different for different tables so I am generating the table names which already exist on the fly.

Hope I am clear.

thanks,

Murthy here

|||

I think you will need to use sp_execsql to execute the dynamic sql you build that way

Keep in mind that many people discourage the use of dynamic sql because of security and performance

1. SQL injection attacks

2. Having to compile the statement every time

3. YOu need to grant permissions on the underlying tables, not on the proc

|||

If you are trying to do something like this:

Declare @.table varchar(20)
set @.table = Northwind.dbo.Products

Select * from @.table

You can't. It's looking for a table afterfromso @.table would have to be a table variable.

To do what you need, asdbland07666 mentioned, you need to use dynamic sql. Caveat Emptor. Here is a link that might help you:

http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html

Dynamic Stored Procedures uses vars only

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

Writing dynamic T-SQL doesn't strike me as being relevant to SSIS so I'm a little confused. Perhaps you could elaborate.

By the way, best practice stipulates that you shouldn't name your sprocs "sp_*".

-Jamie

Dynamic statement in variable - parseerror

I am trying to use this statement in a variable, including another variable:

"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @.[User::LastTimestamp]

But the variable value insists on giving me this error:

The expression for variable "VariableName" failed evaluation. There was an error in the expression.

I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.

Is it because the expression is part string and part integer? If so, how is that avoided?

Thanks in advance

Ohh, also, I have tried with (DT_STR) @.[User::LastTimestamp], but that doesnt work either|||

Casting to a string should fix the problem.

This works for me-

"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + (DT_WSTR,10)@.[User::LastTimestamp]

dynamic SQL variable with Output ?

I know this has been dealt with a lot, but I would still really
appreciate help. Thanks.

I am trying to transform this table
YY--ID-Code-R1-R2-R3-R4...R40
2004-1-101--1--2-3-4
2004-2-101--2--3-4-2
...
2005-99-103-4-3-2-1

Into a table where the new columns are the count for 4-3-2-1 for every
distinct code in the first table based on year. I will get the year
from the user-end(Access). I will then create my report based on the
info in the new table. Here's what I've tried so far (only for 1st
column):

CREATE PROCEDURE comptabilisationDYN
@.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
AS

DECLARE @.SQLStatement varchar(8000)
DECLARE @.TotalNum4 int
DECLARE @.TotalNum3 int
DECLARE @.TotalNum2 int
DECLARE @.TotalNum1 int

SELECT SQLStatement = 'SELECT COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

INSERT INTO Comptabilisation(Total4) VALUES (@.TotalNum4)
GOPatrik (patrik.maheux@.umontreal.ca) writes:
> CREATE PROCEDURE comptabilisationDYN
> @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> AS
> DECLARE @.SQLStatement varchar(8000)
> DECLARE @.TotalNum4 int
> DECLARE @.TotalNum3 int
> DECLARE @.TotalNum2 int
> DECLARE @.TotalNum1 int
> SELECT SQLStatement = 'SELECT COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
> OUTPUT

You need:

SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

You also need to add @.year to the parameter list.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank You, but i have tried your code before and again and it still
doesn't work. I call the procedure and it seems to work but it doesn't
write anything in the table. It doesn't recognize the ouput variable in
the INSERT line.

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > CREATE PROCEDURE comptabilisationDYN
> > @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> > AS
> > DECLARE @.SQLStatement varchar(8000)
> > DECLARE @.TotalNum4 int
> > DECLARE @.TotalNum3 int
> > DECLARE @.TotalNum2 int
> > DECLARE @.TotalNum1 int
> > SELECT SQLStatement = 'SELECT COUNT(*) FROM
> > dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> > EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> > OUTPUT
> You need:
> SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> OUTPUT
> You also need to add @.year to the parameter list.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Your personal narratives and pseudo-code are useless.|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Thank You, but i have tried your code before and again and it still
> doesn't work. I call the procedure and it seems to work but it doesn't
> write anything in the table. It doesn't recognize the ouput variable in
> the INSERT line.

Could you post the exact code you have now. Looking back on your post,
I see now that there will be a syntax error from the dynamic SQL.

Judging from the code you posted, you should always get a row insered,
even if only a NULL value.

I assume that "seems to work" does not mean that you don't get any
error messages. But maybe you should try running the procedure from
Query Analyzer, in case you have poor error handling in your Access
code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thank You, after reading Mr.Sommarskog article more profoundly again
over the week-end, I pick-up my mistake. I wasn't using the parameter
list, now it works fine. Here's my final code.

CREATE PROCEDURE ComptabilisationDYN
@.numsaisie int,
@.code int,
@.colonne varchar(3)
AS

DECLARE @.TotalNum4 int
DECLARE @.SQL1 nvarchar(1000)
DECLARE @.paramlist nvarchar(1000)

SELECT @.SQL1 ='SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE ' +@.colonne + '=''4'''

SELECT @.paramlist = '@.TotalNum4 int OUTPUT'
EXEC sp_executesql @.SQL1, @.paramlist, @.TotalNum4 OUTPUT

INSERT INTO comptabilisation(Num_Saisie, code, Total4) VALUES
(@.numsaisie,@.code, @.TotalNum4)
GO|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Dynamic SQL to populate a variable

Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.

declare @.TableName sysname

set @.TableName = 'Customers'

delcare @.Output bigint

declare @.SQL varchar(max)

set @.SQL = 'select top 1 RowID from ' + @.TableName

select @.Output =

EXEC (@.SQL)

create function udf_GetDatabaseFingerPrint(

@.DBID bigint

)

begin

returns bigint

as

declare @.dbname sysname

, @.iBig bigint

, @.tSQL varchar(2000)

select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID

set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'

set @.iBig = exec(@.tSQL)

return @.iBig

end

Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had

declare @.objectId int,

exec sp_executeSQL

N'select @.objectId = max(object_id) from sys.objects',

N'@.objectId int output', @.objectId=@.objectId output

select @.objectId

Dynamic SQL task

I want to make my SQL task dynamic i.e I want to accept the Connection
String through variable i pass to the package I dont intend on using a
configuration file.How do i go about doing this and what all variables do i pass
to do this.
Thanks
Clayton
P.S How can we assign Connections dynamically to a OLEDB Source by passing values from a list of variables.

Running the package using dtexec you can update the value of the connection string by using the Set command. The Books Online topic dtexec utility contains information about using the set option. You cannot update property expression by using the set option.

Another option is to use the Script task to access the connection strings which are stored outside the package. For example, a database table. The script could retrieve the string and update the connection string.

Marianne
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic SQL in SSIS with Oracle

Hi ,

1.Dynamic Sql

-

My source table is Oracle .we want to have dynamic query the following steps we have done.

a.Created a new variable called StrSQL & ProductID variable contains values for where clause.
b.Set EvaluateAsExpression=TRUE
c.Set Expression=""select * from prod where product_id = " + @.[ProductID]
d.OLE DB Source component, opening up the editor
e.Set Data Access Mode="SQL Command from variable"
f.Set VariableName = "StrSQL"

But i am getting the following error

Error at DataFlow Task[OLEDB Source[1]]:An OLEDB error has occured, Error code: 0x80040e14.
AN OLE DB records is available . Source "Microsoft OLEDB Provider for Oracle" Hresult: 0x80040e14
Description : "ORA-00936:missing expression

What could be the problme.how about the support of dynamic query (Oracle) in ssis?

2.Clarification in Lookup.

--

How to Pass Parameters to Lookup. (Dynamic sql in Lookup)

example

select * from mastertable where reportdate = ?

here also my source is oracle table

Thanks

Jegan

The fact that the source is Oracle is irrelevant as far as the dynamic SQL is concerned. You should take the result of your expression and try and execute it against the source in yur query tool of choice (i.e. outside of SSIS). Verify that the query is correct.

-Jamie

|||

In c. of your question; you have a duplicate quotes

c.Set Expression=""select * from prod where product_id = " + @.[ProductID]

It may be that the problem or is just a typo in your post?

Other than that I don't see any other reason for the error; the steps you described look right.

Regarding the lookup question; I will not recommend that approach. If you have a dynamic query in a lookup it would mean to perform the lookup query for every row in your data flow impacting performance. However, I think you could do it by going to the advanced tab of the lookup transform and editing the query there.

Alternatively, you could include reportdate as a column available to the input of your lookup; then you just use it as a part of the join in the lookup component. Or may be you can create a view at run time with that query and then use that view in the lookup.

Those are just a couple of ideas; I hope this takes you further with a solution to your issues

Rafael Salas

|||

Rafael Salas,

Thanks for the Lookup solution.

Regarding dynamic sql yes its a typo error in my post. the same steps are working when we use a sql server connectivity.but for oracle i am not able to see the available columns in the column tab .what could be the problem any property need to be set?.

Thanks

Jegan.T

Dynamic SQL in a CTE?

Hi all,

Is it possible to execute dynamic SQL in a CTE? that is create the dynamic SQL, stick it into a variable 'strCriteria' and then execute 'strCriteria' within the CTE?

WITH UPCTE

AS

(

this doesn't work
Exec sp_sqlexec @.strCriteria

)

SELECT DISTINCT Comp_Name FROM UPCTE;

Meltdown:

Will something like this get you through as a workaround?

declare @.baseCTEDefinition varchar (500)
declare @.theSelectStatement varchar (500)

set @.baseCTEDefinition = 'with myCTE as ( select 1 as singlet ) '
set @.theSelectStatement = 'select * from myCTE '

exec ( baseCTEDefinition + @.theSelectStatement )

Dave

|||Can you please explain why you need to use dynamic SQL within CTE? CTE is similar to a view, it is a declarative construct, the definition of the CTE is expanded in all references in the query, compiled, optimized and executed. So you cannot execute dynamic SQL or call SPs directly from CTE. If you just want to execute a SQL statement dynamically then use EXEC or sp_executesql. Also, sp_sqlexec has been deprecated for a while and it will be removed soon from the product. So you should remove those from your code also. Lastly, why do you need to use dynamic SQL and what are you trying to solve that requires use of dynamic SQL. If you can explain your problem it will be helpful to suggest easier solutions.

Dynamic SQL error

Hello, Im trying to do dynamic SQL but get the following error with the SQL below

Must Declare the variable '@.TopRange'

can someone please help ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

Set @.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2))
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= @.TopRange AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= @.BottomRange'

EXEC(@.SQL)

In your code, you have added the variable into the string. When the string is executed, the variable does not exist.

I think that you want to add the variable's value to the string, so you would need to set it outside the quotes and concatenate the variable value to the string.

Something like this:

Code Snippet

DECLARE

@.TopRange varchar(8),
@.BottomRange varchar(8),

@.SQL nvarchar(1000)

SELECT

@.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2)),
@.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2)),

@.SQL = 'SELECT ' +

'tc.idStatus, ' +

'tc.idCustomer, ' +

'tc.DateSigned ' +
'FROM dbo.tblCustomer tc' +
'WHERE datediff( day, tc.DateSigned, getdate()) >= ' + @.TopRange +

' AND datediff( day, tc.DateSigned, getdate()) <= ' + @.BottomRange

EXECUTE ( @.SQL )

Note that I changed the datatypes of the Top/Bottom Range variables -that is so they will concatenate with the string without having to be cast or converted.

Also, using the datediff() function this way in the criteria of the query will negate any possibility of using indexing. The query will require at best an index scan, and at worst, an entire table scan.