Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "<p><b>Your search returned " & intRecordCount & " result(s)
for: </b></p>"
Response.Write "<blockquote>"
Response.Write "<p><b>Make</b></p>"
Response.Write "</blockquote>"
Response.Write "<p>"
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write "<p></p>"
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
Message posted via http://www.droptable.com
http://support.microsoft.com/kb/174225/en-us
http://groups.google.de/groups?hl=de...07%26rnum%3D11
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.droptable.co m...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "<p><b>Your search returned " & intRecordCount & "
> result(s)
> for: </b></p>"
> Response.Write "<blockquote>"
> Response.Write "<p><b>Make</b></p>"
> Response.Write "</blockquote>"
> Response.Write "<p>"
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write "<p></p>"
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.droptable.com

Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "<p><b>Your search returned " & intRecordCount & " result(s)
for: </b></p>"
Response.Write "<blockquote>"
Response.Write "<p><b>Make</b></p>"
Response.Write "</blockquote>"
Response.Write "<p>"
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write "<p></p>"
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
Message posted via http://www.droptable.comhttp://support.microsoft.com/kb/174225/en-us
rnum=11&prev=/ groups%3Fq%3DRowset%2Bcannot%2Bbe%2Brest
arted%2Bmovefirst%26start%3D
10%26hl%3Dde%26lr%3D%26selm%3D%2523NwSu4
7ECHA.1732%2540tkmsftngp07%26rnum%3D11[/ur
l]
HTH, Jens Suessmeyer.
[url]http://www.sqlserver2005.de" target="_blank">http://groups.google.de/groups?hl=d...qlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.SQ
droptable.com...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "<p><b>Your search returned " & intRecordCount & "
> result(s)
> for: </b></p>"
> Response.Write "<blockquote>"
> Response.Write "<p><b>Make</b></p>"
> Response.Write "</blockquote>"
> Response.Write "<p>"
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write "<p></p>"
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.droptable.com

Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "<p><b>Your search returned " & intRecordCount & " result(s)
for: </b></p>"
Response.Write "<blockquote>"
Response.Write "<p><b>Make</b></p>"
Response.Write "</blockquote>"
Response.Write "<p>"
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write "<p></p>"
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
--
Message posted via http://www.sqlmonster.comhttp://support.microsoft.com/kb/174225/en-us
http://groups.google.de/groups?hl=de&lr=&threadm=%23NwSu47ECHA.1732%40tkmsftngp07&rnum=11&prev=/groups%3Fq%3DRowset%2Bcannot%2Bbe%2Brestarted%2Bmovefirst%26start%3D10%26hl%3Dde%26lr%3D%26selm%3D%2523NwSu47ECHA.1732%2540tkmsftngp07%26rnum%3D11
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.SQLMonster.com...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "<p><b>Your search returned " & intRecordCount & "
> result(s)
> for: </b></p>"
> Response.Write "<blockquote>"
> Response.Write "<p><b>Make</b></p>"
> Response.Write "</blockquote>"
> Response.Write "<p>"
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write "<p></p>"
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.sqlmonster.com

HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>