Re: embedded ' in strings
From: Brad Syputa - MS (bradsy_at_Online.Microsoft.com)
Date: 07/19/04
- Next message: Brad Syputa - MS: "RE: Comments on Merge Replication/RDA"
- Previous message: albert: "Comments on Merge Replication/RDA"
- In reply to: mgarner1980: "Re: embedded ' in strings"
- Next in thread: mgarner1980: "Re: embedded ' in strings"
- Reply: mgarner1980: "Re: embedded ' in strings"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Jul 2004 17:43:19 GMT
This looks like a bug. I have tried it here myself and it fails with an
unknown error. If you would like to pursue this further and get a fix for
this, please contact Microsoft PSS. Since this is a bug, any fees they
charge you will be refunded. The biggest issue on our side will be figuring
out why the index is causing this to fail. PSS may also be able to come up
with a work around for you.
At this time, the only work arounds I can think of is to remove the index,
or to not check for a length greater than the column allows.
Nice find!!!
I will forward this mail on to my PSS contact, so that if you do start a
case, they have something to work off of.
Repro:
create table foo (col1 int, col2 nchar(6));
create index index_foo on foo (col2 desc);
--create index index_foo1 on foo (col2 desc,col1 desc);
select * from foo where col2 = 'asd''fg';
select * from foo where col2 = 'asd''fgdd';
quit;
--------------------
| From: "mgarner1980" <mgarner@kbsi.com>
| References: <u4i30KfZEHA.3092@tk2msftngp13.phx.gbl>
<j9UqnzDaEHA.3272@cpmsftngxa06.phx.gbl>
<uwWvxZfaEHA.2516@TK2MSFTNGP10.phx.gbl>
<qMx5D6oaEHA.2924@cpmsftngxa06.phx.gbl>
| Subject: Re: embedded ' in strings
| Date: Fri, 16 Jul 2004 10:00:09 -0500
| Lines: 127
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <eCrzXW0aEHA.3404@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.ce
| NNTP-Posting-Host: 209.189.225.79
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12879
| X-Tomcat-NG: microsoft.public.sqlserver.ce
|
| Nope, you seem to have it right. I've been doing some more testing. Here's
| what I've found. I have 2 tables that have a nchar(6) column.
| Table1 has the column "badge" whic is included in several indexes. Any
time
| I try "select * from table1 where badge = (insert any string longer than
| six characters). I get the error. I have some screen shots, but last time
I
| tried to attach some, my mail never went through.
|
| table2 has a column named fman whic is nchar(6) , but is not included in
any
| indexes. I can perform a select query with a string of any length and not
| get the error.
|
| I've started thi s thread out with the assumption that I was having
problems
| with the single quote issue, but really I'n having a problem with select
| queries with vaues longer than the column being queried (on some tables).
I
| might also add that this database is a merge subscription.
|
| I just tried the same test on another table with nchar(2) and an index,
and
| it didn't fail with a 25 length query. So it looks like this problem is a
| particular type of problem.
|
| I guess I could send you the sqlce database so you can test it. (it's a
| subset of our customers data, so I'll have to check on that).
|
|
| ""Brad Syputa - MS"" <bradsy@Online.Microsoft.com> wrote in message
| news:qMx5D6oaEHA.2924@cpmsftngxa06.phx.gbl...
| > I tried what you said and it works fine for me. Was there something I
was
| > missing?
| >
| > Create Table Foo (Col1 nchar(6));
| > Insert Into Foo (Col1) Values ('123''45');
| > Success.
| >
| >
| > --------------------
| > | From: "mgarner1980" <mgarner@kbsi.com>
| > | References: <u4i30KfZEHA.3092@tk2msftngp13.phx.gbl>
| > <j9UqnzDaEHA.3272@cpmsftngxa06.phx.gbl>
| > | Subject: Re: embedded ' in strings
| > | Date: Wed, 14 Jul 2004 18:01:04 -0500
| > | Lines: 59
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| > | Message-ID: <uwWvxZfaEHA.2516@TK2MSFTNGP10.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.ce
| > | NNTP-Posting-Host: 209.189.225.79
| > | Path:
| >
|
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08
| > phx.gbl!TK2MSFTNGP10.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12855
| > | X-Tomcat-NG: microsoft.public.sqlserver.ce
| > |
| > | Sorry should've followed up on this one. Here's what was happening.
The
| > | field I was trying to use the where clause on was a CHAR length 6.
| > | So let's say the user types in 123'45. My function would turn it in to
| > | 123''45. which is length 7. When I tried to execute the query I would
| get
| > | the null reference exception.
| > | So for now if there's a ' I throw it out. it's either that or truncate
| it
| > to
| > | six characters.
| > | The thing that had me going was that the same queries worked fine
insql
| > | query analyzer for the desktop. BTW the same error also occures in th
| > esqlce
| > | query analyzer if you want to see what I'm talking about. It seems
like
| a
| > | bogus error since once the "escape" is processed, the end result
should
| > be a
| > | string length 6.
| > |
| > | ""Brad Syputa - MS"" <bradsy@Online.Microsoft.com> wrote in message
| > | news:j9UqnzDaEHA.3272@cpmsftngxa06.phx.gbl...
| > | > Hi mgarner,
| > | >
| > | > Can you explain a little better how it throws a null reference
| > exception?
| > | I
| > | > us a double single quote and it works fine for me.
| > | >
| > | >
| > | > --------------------
| > | > | From: "mgarner1980" <mgarner@kbsi.com>
| > | > | Subject: embedded ' in strings
| > | > | Date: Fri, 9 Jul 2004 15:24:14 -0500
| > | > | Lines: 13
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| > | > | Message-ID: <u4i30KfZEHA.3092@tk2msftngp13.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.ce
| > | > | NNTP-Posting-Host: 209.189.225.79
| > | > | Path:
| > | >
| > |
| >
|
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
| > | > 8.phx.gbl!tk2msftngp13.phx.gbl
| > | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12805
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.ce
| > | > |
| > | > | I'm having problems with queries like the following in
sqlserverce.
| > | > |
| > | > | select name from employee where lastname = 'O'Brien'
| > | > |
| > | > | If I use double single quote like O''Brien, that works fine on my
| > | > sqlser2000
| > | > | server, but throws a null reference error on sqlserverce merge
| > | > | subscription.
| > | > |
| > | > | I've tried paramaterized queries with to no avail as well.
| > | > |
| > | > | Any ideas?
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
- Next message: Brad Syputa - MS: "RE: Comments on Merge Replication/RDA"
- Previous message: albert: "Comments on Merge Replication/RDA"
- In reply to: mgarner1980: "Re: embedded ' in strings"
- Next in thread: mgarner1980: "Re: embedded ' in strings"
- Reply: mgarner1980: "Re: embedded ' in strings"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
