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 says ODBC is Available. Where?

In BOL it clearly says that you can "use the Microsoft OLE DB Provider for ODBC Drivers provided." in relation to Analysis Services.

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/uas9/html/9fab8298-10dc-45a9-9a91-0c8e6d947468.htm

Unfortunately, this provider is not in my list. Here is the full text section from help.

"Other relational systems support the older Open Database Connectivity (ODBC) specification but do not yet support the OLE DB specification. For these systems, use the Microsoft OLE DB Provider for ODBC Drivers provider. Although ODBC providers can be used by referencing them with the Microsoft OLE DB Provider for ODBC Drivers provider, you should use a native OLE DB provider whenever possible for performance reasons."

HELP PLEASE!

Hi Hugh

From what I've seen there is no ODBC functionality in SQL 2005 analysis services. There is in the integration services not that this helps much as you have to dump all of the data from your source database into SQL. I have successfully created a linked server and then used native SQL source to attach to the data. I have run into problems with data limits and the system running out of memory and there is very little documentation, so am stil playing around with this. It seems that Microsoft has missed the mark here as SQL 2000 does allow this functionality so strange to take away functionality with new release.

|||

Thanks for the input kiwi. I have also used the .Net ODBC driver in Integration Services, but find it oddly missing from Analysis Services. Even stranger is that the documentation says it has an OLE ODBC driver which I can't find anywhere in any part of the Business Intelligence tools. I'll try using the linked server route to see if that works for me. Maybe they will resove this in a future patch.

Thanks again.

|||Kiwi, how did you connect to your linked server through the native SQL source? I can't figure out how to do that in the Data Source setup.|||

Never mind. I found the other post that talks about it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78023&SiteId=1

I knew I could use saved views pointing to a linked server, but I was hoping to be able to directly address the linked server in the data source. If they really aren't planning to support ODBC out of the box in Analysis Services, that makes it a severly limited product. For all of us who have legacy systems that are going to be around for many years to come, we're stuck doing five times the work.

(I'm done whining now :). It's still a great product.)

|||

Hey Hugh

I didn't do all of that I simply used the managment studio to create the linked server and then in Business Intelligence development I used Named queries instead of tables and used Openquery. Worked fine apart from I ran into memory issues with returns over 100k records.

Kiwi

|||Thanks Kiwi. Don't know why I didn't think of that. Definitely much easier that way.

Help required: Using OLE Automation SPs

Hi,
I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
execute a piece of dynamic SQL.
My dynamic SQL needs to return a resultset so I basically did the following:
SET @.vSQL = 'SELECT * FROM MyTable'
EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
I now have the contents of MyTable in @.vResult (which is a handle). I used
the following to return the results as a string into @.pResults (which is a
varchar):
EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
This is all well and good but I don't want the results returned as a string,
I want it returned as a recordset which will ultimately be the output from
the SP that wraps all of this up. Furthermore the max string that I can
return is 8000 chars (i.e. max length of a varchar) so with any decent sized
dataset this doesn't return everything anyway.
So my question is: Once I have the results as handled by @.vResult...how do I
navigate over it and return the results as a normal rowset? The
GetRangeString function is obviously not the answer.
Thanks in advance
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspxWhat is @.vObject a handle to? Normally you don't need to use OLE Automation
SPs to execute dynamic SQL. I think you will have to explain a little bit
better why you want to shove a table into an output variable and then
process it back into a resultset again. Why not just EXEC('SELECT * FROM
MyTable') ... (and I assume that dynamic SQL is actually required here for
some reason, and that your query is a little more complicated than this...
otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> Hi,
> I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
> execute a piece of dynamic SQL.
> My dynamic SQL needs to return a resultset so I basically did the
following:
> SET @.vSQL = 'SELECT * FROM MyTable'
> EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
> I now have the contents of MyTable in @.vResult (which is a handle). I used
> the following to return the results as a string into @.pResults (which is a
> varchar):
> EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
> This is all well and good but I don't want the results returned as a
string,
> I want it returned as a recordset which will ultimately be the output from
> the SP that wraps all of this up. Furthermore the max string that I can
> return is 8000 chars (i.e. max length of a varchar) so with any decent
sized
> dataset this doesn't return everything anyway.
>
> So my question is: Once I have the results as handled by @.vResult...how do
I
> navigate over it and return the results as a normal rowset? The
> GetRangeString function is obviously not the answer.
> Thanks in advance
>
> Regards
> Jamie Thomson
> An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
>
>|||Hi Aaron,
Yes, you're absolutely right. My real query is alot more complicated than
this. :)
The reason I am doing it is a permissions issue. Under our current
application security model the calling app (an ASP.Net app) doesn't have
access to MyTable therefore EXEC(...) doesn't work. I am not allowed to
change the security model either (its not under my control) :o( but I AM
allowed to access it using this method.
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:udXW3iSGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> What is @.vObject a handle to? Normally you don't need to use OLE
> Automation
> SPs to execute dynamic SQL. I think you will have to explain a little bit
> better why you want to shove a table into an output variable and then
> process it back into a resultset again. Why not just EXEC('SELECT * FROM
> MyTable') ... (and I assume that dynamic SQL is actually required here for
> some reason, and that your query is a little more complicated than this...
> otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
> message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> following:
> string,
> sized
> I
>