TSQL problem on Access 2007 and UP

[jsucupira]

Hello,


After upgrading to office 2010 I am not able to make changes to my store procedures. They all work, but every time I try to modify it or even just saving without any modification I get an ADO error.

Here is one of my store procedures:

Create PROCEDURE TestSP
(
@Course varchar(10) = '%', 
@Location varchar(50) = '%', 
@Term varchar(41) = '%', 
@Type varchar(20)  = '%', 
@Status Varchar(20) = '%'
)
as

set nocount on
        

         SELECT DISTINCT Link, Session, Term, Type 
          FROM  dbo.PFMain
          WHERE   (Course LIKE @Course) AND (Location LIKE @Location) AND (Term LIKE @Term) AND (Type LIKE @Type) AND (Status LIKE @Status) 

Return

For this Store Procedure I get the following error: ADO error: Incorrect syntax near the keyword 'PROCEDURE'. Must declare the variable '@Status'.

Thanks in advance for the help.

[Rixxe]

As far as i'm aware, you don't need to bracket any objects or variables...

Below is the first part of a procedure i use in SQL Server:

ALTERPROCEDURE [dbo].[importahd]
@PathFileName nvarchar(2000),
@tblname nvarchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(2000)
....
....

Note the DECLARE.

Perhaps it's very different in TSQL, but hopefully this helped a bit.

Good luck

[jsucupira]

As far as i'm aware, you don't need to bracket any objects or variables...

Below is the first part of a procedure i use in SQL Server:

ALTERPROCEDURE [dbo].[importahd]
@PathFileName nvarchar(2000),
@tblname nvarchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(2000)
....
....

Note the DECLARE.

Perhaps it's very different in TSQL, but hopefully this helped a bit.

Good luck

Bracket or no bracket it still gives me the same error.

[NoellaG]

Hi,

I don't think it's the brackets. You don't need them, but I don't think they're harmfull. And in my knowledge you don't need a DECLARE section if you don't have any variables to declare.
I had some troubles to after upgrading but that was after upgrading both the back-end database from SQL 2000 to 2008 and Access from 2007 to 2010.
I found much depends on the version combination back-end/front-end version.

I solved most of my problems by enabling the TCP/IP protocol in the SQL server Configuration manager.

greeting
Noëlla