SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Tuesday, February 13, 2007

OLE DB Source - SQL Command

I came across something simple on OLE DB Source last week. I was using “SQL Command” as Data Access Mode. Most of source SQL statements were simple, such as

SELECT oid,baseid
FROM STG_PAS99
WHERE baseid = ?

For the above SQL I can pass a parameter without any problem.

For one reason or the other I had to write a source SQL something like this. Yes I do know that I don’t need inside parameter if I have one outside. I am using it to explain my point plus it does improve performance.

SELECT oid,baseid
FROM STG_PAS99
INNER JOIN (
SELECT MAX(loaded), oid, baseid
FROM STG_PAS99
WHERE baseid = ?
GROUP BY oid, baseid
)
WHERE baseid = ?


If I enter the above SQL into “SQL Command Text” and click on Parameters I got the following message

As the error message suggests I used the SQL Command from variable to get around that issue. If you don’t know how to do that please have a look at Jamie’s Blog

Thanks
Sutha

0 Comments:

Post a Comment

<< Home