Using the Table data type in a stored procedure.
-
I currently have a stored procedure that populates a table variable.At the end of the stored procedure I select * from the variable. When I go to access the Data in the stored procedure using ADO I get an error '3704 operation is not allowed when the object is closed.'
Any help will be greatly appreciated.
-
Try this,
Put SET NOCOUNT ON at the beggining of stored procedure
SET NOCOUNT OFF at the ending of stored procedure
-
I have already Set NoCount to ON but it still returns the same error. I also tried using a temp table but it also returns the same error.
Thanks,
Bayani
-
I get this sort of error occassionally. It usually happens when I add code to debug (e.g. return the results of some intermediate query) that is now returning no records.
Grab the SQL that is being used (either from your code or profiler) and run it in query analyzer. Set up query analyzer to show the results in grids. Hopefully, you will see one grid with no data and a second grid with the data you were after.
By the way, "Set Nocount Off" at the end of the stored procedure is pointless. The scope of "Set Nocount" is within the stored proc only. Hence, if you set it one way at the start of the batch and then call a stored procedure that set it to another value, when the stored procedure finishes, the value for Set Nocount is what it was initially set to at the start of the batch.
-
use this connection string then try agin fetching the records in record set
"driver={SQL Server};server=;database=;uid=;pwd="
Rohit -
I think there must be something wrong in either your code or the stored procedure. If you are using a stored procedure you must use BEGIN and END. The count returns without these but with errors.
The following sp and ado code worked for me on tables with high counts and no counts
SP---------------------------------------------------
/* procedure to return the count for a table name supplied via ADO*/
create procedure get_back_count_ado
(@tablename as varchar(100))
AS
BEGIN
set nocount on
declare @sqlstr varchar(100)
set @sqlstr = 'select count(*) count from ' + @tablename
exec (@sqlstr)
set nocount off
END
VB Code ----------------------------------------------
I create a form with:
command button = cmdCallSp
text box for table name = txtTableName
text box to display count result = txtCount
CODE:
Private Sub cmdCallSp_Click()
Dim oconn As ADODB.Connection
Dim SQLSel As String
Set oconn = New ADODB.Connection
oconn.Open "Driver={SQL Server};" & _
"Server=servername;" & _
"Database=databasename;" & _
"Uid=username;" & _
"Pwd=password;"
SQLSel = "get_back_count_ado " & txtTableName
Set rsCount = oconn.Execute(SQLSel)
txtCount.Value = rsCount("count")
Set rsCount = Nothing
Set oConn = Nothing
End Sub
Hope this pints you in the right direction
------------------------------
The Users are always right - when I'm not wrong! -
Jonathon,
Thanks for the help I moved the 'SET NOCOUNT ON' After the Begin instead of directly after the AS and it appears to work fine now.
Thanks,
Bayani
-
might be worth mentioning that we've seen the error message '3704 operation is not allowed when the object is closed.' before raised in ADO, when a different error was actually occurring - in our case a primary key violation, so I'm not sure if this is a "some error has occured" type of error
jon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply