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)

columns_updated compatibility between sql2000 and sql 2005

Hi,
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>