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
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

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 in SQL2000

I want to use checksum function for ntext data field in a table. But this
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:

> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>

Checksum computation help

Please execute the script below to understand the problem -

--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

--id & col1 make up the PK.

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

--The rows are identical.
--Script A

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')

--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.

Thanks

drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?

I know you're trying to accomplish something...but you got me lost..

It's in the same manner as your previous threads...

Can you give us a "big picture" view of what you're trying to accomplish?

I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -

ID...County...Univ...Dept.....Status

1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad

I have an index on the Status field coz I can see all Bad records on top.

Today I have in my source system -

ID...County...Univ...Dept

1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training

I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.

So how do I go about this problem? Is there a need for another column in here.

checking the type of a column

Hello,
I want to make an ALTER COLUMN to change a column's type (say VARCHAR to
TEXT), and I need to check first if the given column has not been changed
yet (the command will be in a script that will likely be runned more than
once and I don't want SQL Server to raise an error if the column to be
altered has already been altered).
So, I 've go table "Models" and I want to change the type of column
"Description" to TEXT (which is xtype 35). The following test ensures this
will only be done once:
if not exists (select * from dbo.syscolumns where id =
object_id(N'[dbo].[Models]')
and xtype = 35 and name = 'Description')
My question: is there a more elegant way of doing this ' (in terms of ease
of maintenance, performance, etc.) ? Thanks!!> My question: is there a more elegant way of doing this ' (in terms of ease
> of maintenance, performance, etc.) ? Thanks!!
Yes it is. Use information schema views instead accessing system tables
directly.
Example:
use northwind
go
create table t (colA varchar(50))
go
if exists(select * from information_schema.columns where table_schema =
'dbo' and table_name = 't' and column_name = 'colA' and data_type = 'varchar
')
alter table t alter column colA text
go
select
*
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't'
and column_name = 'colA'
go
drop table t
go
AMB
"Jeff Robichaud" wrote:

> Hello,
> I want to make an ALTER COLUMN to change a column's type (say VARCHAR to
> TEXT), and I need to check first if the given column has not been changed
> yet (the command will be in a script that will likely be runned more than
> once and I don't want SQL Server to raise an error if the column to be
> altered has already been altered).
> So, I 've go table "Models" and I want to change the type of column
> "Description" to TEXT (which is xtype 35). The following test ensures this
> will only be done once:
> if not exists (select * from dbo.syscolumns where id =
> object_id(N'[dbo].[Models]')
> and xtype = 35 and name = 'Description')
> My question: is there a more elegant way of doing this ' (in terms of eas
e
> of maintenance, performance, etc.) ? Thanks!!
>
>|||OK, this sounds cleaner. But I wonder why MS does not use this approach in
Query Analyzer you generate some script ? They use sysobjects directly...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2EC973B1-31A8-42F2-AC03-51AEC0EC4799@.microsoft.com...
> Yes it is. Use information schema views instead accessing system tables
> directly.
> Example:
> use northwind
> go
> create table t (colA varchar(50))
> go
>
> if exists(select * from information_schema.columns where table_schema =
> 'dbo' and table_name = 't' and column_name = 'colA' and data_type =
> 'varchar')
> alter table t alter column colA text
> go
> select
> *
> from
> information_schema.columns
> where
> table_schema = 'dbo'
> and table_name = 't'
> and column_name = 'colA'
> go
> drop table t
> go
>
> AMB
> "Jeff Robichaud" wrote:
>|||Jeff,
You will find an explanation if you see "Information Schema Views
" in BOL.
AMB
"Jeff Robichaud" wrote:

> OK, this sounds cleaner. But I wonder why MS does not use this approach in
> Query Analyzer you generate some script ? They use sysobjects directly...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:2EC973B1-31A8-42F2-AC03-51AEC0EC4799@.microsoft.com...
>
>

Checking contents of column and replacing contents if first character is a letter

Hi All,

I have come up against a wall which i cannot get over.

I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.

I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.

I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.

Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.

This is how far i have got: casting the varchar column to datetime and comparing.

SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"

Many thanks in advance!

You could adapt your query as follows:

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 0001'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

However, solution two is to add a new column called "Start_DateTime" and populate it with genuine dates. You can then phase out "Start_Date" entirely. It's always easier to data-clean sooner rather than later!

Also, read up on dynamic SQL and SQL injection when you get a minute.

|||

Hi Sohnee,

Thanks for the quick reply!

I've tested your query and I think it is still trying to cast the 'various' entrants as a date, as it returns the following:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Also, how would compare the date selected to the dates in db as i'm unsure of where to place the '>'.

I'll take you advice and start reading!

Thanks again!

|||

Can anybody else help at all?? PLEASEEEEE!!

Ta!

|||

SELECT *FROM table1WHERE CASE WHEN ISDATE(Cast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime))=1 THENCast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime)ELSENULLEND='" + date + "'"

|||

Apologies - in my example I used the minimum date from .NET - which is a bit early for SQL.

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 1900'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

|||

Hi All,

Thanks for all the responses...unfortunately the suggested solutions aren't doing the trick.

I think its still trying convert the varchar 'various' to a date format even when you've asked it set 'various' as '01/01/2010'

Oh well!

|||

Run this query to see if you get any clues back...

SELECT DISTINCT
Start_Date,
ISDATE(Start_Date),
CASE Start_Date
WHEN 'Various' THEN 'Text'
ELSE 'Date'
END = '" + date + "'
FROM
table1

My guess is that you will find other values (as well as 'Various') that aren't dates. - Problem areas will be lines that think they are dates, but that aren't.

Check This

DECLARE @.Temp int
DECLARE @.FullQry varchar(50)

set @.FullQry='select @.Temp=Emp_ID from Employee where....'
Exec(@.FullQry)
select @.@.ROWCOUNT

My Employee table has 3 records and this query sholud return me @.@.ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?The domain of the selection "@.Temp=" is outside the domain of the executed statement so the query as you have written it will return:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Temp'.

(1 row(s) affected)

The correct way to return values from a dynamically executed SQL section is to use sp_executesql with output semantics:

DECLARE @.Temp int
DECLARE @.Rows int
DECLARE @.sql nvarchar(4000)

SET @.sql=N'SELECT @.Rows=@.@.ROWCOUNT, @.Temp=EMp_ID FROM ...'

EXEC sp_executesql @.sql, N'@.Temp int OUTPUT, @.Rows int OUTPUT', @.Temp OUTPUT, @.Rows OUTPUT

SELECT @.Rows, @.Temp

Check the field existence of a database table

Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes

I need SP

SP

(

@.Tablename varchar(30),

@.Fieldname varchar(30),

@.existance char(1) OUTPUT,

@.field_type varchar(30) OUTPUT,

@.field_size int OUTPUT,

@.field_decimal int OUTPUT

)

as

/* Below check the existance of a @.Fieldname in given @.Tablename */

/* And set the OUTPUT variables */

Thanks

To check existance of a data column, try code below:

IFEXISTS (SELECT *FROMSysObjects soINNERJOINSysColumns scON so.ID = sc.IDWHEREObjectProperty(so.ID,'IsUserTable') = 1AND so.Name ='yourtablename'AND sc.Name ='columnname' )

To get datatype of the data column, try:

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

|||

You can also do an sp_Help 'Table' to get all the information.

|||

Hi jackyang,

Thanks for your help.Your first query is running properly but second one has problem

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

 does not work

here is 'dbo' static or my databas name (my database name is 'neuron')?

Thanks

|||

You can disregard the table_schema then. It's likely the security schema is not default 'dbo' in your setup.

Just use the code below:

SELECT data_typeFROM information_schema.columnsWHERE table_name ='yourtablename'AND column_name ='columnname'