Click here to Skip to main content
12,758,135 members (29,965 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 17 Mar 2011

Avoid single line comments in query to OLE DB Datasource

, 17 Mar 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
OLE DB Provider pack all query to single line, thus all query text after comment clause will be truncated
I know that it is too small for article. I just can not put its HTML to tip - I see all tags and others in preview... So if you dont agree that it is article, just walk through. Think about those who really needs this information, at least the guy from this thread. Thanks.


I had to migrate some SSIS packages with ADO.Net datasources to OLE DB datasources because of x64 and Oracle. All packages were workable, so I was surprised when I had got Datasource errors after switching to OLE DB Datasource. They were:

  • ORA-00907: missing right parenthesis
  • ORA-00903: invalid table name 
  • ORA-00904: string: invalid identifier 


OLE DB Provider packs all query to single string, therefore if you have a comment in query, the rest of query will be truncated. So just avoid using of comments in query text that you intend to send to OLE DB Driver.


If you v got well formatted SQL query something like that: 

select dummy
-- dummy table in Oracle

you will take the following as input for OLE DB Provider instead:

select dummy from --dummy table in Oracle dual

that I persume is not exactly what you expected :)


As soon as I got problem with OLE DB Datasource, I had grabbed the contents of the string that is passed in to the query field and had thoroughly examined it and there are no missing parens, all tables exist, all aliases are correct. Additionally, I was able to execute the select statement in Toad for Oracle, without difficulty. So I was sure that the string is syntactically correct.

First hypothesis was that something fishy is happening in the provider - perhaps the string is too long and it is getting truncated somewhere, but I had much longer query that executed successfully in the same package inside OLE DB Datasource.

After several hours of examining of problematic query (it numbered 11k of characters), I selected query of small size and where problem was reproduced. It became obvious that problem is in comments I had in query.

So I deleted all comments in query and problem was solved. 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience

Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.

Feel free to contact me for rates and details.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
RigaJoo17-Mar-11 11:41
memberRigaJoo17-Mar-11 11:41 
GeneralRe: My vote of 1 Pin
db_developer17-Mar-11 20:41
memberdb_developer17-Mar-11 20:41 
GeneralYou're wrong Pin
RigaJoo17-Mar-11 11:40
memberRigaJoo17-Mar-11 11:40 
GeneralA possible alternative Pin
vbfengshui17-Mar-11 8:11
membervbfengshui17-Mar-11 8:11 
GeneralRe: A possible alternative Pin
db_developer17-Mar-11 20:40
memberdb_developer17-Mar-11 20:40 
GeneralRe: A possible alternative Pin
db_developer17-Mar-11 20:57
memberdb_developer17-Mar-11 20:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 18 Mar 2011
Article Copyright 2011 by db_developer
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid