How data can remove automatic from tempdb after some task

Question

How data can remove automatic from tempdb after some task

motanihatim110

SSC Rookie

Points: 32
More actions
December 29, 2008 at 4:52 pm

#203382

Hi Guys,
Please dont laugh as I am new to SQL. I am working with Microsoft Dynamics GP.
I am using eConnect which read data from tempdb (someone will drop data in tempdb) once in a day, now my problem is what will happen next day when eConnect again read data from tempdb if previous day data has not moved so I am wondering how to remove data from one table to another after eConnect read data from tempdb.
Thanks,
Hatim

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004475 More actions · Answer 1

Without more information, I'd have to say "like you would anywhere else". Insert from one table to the other using Insert/Select... then, delete the rows you just moved from the source table. If it's a move of all rows, then you could use TRUNCATE instead of Delete.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 2

Yeah, I'd like to see some information too. What the heck are you moving data into tempdb for? SQL Server puts enough of a load onto the tempdb without adding to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

motanihatim110 SSC Rookie Points: 32 More actions · Answer 3

Hi,

I think I didnt provide enough information.

let me try to explain what I am doing.

We are debiting our customer every month fixed montly amount based on our agreement with customer.

We are getting daily report from our service provider in excel which shows how much money received from each customer and based on this report I prepare cash receipts in Micrsoft Dynamics GP using eConnect (which is also Micorsoft tool) which read data from excel and after reading it, excel file move into another folder so it dont read again same file. I have put eConnect on schedule so it read data once in a day. There is option in eConnect which allow to move file in another folder after creating cash receipts. This works perfectly without any problem since many years. Now we are changing our service provider hence requires some changes in our system. Here my problem starts as one guy will drop data in tempdb instead excel file and eConnect will read data from tempdb instead excel. Now eConnect does not provide any option to move data from tempdb so is there anyway to move data from one table to another table which trigger once eConnect read and make receipts.

Thanks

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 4

Putting data into the tempdb is quite a lot different than putting data into an Excel file. I guess I'm still going to need more information here.

I just glanced through the documentation on eConnect. It's going to require an explicit connection to get to the tempdb as opposed to Excel. It sounds like someone made a mistake in their code. From what you've said, I suspect you're having issues with one connection posting into tempdb and a different connection trying to read the stuff out of it. Anything you post into tempdb is not like an Excel file. There is not need to "move" it to another folder. You can simply drop the table after you move the data you want, or if the table has to stay in place, you delete the data you moved after you're done with it.

But honestly, I'm still a bit confused as to what the explicit issue is.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

motanihatim110 SSC Rookie Points: 32 More actions · Answer 5

Hi,

Thanks for reply.

It is not actually eConnect but modified version (smart connect which is eonesolution product and that is also modified by eonesolution for us). I dont know how other guy will put data into tempdb as it is his job. I dont want to delete data from table but move into another table so we can verify in future if requires.

Thanks,

Hatim

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 6

I dont want to delete data from table but move into another table so we can verify in future if requires.

Then I suggest you do not use tempdb to start with. Create a proper audit trail, containing not only the data you require, but also date/time of when it was uploaded, when it was incorporated into the main data set, etc, etc.

Andy

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 7

motanihatim110 (1/4/2009)
Hi,
Thanks for reply.
It is not actually eConnect but modified version (smart connect which is eonesolution product and that is also modified by eonesolution for us). I dont know how other guy will put data into tempdb as it is his job. I dont want to delete data from table but move into another table so we can verify in future if requires.
Thanks,
Hatim

Moving data from one table to another is pretty simply a matter of writing TSQL code that pulls from one table and inserts into the other:

INSERT INTO TableB

(Col1

,Col2)

SELECT Col1

Col2

FROM TableA

That'll do it. You just need to deal with the fact that since you're loading this data into tempdb, where it shouldn't go, you may not be able to see the table to move it, either because it's associated with a particular session or because the session that created it closed and the table went away.

You really shouldn't use tempdb for this work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 8

Just to check one thing. When you say tempdb, do you mean "the system database used by SQLServer called tempdb" - or "a user database which will be used as temporary storage while the data is processed - could actually be called anything, although obviously not tempdb. Possibly even Fred"

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 9

Andrew Gothard (1/5/2009)
Just to check one thing. When you say tempdb, do you mean "the system database used by SQLServer called tempdb" - or "a user database which will be used as temporary storage while the data is processed - could actually be called anything, although obviously not tempdb. Possibly even Fred"

LOL, you read my mind... except the name was Bob.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

GSquared SSC Guru Points: 260824 More actions · Answer 10

If it's actually tempdb, then you need to keep in mind that tempdb is essentially wiped out whenever you restart the SQL services (such as if you reboot the server after a service pack update). That's a bit of an over simplification, but essentially, data in tempdb is subject to going away without notice.

What you need is a database, not tempdb, and a scheduled job that moves data from the current table to an archive table. You can achieve that with Insert...Select and then Truncate. It's really easy to do if you look up the commands in Books Online, but if you need help, post the table definition here and someone will help you write the archiving code.

If the record of the past data matters to you at all, you'll also want to schedule backups and all that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

motanihatim110 SSC Rookie Points: 32 More actions · Answer 11

Thanks all of you.

I think you are right. I was getting confused when others adviced me not to use tempdb but after reading your answer I got logic for not using tempdb. I will try this one and let you know if find any problem.

Thanks,

Hatim