April 7, 2004 at 10:35 am
It is firing off a procedure, that does an archive operation to another server. So it performs a distributed transaction.
I had a heck of a time getting it to work in the first place, but seemed to get the right combination of sql server agent user to execute it, and the correct linked server user to the other machine. Once I found the magic combination and it started working, i was happy.
But then, on monday, i checked its status, and it had failed on friday prior or thursday, and no longer executes.
I am not sure if it has something to do with SQL Server Agent or not. It cannot start a distributed transaction. Its the same thing that happened originally, and was magically solved.
I have tried dtcping, dtctester applications that i found on microsoft's website. These seem to perform successfully and do not suggest any kinda problems that would explain why my process doesnt work.
Any ideas?
Thanks, in advance.
April 12, 2004 at 8:00 am
This was removed by the editor as SPAM
June 22, 2004 at 12:44 pm
I have the same problem with our SQL Server 2000. It is on a W2K3 box and I have been given the project of cleaning out the log files, since it filled up all of the hard drive space. The scheduled backups have never worked since I have been working on the machine. The only way that they work is if I do it manually. It would be nice to know what the problem is but as I see no one else has replyed to your topic. If you have any tips that would help me out a bunch.
Thanks,
Erik Schlegel
June 23, 2004 at 9:16 am
I fixed my problem and I can schedule backups whenever I want to. The change that I made was in the properties on the servername. In enterprise manager, you will right click the server and go to the properties. After this, you will go to the connections tab and change the maximum concurrent user connections (0 = unlimited). We had it set at 2 before and after I changed it back to 0, it worked just fine. Let me know if this fixed the problem.
Erik Schlegel
June 23, 2004 at 9:16 am
I fixed my problem and I can schedule backups whenever I want to. The change that I made was in the properties on the servername. In enterprise manager, you will right click the server and go to the properties. After this, you will go to the connections tab and change the maximum concurrent user connections (0 = unlimited). We had it set at 2 before and after I changed it back to 0, it worked just fine. Let me know if this fixed the problem.
Erik Schlegel
June 25, 2004 at 9:29 am
I have a scheduled job. It is a one Step, type Transact SQL that runs a Stored Procedure under sa or another Admin user. I wrote:
execute DBNAME.dbo.sp_storedprocedurename
The sproc goes like this:
/*CLEAR TMP TABLE*/
TRUNCATE TABLE TempAccounts
/* INSERT FROM view OF LINKED TABLE */
INSERT INTO TempAccounts SELECT * FROM vAccounts
DECLARE @NumAccounts int
SELECT @NumAccounts = Count(*) FROM TempAccounts
/* IF THERE IS SOMTHING, CLEAR FINAL TABLE AND INSERT FROM TempTable */
IF (@NumAccounts > 0)
BEGIN
TRUNCATE TABLE Accounts
INSERT INTO Accounts SELECT * FROM TempAccounts
END
It is intended to update a table from linked server to a temporary table (not a #temptable). Then, to a final table. It did not work either when I tried a #temptable.
The Job runs OK and reports success, but it is not updating tables. But, when I run sproc via Query Analyzer, sproc updates the tables.
I know it takes about 20 seconds to do the job correctly, when I look at Job History it shows always OK and elapsed time 6 seconds.
What is going on here?
Thanks,
JD
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply