Return Top Row From A ResultSet - SQL Server (TOP)
Jul 7, 2006
Hi all,
I need to return only the top row of a ResultSet that is generated from my Query but can't seem to find anything in SQL Server 3.0 Mobile that allows me to do that. I know in SQL Server (Desktop) I could use the TOP keyword. The query I have is as follows, and just need the top row. Now the returned resultset will, in time, be massive, and there is just no way I can afford to return this amount of data in my application only to take to the top/first row. The query I have is as follows...
SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
ORDER BY DateCompleted DESC
(I want the last record that was inserted into the database)
Hello all, I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob @MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode FROM dbo.TJob INNER JOIN dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN WHERE (dbo.TJob.IJobN = @MyJobNo) GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion. What is the problem with the code? How do i display and return a resultset to my coldfusion client application? SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
ALTER PROCEDURE dbo.nTransaction (@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT) AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR -- FORWARD_ONLY STATIC SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate FROM entryheaders as Eh cross join entrys as E WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo ORDER BY Eh.EntryID DESC SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @ntransCursor into @nValue
END CLOSE @ntransCursor DEALLOCATE @ntransCursor;
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code: while (@I <= @count) begin BEGIN TRY -- delete all previous rows inserted in @customerRow for previous counts @I delete from @customerRow -- this is inserting the current row that we want to save in database insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code: begin catch IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorLine INT; SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
If you write a query that outputs XML, you'll have a long string in the Results window/tab that is also a link. Clicking on this link opens a new tab in SSMS with the formatted XML string easier to read.
My issue is that clicking on the link opens the "formatted XML string" tab ON THE SAME tab as my code and no way to go back to my code after that ... I need to reload the query from disk, if saved, to erase the "formatted XML" window.
I have a program that I'm working on that utilizes a JDBC connection to a SQL 2005 server. A result set is generated from a legacy system. The data that is pulled into this result set is in the same layout as a table in SQL 2005 server.
My question is, once I have good data loaded into the ResultSet object can I pass it to the JDBC to load it into the table?
Or do I have to use the ResultSet.get(string) to pull out each value row by row, populate a string, and then use the string in an insert statement that I pass to JDBC?
Are there tricks or optimizations I can do for this type of JDBC transfer?
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp CREATE TABLE #temp (Command varchar(8000)) INSERT INTO #temp SELECT 'INSERT INTO Test1(column1,column2)values(1,2)' UNION ALL SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13 Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
Using VS 2008 I got a Windows Smart Device project targeting WM6 Standard and using .NET CF 3.5. I add a new database file = creating an empty Compact 3.5 database (creates an sdf-file in my project). Then create an empty dataset in the wizard (creates an xsd-file in my project). Then I add a couple of tables in the database. After that I want to use my earlier created dataset. I mark the xsd-file and looks in Poperties. I get the error when I try to change Custom Tool value from 'MSDataSetGenerator' to 'MSResultSetGenerator'. Why????
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
How much performance hit should I expect to use FOR XML to generate XML string directly from SQL Server 2000? I like this approach because it is simple and easy. However, others told me that it has a big performance hit. They prefer to use a COM+ component to create XML string from a Resultset. Any suggestions? Thanks.
Hi,I have 3 tables as follow :Kanji :kanji_id....References :ref_id....KanjiRefskref_idkanjikref_idrefkref_value....So, there is a many-to-many relationship between Kanjis and References(one kanji may have more than one reference type, and a reference typemay be set to more than one kanji).For example, one kanji may have the value 'abc' for reference type #1,and the value 'def' for reference type #2, another kanji may also havethe reference type #1, but have instead the value '123' and so on...I have two questions :1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)within their list of references?2) How to get the value of all the kanjis that have the reference #3,but still get other kanjis that do not have the reference #3...forexample, if I had 3 kanjis, with the two first having values 'abc' and'def' for reference #3, and the last one having no reference #3, I'dlike to get that resultset :kanji_id kref_value1 'abc'2 'def'3 NULLI manage to get all the kanjis that have reference # 3 with thefollowing query :SELECT kanji_id, kref_valueFROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_idWHERE kref_idref = 3however, this obviously does not include kanjis having no reference#3...any help would be greatly appreciated, thanks! :)ibiza
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
Hi I have 2 Sql statements within a stored procedure.But second one crashes my app - I'm getting object not found I need some help when writing several querys into the same sp, do I have to define some thing special? thanks
I am trying to write a select statement that will return the values of two columns from two different tables in the same statement. I believe the join word needs to be used somewhere, but I am having difficulty with it.
Table1 (table name) Column1 (column name) ------ a b
Table2 (table name) Column2 (column name) ------ c d
Hi,This is what I want to do. For a data acquisition query, if conditionA is met, set weight to 1, else if condition B is met, set weight to 2etc.Is this possible? I thought about using CASE function but to noavail.Using the classical Northwind db as target db, and its employees tableas target table, if [note] column contains 'BA', then I want to setweight to 1, else if it contains 'BTS', then I want to set weight to 2etc. Weight or the like is an auxillary artificially created column.Something likeDECLARE @col intselect firstname, lastname, @col =(CASE noteswhen '%BA%' then 1when '%BTS%' then 2else 0END as aiCOL)-- and I'd like to alias @col as aiCOL or whateverfrom employeeswhere notes LIKE'%BA%'ORnotes LIKE '%BTS%'failed.Thanks.
According to the BOL documenation of the ExecuteSQLTask you can populate a variable with a resultset. I specified a variable of type 'Object' and followed the instructions and everything is fine.
Now I have TWO issues.
1) I want to filter some unwanted rows from this variable and add into another recodset. For filtering i am using foreach loop and putting condition on variable. It works fine till here, but how to update another 'Object' type variable.
2) How to make the result set as source in a data flow task. I want the filtered result set to become source in a data flow
I am using the SQL Server 2005 JDBC driver. I need to write a piece of code that makes use of the streaming ResultSet. That is as soon as I get the first row, a worker thread should be able to begin processing on it without waiting for the second row to arrive. When the second row arrives, the second worker thread should start processing on this new row without waiting for the third row to arrive and so on. Usually, with a ResultSet, I need to wait for all the rows to arrive first before I can start navigating the rows in the ResultSet. But in my code, I need to start navigating the ResultSet even as more rows are pouring in from the DBServer. How can I do that? Any pointers in this direction will be helpful. Further, I want to know will setfetchsize be of any help here? If I set the setfetchsize value to 1, does that mean that as soon as I get the first row, I can start working on this row of the resultset(viz start navigating the ResultSet) without waiting for the second row to arrive in the ResultSet?
Hi, I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing. Anyone got any suggestions to why? web.config: <!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;" providerName="System.Data.SqlClient" /> --> <add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;" providerName="System.Data.SqlClient" /> default.aspx.cs dbConnection.Open(); String ISPQuery = "SELECT STATEMENT"; SqlCommand command = new SqlCommand(ISPQuery, dbConnection); SqlDataReader reader = command.ExecuteReader();
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
I am having some problems exporting to Excel using SQL 7.
I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata ----------- ----------------- person1 data person1 more data person1 other data person2 stuff person2 more stuff person2 even more stuff person2 lots of stuff person2 last bit of stuff person3 info person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata -- ----------- ----------------- 1 person1 data 2 person1 more data 3 person1 other data 1 person2 stuff 2 person2 more stuff 3 person2 even more stuff 4 person2 lots of stuff 5 person2 last bit of stuff 1 person3 info 2 person3 more info
I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.
But duplicate records are being returned in the resultset. How do I eliminate duplicate records?
This is my query.
SELECT table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
I tried doing DISTINCT here but with no success.
SQL with distinct clause.
SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
This is the error I get: The text data type cannot be selected as DISTINCT because it is not comparable
I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables.
Code: With CTESource(Total, ID, Source, Program) AS ( SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
[code]....
The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.
