• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Why does my stored procedure execute successfully in Query Analyzer but unsucessfully in my application when I try the DoCmd.Transferspreadsheet

I created a Stored Procedure and when I execute it in the Query Analyzer, if I assign the 2nd parameter a value of strProp = "'A" as I do in the following example, the response is :

EXEC dbo.procFAllStates 2005,"A",NULL,NULL,NULL,NULL

(331 row(s) affected)
------------------------------------------------------------------
When I run my application, the following sequence of steps executes successfully until I reach the following statement, which causes my program to crash (DoCmd.Transferspreadsheet):      ?err.number  7874


 -------------------->     DoCmd.TransferSpreadsheet acExport, 8, "tblFAllStates", "C:\FALLUD1.XLS", True, ""

intYearSP = 2005
strProp = "A"
strAgg = ""
strIRA = ""
strStateF = ""
strSQL = ""

 With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFAllStates"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append .CreateParameter("Prop", adVarChar, adParamInput, 5, strProp)
       .Parameters.Append .CreateParameter("Agg", adVarChar, adParamInput, 5, strAgg)
       .Parameters.Append .CreateParameter("IRA", adVarChar, adParamInput, 5, strIRA)
       .Parameters.Append .CreateParameter("StateF", adVarChar, adParamInput, 200, strStateF)
       .Parameters.Append .CreateParameter("SQL", adVarChar, adParamInput, 8000, strSQL)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
 End With
 DoCmd.TransferSpreadsheet acExport, 8, "tblFAllStates", "C:\FALLUD1.XLS", True, ""

The stored procedure is as follows:
-----------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (5),
@Agg varchar (5),
@IRA varchar (5),
@StateF varchar(200),
@SQL varchar(8000)

AS

If exists(select * from dbo.sysobjects where name = 'tblFAllStates' and type = 'U')
Drop table tblFAllStates

Set @SQL =
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + '' '' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State WHERE S.FallCycle = 1 '

If @Prop = 'A'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0)) '
   END

If @Prop = 'B'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFA'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFN'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'O'
   BEGIN
      SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
   END

If @Prop = 'E'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0) '  
   END

If @Agg = 'A' AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + '  OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30'' AND (P.CashBalance>=[S].[AggregFS] OR P.Quantity>=[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg = 'B' AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30''  AND (P.CashBalance<[S].[AggregFS] AND P.Quantity<[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg Is Null AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @IRA Is Not Null
   BEGIN
     SET @SQL = @SQL + ' OR ((C.DateOfBirth <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.IRAFS-71) AS VARCHAR) + ''-12-31'' AND LEN(P.IRACode) > 0 ) OR (LEN(C.DateOfBirth) = 0 AND LEN(C.Zip) > 0)) '
   END

If @StateF Is Not Null
   BEGIN
     SET @SQL = @SQL + ' AND C.State In ' + @StateF
   END

PRINT @SQL
EXEC(@SQL)
GO
----------------------------------------------------------------

0
zimmer9
Asked:
zimmer9
  • 2
  • 2
1 Solution
 
zimmer9Author Commented:
If I try the following sequence of statements I crash on the following line:

   Do Until rstQueryFS.EOF

?err.number
 3704
-----------------------------------


With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFAllStates"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append .CreateParameter("Prop", adVarChar, adParamInput, 5, strProp)
       .Parameters.Append .CreateParameter("Agg", adVarChar, adParamInput, 5, strAgg)
       .Parameters.Append .CreateParameter("IRA", adVarChar, adParamInput, 5, strIRA)
       .Parameters.Append .CreateParameter("StateF", adVarChar, adParamInput, 200, strStateF)
       .Parameters.Append .CreateParameter("SQL", adVarChar, adParamInput, 8000, strSQL)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
    End With
    Set objXL = New Excel.Application
      objXL.Workbooks.Add
      strNextFile = GetNextFileName("C:\BRANCHLIST1.XLS")
      objXL.ActiveWorkbook.SaveAs strNextFile
      Set objWS = objXL.ActiveSheet
      objXL.ActiveSheet.Protect UserInterfaceOnly:=True
     
      For intCol = 0 To rstQueryFS.Fields.Count - 1
        Set fld = rstQueryFS.Fields(intCol)
        objWS.Cells(1, intCol + 1) = fld.Name
      Next intCol
     
      intRow = 2
      Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
     
           objWS.Cells(intRow, intCol + 1) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit
        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop
      DoCmd.Hourglass False
0
 
nmcdermaidCommented:
It probably can't see the table.

You can use OPENROWSET to transfer the data into Excel from your stored procedure.

Sounds like more of a VBA/MS Access issue than a SQL Server one .You might have more luck in the MS Access or VB area.
0
 
zimmer9Author Commented:
I'll give OPENROWSET a try. Do you know the syntax for OPERNROWSET to export to Excel  ?
0
 
nmcdermaidCommented:
Yep, its about the third time this week I've mentioned it!!!!

This will transfer data from tblFAllStates to C:\Test.XLS


INSERT INTO
OPENROWSET(
'Microsoft.Jet.OLE.DB.4.0',
'Excel 8.0;Database=C:\Test.XLS',
'SELECT * FROM [WorksheetName$]')

SELECT * FROM tblFAllStates




This is assuming:

-adhoc queries are enabled
-The Jet driver is installed on the server
-Test.XLS exists on the server
-The workbook has a worksheet called 'WorksheetName'
-The SQL Server account has write permissions to that folder


Please note that it creates a file on the server, not on the client.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now