Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Etienne,
Did you also change the types of the variables in the stored procedures? If
not, then you will have conversions happening.
You might also find this article useful if the international and Chinese
world are new to you:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.
>|||Hi Russell,
Yep, changed the types of variables too...
I'm really confused, nowhere on the web i could find somebody with the same
problem as me...
What is strange is that with my access application, i can write, update and
select (of course!) any field that has chinese characters. But when i try
my SP with Query Analyzer, it inserts ? for every chinese character...
I'll keep looking, thank you anyway!
Etienne
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> Etienne,
> Did you also change the types of the variables in the stored procedures?
If
> not, then you will have conversions happening.
> You might also find this article useful if the international and Chinese
> world are new to you:
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
>
> Russell Fields
> http://www.sqlpass.org/
> 2004 PASS Community Summit - Orlando
> - The largest user-event dedicated to SQL Server!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> >
> > Hi,
> > I have a problem with my Stored Procedures...
> > Recently we decided to change the type of our column in our databse from
> > varchar to nvarchar because of new customers (chinese).
> > Everything works fine EXCEPT the stored procedures... When i try to
pass
> > chinese characters for a simple SP that those a basic insert in my
table,
> it
> > inserts ? instead of chinese characters...
> > Did i miss something obvious?
> > Thanks a lot!
> > Etienne
> >
> > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> > newsgroup.
> >
> >
>|||Damn! Finally found it!
I had to put the letter N (capital) in front of each of my Unicode value...
example:
exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
Thanks a lot!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> Hi Russell,
> Yep, changed the types of variables too...
> I'm really confused, nowhere on the web i could find somebody with the
same
> problem as me...
> What is strange is that with my access application, i can write, update
and
> select (of course!) any field that has chinese characters. But when i try
> my SP with Query Analyzer, it inserts ? for every chinese character...
> I'll keep looking, thank you anyway!
> Etienne
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > Etienne,
> >
> > Did you also change the types of the variables in the stored procedures?
> If
> > not, then you will have conversions happening.
> >
> > You might also find this article useful if the international and Chinese
> > world are new to you:
> > http://msdn.microsoft.com/library/default.asp?
> > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> >
> >
> > Russell Fields
> > http://www.sqlpass.org/
> > 2004 PASS Community Summit - Orlando
> > - The largest user-event dedicated to SQL Server!
> >
> > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > >
> > > Hi,
> > > I have a problem with my Stored Procedures...
> > > Recently we decided to change the type of our column in our databse
from
> > > varchar to nvarchar because of new customers (chinese).
> > > Everything works fine EXCEPT the stored procedures... When i try to
> pass
> > > chinese characters for a simple SP that those a basic insert in my
> table,
> > it
> > > inserts ? instead of chinese characters...
> > > Did i miss something obvious?
> > > Thanks a lot!
> > > Etienne
> > >
> > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
this
> > > newsgroup.
> > >
> > >
> >
> >
>|||Congratulations
Russell Fields
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:B5svb.16541$iT4.2072482@.news20.bellglobal.com...
> Damn! Finally found it!
> I had to put the letter N (capital) in front of each of my Unicode
value...
> example:
> exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
> Thanks a lot!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> > Hi Russell,
> > Yep, changed the types of variables too...
> > I'm really confused, nowhere on the web i could find somebody with the
> same
> > problem as me...
> > What is strange is that with my access application, i can write, update
> and
> > select (of course!) any field that has chinese characters. But when i
try
> > my SP with Query Analyzer, it inserts ? for every chinese character...
> > I'll keep looking, thank you anyway!
> > Etienne
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > > Etienne,
> > >
> > > Did you also change the types of the variables in the stored
procedures?
> > If
> > > not, then you will have conversions happening.
> > >
> > > You might also find this article useful if the international and
Chinese
> > > world are new to you:
> > > http://msdn.microsoft.com/library/default.asp?
> > > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> > >
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/
> > > 2004 PASS Community Summit - Orlando
> > > - The largest user-event dedicated to SQL Server!
> > >
> > > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > > >
> > > > Hi,
> > > > I have a problem with my Stored Procedures...
> > > > Recently we decided to change the type of our column in our databse
> from
> > > > varchar to nvarchar because of new customers (chinese).
> > > > Everything works fine EXCEPT the stored procedures... When i try to
> > pass
> > > > chinese characters for a simple SP that those a basic insert in my
> > table,
> > > it
> > > > inserts ? instead of chinese characters...
> > > > Did i miss something obvious?
> > > > Thanks a lot!
> > > > Etienne
> > > >
> > > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
> this
> > > > newsgroup.
> > > >
> > > >
> > >
> > >
> >
> >
>|||On Fri, 21 Nov 2003 11:12:01 -0500, "Etienne M. St-Georges"
<nospam.etienne@.emstg.com> wrote:
>Hi,
>I have a problem with my Stored Procedures...
>Recently we decided to change the type of our column in our databse from
>varchar to nvarchar because of new customers (chinese).
>Everything works fine EXCEPT the stored procedures... When i try to pass
>chinese characters for a simple SP that those a basic insert in my table, it
>inserts ? instead of chinese characters...
>Did i miss something obvious?
>Thanks a lot!
G'day Etienne,
You don't mention how you are calling your stored procedures, but if you
are using ADO, note that you should use the Command object and
Parameters, and each nchar or nvarchar parameter should be created of
type adWChar or adWVarchar as appropriate.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander
Showing posts with label nvarchar. Show all posts
Showing posts with label nvarchar. Show all posts
Sunday, March 11, 2012
Chinese chars through stored procedures...
Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Check the source code of your stored procedure to make sure the parameter is
declared as nvarchar and not varchar.
HTH,
Dave
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:MOqvb.16440$iT4.2055717@.news20.bellglobal.com ...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.
Thursday, March 8, 2012
chinese characters with Unicode data types?
Hi,
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
BodoOne possibility is that the data is actually stored correctly in the
database, but the query tool you are using hasn't been configured to use a
font that defines glyphs for Chinese characters. Try using Query Analyzer
to both insert and view the data (don't use the "Show All Rows" view in
Enterprise Manager as the controls used in this view aren't Unicode aware).
Go to Tools --> Options --> Fonts tab, and select a font that supports
Chinese like "SimSun" or "Arial Unicode MS" for the "Results Text" and
"Results Grid" views.
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "bodo" <bodobecker@.hotmail.com>
Sender: "bodo" <bodobecker@.hotmail.com>
Subject: chinese characters with Unicode data types?
Date: Sat, 13 Sep 2003 09:11:57 -0700
Lines: 14
Message-ID: <000a01c37a11$c15c4bf0$a401280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcN6EcFc9/ArRhOoTrW8Ejd9PK+XmQ==Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306274
NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
Bodo
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
BodoOne possibility is that the data is actually stored correctly in the
database, but the query tool you are using hasn't been configured to use a
font that defines glyphs for Chinese characters. Try using Query Analyzer
to both insert and view the data (don't use the "Show All Rows" view in
Enterprise Manager as the controls used in this view aren't Unicode aware).
Go to Tools --> Options --> Fonts tab, and select a font that supports
Chinese like "SimSun" or "Arial Unicode MS" for the "Results Text" and
"Results Grid" views.
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "bodo" <bodobecker@.hotmail.com>
Sender: "bodo" <bodobecker@.hotmail.com>
Subject: chinese characters with Unicode data types?
Date: Sat, 13 Sep 2003 09:11:57 -0700
Lines: 14
Message-ID: <000a01c37a11$c15c4bf0$a401280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcN6EcFc9/ArRhOoTrW8Ejd9PK+XmQ==Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306274
NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
Bodo
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (�) and
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (�) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (�) and
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (�) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the tex
t
is comeing out as Squares. However, I can paste the value into here (要) an
d
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put
in
> Middle Eastern language, such as Arabic and the text displays as it should
.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the t
ext
> is comeing out as Squares. However, I can paste the value into here (要)
and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the tex
t
is comeing out as Squares. However, I can paste the value into here (要) an
d
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put
in
> Middle Eastern language, such as Arabic and the text displays as it should
.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the t
ext
> is comeing out as Squares. However, I can paste the value into here (要)
and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (要) and
you can see it fine.
Can anyone plese help?
Thanks,
Jon
Problem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (要) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (要) and
you can see it fine.
Can anyone plese help?
Thanks,
Jon
Problem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (要) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Wednesday, March 7, 2012
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?
>
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?
>
Friday, February 24, 2012
Checking the datatype of a columns
Hello,
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 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
Subscribe to:
Comments (Atom)