?
Solved

Error running a stored procedured (-2147217900)

Posted on 2005-04-22
8
Medium Priority
?
250 Views
Last Modified: 2010-03-19
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).
0
Comment
Question by:zimmer9
  • 4
  • 2
  • 2
8 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13846544
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
 
LVL 18

Expert Comment

by:mdougan
ID: 13846622
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
 

Author Comment

by:zimmer9
ID: 13846854
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13846865
Change this one:

EXEC @prmSQL

to this:

EXEC (@prmSQL)
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 1000 total points
ID: 13846882
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
 

Author Comment

by:zimmer9
ID: 13846904
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13846915
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
 
LVL 18

Accepted Solution

by:
mdougan earned 1000 total points
ID: 13846916
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question