October 7, 2011 at 5:36 am
I am working with an application that imports data from tables or stored procedures that return record sets. The problem is that this application is requiring bookmarkable record sets. What makes a stored procedure that is returning a record set not bookmarkable?
I have search the Internet quite a bit but haven't come up with much. I've also tried books online but didn't find much there either. I'm hoping someone in this community will be able to help.
So far here is what I have found though I would like a better list of things that make the results of a stored procedure to be not bookmarkable.
The following stored procedure returns a bookmarkable record set:
CREATE PROCEDURE [dbo].[usp_BookmarkableTest1]
AS
SET NOCOUNT ON
SELECT * FROM [SomeTable]
This procedure's results are not bookmarkable:
CREATE PROCEDURE [dbo].[usp_BookmarkableTest2]
@Param1 int = 0
AS
SET NOCOUNT ON
IF @Param1 = 0 BEGIN
SELECT * FROM [SomeTable]
END
ELSE BEGIN
SELECT * FROM [SomeTable] WHERE Field1 >= @Param1
END
Next step I thought that maybe if I put results into a temporary table first that it would become bookmarkable. Not true, the following procedure is also not bookmarkable.
CREATE PROCEDURE [dbo].[usp_BookmarkableTest3]
@Param1 varchar(50) = ''
AS
SET NOCOUNT ON
CREATE TABLE #t(
[FIRSTNAME] [char](10) NULL,
[LASTNAME] [char](18) NULL,
[RowId] [char](15) NULL
)
IF @Param1 = '' BEGIN
INSERT INTO #t
SELECT * FROM [SomeTable]
END
ELSE BEGIN
INSERT INTO #t
SELECT * FROM [SomeTable] WHERE [LASTNAME] >= @Param1
END
SELECT * FROM #t
DROP TABLE #t
So this gives me a couple examples of how a stored procedure become not bookmarkable. Does anyone have any further examples of what make the resulting record set of a stored procedure not bookmarkable?
Pat
Pat Buskey
October 7, 2011 at 5:56 am
What if you try to make 2 new procs (one for each part of the if). And see if that fixes it.
I don't know your front end and I never had this problem so I'm shooting in the dark here.
October 7, 2011 at 7:55 am
You could do something like this:
CREATE TABLE #someTable (...columnlist...)
INSERT #someTable (...columnlist...)
EXEC #someProcedure(...paramlist...)
SELECT *
FROM #someTable
It's not exactly what you asked, but could be a workaround.
-- Gianluca Sartori
October 7, 2011 at 9:33 am
I'm not sure that this is a T-SQL issue. You may have better luck posting in a forum for the application/language that you are working with. I did find a post on another forum that might be relevant. http://www.developerfusion.com/thread/14647/run-time-error-7004-row-set-is-not-bookmarkable/. The response there indicates that you may need to set the CursorLocation on your recordset object.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2011 at 9:41 am
drew.allen (10/7/2011)
I'm not sure that this is a T-SQL issue.Drew
Changing the cursor location to the client may not work for us. I understand that there are things that can be done at the client. But based on my testing of different stored procedures it appears that how a stored procedure is created can affect whether or not the cursor can be on the server or not.
I'm not really looking for workarounds for my sample code or about how to change a client that we don't have control over, but looking for what types of things in a stored procedure would cause a server-side cursor to no longer be bookmarkable. Hence the posting in a T-SQL forum.
Pat
Pat Buskey
October 7, 2011 at 9:45 am
Never faced that issue or read about it so I can't help further at this point.
October 7, 2011 at 11:06 am
This is just a guess, but it probably needs consistent resultsets. That would mean no IF statements, no dynamic SQL, etc. In this particular example, you can replace your IF statement with a CASE statement. That may not be possible with all of your stored procedures. That means that you may need to sacrifice performance in order to make it bookmarkable.
I'm also not sure how temp tables will affect this. You may need to replace temp tables with CTEs even if it sacrifices performance.
I also don't know how stored procedures that return multiple result sets will affect this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply