Error running a stored procedured (-2147217900)

The following stored procedure works fine.

SELECT ...   FROM ...
(C.DateLost <= @DateFI AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')

However, I tried to move this query into my Access Application and assign it to a string variable named strSQLFS. I then call the stored procedure (see coding at the bottom of this question) and I get the error message:

-2147217900.

Any thoughts on how I can get around this error ?

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS
DECLARE @DateFI AS DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

SELECT ...
   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
   (C.DateLost <= @DateFI AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')

I called the paramterized stored procedure as follows:

With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       .Parameters.Refresh
       Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
       .Parameters.Append prmSQL
       Set RptYear = .CreateParameter("@RptYear", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append RptYear
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
End With

The reason I put the SQL string in the application is because I build the components of the SQL string dynamically based on the users response to various controls on the screen (from list boxes).
zimmer9Asked:
Who is Participating?
 
mdouganConnect With a Mentor Commented:
zimmer9,

the syntax error was probably caused because you didn't put an END statement at the end of your procedure (after the EXEC @prmSQL and before the go)

Maybe it's OK without it, but generally if you have more than one statement to execute, you need to wrap them in a block, and your SELECT before the EXEC would make me think that this would be true for you.  

Your other problem is that the SQL is referencing the variable @DateFI and that's fine in a regular select, but in the EXEC you need to take the value out of @DateFI and string it in your SQL... I think that you can REPLACE it....


CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS BEGIN
   DECLARE @DateFI DATETIME

   SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

   SET @prmSQL = REPLACE(@prmSQL, '@DateFI', ''' + CONVERT(VARCHAR(10), @DateFI, 121) + ''')

   -- for debugging
   PRINT @prmSQL

   EXEC @prmSQL

END
GO

Then, if you're still getting errors, check to see where @prmSQL was printed out and check the SQL for syntax error...
0
 
rafranciscoCommented:
I don't see @prmSQL being used anywhere in your stored procedure.  Have you tried running your stored procedure on Query Analyzer and did you get your desired result?  Are you trying to execute the @prmSQL?  If so, please post your full stored procedure.
0
 
mdouganCommented:
Hi zimmer9,

I'm not familiar with stored procs in Access, or whether it's an access application reading from a SQL Server strored proc... but the below applies to SQL Server anyway...

If you are saying that you're passing part or all of the SQL statement to the stored proc in the parameter @prmSQL (I'm not seeing where in the stored proc you're actually using that parameter), then you'll have to build the entire SQL statement as a varchar in the proc and then use the Execute statement to run it... so,


CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS BEGIN
--DECLARE @DateFI AS DATETIME   -- YOUR DEFINITION HERE IS NOT VALID FOR SQL SERVER AND YOU'RE MISSING 'BEGIN' ON THE LINE ABOVE
DECLARE @DateFI DATETIME
DECLARE @SQL VARCHAR(8000)

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

SET @SQL = @prmSQL + ' WHERE MYDATE = ' + CONVERT(VARCHAR(10), @DateFI, 121)

EXECUTE @SQL

END

Cheers!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
zimmer9Author Commented:
The reason you didn't see @prmSQL being used (and I should have deleted it at the time) was because I substituted the
value of @prmSQL in the stored procedure to prove that the SQL statement was valid when it executed in the stored procedure. As you wish, the value for the stored procedure is as follows.


?prmSQL
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  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
 (C.DateLost <= @DateFI AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')  

I reinstated the stored procedure (with the execution of @prmSQL that generated the error as follows):

I tried inserting "AS BEGIN" as suggested by mdougan but this causes a syntax error.

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS
DECLARE @DateFI DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

EXEC @prmSQL
GO
0
 
rafranciscoCommented:
Change this one:

EXEC @prmSQL

to this:

EXEC (@prmSQL)
0
 
rafranciscoConnect With a Mentor Commented:
Try this:

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS
DECLARE @DateFI DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

SET @prmSQL = REPLACE(@prmSQL, '@DateFI', '''' + CONVERT(VARCHAR(10), @DateFI, 111)
+ '''')
EXEC (@prmSQL)
GO
0
 
zimmer9Author Commented:
My guess is that problem is caused when I create a SQL string in the Access application with reference to @DateFI which is declared in the stored procedure. Then pass along this SQL string into the stored procedure. This doesn't cause a compile error but I guess it does cause the execution error.
0
 
rafranciscoCommented:
Yes, you're correct.  Your dynamic SQL statement is referencing @DateFI which is outside the scope of the stored procedure.

Have you tried my suggestion?
0
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.

All Courses

From novice to tech pro — start learning today.