Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using & instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.