Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
' "SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
' "Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
' "SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
' "RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
' "WHERE asset_cd='" & AssetID & "'" & _
' "AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 & "<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 & "<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!

Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
'"SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
'"Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
'"SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
'"RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
'"WHERE asset_cd='" & AssetID & "'" & _
'"AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 &"<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 &"<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)
I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!
sql

error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>