1

I use a Azure DB(MS-Sql) and administrate the DB with Management Studio for SQL Server 2016. My Application is a simple C#/WPF-Application.

So here we go, this is my SProc:

    ALTER PROCEDURE [dbo].[StartChangeTrackingProcedure]
(
@UID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
declare @sql nvarchar(max);

SET @sql = N'INSERT INTO TBL_ChangeTrackerPerLoggedUser 
             VALUES (0,0,0,0,0, @UID)';
             PRINT @sql;
EXECUTE sp_executesql @sql, '@UID uniqueidentifier', @UID = @UID
END

The TBL_ChangeTrackerPerLoggedUser's name is a bit confusing as it has nothing to do with the ordinary ChangeTracking-Mechanism, it is just a simple table where you can look up wether there were made any changes to DB or not (because Azure has no Query Notifications, right?). It looks like this:

CREATE TABLE [dbo].[TBL_ChangeTrackerPerLoggedUser](
[ChangedTemplates] [bit] NOT NULL,
[ChangedInstances] [bit] NOT NULL,
[ChangedWF] [bit] NOT NULL,
[ChangedUser] [bit] NOT NULL,
[ChangedDoc] [bit] NOT NULL,
[UserDependency] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_TBL_ChangeTrackerPerLoggedUser] PRIMARY KEY CLUSTERED 
(
[UserDependency] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Next you'll see the call from Entitiy Framework, where LoggedUser.Instance.UsrID is a GUID:

 using (NCDBEntities DBContext = new NCDBEntities())
 {
     var uid = new SqlParameter("@UID", LoggedUser.Instance.UsrID);
     var res = DBContext.Database
               .SqlQuery<TBL_ChangeTrackerPerLoggedUser>
               ("StartChangeTrackingProcedure @UID", uid).ToList();
 }

Now, when i process this code I get an EntityCommandExecutionException telling me:

Der Datenleser ist mit dem angegebenen Wert für 'NCDBModel.TBL_ChangeTrackerPerLoggedUser' nicht kompatibel. Ein Element vom Typ ('ChangedTemplates') weist keine entsprechende Spalte im gleichnamigen Datenleser auf.

Translated this means something like:

Datareader is not compatible with the given value for 'NCDBModel.TBL_ChangeTrackerPerLoggedUser'. One element of type ('ChangedTemplates') has no actual column in equal named Datareader.

But when i take a look at DB at this point, SProc has been executed successfully: This is the result, as i expected...

So...is there any advice or explanation you can provide to understand this kind of magic? Should i simply catch this Exception and act like it never happened? Sideeffects etc.? Thank you!

1 Answer 1

2

You use an API that expects a result set (SqlQuery<...>(...)) but your stored procedure produces no result set. Hence the error message. The error occurs on the client side, after your procedure has executed, to explain the 'magic' you see.

You have several alternatives:

2
  • Thank you! I've chosen the ExecuteSQLCommand since at the moment i don't need any return value. My call looks like that: using (NCDBEntities DBContext = new NCDBEntities()) { var temp = DBContext.Database.ExecuteSqlCommand ("StartChangeTrackingProcedure @UID", new SqlParameter("@UID", LoggedUser.Instance.UsrID)); }
    – GeKu
    Jul 5, 2017 at 12:55
  • temp gets an '-1'-value what´s a bit weird but no exception is rising and the result is as expected. If anybody has any suggestions/recommendations feel free to comment, i´m still learning!
    – GeKu
    Jul 5, 2017 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.