cancel
Showing results for 
Search instead for 
Did you mean: 

Using Table Variables MSSQL with xMII 11.5

Former Member
0 Kudos

I was trying an experiment with a query today where instead of creating a temp table in SQL, I used the T-SQL table variable. For a simple example:

DECLARE @ProductTotals TABLE

( ProductID int, Revenue money)

INSERT INTO @ProductTotals (ProductID, Revenue)

SELECT ProductID, SUM(UnitPrice * Quantity)

FROM [Order Details]

GROUP BY ProductID

SELECT *

FROM @ProductTotals

Well, this works fine in SQL Query Analyzer , but not in xMII. I get a message that says "no results returned from this query" So you ask, "why not just use a temp table" Well on the real application, the query can take up to 30 seconds to run, and I have the chance of having multiple users execute the same query in that time, in which my temp table will get stomped on creating an error. So I thought of using table variables as a possible alternative to avoid this.

I have already figured a work around, I put this code into a stored procedure and call that. I could also do a similar workaround using separate queries / BLS. However, I'm curious why xMII wouldn't execute this in the first place, being that it worked fine in Query Analyzer against the exact same database.

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Doug,

I have few question:

+ MS SQL Server Version

+ String in the ServerURL

+ Error Description in the General Logs for your last example.

Regards

Pedro

0 Kudos

Are you running in command or fixed query mode? Since you are doing an insert and a select, it would almost seem as if xMII is going to invalidate one or the other of your scripts statements. (I would guess that you are running in fixed query mode from the error message, but that is just a guess).

Mike

Former Member
0 Kudos

Doug,

it is strange. Your example works on my box with both JDBC Driver:

+ com.inet.tds.TdsDriver

+ com.microsoft.sqlserver.jdbc.SQLServerDriver

Which date have your sqljdbc.jar files inside?

Regards

Pedro

Former Member
0 Kudos

The date on the file is 10/17/2007. FYI. I did not download or install this driver. It was done by the customer. I'm not sure of a quick way to see what version of the driver that it is. I assume 1.2

Edited by: Doug Holtke on May 9, 2008 10:03 PM

Former Member
0 Kudos

Doug,

I tried on my box with the "sqljdbc.jar" from 11.08.2006 (German Dateformat dd.MM.yyyy) and works also.

For my test I had using the following SQL-Statement:

DECLARE @ProductTotals TABLE 
( ProductCount int, ProductID int, Revenue money)

INSERT @ProductTotals
SELECT count(O.ProductID) as ProductCount, P.ProductID, SUM(O.UnitPrice * O.Quantity)
FROM [Order Details] O
INNER JOIN Products P
ON O.ProductID = P.ProductID
GROUP BY P.ProductID

SELECT *
FROM @ProductTotals

Did you get a result back in MII only for the SELECT statement

SELECT count(O.ProductID) as ProductCount, P.ProductID, SUM(O.UnitPrice * O.Quantity)
FROM [Order Details] O
INNER JOIN Products P
ON O.ProductID = P.ProductID
GROUP BY P.ProductID

? My opinion is, that your select brings nothing back.

Regards

Pedro

Former Member
0 Kudos

Even with SET NOCOUNT ON, it still wouldn't work. It's a JDBC Driver issue, after doing some digging I found some posts on Microsofts site.[http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2461966&SiteID=17]

For a VERY simple example take

 
DECLARE  @tblALineID TABLE (

AssetCount       integer,
LineID   	integer)

SET NOCOUNT ON

INSERT @tblALineID
SELECT  '1' AS AssetCount, '2' AS LineID

Select * FROM @tblALineID 

Works fine with com.inet.tds.TdsDriver, but not with com.microsoft.sqlserver.jdbc.SQLServerDriver. From what I'm reading, SETNOCOUNT ON works with stored procedures.

Former Member
0 Kudos

Doug,

you forgot to include the [] before and after the table "Order Detatils".

This works on my box:

DECLARE @ProductTotals TABLE

( ProductID int, Revenue money)

INSERT INTO @ProductTotals (ProductID, Revenue)

SELECT ProductID, SUM(UnitPrice * Quantity)

FROM [Order Details]

GROUP BY ProductID

SELECT *

FROM @ProductTotals

Former Member
0 Kudos

Ahh good catch. But still doesn't work on my xMII server. Here is a sample from the actual query I'm using for my project

DECLARE @tblALineID TABLE (

AssetCount integer,

LineID integer)

INSERT @tblALineID

SELECT COUNT(A.AssetID)as AssetCount,S.SystemLineID

FROM PE_Asset A

INNER JOIN PE_System S

ON A.AssetSystemID = S.SystemID

GROUP BY S.SystemLineID

SELECT * FROM @tblALineID

Again, works fine in Query Analyizer, but not in xMII. I get the error:

"A SQL Error has occurred on query, The statement did not return a result set.."

Perhaps its the JDBC driver we are using? it's the "com.microsoft.sqlserver.jdbc.SQLServerDriver", not the "com.inet.tds.TdsDriver" driver

Edited by: Doug Holtke on May 9, 2008 7:44 PM

Former Member
0 Kudos

Hi,

To my Knowledge this problem is arising out of drivers. xMII drivers can handle single result set.

if you observe your insert and select both generate message like

"5 row(s) effected" this means twop result sets are generated. this was not a problem earlier as driver used for 2000/11.0 was able to find the result set as the output rows.

to get the correct result USE

SET NOCOUNT ON

after declaration part.

this should help.

Regards,

A Prashant Kiran