Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
Sue
If you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
sqlsql

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
--
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

conversion from 'text' to 'int' is not supported

Changing the data type of a column (from text to int) in a saved table the
following error occurs:
conversion from 'text' to 'int' is not supportedHi,
yes this is correct, SQL Server does not allow text-> int on int->text
see the explicit and implicit data type conversions chart in bol (see the
CONVERT function)
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"jrb" <jrb@.discussions.microsoft.com> wrote in message
news:5CB28555-F612-4318-AA60-7290E0DAF26D@.microsoft.com...
> Changing the data type of a column (from text to int) in a saved table the
> following error occurs:
> conversion from 'text' to 'int' is not supported
>

conversion from 'text' to 'int' is not supported

Changing the data type of a column (from text to int) in a saved table the
following error occurs:
conversion from 'text' to 'int' is not supportedHi,
yes this is correct, SQL Server does not allow text-> int on int->text
see the explicit and implicit data type conversions chart in bol (see the
CONVERT function)
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"jrb" <jrb@.discussions.microsoft.com> wrote in message
news:5CB28555-F612-4318-AA60-7290E0DAF26D@.microsoft.com...
> Changing the data type of a column (from text to int) in a saved table the
> following error occurs:
> conversion from 'text' to 'int' is not supported
>

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag