MSSQL Table Variables and MII 12.1

[Former Member]

I'm trying to use table variables in a SQL query in MII 12.1 SP8 Build 43 using MSSQL Server 2005. I'm using MSSQL JDBC driver 3.0 "sqljdbc.jar". I'm not having any luck using table variables (I'm not using these in a stored procedure, just a query template) Mode is fixedquery. For various reasons, creating a stored procedure is not an easy option for me in the environment.

As a test I've tried the following query, which works fine in SQL Server Management Studio.

SET NOCOUNT ON
DECLARE @Temp TABLE
(
	Col1 int IDENTITY(1,1) NOT NULL,
	Col2 varchar(2),
	Col3 datetime
)

INSERT INTO @Temp Values ('1',GETDATE())

SELECT Col1,Col2,Col3
FROM @Temp

I get "com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set."

Has anybody else run into this issue? I ran into this in 11.5 but that was with version 1.0 of the SQL JDBC driver.

UPDATE I just tried this with sqljdbc4.jar on my sandbox, with the same result.

Edited by: Doug Holtke on Jun 28, 2011 8:41 PM

[Former Member]

I get the same error if I try to use temp tables. It seems that version 3.0 of the JDBC driver seems to ignore the command "SET NOCOUNT ON"

[Former Member]

HI Doug

you are saying that it works in SQL servermanagement studio

I suggest you check you server URL sometimes this could be the issue

try other JDBC drivers with MII12.1 it works best with JDBC driver 2.0 which im using at the moment

JDBC driver 3.0 give problems .

let me known the output

[jcgood25]

On a 12.1.x system, I pasted your query in a new template and received 1,1,timestamp in the resultset. The selected Data Server was configured with the Una2000.jar driver (com.inet.tds.TdsDriver)

I get the same error you mentioned when using another Data Server configured to use the sqljdbc.jar driver(com.microsoft.sqlserver.jdbc.SQLServerDriver)

[Former Member]

Hi,

I also tried it on a 12.1.x system, connecting to MSSQL 2005 and 2008 using the

MICROSOFT SQL SERVER JDBC DRIVER 2.0 (sqljdbc.jar) without issue, worked in both cases

Kind Regards,

Diana Hoppe