Click here to Skip to main content
11,411,263 members (65,501 online)
Click here to Skip to main content

Tagged as

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.

Introduction

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 

Solution

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.

Code

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

select dummy
from
-- dummy table in Oracle
dual 

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

History

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. 

License

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

Share

About the Author

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

Comments and Discussions

 
GeneralMy vote of 1 PinmemberRigaJoo17-Mar-11 11:41 
GeneralRe: My vote of 1 Pinmemberdb_developer17-Mar-11 20:41 
GeneralYou're wrong PinmemberRigaJoo17-Mar-11 11:40 
GeneralA possible alternative Pinmembervbfengshui17-Mar-11 8:11 
GeneralRe: A possible alternative Pinmemberdb_developer17-Mar-11 20:40 
GeneralRe: A possible alternative Pinmemberdb_developer17-Mar-11 20:57 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150414.5 | Last Updated 18 Mar 2011
Article Copyright 2011 by db_developer
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid