|
|
You're in the
sql server reporting services
group:Set ConnectionString at run-time
sql server reporting services:
I need to be able to set the ConnectionString for any datasources at
run-time. Basically, I will design a report (against SQL Server 2000) and my application will use URL Access to call this report. Depending on which database the application is running against, the report should run against the same database. This may be the same database as was set at design-time, it may be another database on the same server, or it could be a database on another server (all would be SQL Server 2000 though). I just need the ability to change which server/database the report uses at run-time. I've been able to do this using the SOAP API, but it is too slow and has other problems. I also don't want to mess with linking servers, complicated queries, etc. I just want to be able to change the datasource at run-time (like Crystal or any other reporting tool I've used in the past). I'm assuming this is going to mean a custom data extension. I also assume I'll have to pass the connection string as a parameter to the report using URL access (I'll encrypt the connection don't worry ;-). My real question is how can I get this parameter from the URL to my data extension. Will I need to manually create this parameter for each report? Can I hard-code this parameter in the data-extension (in the GetParameters interface)? Any thoughts or suggestions would be much appreciated!!!
Version 1.0 of RS doesn't support expression-based connection strings. This
is on the wish list for 2005 and most likely will be implemented. Yes, currently one way of doing this is to author a custom data extension. You don't have to pass the connection string as a parameter which is also a security risk. Instead, you could use a setting in in the RS web.config file if this is OK with your requirements. Another option is to use a report-specific data source. When the user requests the report, load the report RDL in XML DOM, change the connection string, upload the report and render it. -- Hope this helps. --------------------------------------------- Teo Lachev, MVP [SQL Server], MCSD, MCT Author: "Microsoft Reporting Services in Action" Publisher website: http://www.manning.com/lachev Buy it from Amazon.com: http://shrinkster.com/eq Home page and blog: http://www.prologika.com/ --------------------------------------------- [quoted text, click to view] "CaymanVisitor" <CaymanVisitor@discussions.microsoft.com> wrote in message news:F767D15C-32B0-4B23-A1A0-963A6FCF288D@microsoft.com... > I need to be able to set the ConnectionString for any datasources at > run-time. Basically, I will design a report (against SQL Server 2000) and my > application will use URL Access to call this report. Depending on which > database the application is running against, the report should run against > the same database. This may be the same database as was set at design-time, > it may be another database on the same server, or it could be a database on > another server (all would be SQL Server 2000 though). > > I just need the ability to change which server/database the report uses at > run-time. I've been able to do this using the SOAP API, but it is too slow > and has other problems. I also don't want to mess with linking servers, > complicated queries, etc. I just want to be able to change the datasource at > run-time (like Crystal or any other reporting tool I've used in the past). > > I'm assuming this is going to mean a custom data extension. I also assume > I'll have to pass the connection string as a parameter to the report using > URL access (I'll encrypt the connection don't worry ;-). > > My real question is how can I get this parameter from the URL to my data > extension. Will I need to manually create this parameter for each report? > Can I hard-code this parameter in the data-extension (in the GetParameters > interface)? > > Any thoughts or suggestions would be much appreciated!!! > >
I have a separate post about this, but since this is related, I'll see what I
get here. I need to have a dynamic connect string so after a user authenticates, I can dynamically set Source and InitialCatalog based on who connected. Connect string in data source: Provider=MSOLAP.2;Client Cache Size=25;Auto Synch Period=10000;Data Source=http://servername;Initial Catalog=Database1 We have 1 web site and multiple customer databases (Database1, Database2) on same Analysis server - who connects points them at correct InitialCatalog. Not sure if I can even do this even using security extension and data processing extension. I don't want a set of reports for every customer since only difference is the Initial Catalog. Also have to deal with development/test/production data source. [quoted text, click to view] "Teo Lachev [MVP]" wrote:
> Version 1.0 of RS doesn't support expression-based connection strings. This > is on the wish list for 2005 and most likely will be implemented. > > Yes, currently one way of doing this is to author a custom data extension. > You don't have to pass the connection string as a parameter which is also a > security risk. Instead, you could use a setting in in the RS web.config file > if this is OK with your requirements. > > Another option is to use a report-specific data source. When the user > requests the report, load the report RDL in XML DOM, change the connection > string, upload the report and render it. > > -- > Hope this helps. > > --------------------------------------------- > Teo Lachev, MVP [SQL Server], MCSD, MCT > Author: "Microsoft Reporting Services in Action" > Publisher website: http://www.manning.com/lachev > Buy it from Amazon.com: http://shrinkster.com/eq > Home page and blog: http://www.prologika.com/ > --------------------------------------------- > > "CaymanVisitor" <CaymanVisitor@discussions.microsoft.com> wrote in message > news:F767D15C-32B0-4B23-A1A0-963A6FCF288D@microsoft.com... > > I need to be able to set the ConnectionString for any datasources at > > run-time. Basically, I will design a report (against SQL Server 2000) and > my > > application will use URL Access to call this report. Depending on which > > database the application is running against, the report should run against > > the same database. This may be the same database as was set at > design-time, > > it may be another database on the same server, or it could be a database > on > > another server (all would be SQL Server 2000 though). > > > > I just need the ability to change which server/database the report uses at > > run-time. I've been able to do this using the SOAP API, but it is too > slow > > and has other problems. I also don't want to mess with linking servers, > > complicated queries, etc. I just want to be able to change the datasource > at > > run-time (like Crystal or any other reporting tool I've used in the past). > > > > I'm assuming this is going to mean a custom data extension. I also assume > > I'll have to pass the connection string as a parameter to the report using > > URL access (I'll encrypt the connection don't worry ;-). > > > > My real question is how can I get this parameter from the URL to my data > > extension. Will I need to manually create this parameter for each report? > > Can I hard-code this parameter in the data-extension (in the GetParameters > > interface)? > > > > Any thoughts or suggestions would be much appreciated!!! > > > > > >
Same as my previous reply. Consider using a custom data extension if you
will be requesting reports by URL. If you can wait for a few more months, RS 2005 will support expression-based connection strings. -- Hope this helps. --------------------------------------------- Teo Lachev, MVP [SQL Server], MCSD, MCT Author: "Microsoft Reporting Services in Action" Publisher website: http://www.manning.com/lachev Buy it from Amazon.com: http://shrinkster.com/eq Home page and blog: http://www.prologika.com/ --------------------------------------------- [quoted text, click to view] "danb" <danb@discussions.microsoft.com> wrote in message news:1F23DF16-314B-48E9-81CA-D96AC66D24C1@microsoft.com... >I have a separate post about this, but since this is related, I'll see what >I > get here. > > I need to have a dynamic connect string so after a user authenticates, I > can > dynamically set Source and InitialCatalog based on who connected. > > Connect string in data source: > > Provider=MSOLAP.2;Client Cache Size=25;Auto Synch Period=10000;Data > Source=http://servername;Initial Catalog=Database1 > > We have 1 web site and multiple customer databases (Database1, Database2) > on > same Analysis server - who connects points them at correct InitialCatalog. > Not sure if I can even do this even using security extension and data > processing extension. I don't want a set of reports for every customer > since > only difference is the Initial Catalog. Also have to deal with > development/test/production data source. > > > "Teo Lachev [MVP]" wrote: > >> Version 1.0 of RS doesn't support expression-based connection strings. >> This >> is on the wish list for 2005 and most likely will be implemented. >> >> Yes, currently one way of doing this is to author a custom data >> extension. >> You don't have to pass the connection string as a parameter which is also >> a >> security risk. Instead, you could use a setting in in the RS web.config >> file >> if this is OK with your requirements. >> >> Another option is to use a report-specific data source. When the user >> requests the report, load the report RDL in XML DOM, change the >> connection >> string, upload the report and render it. >> >> -- >> Hope this helps. >> >> --------------------------------------------- >> Teo Lachev, MVP [SQL Server], MCSD, MCT >> Author: "Microsoft Reporting Services in Action" >> Publisher website: http://www.manning.com/lachev >> Buy it from Amazon.com: http://shrinkster.com/eq >> Home page and blog: http://www.prologika.com/ >> --------------------------------------------- >> >> "CaymanVisitor" <CaymanVisitor@discussions.microsoft.com> wrote in >> message >> news:F767D15C-32B0-4B23-A1A0-963A6FCF288D@microsoft.com... >> > I need to be able to set the ConnectionString for any datasources at >> > run-time. Basically, I will design a report (against SQL Server 2000) >> > and >> my >> > application will use URL Access to call this report. Depending on >> > which >> > database the application is running against, the report should run >> > against >> > the same database. This may be the same database as was set at >> design-time, >> > it may be another database on the same server, or it could be a >> > database >> on >> > another server (all would be SQL Server 2000 though). >> > >> > I just need the ability to change which server/database the report uses >> > at >> > run-time. I've been able to do this using the SOAP API, but it is too >> slow >> > and has other problems. I also don't want to mess with linking >> > servers, >> > complicated queries, etc. I just want to be able to change the >> > datasource >> at >> > run-time (like Crystal or any other reporting tool I've used in the >> > past). >> > >> > I'm assuming this is going to mean a custom data extension. I also >> > assume >> > I'll have to pass the connection string as a parameter to the report >> > using >> > URL access (I'll encrypt the connection don't worry ;-). >> > >> > My real question is how can I get this parameter from the URL to my >> > data >> > extension. Will I need to manually create this parameter for each >> > report? >> > Can I hard-code this parameter in the data-extension (in the >> > GetParameters >> > interface)? >> > >> > Any thoughts or suggestions would be much appreciated!!! >> > >> > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |