Why does the SA EF 4 function importer skip stored procedures that don't have a result set?

[Former Member]

I have a number of stored procedures in my database that aren't supposed to return a result set. However, if I don't end each one with SELECT 1, the stored procedure does not show up in the list of stored procedures that you can create an EF function import for.

Why? Is it because of the word "function" in the name? Really, all I want to do is call a stored procedure from the Entity Framework model.

Most of the stored procedures execute a single INSERT INTO ON EXISTING UPDATE statement. I don't need the procedure to return anything, I just want to call it. I can leave the SELECT 1 statements in the procedures, it's not hurting anything, but it'd be nice not to have it.

Can anyone shed some light on this for me?

EDIT:

I've been busy getting new releases out the door. Today, I had to modify a stored procedure and update the model and I ran into this again. Let me provide some additional information.

I just added a stored procedure called Foo to my database:

CREATE OR REPLACE STORED PROCEDURE FOO (
    IN @Arg1 INT,
    IN @Arg2 INT )
BEGIN
    -- Some code that does something to a table in the database.
    -- . . .

    -- If the next line is not in the stored procedure, I cannot create a Function Import in the model for this stored procedure.
    SELECT 1;

END;

I save the stored procedure in the database and it is added without any errors. I go into Visual Studio 2012. I open up my Entity Framework 4 model for the database. I right-click on the model's surface and choose "Update Model from Database ...". If the "SELECT 1" line is removed or commented out from the stored procedure, it does not show up on the "Add" tab on the Update Wizard. It only shows up on the "Add" tab on the Update Wizard if it returns a result set.

This is the problem I'm talking about. My code is a procedure. It doesn't need to return anything and my code doesn't expect it to. But I can't add the stored procedure to the model unless it returns something. So I have a number of stored procedures in my database, all ending in "SELECT 1", just so they'll show up in the Add tab of the Update Wizard and I can create function imports for them.

Why? Is it Entity Framework that's at fault? Is it the SQL Anywhere ADO .NET driver? Can you call a stored procedure that doesn't return anything using Entity Framework? If you can, why can't I import such a stored procedure into my model?

[jeff_albion]

Hi Tony,

I can't see this behaviour with EF5 / VS 2012 - I don't have EF4 handy to test unfortunately.

Against the demo database, I tried this:

CREATE PROCEDURE GROUPO.testProc()
NO RESULT SET
BEGIN
  MESSAGE 'I am not going to return a result set.';
END;

and then after using the ADO.NET Entity Data Model importer over the SQL Anywhere 16 ADO.NET driver connection, the following SELECT was essentially executed to get the list of procedures:

SELECT '[' + u.user_name + ']' + '[' + p.proc_name + ']' AS [Id],
       '' AS [CatalogName],
       u.user_name AS [SchemaName],
       p.proc_name AS [Name]
  FROM sys.sysprocedure p JOIN sys.sysuserperm u ON p.creator = u.user_id
 WHERE ( u.user_name NOT IN ( 'SYS', 'dbo', 'rs_systabgroup', 'SA_DEBUG', 'ml_server' ) )
   AND ( ( LOCATE( TRIM( p.proc_defn ), 'CREATE PROC' ) = 1 ) OR ( LOCATE( TRIM( p.proc_defn ), 'ALTER PROC' ) = 1 ) )
ORDER BY u.user_name, p.proc_name

This query returns my expected function:

Id,CatalogName,SchemaName,Name
'[GROUPO][testProc]','','GROUPO','testProc'

---

Can you enable request-level logging (i.e. dbsrv16 -zr SQL -zo rll.txt) to discover what query is being executed at the time of the import that would be excluding your procedures?

[jeff_albion]

Even with your updated steps, I'm still not seeing this behaviour in my configuration:

CREATE OR REPLACE PROCEDURE FOO (
IN @Arg1 INT,
IN @Arg2 INT )
BEGIN
    CREATE TABLE IF NOT EXISTS t1 (c1 int);
    INSERT INTO t1 VALUES (1);
    COMMIT;
END;

Can you enable request-level logging as I mentioned above?

[jeff_albion]

Also: Which version of the SQL Anywhere ADO.NET provider are you using, and which version of SQL Anywhere?

[Former Member]

We are running 12.0.1.3895. I have a 67 KB text file with the result from turning on request-level logging. How do I get that to you?

Also, the EF model was created with EF 4. We're using the EF 5 DLLs for .NET Framework 4 in our application (I think they're EF 4.5). I don't think it makes a difference but thought I'd include it in case it does.

[jeff_albion]

How do I get that to you?

If you would like us to look at the log with you, you should open a technical support case underneath your support ID.

If you'd like to continue trying to debug this issue over the forum, you'll want to look for the actual query that is sent when the EF4 importer connects. Searching for some of the query terms that I posted from my query may identify the query faster for you in the request-level log.

[Former Member]

Jeff:

I have more information for you. It turns out the problem isn't what I thought it was. Please see my answer for an explanation.

[jeff_albion · Accepted solution]

Update #1: CR #747308 has now been resolved in 12.0.1.3978. The Windows x86/x64 EBF has been requested.

---

EDIT 2: We just finished some testing and we can verify that the IF NOT EXISTS followed immediately by the ALTER PROCEDURE command, when run in dbisql.exe, causes the comment to appear in front of the CREATE PROCEDURE in the proc_defn column.

Thank you for the addition - I can now reproduce this aspect easily too. I have submitted the procedure comment issue for engineering review to see if this is the intended behaviour (CR #747519) and have also submitted the ADO.NET EF query issue to be fixed (CR #747308).

[Former Member · Accepted solution]

Jeff, thanks. I look forward to the release and getting rid of those SELECT 1 lines in my stored procedures.

I checked off my answer as the answer, but I'd rather have this one be the official answer. Is there any way in that they can be switched?

[VolkerBarth · Accepted solution]

AFAIK, it should work that way: You can switch the "acceped" answer by unchecking the current one and then check the desired one.

[Former Member]

Using the query that Jeff included in his answer, namely:

SELECT '[' + u.user_name + ']' + '[' + p.proc_name + ']' AS [Id],
       '' AS [CatalogName],
       u.user_name AS [SchemaName],
       p.proc_name AS [Name]
  FROM sys.sysprocedure p JOIN sys.sysuserperm u ON p.creator = u.user_id
 WHERE ( u.user_name NOT IN ( 'SYS', 'dbo', 'rs_systabgroup', 'SA_DEBUG', 'ml_server' ) )
   AND ( ( LOCATE( TRIM( p.proc_defn ), 'CREATE PROC' ) = 1 ) OR ( LOCATE( TRIM( p.proc_defn ), 'ALTER PROC' ) = 1 ) )
ORDER BY u.user_name, p.proc_name

I did a little spelunking in my database. I have a stored procedure in my database which I haven't been able to create a function import for. The only way I was able to create one was to edit it and add a "SELECT 1" to the end of the stored procedure.

It turns out that the problem is not that the stored procedure was missing a SELECT 1. If you look at the second line of the WHERE clause in the query that Jeff posted, it's expecting the procedure definition to start with either 'CREATE PROC' or 'ALTER PROC'. In the very first character. The script that we wrote to propagate changes to our database schema to existing installs includes a comment:

/* Version 38 */

The proc_defn column for this stored procedure starts with this comment and the CREATE PROCEDURE statement is the very next thing after it.

I don't know why or how that comment made it into the proc_defn column in the system table, but it did. And if the query that Jeff included is the query that is indeed used by Visual Studio when it queries the database for the list of stored procedures, then this comment is the problem. Either the query needs to be modified so it ignores comments at the start of the procedure definition, like the parser does, or I don't know what.

To be thorough, I edited the stored procedure in question in Sybase Central. All I did was open the stored procedure & hit Ctrl-S to save it. Sure enough, the comment got moved after the CREATE PROCEDURE (after the left parentheses but before the first parameter). Jeff's query now sees this stored procedure.

And, when I run the Update Model from Database command in Visual Studio, sure enough, the stored procedure shows up in the list. I can create a function import and everything works. There is no SELECT 1 at the end of the stored procedure. Which I guess means I can go and delete those lines from the other stored procedures, except I'm probably not going to, yet. There are other fish to fry.

EDIT:

We break our changes up into versions. As we fix issues or implement new functionality, each version is a set of changes being made to the database in a block. There's a table in our database called "CarSystem_Versions". There's a row in that table for each version that has been applied to the database.

The script began as an export of the base database schema, followed by a series of blocks of statements, one for each version change.

Each version is coded in the following format:

/* Version x */
IF NOT EXISTS ( SELECT 1 FROM CarSystem_Versions WHERE VersionId = x ) THEN

    -- Version specific statements

    INSERT INTO CarSystem_Versions
        ( VersionId, VersionTitle, Description, VersionDate, LocaleId, CreatedDate, ModifyDate )
    VALUES
        ( . . . );

/* Version x */
END IF;
go

In looking over the script, there are four stored procedures that have this problem, only one of which I want to import. The blocks for these four versions are similar in that they look like this:

/* Version x */
IF NOT EXISTS ( SELECT 1 FROM CarSystem_Versions WHERE VersionId = x ) THEN

    ALTER PROCEDURE . . .

    INSERT INTO CarSystem_Versions
        ( VersionId, VersionTitle, Description, VersionDate, LocaleId, CreatedDate, ModifyDate )
    VALUES
        ( . . . );

/* Version x */
END IF;
go

Start with something like this and see if that does it. If it doesn't, I don't know how it happens.

I'm no longer sure about dbisqlc.exe. I know for a fact that this issue happens when the script is loaded into dbisql.exe and executed. And, just to make sure you've got everything you need, here's the DDL for the CarSystem_Versions table:

CREATE TABLE "DBA"."CarSystem_Versions" (
    "VersionId" INTEGER NOT NULL,
    "VersionTitle" VARCHAR(500) NOT NULL,
    "Description" VARCHAR(8000) NULL,
    "VersionDate" TIMESTAMP WITH TIME ZONE NOT NULL,
    "LocaleId" VARCHAR(50) NOT NULL,
    "CreatedDate" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    "ModifyDate" TIMESTAMP WITH TIME ZONE NULL,
    PRIMARY KEY ( "VersionId" ASC )
) IN "system";

EDIT 2: We just finished some testing and we can verify that the IF NOT EXISTS followed immediately by the ALTER PROCEDURE command, when run in dbisql.exe, causes the comment to appear in front of the CREATE PROCEDURE in the proc_defn column. This happens even if the stored procedure's body is empty. If there's any other statement between the two, no comment goes into the column. Which is actually preferable, if you ask me.

[Former Member]

To add some more information, our product's installer uses dbisqlc.exe to execute our update script. When I create a new database on my workstation, I use the 64-bit version of Interactive SQL (dbisql.exe) to run the script.

[jeff_albion]

Thank Tony for coming back here and posting the results of your investigation. I am going to investigate if we can improve this situation once the database gets into this state.

I don't know why or how that comment made it into the proc_defn column in the system table, but it did.

I think this is the key issue here that we're not expecting - do you have any simple steps to reproduce just this artifact? (Can you run a SQL script from dbisqlc and see it show up like this in the system tables?)

[Former Member]

Jeff:

Please see my edit to my answer.

[VolkerBarth]

If there's any other statement between the two, no comment goes into the column. Which is actually preferable, if you ask me.

In my experience, comments in front of CREATE PROC/ALTER PROC seem always to make it into the proc_defn column, however just behind the words "CREATE PROC " - i.e. immediately before the procedure's name. (And interestingly enough, it's not part of the "source" column.)

I've always felt that this is not necessarily expected behaviour but a "nice" feature to conserve my comments...

(Note: I've not faced the particular situation with IF NOT EXISTS / ALTER ...)

[Former Member]

That's what I've seen, when you explicitly put the comment in that location (i.e., in front of the CREATE PROC) yourself.

Speaking of preserving formatting, there are times when SA just throws away my formatting, especially if you debug a stored proc. Very annoying.

[reimer_pods]

W.r.t. source formatting you might want to check the value of the database option "preserve_source_format". It should be set to "on", if you want to keep your edit style.

[VolkerBarth]

...though it seems (as stated in my comment above) that the "source" column does not contain those comments that are positioned before the CREATE/ALTER PROC...

No need to worry, I'd say, as I would expect anyone with real care for own's SQL scripts to manage them with a source code control system or something comparable...