change default collation for user database

Hi Folks,
Is there another way to change the default collation for a
user database without using the SQL cmd:
ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
With the ALTER DATABASE it is not possible to change the
database collation whenever there are Check Constraint
which are depending on the database collation.
A go lead for me would also be if someone can tell me in
which master table this is recorded'
Cheers Jack.You have to drop the constraints, then change the database collation, and
recreate the constraints. Don't try to change anything in system table
directly, you can make your database completely unusable.
I have an (unsupported) script from Microsoft that you can use to recollate
a database. Drop me a mail if you want it.
--
Jacco Schalkwijk
SQL Server MVP
"Jack" <jbonapart@.dicon.nl> wrote in message
news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
> Hi Folks,
> Is there another way to change the default collation for a
> user database without using the SQL cmd:
> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
> With the ALTER DATABASE it is not possible to change the
> database collation whenever there are Check Constraint
> which are depending on the database collation.
> A go lead for me would also be if someone can tell me in
> which master table this is recorded'
> Cheers Jack.|||Jacco,
What e-mail adres can I reach you ? I have tried
nospamjaccos@.eurostop.co.uk but i receive a Delivery
Status Notification Failure.
You can e-mail me the collate script to jbonapart@.dicon.nl
cheers.
Delivery to the following recipients failed.
>--Original Message--
>You have to drop the constraints, then change the
database collation, and
>recreate the constraints. Don't try to change anything in
system table
>directly, you can make your database completely unusable.
>I have an (unsupported) script from Microsoft that you
can use to recollate
>a database. Drop me a mail if you want it.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jack" <jbonapart@.dicon.nl> wrote in message
>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me in
>> which master table this is recorded'
>> Cheers Jack.
>
>.
>|||sorry, i had put a wrong sender's E-mail adres.
>--Original Message--
>Jacco,
>What e-mail adres can I reach you ? I have tried
>nospamjaccos@.eurostop.co.uk but i receive a Delivery
>Status Notification Failure.
>You can e-mail me the collate script to jbonapart@.dicon.nl
>cheers.
>Delivery to the following recipients failed.
>
>>--Original Message--
>>You have to drop the constraints, then change the
>database collation, and
>>recreate the constraints. Don't try to change anything
in
>system table
>>directly, you can make your database completely unusable.
>>I have an (unsupported) script from Microsoft that you
>can use to recollate
>>a database. Drop me a mail if you want it.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Jack" <jbonapart@.dicon.nl> wrote in message
>>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
>for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change
the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me
in
>> which master table this is recorded'
>> Cheers Jack.
>>
>>.
>.
>sql

catching of output for RESTORE FILELISTONLY.

Hi Folks,
How can I catch the output(columns) of the following SQL
command in a table or each column in variables?
RESTORE FILELISTONLY
FROM DISK='c:\temp\FlexKIDS.bak'
I have tried the following construction, which did NOT
work:
CREATE TABLE #LIST_FILE
(LogicalName varchar(120),
PhysicalName varchar(500),
L_Type char(1),
L_FileGROUP varchar(60),
L_size int,
Max_size int)
INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
DISK='c:\temp\FlexKIDS.bak'INSERT #LIST_FILE EXEC ('RESTORE FILELISTONLY FROM
DISK=''c:\temp\FlexKIDS.bak''')
--
Jacco Schalkwijk
SQL Server MVP
"jack" <jbonapart@.dicon.nl> wrote in message
news:090801c3a834$312770e0$a401280a@.phx.gbl...
> Hi Folks,
> How can I catch the output(columns) of the following SQL
> command in a table or each column in variables?
> RESTORE FILELISTONLY
> FROM DISK='c:\temp\FlexKIDS.bak'
> I have tried the following construction, which did NOT
> work:
> CREATE TABLE #LIST_FILE
> (LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
> INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
> DISK='c:\temp\FlexKIDS.bak'|||Insert into #LIST_FILE
exec('RESTORE FILELISTONLY
FROM DISK = ''c:\temp\FlexKIDS.bak''')
This will help to capture the result set into a temp. table
Try this out .!
Regards,
Raghu
>--Original Message--
>Hi Folks,
>How can I catch the output(columns) of the following SQL
>command in a table or each column in variables?
>RESTORE FILELISTONLY
>FROM DISK='c:\temp\FlexKIDS.bak'
>I have tried the following construction, which did NOT
>work:
>CREATE TABLE #LIST_FILE
>(LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
>INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
>DISK='c:\temp\FlexKIDS.bak'
>.
>

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
Alex
The NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
AlexThe NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com