ole db source query assistance requested

my query is below:

"SELECT * FROM " + @.[User:: TableName] +
" WHERE OrderDate = " + "'" + @.[User::dTrxnDate] + "'"

needless to say, the ole db source editor is giving me syntax errors. the data type of TableName is string. the data type of dTrxnDate is DateTime.

can someone please help me resolve the syntax errors?

thanks in advance.

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

|||

DarrenSQLIS wrote:

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

thanks darren, but i'm still getting a syntax error. my query is below:

"SELECT * FROM " + @.[User::TableName] +
" WHERE OrderDate = '" + (DT_WSTR,4)YEAR(@.[User::dTrxnDate]) + RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::dTrxnDate]), 2) + RIGHT("0" + (DT_WSTR,2)DAY(@.[User::dTrxnDate]), 2) + "'"

below is the syntax error:

TITLE: Microsoft Visual Studio

Error at Load FactOrders Data Flow [Source from xtract [7629]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK|||

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable? Have you used the expression builder, which only became available for the variable Expression property with SP1. Previously pasting a multi-line expression into the single line property grid box did not work, you just got the first line.

What does your expression evaluate to in the variable's expression ?

|||

DarrenSQLIS wrote:

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable?

i'm evaluating the expression within the ole db source editor. should i evaluate the expression within a variable instead?

my understanding is that the ole db source can evaluate ssis expressions. is this not true?

|||i'm still seeking assistance.|||

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error

Hi guys,

Anyone encountered this type of error " OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error"

The error you saw is quit common and it can be mapped to many cases. In most cases, the error happens because the server closes the connection for some reason when client is expecting data from the server. Can you described more about you app? Does the error happen during a long running query? Is the error consistent or intermittent.|||

Hello!!!

The application is a Business Intelligence application, wherein lots of extract transformation and loading process are involved before processing the cube. The error is intermittent and happens during the dimension processing. Our server is running Win2k3 sp1 sql server 2005 sp1 also configured with 8 GB memory and AWE enabled.

What we are doing in order to continue the process is to either restart sql server service or the analysis service and process the dimension that failed.

any idea on what is causing the error?

|||

Hi Larry,

General Network Error is a infamous error and one of the toughest cases MS PSS faces nowadays. This is due to there can be various cause of this problem.

There is a webcast on this topic:

http://support.microsoft.com/kb/875285/en-us

I remember there is a common cause of GNE for windows 2003 sp1, but can not find the KB now. Maybe I will find and post it here when I get back to work on Monday for your reference.

Thx, -Justin

|||

Tnx Justin, It will be great and I'll appreciate it if you can post the KB here.

Thanks,

Larry

|||

Here is the KB. This is a general cause of GNE on Win2003 SP1.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;899599

Basically, GNE occurs when the client connection is unexpectedly closed. This mostly due to temporarily network hardware failure or firewall related issue.

|||

In case of your cube application, one of the possibility is that the server decided to close a connection for one of the following reason. (1) query exection was too long, you can extend the connection timeout value to mitigate the issue. (2) your failed query, thus the connection, was choosen as a deadlock victim and killed by the server.

If you are using SQL Server 2005, you might find interesting entries in ERRORLOG to correlate your GNE. For two reasons that I listed above, the better forum to ask how to identify them is from sql server engine forum.

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network erro

Hi guys,

Anyone encountered this type of error " OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error"

The error you saw is quit common and it can be mapped to many cases. In most cases, the error happens because the server closes the connection for some reason when client is expecting data from the server. Can you described more about you app? Does the error happen during a long running query? Is the error consistent or intermittent.|||

Hello!!!

The application is a Business Intelligence application, wherein lots of extract transformation and loading process are involved before processing the cube. The error is intermittent and happens during the dimension processing. Our server is running Win2k3 sp1 sql server 2005 sp1 also configured with 8 GB memory and AWE enabled.

What we are doing in order to continue the process is to either restart sql server service or the analysis service and process the dimension that failed.

any idea on what is causing the error?

|||

Hi Larry,

General Network Error is a infamous error and one of the toughest cases MS PSS faces nowadays. This is due to there can be various cause of this problem.

There is a webcast on this topic:

http://support.microsoft.com/kb/875285/en-us

I remember there is a common cause of GNE for windows 2003 sp1, but can not find the KB now. Maybe I will find and post it here when I get back to work on Monday for your reference.

Thx, -Justin

|||

Tnx Justin, It will be great and I'll appreciate it if you can post the KB here.

Thanks,

Larry

|||

Here is the KB. This is a general cause of GNE on Win2003 SP1.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;899599

Basically, GNE occurs when the client connection is unexpectedly closed. This mostly due to temporarily network hardware failure or firewall related issue.

|||

In case of your cube application, one of the possibility is that the server decided to close a connection for one of the following reason. (1) query exection was too long, you can extend the connection timeout value to mitigate the issue. (2) your failed query, thus the connection, was choosen as a deadlock victim and killed by the server.

If you are using SQL Server 2005, you might find interesting entries in ERRORLOG to correlate your GNE. For two reasons that I listed above, the better forum to ask how to identify them is from sql server engine forum.