problem with japanese character 俱

Hi All,

Create a table , and put in some row 子ど and 俱

and fire a query something like SELECT * FROM mytable WHERE (myColumn = '子ど')

it gives me right result.

But if i fire

SELECT * FROM mytable WHERE (myColumn = '俱')

It does not return any result for the same even if myColumn has '俱'.

Surprisnly if i fire query like "SELECT * FROM mytable" it

correctly dispalys 俱.

What's the reason for the same ? Why does it is not able to search me on this japanese character(俱).Collation is Japanese_Unicode_CI_AS

Regards,

Sunil

Hi Sunil,

you wil have to indicate that the string is unicode in your query:

SELECT * FROM mytable WHERE (myColumn = N'俱')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens for your quick response.

I have one query why it was working without N in 子ど but not in 俱

Regards,
Sunil

problem with japanese character 俱

Hi All,
Create a table , and put in some row Xど and 俱
and fire a query something like SELECT * FROM mytable WHERE
(myColumn = 'Xど')
it gives me right result.
But if i fire
SELECT * FROM mytable WHERE (myColumn = '俱')
It does not return any result for the same even if myColumn has '俱'.
Surprisnly if i fire query like "SELECT * FROM mytable" it
correctly dispalys 俱.
What's the reason for the same ? Why does it is not able to search me on
this japanese character(俱).Collation is Japanese_Unicode_CI_AS
Regards,
Sunil
Please don't multi-post. If you really need to post in multiple
newsgroups, you should cross-post (post only one time, but with both
newsgroups in the To: field).
I've responded to this question in the
microsoft.public.sqlserver.programming newsgroup.
Razvan

Problem with IN

I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.

Originally posted by Franky
I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as

Declare @.Query nVarchar(1000)

SET @.Query=N'SELECT * FROM Table ' +
'WHERE Table.ID IN( ' + @.values + ')'

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

====================================

Where @.values must be

@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman|||Originally posted by blindman
Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman

Or this way

SELECT *
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman|||Originally posted by blindman
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman

blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:

create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')

declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where CHARINDEX(cast(id as varchar),@.list)>0

Problem with Delete On Linked Server

I am getting time-out errors when I try to perform a simple delete on a
linked server. The command is:

Delete From MyTable Where PKID=12345

I have tried executing this command directly from Query Analyzer, but it
just times out. However, when I run it from QA against the server itself
(rather than from my local server against a linked server), it executes
immediately. Similarly, if I run the same SQL command through an ODBC linked
table in an Access 2000 MDB file (tweaking the syntax slightly), it also
executes immediately. Only if I run it from SQL 7 as a stored procedure or a
QA command against a linked server does it time-out.

I have no problems inserting rows using any of the three methods noted
above. It is only with deleting rows through the linked server that it times
out.

Thanks for any assistance.

NeilCorrection: The syntax used against the linked server is the four-art
syntax:

Delete From svr.db.dbo.MyTable Where PKID=12345

Also, the same problem occurs with updates as with deletes (but not
inserts).

Thanks,

Neil

"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil|||I ran a profiler trace while inserting and then deleting a row from the
table on the linked server. I added all events to the trace. However, for
both the insert and delete, it only showed a single SQL:BatchCompleted
event -- except that after the line for the delete command, there was a
second, blank, line in the trace. The two lines for the delete didn't show
in the profiler until the batch was canceled. Below are the lines from
Profiler. Any assistance is appreciated.

Thanks,

Neil

Event Class Text Application Name NT User Name SQL User Name CPU Reads
Writes Duration Connection ID SPID Start Time
+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
02:23:56.827

"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil|||Which box were you running the trace against? Try running it
against the destination (the linked server) instead of the
server that you are executing the command from.
Another thing to see if it works - try executing the
statement using OpenQuery instead of the 4 part name and see
if that makes a difference.

-Sue

On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
wrote:

>I ran a profiler trace while inserting and then deleting a row from the
>table on the linked server. I added all events to the trace. However, for
>both the insert and delete, it only showed a single SQL:BatchCompleted
>event -- except that after the line for the delete command, there was a
>second, blank, line in the trace. The two lines for the delete didn't show
>in the profiler until the batch was canceled. Below are the lines from
>Profiler. Any assistance is appreciated.
>Thanks,
>Neil
>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>Writes Duration Connection ID SPID Start Time
>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>02:23:56.827
>
>"Neil" <nospam@.nospam.net> wrote in message
>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>I am getting time-out errors when I try to perform a simple delete on a
>>linked server. The command is:
>>
>> Delete From MyTable Where PKID=12345
>>
>> I have tried executing this command directly from Query Analyzer, but it
>> just times out. However, when I run it from QA against the server itself
>> (rather than from my local server against a linked server), it executes
>> immediately. Similarly, if I run the same SQL command through an ODBC
>> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
>> also executes immediately. Only if I run it from SQL 7 as a stored
>> procedure or a QA command against a linked server does it time-out.
>>
>> I have no problems inserting rows using any of the three methods noted
>> above. It is only with deleting rows through the linked server that it
>> times out.
>>
>> Thanks for any assistance.
>>
>> Neil
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.

In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:

"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."

Any ideas about what that means?

Thanks!

Neil

"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>>I ran a profiler trace while inserting and then deleting a row from the
>>table on the linked server. I added all events to the trace. However, for
>>both the insert and delete, it only showed a single SQL:BatchCompleted
>>event -- except that after the line for the delete command, there was a
>>second, blank, line in the trace. The two lines for the delete didn't show
>>in the profiler until the batch was canceled. Below are the lines from
>>Profiler. Any assistance is appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>>Writes Duration Connection ID SPID Start Time
>>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
>> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>>02:23:56.827
>>
>>
>>
>>"Neil" <nospam@.nospam.net> wrote in message
>>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>>I am getting time-out errors when I try to perform a simple delete on a
>>>linked server. The command is:
>>>
>>> Delete From MyTable Where PKID=12345
>>>
>>> I have tried executing this command directly from Query Analyzer, but it
>>> just times out. However, when I run it from QA against the server itself
>>> (rather than from my local server against a linked server), it executes
>>> immediately. Similarly, if I run the same SQL command through an ODBC
>>> linked table in an Access 2000 MDB file (tweaking the syntax slightly),
>>> it
>>> also executes immediately. Only if I run it from SQL 7 as a stored
>>> procedure or a QA command against a linked server does it time-out.
>>>
>>> I have no problems inserting rows using any of the three methods noted
>>> above. It is only with deleting rows through the linked server that it
>>> times out.
>>>
>>> Thanks for any assistance.
>>>
>>> Neil
>>>
>|||OK, I found out that the error I got originally (per other message) was due
to the fact that Delete doesn't return rows, as OpenQuery is looking for.
So, per http://support.microsoft.com/defaul...b;en-us;Q270119, I
changed it to:

Delete OPENQUERY(abcweb, 'Select ImageID From images Where
ImageID=99987')

and it worked! Would still be good to find out why the original method
wouldn't work for deletes or updates, but did work for inserts. But at least
this works.

Thanks!

Neil

"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>>I ran a profiler trace while inserting and then deleting a row from the
>>table on the linked server. I added all events to the trace. However, for
>>both the insert and delete, it only showed a single SQL:BatchCompleted
>>event -- except that after the line for the delete command, there was a
>>second, blank, line in the trace. The two lines for the delete didn't show
>>in the profiler until the batch was canceled. Below are the lines from
>>Profiler. Any assistance is appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>>Writes Duration Connection ID SPID Start Time
>>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
>> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>>02:23:56.827
>>
>>
>>
>>"Neil" <nospam@.nospam.net> wrote in message
>>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>>I am getting time-out errors when I try to perform a simple delete on a
>>>linked server. The command is:
>>>
>>> Delete From MyTable Where PKID=12345
>>>
>>> I have tried executing this command directly from Query Analyzer, but it
>>> just times out. However, when I run it from QA against the server itself
>>> (rather than from my local server against a linked server), it executes
>>> immediately. Similarly, if I run the same SQL command through an ODBC
>>> linked table in an Access 2000 MDB file (tweaking the syntax slightly),
>>> it
>>> also executes immediately. Only if I run it from SQL 7 as a stored
>>> procedure or a QA command against a linked server does it time-out.
>>>
>>> I have no problems inserting rows using any of the three methods noted
>>> above. It is only with deleting rows through the linked server that it
>>> times out.
>>>
>>> Thanks for any assistance.
>>>
>>> Neil
>>>
>

Problem with Delete On Linked Server

I am getting time-out errors when I try to perform a simple delete on a
linked server. The command is:
Delete From MyTable Where PKID=12345
I have tried executing this command directly from Query Analyzer, but it
just times out. However, when I run it from QA against the server itself
(rather than from my local server against a linked server), it executes
immediately. Similarly, if I run the same SQL command through an ODBC linked
table in an Access 2000 MDB file (tweaking the syntax slightly), it also
executes immediately. Only if I run it from SQL 7 as a stored procedure or a
QA command against a linked server does it time-out.
I have no problems inserting rows using any of the three methods noted
above. It is only with deleting rows through the linked server that it times
out.
Thanks for any assistance.
NeilCorrection: The syntax used against the linked server is the four-art
syntax:
Delete From svr.db.dbo.MyTable Where PKID=12345
Also, the same problem occurs with updates as with deletes (but not
inserts).
Thanks,
Neil
"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil
>|||I ran a profiler trace while inserting and then deleting a row from the
table on the linked server. I added all events to the trace. However, for
both the insert and delete, it only showed a single SQL:BatchCompleted
event -- except that after the line for the delete command, there was a
second, blank, line in the trace. The two lines for the delete didn't show
in the profiler until the batch was canceled. Below are the lines from
Profiler. Any assistance is appreciated.
Thanks,
Neil
Event Class Text Application Name NT User Name SQL User Name CPU Reads
Writes Duration Connection ID SPID Start Time
+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
02:23:56.827
"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil
>|||Which box were you running the trace against? Try running it
against the destination (the linked server) instead of the
server that you are executing the command from.
Another thing to see if it works - try executing the
statement using OpenQuery instead of the 4 part name and see
if that makes a difference.
-Sue
On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
wrote:

>I ran a profiler trace while inserting and then deleting a row from the
>table on the linked server. I added all events to the trace. However, for
>both the insert and delete, it only showed a single SQL:BatchCompleted
>event -- except that after the line for the delete command, there was a
>second, blank, line in the trace. The two lines for the delete didn't show
>in the profiler until the batch was canceled. Below are the lines from
>Profiler. Any assistance is appreciated.
>Thanks,
>Neil
>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>Writes Duration Connection ID SPID Start Time
>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>02:23:56.827
>
>"Neil" <nospam@.nospam.net> wrote in message
>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.
In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:
"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."
Any ideas about what that means?
Thanks!
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.
4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>
>|||OK, I found out that the error I got originally (per other message) was due
to the fact that Delete doesn't return rows, as OpenQuery is looking for.
So, per http://support.microsoft.com/defaul...;en-us;Q270119, I
changed it to:
Delete OPENQUERY(abcweb, 'Select ImageID From images Where
ImageID=99987')
and it worked! Would still be good to find out why the original method
wouldn't work for deletes or updates, but did work for inserts. But at least
this works.
Thanks!
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.
4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>
>