VBA execute stored procedure gave “Operation is not allowed when the object is closed”

Recently we have a requirement which use macro to print some recordset value from a database table. Basically the VBA code which reference to DB part in Word Macro is as below


    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim totalcount As Integer

    strConn = "Provider=SQLOLEDB.1; Data Source= myserver\myinstance;Initial Catalog=mydb; Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    conn.Open strConn

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SpGetSomethingFromSomeDbTable"
    cmd.CommandType = adCmdStoredProc

    cmd.Parameters.Refresh
    cmd.Parameters(1).Value = CInt(divisionid)
    cmd.Parameters(2).Value = 1
    Set rs = cmd.Execute()

    totalcount = 0
    Do While Not rs.EOF
        ReDim Preserve membersFor(totalcount) As memberinfo
        membersFor(totalcount).field1 = RemoveQCInLastName(rs("fieldname1"))
        membersFor(totalcount).field2 = RemoveQCInLastName(rs("fieldname2"))
        membersFor(totalcount).field3 = RemoveQCInLastName(rs("fieldname3"))
        rs.MoveNext
        totalcount = totalcount + 1
    Loop

And part of the stored procedure is as below

if object_id('tempdb..#mytemptable') IS NOT NULL 
drop table #mytemptable

;with table1_CTE 
as(SELECT * FROM
	(SELECT *, ROW_NUMBER() OVER(PARTITION BY field1, field2 ORDER BY FieldDateTime DESC) AS rn
		FROM AnotherDatabase.dbo.table1 WHERE fieldDateTime < @specificdate) as T
 WHERE rn = 1  AND IsFlag = 1
 )
SELECT  m.field3, a.field4, a.field5
into #mytemptable
FROM table1_CTE  m JOIN someothertable a ON m.samefield = a.samefield
WHERE m.field1 = @currentvalue 

When I run macro, the error “Operation is not allowed when the object is closed” popup and stop at line
Set rs = cmd.Execute()

Actually this issue is not due to temp table or CTE table but the insert/delete/update DDL operation in stored procedure. The root reason is the count of the number of rows affected by those DDL statements will return as part of the result set. So the solution is easy and simple just need to add the following behind “AS” before the “BEGIN” in the stored procedure.

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

It took me 2 hours to figure out, hope it is helpful.

Recommend an article for T-SQL ranking usage

Just saw an article explain the t-sql ranking usage very clearly, it is written by famous Pinal Dave, the author of http://blog.sqlauthority.com

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

Another article also explains one of the new feature of SQL 2008 R2–>group sets

http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/

 

How to provide parameters to backend stored procedure by SSRS multi-value parameters

I have a SSRS report with several multi value parameters which are used in a DataSet, before the parameter associate with a long query, it seems the query can automatically deal with the multiple value parameters and in the query, you just need write like that “select * from xxtable where xxfield IN (@multivalue_par)”. However we have some security concern which have to wrap up the query into a a stored procedure, then the issue comes, obviously the stored procedure cannot understand the multi-value correctly.

First thing, let’s see what exactly the multi-value parameter passed to the back SP. I use a temp table and find the value string is like that value1, value2, value3, value4. Also if you check “Select All” in the parameter dropdown list in SSRS report, it will pass all the dropdown list values to the SP, there is no value for the item “selected all” (that’s good to handle). Note, some article said, in the parameter expression, need to add “=Join(Parameters!xxfield.Value, “,”)”, and then can build a parameter string and passed back, actually this is for SSRS 2005, in SSRS 2008 or upper version, the multi value parameter passed the comma deliminator value back, so if you want to use comma as deliminator, no need to use that function except you want to use some other deliminators.

If the SP want to use the value string passed back, there are two options, first opiton is to use excutesql and generate an dynamic SQL string to execute, you can split the value string and add single quote to every value then concatenate them together. However, this required more permission to run this dynamic query.

Option2, parse the inbound multi-value parameter by a function.

step 1: create a function to split the values into a temp table.


USE [xxxdatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnMultiParameter]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnMultiParameter]
(
   @DelimittedString varchar(500),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)

      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END

Step 2, in the stored procedure you can use as below.


create PROCEDURE [dbo].[SP_XXXXX]
	@multivalue_para varchar(500)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT  *
	FROM    tableXXXX
	WHERE   mulitivalue_field IN
        (select value from [dbo].[fnMultiParameter](@multivalue_para, ',')
END

please ensure you use “[]” on the function name, not sure why it doesn’t work if you don’t use it….

You can reference an article but that one is based on SSRS 2005, and that guy didn’t give the function either….
http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

T-SQL Script can used to show SQL Job history

The following script can show the job history and running duration.


select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

Import or Insert values with indentiy column

Recently I need to dump some data from test DB instance to production DB instance, but one table has an identity column and I didn’t want it to be generated by DB as this column are foreign key of other tables, so I need to dump exactly the same value to the identity column. Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT *
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And got this error message:

An explicit value for the identity column in table ‘DB1.dbo.TABLE’ can only be specified when a column list is used and IDENTITY_INSERT is ON

The correct way is as following:

1) set identity insert to ON:

SET IDENTITY_INSERT db1.dbo.table1 ON

2) specify column list, for example:

INSERT INTO
DB1.dbo.TABLE (column1, column2, column3)
SELECT column1, column2, column3
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column