Run-time error '1004' when running SQL stored procedure (VB in Excel) 2

[StevenK]

I'm making use of some stored procedures to load cells within an Excel (2007) spreadsheet via macros (with VB code).
The majority of these are working fine and outputting the cell data as required (and then letting me produce charts from these).

However I've just introduced a new stored procedure and when I attempt to run this in the same way (whereby all the other stored procedures do far have worked without any problems) I get a 'Microsoft Visual Basic' error:

"Run-time error '1004':
The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator.
Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."

And I'm unable to generate the results of the stored procedure.

It should be noted that the same stored procedure (with the same parameters) runs without any trouble via a Query window - and produces the required result set.

As such - I'm unable to run this same procedure through to Excel - and need to!

I think the issue might be due to temporary tables within the stored procedure - with the temp tables being created and made use of in the scope of the stored procedure and then dropped at the end (of the s.p.). I've gone as far as removing the 'drop table...' statements in case they were causing the issue - but still get the same error and no result set.

If I reduce the stored procedure down to a simple result set - i.e. "select 'test return field' " - then this runs through to Excel correctly (using the same Vb code through Excel with the same parameters).
However I clearly need to get the result set as provided by the stored procedure originally written.

Has anyone come across anything similar?
And / or suggest how I can get around this problem?

Any suggestions would be greatly appreciated.
Thanks in advance.

Steve

 

[SkipVought]

Hi,

Hard to say without seeing the code AND the statement that is causing the error.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[StevenK]

I've created a new stored procedure and Excel spreadsheet to establish my thoughts - that it is the use of the temp table(s) in the stored procedure that is giving me the run-time error '1004'.

I've created a new stored procedure in my database as follows:

create proc proc_get_databases (@param1 int, @param2 int, @param3 int, @param4 int, @param5 int)
as
select name 
from master.dbo.sysdatabases
order by name

[Please note the use of the parameters is overkill - and only done for testing]

Running the syntax "exec proc_get_databases 1, 2, 3, 4, 5" in a query window generates a list of databases found - as required.

I then set up a macros in an Excel spreadsheet - similar to the following:

Sub Macro1()

    Dim strCmd As String
    strCmd = "exec proc_get_databases 1, 2, 3, 4, 5"

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        {your connection string here}), Destination:=Range( _
        "A1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strCmd
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        {your source connection file location here}
        .ListObject.DisplayName = "GridData"
        .Refresh BackgroundQuery:=False
    End With

End Sub

At this point - when I run the macros it does so fine. And I get a list of database names.


However, when I modify the stored procedure to make use of a temporary table, say as follows:

create proc proc_get_databases (@param1 int, @param2 int, @param3 int, @param4 int, @param5 int)
as

select name 
into #temptable1
from master.dbo.sysdatabases
order by name

select * from #temptable1
order by name 

drop table #temptable1

Again, when I run this through a query window I get the list of databases as required so the stored procedure still returns a similar result set - albeit done in a different way.

Now when I run the macros in my Excel spreadsheet (having cleared all the cells beforehand) I get the error:

"Run-time error '1004':
The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator.
Make sure the exernal database is available and hasn't been moved or reorganized, then try the operation again."

If I hit the 'Debug' button it highlights the '.Refresh BackgroundQuery:=False' line of the code.
And no data is returned to the Excel spreadsheet.

Clearly this is a simple example - and in this case the use of the temporary table is not essential - but with the workload I'm performing I do need to.
And it seems to be this element of it that is causing me the problems with running the stored procedure to Excel.

Given this information (and example) can anything more be suggested?

Thanks in advance.

 

[SkipVought]

In your connection string or driver configuration, (don't know which) do you have permission to modify the DB?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[StevenK]

Which parameter(s) establish this?

I'm unclear and have let Excel build up the connection string(s) via means of recording a macros for instance.

 

[PHV]

when I run this through a query window
A MS-Query window ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[StevenK]

Via a Query Analyser window within SQL Server Management Studio.

 

[SkipVought]

for instance in my Oracle ODBC Driver configuration and in my DB2 ODBC Driver configuration, I can specify Read Only or not.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[StevenK]

Thanks for the assist.
Changing the way in which the connection string was built has allowed me to run the example as provided - and also the actual code needed to be run.

Many thanks for the pointers with this.

Steve

 

[robodad669]

StevenK I have the exact same problem with temp table access via excel.
Can you explicitly write the connection string detail that made the difference.
Many thanks for the pointers with this.

Robodad669

 

[StevenK]

I'm now working through an ODBC connection (preset to the required db on the required server) - let's call it 'ODBC_CONNECT1'.

Then in the macros I've got (in part):

' Load data
Dim strCmdText As String
strCmdTextSubPara = "exec mystoredproc"
    
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=ODBC_CONNECT1;Description=ODBC_CONNECT1;UID=sa;PWD={sa-password};APP=2007 Microsoft Office system;WSID={Workstation-ID};DATABASE={Database-Name};Network=DBMSSOCN" , Destination:=Range("$A$1")).QueryTable

... etc

Have you tried via an ODBC connection?

Steve

 

[SkipVought]

Have you tried via an ODBC connection?

Yes, every day, although, not using a stored procedure, but using SQL code directly.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[StevenK]

I was asking whether 'robodad669' whether they were making use of an ODBC connection?

I think the issue I was having was due to the security (i.e. not having the permission to create temporary tables within a database) via means of a hard-coded connection string.

Working through an ODBC connection helped solve this for me.

 

[robodad669]

This is the connection string that does not work for temp tables.

Const stCon As String = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=NOISE;Data Source=KSLPQQ03"

Also heard that
"I need to be connecting with an account that has db_ddladmin access to the tempdb system database"
as possible fix.

I will look at ODBC. I think I did a temp table on Oracle and used ODBC last year. Did not this current string would be weak.

 

[StevenK]

That could well depend on whether the user in question has the security to create objects within the database.
Try ODBC and see how you go...

 

[robodad669]

Steve thanks for sending towards connection strings.
This one worked for me in Excel accessing stored procedures with temp tables.
Const stCon As String = "OLEDB;Provider=MSDASQL.1;DRIVER=SQL Server;SERVER=KS01;Database=NOISE;Initial Catalog=NOISE;Trusted_Connection=Yes"