How To Page SQL Query Result Returned From FOR XML Statement?
Apr 25, 2006
Gurus.
I do not know if it is possible, but here is what I want to do.
I want to allow user to page the SQL result, so he could decides to
return from row 10 to row 20, or row 100 to 200, without returns the
whole resultset. Every time he sends another request, I do not mind to
hit the database again, (I do not want to cache the result in the
middle tier server, scalability issue), and I know that I could achieve
this with CURSOR, but unfortunately the FOR XML is not allowed in a
CURSOR statement .
(I know that I could achieve what I want to do by writing custom code
in the middle tier, but I just want to see if there is a way to do this
on the database side.)
Any comments & suggestion is greatly appreciated.
Thanks in advance.
Hi - I am pretty new to Reporting Services. I need to create a report where a single result row from the Select Statement populates an entire page of data. The regular grid or Matrix reports don't fit this need. Is there a simple way to do this?
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer. Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16). Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned. If someone is able to help me figure this out, I would appreciate it. 1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList); 13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
We have multiple employees assigned to the same assignment sometimes, and my query is skewed as that if there are 2 people assigned to the ssame assigment I can only see the amt of time worked for the 1st person. Â What should I do to remedy this?
Ouput I am receiving is
BadgeNum - Assignment -- FullName --TimeworkToday A2323 Â ---- Accident Reconstruction ---- Football Helmet ---- .50 AB12 ---- Accident Reconstruction ---- Brown Shoe ---- Null AB66 ----- Accident Reconstruction ---- Black Shoe ---- Null ABB28 ---- Accident Reconstruction ---- White Shoe ---- Null
--And now here it's a new assignment so timeworktoday is populating
Hi,I got another question here:I want to use query like this:SELECT * FROM (sp_lock AS T) WHERE objectID = ...The purpose is that I want to query the result set returned by sp_lockusing derived table, but it doesn't work. Why?Thanks,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Hi,I have a shopping cart and before each product is added to the cart it checks to see if the stock control is turned on if so then checks the quantity available.My problem is that when i run the query select [dbStatus] from InventoryControl where [ID]=1 to view the status it is returning the incorrect bit value for dbStatus.If "dbStatus" is 0 in the database it returns 0 which is correct, but if the "dbStatus" is 1 in the db it returns -1!! If i run the query in query analyser the correct values are returned. Any ideas???Thanks in Advance
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
In MySQL we use "SELECT (....) LIMIT 0, 10" to only return the first 0 to 10 records. Alternatively we could do "LIMIT 10, 20" to return the 10th to 20th records.
I am working on application developed in ASP.NET 2.0 that uses lots of stored procedures from the SQL Server 2000 backend database.When the app is under load you occasionally see incorrect data returned to the page after running stored procedures.
All the stored procedures have been tested and appear to be fine. it's as if under load the returned data from the stored procedures is getting 'mixed' with another concurrent session. Please let me know what could be the reason behind it
I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.
Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.
But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.
I have a novice question. How does one suppress result sets returned from a stored procedure?
I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.
Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.
The purpose of procA is to query several subsystems and identify which ones need attention.
Cursor While Loop exec @ProcName @ObjectName,@userID,@syncDate set @recs = @@rowcount;
Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.
Anyone out there have any thoughts on how this might be accomplished?
Dear Friends, I want your help to know the best way to cache the data of a dropdownbox returned by a SQL Query.(SELECT FIELD FROM DBO.TABLE) This data returned by database is used in more than one dropdown on multiple pages. I want to cache only the dropdown control, not the entire page. Which is the best way to do it? The data isn't refreshed often, probably once per month. Could you tell me the best way? THANKS
I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am struggling with syntax for an update statement. Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations] SET [HasHistory]= 1 FROM [Tbl_Relations] r INNER JOIN (SELECT RelationID FROM vw_Invoice_history GROUP BY RelationID ) inv ON r.RelationID= inv.RelationID
We have some SQL CLR code that was written to go get information from a web service...all works great and the CLR code does what it is supposed to. The problem is, it returns its success or failure status as a string text value (equivalent of a print statement I imagine) instead of as a result set or output parameter that i can check. In the proc I wrote to call the CLR stored proc I do some cleanup that i would like to rollback if the call to the web service that the CLR proc makes fails. Problem is, how can i get there. I have tried every way that I know and I can't seem to get that text into an object (temp table, variable whatever) that i can evaluate. Anyone have any suggestions?
I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.
I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.
ddave
SELECT Region = CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440, 478,571,572,610,1069) THEN 44 WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0 ELSE 999 END
have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?set ANSI_NULLS OFF set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[mainSearch] @startRowIndex int, @maximumRows int, @rowCount int out, @countedRow int, @QUERY nvarchar(400) AS SELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM ( SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,* FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where (_DELETIONSTATUS=0)
) AS RANKEDADDS
WHERE ROWRANK > @startRowIndex AND ROWRANK <= @startRowIndex + @maximumRows -1
if(@countedRow < 1) SET @rowCount = ( SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where _DELETIONSTATUS=0 ) else SET @rowCount = @countedRow
is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row. if (Reader.Read()){ while (Reader.Read()) { //Get data from rows }}
I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
What I would like to do is run a select statement, and then have the result of the select be used/run thru the same select again untill it hits a certain where clause.
This is for a where used search in our BOM (Bill of Material) table.
So for example
BOM 1
level 1 Item# 123 type Finished Good level 2 Item# 45 type subassembly level 3 Item# 56 type purchased item
BOM 2
level 1 Item# 678 type Finished Good level 2 Item# 78 type subassembly level 3 Item#56 type purchased item
So my input into the select statement would be Item# 56, it will then first find Item# 45 and Item# 78, but those I am not interested in. I want the select to then take Item#45 and Item#78 and run it thru the same select so that it will find Item#123 and Item#678.
The select will know to stop there since these are flagged as "Finished Good" in our system, so they will not be in any other BOM. This is what I would imagine being specified in the "where clause".
I can write the first part, where the select will find Item #45 and Item#78, but don't know how to get past that point.
I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.
I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.
I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.
I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)
Application Server: JBoss v 4.0.4 Driver : sql 2005 driver.
Hi,
I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?
used by: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2148) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63) ... 88 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2145) ... 97 more
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986 2nd result : 17037903 3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
Hi everyone,My brain refuses to remember the (undocumented?) stored procedure I'mthinking of. It takes at least two parameters: a sql statement toexecute, and a table name (or something of that nature).Then, for each value in the table, it executes the sql statement andpasses the value as a parameter.Can anybody refresh my memory? The functionality may be slighlydifferent than described, but the principle is the same. Thanks verymuch...-Joe
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation FROM Invoice WHERE WorkStation Is Not Null ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber FROM Suppliers WHERE AccountNumber Is Not Null ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info: --- select @@Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) --- dbcc checkdb Abridged result: CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'. --- SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation FROM #Inv WHERE WorkStation Is Not Null ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
