Massaging job parameters in an OLEDB Where clause

justlrng · Post by **justlrng** » Fri Nov 28, 2003 2:52 pm

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

trobinson · Post by **trobinson** » Fri Nov 28, 2003 3:04 pm

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.

justlrng · Post by **justlrng** » Fri Nov 28, 2003 3:32 pm

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.

kcbland · Post by **kcbland** » Fri Nov 28, 2003 3:38 pm

Your original code:

Code: Select all

sch_yr = RIGHT(#PARAM_1#,4)

Shouldn't it have quotes, as it is a string value?

Code: Select all

sch_yr = RIGHT('#PARAM_1#',4)

If you were typing this in as a literal, it would look like this:

Code: Select all

sch_yr = RIGHT('2001/2002',4)

so try it with quotes.

Just a guess.

justlrng · Post by **justlrng** » Fri Nov 28, 2003 3:56 pm

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

kcbland · Post by **kcbland** » Fri Nov 28, 2003 4:26 pm

Okay, reverse that, are you comparing a numeric sch_yr to a string value, and that's where the problem lies? Just a guess.

justlrng · Post by **justlrng** » Fri Nov 28, 2003 4:29 pm

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

justlrng · Post by **justlrng** » Fri Nov 28, 2003 4:41 pm

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

justlrng · Post by **justlrng** » Mon Dec 01, 2003 10:54 am

Okay, I spoke a little too soon. The Direct read does not seem to pass parameters back to the database. So, basically I still need help!

shawn_ramsey · Post by **shawn_ramsey** » Mon Dec 01, 2003 11:11 am

justlrng wrote:

Okay, I spoke a little too soon. The Direct read does not seem to pass parameters back to the database. So, basically I still need help!

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.

justlrng · Post by **justlrng** » Tue Dec 02, 2003 2:48 pm

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.

shawn_ramsey · Post by **shawn_ramsey** » Tue Dec 02, 2003 3:16 pm

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