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.

Cursed Error Messages

Hi everyone,
How do I get the error message?
I have a very long sproc that needs to be done in one transaction. I
have an error happening somewhere in the middle, but with a low enough
severity it doesn't terminate the procedure. To make sure I don't
miss any errors, I am storing @.@.error after every statement:
If @.Error<=@.@.error Set @.Error=@.@.error
that way at the end I can say if @.error<>0 rollback trans.
How do I get the error message? I have the number, and what I get
from sysmessages has the wildcards in it %d and so on.
Also , I can't use Xact_Abort, the web user permissions don't allow
it.

Or better yet is there a better way to do this? Sql has
@.@.total_errors - since the server was started, how about since the
transaction or the sproc was started.
Thanks a ton
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> How do I get the error message?
> I have a very long sproc that needs to be done in one transaction. I
> have an error happening somewhere in the middle, but with a low enough
> severity it doesn't terminate the procedure. To make sure I don't
> miss any errors, I am storing @.@.error after every statement:
> If @.Error<=@.@.error Set @.Error=@.@.error
> that way at the end I can say if @.error<>0 rollback trans.
> How do I get the error message? I have the number, and what I get
> from sysmessages has the wildcards in it %d and so on.
> Also , I can't use Xact_Abort, the web user permissions don't allow
> it.
> Or better yet is there a better way to do this? Sql has
> @.@.total_errors - since the server was started, how about since the
> transaction or the sproc was started.

There is no way to get the text of the error message in SQL. You must
catch it on client level.

If your code is as above, there is a serious problem in your error
handling. @.@.error is set after each statement, so you will always
set @.error to 0 above.

I have an article on my web site about error handling, that you may
find useful. http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

CTE behaviour in SQL 2005

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.

I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:

Code Snippet

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

DECLARE @.sql1 varchar(4000)

SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

) tbl1'

exec sp_executesql @.sql1

If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.

This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.

Code Snippet

CREATE PROCEDURE [dbo].[usp_getSalesOrders]

@.SalesPerson int,

@.startRowIndex int,

@.maximumRows int,

@.SortExpression varchar(20)

AS

BEGIN

SET NOCOUNT ON;

--if a sortExpression isn't passed in, give it a default

IF len(isnull(@.SortExpression, '') = 0

set @.SortExpression = 'SalesPersonID ASC'

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader with(nolock)

WHERE

(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)

GROUP BY SalesPersonID

) t

WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'

exec sp_executesql @.sql,

N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',

@.NumOfOrders,

@.startRowIndex,

@.maximumRows;

END

You can further optimize this, and I welcome comments as it'll help to improve my own code.

Hope this helps!

Nick

CTE behaviour in SQL 2005

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.

I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:

Code Snippet

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

DECLARE @.sql1 varchar(4000)

SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

) tbl1'

exec sp_executesql @.sql1

If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.

This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.

Code Snippet

CREATE PROCEDURE [dbo].[usp_getSalesOrders]

@.SalesPerson int,

@.startRowIndex int,

@.maximumRows int,

@.SortExpression varchar(20)

AS

BEGIN

SET NOCOUNT ON;

--if a sortExpression isn't passed in, give it a default

IF len(isnull(@.SortExpression, '') = 0

set @.SortExpression = 'SalesPersonID ASC'

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader with(nolock)

WHERE

(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)

GROUP BY SalesPersonID

) t

WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'

exec sp_executesql @.sql,

N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',

@.NumOfOrders,

@.startRowIndex,

@.maximumRows;

END

You can further optimize this, and I welcome comments as it'll help to improve my own code.

Hope this helps!

Nick