Pass date parameter
Moderators: chulett, rschirm, roy
Pass date parameter
In the job sequence I have :
Job1 - Creates a Location\MaxDate.txt Seq file
Job2 - Execute command activity -
Command: Type
Parameter: E:\ETL\ETL_Datasets\MaxDate.txt
Job3 - Job activity -
Parameter
p_MaxDate - EReplace(Read_MaxDate.$CommandOutput, @FM,"")
Job4 - SQL stage has #p_MaxDate#
In the job log I am getting
p_MaxDate = "2012-08-14"
But the job is failing with error:
main_program: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2012-08-14'.
Any ideas any one ?
Thanks
Job1 - Creates a Location\MaxDate.txt Seq file
Job2 - Execute command activity -
Command: Type
Parameter: E:\ETL\ETL_Datasets\MaxDate.txt
Job3 - Job activity -
Parameter
p_MaxDate - EReplace(Read_MaxDate.$CommandOutput, @FM,"")
Job4 - SQL stage has #p_MaxDate#
In the job log I am getting
p_MaxDate = "2012-08-14"
But the job is failing with error:
main_program: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2012-08-14'.
Any ideas any one ?
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I suspect it needs to be quoted.
Code: Select all
WHERE UpdateDateTime > '#p_MaxDate#'
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
If I run it quoted
where UpdateDateTime >'#p_MaxDate#'
now I get error:
vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
I also tried
where UpdateDateTime > (select convert(datetime, '#p_MaxDate#', 111))
Same error:
vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
where UpdateDateTime >'#p_MaxDate#'
now I get error:
vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
I also tried
where UpdateDateTime > (select convert(datetime, '#p_MaxDate#', 111))
Same error:
vItem_SRC,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
Not quite the same as Oracle, it would seem. See if this helps.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I tried
where UpdateDateTime >(select CONVERT(DATETIME, ('#p_MaxDate#', 111))
& tried
where UpdateDateTime >(select CAST ('#p_MaxDate#' as DATETIME))
Still same error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
Any idea ...
where UpdateDateTime >(select CONVERT(DATETIME, ('#p_MaxDate#', 111))
& tried
where UpdateDateTime >(select CAST ('#p_MaxDate#' as DATETIME))
Still same error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
Any idea ...
OK... sorry but here are my problems.
One is that I don't "do SQL Server" so the nuances of handling DATETIME fields there rather than say in Oracle are not something I'm familiar with. That means I don't how implicit conversions of things like dates or timestamps are handled internally there or if/when that CONVERT function is really needed.
And second I'm not really following what all you are doing because you only give us little teaser bits with no real detail to back them up so I don't really know exactly what you are doing. So I'm left either guessing or assuming and I'm not a big fan of either.
For example, you say you tried the CONVERT as posted without any detail of what value you used in the parameter. Then you "hard code the date" but show us the parameter being set instead of what the SQL looked like after you hard coded it. Help us out here, or at least help me out. Show me what the SQL looks like that "works perfectly". Tell us what the data type of the p_MaxDate parameter is: Date or String. I suspect the former but you've never actually said.
If the job works perfectly with a hard coded date string in the SQL then there's no reason it won't still work just as perfectly if you put a Job Parameter inside those quotes in the SQL and then pass in a valid value in the exact same format. If you don't need any conversion with the value hard-coded then you won't need any conversion with the job parameter in place.
If you still are having an issue with this, please make sure you post enough detail so we can help you properly. Don't forget we're not sitting there on the same side of the glass as you, we're stuck on the other side and all we can "see" is what you describe to us. Please help us see the whole picture.
One is that I don't "do SQL Server" so the nuances of handling DATETIME fields there rather than say in Oracle are not something I'm familiar with. That means I don't how implicit conversions of things like dates or timestamps are handled internally there or if/when that CONVERT function is really needed.
And second I'm not really following what all you are doing because you only give us little teaser bits with no real detail to back them up so I don't really know exactly what you are doing. So I'm left either guessing or assuming and I'm not a big fan of either.
For example, you say you tried the CONVERT as posted without any detail of what value you used in the parameter. Then you "hard code the date" but show us the parameter being set instead of what the SQL looked like after you hard coded it. Help us out here, or at least help me out. Show me what the SQL looks like that "works perfectly". Tell us what the data type of the p_MaxDate parameter is: Date or String. I suspect the former but you've never actually said.
If the job works perfectly with a hard coded date string in the SQL then there's no reason it won't still work just as perfectly if you put a Job Parameter inside those quotes in the SQL and then pass in a valid value in the exact same format. If you don't need any conversion with the value hard-coded then you won't need any conversion with the job parameter in place.
If you still are having an issue with this, please make sure you post enough detail so we can help you properly. Don't forget we're not sitting there on the same side of the glass as you, we're stuck on the other side and all we can "see" is what you describe to us. Please help us see the whole picture.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
SOLUTION:
THE EASIEST WAY (FOR ME) TO PASS A DATE PARAMETER
THESE JOBS GO IN A SEQUENCE
JOB1
SELECT ISNULL(MAX(Update_Dt),current_timestamp) Inserteddt FROM Table_Name
Save as MaxDate.txt Sequential file as varchar format
JOB2
Execute command activity
cat: location/MaxDate.txt
JOB3
User variable activity
(Name)
MaxDateField
(Expression)
Field(Execute command activity_name.$CommandOutput,'|',1)
JOB4
(Name)
P_MaxDate
(Value expression)
LinkName.MaxDateField
Create parameter
p_MaxDate as string
THE EASIEST WAY (FOR ME) TO PASS A DATE PARAMETER
THESE JOBS GO IN A SEQUENCE
JOB1
SELECT ISNULL(MAX(Update_Dt),current_timestamp) Inserteddt FROM Table_Name
Save as MaxDate.txt Sequential file as varchar format
JOB2
Execute command activity
cat: location/MaxDate.txt
JOB3
User variable activity
(Name)
MaxDateField
(Expression)
Field(Execute command activity_name.$CommandOutput,'|',1)
JOB4
(Name)
P_MaxDate
(Value expression)
LinkName.MaxDateField
Create parameter
p_MaxDate as string