cursor type

Hi there,
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Cursor from stored procedure?

I have a situation where I need to create a cursor based on a stored
procedure, so basically something like this...
DECLARE @.proc varchar(250)
Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
Cast(@.LastUpdateDate As Varchar(7))
DECLARE the_cursor CURSOR FOR @.proc
SQL Server expects a 'Select' statement instead of an SP name and therefore
is giving me an error. I am building the stored procedure name to include
the server name because I am linking with the @.DBServerName server before
this statement (with sp_addlinkedserver).
I tried just issuing a built 'Select' statement against the linked server,
but again, the 'Select' statement had to be built as a string to include the
linked server name. Is there a Transact-SQL version of eval() or something
like that to tell it to treat the string version of the 'Select' statement a
s
if it were a 'Select' statement that was just typed in (not built as a
string)? Or, is there a way to build a cursor by defining it to be a stored
procedure instead of a 'Select' statement? Seems as though I'm in a catch-2
2
unless I'm missing something.
Thanks,
ToddI dont think you can define based directly on a SP.
Instead, store the results in a Temp table and base your cursor on the temp
table.
Gopi
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>I have a situation where I need to create a cursor based on a stored
> procedure, so basically something like this...
> DECLARE @.proc varchar(250)
> Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
> Cast(@.LastUpdateDate As Varchar(7))
> DECLARE the_cursor CURSOR FOR @.proc
> SQL Server expects a 'Select' statement instead of an SP name and
> therefore
> is giving me an error. I am building the stored procedure name to include
> the server name because I am linking with the @.DBServerName server before
> this statement (with sp_addlinkedserver).
> I tried just issuing a built 'Select' statement against the linked server,
> but again, the 'Select' statement had to be built as a string to include
> the
> linked server name. Is there a Transact-SQL version of eval() or
> something
> like that to tell it to treat the string version of the 'Select' statement
> as
> if it were a 'Select' statement that was just typed in (not built as a
> string)? Or, is there a way to build a cursor by defining it to be a
> stored
> procedure instead of a 'Select' statement? Seems as though I'm in a
> catch-22
> unless I'm missing something.
> Thanks,
> Todd|||The problem is that I have to build a string that represents the remote
stored procedure or string that represents a 'Select' statement. I must
build the string representations in order to dynamically include the remotel
y
linked server name into the equation. If I build a string representation of
an SP SQL Server will run it, but I have no way of seeing the records it
returns that I know of (can't build a cursor from an SP). If I build a
string representation of a 'Select' statement that includes the remote serve
r
name, how the heck do you tell SQL Server to run the 'Select'? It's just a
string value to SQL Server.
I guess my other options are to have a separate SP for each server I'm going
to link to or to put the code on each SQL Server machine and link back up
with the main server. That way the linked server name will be constant. I
didn't want to do either of these unless, of course, the other way just won'
t
work.
"rgn" wrote:

> I dont think you can define based directly on a SP.
> Instead, store the results in a Temp table and base your cursor on the tem
p
> table.
> Gopi
> "Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
> news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>
>sql

current keyID from sql update

When I return from executing a sql storedprocedure where I insert a record, where can I find the keyID?Inside of the stored procedure you should set up an OUTPUT parameter called e.g. @.keyID to communicate the newly added ID back to your page. After the INSERT you would do this to capture the newly added ID:

SELECT @.keyID = Scope_Identity()

Terri|||Got it! Thanks