December 16, 2009 at 9:57 am
Hello,
I am creating a report to gather data from various SQL servers to which a user has access to.
I am using a dynamic connection string, something like
="Data Source=" &Parameters!ServerName.Value & ";initial Catalog=master"
User would click on a server name and which would set the serverName parameter.
This works fine when in SSRS preview but when deployed to report server, I get the error:
--------------------------
Cannot connect to DS_Dynamic (which is the name of the data source)
Format of the initialization string does not conform to specification starting at index
--------------------------
and it has to work with integrated security so any user can run the report and depending on his access to the servers, will get the data in the report.
Tried several options but to no avail.
Please let me know if this is a common error or I am missing some "special" step in deploying.
Thanks.
December 16, 2009 at 2:37 pm
(="Data Source=" &Parameters!ServerName.Value & ";initial Catalog=master")
If the above code run then Microsoft is doing something wrong but it did not run so you need to find out what is a connection object and where you can consume it in SSRS.
and it has to work with integrated security so any user can run the report and depending on his access to the servers, will get the data in the report.
Tried several options but to no avail.
Please let me know if this is a common error or I am missing some "special" step in deploying.
Your needs are not valid so when you define valid connection property that is not consumed in an expression box of the report post again so I can try to help you. SSRS is initialized by the relational engine it will not run such connection object that may be used for bad things.
Kind regards,
Gift Peddie
December 16, 2009 at 2:46 pm
Ok may be I did not clear the situation enough.
1.The code and the entire report works 100% fine in SSRS preview.
2.Integrated security is working as well since when I try to connect to a server where I don't have access, the report errors out which is exactly what I want.
So why are my needs not "Valid". All I need is to deploy the report which is already working in preview.
Also, this is not being used for any "Bad thing" :). This report can be used to get useful information on 100s of servers where the user has access to.
Thanks.
December 16, 2009 at 2:56 pm
So why are my needs not "Valid". All I need is to deploy the report which is already working in preview.
Also, this is not being used for any "Bad thing" :). This report can be used to get useful information on 100s of servers where the user has access to.
So the user is connecting to 100 server through a none user database which is the master and why it runs in preview that may be related to SSRS and other development tools running what we call delayed execution. In SQL Authentication SSRS is known to drop connectionstrings at random so I am not sure it will run in production. Post your code when it runs.
Kind regards,
Gift Peddie
December 17, 2009 at 10:57 am
Attached is the code.
Thanks.
December 17, 2009 at 12:49 pm
sumit-679660 (12/17/2009)
Attached is the code.Thanks.
You have attached RDL post the connection object code for the Report that is connecting to 100 servers that you have deployed in production.
Kind regards,
Gift Peddie
December 17, 2009 at 1:17 pm
Nevermind the 100 servers. I am truly not concerned about how many servers can the code connect.
If it works for one, it will work for rest.
The key issue for me is if the previous attached code works in SSRS preview, why can it not work in report server?
Thanks for your time and replies.
December 17, 2009 at 1:45 pm
sumit-679660 (12/17/2009)
Nevermind the 100 servers. I am truly not concerned about how many servers can the code connect.If it works for one, it will work for rest.
The key issue for me is if the previous attached code works in SSRS preview, why can it not work in report server?
Thanks for your time and replies.
I need your connection code and you can find that at the point of connection when you start developing report if it is working in preview go in there and post the code. RDL is Microsoft proprietary so I don't use it for anything.
Kind regards,
Gift Peddie
December 17, 2009 at 2:49 pm
This is the connection string
="Data Source=" & Parameters!ServerName.Value & ";initial Catalog=" &Parameters!db.Value
and the starting default values of both these parameters are valid values.
If, by Connection Object, you meant something else please elaborate.
Thanks.
December 17, 2009 at 3:24 pm
sumit-679660 (12/17/2009)
This is the connection string="Data Source=" & Parameters!ServerName.Value & ";initial Catalog=" &Parameters!db.Value
and the starting default values of both these parameters are valid values.
If, by Connection Object, you meant something else please elaborate.
Thanks.
The connection object does not come with parameter, you will need a configuration component to connect to more than one server. You could look into Enterprise Library it is Microsoft data access library it may help you, what you have now is not valid because it is running in preview does not make it valid.
Here is my connection object it is not the same with what you have posted.
Data Source=YourServer;Initial Catalog=AdventureWorks;Integrated Security=True
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply