Combining Data from MySql and SQL2005

Problem:
I have a MySql database that houses status information that is linked
by a primary key from a SQL2005 database
Using Report Designer in VS2005, I have created 3 datasets pointing to
the 2 datasources. I need to create a relationship between the MySql
based datasets and a dataset that is based on a SQL2005 datasource, so
that I can have all of the data available in one table...
MySQL Table1
DocID, JobID, FKeyToSQL
MySQL Table2
JobID,Status
SQL2005 Data
PKeyToMySQL,Description,etc.
I would like to be able to see a status report that shows...
PKeyToMySQL,Description,JobID,Status
Thanks for any help.Can you try adding MySQL database as a LinkedServer?
"Kevin.Ailes@.gmail.com" wrote:
> Problem:
> I have a MySql database that houses status information that is linked
> by a primary key from a SQL2005 database
> Using Report Designer in VS2005, I have created 3 datasets pointing to
> the 2 datasources. I need to create a relationship between the MySql
> based datasets and a dataset that is based on a SQL2005 datasource, so
> that I can have all of the data available in one table...
> MySQL Table1
> DocID, JobID, FKeyToSQL
> MySQL Table2
> JobID,Status
> SQL2005 Data
> PKeyToMySQL,Description,etc.
> I would like to be able to see a status report that shows...
> PKeyToMySQL,Description,JobID,Status
> Thanks for any help.
>|||Jaraba wrote:
> Can you try adding MySQL database as a LinkedServer?
>
> "Kevin.Ailes@.gmail.com" wrote:
> > Problem:
> > I have a MySql database that houses status information that is linked
> > by a primary key from a SQL2005 database
> >
> > Using Report Designer in VS2005, I have created 3 datasets pointing to
> > the 2 datasources. I need to create a relationship between the MySql
> > based datasets and a dataset that is based on a SQL2005 datasource, so
> > that I can have all of the data available in one table...
> >
> > MySQL Table1
> > DocID, JobID, FKeyToSQL
> > MySQL Table2
> > JobID,Status
> >
> > SQL2005 Data
> > PKeyToMySQL,Description,etc.
> >
> > I would like to be able to see a status report that shows...
> >
> > PKeyToMySQL,Description,JobID,Status
> >
> > Thanks for any help.
> >
> >
I was able to embed a sub-report and submit a parameter to it and it
seems to have accomplished what I wanted. It seems like a round about
way to do it though. I would have thought there would be some way to
build relationships between datasets since you can't combine data from
2 different datasources into 1 dataset. Oh well, now if I could just
get this sub-report to be centered in the column.(TextAlign=center on
column properties doesn't seem to be working)

Collation problem with Oracle as linked server

I have the following configuration of the servers:
1.Oracle 9i (database codepage is CL8MSWIN1251)
2. MS SQL Server 2000 Standard Edition (server codepage is
Cyrillic_General_CI_AS)
Oracle is the linked server in the SQL. When I use
query "Select * from openquery(ORASRV, 'select field1,
field2 from table1')" I receive question symbols ("?")
instead of russian letters.
What should I do to correct this situation?Sergey,
I don't know Oracle (sorry) but the COLLATE command is used by SQL Server to
move characters into a particular collation.
I see that Cyrillic_General is considered to be in code page 1251, which is
what Oracle is using, so I don't know why you would be getting back only
?.
Try:
'select field1 COLLATE Cyrillic_General_CI_AS,
field2 COLLATE Cyrillic_General_CI_AS from table1')
See if that resolves the problem.
Russell Fields
"Sergey Khanzhin" <skhan@.mail.ru> wrote in message
news:0d7d01c36177$e7ba61e0$a101280a@.phx.gbl...
> I have the following configuration of the servers:
> 1.Oracle 9i (database codepage is CL8MSWIN1251)
> 2. MS SQL Server 2000 Standard Edition (server codepage is
> Cyrillic_General_CI_AS)
>
> Oracle is the linked server in the SQL. When I use
> query "Select * from openquery(ORASRV, 'select field1,
> field2 from table1')" I receive question symbols ("?")
> instead of russian letters.
>
> What should I do to correct this situation?
>