Help with Special Characters in Updategram

I'm looking for help and hope you can provide it. I've got an
updategram with special ISO Latin 1 Characters like begin and end
quotes #147 and #148 or copyright character #169. The UpdateGram fails
with the message "400.100 Bad Request, Query not specified". The
UpdateGram works when the characters are removed. If I try to HTML
encode the characters (“ ” or ©) then the updategram
saves but the characters are replaced with '?'.
Any ideas how to get around this?Hi Todd,
The error message you are seeing is not coming from the Updagram component
and is coming from some other layer of the web application. How are you
sending this to the database? One way to check if something is wrong with
the Updategram is to try to send the Updategram directly through ADO or
managed provider. If that does not work and you see an error then please
post a snippet of the Updategram that you think is causing the problem.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided AS IS, and confers on rights or warranties.
"Todd" <todd_gochenour@.msn.com> wrote in message
news:1110836825.988706.296230@.z14g2000cwz.googlegroups.com...
> I'm looking for help and hope you can provide it. I've got an
> updategram with special ISO Latin 1 Characters like begin and end
> quotes #147 and #148 or copyright character #169. The UpdateGram fails
> with the message "400.100 Bad Request, Query not specified". The
> UpdateGram works when the characters are removed. If I try to HTML
> encode the characters (“ ” or ©) then the updategram
> saves but the characters are replaced with '?'.
> Any ideas how to get around this?
>|||The updategram is posted to the SQLXML website. The content of the
updategram is:
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<ROOT xmlns:updg=3D"urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:before>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test"/>
</updg:before>
<updg:after>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test" VALUE=3D"Tomorrow's Hope=A9"/>
</updg:after>
</updg:sync>
</ROOT>
Note the curly apostrophe and the copyright symbol. If they are
removed, then the updategram is successful.|||Also, I have not attempted to submit this updategram through the ADO
interface. It'll take some work to get this setup, and my current
configuration is using the HTTP based interface.

Help with Special Characters in Updategram

I'm looking for help and hope you can provide it. I've got an
updategram with special ISO Latin 1 Characters like begin and end
quotes #147 and #148 or copyright character #169. The UpdateGram fails
with the message "400.100 Bad Request, Query not specified". The
UpdateGram works when the characters are removed. If I try to HTML
encode the characters (“ ” or ©) then the updategram
saves but the characters are replaced with '?'.
Any ideas how to get around this?
Hi Todd,
The error message you are seeing is not coming from the Updagram component
and is coming from some other layer of the web application. How are you
sending this to the database? One way to check if something is wrong with
the Updategram is to try to send the Updategram directly through ADO or
managed provider. If that does not work and you see an error then please
post a snippet of the Updategram that you think is causing the problem.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided AS IS, and confers on rights or warranties.
"Todd" <todd_gochenour@.msn.com> wrote in message
news:1110836825.988706.296230@.z14g2000cwz.googlegr oups.com...
> I'm looking for help and hope you can provide it. I've got an
> updategram with special ISO Latin 1 Characters like begin and end
> quotes #147 and #148 or copyright character #169. The UpdateGram fails
> with the message "400.100 Bad Request, Query not specified". The
> UpdateGram works when the characters are removed. If I try to HTML
> encode the characters (“ ” or ©) then the updategram
> saves but the characters are replaced with '?'.
> Any ideas how to get around this?
>
|||The updategram is posted to the SQLXML website. The content of the
updategram is:
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<ROOT xmlns:updg=3D"urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:before>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test"/>
</updg:before>
<updg:after>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test" VALUE=3D"Tomorrow's Hope=A9"/>
</updg:after>
</updg:sync>
</ROOT>
Note the curly apostrophe and the copyright symbol. If they are
removed, then the updategram is successful.
|||Also, I have not attempted to submit this updategram through the ADO
interface. It'll take some work to get this setup, and my current
configuration is using the HTTP based interface.

Help with looping through records in stored procedure

I'm not even sure if I'm doing this correctly. Basically, my goal is
to provide the stored procedure 2 pieces of information that may
change:
- a member ID #
- a "WHERE" clause statement
I want the SP to loop through all the records it finds, and as it is
looping through, insert records into a relational table if necessary.
I am getting syntax errors when trying the below.
========================================
===================
CREATE PROCEDURE dbo.InsertAvpArticles2
@.premiumUserId int,
@.whereClause varchar(5000)
AS
DECLARE @.articleId int
DECLARE @.categoryId int
DECLARE @.hotelId int
SET NOCOUNT ON
WHILE EXISTS(EXEC('SELECT @.articleId=articleId, @.categoryId=categoryId,
@.hotelId=hotelId FROM tblArticles WHERE' + @.whereClause))
BEGIN
IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
premiumUserId = @.premiumUserId AND articleId = @.articleId)
BEGIN
INSERT tblAVPArticles
(premiumUserId, articleId, categoryId, hotelId, createdByAvp)
VALUES
(@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
END
END
GOHi
What syntax errors are you getting --which line?
For example, on line 13 , there is no space b/w WHERE and the @.whereclause
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<CarlosSanchezJr@.gmail.com> wrote in message
news:1149639693.901501.151990@.g10g2000cwb.googlegroups.com...
> I'm not even sure if I'm doing this correctly. Basically, my goal is
> to provide the stored procedure 2 pieces of information that may
> change:
> - a member ID #
> - a "WHERE" clause statement
> I want the SP to loop through all the records it finds, and as it is
> looping through, insert records into a relational table if necessary.
> I am getting syntax errors when trying the below.
> ========================================
===================
> CREATE PROCEDURE dbo.InsertAvpArticles2
> @.premiumUserId int,
> @.whereClause varchar(5000)
> AS
> DECLARE @.articleId int
> DECLARE @.categoryId int
> DECLARE @.hotelId int
> SET NOCOUNT ON
> WHILE EXISTS(EXEC('SELECT @.articleId=articleId, @.categoryId=categoryId,
> @.hotelId=hotelId FROM tblArticles WHERE' + @.whereClause))
> BEGIN
> IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> premiumUserId = @.premiumUserId AND articleId = @.articleId)
> BEGIN
> INSERT tblAVPArticles
> (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> VALUES
> (@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
> END
> END
> GO
>|||I'll get the following error:
Error 156: Incorrect syntax near the keyword 'EXEC'
Thanks!
Jack Vamvas wrote:
> Hi
> What syntax errors are you getting --which line?
> For example, on line 13 , there is no space b/w WHERE and the @.whereclause
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> <CarlosSanchezJr@.gmail.com> wrote in message
> news:1149639693.901501.151990@.g10g2000cwb.googlegroups.com...|||Actually I find a method of how to do this, and I'm trying it below. I
get an error though (Must declare the variable @.li_seq_no) about
declaring a variable, although I am declaring it. Any thoughts?
CREATE PROCEDURE dbo.InsertAvpArticles2
@.premiumUserId int,
@.whereClause varchar(5000)
AS
DECLARE @.articleId int
DECLARE @.categoryId int
DECLARE @.hotelId int
DECLARE @.li_seq_no int
/* hold the current pk we're working on */
/* Get starting row primary key */
EXEC('SELECT @.li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
@.whereClause )
/* While loop, processing ALL records, see the end of the loop on how
to
get the "next pk" selection */
WHILE (@.li_seq_no != NULL)
BEGIN
/* Select what you want from the row at the current pk */
SELECT @.articleId=articleId, @.categoryId=categoryId, @.hotelId=hotelId
FROM tblArticles
WHERE articleId = @.li_seq_no
IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
premiumUserId = @.premiumUserId AND articleId = @.articleId)
BEGIN
INSERT tblAVPArticles
(premiumUserId, articleId, categoryId, hotelId, createdByAvp)
VALUES
(@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
END
/* get the next pk like this */
SELECT @.li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
>@.li_seq_no
END /*this goes back to the beginning of the while loop */|||Here is one way (untested of course ;-( )
you can also use an (evil) cursor for this
I am almost positive that you can do this in 1 set statement, but
without data and DLL I won't embark on such a mission
Less talk more code, here it is
CREATE PROCEDURE dbo.InsertAvpArticles2
@.premiumUserId int,
@.whereClause varchar(5000)
AS
DECLARE @.articleId int
DECLARE @.categoryId int
DECLARE @.hotelId int
SET NOCOUNT ON
create table #temp (id int identity,
articleId int,categoryId int,hotelId int)
exec ('insert #temp(articleId ,categoryId ,hotelId)
select articleId ,categoryId ,hotelId
FROM tblArticles WHERE' + @.whereClause)
declare @.LoopID int, @.MaxID int
select @.LoopID=1, @.MaxID = MAX(ID) from #temp
WHILE @.LoopID <= @.MaxID
BEGIN
SELECT @.articleId=articleId, @.categoryId=categoryId,
@.hotelId=hotelId FROM #temp
where ID = @.LoopID
IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
premiumUserId = @.premiumUserId AND articleId = @.articleId)
BEGIN
INSERT tblAVPArticles
(premiumUserId, articleId, categoryId, hotelId, createdByAvp)
VALUES
(@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
END
SET @.LoopID = @.LoopID + 1
END
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
CarlosSanchezJr@.gmail.com wrote:
> Actually I find a method of how to do this, and I'm trying it below. I
> get an error though (Must declare the variable @.li_seq_no) about
> declaring a variable, although I am declaring it. Any thoughts?
> CREATE PROCEDURE dbo.InsertAvpArticles2
> @.premiumUserId int,
> @.whereClause varchar(5000)
> AS
> DECLARE @.articleId int
> DECLARE @.categoryId int
> DECLARE @.hotelId int
> DECLARE @.li_seq_no int
> /* hold the current pk we're working on */
> /* Get starting row primary key */
> EXEC('SELECT @.li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
> @.whereClause )
> /* While loop, processing ALL records, see the end of the loop on how
> to
> get the "next pk" selection */
> WHILE (@.li_seq_no != NULL)
> BEGIN
> /* Select what you want from the row at the current pk */
> SELECT @.articleId=articleId, @.categoryId=categoryId, @.hotelId=hotelId
> FROM tblArticles
> WHERE articleId = @.li_seq_no
> IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> premiumUserId = @.premiumUserId AND articleId = @.articleId)
> BEGIN
> INSERT tblAVPArticles
> (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> VALUES
> (@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
> END
> /* get the next pk like this */
> SELECT @.li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
> END /*this goes back to the beginning of the while loop */|||That's fairly interesting. But would that work with thousands of users
logged into the system and possibly hundreds of them hitting that page
(which would in turn I guess create a temp table for each one of them)?
Thanks!
SQL Menace wrote:
> Here is one way (untested of course ;-( )
> you can also use an (evil) cursor for this
> I am almost positive that you can do this in 1 set statement, but
> without data and DLL I won't embark on such a mission
> Less talk more code, here it is
>
> CREATE PROCEDURE dbo.InsertAvpArticles2
> @.premiumUserId int,
> @.whereClause varchar(5000)
> AS
>
> DECLARE @.articleId int
> DECLARE @.categoryId int
> DECLARE @.hotelId int
>
> SET NOCOUNT ON
> create table #temp (id int identity,
> articleId int,categoryId int,hotelId int)
> exec ('insert #temp(articleId ,categoryId ,hotelId)
> select articleId ,categoryId ,hotelId
> FROM tblArticles WHERE' + @.whereClause)
> declare @.LoopID int, @.MaxID int
> select @.LoopID=1, @.MaxID = MAX(ID) from #temp
> WHILE @.LoopID <= @.MaxID
> BEGIN
> SELECT @.articleId=articleId, @.categoryId=categoryId,
> @.hotelId=hotelId FROM #temp
> where ID = @.LoopID
>
> IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> premiumUserId = @.premiumUserId AND articleId = @.articleId)
>
> BEGIN
> INSERT tblAVPArticles
> (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> VALUES
> (@.premiumUserId, @.articleId, @.categoryId, @.hotelId, 1)
> END
> SET @.LoopID = @.LoopID + 1
> END
> GO
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> CarlosSanchezJr@.gmail.com wrote:|||Why wouldn' it?
A temp table is unique to the connection
Your alternative is a cursor or rewriting it as a SET statement
Denis the SQL Menace
http://sqlservercode.blogspot.com/
CarlosSanchezJr@.gmail.com wrote:
> That's fairly interesting. But would that work with thousands of users
> logged into the system and possibly hundreds of them hitting that page
> (which would in turn I guess create a temp table for each one of them)?
> Thanks!
> SQL Menace wrote:|||That seemed to work! Though if possible, I would still like to
understand completely why the second SP I wrote failed and how that
could be corrected for knowledge purposes. :-)|||Another issue...
Say my variable, @.whereClause, has a value of the following:
accomodationTypes IN ('Villas','Deluxe')
This is throwing an error if I wrap it in single qoutes like I need to.
How do you wrap something like that in single qoutes yet account for
the string values 'Villas' and 'Deluxe'?