Conversion from char to Nchar

Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.If you have enough disk space, I strongly recommend:

1) Use SQL Enterprise Manager to script your old database
2) Edit the script to change CHAR to NCHAR
3) Edit the script to change VARCHAR to NVARCHAR
4) Edit the script to change TEXT to NTEXT
5) Create a new database
6) Play the script into the new database using SQL Query Analyzer
7) Copy the data from the old database to the new one

The down side is that this new database can take about 2.5 times as much disk space as your old database, so you have to have quite a bit of space free to make this happen.

There are other ways to do this conversion, but they are a lot more complicated. If you have the disk space, this is a much simpler way to do the conversion.

-PatP|||Hello there,

Thanks very much for your speedy reply!
Excuse me if i sound like a complete beginner, but I am not really experienced with MS Server, as a result, I'm not aware of whether my approach to scripting the database is correct or not. Is it, right click on the existing database -> All new tasks -> Generate SQL Script , and if so,then , General->Show All ;Options->All checkboxes selected??
When the script is ready, i try to execute it on the 'master' DB, after renaming the existing database.
But I get errors and it doesn't execute saying it that it doesn't recognise the user defined data types and roles (while giving their names)
How can I take care of this?
Lastly, after changing the concerned fields, i.e, char to Nchar, varchar to Nvarchar and text to Ntext, which tool is used to transfer the data from the old DB to the new empty one?

Thanks again!
Sheetal.|||That's Ok, everybody has to start somewhere!

First, create a new database. If you prefer working in a GUI environment, you can do this using SQL Enterprise Manager.

Next, open SQL Query Analyzer. Connect to the database, then open the edited script. Click on the "play" button in the toolbar, or just hit Ctrl-E to execute. The script should run in the new database with no error messages.

The simplest way to move the data is probably to use the DTS Wizard. You can get to it by right clicking the Data Transformation Service in SQL Enterprise Manager.

-PatP|||Hello Pat :-)

Many thanks again for your speedy (and warm) reply!
I tried using the SQL Enterprise Manager to create a new database, and then play into it the edited version of the old DB Script, but it gives me really wierd errors everytime, like a certain table or type doesn't exist(though it does exist in the original DB), and there's no way I can find out what's going on with the automated script generation. Any tips?
If not, I wrote a piece of code which works perfectly in converting Char to Nchar :
ALTER TABLE t_nm_reports
DROP CONSTRAINT UQ__t_nm_reports__5D60DB10

ALTER TABLE t_nm_reports
ALTER COLUMN nm_dw_name NCHAR(50) /*the column which needs to be changed*/

ALTER TABLE t_nm_reports
ADD CONSTRAINT UQ__t_nm_reports__5D60DB10
UNIQUE (nm_dw_name);

But, this is a very rough and basic way of solving the problem, done manually for each concerned table. I'm looking for a piece of code which can search for the concerned tables and perform the query, all in one program. Is that possible?
Thank you for your time!

Sheetal.|||I'm going to be really, really detailed about this. Please don't be offended, I'm trying to cover everything, not insult anyone!

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all objects checkbox
9) Click the Options tab
10) In the security section:
11) Click the checkbox for Script database users and database roles
12) Click the checkbox for Script object-level permissions
13) In the Table Scripting section:
14) Click all four checkboxes
15) Click the Ok button
16) Make the appropriate choices for saving the file

This should get you a script that includes everything, in the correct order to rebuild the schema from scratch. You should be able to edit this script to change CHAR to NCHAR and VARCHAR to NVARCHAR without any problems (or at least I can't think of any).

While you can hunt down all of the "problem child" columns and fix them as you did in your example, it is a lot more work and I'm not completely comfortable that you'll get what you really want, especially from a performance standpoint.

-PatP|||Hello there,
I'm sorry but it just doesn't seem to work :-(
Everytime I try to execute the edited script in the query analyzer(against the new empty database or the source database), I get errors like a particular table/sp doesn't exist (or incorrect syntax), even though it does exist before the execution, but seems to get dropped/deleted during the execution from the source database.
Thanks for ur time, any other suggestions would be highly appreciated!

Sheetal.|||If you have foreign key definitions (look for the keyword FOREIGN to find them), you will want to move them to the end of the script. This is due to the fact that the scripting engine doesn't always respect the "dependance sequence" of the tables, so the tables aren't always created in the same order that they were originally created.

-PatP|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.As long as you skip the DROPs after the first run!

-PatP|||So, the scripting was completed successfully,with everything done as specified except that in the Options tab, the MS-DOS(OEM) format was selected, and not the Windows ANSI format.It does give some errors because of dependent objects, which when moved before the creation of the calling procedure, works fine. But it can be a hassle if they are many in number(as in my case). Any workarounds this problem??

Also, an important question for me is to know how to find and replace a certain string, eg changing char to nchar while using the query analyzer's "Replace", passes thru every string named 'varchar' or 'character' as well, so its very time consuming.I've tried using '(space)char', but its not foolproof either. Is there any way I can search for regular expressions automatically, as doin it manually in a huge database script is not very practical. Like for eg, creating a .bat file and using FINDSTR? if I'm on the right track, please guide me further!
Thanks!
Sheetal.|||Let's try a different approach...

Modify the directions for step #14 to exclude the last check box for Primary, Foreign, and Check constraints. Build the script that way (without the constraints).

Build a second script with only the constraints.

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all tables checkbox
9) Click the Formatting tab
10) Clear all of the check boxes
11) Click the Options tab
12) In the Table Scripting section:
13) Click only the PRIMARY keys, FOREIGN KEYS, and check constraints checkbox
14) Click the Ok button
15) Make the appropriate choices for saving the file

Now you should be able to play the first script, then play the second script without running into the dependancy problems you've been having.

In terms of better editing tools, I'd use an editor that recognizes regular expressions (Elvis is free, there are lots of others), or a tool like Perl that was made for those kinds of tasks.

-PatP

Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>

Contains Clause and Single Characters

I have a commerce server database that uses a full text index to search
product numbers and descriptions. My issue is that some of my product
numbers contain single characters, "EMT 1" for example, and the Contains
clause does not seem to treat this phrase as an exact match search when I do
something like
select *
from <table>
where contains(*, '"EMT 1"')
It finds that product and a number of other products that happen to contain
EMT in the description. Is there any way that I can get the index to
recognize the entire string? It seems that it treats the "1" as a noise word
and just drops it from the criteria.
I have tried stripping out all puncuation and white space from the product
number and adding that value to the FT Index but it complicates any Keword
searches that may have valid white space in that I can not universally strip
white space out of the criteria that the user entered to try to get a match.
Chris
cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:

> I have a commerce server database that uses a full text index to search
> product numbers and descriptions. My issue is that some of my product
> numbers contain single characters, "EMT 1" for example, and the Contains
> clause does not seem to treat this phrase as an exact match search when I
> do something like
> select *
> from <table>
> where contains(*, '"EMT 1"')
> It finds that product and a number of other products that happen to
> contain EMT in the description. Is there any way that I can get the index
> to recognize the entire string? It seems that it treats the "1" as a
> noise word and just drops it from the criteria.
> I have tried stripping out all puncuation and white space from the product
> number and adding that value to the FT Index but it complicates any Keword
> searches that may have valid white space in that I can not universally
> strip white space out of the criteria that the user entered to try to get
> a match.
Edit the noise word file for the language you are using, removing everything
but leaving a single line with a space on it. Then rebuild the index -
everything will now be indexed.
Dan
|||Works perfectly, thanks!
"Daniel Crichton" wrote:

> cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:
>
> Edit the noise word file for the language you are using, removing everything
> but leaving a single line with a space on it. Then rebuild the index -
> everything will now be indexed.
> Dan
>
>

constraints across normalized tables

Dear Experts,

When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.

For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.

I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?

Thanks.Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.
>
For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.
>
I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?

I wouldn't call that particular example normalistion, but rather
vertical partitioning. :-)

If you have

CREATE TABLE leftside (id int NOT NULL PRIMARY KEY,
name varchar(34) NOT NULL)
CREATE TABLE rightside (id int NOT NULL PRIMARY KEY,
salary int NOT NULL)

You can use a foreign-key constraint to ensure that an id is not
inserted into leftside, if it's not also in rightside.

However, there is no way that you can ensure that there are rows in
both table, at least not with useful data. Not with triggers, not
with constraints, since there are not any commit-time versions of
the same.

The best you can do is to have a trigger to cascade inserts with dummy
values into the other table.

More generally, cross-table checks you have to do with triggers,
with the exception of foriegn keys. (OK, you can use user-defined
functions that you call from you CHECK constraints, but that can
be very costly performancewise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>When I use a single table I use constraints to enforce my business logic, but what do I do when I normalize a single table into multip can easilyle tables. <<

The same thing -- use constraints to enforce business logic.

Quote:

Originally Posted by

Quote:

Originally Posted by

>For example, imagine that my initial table has the columns ID, Name, Salary with the constraint that Salary is not NULL. <<

Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Now imagine that I break this into two tables, one with ID and Name and another with ID and Salary. <<

We fire you for not having taken a data modeling course and knowing
about attribute splitting. The rule is that all the attributes of an
entity stay in one and only one table. Can I assume that this is
Personnel (emp_id, emp_name, salary_amt) and that the three are
required attributes?

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would like to have a constraint that prevents the creation of a row with (ID,Name) in the first table unless a corresponding row in the second table is also created. <<

You do not need that if you keep your **already normalized** table.

I am guessing that you are really trying to ask about DRI actions, but
don't have enough background to know the concepts of normalization and
how they work together.|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>For example, imagine that my initial table has the columns ID, Name,

Salary with the constraint that Salary is not NULL. <<

Quote:

Originally Posted by

>
Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

Joe, in your endeavour to spit on as many SQL users as posssible, can't
you just be a little bit discriminant? Emin's question was apparently of
a generic nature, and his table was just an exmaple. For a table that
is just to be used in an example, and nothing else I think "id" is a
perfect name. There is no reason to call it CheeseID, AppleID, or
WhateverID; that would only distract attention from what the example
is intended to discuss.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>Emin's question was apparently of a generic nature, and his table was just an example. <<

Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.

Quote:

Originally Posted by

Quote:

Originally Posted by

> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.

If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

Quote:

Originally Posted by

>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Emin wrote:

Quote:

Originally Posted by

>
Dear Experts,
>
When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.
>
For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.
>
I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?
>
Thanks.

Your example doesn't make sense. Following the normalization rules, you
would not split this initial table, because there is no reason to split
it. Why do you want to split this table?

But even if you had an example: it is usually not necessary to go beyond
the 3rd normal form when modelling for an RDBMS. It just add a lot of
overhead and complexity to your queries.

Gert-Jan|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Emin's question was apparently of a generic nature, and his table was just an example. <<

>
Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

>
So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.
>
If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

You need a good cop and a bad cop. Except in Alpha Complex, where
the positions have been combined due to budgetary restraints. :)

consolidating folders

I have 8 folders with reports in each folder. I want to move those 8 folders into a single folder. Is there a way to do this without having to create the folder hierarchy from scratch and then upload all the .RDL's again? Thanks for your help!

frank

You can do this through the Report Manager UI using the move button. You'll need to click the "show details" button on the right hand side of the toolbar. You can do this programmatically using the MoveItem SOAP API.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||thanks Lukasz. I actually just saw it.