BUG: ODBC Script File Does Not Act Like Stored Procedure Editor

When creating a database Stored Procedure in an ODBC Script File window via the ODBC Script File dialog box (from the File menu, select New and click the File tab), the ODBC Script File dialog box does not act like a Stored Procedure Editor dialog box. It allows a save of the Stored Procedure only once, failing on any successive attempts to save the Stored Procedure.

Visual InterDev does not parse any commands in the ODBC Script File window to see if they are Stored Procedures, Triggers, or Views. It treats them like regular ODBC commands and does no special work to ensure that they run properly. However, the Stored Procedure Editor ensures that a stored procedure saves without encountering the error saying that the Stored Procedure already exists in the database.

To work around this problem, right-click on the Database Connection node in the Data View tab of the Workspace window, and click Refresh. Expand the connection node to see the Stored Procedures folder. Expand that folder. Double-click the Stored Procedure created in the ODBC Script File window to edit it.

NOTE: Another workaround is to create the Stored Procedure by selecting Data View, right-clicking the Stored Procedures folder, selecting New Stored Procedure, then Write Procedure. Right-click in the window and select Run. Right-click and select Run again. No error appears.

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

Stored Procedures are saved to a database by executing the text of the Stored Procedure as if it were an ODBC command. They are "run" by executing the name of the Stored Procedure and adding any parameters ("call <procedure name>").

ODBC Script commands are saved to local storage, not in a database. ODBC Scripts are "run" by simply executing the commands they contain.

Saving a Stored Procedure requires work to remove the existing Stored Procedure before the updated one is saved. Visual InterDev does this work when a Stored Procedure is saved from within the Stored Procedure Editor. It does no special work when an ODBC Script is run, and so the second time a Stored Procedure inside an ODBC Script is run fails because the Stored Procedure already exists in the database.

Steps to Reproduce Behavior

1. Create a database project or add a data connection to a Web project.
2. Click the Data View tab in the Workspace window.
3. Select the Data connection to which to add a Stored Procedure in the Data View window.
4. From the File menu, select New. Click ODBC Script File.
5. Enter the text "Create Procedure MyProc as return" in the ODBC Script window (without the quotes).
6. Right-click in the window and select Run. NOTE: Repeating step 6 fails at this point.
   
   
7. Right-click the data connection sub-folder of the Workspace Window and select Refresh.
8. Expand the Data connection sub-folder.
9. Expand the Stored Procedures folder.
10. Double-click the new Stored Procedure sub-folder to open it for editing.

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site: