Complex queries to MS SQL Server

Question

I'm using DatabaseLink to query MS SQL Server with SQLExecute which works totaly fine, for ex.:

SQLExecute[conn, "declare @dt date;
select * from ttable where date=@dt"]

However the problem starts when i try to use table variable and try to return it, for ex.:

SQLExecute[conn, "declare @dt date;
declare @tab table(
var1 int
date date)
insert into @tab
select * from ttable where date=@dt;
select * from @tab"]

It returns the number of rows and not the table. Is there a way to solve that? It seems that Mathematica returns results for the 'insert' statement, not the final results.

Answer 1

By default, SQL Server will return two result sets when it executes the exhibited SQL batch. The first result set contains the number of rows inserted by the INSERT statement. The second contains the rows from the SELECT statement. DatabaseLink will only report the first result set from a batch. However, you can tell SQL Server to avoid generating the first result set by adding the command SET NOCOUNT ON prior to the INSERT statement.