Using data source expressions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using data source expressions

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-03 : 22:39:20

I have a report that is using a shared datasource. The report is finished and runs OK. I now need to chnage it so the user can select the server and database to run the report against. So far and I have done the following and obtain these errors?

1. Added two report parameters called ServerName and DatabaseName with datatype of string.
2. Changed the connection data source to Type:
Microsoft SQL Server
and set Connection String to:
= "data source=" & Parameters!ServerName.Value & ";initial catalog=Parameters!DatabaseName.Value
3. The use shared data source reference check box is off.

When I run the report in preview I get:
The ConnectString expression for the data source VC_1_DB contains an error:[BC30648] String constraints must end with a double quote.

If I deploy the report I get:
Error 1 [rsCompilerErrorInExpression] The ConnectString expression for the data source ‘VC_1_DB’ contains an error: [BC30648] String constants must end with a double quote. c:\documents and settings\steveha\my documents\visual studio 2005\projects\report project3\report project3\Event Site Report.rdl 0 0

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-03 : 22:49:12

>> "data source=" & Parameters!ServerName.Value & ";initial catalog=Parameters!DatabaseName.Value

Should it be "data source=" & Parameters!ServerName.Value & ";initial catalog=Parameters!DatabaseName.Value"?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-03 : 23:50:26

Sorry I did try this as well and the report does deploy however when run the following error comes up in Report Manger:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'VC_1_DB'. (rsErrorOpeningConnection)
Keyword not supported: '"data source'.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 00:16:18

I did get the information from and a few other web sites:
http://msdn.microsoft.com/en-us/library/ms156450.aspx

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 00:20:49

Also using express version, does this make a difference?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 00:37:28

This is my normal connection string:
Data Source=BUTTERFINGERS\SQLEXPRESS;Initial Catalog=VC

So I tried this one and received the following error in Report Manager:
Data Source=BUTTERFINGERS\SQLEXPRESS;initial catalog=Parameters!DatabaseName.Value

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'VC_DB'. (rsErrorOpeningConnection)
Cannot open database "Parameters!DatabaseName.Value" requested by the login. The login failed. Login failed for user 'ADPRO.COM.AU\steveha'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 00:40:24

You haven't included the authentication information in your connection string.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 01:22:06

So I have done some searching and found a lot of information however I am still confused about what to do. I am just starting out so I have no idea what I am doing, example please. My data source is set to Use Windows Authentication. I have tried adding the following however when I select OK and then go back in the ; Integrated Security=SSPI; part is not saved?
Data Source=BUTTERFINGERS\SQLEXPRESS;Initial Catalog=Parameters!DatabaseName.Value; Integrated Security=SSPI;

I should also indicate when I used the following and received the error above it never asked for the database parameter, just gave the error?:
Data Source=BUTTERFINGERS\SQLEXPRESS;initial catalog=Parameters!DatabaseName.Value

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'VC_DB'. (rsErrorOpeningConnection)
Cannot open database "Parameters!DatabaseName.Value" requested by the login. The login failed. Login failed for user 'ADPRO.COM.AU\steveha'.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 08:53:35

Still need help here and I thought this was going to be easy. What am I missing or specifically how do I perform this task?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 21:36:47

You'll need to resolve the "Login failed" error message. Go into SQL Server Management Studio and verify that the account has access to the database and has the appropriate permissions. So in essence, the error is with SQL Server account access and not in Reporting Services.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 23:26:06

Please put me in my place however I am trying to understand the logic rather than just getting an answer so I can learn.
1. I have a connection string that works: Data Source=BUTTERFINGERS\SQLEXPRESS;Initial Catalog=VC
2. I am trying to replace the database name, in this case VC with a variable so the user can select which DB to run the report against as per the instructions from MSDN.
3. When I change to Data Source=BUTTERFINGERS\SQLEXPRESS;initial catalog=Parameters!DatabaseName.Value and add a report parameter and run I get the following error: Cannot open database "Parameters!DatabaseName.Value" requested by the login. The login failed. Login failed for user 'ADPRO.COM.AU\steveha'.
4. What I find interesting is that of course it cannot login with "Parameters!DatabaseName.Value" as this is not a valid DB, it is the variable name. It never prompt me the report parameter to enter a valid DB name.
5. I would have thought that when the report was run it would ask for a DB name via the report parameter, replacing the variable name with the actual string or name of the DB and then run so in essence the connection string would be the same as my original working string?

Have I lost the plot here:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 23:34:35

I don't know anything about these dynamic database names. I'm only commenting on what I know of connection strings in general and also about the error message.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-06 : 21:03:31

I still have not resolved this one. Has anyone created a variable for a connection string in reporting services? This is so the customer can select the DB the report is going to run against from a drop down list. I obtained the info from:
http://msdn.microsoft.com/en-us/library/ms156450.aspx
="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks
I am trying to have the server name fixed and DB name as a variable. See above for further details.

Thanks

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 01:34:21

OK, so after 3 days of pain I have the solution. Lets start with the problem. I had two report parameters in the report that worked. When I added the two new parameters for server and database name they became paramters 3 and 4 in the list. They have to be 1 and 2. doh! So in the end all that was needed was to:
1. Create the report with a shared data source or static string that works and test report.
2. Add two report parameters called server and database (important, if you have other parameters these need to be 1 and 2 in the list and make sure allow blank checkbox is off)
3. Change connection to:
="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value
4. Deploy and test report.

Vasuki
Starting Member

1 Post

Posted - 2008-11-07 : 13:47:32

HI Harlington,

I tried the above steps but still when i do the point 3 , Iam getting the below error message Keyword not supported .Its kind of urgent.COuld you please help me with this?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-07 : 15:49:25

Here's a step by step article to a dynamic connection string in SSRS. I have tested and it works

http://www.sqlservercentral.com/articles/Development/2945/

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources