Call Parameterized SQL SP in Excel 2007

Within Excel I have created a SQL connection to pull data by calling a stored procedure. I have the Command Type of the connection as SQL and the Command Text as follows: spReportWhoWhatWhere_TST '','',''
I have granted execute permissions on the SP and I'm able to query other tables and get a result set within Excel without a problem. The query takes close to a minute to run within Management Studio, however when run in Excel I get an error: "The query did not run, or the database table could not be opened" after almost exactly 1 min  and I'm wondering if it has to do with Excel timing out. Or I'm just using bad syntax within the Command Text.  
There was a suggestion to increase the Timout within Excel by using a macro but I am not sure how to implement this within Excel. Any suggestions?

Use the CommandTimeout property in Excel:

Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command

objCommand.CommandTimeout = 99 '
objCommand.ActiveConnection = cnConn
objCommand.CommandText = "DELETE Users WHERE IdLevel < 98"
objCommand.Execute

GrapheneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrapheneAuthor Commented:
As I can successfully call other SP's I am leaning toward it being a timeout issue. If anyone can assist me in changing the timeout property in Excel that would be a helpful start.
Thank you
0
TempDBACommented:
You can go through the following link by msft that will guide you how to get data in excel from sql server:-
http://support.microsoft.com/kb/306125
0
GrapheneAuthor Commented:
I was following this earlier but got stuck on the VBA Editor as I only have Visual Studio. Can this be done through Visual Studio or do you recommend a free VB Editor?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

GrapheneAuthor Commented:
When adding this to a module within Excel I receive a Connection Timeout Error. I have tried a trillion ways that I can think of to get this working so that I can get my result set to come through . Below is my last attempt. Yes I realize the command timeout is redundant here, but I was attempting several ways to implement it. I do not receive any errors in the VB code other than the Runtime error '2147217871 (80040e31)' Timeout Expired error. Any suggestions of the below code I could attempt?

Sub DataExtract()

Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command

objCommand.CommandTimeout = 99 '







' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=cpssrv235m\test_sql_2005;INITIAL CATALOG=trainingv3_TST;"

strConn = strConn & "ConnectionTimout=120;"

strConn = strConn & "CommandTimout=120;"
strConn = strConn & "ExecutionTimout=120;"


'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;ConnectionTimeout=120;CommandTimeout=120;ExecutionTimeout = 120"

'Now open the connection.
cnPubs.Open strConn


                   
     ' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnPubs
cmd.CommandTimeout = 0
cmd.CommandText = strConn

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "exec spReportWhoWhatWhere_TST '','','' "
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsPubs
   
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
                 
End Sub




0
leonstrykerCommented:
Lets try something simple:
Sub DataExtract()
Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String

    ' Create a connection object.
    Set cnPubs = New ADODB.Connection
    cnPubs.ConnectionTimeout = 0
    cnPubs.CommandTimeout = 0
   
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE=cpssrv235m\test_sql_2005;INITIAL CATALOG=trainingv3_TST;"

    'Now open the connection.
    cnPubs.Open strConn
                   
    ' Create a recordset object.
    Set rsPubs = New ADODB.Recordset
    strSQL = "exec spReportWhoWhatWhere_TST '','','' "
    rsPubs.Open strSQL, cnPubs, adOpenStatic, adLockBatchOptimistic

    Sheet1.Range("A1").CopyFromRecordset rsPubs
    rsPubs.Close
   
    cnPubs.Close
    Set cnPubs = Nothing
End Sub

Leon
0
GrapheneAuthor Commented:
Thank you Leon, However I get an Invalid authorization specification error when I run the macro.
0
GrapheneAuthor Commented:
Debug hightlights the cnPubs.Open strConn as the culprit.
0
GrapheneAuthor Commented:
I added: strConn = strConn & " INTEGRATED SECURITY=sspi;" and it seemed to get a little further, but then I received an error: Operation is not allowed when the object is closed. With the code: Sheet1.Range("A1").CopyFromRecordset rsPubs highlighted
0
leonstrykerCommented:
What does your store procedure return when you execute it directly on the data base?
0
GrapheneAuthor Commented:
On average around 58 seconds to return a little over 5000 records.
0
leonstrykerCommented:
Ok, add this

cnPubs.CursorLocation = adUseServer

and change adLockBatchOptimistic to adLockOptimistic

Before you place data on the sheet try to get a recordcount

Debug.Print rsPubs.RecordCount

Leon
0
GrapheneAuthor Commented:
Did I place these in the right locations? I'll have to pick back up tomorrow as my time is out today. Thank you for the assistance!

Traci

Sub DataExtract()
Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String

    ' Create a connection object.
    Set cnPubs = New ADODB.Connection
    cnPubs.ConnectionTimeout = 0
    cnPubs.CommandTimeout = 0
   
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE=cpssrv235m\test_sql_2005;INITIAL CATALOG=trainingv3_TST;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
    'Now open the connection.
    cnPubs.Open strConn

                   
    ' Create a recordset object.
    Set rsPubs = New ADODB.Recordset
    strSQL = "exec spReportWhoWhatWhere_TST '','','' "
    rsPubs.Open strSQL, cnPubs, adOpenStatic, adLockOptimistic
    cnPubs.CursorLocation = adUseServer
    ug.Print rsPubs.RecordCount
    Sheet1.Range("A1").CopyFromRecordset rsPubs
 
   
    cnPubs.Close
    Set cnPubs = Nothing
End Sub
0
leonstrykerCommented:
Not exactly. I corrected it below.

Also make sure to add the following line to your store procedure right at the top:

SET NOCOUNT ON

Leon
Sub DataExtract()
Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String

    ' Create a connection object.
    Set cnPubs = New ADODB.Connection
    cnPubs.ConnectionTimeout = 0
    cnPubs.CommandTimeout = 0
    cnPubs.CursorLocation = adUseServer
    
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=cpssrv235m\test_sql_2005;" & _
               "INITIAL CATALOG=trainingv3_TST;INTEGRATED SECURITY=sspi;"
    'Now open the connection.
    cnPubs.Open strConn
        
    ' Create a recordset object.
    Set rsPubs = New ADODB.Recordset
    strSQL = "exec spReportWhoWhatWhere_TST '','','' "
    rsPubs.Open strSQL, cnPubs, adOpenStatic, adLockOptimistic
    Debug.Print rsPubs.RecordCount
    
    Sheet1.Range("A1").CopyFromRecordset rsPubs

    cnPubs.Close
    Set cnPubs = Nothing
End Sub

Open in new window

0
GrapheneAuthor Commented:
Thank you Leon. I applied the suggested code to both the VB script and also the SP and received the error: Run-time error '3704': Operation is not allowed when the object is closed.
0
GrapheneAuthor Commented:
I forgot to add that Debug highlights the Debug.Print rsPubs.RecordCount
0
GrapheneAuthor Commented:
I was wondering if the below scenario might be happening in my case. If so, is there a way to ignore those errors within VB so that it runs all the way through?

---hey JJJCR_FOX and koolsid, thanx so much for replying and giving suggestions. The problem is resolved now. The problem was that the stored procedure had a sum of column query which was throwing error (coz the integer column had some null values). I was not able to catch the error in the beginning itself because the stored procedure as a whole was not throwing any error and executed properly. Since the query inside it was throwing an error, the front end Excel (macro) immediately caught that error and the record set object was closed.
0
leonstrykerCommented:
>>I was wondering if the below scenario might be happening in my case

The best way to track that is to extract the SQL string into the Immediate window and execute it directly.
0
GrapheneAuthor Commented:
Thank you Leon. I have done this and I receive no errors.
0
leonstrykerCommented:
As a test, change teh code inside your store procedure to something very simple like:

SELECT Count(*) FROM myTable

Now run your code. Does that work?

Leon
0
GrapheneAuthor Commented:
I get a result set of 5321, but I'm pulling from a temp table off of a stored procedure...should this make a difference?
0
leonstrykerCommented:
No it should not. Can I see your store procedure?

Leon
0
GrapheneAuthor Commented:
You might need glass eyes after as the code is horrendous, just a forewarning.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


--DECLARE @OrgNo VARCHAR(20)
--DECLARE @OrgVp VARCHAR(20)
--DECLARE @OrgDirector VARCHAR(20)
--
--DECLARE @whenDue DATETIME
--
--SET @whenDue = '04/30/2010'
--SET @OrgNo = '9830'
--SET @OrgDirector = 'Bob Chauza'

ALTER procedure [dbo].[spReportWhoWhatWhere_Exempt]

--exec    spReportWhoWhatWhere_Exempt '12/22/2011','7742','','Zawacke, Richard'
--exec    spReportWhoWhatWhere_Exempt '7742','','Zawacke, Richard'
--exec    spReportWhoWhatWhere_Exempt '','','' 

   --@whenDue DATETIME
	 @OrgNo VARCHAR(20) = ''
	,@OrgVp VARCHAR(20) = ''
	,@OrgDirector VARCHAR(20) = ''
AS
/*
	This procedure does a who,what, where report that includes the Exempt column. The report will show training requirements for employees based on individual and 
    organizational requirements. The report will also show the status of training requirement as to being completed, not completed, or exempt from training.  
	12/5/2012 knorseth Original
*/


DECLARE @TmpWhoTST TABLE (
	orgVp VARCHAR(30)
	,OrgDirector VARCHAR(30)
	,EmpOrgn VARCHAR(20)
	,orgName VARCHAR(30)
	,LifeNumber VARCHAR(100)
	,empName VARCHAR(100)
	,moduleType VARCHAR(50)
	,TrainingModule VARCHAR(100)
	,whenNextDue DATETIME
	,whenActuallyDue DATETIME
	,DateRequired DATETIME
	,WhenDue DATETIME
    ,ooc varchar(10)
	,DateCompleted DATETIME
	,CompletedText VARCHAR(100)
	,dateCreated DATETIME
	,EmailNotify INT
	,whenFirstDue DATETIME
	,ModuleTypeID INT
	,TrainingModuleID INT
    ,ssn varchar(50)
    ,modulecodealpha varchar(10)
	,EmployeeExempt varchar(50) 
)

IF @OrgNo <> ''
	INSERT INTO @TmpWhoTST
		SELECT
			r.orgVp
			,r.OrgDirector
			,r.EmpOrgn
			,r.orgName
			,r.LifeNumber
			,r.empName
			,r.moduleType
			,r.TrainingModule
			,r.whenNextDue
			,r.whenActuallyDue
			,r.DateRequired
			,r.WhenDue
            , case when (r.WhenDue < getdate()) then 'OOC'Else '' end  as ooc
			,r.DateCompleted
			,r.CompletedText
			,r.dateCreated
			,t.EmailNotify
			,r.whenFirstDue
			,r.ModuleTypeID
			,r.TrainingModuleID
            ,case when o.ssn is not null then 'Match located in orphan table' else '' end
            ,o.modulecodealpha 
			,case cast(r.EmployeeExempt as varchar(50)) when 1 then 'Exempt'when -1 then 'Exempt' Else '' end --ADDED TLV

		FROM vempRequirementsCompletedDetailDisplayTST R
		 JOIN ComplianceModuleCode t
			ON t.trainingModuleID = r.trainingModuleID
         left join orphanedcompliance o 
            ON r.lifenumber = o.ssn
            and
               t.modulecode = o.modulecodealpha
            --WHERE whenDue < @whenDue
		    --AND (employeeExempt =0 OR employeeExempt IS NULL)--MODIFIED TLV
	        WHERE emporgn = @OrgNo

ELSE IF @OrgVp <> '' 
	INSERT INTO @TmpWhoTST
		SELECT
			r.orgVp
			,r.OrgDirector
			,r.EmpOrgn
			,r.orgName
			,r.LifeNumber
			,r.empName
			,r.moduleType
			,r.TrainingModule
			,r.whenNextDue
			,r.whenActuallyDue
			,r.DateRequired
			,r.WhenDue
			, case when (r.WhenDue < getdate()) then 'OOC'Else '' end  as ooc
			,r.DateCompleted
			,r.CompletedText
			,r.dateCreated
			,t.EmailNotify
			,r.whenFirstDue
			,r.ModuleTypeID
			,r.TrainingModuleID 
            ,case when o.ssn is not null then 'Match located in orphan table' else '' end
            ,o.modulecodealpha 
			,case cast(r.EmployeeExempt as varchar(50)) when 1 then 'Exempt'when -1 then 'Exempt' Else '' end --ADDED TLV

		FROM vempRequirementsCompletedDetailDisplayTST R
		 JOIN ComplianceModuleCode t
			ON t.trainingModuleID = r.trainingModuleID
		 left join orphanedcompliance o 
            ON r.lifenumber = o.ssn
            and
               t.modulecode = o.modulecodealpha
	    --	WHERE whenDue < @whenDue
		--	AND (employeeExempt =0 OR employeeExempt IS NULL)--MODIFIED TLV
		    WHERE OrgVp = @OrgVp

ELSE IF @OrgDirector <> ''
	INSERT INTO @TmpWhoTST
		SELECT
			r.orgVp
			,r.OrgDirector
			,r.EmpOrgn
			,r.orgName
			,r.LifeNumber
			,r.empName
			,r.moduleType
			,r.TrainingModule
			,r.whenNextDue
			,r.whenActuallyDue
			,r.DateRequired
			,r.WhenDue
            , case when (r.WhenDue < getdate()) then 'OOC'Else '' end  as ooc
			,r.DateCompleted
			,r.CompletedText
			,r.dateCreated
			,t.EmailNotify
			,r.whenFirstDue
			,r.ModuleTypeID
			,r.TrainingModuleID 
            ,case when o.ssn is not null then 'Match located in orphan table' else '' end
            ,o.modulecodealpha 
			,case cast(r.EmployeeExempt as varchar(50)) when 1 then 'Exempt'when -1 then 'Exempt' Else '' end --ADDED TLV

		FROM vempRequirementsCompletedDetailDisplayTST R
	     JOIN ComplianceModuleCode t
			ON t.trainingModuleID = r.trainingModuleID
         left join orphanedcompliance o 
            ON r.lifenumber = o.ssn
            and 
               t.modulecode = o.modulecodealpha
		  --WHERE whenDue < @whenDue
		  --AND (employeeExempt =0 OR employeeExempt IS NULL)--MODIFIED TLV
		    WHERE OrgDirector = @OrgDirector
ELSE
	INSERT INTO @TmpWhoTST
		SELECT
			r.orgVp
			,r.OrgDirector
			,r.EmpOrgn
			,r.orgName
			,r.LifeNumber
			,r.empName
			,r.moduleType
			,r.TrainingModule
			,r.whenNextDue
			,r.whenActuallyDue
			,r.DateRequired
            ,r.WhenDue
		    , case when (r.WhenDue < getdate()) then 'OOC'Else '' end  
			,r.DateCompleted 
			,r.CompletedText
			,r.dateCreated
			,t.EmailNotify
			,r.whenFirstDue
			,r.ModuleTypeID
			,r.TrainingModuleID 
            ,case when o.ssn is not null then 'Match located in orphan table' else '' end
            ,o.modulecodealpha 
			,case cast(r.EmployeeExempt as varchar(50)) when 1 then 'Exempt'when -1 then 'Exempt' Else '' end --ADDED TLV
	
		FROM vempRequirementsCompletedDetailDisplayTST R
		 JOIN ComplianceModuleCode t
			ON t.trainingModuleID = r.trainingModuleID
         left join orphanedcompliance o 
            ON r.lifenumber = o.ssn
            and
               t.modulecode = o.modulecodealpha
		    --WHERE whenDue < @whenDue
			--AND (employeeExempt =0 OR employeeExempt IS NULL)--MODIFIED TLV

----------------------------------------------------------------------------------  
--
----------------------------------------------------------------------------------
SELECT
	orgVp as 'Org VP'
	,OrgDirector as 'Org Director'
	,EmpOrgn as 'Org Number'
	,orgName as 'Org'
	,EmployeeID = LifeNumber --as 'Badge Number'
	,empName as 'Employee Name'
	,WhenDue as 'Training Due Date'
    ,ooc as 'Out of Compliance'
    ,CASE 
		WHEN DateCompleted IS NULL THEN 'Never Taken'
		ELSE CAST(DateCompleted AS CHAR(12))
 		END as 'Module Completion'
    ,whenactuallydue as 'Previous Required Date'
    ,TrainingModule as 'Training Module'
	,moduleType as 'Training Type'
    ,dateCreated as 'Date Initiated'
	,daterequired as 'Individual Training Due'
	,whennextdue as 'Next Requirement Date'
	,Notes=COALESCE(CompletedText,'')
	--,EmailNotify
	--,whenFirstDue
	--,ModuleTypeID
	,TrainingModuleID as 'Module ID'
    ,ssn as 'Orphan Record' 
    ,case when modulecodealpha is null then '' else modulecodealpha end as 'Compliance Code'
	,EmployeeExempt as 'Exempt'

FROM @TmpWhoTST r
--where ooc = 'OOC'
--where ssn = 'Match located in orphan table'
--where datepart(yy, WhenDue) = 2012
--or datepart(yy, Whennextdue) = 2012
--where datepart(yy, Whennextdue) = 2012
--and trainingmoduleid in (83,122,85,53,54,57,125,126,59,56,61,62,63,64,65,66,67,86,68,69,72,73,74,75,76,77,70,87,79,82)
ORDER BY 
--r.trainingmodule,
r.whendue
	,r.OrgVp
	,r.orgDirector
	,r.empOrgn

/*
SELECT TrainingModule, 
       count(*)
FROM @TmpWhoTST r
where datepart(yy, Whendue) = 2012
or datepart(yy, whennextdue) = 2012
and trainingmoduleid in (83,122,85,53,54,57,125,126,59,56,61,62,63,64,65,66,67,86,68,69,72,73,74,75,76,77,70,87,79,82)
group by TrainingModule
*/
/*
select count(*)
from @tmpwhotst r

Open in new window

0
leonstrykerCommented:
Add SET NOCOUNT ON inside your store procedure and change cnPubs.CursorLocation = adUseServer
 to cnPubs.CursorLocation = adUseClient

Leon
0
GrapheneAuthor Commented:
Run-time error '3704': Operation is not allowed when the object is closed.
0
leonstrykerCommented:
Ok, lets try this. Change the first Insert in your code to a Select and comment everything else out. Can you return this to Excel?

Leon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrapheneAuthor Commented:
Thank you kindly for the assistance. My apologies for not updating this sooner. I will need to use your suggestions and resubmit at a later date as I needed to switch projects.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.