SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Rodney Landrum / The Reporting Services 3-Trick Pony / Latest Posts

RE: The Reporting Services 3-Trick Pony

chenthor — Fri, 14 Dec 2007 13:34:18 GMT

I have tried to get this report to work to no avail, either. I downloaded what I believe to be the latest code, I am using SSRS2005 from within VS2005. I have set up the serverlist to only point to a remote dev box that I use all the time, but is sql2k, not 2k5. "Cannot create a connection to data source "test"An error has occurred while establishing a connection to the server. When connecting to Sql Server 2005 (which I am not), this failure may be caused by the fact that under the default settings sql server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to sql server)"My serverlist and the static data source are on this dev box,and when I hit the exclamation point in the data tab, it brings back a server list result set, so I know I can connect to it. When I hard code the data connection string, it works fine. it just doesnt like the dynamic part.Also, what is the @loginname param used for? where is it set?Thanks!

RE: The Reporting Services 3-Trick Pony

Ramseier Andreas — Thu, 25 Oct 2007 03:47:59 GMT

HiThank's for the link. Yes, on this site it says under "unsupported features" that"Remote data sources are not supported"So only local data sources are supported and i cannot connect to other serversAndy

RE: The Reporting Services 3-Trick Pony

VSVaidya — Thu, 25 Oct 2007 03:45:53 GMT

You have not configured the Reporting services while installing the Sql Server Exp Edition. Please read the below mentioned link on more to do that.....msdn2.microsoft.com/en-us/library/ms365166.aspx

RE: The Reporting Services 3-Trick Pony

VSVaidya — Thu, 25 Oct 2007 03:41:39 GMT

You have configured the Reporting services while installing the Sql Server Exp Edition. Please read the below mentioned link on more to do that.....msdn2.microsoft.com/en-us/library/ms365166.aspx

RE: The Reporting Services 3-Trick Pony

Ramseier Andreas — Thu, 25 Oct 2007 02:55:52 GMT

HiIndeed Express Edition only supports local Datasource...So this report doesn't run on Express Edition Report ServiceAndy

RE: The Reporting Services 3-Trick Pony

Ryan Austin — Wed, 24 Oct 2007 12:43:35 GMT

OK that does make sense because I have the DBA_Rep on another server altogether from the SSRS instance.

RE: The Reporting Services 3-Trick Pony

Rodney Landrum — Wed, 24 Oct 2007 12:40:28 GMT

It could be the local server connection in the DBA_Rep data source. You might need to change it to the server where the DBA_Rep database resides if it is not local to the SSRS server instance. Most likely it is working in design because that is where you have the DBA_Rep database.Rodney

RE: The Reporting Services 3-Trick Pony

Ryan Austin — Wed, 24 Oct 2007 12:17:37 GMT

Well in my case, its not because its Express, it is the Standard install of SQL2005 with Reporting Services and the tests all work fine when previewing from Visual Studio, so I am a little stumped.

RE: The Reporting Services 3-Trick Pony

Ramseier Andreas — Wed, 24 Oct 2007 00:04:57 GMT

HiThe new version works fine within Visual Studio. But if i deploy it to my local report server (Expresion Edition). I had first this error:"The 'ServerName' parameter is missing a value "-> so i changed the default Paramter for ServerName from default "NULL" to="localhost"But now i have to following error when running the report outside from VS:"An error has occurred during report processing.Cannot create a connection to data source 'test'.The feature: "The edition of Reporting Services that you are using requires that you use local SQL Server relational databases for report data sources and the report server database." is not supported in this edition of Reporting Services." -> Maybe this reports don't run with express edition?RegardsAny

RE: The Reporting Services 3-Trick Pony

Ryan Austin — Tue, 23 Oct 2007 22:18:07 GMT

I did try that and still getting the same problems. :crazy:

RE: The Reporting Services 3-Trick Pony

Rodney Landrum — Tue, 23 Oct 2007 19:46:31 GMT

Try downloading the latest RDLs. There was an issue.Rodney

RE: The Reporting Services 3-Trick Pony

Ryan Austin — Tue, 23 Oct 2007 16:32:56 GMT

I'm still getting the "Cannot create a connection to data source 'test' " error message even though I have changed the DataSet2 to be a Stored Proc. I've only added one server to the table for simplicity and still nothing, do I need to pass a service account for credentials or something??

RE: The Reporting Services 3-Trick Pony

bbychkov — Tue, 23 Oct 2007 11:54:37 GMT

This is really cool!I'm thinking of using these techniques and combining them with standard SSMS reports and/or performance dashboard reports. That would be a nice muli-server monitoring solution.Thanks Rodney!

RE: The Reporting Services 3-Trick Pony

Jereme Guenther — Tue, 23 Oct 2007 11:27:47 GMT

Thanks Tony. It is really nice having a larger group of people who can makes these small changes for us. I think poor Steve was getting overwhelmed with the amount of work and time this site was taking.

RE: The Reporting Services 3-Trick Pony

Tony Davis — Tue, 23 Oct 2007 10:26:36 GMT

The new RDLs are now in the code download.

Cheers,Tony.

RE: The Reporting Services 3-Trick Pony

Rodney Landrum — Tue, 23 Oct 2007 09:48:17 GMT

I have asked to place new RDLs in the code download.The issue everyone is having with the "TEST" datasource is the default parameter value for the ServerName parameter.To address without new code, change the default value to be NULL not Non-Queried.Sorry for the oversight.Rodney Landrum

RE: The Reporting Services 3-Trick Pony

Rodney Landrum — Tue, 23 Oct 2007 05:49:16 GMT

Thanks for all of your comments so far. The "test" data source is not a shared data source. When developing the reports, I used a static datasource for the "test" connection. Yes, I realize, I should have named "test" something else, like "dynamic_server_source". Once the sp_who2 returned results, I then added the dynamic connection string that is in the article.Two notes:When I downloaded and tested loading in the RDL in a new project this morning everthing went fine except that the data set that execute "sp_who2" reverted to a type of Text. You will need to change dataset2 to be Stored Procedure, not Text. And yes...I sheepishly note that SR1 can call itself in a hyperlink. I was just waiting for someone to point that out. :) Actually, that is a great idea, instead of having two reports. Big red palm print on my forehead. I recall starting down that path and hit an error with a invalid nested parameter. Works great, though...thanks for additional the tip Jon.Rodney LandrumAuthor (Pro SQL Server 2005 Rpeorting Services - Apress)

RE: The Reporting Services 3-Trick Pony

Bart van de Griendt — Tue, 23 Oct 2007 03:11:12 GMT

It keeps failing on error "Cannot create a connection to data source 'test'.I checked the datasets and one of them is referring to 'test'. That data source is not available of course.I tried to repoint the dataset to the 'DBA_REP.rsd' datasource, but that does not help.

RE: The Reporting Services 3-Trick Pony

Bart Hayes-427724 — Tue, 23 Oct 2007 03:02:40 GMT

I found the answer using the magic of Google. A report specific datasource can be created by selecting the Data tab and creating a new dataset. Should have known but funnily enough I've just always used shared datasources.Cheers for now.

RE: The Reporting Services 3-Trick Pony

Jon Spink — Tue, 23 Oct 2007 02:25:14 GMT

This is neat, but couldn;t you have the report call itself? Then there's be no need for an SR1 AND an SR2. I do this for a couple of reports where a hyperlink initiates a data update and the report then runs again automatically to show the changed state of the data.

RE: The Reporting Services 3-Trick Pony

Bart Hayes-427724 — Tue, 23 Oct 2007 01:49:24 GMT

I'm keen to try this out but cannot create the data source. An error message stating that "expressions cannot be used in a shared data source" appears. How do I create a data source that is not a shared data source? The Solution only has Shared Data Sources.Even the Report Wizard stalled at this, there was a warning messages saying "that a connection can not be made to the database" and that "connection string expressions cannot be tested at design time" which is fine but the "next" button would not respond and I could only go back or cancel.How did you get around this?

The Reporting Services 3-Trick Pony

Rodney Landrum — Mon, 22 Oct 2007 21:56:02 GMT

Comments posted to this topic are about the item [B]The Reporting Services 3-Trick Pony[/B]