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

access 2010 stored procedure made as text can not be edited

When i create a stored procedure as text in msacces, it works fine, but when i want to edit it i get numerous errors. if i choose not to save the changes and run the procedure again. it works fine

when i remove the second set no count on an save i get the following error

must declare the scalar variable @isbn

when i used the same sort of procedure in access 2003 it worked just fine ??

CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS
SET              NOCOUNT ON
SET              NOCOUNT ON CREATE TABLE #voorraad_afrekentitel(ISBN varchar(13) NOT NULL, jaar char(4), fonds int, titel varchar(50), auteur varchar(50), voornaam varchar(50), 
                      eindvoorraad int, inslagen int, verkopen int, verkopenR int)
                          INSERT      
                           INTO            #voorraad_afrekentitel(Isbn, jaar, fonds, Titel, Auteur, Voornaam, Eindvoorraad, inslagen, verkopen, verkopenr)
                                                      SELECT DISTINCT 
                                                                              dbo.VW_eindvoorraad.ISBN, dbo.VW_eindvoorraad.jaar, dbo.PI_artikel_informatie.Fonds, dbo.VW_eindvoorraad.Titel, 
                                                                              dbo.VW_eindvoorraad.Auteur, dbo.VW_eindvoorraad.Voornaam, dbo.VW_eindvoorraad.eindvoorraad, dbo.VW_Inslagen.inslagen, 
                                                                              ISNULL(dbo.VW_Inslagen.inslagen, 2) - ISNULL(dbo.VW_eindvoorraad.eindvoorraad, 2) AS verkopen, 
                                                                              dbo.[Tsomverkopen in jaar].[verkopen royalty]
                                                       FROM         dbo.VW_eindvoorraad LEFT OUTER JOIN
                                                                              dbo.[Tsomverkopen in jaar] ON dbo.VW_eindvoorraad.ISBN = dbo.[Tsomverkopen in jaar].ISBN AND 
                                                                              dbo.VW_eindvoorraad.jaar = dbo.[Tsomverkopen in jaar].Jaar LEFT OUTER JOIN
                                                                              dbo.PI_artikel_informatie ON dbo.VW_eindvoorraad.ISBN = dbo.PI_artikel_informatie.ISBN LEFT OUTER JOIN
                                                                              dbo.VW_Inslagen ON dbo.VW_eindvoorraad.jaar = dbo.VW_Inslagen.jaar AND dbo.VW_eindvoorraad.ISBN = dbo.VW_Inslagen.Isbn
                                                       WHERE     (dbo.VW_eindvoorraad.ISBN = @isbn)
                                                                                  SELECT     Isbn, jaar, fonds, Titel, Auteur, Voornaam, Eindvoorraad, inslagen, verkopen, verkopenr
                                                                                   FROM         #voorraad_afrekentitel RETURN

Open in new window

0
timohorn
Asked:
timohorn
  • 5
  • 3
1 Solution
 
Nick67Commented:
Dumb Question
<CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS....>

This creates it.  When you go to edit it, it must be
ALTER PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS...

Is the problem as simple as that?
0
 
timohornAuthor Commented:
i wil try it tomorrow morning its 23:30 here and let you know i hope so, but still its strange.
0
 
Nick67Commented:
The Netherlands?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Nick67Commented:
This may be a bit squirrelly

CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS

Why is the bold text there?

shouldn't it be

CREATE PROCEDURE OpgeslagenProcedure2  AS
0
 
Nick67Commented:
Ah,
Formatting!

Try

CREATE PROCEDURE OpgeslagenProcedure2
@Isbn char(13) = ''
AS
SET              NOCOUNT ON
...

It may be insisting on a default for your parameter
<must declare the scalar variable @isbn>
0
 
timohornAuthor Commented:
Yes. The netherlands try it tomorow , but with my old acces i had no troubleshooter at all ..
0
 
Nick67Commented:
Each version of Access expects a certain version of SQL Server and has features inherent to that SQL Server version.
Access 2003 played with SQL Server 2005
Access 2007 with SQL Server 2008
Access 2010 with SQL Server 2008 R2

You can downlevel edit -- Access 2010 works with any -- up not uplevel edit -- Access 2003 can't change 2008R2 objects.
Any changes made to SQL Server 2008R2 therefore get reflected by Access 2010.
If SQL Server 2008R2 doesn't allow parameter declarations without defaults, then even if you are using SQL Server 2005, Access 2010 won't let you use non-current syntax.

That explains the discrepancy.
Tommorow, we'll see if we can fix it
Welterusten
0
 
Anthony PerkinsCommented:
Unrelated to your problem but:
A.  You have SET NOCOUNT ON twice.
B.  There is no need for a temporary table
C. Use aliases as in something like this:
CREATE PROCEDURE OpgeslagenProcedure2 
		@Isbn char(13)
AS 

SET NOCOUNT ON

SELECT DISTINCT
        e.ISBN,
        e.jaar,
        p.Fonds,
        e.Titel,
        e.Auteur,
        e.Voornaam,
        e.eindvoorraad,
        i.inslagen,
        ISNULL(i.inslagen, 2) - ISNULL(e.eindvoorraad, 2) AS verkopen,
        t.[verkopen royalty]
FROM    dbo.VW_eindvoorraad e
        LEFT JOIN dbo.[Tsomverkopen in jaar] t ON e.ISBN = t.ISBN AND e.jaar = t.Jaar
        LEFT JOIN dbo.PI_artikel_informatie p ON e.ISBN = p.ISBN
        LEFT JOIN dbo.VW_Inslagen i ON e.jaar = i.jaar AND e.ISBN = i.Isbn
WHERE   e.ISBN = @isbn

RETURN

Open in new window

0
 
timohornAuthor Commented:
access 2007 does the trick

i have no time to experiment, maybey later
Thanxs
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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