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 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 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? |
Update #1: CR #747308 has now been resolved in 12.0.1.3978. The Windows x86/x64 EBF has been requested.
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). Jeff, thanks. I look forward to the release and getting rid of those 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?
(09 Oct '13, 08:24)
TonyV
Replies hidden
AFAIK, it should work that way: You can switch the "acceped" answer by unchecking the current one and then check the desired one.
(09 Oct '13, 08:56)
Volker Barth
|
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 " It turns out that the problem is not that the stored procedure was missing a /* Version 38 */
The I don't know why or how that comment made it into the 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 And, when I run the 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 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.
(19 Sep '13, 13:46)
TonyV
Replies hidden
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 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?)
(19 Sep '13, 17:19)
Jeff Albion
Jeff: Please see my edit to my answer.
(20 Sep '13, 12:04)
TonyV
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 ...)
(20 Sep '13, 13:44)
Volker Barth
Replies hidden
That's what I've seen, when you explicitly put the comment in that location (i.e., in front of the Speaking of preserving formatting, there are times when SA just throws away my formatting, especially if you debug a stored proc. Very annoying.
(20 Sep '13, 14:09)
TonyV
1
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.
(23 Sep '13, 04:03)
Reimer Pods
Replies hidden
...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...
(24 Sep '13, 03:55)
Volker Barth
Comment Text Removed
|
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:
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:
This query returns my expected function:
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?Even with your updated steps, I'm still not seeing this behaviour in my configuration:
Can you enable request-level logging as I mentioned above?
Also: Which version of the SQL Anywhere ADO.NET provider are you using, and which version of SQL Anywhere?
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.
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.
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.