Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
znwpta
Starting Member
12 Posts |
 Posted - 2009-07-10 : 10:04:08
|
| I am working on upgrading our current database from MSAccess into SQL Server, and I came across the following problem, it seems that a move last in sql when using a recordset from a ASP page takes an aweful long time as as locking all the SQL resources. I also I was wondering what is the best way to convert the following ASP code into SQL code. 1- Shipping Header is created rs.addnew ..... rs.update rs.movelast intShipID = rs("ID")2 - DEPLETE 559952 PL2222 5 (qty) -- there might be multiple line items for a lot -- ie. Item: 559952 lot PL222 might be in the table as -- 559952 PL2222 3 -- 559952 PL2222 1 -- 559952 PL2222 4 3- Transaction needs to be written rs.addnew ..... rs.update rs.movelast intTransID = rs("ID") 4- Deplete from inventory table 5- write to archive table rs.addnew rs("TransID") = intTransID rs("ShipID") = intShipID rs.update6 end loopI was thinking of doing the following1- StoreProcedure that creates the shipping header and returns the ScopeID2- SP that creates the transaction and returns the ScopeID3- Using a cursor to deplete the items from the first table and write to the second table for every line item sending the IDs from both shipheader and transaction.The problem I had with this was that by calling multiple stored procedures from my asp page I lose the begin/commit/rollback in case there is something wrong with the last SP. Does anyone have any suggestions about all this? |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2009-07-10 : 17:55:11
|
| Its a very bad bad practice to move access to sql server over a web page.ASP is only a script that runs on IIS, which reads your page and has a timeout value. The best way to do this, in my opinion is simply import the values through sql server, manually check to see if its ok and update your connection stringThen again, am sure you would want to move to .net soon ??? |
 |
|
|
|
|
|
MS Access --> SQL Server