Interesting SQL Problem : How To Track Movement History
Apr 20, 2007
Hello everyone,
There's an interesting SQL problem I've come across that I'm currently
banging my head against. Given the following table that contains item
location information populated every minute :
location_id date_created
=========== ============
5 2000-01-01 01:00 <-- Don't need
5 2000-01-01 01:01 <-- Don't need
5 2000-01-01 01:02 <-- Need
7 2000-01-01 01:03 <-- Don't need
7 2000-01-01 01:04 <-- Need
5 2000-01-01 01:05 <-- Need
2 2000-01-01 01:06 <-- Don't Need
2 2000-01-01 01:07 <-- Need
7 2000-01-01 01:08 <-- Need
how would you generate a result-set that returns the item's location
history *without* duplicating the same location if the item has been
sitting in the same room for a while. For example, the result set
should look like the following :
location_id date_created
=========== ============
5 2000-01-01 01:02
7 2000-01-01 01:04
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08
This is turning out to be a finger twister and I'm not sure if it
could be done in SQL; I may have to resort to writing a stored-proc.
Regards,
Anthony
View 9 Replies
ADVERTISEMENT
Nov 30, 1998
I have a table named sales, num of rows are 20. total number of rows for stor_id is 5 rows
I assume that when I create a cursor like this one which will have 5 rows from the cursor Now if I print a statement , I assume that I will have the statement 5 times( one for each row) Am I correct...
well, if this is the concept, I am still having one printed statement.. I do not know why , am I doing something wrong?
thanks for help
CREATE PROCEDURE p_cursor @ord_nbr char(4)
declare rst cursor for
select * from sales
where stor_id= 123
open rst
fetch rst
if (@@fetch_status=0)
print ' I may have 5 rows '
close rst
deallocate rst
View 3 Replies
View Related
Aug 21, 2006
We're very interested in having our application use SQL/e but we can't have the 4 gig limit. It makes sense to me that SQL/e simply should not be able to access a file over the network, and then you wouldn't have any reason to put a 4 gig limit on it.
At that point it becomes a very flexible alternative for remote users that need to have a large amount of data (i.e. documents, images etc.) with them.
We'd love to start building an abstraction layer so that we can support both SQL Server and SQL/e so that we can support network and remote users and not have the nightmare that is SQL Server Express installation. (care of the windows installer group's bugs...)
Thanks! Hoping for a favourable answer!
View 3 Replies
View Related
Jan 22, 2014
I'm moving set of data from one partition to another what is the best way.
what all the things need to be considered
Note: The set of data will be all from one partition to another one partition
My current query:
UPDATEtable1
SET table1.partitioncolumn = @newpartitioncolumn
FROMtable1
INNER JOIN table2
ON table1.id = table1.id
AND table1.partitioncolumn = @oldpartitioncolumn
View 7 Replies
View Related
Mar 28, 2006
Hi all
I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.
I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:
1) It runs in a tight loop and consequently degrades system performance enormously.
2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.
Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.
All help is appreciated.
Jays :-)
View 2 Replies
View Related
Apr 23, 2015
I have transactional replication setup from server A to Server B. I wanted to move the subscriber from B to C. What could be the best approach.
1. Backup the DB from Server B and restore on Server C. set the replication between A & C.
2. setup the transaction replication between A & C along between A & B. Test A& C working fine and then remove B.
If I am going with approach 2 , I have to replicate data approx. 70 GB so If I ran both the replication on Server A that will stress as 140 GB of data moving out. How do I control this large movement ? Can the replication be manual synch?
View 1 Replies
View Related
May 28, 2015
The secondary server for my availability group was recycled. When SQL Server came back online the data movement for a database was suspended. The error log shows:
"AlwaysOn Availability Groups data movement for database 'XXXXXXXXX' has been suspended for the following reason: "system" (Source ID 5; Source string: 'SUSPEND_FROM_RESTART'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online."
I was able to resume data movement with no issue. I would like to understand the technical reason as to WHY the data movement was put in the suspended state and left there upon recycle. I searched for an article that would list possible reasons (BOL, Google, Bing, etc..). I just could not find much information out there on 'SUSPEND_FROM_RESTART'.
View 2 Replies
View Related
Oct 5, 2006
Hi All,
I have SQL server 2005 Database which is having following Replication stuff.
1. 6 merge Subscribers
2. 5 Snapshot Subscribers (Push Susbribers)
3. 3 Transactional Publisher
Due to the Performance Issue, there is need to move SQL server from the Current Server to an Higher End Server.
I want to keep all the Replication settings after movement of the Database. Can anyone tell me how to acheive this requirement?
Is there a possibility to keep the Replication settings ? Even we can have the Same System Name to the New Server.
Awaiting response...
Thanks,
Thams.
View 1 Replies
View Related
Feb 13, 2015
I am aware that TDE protects data at Rest and not during communication or data in motion (UNLESS you use Encrypted communication channels using SSL certs etc). Hence I am thinking of doing data export from a TDE encrypted database to a database on the instance where TDE is not enabled or supported. I believe it works and need to take care of relationships between tables.The target database is hosted on SQL 2012 standard edition on which TDE is not supported.
View 4 Replies
View Related
Aug 24, 2007
Hi all,
I'm getting this error on expanding the Databases node in SQL Server Management Studio Express Object Explorer:
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)
A similar error message also appeared when I tried opening an existing data connection within Database Explorer in Visual Web Developer Express. (However, the web application ran fine and it managed to access the database normally.)
These errors only appeared recently. Any ideas how to go about solving this issue?
Thanks in advance.
View 1 Replies
View Related
Mar 5, 2008
Peace be on you,
In my organization, we have a database which exceed unto 2 GB. The application on which we are working is developing since the time of asp / cb 6 and in short it is the baby of many developers. It is a kind of ERP which has no documentation.
Problem 1:
=========
Now, to reduce the size of the database we have examined some of the orphan procedures, tables, and columns. Is it possible for me to create a DTS package which create the backup of all the orphan stuff to some other database and delete it from the actual one. Moreover, We still have doubts that some of fields which we think are orphan, might not be. So we need another DTS package which rollback all the changes. (Any Idea for that)
Problem 2:
=========
We want another DTS package which would move the data from one database to another and same rollback DTS for this one. (Any Idea for that)
Problem 3:
=========
How can we use DTS programming to do these tasks ? what benefits do I got from DTS Programming over DTS Wizard.
Any help would be greatly appreciated.
View 4 Replies
View Related
Mar 20, 2008
We have a stored procedure that failed with: Could not continue scan with NOLOCK due to data movement
We are running with ISOLATION LEVEL READ UNCOMMITTED. There are other jobs running, some of which might be hitting these tables (all using the ROWLOCK hint - though I know that's not guaranteed), however, this stored proc would not be going near the same rows. But even if they were, we'd be happy with either the before-look or after-look. This needs to be a low-impact job and should have minimal impact on ther other jobs, so we can't take out locks. Is there any hint we can use to do this? e.g. can we tell the query to just wait until the data has stopped moving then try again?
View 3 Replies
View Related
Jun 12, 2004
Below is the statement given in MicroSoft SQL Server migration documentation :
"When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ. In most cases, Oracle views are easily translated into SQL Server views"
Please can anyone explain the above with some examples. I don't find such a 'CREATE VIEW' statement existsing in two DBs, which results in different result set. Am I wrong ?
Thanks,
Sam
View 2 Replies
View Related
Aug 26, 2004
Another tech was having the same problem with his sp which he also made into a macro. He was having the same problem as I was, he thought since he made the user the dbo owner that he woud have no problems...but since hes the one who created the db and the stored procedures when the user tried to access it he got that same error message. It went away after he went into the Enterprise manager and in the properties of the stored procedure under permission he gave the user EXEC permissions...And WAAALLLAAA problem solved :):):):)
View 10 Replies
View Related
Oct 22, 2004
Anyone else experience this? A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second. SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co, dbo.v_contract_co_statusWHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id ) AND contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co INNER JOIN dbo.v_contract_co_status ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_idWHERE contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )
View 3 Replies
View Related
Feb 1, 2007
Hi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID'sFor example;Table "Sales"ID - IntRemoteID - IntVarious Data fieldsThe 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.So i have been considering two possibilities to address this problem,1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.2. Having a controlling ID table which provides the next IDBoth options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem? Thanks in advance!
View 2 Replies
View Related
Jan 3, 2001
We've had an interesting server issue here since about the start of the year, where the dual processor Netfinity server we've been running SQL 7 on has been holding steady at almost exactly 50% CPU utilization, give or take about 5%-10%. Disk utilization in all areas is relatively low. In fact, the server will do this right after a fresh reboot, with no users accessing. The really interesting part is that a graph of the two processors mirror each other. When the utilization of one goes up, the other goes down, almost perfectly. The two processors are the same speed, but their firmware build is different. IBM says it's not a problem. What say ye?
View 1 Replies
View Related
Nov 28, 2001
If Query1 gives 1 row output as:
customer_id
1234
-------------------
Query2 gives 1 row output as:
product_id
8970
-------------------
How do we get the above results as a single result set of 2 columns side by side? as follows:
customer_id product_id
1234 8970
Is that possible in a single select statement??
Query1 and Query2 both are select statements on the same table with different where clauses.
Sheila.
View 2 Replies
View Related
Jul 14, 1999
Hi ! I am facing an interesting problem for one of my databases. When I double click on a particular table in Enterprise Manager, the Manage Tables window shows me only one or two fields from the table. (it does not show me all the fields). When I run sp_help <tablename> in isql, all the information is displayed. Why is there such inconsistency. It happens only on one particular database. I have run DBCC commands but still no help. Does anyone have any ideas on this. Thankx,
View 3 Replies
View Related
Jul 12, 2005
We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
View 9 Replies
View Related
Jun 8, 2004
Hi Folks,
This is a create table statement in DB2 z/ODS and OS/390 ( mainframe versions ) :
create table test(sno int) IN DATABASE database1 AUDIT NONE DATA CAPTURE NONE
This will create a table in the named database 'database1'.
1) In SQL Server, is there any equivalent for this 'IN DATABASE' syntax in DB2 ?
2) 'Data capture none' will not store any extra info about the data replication in the log files. Is there any eqiuivalent for this in SQL Server ?
Thanks,
Sam
View 1 Replies
View Related
Jan 10, 2008
In this SQL query I get en error like this:Incorrect syntax near ','.SELECT SUM(mCount * mPrice), CONVERT(DATEPART(yyyy, mDate), varchar) + '.' + CONVERT(DATEPART(mm, mDate), varchar) AS DateSoldFROM salesWHERE mDate BETWEEN '9/10/2007 0:0:0' AND '1/10/2008 23:59:59'I think you it isnt necessary to show values in the table because it is a syntax error but I dont undertand how it can be incorrect...Thanks for help...Saren Taşçıyan
View 14 Replies
View Related
Jul 23, 2005
Imagine a table in Microsoft Sql Server 2000 named Pictures with threefields. A primary key ID(int), a Name(nvarchar) and a Picture(image)field.Lets put some records into the table. The first two fields as expectedwould take an int and a string. The third field is of type image.Instead of putting a bitmap in this field lets place an xml documentthat has been streamed into a byte array. The xml document woulddescribe the Picture using say lines. If the picture was that of asquare we would have four lines in the xml document. You could think ofthe xml document as something similar to vector graphics but thedetails are not relevant. The important fact is that the contents ofthe image field is NOT a bitmap but a binary stream of an xml document.Now imagine we have a reporting tool like Crystal Reports that can beused to report on this database table. Imagine we create a report byusing the three fields mentioned above. As far as Crystal is concernedthe first field is an int, the second a string and the third an image.If our table had ten records and we preview the report we like to see10 entries each consisting of an ID, Name and a Picture.This can only happen if the image field contains a Bitmap, but asmentioned above the field contains an xml document.Now my question...Can we write something in SQL Server 2000 (not 2005) to sit between thetable and Crystal Reports so to convert the XML document to a bitmap.The restriction is that we cannot use anything but sql server itself.The client in the above case has been Crystal Reports but it could beanything.I know SQL Server 2005 supports C# with access to the .NET frameworkwithin the database. Unfortunately, I am not using SQL server 2005.Some people have suggested the use of User Defined Functions and TSQL.I like to know from the more experienced SQL Server people if what I amtrying to achieve is possible. Maybe it has not been done but is itpossible?Any suggestions would be greatly appreciated...Many Regards
View 5 Replies
View Related
Sep 20, 2007
Hi All,
When I wanted to see ONLY today€™s records on the sql server, it took 5 minutes.
When I wanted to see records including today€™s records on the sql server, no problem. (19 Sep. is a past date which is working properly)
It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.
I ran DBCC INDEXDEFRAG method but no change anything.
I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?
It is really interesting issue. I do not know what I can say. It seems there is an issue with today€™s records. May be there are some locks on the today€™s records. I do not know. Why does not cause any issue for old dates but for current day ?
SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration
FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo
WHERE CallIDChar = CallIDChar
AND (CallStartTimeStamp >= '2007-09-20T00:00:00')
AND (CallStartTimeStamp <= '2007-09-20T23:59:59')
What can be reason for this issue and how can I solve it ?
Thank you in advance,
Melih
View 15 Replies
View Related
Apr 12, 2007
I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!
How can this happen?
I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.
Thanks.
View 11 Replies
View Related
Aug 2, 2007
I am making a report in Visual Studio..Now my question is,is there anyway that i can add a tab control for my rdl??Hope to here from anyone out there with a know how..Thanks guys!!
View 13 Replies
View Related
Jun 23, 2007
Having no more experience than reading books online, here is an interesting idea I would like to run by you guys and you can let me know if it is feasible or tell me I need to put the crack pipe down...
We are going to increase the number of disks in our SAN, and I was speaking with the SAN administrator and he mentioned the shuffling of logical drives to match the new space. He said he is going to have to go through quite a few combinations/permutations on figuring out the best configuration for what data goes on the old vs. new to get the optimal space.
Is this something that can be modeled out? I can write something that recursively figures it out, but why not explore fun ideas with tools that may be able to do it?
Thank you in advance,
John Hennesey
View 3 Replies
View Related
May 26, 2008
All of you just read this article
http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx
View 1 Replies
View Related
Sep 1, 2004
Using Join when 2 columns in one table point to 1 column in another table
I have spent hours on this problem and no research has turned in my favour. Does anyone have any examples they can put forward for me.
I am really desperate to sort this out, and any help will be greatly appreciated.
Thanks
Shaun
View 1 Replies
View Related
May 25, 2006
Hello,
In the code SqlParameter dtDate = new SqlParameter("@date",SqlDbType.SmallDateTime,4);
dtDate.Value=Convert.ToDateTime(Session["date"].ToString());
cmd.Parameters.Add(dtDate);where the @date parameter is defined as a SMALLDATETIME inside a stored procedure, I cannot use a function such as "Convert.ToSmallDateTime" and it seems a conversion from DateTime to SmallDateTime takes place anywhere within the code. Eventually it works but I wonder if everything's ok with this code. Is there anything wrong about it? Thanks
View 2 Replies
View Related
Jan 23, 2002
I have an interesting request.
I would like to take a table such as:
date value
----- -----
1/1/01 10.5
1/2/01 9.3
1/3/01 11.5
1/4/01 4.7
1/5/01 3.2
and calculate an average for each record, based on its own value AND the following two values. In other words, for the first record, I would take
sum(10.5 + 9.3 + 11.5)/3 and put this into a third column in my table. I would then do the same for the next record: sum(9.3 + 11.5 + 4.7)/3.
Thanks in advance!
BV
Does anyone know how to do this in SQl.
View 2 Replies
View Related
Dec 8, 2004
Run the following: declare @s1 varchar(10), @s2 varchar(10) declare @t table( recordid int identity(1,1)not null,field char(1)not null) insert @t (field) select 'a' union select 'b'select @s1 = '', @s2 = ''select @s1 = @s1 + field +',' from @t order by 1select @s2 = @s2 + field +','from @t order by recordidselect[Weird]=substring(@s1, 1,(datalength(@s1)-1)),[Not]=substring(@s2, 1,(datalength(@s2)-1))
View 2 Replies
View Related
Oct 2, 2007
The value in the table of one DB is 17869 sq. ft. Now to insert this value in a new table of other database the reporting basis is 1000 i.e I need to do 17869/1000 = 17.8 so I have to take the value as 18. Another thing to be kept in mind is the value in the new table should have leading Zeroes. If the value is 18 it should be displayed as 0000018 ( data type in new table is Varchar(7) and in old table char (9) ). What can be the best way to implement this??
View 8 Replies
View Related
