This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I just created a new stored procedure (MSSQL 2008). Dragging the procedure into the design surface resulted in this error message:
Exception while processing Server Explorer drag: System.Data.SqlClient.SqlException: Must declare the scalar variable "@InitialTransactionStatusId". Must declare the scalar variable "@InitialTransactionStatusId". at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Mindscape.LightSpeed.Generator.Extraction.SqlServerExtractorBase.<ExtractProcedures>d__d.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at Mindscape.LightSpeed.Generator.Extraction.Provider`1.ExtractProcedures(IEnumerable`1 procedureNames) at Mindscape.LightSpeed.Generator.Integration.ProcedureBuilder.AddProceduresToModel(Model model, ILightSpeedDataSource connection, IEnumerable`1 procedureNames, IGenerationLog log) at Mindscape.LightSpeed.Generator.Integration.DiagramDragDropHandler.<>c__DisplayClass4.<OnDragDrop>b__3(DragObject dragObject, IGenerationLog log) at Mindscape.LightSpeed.Generator.Integration.ServerExplorer.IfIsServerExplorerDrag(DragEventArgs e, DragAction action) --
below is the current signature of my procedure (in case it causes confusion...the word 'Transaction' in variables refers to payment transactions, not db transactions):
ALTER PROCEDURE [dbo].[ChangeTransactionStatus]
@InitialTransactionStatus varchar(25) = '',
@InitialTransactionStatusId int = 0,
@NewTransactionStatus varchar(25) = '',
@LookupCode char(3) = null,
@PendingTimesoutAfter datetime = null
Do you have any ideas what this error might be?
|
|
|
It looks like it may be an error in the body of the procedure. When you drag a procedure onto the design surface, we invoke that procedure (with dummy values and the SchemaOnly option) because this is the only way to determine the schema of the result set. If the procedure throws an error, that error propagates up to us. (I've tested a procedure with your signature but a dummy implementation and that appears to work.) Can you execute the procedure successfully in SQL Server Management Studio (with default parameter values)? |
|
|
Yes, and as a workaround I have the stored procedure working using ADO.NET instead of Lightspeed, but I'd rather have all the data access done the same way. |
|
|
Could you provide us with the procedure body? It works for me with a dummy body, so I think there must be something that causes it to error with however we call it. By the way, if all else fails, don't forget you can create the procedure and result entity type by dragging on a Select Procedure and Entity from the toolbox and configuring them by hand. |
|
|
Procedure body sent directly via email. Thanks |
|
|
Well, I'm still not sure what the underlying problem is, but looking at the procedure body, you won't be able to represent this on the designer anyway. The designer currently supports only "select procedures," i.e. procedures that return a result set that we can materialise as entities. Your procedure doesn't return an entity record but a single-element result set containing an ID or status code. So even if we were to fix the exception, the designer wouldn't generate a useful wrapper for you anyway. You will therefore need to call this procedure manually using a ProcedureQuery object and the IUnitOfWork.Calculate method. (Of course you can wrap this up in a custom method on the unit of work class via a partial class so as not to clutter your application code; this is all that a designer-generated wrapper would do anyway.) Given this, I'm not going to investigate the designer issue any further at the moment; but thanks for reporting the bug and we'll bear this test case in mind if we later add scalar procedure support to the designer. |
|