How do I join tables from different databases (under same server though)

Hey, I have two databases (db1 and db2) under the same server. How do I combine tables from both of them?

I searched the forum and tried

SELECT

t1.col1

,t2.col2

FROM

db1.dbo.table1 t1

INNER JOIN

db2.dbo.table2 t2

ON t1.somecol = t2.somecol

I get Error Message: Invalid object name 'db1.dbo.table1
Error source: .Net SqlClient Data Provider

I read somewhere that the connectionstring could cause the problem since I'm trying to access two databases. I'm using SQL Server Express

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db1.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT t1.col1, t2.col2 FROM db1.dbo.table1 t1 INNER JOIN db2.dbo.table2 t2 ON t1.somecol = t2.somecol"></asp:SqlDataSource>

Anyway, how can I solve this?ahhh comon, someone must know a solution|||

I guess that may because the 2 databases can not 'recognize' each other. Have you ever tried register the 2 databases into SQL Express by Detach/Attach? You can firstly detach the databases from your project, and then attach them in Management Studio:

http://msdn2.microsoft.com/en-us/library/ms190209.aspx

|||thanks for the idea, i'll look into it and see what I can find out.sql

How do I deploy XSL file to Reports Server?

Hi:
I have developed a report that uses and XSLT file to format data found in a
CLOB field from our DB2 database. It works just great in Report Designer,
but when I deploy to the Reporting Server, it does not render the data from
the CLOB but rather an error.
The value of the text box is
= Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
I have created a C:\XSL directory on the Reports Server and placed a copy of
the ClearingHouseException.xsl file in it, but it just isn't working.
What am I doing wrong?
Thanks.
Bruce.If this works from development and not from production then you are hitting
a security problem. Could be one of two places, either the rights on the
c:\xsl directory OR (more likely) the rights for the code you are executing.
By default RS limits what code can do to only safe things, which accessing
the file system is definitely not safe. I suggest reading up on what you
would have to do if you were using your own assemblies (this isn't your own
assembly but it is the same thing). You might need to have thise be your own
assembly instead of code behind in order to get the proper security rights
but I am not sure. I feel pretty confident it is one of these two things.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> Hi:
> I have developed a report that uses and XSLT file to format data found in
a
> CLOB field from our DB2 database. It works just great in Report Designer,
> but when I deploy to the Reporting Server, it does not render the data
from
> the CLOB but rather an error.
> The value of the text box is
> = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> I have created a C:\XSL directory on the Reports Server and placed a copy
of
> the ClearingHouseException.xsl file in it, but it just isn't working.
> What am I doing wrong?
> Thanks.
> Bruce.|||Hi Bruce:
Thanks for the reply, although the "assembly" approach seems like a lot of
work to get right...
I did read in one book on RS that you could upload the XSL file to the same
folder in Report Manager and it could be accessed from there. Of course, it
didn't say how the report would call the file, ie: the normal C:\ path
wouldn't work. Is it possible to point to the file in the Report Manger
folder?
Thanks again.
Bruce.
"Bruce L-C [MVP]" wrote:
> If this works from development and not from production then you are hitting
> a security problem. Could be one of two places, either the rights on the
> c:\xsl directory OR (more likely) the rights for the code you are executing.
> By default RS limits what code can do to only safe things, which accessing
> the file system is definitely not safe. I suggest reading up on what you
> would have to do if you were using your own assemblies (this isn't your own
> assembly but it is the same thing). You might need to have thise be your own
> assembly instead of code behind in order to get the proper security rights
> but I am not sure. I feel pretty confident it is one of these two things.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> > Hi:
> >
> > I have developed a report that uses and XSLT file to format data found in
> a
> > CLOB field from our DB2 database. It works just great in Report Designer,
> > but when I deploy to the Reporting Server, it does not render the data
> from
> > the CLOB but rather an error.
> >
> > The value of the text box is
> >
> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> >
> > I have created a C:\XSL directory on the Reports Server and placed a copy
> of
> > the ClearingHouseException.xsl file in it, but it just isn't working.
> >
> > What am I doing wrong?
> >
> > Thanks.
> >
> > Bruce.
>
>|||Sorry, I haven't tried to get to a file. Since I haven't had to mess with
the security stuff for assemblies I can't help too much there (or with
getting to the file). It does make sense for security reasons that they
would not by default allow access to the hard drive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:EE73655D-F3D0-4525-A03B-04DE703FB560@.microsoft.com...
> Hi Bruce:
> Thanks for the reply, although the "assembly" approach seems like a lot of
> work to get right...
> I did read in one book on RS that you could upload the XSL file to the
> same
> folder in Report Manager and it could be accessed from there. Of course,
> it
> didn't say how the report would call the file, ie: the normal C:\ path
> wouldn't work. Is it possible to point to the file in the Report Manger
> folder?
> Thanks again.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> If this works from development and not from production then you are
>> hitting
>> a security problem. Could be one of two places, either the rights on the
>> c:\xsl directory OR (more likely) the rights for the code you are
>> executing.
>> By default RS limits what code can do to only safe things, which
>> accessing
>> the file system is definitely not safe. I suggest reading up on what you
>> would have to do if you were using your own assemblies (this isn't your
>> own
>> assembly but it is the same thing). You might need to have thise be your
>> own
>> assembly instead of code behind in order to get the proper security
>> rights
>> but I am not sure. I feel pretty confident it is one of these two things.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
>> > Hi:
>> >
>> > I have developed a report that uses and XSLT file to format data found
>> > in
>> a
>> > CLOB field from our DB2 database. It works just great in Report
>> > Designer,
>> > but when I deploy to the Reporting Server, it does not render the data
>> from
>> > the CLOB but rather an error.
>> >
>> > The value of the text box is
>> >
>> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
>> >
>> > I have created a C:\XSL directory on the Reports Server and placed a
>> > copy
>> of
>> > the ClearingHouseException.xsl file in it, but it just isn't working.
>> >
>> > What am I doing wrong?
>> >
>> > Thanks.
>> >
>> > Bruce.
>>