Dynamic selection of flat file

I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.

How can I implement this?

Can I make a call to a package or a sql server job to run from .net 2.0?

Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...

Please help!!

How about a scheduled package that runs every minute, and if it finds a file it runs the load task, otherwise it just ends.

You could do something event driven with the WMI event task, but I dislike that since it does not tell you what file has been found, or try the File Watcher Task (http://www.sqlis.com/default.aspx?23)

|||

Appreciate your response...

I was wondering about creating a config file in the database and update the Connection string value everytime a new file is uploaded with the new file name

And then call the package to run by adding dts assembly to visual studio and doing package.load....

Any suggestion or comments on this as I really dont want to run this every min....

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.c..._procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.c..._procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
Jim
CREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>
|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
sql

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.com/tn_stored_procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hello,
>> I have a website that I designed with Visual Web Developer and SQL 2005
>> Express. Everything works fine, except that I had to write a seperate
>> query for each product category which is currently around 10. I expect
>> this to grow as we add new products and I can see this getting hard to
>> manage in the future.
>> Is there a way to write a dynamic query that will take a single parameter
>> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
>> but new to SQL 2005 Express.
>> Thanks
>> Jim
>>
>
>

dynamic export to excel from web

hi there,

this is my first time really using DTS packages and am trying to export some data to an excel file through a jsp page. This isn't my main problem tho...

the main problem is that i've coded the activeX script to dynamically name the file, as well as add the column headings in the excel file. The problem arises when I go to the data transform task and in the Destination tab, I can't select the worksheet that my code apparently creates.

Naturally it won't run because it will return me an error saying that the destination doesn't exist.

Any thoughts?

Thanks in advance.Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||That's great help! Thanks!

My next questions are:
1. Is it possible to pop-up a Save As dialog box when you run the DTS? I tried the msoFileDialogSaveAs but of course it doesn't work because it's not an Office app.

2. Is there a way to set global variables in jsp? I've seen code for asp, but haven't found any sample codes.

Thanks again.

Originally posted by SHICKS
Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||I don't know if JSP supports it, but we use ADO to save a stream of data as XML. Then using an XSL-T transform, we set the file up just about any way the user wants it (comm-delimited, fixed width, different delimiters, etc).

hmscott

Originally posted by SHICKS
I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||Shicks: The problem with creating in a csv file is that if you have a large number field and you try to open the file in Excel, it won't retain the format of the number. I've had this happen on many occasion.

Here's my current situation:
I've now been able to create a dts package that exports data to an excel file. For security reasons, I saved the package as a .dts file on our server to be called by the webserver when run.

The question is.. can I set the global variables of the package by referencing the file itself rather than the package on the server? Also, can this be done in java?

Dynamic display of SQL in Excel?

I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.See if under Data menu...Import External Data...New Web Query

Not sure of the particulars but it would get you started.

Originally posted by shadowplayer
I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.|||Thanks for your reply. That's exactly what I ended up doing. Found it by accident when I noticed the "little yellow box" that appeared when I cut & pasted data from the webpage into my spreadsheet.

I just saved the qry file and opened it in notepad to config the values.

This will save me HOURS of work.

VERY cool.

Thanks again for your reply!

Dynamic Dataset to Reports

Hi,
I have some selection criteria in my .aspx web page and based on the
selections, by using an stored procedure, I am generating the dataset. As of
now, I am displaying this in HTML table format.
Now I have designed the report using Reporting Services and have deployed it
onto the Report Server. I would like to know how do I pass this dataset from
my .aspx webpage onto the report and generate the same.
Could any one please let me know the links to materials explaining this
technique or any help would be highly appreciated.
Regards,
Sudhakara.T.P.Hi,
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link your dataset to a report at runtime by using ReportViewer.LocalReport
methods or at disign level too.
Hopefully I help you.
Arturo Carrión
at Teimpo Hard Development team
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> Hi,
> I have some selection criteria in my .aspx web page and based on the
> selections, by using an stored procedure, I am generating the dataset. As
> of
> now, I am displaying this in HTML table format.
> Now I have designed the report using Reporting Services and have deployed
> it
> onto the Report Server. I would like to know how do I pass this dataset
> from
> my .aspx webpage onto the report and generate the same.
> Could any one please let me know the links to materials explaining this
> technique or any help would be highly appreciated.
> Regards,
> Sudhakara.T.P.|||Hi,
Thank you very much for your reply.
Infact, I tried doing this earlier, but the problem with this is that, in
the preview mode, I am not getting the print button to print the button, even
tough I have set the showprintoption to true.
Any alternative solution for this?
Regards,
Sudhakara.T.P.
"tiempotecnologia@.newsgroup.nospam" wrote:
> Hi,
> Try to do this.
> 1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
> 2. Configure your ReportViewer to a LocalReport mode. This will allow you to
> link your dataset to a report at runtime by using ReportViewer.LocalReport
> methods or at disign level too.
> Hopefully I help you.
> Arturo Carrión
> at Teimpo Hard Development team
> "Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
> mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> > Hi,
> > I have some selection criteria in my .aspx web page and based on the
> > selections, by using an stored procedure, I am generating the dataset. As
> > of
> > now, I am displaying this in HTML table format.
> > Now I have designed the report using Reporting Services and have deployed
> > it
> > onto the Report Server. I would like to know how do I pass this dataset
> > from
> > my .aspx webpage onto the report and generate the same.
> > Could any one please let me know the links to materials explaining this
> > technique or any help would be highly appreciated.
> > Regards,
> > Sudhakara.T.P.
>
>

Dynamic database in Web Service

I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.

Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services

Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo

With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With

mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.

Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.

Good luck solving your problem.

Dynamic database creation

Hi All,

I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed

However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.

I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).

Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.

Thanks & regards,

Paul

Hi Paul,

As the simplest solution you can run the entire DB creation script through code, for e.g.:

using (SqlConnection cn = new SqlConnection("connectionString from config file"))
{
StringBuilder sql = new StringBuilder();
sql.Append(" use master; create database " + txtDatabase.Text);

SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cn.Open();
cmd.ExecuteNonQuery();
}

Hope this helps,

Vivek

|||

Hi Vivek,

Thanks for your reply. I haven't seen / used sql.Append before, so that definitely could be a way around the problem. I had hoped that there would be a way of running the .sql script, so that if I make any changes to the database layout then I can simply issue a new script, but it wouldm't be the end of the world if I have to issue a new vb file instead. I'll let you know how I get on over the next few days!

Thanks again,

Paul

Dynamic Connection string (config file)

Hi,

I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.

The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.

1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.

If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.

2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?

Sorry for the long post, but required to explain the scenario.

Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.

In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).

General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||

dynamic variable sql database Connection String

this is work easy

http://www.codeproject.com/useritems/PersistConnectionStrings.asp

Dynamic Connection string (config file)

Hi,

I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.

The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.

1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.

If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.

2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?

Sorry for the long post, but required to explain the scenario.

Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.

In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).

General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||

dynamic variable sql database Connection String

this is work easy

http://www.codeproject.com/useritems/PersistConnectionStrings.asp

Dynamic connect...?

I am trying to implement a web application user login system where every user is an Oracle user, so I can avoid having tables containing passwords and what not. In fact, having passwords in a table is not an option, even if they're encrypted. Anyway, I'm trying to set it up so that the login page is under a DAD that logs in as a user with rights to the login package only. Then, once the user has typed in their name and password and submitted, I want to then log them in as their user that has already been created in Oracle.

The first part is easy enough, but I have tried unsuccessfully to find some way to use dynamic sql to change users, such as EXECUTE IMMEDIATE 'CONNECT user/pass@.db'; and concatenating the appropriate values, but nothing seems to work.

I'm trying to avoid the basic authentication dialog box, as well as avoiding storing passwords in tables. I have looked into the custom authorization stuff provided by owa_custom, but I can't see any way to implement it with Oracle users. Any help on this would be greatly appreciated. Thanks!"connect" is a SQL*Plus command and hence cannot be executed dynamically. Other examples would be "show user", "desc table" etc. Only sql commands can be executed using dynamic sql.

But if you have the uid & pwd, can you not connect from your web application to see if the user is a valid database user or not ?|||Yeah, that is an option. I'm trying to avoid using JDBC or anything like that. However, if need be, I suppose that is something I can try. If there are any other ways to connect from the app, I would be interested in hearing about them. My experience with web application login systems is extremely limited, so any kind of help is greatly appreciated.

Dynamic building of reports in C# for ASP.NET

I saw a post earlier referencing a book on how to dynamically building reports. But does anybody know of a web site I can review to simply get an idea of how to do it to see if this is what we want to do vs simply using a data grid for reporting.

Thanks

We are currently doing this for an enterprise solution, however we have hit a couple of snags. See my other posts. As far as materials for this, there is very little. The best idea is to generate a few reports, and look at the RDL files that are generated. All you then have to do is create an engine to dynamically generate this content, which can then be executed.

Unfortuantely I am unable to share with you are current application code as it is confidential however I would be more than happy to answer a few help questions.

Matthew Christopher
.Net Developer / Consultant
Edmonton, AB, Canada
matt_chrs@.hotmail.com(nospam)

|||It's a lot of work. This article is a start.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/AdHocRepSr.asp