Report with 2 Databases?

Hello NG,
Is it possible to build a Report or rather a Connectionstring with two
Databases?
Thanks
ThomasHello,
I think it's not possible.
You can combine data from two databases only by using two datasets with two
separate db connections.
best regards,
Radoslaw Lebkowski
U¿ytkownik "Thomas Burger" <thomas.burger@.nospam.de> napisa³ w wiadomo¶ci
news:eYkdrjZNIHA.4948@.TK2MSFTNGP02.phx.gbl...
> Hello NG,
> Is it possible to build a Report or rather a Connectionstring with two
> Databases?
> Thanks
> Thomas
>|||try something like this
select *
from table1 t join
database2.dbo.table2 t2 on t.id=t2.id
That way they're in the same dataset and you're selecting from two
databases. Of course the connection string for table 1 would be the
first database.|||On Dec 3, 5:41 am, "Thomas Burger" <thomas.bur...@.nospam.de> wrote:
> Hello NG,
> Is it possible to build a Report or rather a Connectionstring with two
> Databases?
> Thanks
> Thomas
It is possible to define a single DataSet with an Expression
ConnectionString, so that the connection string is swapped based on
the report parameter. This can only be done with RS 2005 and up.
It would be easier to build a Linked Server and query against that in
my opinion.
-- Scott|||Ok thank you it works.
"SQL Guy" <aymantg@.gmail.com> schrieb im Newsbeitrag
news:81b6bd8f-ddee-4a73-a369-c570d3b3aad1@.l16g2000hsf.googlegroups.com...
> try something like this
> select *
> from table1 t join
> database2.dbo.table2 t2 on t.id=t2.id
>
> That way they're in the same dataset and you're selecting from two
> databases. Of course the connection string for table 1 would be the
> first database.|||Orne, could you show us an example. It's nice to have multiple
solutions to a problem. Also, I don't think a linked server would be
needed in this case since the databases are on the same server.|||On Dec 3, 10:11 am, SQL Guy <ayma...@.gmail.com> wrote:
> Orne, could you show us an example. It's nice to have multiple
> solutions to a problem. Also, I don't think a linked server would be
> needed in this case since the databases are on the same server.
Normally, I used a Shared Data Source for my Reports, but you don't
have to.
In your Data tab, click the Dataset Properties button [...] . You
should see an combo box that says Data Source, with a button next to
it [...]. Clicking this gets you a Data Source Properties window. If
you uncheck the "Use a shared data source reference" box, you can now
enter an explicit connection string for the report.
In the Connection String box, you can see a Expression button [fx].
Clicking this enters the expression builder. To access a SQL Server,
you can use a "Microsoft SQL Server" in the Type combobox, Integrated
Security for credentials, and use the following in the connection
string window:
="Data Source=YOURSERVER;Initial Catalog=YOURCATALOG"
Because it is an expression string, you can swap servers (instances)
with a parameter:
="Data Source=" & Parameters!ServerName.Value & ";Initial
Catalog=YOURCATALOG"
You lose the ability to preview your data with the [!] button, but if
you have an issue like we do here where we have a Production server
(with Now to -7 days of data) and an Archive server (with Yesterday to
-7 years of data) with the same schema names, etc etc then it could be
a solution for you. Same Fields returned so you use the same Report.
-- Scott|||Since it's a string, you can technically use a parameter to define the
database through Catalog=YOURCATALOG
Now that is really nice!

Report Viewer and Stored Procedure

I'm trying to build a report using the Report Viewer and a stored procedure. My sproc has paramerters and uses a #temp table. I have been able to get all the way to the point of adding fields to the RDLC , however, there is no table or fields in my dataset. Do I need to do something special that I'm missing to build the dataset with a sproc that uses a #temp table? My parameters are provided by fields on the aspx page where the Report Viewer is located.

I appreciate any help.

You should be able to execute the proc under the Data tab and see the results and make sure your proc is returning some data before you even start off building the report. So you konw what fields are available.sql

Report Viewer

Hi, I am new to Report Viewer. I created a report using typed dataset and the report works fine,... But I am trying to build a report with parameters.

Can anyone tell me the step by step procedure of how to do this on entering the search criteria and clicking the button to generate a report?

seehttp://forums.asp.net/p/1143091/1844030.aspx#1844030 for some help

Report task?

Hello,
I'm not sure if this one should be in this forum. Anyway...
I need to build some reports in MS Word format. Is there are any way to
do that? It can probably can be done in Reporting Services, but how I can merge
these two tools in one project (solution) for example?
Thanks.You can have a SSRS project and a SSIS project in the same solution.

As an aside...you can also share data sources between those projects which is a handy feature.

-Jamie|||But how to do that? I couldn't any way (via set of menues) to add SSRS to SSIS solution.|||

Metal_Fly wrote:

But how to do that? I couldn't any way (via set of menues) to add SSRS to SSIS solution.

There is no such thing as an "SSIS solution", just an "SSIS Project". A solution is not limited to a single tool/technology.

I imagine that you can see your SSIS project in Project Explorer but because you only have 1 project in your solution the solution root is not being displayed.

If you click File-->New-->Project the "New Project" dialog will be displayed. This has a dropdown labelled "Solution" in which you should select "Add To Solution". This will add your new project (which can be a SSRS project) to the existing solution and the solution root will be displayed as you would hope.

Hope this helps.

-Jamie|||Ok, thanks I got it. :))

So, back to my first question. How report can be converted to MS Word format (file)?|||Reporting Services does not natively support rendering reports as MS Word Documents. This is outlined in the following Q&A for Reporting Services 2005.
http://msdn.microsoft.com/SQL/community/webcasts/ReportingServicesQandA.aspx
You'll either need to code it yourself, purchase a third party component, or change to another output format.
The easiest solution would be to change to the PDF output format.
If you really need to have the report in Word format, you could try saving the report as MHTML and renaming the extension to ".doc". I won't guarantee that the report will be formatted correctly because your essentially opening a webpage in Word. This will only really work in Word >= 2000.
You could also try using Office Automation to build the report. Without attempting it myself, I would guess that this would not be easily integrated into a Report Services Solution, since you would have to build a .Net Assembly to query the DB and build the word doc.
Larry|||Ok... Thanks.
Last question. How I can include report part in SSIS package itself? So the report can be executed on package flow. Let's say I have 2 files. I do some data manipulation and then for every file I need report.|||

Metal_Fly wrote:

Ok... Thanks.
Last question. How I can include report part in SSIS package itself? So the report can be executed on package flow. Let's say I have 2 files. I do some data manipulation and then for every file I need report.

There is currently no out-of-the-box task that allows you to do this. You could roll your own or use a script task.

-Jamie|||Ok, if I use script task, how I reference to report?|||

You can't use SSIS to push data to SSRS. So you'll need to use a temporary table to store data, and then ren the report (e.g. using script task).

What is usually done is the opposite - schedule a SSRS report and use SSIS as data source for the report. This way you can avoid any temporary table.

|||Or, for SSRS reports, one can programmatically generate the reports as .RDL files. BOL contains a summary of this approach, along with a tutorial and code. Start at "Generating Report Definition Language Programmatically."

However, to return to your original request for Word reports, you do not want to automate an Office application in an unattended package. The better solution would be to generate WordML XML files. Although a WordML file is a frightening site to behold -- and many XML editors refuse to display and format it for you because of nested and recursive entries -- the bare minimum of XML elements required is fairly manageable.

You could accomplish this by selecting your data FOR XML and putting your conversion logic in an XSLT stylesheet, or more procedurally/verbosely by writing the data out one row and column at a time by using the .NET's XmlTextWriter. I have a sample using the latter approach to load a SQL table and write it out as an Excel XML worksheet, or a table in a Word XML document. Someday in "spare time" I'd like to adapt this as a custom SSIS destination.

-Doug|||Thanks everyone. I will try to to use BOL (Generating RDL programmatically).
Keep you informed on my progress/regress. :))|||Ok, here is another thing. One option for report in Preview mode
is to export it to Adobe format (.pdf).
How I can do that programatically? In VB.NET for examp.
|||You will need to invoke the Reporting Services web service and in particular its Render method, which accepts a format argument. See the Reporting Services Programming section of BOL, almost right under the Integration Services Programming section.

If you need additional details on Reporting Services programming, you'll probably get more detailed responses in a Reporting Services newsgroup.

Off to Thanksgiving dinner!

-Doug|||Seems too complicated and there is definately lack of info how to do that
on practice. But thanks anyway.