How to retrive Sqlserver temporary table data to VB recordse

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to retrive Sqlserver temporary table data to VB recordse

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-13 : 08:00:23

Senthilkumar writes "hi
I am working with Stored proc with local temporary table but it will return the data when i run the stored proc using Query Analyser but when i called the proc using command object using visual basic it will not return any values to client.Is there any specific way to solve this problem..

THIS IS MY PROCEDRUE

ALTER PROC TEST
@lngHK_Id int,
@strAttributeName varchar(128),
@strArea varchar(128),
@strTypeName varchar(128)
WITH RECOMPILE
AS
BEGIN
DECLARE @strSKTable varchar(128),
@STRSQL varchar(1000)
BEGIN

IF OBJECT_ID('#TEMPLONG') IS NOT NULL DROP TABLE TEMPLONG
CREATE TABLE #TEMPLONG(LONG_ID INTEGER,LONGSTRING TEXT)
SET @strSKTable='SK_' + @strArea + '_' + @strTypeName+'_Val'
SET @STRSQL='select a.lngHK_id,b.['+ @strAttributeName +'] from ['+@strSKTable+'] a ,(select long_string as ['+@strAttributeName+'] from longstringlist where'
SET @STRSQL=@STRSQL + ' longRef=(select ['+@strAttributeName+'] from ['+@strSKTable+'] where lngHk_id=' + CAST(@lngHK_Id AS VARCHAR)+ '))b where a.lngHk_id=' + CAST(@lngHK_Id AS VARCHAR)
EXEC (@STRSQL)
SET @STRSQL='INSERT INTO #TEMPLONG ' + @STRSQL
EXEC (@STRSQL)
--SELECT * FROM #TEMPLONG
END
END

AND MY CALLING VB PROGRAM LIEK TAHT..

With objcmd
.ActiveConnection = objcnn
.Prepared = True
.CommandText = "TEST"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Return", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("lngHk_id", adInteger, adParamInput, , 968)
.Parameters.Append .CreateParameter("StrAttributename", adVarChar, adParamInput, 128, "HELP1")
.Parameters.Append .CreateParameter("strarea", adVarChar, adParamInput, 128, "FU")
.Parameters.Append .CreateParameter("strtypename", adVarChar, adParamInput, 128, "COMMONICSDEFDATA")
'.Parameters.Append .CreateParameter("strRet", adVarChar, adParamOutput, 8000)
Set objRs = .Execute()
End With

GIVE ME THE SOLUNTION FOR THIS SPECIFIC PROBLEM"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-13 : 08:31:01

Create the temp table with the ##TEMPLONG since ths table scope is there till the active connetion to the db is not set to nothing where As.. #TEMPLONG table scope is only within the procedure..

hope this helps you..

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources