ID DeviceName Status
1 Sony Good
2 Toshiba OK
3 Sony Bad
4 Tata OK
I need to return the following records
ID DeviceName Status
2 Toshiba OK
3 Sony Bad
4 Tata OK
If there are more than one record for the Device, then record with the latest ID should be returned.
If there is only one record for the Device, then that record should be returned.
Can this be achieved through a single query.
Any help is appreciated.
Hi, I get 5000 records on executing a query. In the form, I would like to display 50 records at a time in the grid from this resulted query, so i create 100 link labels(dynamically created based on the no. of records resulted from the query) within a panel.
When clicked on link1, first 50 records should be displayed,on clicking link2 next 50 records(ie, 51 to 100) should be displayed and so on. So , i wrote the query as---
select top(50)* from tblindividual where id not in (select top(intValue)id from tblindividual )
where intValue would be 50 for link2,100 for link3,150 for link4 and so on
If i want to fetch last 50 records of the query, intValue would be 4950.Here the "not in" list becomes very big(1,2,3,.......,4949,4950) and hence the query is becoming a bit slow...
Is there any other method(query) to get the same result??.. because i heard that using "not in" keyword would make query execution slow.
CREATE TABLE [dbo].[DR_Test]( [source_item_id] [int] NOT NULL, [source_line_no] [int] NULL, [buyer_id] [int] NOT NULL, [seller_member_id] [int] NULL,
[code]...
the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique.how to fetch the data fast or need to change the structure of the table
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID 2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result 2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"." Here is my Query, sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go reconfigure SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Hi all!I'm trying to write a T-SQL statement that will allow me to do a maintenance for all user table for all databases on my server.This is what I got so far :DECLARE @cStatement varchar(255)DECLARE @dStatement varchar(255)DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6SET nocount ONOPEN T_databaseFETCH NEXT FROM T_database INTO @dStatement WHILE (@@FETCH_STATUS <> -1)begin-- LOOP IMBRIQUÉ POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS-- DE LA BASE DE DONNÉES ACTUELLE.EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ['' + CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')OPEN T_cursorFETCH NEXT FROM T_curosr INTO @cStatementWHILE (@@FETCH_STATUS <> -1)beginPRINT(@cStatement)FETCH NEXT FROM T_cursor INTO @cStatementendDEALLOCATE T_cursorFETCH NEXT FROM T_database INTO @dStatement endDEALLOCATE T_databaseBut I get a sh**t load of errors :Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Please help!!! :beer:
I would like to know that, I have three instances of the same database at three different servers and I am trying to fetch the data from the select query. "select * from table_name"
I would like to know, whether the order of rows fetched by this query will be different on different servers of sql server or the same order of rows will be fetched.
For me the output is coming different on each server database with he same query . Pls let me know, is there any default order by or it takes it randomly.
i have a task that has a ole-db destination. it inserts some data to a table. it's always 1 record that is gonna be inserted.after that the task is finished. however i want to use that same record in the followup-task. is there a way to retrieve the id of that specific record.
Please note that the id of the record is an int-identity from sql-server itself.
at the moment i'm fiddling with "remembering" the inserted data and hopefully it will be a 100%match in the next task, but i have a dark feeling it's not 100%.
I'm currently developing an application using C# and SQL Server Express, in which one of the functionalities is to allow the user to browse through the existing products, wether by browsing one by one (next, previous, first, last) or performing a search (by product name, id, etc.). I'm currently using the default DataSource and TableAdapter aproach, but I found out that when I execute the TableAdapter.Fill() method, all the data in the table is fetched... what with 10,000 records it takes a bit to perform, specially on slower connections.
So my doubt is, what would be the best aproach to solve this problem? I'm considering loading first position, and then when the user presses the next button I'd fetch second position, etc, this using ROW_NUMBER(). I could even cache every other 10 records or something like that. Since it is possible to know any records row number (as long as the records are ordered) it would even work when the user performs a search and goes directly into row number 5000 for instance.
Would this aproach be a good practice, because that's all I want, to do things the right way? Thank you for your time
EDIT: I've noticed that when creating a SqlDataReader there is the option to provide a CommandBehavior, but I'm not quite undestanding how this could be used, would some tweaking in this area do the job?
Hi, I have a small Problem. Lets say i have 6 rows in a table (two column month and MonthCost )naming jan,feb,mar,apr,may.jun and all of them have some data. but while displaying in Ui i need to show all the months from Jan to december and july to december with values as something, say 0 so the values displayed in gridview sholud be like this Month MontCost Jan 1 Feb 1 Mar 1 . . . Jul 0 Dec 0 . I tried it can be done using a temp table in db , but think of a scenaio where the i want to display for Number of times( 1 to 12 * 50). I don't want to hit performance and i am uisng sql server 2000. Any hints or suggestions are welcomed.
set dateformat dmycreate table tbl_sampemptable(employeeid int,StartDate datetime) declare @employeeid intset @employeeid=1declare @startdate datetime,@enddate datetimewhile(@employeeid<=1000)begin set @startdate='01/05/2008' set @enddate='31/05/2008' while(@startdate<=@enddate) begin if(@employeeid<>1 and @startdate<>'02/05/2008') insert into tbl_sampemptable values (@employeeid,@startdate) else if(@employeeid=1) insert into tbl_sampemptable values (@employeeid,@startdate)z set @startdate=dateadd(day,1,@startdate) endset @employeeid=@employeeid+1end select * from tbl_sampemptabledrop table tbl_sampemptableset dateformat mdy
i have to select records depending on @count parameter to this table.Depending on this parameter value it should fetch sequential dates.For example if @count=2then result should be like this,
EmployeeID FromDate ToDate 1 01/05/2008 02/05/2008 1 03/05/2008 04/05/2008 . . 2 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected because 02/05/2008 is missing 2 05/05/2008 06/05/2008 . . 3 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected because 02/05/2008 is missing 3 05/05/2008 06/05/2008 . .
if @count=3 then result should be like this,
EmployeeID FromDate ToDate 1 01/05/2008 03/05/2008 1 04/05/2008 06/05/2008 . . 2 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected because 02/05/2008 is missing 2 06/05/2008 08/05/2008 . . 3 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected because 02/05/2008 is missing 3 06/05/2008 08/05/2008 . . how can i do this.please help me.thanks in advance
hello i'm using visual studio 2005 and asp.net 2 i have an error in the code that fetching data from databse in grid here is the code :Dim con As SqlConnection con = New SqlConnection("Data Source=local;AttachDbFilename='D:New Folderhorushorus.mdf';Integrated Security=True") Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))") Dim cd As SqlDataAdapter cd = New SqlDataAdapter(cmd, con) Dim ds As New DataSet() cd.Fill(ds, "students") grid.DataSource = ds.Tables("students").DefaultView grid.DataBind()that code is selecting random records from the database and showing it in grid the error is in the cd.fill(ds,"students") line so can any one help me in that problem.thanks
hello, i'm using asp.net 2 with VB, i have a table in my db named 'exams' and i have 6 columns in this table one for question number and one for the question and 3 columns for 3 answers and a last column for the right answer, now in my page i want to show 7 questions and each question has 3 answers that i can choose between them i want the question appear in datatable and the answers in radiolist i have a little code for that but it shows an error it says "no row at position 1" so hope u guys can help : Dim con As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|aspnetdb.mdf;Integrated Security=True;User Instance=True") Dim a As New ArrayList(8) Dim i As New Integer i = 0 Dim d As New DataSet() Dim cmd As New SqlDataAdapter("select top(7) question from exams", con) cmd.Fill(d, "exams") a.Add(d.Tables.Item(0, 1)) i = i + 1 Dim ta As New DataTable ta.Rows(i).Item(0).text = d.Tables(0).Rows(0)(i)and by the way this code is for the questions i still dont have the code for the answers that will appear in a radiolist hope u can help .thanks
Hi, I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.
Below is the error that I get when tryong to open the record from SSMS
SQL Execution Error.
Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader Error Source: .Net SqlClient Data Provider Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. --------------------------- OK Help ---------------------------
I am getting problem in fetching data from SQL using C++ program. How to Fetch SQL Unicode characters? I am using ODBC driver calls in C++ to fetch SQL data. It is working fine with character data. But for Unicode data it displays “????? instead of data. Can anybody please help?
1.location id primary key authorization string count int 2.account acc_id primary key loc_id foreign key of location table hsr string 3.Line id primary key intaralata string interalat string 4.trunk id primary key intaralata string interalat string 5.RCF id primary key intaralata string interalat string 6.TSG id primary key intaralata string interalat string
now i need to fetch the field authorization from location table by the following conditins
LOCATION.AUTHORIZATION using ACCOUNT.LOC_ID. If any (LINE/TRUNK/TSG/RCF.INTERLATA/INTRALATA= ’F’), and this value is blank, send LOCATION.AUTHORIZATION
can anyone help me out in this scenario. i need the sql query for fetching that fiels.
Hi,I have a table which contains a bunch of prepaid PINs. What is thebest way to fetch a unique pin from the table in a high-trafficenvironment with lots of concurrent requests?For example, my PINs table might look like this and contain thousandsof records:ID PIN ACQUIRED_BYDATE_ACQUIRED....100 1864678198101 7862517189102 6356178381....10 users request a pin at the same time. What is the easiest/best wayto ensure that the 10 users will get 10 different unacquired pins?Thanks for any help...
I want to fetch definitions for all stored procedures & views in a database. For stored procedures, I use sp_stored_procedures to retrieve all SPs & then use Procedure_Name column to fetch each SP's name followed by a call to OBJECT_DEFINITION to retrive its definition.
Now, I have found 'sp_views_rowset2' that returns all views. As I am not very sure about it, this is just for confirming that the task of 'sp_views_rowset2' is actually to return all views defined for the database!!!
Hi i am firing a query in SQL SERVER CE as given below
SELECT Ticker_Master.Ticker, Ticker_Master.Company_Name, Capital.DataSet_Date, Capital.Quick_Ratio, Dividend.Payout FROM Ticker_Master INNER JOIN Capital ON Ticker_Master.Ticker = Capital.Ticker INNER JOIN Dividend ON Capital.Ticker = Dividend.Ticker AND Capital.DataSet_Date = Dividend.DataSet_Date AND Capital.Region = Dividend.Region WHERE (Capital.Quick_Ratio = @PARAM1) AND (Capital.DataSet_Date BETWEEN @PARAM3 AND @PARAM4) AND (Capital.Region = @PARAM5) OR (Capital.DataSet_Date BETWEEN @PARAM3 AND @PARAM4) AND (Capital.Region = @PARAM5) AND (Dividend.Payout = @PARAM2)
On execution is asks 4 the parameter values as follows : @PARAM1 @PARAM3 @PARAM4 @PARAM5 @PARAM2
After values are given on execution it throws error as Duplicated parameter names are not allowed. [Parameter Name = @PARAM3]
Can anybody tell me what's wrong ?????????????????
I am getting an error while fetching data from IBM DB2: I am using an OLE DB Connection provider - "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider".
I am also setting the "Force Translate" value in "Data Link Properties" of the Connection to "37" because I am getting some BYTE_STREAM data which I need to translate.
I am using the OLE DB Source to extract data using a SQL Query. I can see the data correctly when I do a "Preview".
However, when I execute the task, I get the following error. It will be great if someone can help me with this.
Error: 0xC0202009 at Customer Telephone, CUSTOMER_TELEPHONE Source [238]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at Customer Telephone, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CUSTOMER_TELEPHONE Source" (238) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Customer Telephone, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Customer Telephone, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Customer Telephone, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?
The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.
(there is some cookie management also required to logon successfully into the web services).
Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?
Hi there, I'm using a query to fetch data from a table where one of the criteria is IN(...) clause for the key column of the table.Now the data being retrieved is ordered by the key column of the table even though I haven't specified any order by clause. I want to know if there a way in which the data being fetched is in the order of my IN(...) clause.
Hello folks, I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.
Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends........:eek: :eek: :eek:
I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particular pagename which is stored in a table with sorting of dateadded
like my products table structure
id iQty strProductTitle strPageName DtAdded DtApproved
I am trying to fetch data from IBM DB2 to SQL Server 2005. The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data. The datatype of the original field is "Byte Stream". I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.
I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text". When I check this box, I am able to see the data correctly.
But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.
I would like to call a function in a calculated field like =code.GetNHW(Fields!id1.Value,Fields!date1.Value) where the GetNHW() function would return a double value based on some conditions- The code I would like to write in Code window- Public Function GetNHW(ByVal ID As Integer, ByVal attDate As Date) As Double Dim dsDTConfig As New DataSet Dim NHW As Double dsDTConfig = GetDS("tblEmployee_DailyTimingsConfig", "id=" & ID & " and '" & attDate & "' between configStartDate and configExpiryDate", False) If dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = True Then NHW = 0.01 ElseIf dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = False Then NHW = 0.05 End If Return NHW End Function What I am looking for is - I would want to get the record(dataset) within the function from the database and based on the values got, I would check few conditions and calculate NHW.
I get the following error. Err.Description - "Rowset does not support fetching backward." Err. Number - -2147217884
Can somebody tell me the reason behind this error.
My Reasearch: I have got to know that I get this error in the line of code recordset.MoveLast in My code.
My Code environment: I have built an SQL Query string which calls the Strored procedure with one parameter (parameter tyoe is string type). I have attached the SP code below.
and SP contains the call for querying other database in the same server using OPENQUERY command.
and the VB code contains code as Recordset.MoveLast. (Error is generated here and Recordset.RecordCount is also -1)
AS BEGIN DECLARE @sSQL VARCHAR (1000) SET @sSQL = '''SELECT * FROM VW_JOKE WHERE JOKE_ID = ''' + '''' + @sRMS_Status + '''' + ''' ORDER BY JOKE_NO'' EXEC ( 'SELECT * FROM OPENQUERY(lnk_joke__cat,' + @sSQL + ')' ) END
My Questions: 1). Is there any problem in SP as I am using OpenQuery? I queries the SP and concluded that it generates result properly.
2). Is there any problem with the Recordset.MoveLast line? I am using adOpenKeyset, adLockReadOnly as parameters to my Recordset.Open command along with other parameters.
Let me know the corrective steps to be taken to get rid of this error.
Thanks in Advance for your valuable time Ranjan Jain
now this bat file only start the job, does not wait for it completion or send us the success or failure message.Now i want to check the value of LastRunStatus for this job using BAT file?? Is is possible to fetch the LastRunStatus of sql job using bat file.However i have achieved the same using vbscript that starts a job using vbscript start method and then looping through al the jobs at server and displaying the LastRunStatus. However i wanted to achieve this uisng bat file only.
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
