Access can't find stored procedure after scripting on sql server

[AkAlan via AccessMonster.com]

I tried to script a stored procedure from my development database to my
production database by scripting it. He are the steps I followed:
In SMS I right click on the sp and select Script Stored Procedure AS_ Create
to_ New Query editor.
I edit the database in the square brackets USE [My DatabaseDevelopment] to
USE [My DatabaseProduction].
I then execute the script.
Now when I tryu and opent the stored procedure in the production adp I get an
error saying it can't find the stored procedure. I am using a sql compare
product and the only difference between the two sp's is how the name looks.
In the Development database it reads:

CREATE PROCEDURE dbo.spSelectFAC_PowerPlantsBySiteID

in the Production database (where I can't read it in access adp) it reads:

CREATE PROCEDURE [dbo].[spSelectFAC_PowerPlantsBySiteID]

Is there another way of moving a stored procedure created in one database to
another that I need to be aware of?

 

[Sylvain Lafontaine]

The absence or presence of the [] are probably of no importance here and you
see them in the second version because they are included in the creation
script.

First thing to try: refresh the database window by pressing F5 (or choosing
Refresh from the menu) when the database window has the focus and the focus
is set on the list of SP/Views/Functions or close the ADP project and reopen
it.

Another possibility would be a permission issue or a schema issue. For
example, it's not sure that your user account is mapped by default to dbo in
your production database. To be sure, create a new blank ADP project
connected to the production database and take a look at what is displaying.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Mary Chipman [MSFT]]

Sylvain had some good advice for figuring out where your sproc went,
so I'll answer the last part of your question -- another way of moving
a sproc from one database (or server) to another. Open up the stored
procedure definition in the development database and save it out to a
..SQL file. Then open up your production database in SSMS and create a
new, blank stored procedure. Open the saved .sql file in notepad and
copy/paste/execute, then press F5 on the database container/stored
procedure node to refresh the view so the new object displays in the
UI. It's a manual, slow way of doing things, but at least you know
what's going on and can verify all the steps. It also has the added
benefit of backing up your stored procedure definitions on disk.

You can also double-click a .SQL file and it will execute based on the
file association for .SQL. If you do this, then you need to make sure
that the USE [database] statement is in the file or your sproc will
end up getting created in the master database on that server.

--Mary