MSDE - Temporary Tables - Closed Recordset

Question

MSDE - Temporary Tables - Closed Recordset

geoff hopkins

Ten Centuries

Points: 1147
More actions
December 16, 2005 at 4:14 am

#111034

I understand this question fringes on VB but wanted to check the database side too.
I am currently developing using MSDE and VB but whenever I use (#)temporary tables in a SPROC to return a select query the vb only picks up a closed recordset (i.e no records returned). But when I run the sproc in the query analyzer I get the full results.
Does anybody know if this a known issue with MSDE? Or any things I can look into ....
below is a sproc example in case I am doing anything silly....
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.usp_Relatonships_No_Instance Script Date: 16/12/2005 11:06:00 ******/
ALTER PROCEDURE usp_Relatonships_No_Instance
AS
--
DECLARE @sql NVARCHAR(3000)
CREATE TABLE #TBL_TEMP
(
DOC VARCHAR(200),
SEC VARCHAR(200)
)
INSERT INTO #TBL_TEMP(DOC,SEC)
SELECT PARENT_DOC AS [DOC], PARENT_SEC AS [SEC] FROM TBL_RELATIONSHIPS
UNION
SELECT CHILD_DOC AS [DOC], CHILD_SEC AS [SEC] FROM TBL_RELATIONSHIPS
SELECT * FROM #TBL_TEMP
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

You may want to post a couple of your VB lines that call an SP including your concurrency settings, I mean, CursorType and LockType. Also, are you getting any error messages? Does your application code have access to the database and tempdb? It is sometimes a reason. An identity that your app is running under is different from your login that is used from Query Analyser and may or may not see the records!

Yelena

Regards,Yelena Varsha

Lowell SSC Guru Points: 323518 More actions · Answer 2

i would bet it's the SET NOCOUNT ON thing; the adodb recordset can get the 12 records(s) affected as the recordset to return, unless you explicily say set nocount:

ALTER PROCEDURE usp_Relatonships_No_Instance

AS

--

SET NOCOUNT ON

DECLARE @sql NVARCHAR(3000)

...rest of proc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 3

Lowell - you are a genius.... you can't believe that so many people had no idea... well done