Running an INSERT statement a @variable without using EXEC

The Code-Piece below does not work because @.Tablename is not an objekt.

=============================================

DECLARE @.Tablename varchar(256)

set @.Tablename = 'MyTable'

insert into @.Tablename default values

=============================================

How can i make it work without using EXEC?

Thanks in advance

Raimund

You have to use dynamic SQL (which is what I presume you mean by using EXEC). I don't think there is any other way.|||If I use scope_identity with an insert-statement by dynamic SQL, scope_identity reurns NULL;|||

If you put the Scope_identity call into the dynamic sql you will be able to capture it to a variable. That variable can then be returned if you use the sp_executesql form of dynamic sql.

|||

You need to call the identity in the dynamic SQL

declare @.tablename varchar(100)

declare @.sql nvarchar(1000)

declare @.i int

set @.tablename = 'mytable'

set @.sql = 'insert into ' + @.tablename + ' default values

set @.i = scope_identity()'

exec sp_executesql @.sql, N'@.i int output', @.i output

select @.i

|||This does mean you can put this code in a UDF|||

Ok. It works fine.

Thanks.

Best Regards

Raimund

Running a LIKE statement when searching for a date field...

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

Where did you find that preferred way to compare dates is LIKE. You might want to use many date functions to compare dates available in 2k and 2k5.

OR if you incist on using LIKE convert "wheninstalled" to format that you specify in LIKE.

|||datetime is stored in database in an internal format. It is not in YYYY-MM-DD or whatsoever format.

>> I have dates in this column ie 2006-02-13 11:30:54.220
This just how Query Analyser represent the date or format the date and time when it return the records.

>> SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
To retireve records for month of Feb 2006,

SELECT * FROM workorderhistory WHERE wheninstalled >= '2006-02-01' and wheninstalled < '2006-03-01'

|||

Here is the text from the sql help file.

It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

Thanks for your help.

|||Thanks for the help... I don't konw why I did not think of doing it that way... Slipped my mind I guess.|||

try this may be helpful for you

SELECT * FROM
WHERE (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) = '3/14/2006')

|||

How can I use a like statement in there. I tried doing

select * from workorderhistory where (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) like '03/%') and this did not work. Basically I want to see all the workorders that were installed in the month of march.

Running a LIKE statement when searching for a date field...

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

Where did you find that preferred way to compare dates is LIKE. You might want to use many date functions to compare dates available in 2k and 2k5.

OR if you incist on using LIKE convert "wheninstalled" to format that you specify in LIKE.

|||datetime is stored in database in an internal format. It is not in YYYY-MM-DD or whatsoever format.

>> I have dates in this column ie 2006-02-13 11:30:54.220
This just how Query Analyser represent the date or format the date and time when it return the records.

>> SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
To retireve records for month of Feb 2006,

SELECT * FROM workorderhistory WHERE wheninstalled >= '2006-02-01' and wheninstalled < '2006-03-01'

|||

Here is the text from the sql help file.

It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

Thanks for your help.

|||Thanks for the help... I don't konw why I did not think of doing it that way... Slipped my mind I guess.|||

try this may be helpful for you

SELECT * FROM
WHERE (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) = '3/14/2006')

|||

How can I use a like statement in there. I tried doing

select * from workorderhistory where (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) like '03/%') and this did not work. Basically I want to see all the workorders that were installed in the month of march.

Running a delete, insert, update SQL statement from a text field

Let's say you have a text field on some application that's used to be part of a SQL select statement like "SELECT " + txtField.Text() + " FROM [Some_Table];"

What if the user entered "(DELETE *)" or some other insert, update, etc. in the text field? Is there any way it could embed the statement and really mess things up in your database?Yes. Google for "SQL injection". And vow never again to build your SQL like that; use bind variables to pass user input to the SQL engine. This also makes the database perform better AND makes your SQL easier to write:

"SELECT ? FROM [Some_Table]"

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

sql

Run SSIS from ASP.net

in sql 2005 sp1,
when I try to run a package from an asp.net page
the following statement generate error:
myPackage = integrationServices.LoadFromDtsServer(packagePath,
"myServerName", Nothing)
error:
EXECUTE permission denied on object 'sp_dts_getpackage', database 'msdb',
schema 'dbo'.
I followed "Kirk Haselden" instruction on how to assign permissions for
remote user but did not solve the issue.
thanks for help
what permissions should be assigned and to which user.Hello,
This issue may occur if the identity of application pool of asp.net
application does not have the proper permission on msdb database.
You may want to run inetmgr and check the identity of the Website hosting
the asp.net application. If it is "network service", you may add "nt
authortiy/network service" to login of SQL Server, and grant it proper
permission on msdb SQL Server.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I assigned "nt authortiy/network service" the right permissions on procedure
s
sp_dts_getpackage and sp_dts_listpackages, but now I'm getting another error
:
"
Only the owner of DTS Package 'myPackage' or a member of the symin role
may create new versions of it.
"
I was checking those procedure and find a check on the dts owner.
I can disable these checks, but worried that would affect something else on
the server.
any other work arround ?
thanks|||Hello,
As mentioned in the error message, you may want grant SQL server symin
role to the login to work around the issue but it may bring security risk.
Based on my scope, Usually disabling these check shall not have other
effect on the server.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

run sql script at a specific time

I have an sql statement that I can run by hand. How do I get a job to run this automatically.
I have tried to create a job and it just failes out every time...You need to give more information. What does your job do. What job steps are executed. What error messages are generated...
Start by setting the job to log messages to a file for review.|||Sorry for the late response here is my sql statement:

use intranet;
INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE id NOT IN (SELECT FPU_ID FROM USER_INFO);

And when I go through the DTS screens. Telling DTS to copy data from a 3 column temp database to a permanent database called USER_INFO I get a warning that says "LINE 3 incorrect syntax near ')'" This is for the create table steps....

Under the next line I get, Invalid Opject named results...

I am new to DTS,

Any help would be good|||You are still not making any sense.
In your first post you say you have a job that is failing. In your second post you are talking about a DTS package.
You list some SQL code, but not how this has anything to do with your DTS package. Your error "LINE 3 incorrect syntax near ')'" doesn't even make sense given that your code only has two lines. I don't even know whether your error is referring to your SQL statement or code within your DTS package.

Run jobs in SELECT CASE?

Hello!
Is that possible to call a job using SELECT CASE statement? I am trying
to run some jobs based on some cases in my stored procedure. See below
the code:
select case RptName
when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
end
from Table
Trying not to use IF-ELSE statement (which I have tried and it is
working fine).
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***>> Trying not to use IF-ELSE statement
SELECT statement returns a resultset, it is not not meant to execute
procedures. CASE is not supposed to be used as you have suggested. It
returns a scalar value.
Each sp_start_job invocation is a separate statement. Use IF.. ELSE
construct to execute them conditionally.
Anith|||In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
then.. end] is an expression, so you can't execute a procedure from it, but
you can call functions.
However, this would seem to do what you want:
select @.RptName = RptName from Table
if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
Or, looking at your example, it seems that perhaps just this would work:
exec sp_start_job @.job_name = @.RptName
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> Is that possible to call a job using SELECT CASE statement? I am trying
> to run some jobs based on some cases in my stored procedure. See below
> the code:
> select case RptName
> when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
> when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
> when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
> when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
> end
> from Table
> Trying not to use IF-ELSE statement (which I have tried and it is
> working fine).
> Thanks for your help!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks, Anith.
*** Sent via Developersdex http://www.examnotes.net ***|||But then so would...
select @.RptName = RptName from Table
exec sp_start_job @.job_name = @.RptName
Just remember to keep the RptName matching the Job name.
Colin Dawson
www.cjdawson.com
"JT" <someone@.microsoft.com> wrote in message
news:OwGor8aBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
> then.. end] is an expression, so you can't execute a procedure from it,
> but you can call functions.
> However, this would seem to do what you want:
> select @.RptName = RptName from Table
> if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
> if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
> Or, looking at your example, it seems that perhaps just this would work:
> exec sp_start_job @.job_name = @.RptName
>
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
>

Run a select from against two dbs.

Hi,
is there any possiblity to run a select statement against the tables of two different databases which are both installed on the same database server ? Does anybody know how to join those tables reasonable way ?
Thnxselect *
from db1.dbo.table1 t1 inner join db2.dbo.table2 t2
on t1.pk = t2.pk|||Check BOL (http://msdn2.microsoft.com/en-us/library/ms187879.aspx).

-PatP|||Use Linked Servers concept and BOL is your friend.|||Linked servers unnecessary since both dbs on same server.