Default Collate Codes for SQL 7 and 2000

I am in the process of building a new sql 2000 server and will be transfering
a 2G database from a sql 7 server. I have already done this in development
and come across the collate problem where the 2000 version had a different
collate code than version 7, but both were installed with the default
settings.
I would like to know that the best options are, I assume I only have 2.
1. Make sure that the 2000 server is installed with the same collate code as
the sql 7 version.
2. Change all the collate codes when the sql 7 database has been restored.
Please can someone tell me which is the best option, and why the default
collate settings for the 2 versions are different, which causes all these
problems.
Many thanks - Richard
"Richard Badge" <Richard Badge@.discussions.microsoft.com> wrote in message
news:BC784CC8-6DAF-43AA-8116-D05E2D51A9BA@.microsoft.com...
> 1. Make sure that the 2000 server is installed with the same collate code
> as
> the sql 7 version.
You can do an upgrade in place and it will pick the same collation as 7.0.

> 2. Change all the collate codes when the sql 7 database has been restored.
> Please can someone tell me which is the best option,
The best is whichever your business needs say is the best. By moving to the
new collation, you will have better support for more things.
Read up on the collations and what they have to offer in the BOL. This
should help you with your decisions.

> and why the default collate settings for the 2 versions are different,
> which causes all these
> problems.
I can't speak for Microsoft, however, the new features and additions that
can be found in the newer collations give you more flexibility. If you go
all the way back to early versions of SQL Server, the default sequence was
code page 437 (US English). This was very limited in it's support for
other languages etc.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Default Collate Codes for SQL 7 and 2000

I am in the process of building a new sql 2000 server and will be transferin
g
a 2G database from a sql 7 server. I have already done this in development
and come across the collate problem where the 2000 version had a different
collate code than version 7, but both were installed with the default
settings.
I would like to know that the best options are, I assume I only have 2.
1. Make sure that the 2000 server is installed with the same collate code as
the sql 7 version.
2. Change all the collate codes when the sql 7 database has been restored.
Please can someone tell me which is the best option, and why the default
collate settings for the 2 versions are different, which causes all these
problems.
Many thanks - Richard"Richard Badge" <Richard Badge@.discussions.microsoft.com> wrote in message
news:BC784CC8-6DAF-43AA-8116-D05E2D51A9BA@.microsoft.com...
> 1. Make sure that the 2000 server is installed with the same collate code
> as
> the sql 7 version.
You can do an upgrade in place and it will pick the same collation as 7.0.

> 2. Change all the collate codes when the sql 7 database has been restored.
> Please can someone tell me which is the best option,
The best is whichever your business needs say is the best. By moving to the
new collation, you will have better support for more things.
Read up on the collations and what they have to offer in the BOL. This
should help you with your decisions.

> and why the default collate settings for the 2 versions are different,
> which causes all these
> problems.
I can't speak for Microsoft, however, the new features and additions that
can be found in the newer collations give you more flexibility. If you go
all the way back to early versions of SQL Server, the default sequence was
code page 437 (US English). This was very limited in it's support for
other languages etc.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Default Collate Codes for SQL 7 and 2000

I am in the process of building a new sql 2000 server and will be transfering
a 2G database from a sql 7 server. I have already done this in development
and come across the collate problem where the 2000 version had a different
collate code than version 7, but both were installed with the default
settings.
I would like to know that the best options are, I assume I only have 2.
1. Make sure that the 2000 server is installed with the same collate code as
the sql 7 version.
2. Change all the collate codes when the sql 7 database has been restored.
Please can someone tell me which is the best option, and why the default
collate settings for the 2 versions are different, which causes all these
problems.
Many thanks - Richard"Richard Badge" <Richard Badge@.discussions.microsoft.com> wrote in message
news:BC784CC8-6DAF-43AA-8116-D05E2D51A9BA@.microsoft.com...
> 1. Make sure that the 2000 server is installed with the same collate code
> as
> the sql 7 version.
You can do an upgrade in place and it will pick the same collation as 7.0.
> 2. Change all the collate codes when the sql 7 database has been restored.
> Please can someone tell me which is the best option,
The best is whichever your business needs say is the best. By moving to the
new collation, you will have better support for more things.
Read up on the collations and what they have to offer in the BOL. This
should help you with your decisions.
> and why the default collate settings for the 2 versions are different,
> which causes all these
> problems.
I can't speak for Microsoft, however, the new features and additions that
can be found in the newer collations give you more flexibility. If you go
all the way back to early versions of SQL Server, the default sequence was
code page 437 (US English). This was very limited in it's support for
other languages etc.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Debugging a customponent used in a Integration Services project

Hi there
I'm building a SQL Server integration services project with a custom component. I created the custom component as a separate C# library project and compiled it. With the help of some online resources I was able to make it appear in the Dataflow Transformations toolbox items of Integration Services IDE. But this component is throwing some errors. My question how do I setup the environment such that I can set break points and debug the component's source while debugging the integration services project?

Any help will be greatly appretiated.

Thanks in advance.

Arun M

Hi Arun,

open the SSIS designer with your component in it, open another VS window with your C# project where you would like to debug. For design-time debugging, attach the VS debugger (using Debug\Attach to Process...) to devenv.exe process that hosts the SSIS designer. For runtime debugging, attach your debugger to DtsDebugHost.exe.

HTH.

Thanks,

|||Here's another way to debug things, a little different than the way Bob suggested above:

Setup the Custom task to run using DTEXEC as it's startup
program. In the CommandLine arguments, pass in the
filename of the DTSX package that includes the custom
task. Then start the debugger and it should hit your
breakpoints...|||

I am trying to use the second method of DTEXEC but I am getting a "The product level is insufficient for component ..." message on one of my machines and ONLY ONE of them.

The notable difference is that I am connecting to my local machine running the databases VS a remote database. The one using the remote database is failing with the above message and the local database machine debugs just fine.

Note: Running the package with the custom component through VS works just fine.

I am going to attempt the DtsHost... item above but I don't like that one as you have to close the VS environment over and over to do testing of changes!

|||This issue has already been discussed in this forum. To use dtexec you must have a server OS.|||

Then why would I get one working and not the other when they are both on Windows XP SP2?

Is it that DTEXEC can only connect remotely while on a server OS? That seems rather strange to me...

|||No you must have installed SSIS. DTEXEC runs fine on all OS's that SSIS runs on, which includes XP. The message is usally encountered when using the Wizard, and only having installed workstation components, and not the SSIS server components.|||

I am pretty sure I have it all installed and not just the workstation components as I am developing and running on the machine itself.

The component runs fine in BIDS both at design and run-time but only fails with the message when I try to run using DTEXEC.

Darren: Thank you for the pointer to the Regex example that was recently released. I am building a component that will use Regex to allow validation with custom messages logged, data type reformatting based on the matches (e.g. yyyyMMdd -> ^(\d{4})(\d{2})(\d{2})$ would validate the data and $1-$2-$3 would convert the data to a data string that can be type cast to DT_DBTIMESTAMP). Makes the date conversion much simpler.

Debugging a customponent used in a Integration Services project

Hi there
I'm building a SQL Server integration services project with a custom component. I created the custom component as a separate C# library project and compiled it. With the help of some online resources I was able to make it appear in the Dataflow Transformations toolbox items of Integration Services IDE. But this component is throwing some errors. My question how do I setup the environment such that I can set break points and debug the component's source while debugging the integration services project?

Any help will be greatly appretiated.

Thanks in advance.

Arun M

Hi Arun,

open the SSIS designer with your component in it, open another VS window with your C# project where you would like to debug. For design-time debugging, attach the VS debugger (using Debug\Attach to Process...) to devenv.exe process that hosts the SSIS designer. For runtime debugging, attach your debugger to DtsDebugHost.exe.

HTH.

Thanks,

|||Here's another way to debug things, a little different than the way Bob suggested above:

Setup the Custom task to run using DTEXEC as it's startup
program. In the CommandLine arguments, pass in the
filename of the DTSX package that includes the custom
task. Then start the debugger and it should hit your
breakpoints...
|||

I am trying to use the second method of DTEXEC but I am getting a "The product level is insufficient for component ..." message on one of my machines and ONLY ONE of them.

The notable difference is that I am connecting to my local machine running the databases VS a remote database. The one using the remote database is failing with the above message and the local database machine debugs just fine.

Note: Running the package with the custom component through VS works just fine.

I am going to attempt the DtsHost... item above but I don't like that one as you have to close the VS environment over and over to do testing of changes!

|||This issue has already been discussed in this forum. To use dtexec you must have a server OS.
|||

Then why would I get one working and not the other when they are both on Windows XP SP2?

Is it that DTEXEC can only connect remotely while on a server OS? That seems rather strange to me...

|||No you must have installed SSIS. DTEXEC runs fine on all OS's that SSIS runs on, which includes XP. The message is usally encountered when using the Wizard, and only having installed workstation components, and not the SSIS server components.|||

I am pretty sure I have it all installed and not just the workstation components as I am developing and running on the machine itself.

The component runs fine in BIDS both at design and run-time but only fails with the message when I try to run using DTEXEC.

Darren: Thank you for the pointer to the Regex example that was recently released. I am building a component that will use Regex to allow validation with custom messages logged, data type reformatting based on the matches (e.g. yyyyMMdd -> ^(\d{4})(\d{2})(\d{2})$ would validate the data and $1-$2-$3 would convert the data to a data string that can be type cast to DT_DBTIMESTAMP). Makes the date conversion much simpler.