Can anyone point me in the right direction on the best way to take a SQL 6.5
sort order 44 database and move it and the data to a SQL 7.0 sort order 52
database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
sort order change has me dizzy...It's been a while since I did any 6.5 upgrades but if I remember correctly
you can't upgrade between sort orders. You have to BCP out all the data,
recreate the schema with the correct sort order on 7.0 and then BCP it back
in. Why upgrade to 7.0 anyway? Why not 2000?
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||Hi,
Upgrade wizard can not be used if the sort order id is different. The easy
way is :-
1. From SQL 6.5 Enterprise manager -- Object Menu -- Generate SQL Script.
2. Generate the script for all objects
3. COpy the Script to destination SQL 7 server
4. Create a new database in SQL 7 and execute the script. Ensure no error
occured
5. Use the DTS utility or BCP OUT/ IN to load the data into SQL 7
If you have huge volume of data it is recommended to go for BCP OUT, copy
the files to local SQl 7 server
and BCP IN.
Thanks
Hari
MCDBA
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||I'm planning to go to SQL 2K. However, I just needed help getting from one
sort order to another...
Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
Thanks to all for the help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> It's been a while since I did any 6.5 upgrades but if I remember correctly
> you can't upgrade between sort orders. You have to BCP out all the data,
> recreate the schema with the correct sort order on 7.0 and then BCP it
back
> in. Why upgrade to 7.0 anyway? Why not 2000?
> --
> Andrew J. Kelly SQL MVP
>
> "Nick" <Test@.test.com> wrote in message
> news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> 6.5
52[vbcol=seagreen]
>|||Nick,
There is no issue going from SQL6.5 to SQL2k. In fact, it is much
preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
It will be worthwhile to do some reading before you perform this work.
Check out these:
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/pr...oy/sqlugrd.mspx
Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
http://msdn.microsoft.com/library/d... />
e_5dko.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nick wrote:
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
>
> back
>
> 52
>
>|||According to one of the links Mark listed it seems you might be able to
upgrade to a different sort order after all. As always I would try it first
and make sure you have proper and tested backups before starting.
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:ebt5sjCZEHA.3476@.tk2msftngp13.phx.gbl...
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
> back
SQL[vbcol=seagreen]
order[vbcol=seagreen]
> 52
this[vbcol=seagreen]
>|||Mark,
This is the upgrade issue I was referring to.
http://support.microsoft.com/defaul...kb;en-us;818550
However, there are workarunds.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OfPYD%23CZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Nick,
> There is no issue going from SQL6.5 to SQL2k. In fact, it is much
> preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
> It will be worthwhile to do some reading before you perform this work.
> Check out these:
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/pr...oy/sqlugrd.mspx
> Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
>
http://msdn.microsoft.com/library/d...pgrade_5dko.asp
[vbcol=seagreen]
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Nick wrote:
one[vbcol=seagreen]
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
SQL[vbcol=seagreen]
this[vbcol=seagreen]sql
Showing posts with label order. Show all posts
Showing posts with label order. Show all posts
Monday, March 26, 2012
Moving a database from Sort order 44 to Sort order 52
Can anyone point me in the right direction on the best way to take a SQL 6.5
sort order 44 database and move it and the data to a SQL 7.0 sort order 52
database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
sort order change has me dizzy...It's been a while since I did any 6.5 upgrades but if I remember correctly
you can't upgrade between sort orders. You have to BCP out all the data,
recreate the schema with the correct sort order on 7.0 and then BCP it back
in. Why upgrade to 7.0 anyway? Why not 2000?
--
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||Hi,
Upgrade wizard can not be used if the sort order id is different. The easy
way is :-
1. From SQL 6.5 Enterprise manager -- Object Menu -- Generate SQL Script.
2. Generate the script for all objects
3. COpy the Script to destination SQL 7 server
4. Create a new database in SQL 7 and execute the script. Ensure no error
occured
5. Use the DTS utility or BCP OUT/ IN to load the data into SQL 7
If you have huge volume of data it is recommended to go for BCP OUT, copy
the files to local SQl 7 server
and BCP IN.
--
Thanks
Hari
MCDBA
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||I'm planning to go to SQL 2K. However, I just needed help getting from one
sort order to another...
Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
Thanks to all for the help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> It's been a while since I did any 6.5 upgrades but if I remember correctly
> you can't upgrade between sort orders. You have to BCP out all the data,
> recreate the schema with the correct sort order on 7.0 and then BCP it
back
> in. Why upgrade to 7.0 anyway? Why not 2000?
> --
> Andrew J. Kelly SQL MVP
>
> "Nick" <Test@.test.com> wrote in message
> news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Can anyone point me in the right direction on the best way to take a SQL
> 6.5
> > sort order 44 database and move it and the data to a SQL 7.0 sort order
52
> > database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> > sort order change has me dizzy...
> >
> >
>|||Nick,
There is no issue going from SQL6.5 to SQL2k. In fact, it is much
preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
It will be worthwhile to do some reading before you perform this work.
Check out these:
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_5dko.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nick wrote:
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
>>It's been a while since I did any 6.5 upgrades but if I remember correctly
>>you can't upgrade between sort orders. You have to BCP out all the data,
>>recreate the schema with the correct sort order on 7.0 and then BCP it
> back
>>in. Why upgrade to 7.0 anyway? Why not 2000?
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Nick" <Test@.test.com> wrote in message
>>news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
>>Can anyone point me in the right direction on the best way to take a SQL
>>6.5
>>sort order 44 database and move it and the data to a SQL 7.0 sort order
> 52
>>database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
>>sort order change has me dizzy...
>>
>>
>|||According to one of the links Mark listed it seems you might be able to
upgrade to a different sort order after all. As always I would try it first
and make sure you have proper and tested backups before starting.
--
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:ebt5sjCZEHA.3476@.tk2msftngp13.phx.gbl...
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> > It's been a while since I did any 6.5 upgrades but if I remember
correctly
> > you can't upgrade between sort orders. You have to BCP out all the
data,
> > recreate the schema with the correct sort order on 7.0 and then BCP it
> back
> > in. Why upgrade to 7.0 anyway? Why not 2000?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nick" <Test@.test.com> wrote in message
> > news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > Can anyone point me in the right direction on the best way to take a
SQL
> > 6.5
> > > sort order 44 database and move it and the data to a SQL 7.0 sort
order
> 52
> > > database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But
this
> > > sort order change has me dizzy...
> > >
> > >
> >
> >
>|||Mark,
This is the upgrade issue I was referring to.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818550
However, there are workarunds.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OfPYD%23CZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Nick,
> There is no issue going from SQL6.5 to SQL2k. In fact, it is much
> preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
> It will be worthwhile to do some reading before you perform this work.
> Check out these:
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
> Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_5dko.asp
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Nick wrote:
> > I'm planning to go to SQL 2K. However, I just needed help getting from
one
> > sort order to another...
> > Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> >
> > Thanks to all for the help.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> >
> >>It's been a while since I did any 6.5 upgrades but if I remember
correctly
> >>you can't upgrade between sort orders. You have to BCP out all the
data,
> >>recreate the schema with the correct sort order on 7.0 and then BCP it
> >
> > back
> >
> >>in. Why upgrade to 7.0 anyway? Why not 2000?
> >>
> >>--
> >>Andrew J. Kelly SQL MVP
> >>
> >>
> >>"Nick" <Test@.test.com> wrote in message
> >>news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> >>
> >>Can anyone point me in the right direction on the best way to take a
SQL
> >>
> >>6.5
> >>
> >>sort order 44 database and move it and the data to a SQL 7.0 sort order
> >
> > 52
> >
> >>database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But
this
> >>sort order change has me dizzy...
> >>
> >>
> >>
> >>
> >
> >
sort order 44 database and move it and the data to a SQL 7.0 sort order 52
database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
sort order change has me dizzy...It's been a while since I did any 6.5 upgrades but if I remember correctly
you can't upgrade between sort orders. You have to BCP out all the data,
recreate the schema with the correct sort order on 7.0 and then BCP it back
in. Why upgrade to 7.0 anyway? Why not 2000?
--
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||Hi,
Upgrade wizard can not be used if the sort order id is different. The easy
way is :-
1. From SQL 6.5 Enterprise manager -- Object Menu -- Generate SQL Script.
2. Generate the script for all objects
3. COpy the Script to destination SQL 7 server
4. Create a new database in SQL 7 and execute the script. Ensure no error
occured
5. Use the DTS utility or BCP OUT/ IN to load the data into SQL 7
If you have huge volume of data it is recommended to go for BCP OUT, copy
the files to local SQl 7 server
and BCP IN.
--
Thanks
Hari
MCDBA
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>|||I'm planning to go to SQL 2K. However, I just needed help getting from one
sort order to another...
Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
Thanks to all for the help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> It's been a while since I did any 6.5 upgrades but if I remember correctly
> you can't upgrade between sort orders. You have to BCP out all the data,
> recreate the schema with the correct sort order on 7.0 and then BCP it
back
> in. Why upgrade to 7.0 anyway? Why not 2000?
> --
> Andrew J. Kelly SQL MVP
>
> "Nick" <Test@.test.com> wrote in message
> news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Can anyone point me in the right direction on the best way to take a SQL
> 6.5
> > sort order 44 database and move it and the data to a SQL 7.0 sort order
52
> > database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> > sort order change has me dizzy...
> >
> >
>|||Nick,
There is no issue going from SQL6.5 to SQL2k. In fact, it is much
preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
It will be worthwhile to do some reading before you perform this work.
Check out these:
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_5dko.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nick wrote:
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
>>It's been a while since I did any 6.5 upgrades but if I remember correctly
>>you can't upgrade between sort orders. You have to BCP out all the data,
>>recreate the schema with the correct sort order on 7.0 and then BCP it
> back
>>in. Why upgrade to 7.0 anyway? Why not 2000?
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Nick" <Test@.test.com> wrote in message
>>news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
>>Can anyone point me in the right direction on the best way to take a SQL
>>6.5
>>sort order 44 database and move it and the data to a SQL 7.0 sort order
> 52
>>database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
>>sort order change has me dizzy...
>>
>>
>|||According to one of the links Mark listed it seems you might be able to
upgrade to a different sort order after all. As always I would try it first
and make sure you have proper and tested backups before starting.
--
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:ebt5sjCZEHA.3476@.tk2msftngp13.phx.gbl...
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> > It's been a while since I did any 6.5 upgrades but if I remember
correctly
> > you can't upgrade between sort orders. You have to BCP out all the
data,
> > recreate the schema with the correct sort order on 7.0 and then BCP it
> back
> > in. Why upgrade to 7.0 anyway? Why not 2000?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nick" <Test@.test.com> wrote in message
> > news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > Can anyone point me in the right direction on the best way to take a
SQL
> > 6.5
> > > sort order 44 database and move it and the data to a SQL 7.0 sort
order
> 52
> > > database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But
this
> > > sort order change has me dizzy...
> > >
> > >
> >
> >
>|||Mark,
This is the upgrade issue I was referring to.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818550
However, there are workarunds.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OfPYD%23CZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Nick,
> There is no issue going from SQL6.5 to SQL2k. In fact, it is much
> preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
> It will be worthwhile to do some reading before you perform this work.
> Check out these:
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
> Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_5dko.asp
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Nick wrote:
> > I'm planning to go to SQL 2K. However, I just needed help getting from
one
> > sort order to another...
> > Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> >
> > Thanks to all for the help.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> >
> >>It's been a while since I did any 6.5 upgrades but if I remember
correctly
> >>you can't upgrade between sort orders. You have to BCP out all the
data,
> >>recreate the schema with the correct sort order on 7.0 and then BCP it
> >
> > back
> >
> >>in. Why upgrade to 7.0 anyway? Why not 2000?
> >>
> >>--
> >>Andrew J. Kelly SQL MVP
> >>
> >>
> >>"Nick" <Test@.test.com> wrote in message
> >>news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> >>
> >>Can anyone point me in the right direction on the best way to take a
SQL
> >>
> >>6.5
> >>
> >>sort order 44 database and move it and the data to a SQL 7.0 sort order
> >
> > 52
> >
> >>database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But
this
> >>sort order change has me dizzy...
> >>
> >>
> >>
> >>
> >
> >
Moving a database from Sort order 44 to Sort order 52
Can anyone point me in the right direction on the best way to take a SQL 6.5
sort order 44 database and move it and the data to a SQL 7.0 sort order 52
database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
sort order change has me dizzy...
It's been a while since I did any 6.5 upgrades but if I remember correctly
you can't upgrade between sort orders. You have to BCP out all the data,
recreate the schema with the correct sort order on 7.0 and then BCP it back
in. Why upgrade to 7.0 anyway? Why not 2000?
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>
|||Hi,
Upgrade wizard can not be used if the sort order id is different. The easy
way is :-
1. From SQL 6.5 Enterprise manager -- Object Menu -- Generate SQL Script.
2. Generate the script for all objects
3. COpy the Script to destination SQL 7 server
4. Create a new database in SQL 7 and execute the script. Ensure no error
occured
5. Use the DTS utility or BCP OUT/ IN to load the data into SQL 7
If you have huge volume of data it is recommended to go for BCP OUT, copy
the files to local SQl 7 server
and BCP IN.
Thanks
Hari
MCDBA
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>
|||I'm planning to go to SQL 2K. However, I just needed help getting from one
sort order to another...
Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
Thanks to all for the help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> It's been a while since I did any 6.5 upgrades but if I remember correctly
> you can't upgrade between sort orders. You have to BCP out all the data,
> recreate the schema with the correct sort order on 7.0 and then BCP it
back[vbcol=seagreen]
> in. Why upgrade to 7.0 anyway? Why not 2000?
> --
> Andrew J. Kelly SQL MVP
>
> "Nick" <Test@.test.com> wrote in message
> news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> 6.5
52
>
|||Nick,
There is no issue going from SQL6.5 to SQL2k. In fact, it is much
preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
It will be worthwhile to do some reading before you perform this work.
Check out these:
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
http://msdn.microsoft.com/library/de...grade_5dko.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nick wrote:
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
>
> back
>
> 52
>
>
|||According to one of the links Mark listed it seems you might be able to
upgrade to a different sort order after all. As always I would try it first
and make sure you have proper and tested backups before starting.
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:ebt5sjCZEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
> back
SQL[vbcol=seagreen]
order[vbcol=seagreen]
> 52
this
>
|||Mark,
This is the upgrade issue I was referring to.
http://support.microsoft.com/default...b;en-us;818550
However, there are workarunds.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OfPYD%23CZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Nick,
> There is no issue going from SQL6.5 to SQL2k. In fact, it is much
> preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
> It will be worthwhile to do some reading before you perform this work.
> Check out these:
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
> Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
>
http://msdn.microsoft.com/library/de...grade_5dko.asp[vbcol=seagreen]
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Nick wrote:
one[vbcol=seagreen]
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
SQL[vbcol=seagreen]
this[vbcol=seagreen]
sort order 44 database and move it and the data to a SQL 7.0 sort order 52
database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
sort order change has me dizzy...
It's been a while since I did any 6.5 upgrades but if I remember correctly
you can't upgrade between sort orders. You have to BCP out all the data,
recreate the schema with the correct sort order on 7.0 and then BCP it back
in. Why upgrade to 7.0 anyway? Why not 2000?
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>
|||Hi,
Upgrade wizard can not be used if the sort order id is different. The easy
way is :-
1. From SQL 6.5 Enterprise manager -- Object Menu -- Generate SQL Script.
2. Generate the script for all objects
3. COpy the Script to destination SQL 7 server
4. Create a new database in SQL 7 and execute the script. Ensure no error
occured
5. Use the DTS utility or BCP OUT/ IN to load the data into SQL 7
If you have huge volume of data it is recommended to go for BCP OUT, copy
the files to local SQl 7 server
and BCP IN.
Thanks
Hari
MCDBA
"Nick" <Test@.test.com> wrote in message
news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on the best way to take a SQL
6.5
> sort order 44 database and move it and the data to a SQL 7.0 sort order 52
> database ? The 6.5 to 7.0 part I got down (SQL Upgrade wizard). But this
> sort order change has me dizzy...
>
|||I'm planning to go to SQL 2K. However, I just needed help getting from one
sort order to another...
Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
Thanks to all for the help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
> It's been a while since I did any 6.5 upgrades but if I remember correctly
> you can't upgrade between sort orders. You have to BCP out all the data,
> recreate the schema with the correct sort order on 7.0 and then BCP it
back[vbcol=seagreen]
> in. Why upgrade to 7.0 anyway? Why not 2000?
> --
> Andrew J. Kelly SQL MVP
>
> "Nick" <Test@.test.com> wrote in message
> news:elDz5$5YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> 6.5
52
>
|||Nick,
There is no issue going from SQL6.5 to SQL2k. In fact, it is much
preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
It will be worthwhile to do some reading before you perform this work.
Check out these:
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
http://msdn.microsoft.com/library/de...grade_5dko.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Nick wrote:
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
>
> back
>
> 52
>
>
|||According to one of the links Mark listed it seems you might be able to
upgrade to a different sort order after all. As always I would try it first
and make sure you have proper and tested backups before starting.
Andrew J. Kelly SQL MVP
"Nick" <Test@.test.com> wrote in message
news:ebt5sjCZEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I'm planning to go to SQL 2K. However, I just needed help getting from one
> sort order to another...
> Isn't there a problem upgrading from SQL 6.5 directly to SQL 2K ?
> Thanks to all for the help.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eeTEeX7YEHA.992@.TK2MSFTNGP10.phx.gbl...
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
> back
SQL[vbcol=seagreen]
order[vbcol=seagreen]
> 52
this
>
|||Mark,
This is the upgrade issue I was referring to.
http://support.microsoft.com/default...b;en-us;818550
However, there are workarunds.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OfPYD%23CZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Nick,
> There is no issue going from SQL6.5 to SQL2k. In fact, it is much
> preferred over SQL7. Ensure that you apply SQL2k SP3a after the upgrade.
> It will be worthwhile to do some reading before you perform this work.
> Check out these:
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
> Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
>
http://msdn.microsoft.com/library/de...grade_5dko.asp[vbcol=seagreen]
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Nick wrote:
one[vbcol=seagreen]
correctly[vbcol=seagreen]
data,[vbcol=seagreen]
SQL[vbcol=seagreen]
this[vbcol=seagreen]
Monday, February 20, 2012
move data from SQL 2005 to DB2/as400 using DB2OLEDB
I have a simple SSIS package that I want to move data from SQL 2005 to DB2/AS400. In order to use OLEDDB destination, I installed HIS2004 that provides DB2OLEDB support.
For simplicity and testing purpose, I created a connection pointing to a SQL table that contains only one ccolumn "last_name" of type "nvarchar(50)". This is my source, I then created another connection using DB2OLEDB pointing to a table on DB2 that contains only one field "name" of type "GRAPHIC(50)". This is my destination and the connection works fine to DB2 database. When I run the package, it has type conversion error as follows:
[OLE DB Destination [34]] Error: An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E07 Description: "Data or literal value could not be converted to the type of the column in the data source, and the provider was unable to determine which columns could not be converted. Data overflow or sign mismatch was not the cause.".
If I manually assign a vaule to the "last_name" using the "Derived Column" task like "Hello John", it just works fine.
Anyone knows what went wrong? Did I miss anything here? Thanks.
As a FYI: DTS works great in moving data from SQL 2000/2005 to DB2.
Your help will be appreciated.
There are quite some conceptual differences between SQLServer's nvarchar/nchar and DB2's vargraphic/graphic, for example, nvarchar/nchar are unicode columns, graphic/vargraphic however are not, unless you bind them with a unicode CCSID.
I was able to accomplish similar scenarios using DB2OLEDB provider by following the steps below:
1) Set DB2 connection's "HOST CCSID " property to "Unicode - UTF(8) [1208]"
2) At DB2 destination column, for example, for storing data from your nvarchar(50) input column, set the column type to Graphic/Vargraphic(100) CCSID 13488.
HTH
Wenyang
Subscribe to:
Posts (Atom)