Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

Data-driven subscription error

I'm using Windows 2003 server with Sql server 2005 June CTP. When I create a data-driven subscription for a report everything works fine, the query that gets the subscribers from a database is validated successfully. The subscription is time triggered, but when it should start and deliver the reports I get a logon failure error. I use credentials stored securely in the report server. I tried it with sa account and it still doesn't work. When I try to edit the subscription I can see there is blank space where the password should be, is this just to prevent abuse or because it just doesn't store the password? Could someone please tell me what am I doing wrong, thanks.
I believe the problem was that the report server didn't want to store my execution account, still not sure why...

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)