on 05-09-2008 4:45 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
9 | |
7 | |
3 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.