Devart Forums

Hi Devart,

I downloaded the trial edition and I see that queries with parameters are executed with sp_executesql.

This is okay so far as I get the rows affected from my query. If however I want the scope_identity() it doesn't work because sp_executesql is executed under its own context.

Is it possible to somehow embed the parameter values in the query before it is sent to mssql?

SDAC doesn't use sp_executesql to execute a query with parameters. To assign a parameter value, you can use the followng code:

I know that you don't explictly use sp_executesql but oledb does. If you execute a query or an insert/update statement, check the mssql profiler and you will see that sp_executesql is used.

This is because parameters are sent to the back end to be evaluated by SQL.

Was the problem solved? If any other questions come up, please contact me.

Dimon wrote:Was the problem solved? If any other questions come up, please contact me.

No I still have the problem.

The problem is that if a query is without parameters, it is executes "as-is".

If the query has parameters then I see in the profiler that sp_executesql is used. I'd like parameters to be embedded in the query before being sent to the server.

Following up, here's what I get from the profiler.

Without parameters
SQL:BatchStarting select * from order
SQL:BatchCompleted select * from order

With parameters
exec sp_executesql N'select * from order where id=@P1',N'@P1 int',1
[/b]

What i need is that when I have parameters, the first scenario (select * from order where id=1) is used instead of calling stored procedure and letting sql handle parameters.

The previous engine (sqldirect) I use works like expected.

To include parameters values directly in a SQL query you should use macros instead of parameters. You can find more detailed information about macros in the SDAC help. But using of macros in this case is not a better way.
Please give a detailed description of the problem with using scope_identity().

Dimon wrote:To include parameters values directly in a SQL query you should use macros instead of parameters. You can find more detailed information about macros in the SDAC help. But using of macros in this case is not a better way.
Please give a detailed description of the problem with using scope_identity().

Hi, what do you mean is not a better way?


About scope_identity, the problem is that when sp_executesql is used, then your insert statement is executed by sp_executesql. If after the sp_executesql you run select scope_identity() you will see that you will not get the last identity value that was executed by sp_executesql because the scope of the statement is different.

I urge you to find a way to embed parameters the performance impact is killing me.

1. When you use parameters then SDAC passes it to SQL Server in optimal format and in most cases it improves performance.

2. The point is that the SCOPE_IDENTITY function returns the last identity value only in the same scope.
Therefore to solve the problem use the following SQL to insert the record: