Calling a SQL Server Stored Proc that contains a CREATE TABLE

[pmurch]

I have a VBA macro that kicks off a SQL Server stored procedure. During development all was well, until we added code to the stored proc to take a before/after snapshot of changes made. Without modifying the VBA macro, if we remove the CREATE TABLE code, the macro works fine. If we include it, it crashes with an 8004E021 error. We've tried creating the table both in memory and on disk; behavior is the same.

Is there a setting on SQL Server, or in Excel/VBA that we're missing?

[Rabbit]

There's not nearly enough information to diagnose the problem.

What's the macro code?
What's the stored procedure code?
What's the full error text?
Is it an Excel error or SQL Server error?
Does the stored procedure with the create table statment run outside of Excel, say in SSMS?

[pmurch]

@Rabbit
Sorry -- I debated how much detail to get into, and apparently I erred too much on the lean side.

What's the macro code?
---------------------

cnnConnect.Open "Provider=SQLOLEDB.1;Initial Catalog=" & dbnShort _
            & ";Data Source=myservername;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" _
            & "Use Encryption for Data=False;Tag with column collation when possible=False;" _
            & "User ID=myuserid;Password=mypassword;"
 
        Set rstRecordset = New ADODB.Recordset
 
        rstRecordset.Open _
            Source:="exec DB_GLOBAL.dbo.[USR_Cred2_DisplayUncompletedCreds-Excel] '" _
                        & dbnShort & "', '" & DBList(idxDB, 2) & "'", _
            ActiveConnection:=cnnConnect, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockReadOnly, _
            Options:=adCmdText
 
        'Add the new QueryTable to the new worksheet and populate it from rstRecordset
        With ActiveSheet.QueryTables.Add( _
            Connection:=rstRecordset, _
            Destination:=Range(wsName & "!A6"))
 
            .Name = "BLSQL1_" & wsName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertEntireRows
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh (False)
        End With

What's the stored procedure code?
--------------------------------
The Stored Procedure code is lengthy, but the CREATE TABLE portion is as follows. This is the section that, when commented out, works fine, but fails when left in. This SQL code runs correctly when run on the server; it is only when called from Excel that it fails:
-----------------

SET @cmdlogs = 'USE ' + @db + ' '
    + 'IF OBJECT_ID(''USR_LN_UPDATE_BEFORE'') is null '
        + 'BEGIN '
        + 'CREATE TABLE [dbo].[USR_LN_UPDATE_BEFORE]('
        + '[rundate] [datetime] NOT NULL,'
        + '[db] [varchar](40) NOT NULL,'
        + '[mcid] [varchar](12) NOT NULL,'
        + '[CusAddrID] [numeric](12, 0) NOT NULL,'
        + '[Cred] [varchar](100) NULL,'
        + '[Creds] [varchar](40) NULL,'
        + '[Creds Desc] [varchar](40) NULL,'
        + '[Cust LN] [varchar](80) NULL,'
        + '[Cad LN] [varchar](80) NULL,'
        + '[Fmt Dtl] [varchar](400) NULL,'
        + '[Srch Name] [varchar](80) NULL,'
        + '[Script Name] [varchar](255) NULL) '
        + 'GRANT ALTER ON [dbo].USR_LN_UPDATE_BEFORE TO xlsuser '
        + 'GRANT DELETE ON [dbo].USR_LN_UPDATE_BEFORE TO xlsuser '
        + 'GRANT INSERT ON [dbo].USR_LN_UPDATE_BEFORE TO xlsuser '
        + 'GRANT SELECT ON [dbo].USR_LN_UPDATE_BEFORE TO xlsuser '
        + 'GRANT UPDATE ON [dbo].USR_LN_UPDATE_BEFORE TO xlsuser '
        + 'END '
    + 'IF OBJECT_ID(''USR_LN_UPDATE_AFTER'') is null '
        + 'BEGIN '
        + 'CREATE TABLE [dbo].[USR_LN_UPDATE_AFTER]('
        + '[rundate] [datetime] NOT NULL,'
        + '[db] [varchar](40) NOT NULL,'
        + '[mcid] [varchar](12) NOT NULL,'
        + '[CusAddrID] [numeric](12, 0) NOT NULL,'
        + '[Cred] [varchar](100) NULL,'
        + '[Creds] [varchar](40) NULL,'
        + '[Creds Desc] [varchar](40) NULL,'
        + '[Cust LN] [varchar](80) NULL,'
        + '[Cad LN] [varchar](80) NULL,'
        + '[Fmt Dtl] [varchar](400) NULL,'
        + '[Srch Name] [varchar](80) NULL,'
        + '[Script Name] [varchar](255) NULL) '
        + 'GRANT ALTER ON [dbo].USR_LN_UPDATE_AFTER TO xlsuser '
        + 'GRANT DELETE ON [dbo].USR_LN_UPDATE_AFTER TO xlsuser '
        + 'GRANT INSERT ON [dbo].USR_LN_UPDATE_AFTER TO xlsuser '
        + 'GRANT SELECT ON [dbo].USR_LN_UPDATE_AFTER TO xlsuser '
        + 'GRANT UPDATE ON [dbo].USR_LN_UPDATE_AFTER TO xlsuser '
        + 'END '

What's the full error text?
--------------------------
It is the very informative "System Error 8004E021" that appears in a popup messagebox whenever there is some difficulty between Excel and SQL Server. Separately, I've found a table that says this error refers to a field being "too small to accept the amount of data you attempted to add." However, I've verified all field lengths in the CREATE code, and I'm not adding data from Excel -- only kicking off a stored proc. Since I can run the SQL successfully on the server, I can't see where such an error comes from.

Is it an Excel error or SQL Server error?
----------------------------------------
Hard to tell. I believe Excel is responding with the "System Error" messagebox based on something happening on the SQL side (since it only happens when the CREATE code is present in the stored proc).

Does the stored procedure with the create table statment run outside of Excel, say in SSMS?
-----------------------------------
Yes, it runs on a server dedicated to running SQL Server. I wouldn't say it runs "in SSMS", since Excel doesn't open an SSMS session -- it simply makes a connection (see the connection object in the VBA code). However, your question may be answered by the fact the the SQL code runs successfully when executed directly from an SSMS session.

Thanks for bearing with me this far...
Any ideas?

[Rabbit]

Please use code tags when posting code or formatted data.

Is the user credentials in the macro connection the same one you use to log into the SQL Server outside of Excel? My guess is that there are missing permissions for the user.

[pmurch]

Thanks, Rabbit. That appears to be the issue. Had looked at the permissions at the beginning of troubleshooting, but apparently missed an important setting. Your pointing me back to permissions was an excellent idea -- thanks!

[Rabbit]

No problem, glad you got it working!