checksum() of char, varchar, nchar, nvarchar, or sql_variant

Karam,
checksum and binary_checksum can be used for char, varchar, nchar, and
nvarchar. If you need it for sql_variant, you could convert the
sql_variant value to varbinary before applying the checksum or
binary_checksum. The following works fine for me (SQL Server 2000 sp3).
declare @.t table (
a char(10),
b nchar(10),
c varchar(10),
d nvarchar(10),
e sql_variant
)
insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_variant))
select
checksum(a),
checksum(b),
checksum(c),
checksum(d),
checksum(cast(e as varbinary(8000)))
from @.t
Steve Kass
Drew University
Karam Chand wrote:

>Hello,
>As the books online suggest we cannot generate checksum() value of the above types
using Checksum() or binary_checksum(), but my app requires it. Can somebody tell me
how to do that? Or is it just not possible... Maybe I can use some external program
min
g language?
>Karam
>Karam,
If you include a, b, c, d, and e in the output, it should look fine:
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.2 135216
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 mno 27535
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.1415926535897931 200148684
You should be aware of the fact that CHECKSUM is not guaranteed to
return different values from different input. There are only
~4000000000 possible checksum values, but far more possible input values.
SK
Karam Chand wrote:

>Hello,
>I tried your query on SQL Server 2000 and checksum is always returning me t
he same value for different set of data. If you execute the checksum() for y
our above data, I am getting result:
>304227412 1174430821 25065 6974316 135216
>304227412 1174430821 25065 6974316 27535
>304227412 1174430821 25065 6974316 200148684
>As you can see, its all same so it is difficult to know the difference?
>Karam
> -- Steve Kass wrote: --
> Karam,
> checksum and binary_checksum can be used for char, varchar, nchar, a
nd
> nvarchar. If you need it for sql_variant, you could convert the
> sql_variant value to varbinary before applying the checksum or
> binary_checksum. The following works fine for me (SQL Server 2000 sp3
).
> declare @.t table (
> a char(10),
> b nchar(10),
> c varchar(10),
> d nvarchar(10),
> e sql_variant
> )
> insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant
))
> insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_varia
nt))
> insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_varian
t))
> select
> checksum(a),
> checksum(b),
> checksum(c),
> checksum(d),
> checksum(cast(e as varbinary(8000)))
> from @.t
> Steve Kass
> Drew University
> Karam Chand wrote:
>
gramming language?
>

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

Checking all views for errors after upgrade

I just upgraded from SQL 7.0 to 2000. I know I have an error with the
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?

Thanks.
Sherry[posted and mailed, please reply in news]

sherkozmo (skosmicki@.sfmc-gi.org) writes:
> I just upgraded from SQL 7.0 to 2000. I know I have an error with the
> CAST and CONVERT functions I used in some views. Anyway, is there a
> sp I can run that will run all my views and give me back a list of
> those which have an error instead of going through each view one by
> one?

SELECT 'SELECT * FROM ' + name + char(13) + char(10) + 'go'
FROM sysobjects
WHERE xtype = 'V'
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY name

Run from Query Analyzer with results set to text. Cut and paste result
into query window.

I added the go between each SELECT, as it is likely that SQL Server will
abort the batch in case of an error and not run remaining views.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"sherkozmo" <skosmicki@.sfmc-gi.org> wrote in message
news:3ff08a65.0401060912.728b917@.posting.google.co m...
> I just upgraded from SQL 7.0 to 2000. I know I have an error with the
> CAST and CONVERT functions I used in some views. Anyway, is there a
> sp I can run that will run all my views and give me back a list of
> those which have an error instead of going through each view one by
> one?

You can bundle all your views into one script file by EM scripting.
Whether this is an advantage in examining them all at once?
This can be tested by creating a test database etc.

Pete Brown
Falls Creek
Oz