I have several stored procedures that import data from other remote databases into tables on my local database. These remote databases are a mix of SQL and Oracle databases. All of the stored procedures follow the same format.
CREATE PROCEDURE [dbo].[ImportStoredProcedure1] AS SET NOCOUNT OFF; BEGIN DECLARE @TranName varchar(20) SET @TranName = 'Import' BEGIN TRAN @TranName TRUNCATE TABLE TABLE_NAME_HERE INSERT INTO TABLE_NAME_HERE SELECT Column1 , Column2 , Column3 , etc FROM LINKED_SERVER_ALIAS..LINKED_SERVER_DB.SOME_REMOTE_TABLE IF @@ERROR <> 0 BEGIN PRINT 'Rollback' ROLLBACK TRAN @TranName END ELSE BEGIN PRINT 'Commit' COMMIT TRAN @TranName END END
These stored procedures are executed by SQL Server Agent scheduled jobs and that is the only thing these jobs do.
Step Name: Execute ImportStoredProcedure1 Type: Transact-SQL script (T-SQL) Command: EXEC [DatabaseName].[dbo].[ImportStoredProcedure1]
When one of these scheduled jobs is executing I get the following error when I try to refresh the list of Tables the database has in SQL Server Management Studio: "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)"
The weird thing is if I execute this stored procedure normally I do not get this error, it only happens when I invoke it through the SQL Server Agent.
I already tried adding a WITH (NOLOCK) to my SELECT statement and that did not help. I am on SQL Server 2008. Any ideas?