7
$\begingroup$

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.

$\endgroup$
8
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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