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
---------------------------
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 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 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...
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
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?
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table.  So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy] EXCEPT SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
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
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 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.
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.
