The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Simple Update on Execute SQL Never Completes
Posted: 12 Feb 09 12:27 PM
|
So I did something stupid - I deleted a user instead of retiring her. This caused 28000 History notes to now display with UserName = "Unknown User".
I have identified the old user Id and the ID of the user I want to transfer the History records to. I came up with the following SQL update statement:
UPDATE History SET UserID = ‘U6UJ9A000035’ FROM History WHERE UserID = ‘U6UJ9A000010’
I've simulated the problem on a VM and currently testing this update command, in the "Execute SQL" dialog of the Administrator. It's been running for two and a half hours with no sign of progress.
I'm used to working with SQL Server directly, and would not expect such a relatively simple UPDATE statement to run so long. Is it normal for the SLX OLE DB to run so slow? Is there something wrong with my statement? |
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 12 Feb 09 8:31 PM
|
Why is "From History" in there? If you're simply swapping ID's in the history table I think you'd just want
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’
|
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 13 Feb 09 7:59 AM
|
| Todd - that's the statement I originally used, and had the same result. From Google results, I had gotten the impression that the SLX OLE DB needed that "FROM" clause. But either with or without it, the statement never completes. |
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 13 Feb 09 12:33 PM
|
| Joe, Seems odd that it wouldn't finish at all. Do you have a test DB that you have run this on outside of SLX? It is a fair number of records, but it seems like this should run successfully. Are log files doing anything strange due to these updates, or is the SQL server struggling? You could try running the statement with an additional date condition to limit the rows affected to see if it's performance related. Not exactly sure why it would eventually compelte for you. The statement should work. |
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 14 Feb 09 5:06 AM
|
How many records are in the table and, if you do this in EntMgr, does it complete (don't worry about sync for now).
Also, do you have any triggers on the table (check the triggers physically, even if you believe you haven't created any). If so, disable prior to running.
|
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 17 Feb 09 3:59 PM
|
This happens all the time.....
Assume that the History.UserName is correct still, you aren't populating it....
Break this up into chunks by CompletedDate....... Monthly, Quarterly, etc. should get the job done. |
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 17 Feb 09 4:04 PM
|
Originally posted by Joe
I'm used to working with SQL Server directly, and would not expect such a relatively simple UPDATE statement to run so long. Is it normal for the SLX OLE DB to run so slow? Is there something wrong with my statement? |
|
1. you are on VMWare.....Workstation? Slow is the word.
2. No FROM in an Update statement....wrong syntax for SLX.
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '1/1/2006'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '1/1/2007'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '1/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '4/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '7/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '9/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '10/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '11/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '12/1/2008'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '1/1/2009'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < '2/1/2009'
UPDATE History SET UserID = ‘U6UJ9A000035’ WHERE UserID = ‘U6UJ9A000010’ AND COMPLETEDDATE < GETUTCDATE()
|
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 17 Feb 09 4:18 PM
|
As an update:
1. Even a simple select statement never completes, same behaviour on Production as in the VM
2. The SalesLogix VM is connected to an actual physical SQL Server
3. A select statement does complete if I leave UserID out of the result set. I did a select with a date range that covered the entire time we've used the system. It took about 5 minutes but it did complete. Adding UserID back in to the select reintroduced the problem.
So I'm thinking there's something wrong with the UserID field. At the moment I can't look directly at the SQL Server database - the people who have the logins are too busy too find them for me. |
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 26 Feb 09 4:44 PM
|
If you're used to writing SQL in Query Analyzer, then you probably test update commands by running them with begin tran/rollback tran.
If you run something with begin tran in SLX and forget to rollback or commit, that creates a lock in Saleslogix that will stall SLX Provider operations until a commit/rollback is run. When a simple operation stalls like the first thing I do is look for locks. If it's not me fogetting to rollback tran, then it's a user with a frozen client.
|
|
|
|
Re: Simple Update on Execute SQL Never Completes
Posted: 27 Feb 09 8:12 AM
|
The problem has been resolved. One thing it may have been is my Admin program was version 7.2.0 but the database is 7.2.2.
Also, and this is the stupid part, I was keeping my SQL statements in a Word document, and copying and pasting into the Admin SQL screen. I think Word was reformatting my single quotes into something different, as when I copied the SQL statements into SQL Server Management Studio, it couldn't parse the statement until I copied over the single quotes. That clued me in to the formatting issue. |
|
|
|