precautions for conversion

Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
SridharIf you are changing the column datatypes on a big table, make sure you have
enough space on the log drive, to allow the log to grow.
If you have any constraints (like defaults, indexes, primary and foreign
keys etc.) defined on the columns being altered, make cure you remove those
before the alter and readd them later.
if the tables are related using multiple foreign keys, then you'll have to
drop the constraints in the right order and readd them in the right order as
well.
Make sure you test your scripts in your test environment.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
Sridhar|||Thanks..I have taken care of the dropping
constraints ,indexes and adding them in ordered way in my
script...i am worried about the log and data file size
growth...
>--Original Message--
>If you are changing the column datatypes on a big table,
make sure you have
>enough space on the log drive, to allow the log to grow.
>If you have any constraints (like defaults, indexes,
primary and foreign
>keys etc.) defined on the columns being altered, make
cure you remove those
>before the alter and readd them later.
>if the tables are related using multiple foreign keys,
then you'll have to
>drop the constraints in the right order and readd them in
the right order as
>well.
>Make sure you test your scripts in your test environment.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
>Hi,
>I am in the process of converting all char and varchar
>colums to nchar /nvarchar ...I am done with all the
>scripts required for conversion..Can someone help me in
>precautions to be taken care while doing this upgrade
>like data file size , log file size etc......
>
>Sridhar
>
>.
>

precautions for conversion

Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
Sridhar
If you are changing the column datatypes on a big table, make sure you have
enough space on the log drive, to allow the log to grow.
If you have any constraints (like defaults, indexes, primary and foreign
keys etc.) defined on the columns being altered, make cure you remove those
before the alter and readd them later.
if the tables are related using multiple foreign keys, then you'll have to
drop the constraints in the right order and readd them in the right order as
well.
Make sure you test your scripts in your test environment.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
Sridhar
|||Thanks..I have taken care of the dropping
constraints ,indexes and adding them in ordered way in my
script...i am worried about the log and data file size
growth...
>--Original Message--
>If you are changing the column datatypes on a big table,
make sure you have
>enough space on the log drive, to allow the log to grow.
>If you have any constraints (like defaults, indexes,
primary and foreign
>keys etc.) defined on the columns being altered, make
cure you remove those
>before the alter and readd them later.
>if the tables are related using multiple foreign keys,
then you'll have to
>drop the constraints in the right order and readd them in
the right order as
>well.
>Make sure you test your scripts in your test environment.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
>Hi,
>I am in the process of converting all char and varchar
>colums to nchar /nvarchar ...I am done with all the
>scripts required for conversion..Can someone help me in
>precautions to be taken care while doing this upgrade
>like data file size , log file size etc......
>
>Sridhar
>
>.
>
sql

precautions for conversion

Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
SridharIf you are changing the column datatypes on a big table, make sure you have
enough space on the log drive, to allow the log to grow.
If you have any constraints (like defaults, indexes, primary and foreign
keys etc.) defined on the columns being altered, make cure you remove those
before the alter and readd them later.
if the tables are related using multiple foreign keys, then you'll have to
drop the constraints in the right order and readd them in the right order as
well.
Make sure you test your scripts in your test environment.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
Hi,
I am in the process of converting all char and varchar
colums to nchar /nvarchar ...I am done with all the
scripts required for conversion..Can someone help me in
precautions to be taken care while doing this upgrade
like data file size , log file size etc......
Sridhar|||Thanks..I have taken care of the dropping
constraints ,indexes and adding them in ordered way in my
script...i am worried about the log and data file size
growth...
>--Original Message--
>If you are changing the column datatypes on a big table,
make sure you have
>enough space on the log drive, to allow the log to grow.
>If you have any constraints (like defaults, indexes,
primary and foreign
>keys etc.) defined on the columns being altered, make
cure you remove those
>before the alter and readd them later.
>if the tables are related using multiple foreign keys,
then you'll have to
>drop the constraints in the right order and readd them in
the right order as
>well.
>Make sure you test your scripts in your test environment.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:dcf301c43bf0$dc370680$a401280a@.phx.gbl...
>Hi,
>I am in the process of converting all char and varchar
>colums to nchar /nvarchar ...I am done with all the
>scripts required for conversion..Can someone help me in
>precautions to be taken care while doing this upgrade
>like data file size , log file size etc......
>
>Sridhar
>
>.
>

Possible to specifying a list as a parameter to a stored procedure ?

I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...

SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))

The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?

Any help much appreciated!The answer is, "maybe".

It depends on your needs for performance. Please take a look at this discussion for more details.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403

Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.

I hope this helps,

CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like

level1 varchar(255)

I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.

IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END

If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.

' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END

Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.