How can I select all of the rows of my table for which a certain
field's value contains other characters than a-z and A-Z (non alpha
string) ?
Thank youWhere Column like '%[0-9]%'
Look up "Pattern Matching in Search Conditions" in BOL for more information.
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||If it does not help ,please post your actual data + expected result
create table #t (c1 varchar(20))
insert into #t values ('cdjdfj')
insert into #t values ('cd4jdfj')
insert into #t values ('fh')
insert into #t values ('1525')
insert into #t values ('1jj')
insert into #t values ('jkk')
select * from #t where c1 like '%[0-9]%'
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||found out, had to use the PATINDEX function|||Uri Dimant wrote:
> If it does not help ,please post your actual data + expected result
> create table #t (c1 varchar(20))
> insert into #t values ('cdjdfj')
> insert into #t values ('cd4jdfj')
> insert into #t values ('fh')
> insert into #t values ('1525')
> insert into #t values ('1jj')
> insert into #t values ('jkk')
>
> select * from #t where c1 like '%[0-9]%'
>
What about :
insert into test values ('!"=A3$%^&*()_')
Try this instead:
select * from #t where c1 like '%[^A-Z]%'=20
Jamie.
Showing posts with label character. Show all posts
Showing posts with label character. Show all posts
Thursday, March 22, 2012
Thursday, February 16, 2012
CHARINDEX starting from the end?
Hi,
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
Anyone pleas give me a hand.
/Memgardsee if this helps...
Code:
------------------------------
declare @.s varchar(17)
set @.s = '1234|67890A|CDEFG'
select @.s as 'Original String'
select reverse(@.s) as 'In Reverse', datalength(@.s) as 'String Length'
select charindex('|',reverse(@.s)) as 'Location of first ''|'' in Reverse String'
select datalength(@.s) - charindex('|',reverse(@.s)) + 1 as 'Location of last ''|'' in Original string'
------------------------------|||That did help.
Thanks a lot!
Stange that SQL Server dosn't support this with a built in function.
In Oracle you can just add a negative value to scan from the right to left...|||I have found over the years that Oracle and SQL Server both get the job done, just in diffrent ways.
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
Anyone pleas give me a hand.
/Memgardsee if this helps...
Code:
------------------------------
declare @.s varchar(17)
set @.s = '1234|67890A|CDEFG'
select @.s as 'Original String'
select reverse(@.s) as 'In Reverse', datalength(@.s) as 'String Length'
select charindex('|',reverse(@.s)) as 'Location of first ''|'' in Reverse String'
select datalength(@.s) - charindex('|',reverse(@.s)) + 1 as 'Location of last ''|'' in Original string'
------------------------------|||That did help.
Thanks a lot!
Stange that SQL Server dosn't support this with a built in function.
In Oracle you can just add a negative value to scan from the right to left...|||I have found over the years that Oracle and SQL Server both get the job done, just in diffrent ways.
Tuesday, February 14, 2012
character(s) that cannot be stored in DB
Does any one know if there're certain character(s) that
cannot be stored in the DB which is similar to Windows
cannot have " or * etc.?
Thanks
OwenNot really. But the character repertoire is based on the collation selected for the
column/database/server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> Does any one know if there're certain character(s) that
> cannot be stored in the DB which is similar to Windows
> cannot have " or * etc.?
> Thanks
> Owen|||Thanks for your reply Tibor.
I am in a situation where the company that is hosting my
web site has told me that I cannot enter characters ' and
+ in the column as it will cause problem, I can enter "
however. What can you suggest that I tell them as I do
need to input those characters.
Thanks
Owen
>Not really. But the character repertoire is based on the
collation selected for the
>column/database/server.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Owen" <owen_lin@.hotmail.com> wrote in message
news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>> Does any one know if there're certain character(s) that
>> cannot be stored in the DB which is similar to Windows
>> cannot have " or * etc.?
>> Thanks
>> Owen|||Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
the application you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> >Not really. But the character repertoire is based on the
> collation selected for the
> >column/database/server.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Owen" <owen_lin@.hotmail.com> wrote in message
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> >> Does any one know if there're certain character(s) that
> >> cannot be stored in the DB which is similar to Windows
> >> cannot have " or * etc.?
> >>
> >> Thanks
> >>
> >> Owen|||"Owen" <owen_lin@.hotmail.com> wrote in message
news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
My guess is they're trying to trap certain stuff to prevent SQL Injection
attacks.
However, allowing " isn't smart in that case.|||I spoke to the company that is hosting my web site and DB, although I did not
asked them in details as to what sort of problem it might cuase or have
caused in the past, but they did told me that it has been a problem in the
past where their hosting site(s) needing to store ' within SQL and have
encountered some issue.
They have asked me to seek advice on their behalf as to what can/should they
do to overcome such problem.
Many thanks
Owen
"Tibor Karaszi" wrote:
> Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> the application you are using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > Thanks for your reply Tibor.
> >
> > I am in a situation where the company that is hosting my
> > web site has told me that I cannot enter characters ' and
> > + in the column as it will cause problem, I can enter "
> > however. What can you suggest that I tell them as I do
> > need to input those characters.
> >
> > Thanks
> >
> > Owen
> >
> > >Not really. But the character repertoire is based on the
> > collation selected for the
> > >column/database/server.
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >http://www.karaszi.com/sqlserver/default.asp
> > >http://www.solidqualitylearning.com/
> > >
> > >
> > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > >> Does any one know if there're certain character(s) that
> > >> cannot be stored in the DB which is similar to Windows
> > >> cannot have " or * etc.?
> > >>
> > >> Thanks
> > >>
> > >> Owen
>
>|||Thanks for your reply Greg.
May I ask what sort of issue(s) might occur by allowing the use of " ?
Owen
"Greg D. Moore (Strider)" wrote:
> "Owen" <owen_lin@.hotmail.com> wrote in message
> news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > Thanks for your reply Tibor.
> >
> > I am in a situation where the company that is hosting my
> > web site has told me that I cannot enter characters ' and
> > + in the column as it will cause problem, I can enter "
> > however. What can you suggest that I tell them as I do
> > need to input those characters.
> My guess is they're trying to trap certain stuff to prevent SQL Injection
> attacks.
> However, allowing " isn't smart in that case.
>
>|||Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
does it make any different in this case?
"Owen" wrote:
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> >Not really. But the character repertoire is based on the
> collation selected for the
> >column/database/server.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Owen" <owen_lin@.hotmail.com> wrote in message
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> >> Does any one know if there're certain character(s) that
> >> cannot be stored in the DB which is similar to Windows
> >> cannot have " or * etc.?
> >>
> >> Thanks
> >>
> >> Owen
>|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did
not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
>
The general way of handling this is use '' (two single quotes).
ie. select * from names where lastname='O''brien'
It's not clear to me what they're doing, blocking ' entirely?
> They have asked me to seek advice on their behalf as to what can/should
they
> do to overcome such problem.
> Many thanks
> Owen|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:DB0511AF-7798-4821-A269-52F5FEFB3236@.microsoft.com...
> Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
> does it make any different in this case?
>
Depending on how their server is setup, it's possible to treat " like '
which then of course leads to SQL Injection attacks.|||> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
"I have an application that uses SQL Server, and my application has a lot of bugs in it. I.e.,
there's a problem in SQL server, is there a fix for it?"
I'm not sure I can put it simpler than above. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
> Many thanks
> Owen
> "Tibor Karaszi" wrote:
> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who
wrote
> > the application you are using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > > Thanks for your reply Tibor.
> > >
> > > I am in a situation where the company that is hosting my
> > > web site has told me that I cannot enter characters ' and
> > > + in the column as it will cause problem, I can enter "
> > > however. What can you suggest that I tell them as I do
> > > need to input those characters.
> > >
> > > Thanks
> > >
> > > Owen
> > >
> > > >Not really. But the character repertoire is based on the
> > > collation selected for the
> > > >column/database/server.
> > > >
> > > >--
> > > >Tibor Karaszi, SQL Server MVP
> > > >http://www.karaszi.com/sqlserver/default.asp
> > > >http://www.solidqualitylearning.com/
> > > >
> > > >
> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > > >> Does any one know if there're certain character(s) that
> > > >> cannot be stored in the DB which is similar to Windows
> > > >> cannot have " or * etc.?
> > > >>
> > > >> Thanks
> > > >>
> > > >> Owen
> >
> >
> >|||They have overcome the problem within the ASP coding by using "relapce".
The issue came about as we have an admin section for our company's web site,
this is an interface where we can update the info on the site, but apparently
when a single quotation mark (') is written to the SQL via the ASP it would
cause some problem. So someone have suggested the use of "relapce" to write
two single quotation mark where one is entered, and would display it as one
single quotation mark when two singles '' is detected.
This seem to do the trick nevertheless, not sure what your thoughts are on
this?
"Owen" wrote:
> I spoke to the company that is hosting my web site and DB, although I did not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
> Many thanks
> Owen
> "Tibor Karaszi" wrote:
> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> > the application you are using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > > Thanks for your reply Tibor.
> > >
> > > I am in a situation where the company that is hosting my
> > > web site has told me that I cannot enter characters ' and
> > > + in the column as it will cause problem, I can enter "
> > > however. What can you suggest that I tell them as I do
> > > need to input those characters.
> > >
> > > Thanks
> > >
> > > Owen
> > >
> > > >Not really. But the character repertoire is based on the
> > > collation selected for the
> > > >column/database/server.
> > > >
> > > >--
> > > >Tibor Karaszi, SQL Server MVP
> > > >http://www.karaszi.com/sqlserver/default.asp
> > > >http://www.solidqualitylearning.com/
> > > >
> > > >
> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > > >> Does any one know if there're certain character(s) that
> > > >> cannot be stored in the DB which is similar to Windows
> > > >> cannot have " or * etc.?
> > > >>
> > > >> Thanks
> > > >>
> > > >> Owen
> >
> >
> >|||> So someone have suggested the use of "relapce" to write
> two single quotation mark where one is entered, and would display it as one
> single quotation mark when two singles '' is detected.
> This seem to do the trick nevertheless, not sure what your thoughts are on
> this?
The correct way to input a single quote in the SQL language is indeed to escape it with a preceding
single quote (which makes it two single quotes).
Consider using command objects and parameter object in your app, this way your database programming
interface (ADO etc) will do this for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:7E044AE8-4459-427F-9923-DEE984ABD117@.microsoft.com...
> They have overcome the problem within the ASP coding by using "relapce".
> The issue came about as we have an admin section for our company's web site,
> this is an interface where we can update the info on the site, but apparently
> when a single quotation mark (') is written to the SQL via the ASP it would
> cause some problem. So someone have suggested the use of "relapce" to write
> two single quotation mark where one is entered, and would display it as one
> single quotation mark when two singles '' is detected.
> This seem to do the trick nevertheless, not sure what your thoughts are on
> this?
>
> "Owen" wrote:
>> I spoke to the company that is hosting my web site and DB, although I did not
>> asked them in details as to what sort of problem it might cuase or have
>> caused in the past, but they did told me that it has been a problem in the
>> past where their hosting site(s) needing to store ' within SQL and have
>> encountered some issue.
>> They have asked me to seek advice on their behalf as to what can/should they
>> do to overcome such problem.
>> Many thanks
>> Owen
>> "Tibor Karaszi" wrote:
>> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who
>> > wrote
>> > the application you are using?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
>> > > Thanks for your reply Tibor.
>> > >
>> > > I am in a situation where the company that is hosting my
>> > > web site has told me that I cannot enter characters ' and
>> > > + in the column as it will cause problem, I can enter "
>> > > however. What can you suggest that I tell them as I do
>> > > need to input those characters.
>> > >
>> > > Thanks
>> > >
>> > > Owen
>> > >
>> > > >Not really. But the character repertoire is based on the
>> > > collation selected for the
>> > > >column/database/server.
>> > > >
>> > > >--
>> > > >Tibor Karaszi, SQL Server MVP
>> > > >http://www.karaszi.com/sqlserver/default.asp
>> > > >http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
>> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>> > > >> Does any one know if there're certain character(s) that
>> > > >> cannot be stored in the DB which is similar to Windows
>> > > >> cannot have " or * etc.?
>> > > >>
>> > > >> Thanks
>> > > >>
>> > > >> Owen
>> >
>> >
>> >
cannot be stored in the DB which is similar to Windows
cannot have " or * etc.?
Thanks
OwenNot really. But the character repertoire is based on the collation selected for the
column/database/server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> Does any one know if there're certain character(s) that
> cannot be stored in the DB which is similar to Windows
> cannot have " or * etc.?
> Thanks
> Owen|||Thanks for your reply Tibor.
I am in a situation where the company that is hosting my
web site has told me that I cannot enter characters ' and
+ in the column as it will cause problem, I can enter "
however. What can you suggest that I tell them as I do
need to input those characters.
Thanks
Owen
>Not really. But the character repertoire is based on the
collation selected for the
>column/database/server.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Owen" <owen_lin@.hotmail.com> wrote in message
news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>> Does any one know if there're certain character(s) that
>> cannot be stored in the DB which is similar to Windows
>> cannot have " or * etc.?
>> Thanks
>> Owen|||Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
the application you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> >Not really. But the character repertoire is based on the
> collation selected for the
> >column/database/server.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Owen" <owen_lin@.hotmail.com> wrote in message
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> >> Does any one know if there're certain character(s) that
> >> cannot be stored in the DB which is similar to Windows
> >> cannot have " or * etc.?
> >>
> >> Thanks
> >>
> >> Owen|||"Owen" <owen_lin@.hotmail.com> wrote in message
news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
My guess is they're trying to trap certain stuff to prevent SQL Injection
attacks.
However, allowing " isn't smart in that case.|||I spoke to the company that is hosting my web site and DB, although I did not
asked them in details as to what sort of problem it might cuase or have
caused in the past, but they did told me that it has been a problem in the
past where their hosting site(s) needing to store ' within SQL and have
encountered some issue.
They have asked me to seek advice on their behalf as to what can/should they
do to overcome such problem.
Many thanks
Owen
"Tibor Karaszi" wrote:
> Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> the application you are using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > Thanks for your reply Tibor.
> >
> > I am in a situation where the company that is hosting my
> > web site has told me that I cannot enter characters ' and
> > + in the column as it will cause problem, I can enter "
> > however. What can you suggest that I tell them as I do
> > need to input those characters.
> >
> > Thanks
> >
> > Owen
> >
> > >Not really. But the character repertoire is based on the
> > collation selected for the
> > >column/database/server.
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >http://www.karaszi.com/sqlserver/default.asp
> > >http://www.solidqualitylearning.com/
> > >
> > >
> > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > >> Does any one know if there're certain character(s) that
> > >> cannot be stored in the DB which is similar to Windows
> > >> cannot have " or * etc.?
> > >>
> > >> Thanks
> > >>
> > >> Owen
>
>|||Thanks for your reply Greg.
May I ask what sort of issue(s) might occur by allowing the use of " ?
Owen
"Greg D. Moore (Strider)" wrote:
> "Owen" <owen_lin@.hotmail.com> wrote in message
> news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > Thanks for your reply Tibor.
> >
> > I am in a situation where the company that is hosting my
> > web site has told me that I cannot enter characters ' and
> > + in the column as it will cause problem, I can enter "
> > however. What can you suggest that I tell them as I do
> > need to input those characters.
> My guess is they're trying to trap certain stuff to prevent SQL Injection
> attacks.
> However, allowing " isn't smart in that case.
>
>|||Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
does it make any different in this case?
"Owen" wrote:
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> >Not really. But the character repertoire is based on the
> collation selected for the
> >column/database/server.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Owen" <owen_lin@.hotmail.com> wrote in message
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> >> Does any one know if there're certain character(s) that
> >> cannot be stored in the DB which is similar to Windows
> >> cannot have " or * etc.?
> >>
> >> Thanks
> >>
> >> Owen
>|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did
not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
>
The general way of handling this is use '' (two single quotes).
ie. select * from names where lastname='O''brien'
It's not clear to me what they're doing, blocking ' entirely?
> They have asked me to seek advice on their behalf as to what can/should
they
> do to overcome such problem.
> Many thanks
> Owen|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:DB0511AF-7798-4821-A269-52F5FEFB3236@.microsoft.com...
> Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
> does it make any different in this case?
>
Depending on how their server is setup, it's possible to treat " like '
which then of course leads to SQL Injection attacks.|||> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
"I have an application that uses SQL Server, and my application has a lot of bugs in it. I.e.,
there's a problem in SQL server, is there a fix for it?"
I'm not sure I can put it simpler than above. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
> Many thanks
> Owen
> "Tibor Karaszi" wrote:
> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who
wrote
> > the application you are using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > > Thanks for your reply Tibor.
> > >
> > > I am in a situation where the company that is hosting my
> > > web site has told me that I cannot enter characters ' and
> > > + in the column as it will cause problem, I can enter "
> > > however. What can you suggest that I tell them as I do
> > > need to input those characters.
> > >
> > > Thanks
> > >
> > > Owen
> > >
> > > >Not really. But the character repertoire is based on the
> > > collation selected for the
> > > >column/database/server.
> > > >
> > > >--
> > > >Tibor Karaszi, SQL Server MVP
> > > >http://www.karaszi.com/sqlserver/default.asp
> > > >http://www.solidqualitylearning.com/
> > > >
> > > >
> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > > >> Does any one know if there're certain character(s) that
> > > >> cannot be stored in the DB which is similar to Windows
> > > >> cannot have " or * etc.?
> > > >>
> > > >> Thanks
> > > >>
> > > >> Owen
> >
> >
> >|||They have overcome the problem within the ASP coding by using "relapce".
The issue came about as we have an admin section for our company's web site,
this is an interface where we can update the info on the site, but apparently
when a single quotation mark (') is written to the SQL via the ASP it would
cause some problem. So someone have suggested the use of "relapce" to write
two single quotation mark where one is entered, and would display it as one
single quotation mark when two singles '' is detected.
This seem to do the trick nevertheless, not sure what your thoughts are on
this?
"Owen" wrote:
> I spoke to the company that is hosting my web site and DB, although I did not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
> They have asked me to seek advice on their behalf as to what can/should they
> do to overcome such problem.
> Many thanks
> Owen
> "Tibor Karaszi" wrote:
> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> > the application you are using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> > > Thanks for your reply Tibor.
> > >
> > > I am in a situation where the company that is hosting my
> > > web site has told me that I cannot enter characters ' and
> > > + in the column as it will cause problem, I can enter "
> > > however. What can you suggest that I tell them as I do
> > > need to input those characters.
> > >
> > > Thanks
> > >
> > > Owen
> > >
> > > >Not really. But the character repertoire is based on the
> > > collation selected for the
> > > >column/database/server.
> > > >
> > > >--
> > > >Tibor Karaszi, SQL Server MVP
> > > >http://www.karaszi.com/sqlserver/default.asp
> > > >http://www.solidqualitylearning.com/
> > > >
> > > >
> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> > > >> Does any one know if there're certain character(s) that
> > > >> cannot be stored in the DB which is similar to Windows
> > > >> cannot have " or * etc.?
> > > >>
> > > >> Thanks
> > > >>
> > > >> Owen
> >
> >
> >|||> So someone have suggested the use of "relapce" to write
> two single quotation mark where one is entered, and would display it as one
> single quotation mark when two singles '' is detected.
> This seem to do the trick nevertheless, not sure what your thoughts are on
> this?
The correct way to input a single quote in the SQL language is indeed to escape it with a preceding
single quote (which makes it two single quotes).
Consider using command objects and parameter object in your app, this way your database programming
interface (ADO etc) will do this for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:7E044AE8-4459-427F-9923-DEE984ABD117@.microsoft.com...
> They have overcome the problem within the ASP coding by using "relapce".
> The issue came about as we have an admin section for our company's web site,
> this is an interface where we can update the info on the site, but apparently
> when a single quotation mark (') is written to the SQL via the ASP it would
> cause some problem. So someone have suggested the use of "relapce" to write
> two single quotation mark where one is entered, and would display it as one
> single quotation mark when two singles '' is detected.
> This seem to do the trick nevertheless, not sure what your thoughts are on
> this?
>
> "Owen" wrote:
>> I spoke to the company that is hosting my web site and DB, although I did not
>> asked them in details as to what sort of problem it might cuase or have
>> caused in the past, but they did told me that it has been a problem in the
>> past where their hosting site(s) needing to store ' within SQL and have
>> encountered some issue.
>> They have asked me to seek advice on their behalf as to what can/should they
>> do to overcome such problem.
>> Many thanks
>> Owen
>> "Tibor Karaszi" wrote:
>> > Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who
>> > wrote
>> > the application you are using?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
>> > > Thanks for your reply Tibor.
>> > >
>> > > I am in a situation where the company that is hosting my
>> > > web site has told me that I cannot enter characters ' and
>> > > + in the column as it will cause problem, I can enter "
>> > > however. What can you suggest that I tell them as I do
>> > > need to input those characters.
>> > >
>> > > Thanks
>> > >
>> > > Owen
>> > >
>> > > >Not really. But the character repertoire is based on the
>> > > collation selected for the
>> > > >column/database/server.
>> > > >
>> > > >--
>> > > >Tibor Karaszi, SQL Server MVP
>> > > >http://www.karaszi.com/sqlserver/default.asp
>> > > >http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > >"Owen" <owen_lin@.hotmail.com> wrote in message
>> > > news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>> > > >> Does any one know if there're certain character(s) that
>> > > >> cannot be stored in the DB which is similar to Windows
>> > > >> cannot have " or * etc.?
>> > > >>
>> > > >> Thanks
>> > > >>
>> > > >> Owen
>> >
>> >
>> >
character(s) that cannot be stored in DB
Does any one know if there're certain character(s) that
cannot be stored in the DB which is similar to Windows
cannot have " or * etc.?
Thanks
Owen
Not really. But the character repertoire is based on the collation selected for the
column/database/server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> Does any one know if there're certain character(s) that
> cannot be stored in the DB which is similar to Windows
> cannot have " or * etc.?
> Thanks
> Owen
|||Thanks for your reply Tibor.
I am in a situation where the company that is hosting my
web site has told me that I cannot enter characters ' and
+ in the column as it will cause problem, I can enter "
however. What can you suggest that I tell them as I do
need to input those characters.
Thanks
Owen
>Not really. But the character repertoire is based on the
collation selected for the
>column/database/server.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Owen" <owen_lin@.hotmail.com> wrote in message
news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...[vbcol=seagreen]
|||Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
the application you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> collation selected for the
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
|||"Owen" <owen_lin@.hotmail.com> wrote in message
news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
My guess is they're trying to trap certain stuff to prevent SQL Injection
attacks.
However, allowing " isn't smart in that case.
|||I spoke to the company that is hosting my web site and DB, although I did not
asked them in details as to what sort of problem it might cuase or have
caused in the past, but they did told me that it has been a problem in the
past where their hosting site(s) needing to store ' within SQL and have
encountered some issue.
They have asked me to seek advice on their behalf as to what can/should they
do to overcome such problem.
Many thanks
Owen
"Tibor Karaszi" wrote:
> Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> the application you are using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
>
>
|||Thanks for your reply Greg.
May I ask what sort of issue(s) might occur by allowing the use of " ?
Owen
"Greg D. Moore (Strider)" wrote:
> "Owen" <owen_lin@.hotmail.com> wrote in message
> news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> My guess is they're trying to trap certain stuff to prevent SQL Injection
> attacks.
> However, allowing " isn't smart in that case.
>
>
|||Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
does it make any different in this case?
"Owen" wrote:
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> collation selected for the
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>
|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did
not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
>
The general way of handling this is use '' (two single quotes).
ie. select * from names where lastname='O''brien'
It's not clear to me what they're doing, blocking ' entirely?
> They have asked me to seek advice on their behalf as to what can/should
they
> do to overcome such problem.
> Many thanks
> Owen
|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:DB0511AF-7798-4821-A269-52F5FEFB3236@.microsoft.com...
> Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
> does it make any different in this case?
>
Depending on how their server is setup, it's possible to treat " like '
which then of course leads to SQL Injection attacks.
cannot be stored in the DB which is similar to Windows
cannot have " or * etc.?
Thanks
Owen
Not really. But the character repertoire is based on the collation selected for the
column/database/server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
> Does any one know if there're certain character(s) that
> cannot be stored in the DB which is similar to Windows
> cannot have " or * etc.?
> Thanks
> Owen
|||Thanks for your reply Tibor.
I am in a situation where the company that is hosting my
web site has told me that I cannot enter characters ' and
+ in the column as it will cause problem, I can enter "
however. What can you suggest that I tell them as I do
need to input those characters.
Thanks
Owen
>Not really. But the character repertoire is based on the
collation selected for the
>column/database/server.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Owen" <owen_lin@.hotmail.com> wrote in message
news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...[vbcol=seagreen]
|||Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
the application you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> collation selected for the
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
|||"Owen" <owen_lin@.hotmail.com> wrote in message
news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
My guess is they're trying to trap certain stuff to prevent SQL Injection
attacks.
However, allowing " isn't smart in that case.
|||I spoke to the company that is hosting my web site and DB, although I did not
asked them in details as to what sort of problem it might cuase or have
caused in the past, but they did told me that it has been a problem in the
past where their hosting site(s) needing to store ' within SQL and have
encountered some issue.
They have asked me to seek advice on their behalf as to what can/should they
do to overcome such problem.
Many thanks
Owen
"Tibor Karaszi" wrote:
> Ask them what they mean by "it will cause problem". SQL Server can certainly handle this. Who wrote
> the application you are using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Owen" <owen_lin@.hotmail.com> wrote in message news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
>
>
|||Thanks for your reply Greg.
May I ask what sort of issue(s) might occur by allowing the use of " ?
Owen
"Greg D. Moore (Strider)" wrote:
> "Owen" <owen_lin@.hotmail.com> wrote in message
> news:35f801c4a601$fd6b9200$a501280a@.phx.gbl...
> My guess is they're trying to trap certain stuff to prevent SQL Injection
> attacks.
> However, allowing " isn't smart in that case.
>
>
|||Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
does it make any different in this case?
"Owen" wrote:
> Thanks for your reply Tibor.
> I am in a situation where the company that is hosting my
> web site has told me that I cannot enter characters ' and
> + in the column as it will cause problem, I can enter "
> however. What can you suggest that I tell them as I do
> need to input those characters.
> Thanks
> Owen
> collation selected for the
> news:12f901c4a5f1$04a6acf0$a601280a@.phx.gbl...
>
|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:67D98731-28FA-4E60-8A36-6E0A116F9E91@.microsoft.com...
> I spoke to the company that is hosting my web site and DB, although I did
not
> asked them in details as to what sort of problem it might cuase or have
> caused in the past, but they did told me that it has been a problem in the
> past where their hosting site(s) needing to store ' within SQL and have
> encountered some issue.
>
The general way of handling this is use '' (two single quotes).
ie. select * from names where lastname='O''brien'
It's not clear to me what they're doing, blocking ' entirely?
> They have asked me to seek advice on their behalf as to what can/should
they
> do to overcome such problem.
> Many thanks
> Owen
|||"Owen" <Owen@.discussions.microsoft.com> wrote in message
news:DB0511AF-7798-4821-A269-52F5FEFB3236@.microsoft.com...
> Oh yes, I forgot to mention the SQL Server is Traditional Chinese version,
> does it make any different in this case?
>
Depending on how their server is setup, it's possible to treat " like '
which then of course leads to SQL Injection attacks.
character types
i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc"
How to find out the difference between them...
Where do i find a tutorial for this kind of basic knowledge ?
regardsHomework questions are not permitted on these forums, and ones that are written so brazenly will just invite ridicule at the poster.
Have a good day now.
Regards,|||A great resource for this sort of things is our best friend Google...
Search for "T-SQL data types" and you'll get a lovely handful of links that will tell you exactly what you want to know! :)
Once you've had a read, if you any specific questions post them back here!|||Oh George, you really are far too tolerant.|||That's because I don't think this one is homework (yet) ;)|||homework is indeed allowed and is often some of the more interesting posts.|||homework is indeed allowed and is often some of the more interesting posts.
I'll get my coat.|||Hi there,
Check the SQL server 2000 help file (called books online or BOL), if you do a search on 'data types' you should find everything you need.|||I'll get my coat.
Well, it is allowed, but it is certainly treated differently.
We do our best to help the poster to the answer themselves, but if they're unwilling then so are we.
I can understand why you think this one might be homework - but I personally don't see it yet. If you asked me a year ago what the datatypes were, I could probably name 3 :p
Not to mention I'd have even less of an idea what "BoL" stands for ;)|||The ignorance of youth.
Once you get to my age (24) you tend to show less emotion and occasionally affront a manner of indifference, often unknowingly, when you fine yourself in a social predicament. Such causes of this decline to a dour emotional state vary widely, though one that invariably seems to be effective, and which is notably the most prominent, is when one is asked to answer a painfully trivial question. The immediate reaction is to swiftly determine whether the original post was said in jest, an attempted cure to the illness described above, or if in fact, it was a question. Occasionally in exercising this judgment, we may reach the wrong conclusion.|||Occasionally in exercising this judgment, we may reach the wrong conclusion.Been there, done that, got the blood-stained tee-shirts to prove it! No serious harm done, such is life.
Twenty four years old... Yikes, I remember that wistfully!
-PatP|||Twenty four years old... Yikes, I remember that wistfully!
-PatP
I'll let you know my perspective in the year 2047.|||I'll let you know my perspective in the year 2047.Assuming that I'm still alive at that point, I'll be eagerly awaiting your analysis! ;)
-PatP|||Once you get to my age (24)damn...people around you are gonna be in BIG trouble then, by the time you get to my age (47)|||I thought I was ahead of my age in cynicism, but I think Robert may have taken the gold for that ;)
kc3377, how are you doing with your original question, do you have all the answers you need?
How to find out the difference between them...
Where do i find a tutorial for this kind of basic knowledge ?
regardsHomework questions are not permitted on these forums, and ones that are written so brazenly will just invite ridicule at the poster.
Have a good day now.
Regards,|||A great resource for this sort of things is our best friend Google...
Search for "T-SQL data types" and you'll get a lovely handful of links that will tell you exactly what you want to know! :)
Once you've had a read, if you any specific questions post them back here!|||Oh George, you really are far too tolerant.|||That's because I don't think this one is homework (yet) ;)|||homework is indeed allowed and is often some of the more interesting posts.|||homework is indeed allowed and is often some of the more interesting posts.
I'll get my coat.|||Hi there,
Check the SQL server 2000 help file (called books online or BOL), if you do a search on 'data types' you should find everything you need.|||I'll get my coat.
Well, it is allowed, but it is certainly treated differently.
We do our best to help the poster to the answer themselves, but if they're unwilling then so are we.
I can understand why you think this one might be homework - but I personally don't see it yet. If you asked me a year ago what the datatypes were, I could probably name 3 :p
Not to mention I'd have even less of an idea what "BoL" stands for ;)|||The ignorance of youth.
Once you get to my age (24) you tend to show less emotion and occasionally affront a manner of indifference, often unknowingly, when you fine yourself in a social predicament. Such causes of this decline to a dour emotional state vary widely, though one that invariably seems to be effective, and which is notably the most prominent, is when one is asked to answer a painfully trivial question. The immediate reaction is to swiftly determine whether the original post was said in jest, an attempted cure to the illness described above, or if in fact, it was a question. Occasionally in exercising this judgment, we may reach the wrong conclusion.|||Occasionally in exercising this judgment, we may reach the wrong conclusion.Been there, done that, got the blood-stained tee-shirts to prove it! No serious harm done, such is life.
Twenty four years old... Yikes, I remember that wistfully!
-PatP|||Twenty four years old... Yikes, I remember that wistfully!
-PatP
I'll let you know my perspective in the year 2047.|||I'll let you know my perspective in the year 2047.Assuming that I'm still alive at that point, I'll be eagerly awaiting your analysis! ;)
-PatP|||Once you get to my age (24)damn...people around you are gonna be in BIG trouble then, by the time you get to my age (47)|||I thought I was ahead of my age in cynicism, but I think Robert may have taken the gold for that ;)
kc3377, how are you doing with your original question, do you have all the answers you need?
Character to date conversion
I am trying to write and insert statement that takes up a date value, such as,
Insert into tablename(date_column) values (date('some date', format));
I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?
Insert into tablename(date_column) values (date('some date', format));
I however do not know how exactly to convert a character string to transact sql date. can someone please lead me in correct direction? Some documentation could also help.
Thanks
--Shilpa
No special functions needed. Just insert it as text:
CREATE TABLE test
(
dateValue datetime
)
go
INSERT INTO test
VALUES ('2006-01-01T00:00:00')
go
As for format of datetime values, there are many different ways to format a date, but really only a couple of good ways. Look up "Date Data Types" in books online. It explains it really well.
|||I am new to MS domain. Couldn't get to Books online.I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?
Thanks
S
|||INSERT INTO test
VALUES ('2006-01-01T00:00:00')
Sorry about the previous post. Your solution works without the T before time insertion.
Thanks,
S
|||
There is a copy of books online (not unsurprisingly) online:
http://msdn2.microsoft.com/en-us/ms130214(sql.90).aspx
You can also download it online. They update it quarterly, and this download is from April:
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en
character strings as primary keys
Hello,
|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:
Yes, to things for the same (unique ID) purpose cannot be not confusing.
What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.
How bad is normalizing the database to the 3rd form, which requires that all fields depend on nothing but primary key. Consider the first table you create -- users. They have int primary keys, which duplicate the real primary keys -- user names. When user logs in, the user's entry is uniquely identified by its name, which is not primary key. The fundamental design rule -- avoid redundancy -- is violated. A VERY serious reason should be there for that.
Usually, design is compromised by redundancy for performance. Here, both copies are stored in one remote database, but integer keys may be located/used faster. Additionally, using long string references everywhere instead of short integer keys may save a lot of storage space (additionally increasing speed). How serious these impacts are? Am I missing something?
Usually, login names are not allowed to change. You have problems changing primary keys because all the foreign keys must be updated accordingly. Does it reveal that most user databases use character strings as primary keys?
Valentin,
Names are rarely UNIQUE -a primary requirement for a Primary Key. You only have to look at a telephone book to see that using Names as a Primary Key will be impossible. (Granted, a few 'small' countries mandate name uniqueness.)
From: http://howmanyofme.com/people/John_Smith/
There are 49,842 people in the U.S. named John Smith.
User Names, while sometimes, in small systems, seem unique, and seem suitable for Primary Key usage, will, over time prove to be a significant problem. Then the system will have to devolve to using ( Name + identifier ), e.g., JohnSmith1, JohnSmith2, etc. I have found that it is so problemmatic to use Names as keys that is is not worth even considering. As you indicate, Names 'should' allow for changes, i.e, marriage, divorce, etc. But the Primary Key 'should not' change. If you consider the domain security system, the User Name is NOT the Primary Key, and the Login Name is changable. The Primary Key is the SID, a unique identifier normally hidden from view, somewhat like using a IDENTITY field (or other indentifier) -in fact, a surrogate key. (Beware: simplistic explanition.)
It is extremely difficult to design a system for identifying People with a 'natural' Primary Key. Some adamately espouse using the SSN (in the USA) as a Primary Key. But it is NOT a naturally occuring characteristic of the individual -it is a surrogate key created by a remote system. It is legally protected, and increased being enforced, as 'sensitive' data. Most data systems 'should' NOT use the SSN as a Primary Key -of course, exceptions are allowed, or required, by law. So what does that leave as a naturally occuring characteristic of the entity -nothing really, except perhaps biometrics. Now try using the DNA sequence as a key -talk about size... (Though I know of some attempts to synthesize through the sequence redundancy to create smaller and more usable keys. -Think DNA/Fingerprint/Biometric databases.)
From: http://www.eogn.com/archives/news0202.htm
The fact that DNA is inherited and that each individual is the product of his/her progenitors means that DNA can be used to not only create unique identifications, but also to identify members of the same family, the same clan or tribal group, or the same population.
I hope I have sufficiently challenged your assertion that names are usable as primary keys.
... which duplicate the real primary keys -- user names.
A Passport number a NOT good Primary Key candidate -many people do not have a passport.
A Driver's License Number is NOT a good Primary Key candidate -many people do not have one.
So the real question is, in my system, for our business needs, what are appropriate surrogate keys?
|||By the character strings, I did not mean the real names. It is clear that real people can be namesakes. But in the internet, rarely anybody uses their numeric ids. For instance, Microsoft uses emails as user identifiers. The DNS maps between real IP addresses and memorizable symbolic names. People enjoy having fancy monikers.|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:
SQL Server 2005 Books OnlineUnique Index Design Guidelines
Designing and Creating Databases > Indexes > Designing Indexes >A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.
The engine designers foresee the situation where there is a redundancy -- two key columns in a table. But they do not explain why there may appear a need for such abnormal relation. Isn't EmployeeID redundant if they are identified by national id?
|||I think you are confusing a "primary key" with a "unique index/constraint".
I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".
Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.
For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.
In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.
|||
Tom Phillips wrote:
I think you are confusing a "primary key" with a "unique index/constraint".
Yes, to things for the same (unique ID) purpose cannot be not confusing.
I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".
What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.
Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.
This is what I wanted to see. It is not obvious, since the hash tables reach O(1) efficiency by hashing strings as well as serial numbers and the speed could easily be of the same order.
In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.
In my case I want to know the reason for overcomplicating the relations by inferring the redundant IDs to the emails I already have. The redundancy does violate the normalization as I have explained before. Normalization = elimination of redundancy.
|||
Another reason not to use SSN. SSN is not guaranteed to be unique.
Credit agencies got burned by this in the past|||Valentin,
There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.
Isn't EmployeeID redundant if they are identified by national id?
So in most situations, there will be an EmployeeID, Primary Key, and also a need to verify and be certain that there are no duplicate TaxIDs. So the Unique Constraint (Unique Index) helps in that respect.
With a Unique Constraint, you DO NOT have to have a value (as a Primary Key requires) -BUT if you do provide a value, it must be UNIQUE.
|||They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.
From what you describe in your situation, lets say you have 2 tables:
User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) (this is the max size of an SMTP email address) UNIQUE
LoginData:
LoginID INT IDENTITY(1,1) PK
UserID INT FK to User
LoginDate DATETIME
What would happen if the user changes his/her email address? The answer, NOTHING.
What I think you are suggesting is the following:
User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) UNIQUE (this is the max size of an SMTP email address)
LoginData:
LoginID INT IDENTITY(1,1) PK
UserEmailAddress varchar(255) FK to User
LoginDate DATETIME
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.
|||Now, lets further complicate your issue by talking about adding some way to find "Last Login Date".
Normalization would say, you have it:
SELECT MAX(LoginData.LoginDate) FROM LoginData WHERE LoginID=@.LoginID
However, it is a terrible waste of time to scan 1 billion records.
FYI, in MS SQL (but no other engine) you could do:
SELECT TOP 1 LoginData.LoginDate FROM LoginData WHERE LoginID=@.LoginID ORDER BY LoginData.LoginDate DESC
So most people would just add "LastLoginDate datetime" to User and update it when the user logged in.
Yes, it violates normalization rules, but one field is nothing. Then it is 2, then 10, then 100 and then normalization is out the window.
|||
They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant. You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.
<skip>
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.
The fact that changing primary key (like object address in a program) is problematic does not mean that adding an extra reference you do no incur a redundancy.
Valentin,
There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.
But I'm addressing the case when the symbolic name is unique and mandatory. In our app, user logs in by email. In this case, email is not just unique. Since it is used to uniquely identify users, it is a key (nulls are not allowed). The int ID uses the same purpose. It duplicates the email field. The same situations I have in 'groups' table where human-manageable groups must have unique names but we refer the groups by ID. In addition to being redundant, the design overcomplicates the things because user normally wants to see the group (s)he belongs in text rather than an integer id of the group.
Though, the keys are duplicated, I cannot bring an inconsistency example, which proves the redundancy. But 3rd normal form requires the record fields to depend only on the primary key. Meantime, users in my database are identified/refered/addressed/pointed to by email. It is a natural primary key. Integer IDs were artificially introduced to do the same job.
character strings as primary keys
Hello,
|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:
Yes, to things for the same (unique ID) purpose cannot be not confusing.
What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.
How bad is normalizing the database to the 3rd form, which requires that all fields depend on nothing but primary key. Consider the first table you create -- users. They have int primary keys, which duplicate the real primary keys -- user names. When user logs in, the user's entry is uniquely identified by its name, which is not primary key. The fundamental design rule -- avoid redundancy -- is violated. A VERY serious reason should be there for that.
Usually, design is compromised by redundancy for performance. Here, both copies are stored in one remote database, but integer keys may be located/used faster. Additionally, using long string references everywhere instead of short integer keys may save a lot of storage space (additionally increasing speed). How serious these impacts are? Am I missing something?
Usually, login names are not allowed to change. You have problems changing primary keys because all the foreign keys must be updated accordingly. Does it reveal that most user databases use character strings as primary keys?
Valentin,
Names are rarely UNIQUE -a primary requirement for a Primary Key. You only have to look at a telephone book to see that using Names as a Primary Key will be impossible. (Granted, a few 'small' countries mandate name uniqueness.)
From: http://howmanyofme.com/people/John_Smith/
There are 49,842 people in the U.S. named John Smith.
User Names, while sometimes, in small systems, seem unique, and seem suitable for Primary Key usage, will, over time prove to be a significant problem. Then the system will have to devolve to using ( Name + identifier ), e.g., JohnSmith1, JohnSmith2, etc. I have found that it is so problemmatic to use Names as keys that is is not worth even considering. As you indicate, Names 'should' allow for changes, i.e, marriage, divorce, etc. But the Primary Key 'should not' change. If you consider the domain security system, the User Name is NOT the Primary Key, and the Login Name is changable. The Primary Key is the SID, a unique identifier normally hidden from view, somewhat like using a IDENTITY field (or other indentifier) -in fact, a surrogate key. (Beware: simplistic explanition.)
It is extremely difficult to design a system for identifying People with a 'natural' Primary Key. Some adamately espouse using the SSN (in the USA) as a Primary Key. But it is NOT a naturally occuring characteristic of the individual -it is a surrogate key created by a remote system. It is legally protected, and increased being enforced, as 'sensitive' data. Most data systems 'should' NOT use the SSN as a Primary Key -of course, exceptions are allowed, or required, by law. So what does that leave as a naturally occuring characteristic of the entity -nothing really, except perhaps biometrics. Now try using the DNA sequence as a key -talk about size... (Though I know of some attempts to synthesize through the sequence redundancy to create smaller and more usable keys. -Think DNA/Fingerprint/Biometric databases.)
From: http://www.eogn.com/archives/news0202.htm
The fact that DNA is inherited and that each individual is the product of his/her progenitors means that DNA can be used to not only create unique identifications, but also to identify members of the same family, the same clan or tribal group, or the same population.
I hope I have sufficiently challenged your assertion that names are usable as primary keys.
... which duplicate the real primary keys -- user names.
A Passport number a NOT good Primary Key candidate -many people do not have a passport.
A Driver's License Number is NOT a good Primary Key candidate -many people do not have one.
So the real question is, in my system, for our business needs, what are appropriate surrogate keys?
|||By the character strings, I did not mean the real names. It is clear that real people can be namesakes. But in the internet, rarely anybody uses their numeric ids. For instance, Microsoft uses emails as user identifiers. The DNS maps between real IP addresses and memorizable symbolic names. People enjoy having fancy monikers.|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:
SQL Server 2005 Books OnlineUnique Index Design Guidelines
Designing and Creating Databases > Indexes > Designing Indexes >A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.
The engine designers foresee the situation where there is a redundancy -- two key columns in a table. But they do not explain why there may appear a need for such abnormal relation. Isn't EmployeeID redundant if they are identified by national id?
|||I think you are confusing a "primary key" with a "unique index/constraint".
I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".
Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.
For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.
In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.
|||
I think you are confusing a "primary key" with a "unique index/constraint".
Yes, to things for the same (unique ID) purpose cannot be not confusing.
I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".
What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.
Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.
This is what I wanted to see. It is not obvious, since the hash tables reach O(1) efficiency by hashing strings as well as serial numbers and the speed could easily be of the same order.
In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.
In my case I want to know the reason for overcomplicating the relations by inferring the redundant IDs to the emails I already have. The redundancy does violate the normalization as I have explained before. Normalization = elimination of redundancy.
|||
Another reason not to use SSN. SSN is not guaranteed to be unique.
Credit agencies got burned by this in the past|||Valentin,
There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.
Isn't EmployeeID redundant if they are identified by national id?
So in most situations, there will be an EmployeeID, Primary Key, and also a need to verify and be certain that there are no duplicate TaxIDs. So the Unique Constraint (Unique Index) helps in that respect.
With a Unique Constraint, you DO NOT have to have a value (as a Primary Key requires) -BUT if you do provide a value, it must be UNIQUE.
|||They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.
From what you describe in your situation, lets say you have 2 tables:
User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) (this is the max size of an SMTP email address) UNIQUE
LoginData:
LoginID INT IDENTITY(1,1) PK
UserID INT FK to User
LoginDate DATETIME
What would happen if the user changes his/her email address? The answer, NOTHING.
What I think you are suggesting is the following:
User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) UNIQUE (this is the max size of an SMTP email address)
LoginData:
LoginID INT IDENTITY(1,1) PK
UserEmailAddress varchar(255) FK to User
LoginDate DATETIME
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.
|||Now, lets further complicate your issue by talking about adding some way to find "Last Login Date".
Normalization would say, you have it:
SELECT MAX(LoginData.LoginDate) FROM LoginData WHERE LoginID=@.LoginID
However, it is a terrible waste of time to scan 1 billion records.
FYI, in MS SQL (but no other engine) you could do:
SELECT TOP 1 LoginData.LoginDate FROM LoginData WHERE LoginID=@.LoginID ORDER BY LoginData.LoginDate DESC
So most people would just add "LastLoginDate datetime" to User and update it when the user logged in.
Yes, it violates normalization rules, but one field is nothing. Then it is 2, then 10, then 100 and then normalization is out the window.
|||
They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant. You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.
<skip>
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.
The fact that changing primary key (like object address in a program) is problematic does not mean that adding an extra reference you do no incur a redundancy.
Valentin,
There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.
But I'm addressing the case when the symbolic name is unique and mandatory. In our app, user logs in by email. In this case, email is not just unique. Since it is used to uniquely identify users, it is a key (nulls are not allowed). The int ID uses the same purpose. It duplicates the email field. The same situations I have in 'groups' table where human-manageable groups must have unique names but we refer the groups by ID. In addition to being redundant, the design overcomplicates the things because user normally wants to see the group (s)he belongs in text rather than an integer id of the group.
Though, the keys are duplicated, I cannot bring an inconsistency example, which proves the redundancy. But 3rd normal form requires the record fields to depend only on the primary key. Meantime, users in my database are identified/refered/addressed/pointed to by email. It is a natural primary key. Integer IDs were artificially introduced to do the same job.
Character String Query Doesnt Fill Dataset
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
try to build your command in ASP and pass it to SQL command command object
like:
Dim
yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)yourCommand.CommandType = CommandType.Text
Maybe it will work.
When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.
Thanks
character sets, sort order, collations
Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
> select *
> from ::fn_helpcollations()
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
> select *
> from ::fn_helpcollations()
character sets, sort order, collations
Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/e...lation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/e...lation_3oa6.asp
> select *
> from ::fn_helpcollations()
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/e...lation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/e...lation_3oa6.asp
> select *
> from ::fn_helpcollations()
character sets, sort order, collations
Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.
these might be of help:
http://msdn.microsoft.com/library/en...ation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>
|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en...ation_3oa6.asp
> select *
> from ::fn_helpcollations()
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.
these might be of help:
http://msdn.microsoft.com/library/en...ation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>
|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en...ation_3oa6.asp
> select *
> from ::fn_helpcollations()
character sets in SQL server
Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Thanks
VishalVishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?
First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.
On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.
On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...
So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.
If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.
Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
character sets in SQL server
Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Thanks
Vishal
Vishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?
First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.
On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.
On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...
So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.
If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.
Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Thanks
Vishal
Vishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?
First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.
On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.
On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...
So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.
If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.
Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Character sets
So, I've looked around, read what I can find and I think I know the answer
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>
Character sets
So, I've looked around, read what I can find and I think I know the answer
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>
Character set, Sort Order, Unicode Collation
Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksThis should do it:
select serverproperty('collation')
Regards,
Paul Ibison|||Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
--
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksThis should do it:
select serverproperty('collation')
Regards,
Paul Ibison|||Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
--
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Character set, Sort Order, Unicode Collation
Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksHi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx
.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksHi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx
.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Character set, Sort Order, Unicode Collation
Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
Thanks
Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
Thanks
Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks
Character set translation / tp performance
Hi,
I have a problem regarding the perfomance of a stp in combination with
character translation.
The following happens.
We have an automated installation script (nt command file) for
creating all stored procedures in a database.
At first we used osql in this script, but with osql the international
characters (, etc) were not entered correctly into the database.
The tip given in some newsgroups was to use isql in stead of osql, and
turn off automatic ansi to oem translation.
This worked perfectly. However now some stored procedures are executed
much slower than before (and they take up much cpu). When I drop and
recreate the stored procedure using Query Analyser, the performance
goes up again.
When I look through the messages in this group, the advise is to use
osql instead of isql. But I just changed to isql because of the
character translation!
Does anyone have a solution for this?You might try including SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
at the beginning of your script file. QA (which uses ODBC) sets these
options on by default and this may result in a different execution plan.
Regarding ISQL, you might consider saving your file in Unicode format
and using OSQL instead. You can then use the OSQL -I command line
parameter to turn on the QUOTED_IDENTIFIER option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"Dick Zeeman" <minicontainer@.hotmail.com> wrote in message
news:fad166e1.0309010100.3464bf9e@.posting.google.c om...
> Hi,
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!
> Does anyone have a solution for this?|||[posted and mailed, please reply in news]
Dick Zeeman (minicontainer@.hotmail.com) writes:
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!
Dan's reply gave the answer you needed to get things working. I'll only
add some explanation to this.
With ISQL, all SET options are off. With Query Analyzer, a couple are on
by default. The ones that Dan mentioned, ANSI_NULLS and QUOTED_IDENTIFIER
are particularly important, because the setting at compile time is
saved with the procedures.
I don't think that QUOTED_IDENTIFIER can affect the query plan for
queries in general. ANSI_NULLS could in theory, although I don't know
how common this is. However, there are two cases where these settings
are essential, and that is when you have queries that involves indexed
computed columns and indexed views. For these indexes to be used, these
two settings must be ON. This is the most likely reason why you saw
such a drastic difference in execution. It is also therefore Dan tipped
you to use -I with OSQL.
I should also add that queries that involves linked queries requires
ANSI_NULLS to be on.
Finally, I should add to Dan's reply that you can use QA to save files
in Unicode format. You can actually even save in OEM format from QA,
if you like.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan and Erland thanks for you reaction.
We will try saving all scripts in oem format.
Unicode is not an option, since we use SourceSafe and SS does not like
unicode files.
Regards
Dick Zeeman
Subscribe to:
Posts (Atom)