SQL Command From Variable - Datetime
Nov 16, 2007
I was not able to find the solution myself so here I am to ask you.
I'm using an Ole DB Source with the SQL command from variable.
My variable expression needs to look like this:
select * from dbo.fx_function (SomeDateVariable)
My sql server fx_function recieves as a parameter datetime, my variable SomeDateVariable is datetime type.
I tried all different types of cast on @User :: SomeDateVariable but I either can't evaluate the expression or I have errors trying to execute the package.
Any ideas how the expression should look like?
I'll keep trying...
View 8 Replies
ADVERTISEMENT
Apr 28, 2008
I'm looking at http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx and http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx trying to understand where we are at using datetime overrides in ssis from the command line. One of these articles suggests something has been fixed in ssis sp1, what part of this issue was fixed?
Can you override (from the command line) a user var in ssis whose ValueType property is datetime? Or do some of the older articles still apply, ie the user var must be string so that ssis wont create a separate user var with the same name etc etc? And if it is string, does some kind of conversion need to be done to compare it against datetime cols in t-sql inside the pkg?
I already know that the syntax is
dtexec /F Package.dtsx /SET Package.Variables[User::Var].Properties[Value];2007-01-02.
If this has been fixed, was it fixed in service pack 1, 2?
View 1 Replies
View Related
Nov 4, 2006
Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Feb 22, 2008
Hi. I get problem when I convert DateTime to literal value. It didn't match sql syntax. ALTER PROCEDURE dbo.Search
(
@Table nvarchar(50),
@Field nvarchar(50),
@BeginDate DateTime,
@EndDate DateTime
)
AS
DECLARE @Sql nvarchar(1000)
SET @Sql = 'SELECT ' + @Table + '.* FROM ' + @Table + ' WHERE ' + @Field + ' BETWEEN ' + convert(nvarchar(20), @BeginDate) + ' AND ' + convert(nvarchar(20), @EndDate)
EXEC sp_executesql @Sql
View 5 Replies
View Related
Jan 15, 2008
I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.
View 1 Replies
View Related
Mar 2, 2008
I'm having difficulty passing a date parameter in a SSIS OLEDB connection to a stored procedure using SQL command with the error message "Attempted to read or write protected memory ..." and if I create a variable, I get the error "must declare the scalar variable..". Here is the OLEDB command: "EXEC dbo.mic_TGDrop ?" where ? requires a date parameter @ReportDate. Why does the call to the stored procedure fail? What would be the correct syntax of a variable to use SQL Command from variable? The SQL BOL does not cover this; is there another resource that has a simple example? Thanks.
View 2 Replies
View Related
Mar 29, 2006
Why I canīt use the command USE with variable?
declare @banco varchar(20)
select @banco='xpto'
USE @banco
I have try in other way without success
DECLARE @banco varchar(50)
declare @SQL varchar(50)
select @banco='xpto'
print @banco
select @SQL='USE '+@banco
print @SQL
exec(@SQL)
Help me, please.
Rodrigo
View 7 Replies
View Related
Oct 21, 2007
KEEPS SAYING MISSING EXPRESSION:
IN THE VARIABLE EXPRESSION I'VE WRITTEN THE FOLLOWING COMMAND:
"SELECT * FROM MY_TABLE WHERE T1.DATE >= @[USER:ATE]"
I'VE CLICKED EVALUATE EXPRESSION AND IT ACCEPTS THE EXPRESSION
HOWEVER WHEN I GO TO DATA FLOW TO SELECT SQL COMMAND FROM VARIABLE IT BRINGS BACK MISSING EXPRESSION????
View 13 Replies
View Related
Oct 17, 2007
Hi,
I need to execute some store procedures I have in the SQL editor but I seem to been having problems with the formatting for datetime variables needed for the execution of my code. can anyone please help?
thanks in Advance
exec [usp_CMSTemplateCreateNewTemplate] 0, 0, 'My First Page', '', 0, CAST(10/16/2007,DATETIME), CAST(10/25/2007, DATETIME), 0, @PageTemplateID=0
THE STORED PROCEDURE SAMPLE
ALTER PROCEDURE [dbo].[usp_CMSTemplateCreateNewTemplate]
@SiteID AS INT,
@PageID AS INT,
@Title AS NVARCHAR(50),
@EditHREF AS NVARCHAR(512),
@CreatorID AS INT,
@StartDate AS DATETIME,
@EndDate AS DATETIME,
@Child AS INT,@PageTemplateID AS INT OUTPUT
AS
View 1 Replies
View Related
Nov 14, 2007
Hi all,
I have created a global variable and it will read the user input from a web application. How can i pass this variable into OLE DB destination SQL command so that i can retrieve the user specified table? Or is there a better way to do this?
View 4 Replies
View Related
Nov 8, 2004
select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.
View 2 Replies
View Related
Dec 5, 1999
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.
Here's what it looks like:
[after submitting a form on the previous page with a drop down list box titled "fiscal"]
dim strYear
strYear = Request.Form("fiscal")
sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"
[where strYear is the following: PYMNT_DT > ''12/31/98'']
Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?
Thanks in advance.
Sincerely,
Chad Massie
View 1 Replies
View Related
Apr 29, 2008
This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed.
Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression?
I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...
View 9 Replies
View Related
Mar 29, 2007
Hello everyone!
Bit of a problem executing a DTS command from a command line.
I have the following variables defined in my package:
UserVarchar1
UserVarchar2
UserVarchar3
All have a scope of Package, all of the mstrings
The command I'm attempting to run in 1 line is:
DTEXEC /FILE "C:SSIS PackagesED-CustomersPackage.dtsx"
/SET Package.Variables[User::UserVarchar1].value;"10"
/SET Package.Variables[User::UserVarchar2].value;"30719"
/SET Package.Variables[User::UserVarchar3].value;"BILLTO"
Description: The package path referenced an object that cannot be found: "Package.Variables[User::UserVarchar1].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
DTExec: Could not set Package.Variables[User::UserVarchar1].value value to 10.
Any idea why?
Thanks for the help!
View 10 Replies
View Related
Apr 15, 2008
I read several posts on this topic and I would like to confirm my understanding.
This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement.
But in researching further, it looks like I need to store the entire SQL command in a one long string variable (let's call it A), where A is built off of another variable that has my parameter. I then use A in Data access mode: SQL command from variable.
Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value?
It seems a bit clumsy to store the entire SQL expression in a string variable.
View 10 Replies
View Related
Apr 10, 2008
Using Jamie's, John's and Rafae post i have got this far.
My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this;
"select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].
I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day.
The flow is successful but there is no records written to the target.
I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records.
I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error.
Am i missing something fundamental?
View 7 Replies
View Related
Aug 29, 2001
How to pass datetime as a variable to an sp? Can any one give the syntax??thanks.
View 1 Replies
View Related
May 12, 2004
HI,
I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.
DECLARE @DATE NVARCHAR(100)
SET @DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @DATE)
WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.
BUT,
WHEN I RUN A VARIABLE CONTAINING DATETIME,
DECLARE @DATE DATETIME
SET @DATE = GETDATE()
EXEC ('SELECT ' + @DATE)
IT GIVES ME AN ERROR :-
"Line 1: Incorrect syntax near '12'. "
IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.
View 7 Replies
View Related
May 7, 2008
Hello All
I m trying to update a table whose col name will be read from another table.
For e.g. Table1 gives the result:
'emp1', 1, 'John'
'emp2', 2, 'Mike'
Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2'
I need to write one Update Statement which will handle all the cases. I tried
Update Table2 set @VariableName = .......
but didnt work...
How can i do that ?
View 7 Replies
View Related
Apr 7, 2008
Hello.
I created a package scope variable to use as a sql command for a data
flow task. But when I click the drop down to select it, nothing
appears. Anybody else have this issue and find a solution?
Thanks,
Rich
View 2 Replies
View Related
Sep 27, 2007
Group,
First let me say that I'm new to SSIS, so be gentle with me.
I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this.
Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist)
As you can see, this is an impossible query on many levels.
My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter.
My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed.
Any ideas would be appreciated.
RH
View 11 Replies
View Related
Sep 7, 2007
In the data flow task I have multiple OLE DB source task to retreive data from a cube using MDX. Below is an example of the query from sql command text field: I have a few questions on how to modify this statment to make it more robust.
1) The where clause [Calendar].[Quarter 2 (2007)] should actually be some kind of variable that will change each time
the user runs this project. So I am wondering how to make this a variable and get it into this field.
2) I had thought that I could use the SSIS variable. I have created one, but I can not figure how to get it into the field that
contains the given select statment.
3) I believe that once I get this variable part to work then I want a way to have user set this data value. Either by selecting data from a table in database or through a user interface where user enters data. I did do some resarch on creating a user interface, but I did not understand what I had to do, so if any one knows where to find a tutorial on how to do this let me know, or what they believe the best/easiest way is to get data from user to fill this where clause.
select * from OPENROWSET('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Adjusted Relative Weight],[Measures].[Adjusted Case Weight]} ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS) ON ROWS FROM [ESR]
WHERE [Calendar].[Quarter 2 (2007)]
')
View 10 Replies
View Related
Jan 18, 2008
Hello,
I have a SP that recevies a date value for a users date of birth called "dob".
However when passing it into the class which contains the Stored procedure it gives an error.
Below is my code
please advice
Thanks
Ehi
1
2 command.Parameters.Add(new SqlParameter("@usernames", SqlDbType.Int, 0, "RegionID"));
3 command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20, "username"));
4 command.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.VarChar, 50, "First_Name"));
5 command.Parameters.Add(new SqlParameter("@Last_Name", SqlDbType.VarChar, 50, "Last_Name"));
6 command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 50, "dob"));
7
8 command.Parameters[0].Value = 4;
9 command.Parameters[1].Value = "username";
10 command.Parameters[2].Value = "First_Name";
11 command.Parameters[3].Value = "Last_Name";
12 command.Parameters[4].Value = DateTime.Parse(dob.Text);
HERE IS THE ERROR MESSAGE
Compiler Error Message: CS0103: The name 'dob' does not exist in the current context
Source Error:
Line 40: command.Parameters[2].Value = "First_Name";
Line 41: command.Parameters[3].Value = "Last_Name";
Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text);
Line 43:
Line 44: int i = command.ExecuteNonQuery();
Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 42
View 7 Replies
View Related
Aug 1, 2007
Hello,
I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.
Any ideas?
Thank you for your help!
cdun2
View 4 Replies
View Related
Feb 23, 2008
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
View 3 Replies
View Related
Sep 13, 2004
Hi,
i am using command files(.cmd) and osql utility to conneft to a database.
following is the requirement.
I have a query "SELECT top 1 au_fname FROM pubs..authors".
Can i store the value of au_fname in the command file?
Is there any work around for this.
Any help is appreciated.
Thanks.... :)
View 1 Replies
View Related
Feb 13, 2007
How can I return a datetime type variable from a stored procedure in SQL Server to C# code?
View 4 Replies
View Related
Mar 30, 2004
CAST a variable into a datetime object
I need to do a CAST(@variable_name as datetime)
this won't work because @variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (
View 4 Replies
View Related
Jul 19, 2007
I am having the wrost trouble with this today for some dumb reason...
Please don't suggest any alternates; this is just a quick example, full code is more elaberate.
Today is 07/19/2007
Declare @StartDate DateTime
@StartDate = CONVERT(VARCHAR(10), Month(GetDate()) & "/22/" & Year(GetDate()), 101)
So I want @StartDate = '07/22/2007'
What I need is CDate
View 6 Replies
View Related
Sep 13, 2006
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
View 5 Replies
View Related
Mar 1, 2007
The source table has timestamp column (Modified Date), which gets upated with each modification via an update trigger.
I store the MAX(modifieddate) in a DeltaExtractionHistory Table in the staging database for each extraction or package execution. Then in the next run, I need to pick the coulmns that have a greater datetime, than the last extracted MAX(ModifiedDate) in the DeltaExtractionHistory table for delta processing.
The OLEDB source has the following SQL statement:
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ?
with the following parameter:
Parameter 0 : dtLastModifiedDate
dtLastModifiedDate is an SSIS variable of type Datetime. I read the value of MAX(ModifiedDate) in this variable in a SQLExecuteTask prior to running the data flow task that contains the OLEDB source task.
The problem is - instead of extracting greater datetime, it also extracts the MAX(Modifieddate), which was already extracted in the last run. In other words instead of functioning as [ModifiedDate] > ?, it is functioning as [ModifiedDate] >= ?
because of this, the primary key gets violated on the target table. I think this is happening because the date is stored as "2007-03-01 11:56:34.550" in the table, but SSIS variable shows it in the data viewer as "01/03/2007 11:56:34 PM". It truncates the milliseconds and picks the same date again, because it thinks milliseconds .550 is greater than 000. But I am not 100% sure, may be it is only showing this in the dataviewer, but it contains the ms part as well. I tried using
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ? AND DATEPART(ms, MODIFIEDDATE) > DATEPART(ms, ?)
But it fails with an error that the provider was not able to parse the SQL and try storing the statement in the SQL variable. I could not succeed in that - the statement never parses with the parameters.
Can someone please help in this.
Thanks
View 8 Replies
View Related
Apr 19, 2007
All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View 2 Replies
View Related
Mar 13, 2008
Hi
How to use a global variable of a package inside oledb command
Scenario:
Glb_Rowcount Variable
I need to use this variable value inside oledb command.
P.S: No use of stored procedures and no script component
View 4 Replies
View Related
