Page 1 of 1
Massaging job parameters in an OLEDB Where clause
Posted: Fri Nov 28, 2003 2:52 pm
by justlrng
Hi,
I have a simple job to pull data into a sequential file from a SQL Server database using OLEDB. I want to restrict the records I am reading in by school year. We are passing in a 9 character school year ex. 2001/2002 but the database table uses a 4 digit school year ex. 2002. Therefore I need to grab just the last 4 characters of the 9 character string to do my comparison. So my statement in the Selection pane reads as:
sch_yr = RIGHT(#PARAM_1#,4)
where sch_yr is the column from the database and PARAM_1 is the 9 character school year. Although, the job compiles and runs, it is not returning any rows.
Can I manipulate job parameters coming into an OLEDB stage in the Where clause? If so, what is wrong with the above statement?
Thanks
Posted: Fri Nov 28, 2003 3:04 pm
by trobinson
Is RIGHT() a SQL Server function? The SQL you are generating in the ODBC stage is sent to to be executed on the database therefore it must be in the database's format. Parameters are the only exception I know of. One thing you could do is define the school year as a key column and use the RIGHT function in a derivation. Because it is a key column the where clause would be generated for you.
Posted: Fri Nov 28, 2003 3:32 pm
by justlrng
RIGHT is a SQL Server function. The reason I want to do it in the OLEDB stage is to reduce the number of rows being read in, in this case about 600,000 versus 6.5 million.
Posted: Fri Nov 28, 2003 3:38 pm
by kcbland
Your original code:
Shouldn't it have quotes, as it is a string value?
If you were typing this in as a literal, it would look like this:
so try it with quotes.
Just a guess.
Posted: Fri Nov 28, 2003 3:56 pm
by justlrng
Hi Ken,
I tried using single quotes and double quotes and neither works. In fact the job fails as "OLE DB provider error: The command contained one or more errors."
Posted: Fri Nov 28, 2003 4:26 pm
by kcbland
Okay, reverse that, are you comparing a numeric sch_yr to a string value, and that's where the problem lies? Just a guess.
Posted: Fri Nov 28, 2003 4:29 pm
by justlrng
kcbland wrote:Okay, reverse that, are you comparing a numeric sch_yr to a string value, and that's where the problem lies? Just a guess.
I've tried using the following code to convert the string to an integer but I still get 0 rows returned.
sch_yr = CONVERT(SMALLINT,RIGHT(#PARAM_1#,4))
Posted: Fri Nov 28, 2003 4:41 pm
by justlrng
Okay, never mind. One of the guys figured it out over here. In the Output Action of the Output tab of the OLEDB stage - "Direct Read" must be selected. After that, everything runs fine.
Thanks everyone for your suggestions and into looking into this problem. :D
Posted: Mon Dec 01, 2003 10:54 am
by justlrng
Posted: Mon Dec 01, 2003 11:11 am
by shawn_ramsey
Since the errors coming via the oledb stage can be misleading (it does not return the error coming from SQL Server) I would recommend that you turn on the SQL Profiler and watch for the errors. This has been the only way that we have been able to track down these errors. Once you have the real SQL Server error it is a lot easier to track down the problem.
Posted: Tue Dec 02, 2003 2:48 pm
by justlrng
Well, we finally figured it out with the help of the profiler. single quotes are required so that the code will be:
sch_yr = RIGHT('#Param_1#',4)
I thought we had tried everything including single and double quotes but evidently not.
Thanks again.
Posted: Tue Dec 02, 2003 3:16 pm
by shawn_ramsey
justlrng wrote:Well, we finally figured it out with the help of the profiler. single quotes are required so that the code will be:
sch_yr = RIGHT('#Param_1#',4)
I thought we had tried everything including single and double quotes but evidently not.
Thanks again.
If Ascential would pass the real error messages back in the OLEDB and SQL Bulk stages we could avoid having to use profiler. :D
