Dear all, I have a little puzzle. I have an SSIS package on Server A. The first part of my SSIS package runs two SQL scripts. These execute a script which locks down the two key databases which live on our "live" server or Server B. The queries lock the databases to single user, then restore the new extracts which are delivered via attachmate to Server C. It then unlocks the database to multi-user and adds local key user permissions and roles to the database. Firstly, has anyone got ay idea what might cause this to happen? Could it be network dropout perhaps? Secondly, can anyone advise as to how I could introduce a wait or a testing loop to make sure the extract is fuly restored before permissions are changed? Thanks Iain [code] DECLARE @LogRestoreScript VARCHAR(MAX) SET @LogRestoreScript = ' EXEC ( '' Alter Database Rio_Daily_Extract SET SINGLE_USER With ROLLBACK IMMEDIATE '') AT YDD36MS38 EXEC ( '' RESTORE DATABASE [Rio_Daily_Extract] FROM DISK = N''''\\10.51.205.199\e$\SFTP\inbound\' + @LatestExtractFileName + ''''' WITH FILE = 1, MOVE N''''RiO_5LEC_Data'''' TO N''''E:\SQL Data\Rio_Daily_Extract.mdf'''', MOVE N''''RiO_5LEC_FullText'''' TO N''''E:\SQL Data\Rio_Daily_Extract.ndf'''', MOVE N''''RiO_5LEC_Log'''' TO N''''D:\SQL Logs\Rio_Daily_Extract.ldf'''', NOUNLOAD, REPLACE, STATS = 10 '') AT YDD36MS38 EXEC ( '' Alter Database Rio_Daily_Extract SET MULTI_USER WITH ROLLBACK IMMEDIATE '') AT YDD36MS38 EXEC ( '' USE [Rio_Daily_Extract] CREATE USER [RAccess2] FOR LOGIN [RAccess2] '') AT YDD36MS38 EXEC ( '' USE [Rio_Daily_Extract] EXEC sp_addrolemember N''''db_datareader'''', N''''RAccess2'''' '') AT YDD36MS38 ' EXEC(@LogRestoreScript) [/code]

What are you trying to solve? What do you mean under "what might cause this to happen"? The approach chosen is a tad odd IMHO. The code puts a db into single user mode, and it is monolithic, so all is done in one hop. I would add a begin try to this SQL. Arthur My Blog

What has happened on multiple occasions is that the SQL proceeds to try to apply the SET MULTI-USER statements and causes a complete hang of the restore process, which prevents the rest of the ETL scripts from running. I cannot find a reason that sometimes the script waits politely for the restore to compete and sometimes seems to get bored and jumps ahead. If I could discover root cause, I could then more successfully find a way to stop it happening. My initial attempt to resolve the issue appeared successful, but then I had another repeat of the problem. I split the script into two seperate scripts, each within a BEGIN END statement:- BEGIN --PRINT 'STAGE 2 Restore Condition met: Restore begun' INSERT INTO Sp_Reports.dbo.EXTRANLOG_SYS(Log_Type, TimeComp, LogDate, Task, Sub_Task, Context, EXTRANLOG_User) SELECT '1', 'START', GETDATE(), 'SSIS - Extract Restore SWK RIO', 'Extract Found - Beginning Restore','Staging', SUSER_NAME() DECLARE @LatestExtractFileName VARCHAR(500) SET @LatestExtractFileName = ( SELECT RioExtractFileName FROM @ETL_MONITOR_ExtractFileStore WHERE RANK = 1 ) DECLARE @LogRestoreScript VARCHAR(MAX) SET @LogRestoreScript = ' EXEC ( '' Alter Database Rio_Daily_Extract SET SINGLE_USER With ROLLBACK IMMEDIATE '') AT YDD36MS38 EXEC ( '' RESTORE DATABASE [Rio_Daily_Extract] FROM DISK = N''''\\10.51.205.199\e$\SFTP\inbound\' + @LatestExtractFileName + ''''' WITH FILE = 1, MOVE N''''RiO_5LEC_Data'''' TO N''''E:\SQL Data\Rio_Daily_Extract.mdf'''', MOVE N''''RiO_5LEC_FullText'''' TO N''''E:\SQL Data\Rio_Daily_Extract.ndf'''', MOVE N''''RiO_5LEC_Log'''' TO N''''D:\SQL Logs\Rio_Daily_Extract.ldf'''', NOUNLOAD, REPLACE, STATS = 10 '') AT YDD36MS38 ' EXEC(@LogRestoreScript) PRINT @LogRestoreScript INSERT INTO Sp_Reports.dbo.EXTRANLOG_SYS(Log_Type, TimeComp, LogDate, Task, Sub_Task, Context, EXTRANLOG_User) SELECT '1', 'END', GETDATE(), 'SSIS - Extract Restore SWK RIO', 'Extract Found - Beginning Restore','Staging', SUSER_NAME() END BEGIN INSERT INTO Sp_Reports.dbo.EXTRANLOG_SYS(Log_Type, TimeComp, LogDate, Task, Sub_Task, Context, EXTRANLOG_User) SELECT '1', 'START', GETDATE(), 'SSIS - Assign Permissions SWK RIO', 'Extract Found - Assigning Permissions','Staging', SUSER_NAME() SET @LogRestoreScript = ' EXEC ( '' Alter Database Rio_Daily_Extract SET MULTI_USER WITH ROLLBACK IMMEDIATE '') AT YDD36MS38 EXEC ( '' USE [Rio_Daily_Extract] CREATE USER [RAccess2] FOR LOGIN [RAccess2] '') AT YDD36MS38 EXEC ( '' USE [Rio_Daily_Extract] EXEC sp_addrolemember N''''db_datareader'''', N''''RAccess2'''' '') AT YDD36MS38 ' EXEC(@LogRestoreScript) PRINT @LogRestoreScript INSERT INTO Sp_Reports.dbo.EXTRANLOG_SYS(Log_Type, TimeComp, LogDate, Task, Sub_Task, Context, EXTRANLOG_User) SELECT '1', 'END', GETDATE(), 'SSIS - Assign Permissions SWK RIO', 'Extract Found - Assigning Permissions','Staging', SUSER_NAME() END As the problem still occurs, my next thought is to put a "test" in such that the second script cannot run until the restore process has completed. What I have not yet been able to figure out is how to test the state of the restore process, in order to do this. I am also open to any other suggestions. Iain

Iain, EXEC does that in an out of process manner, so I guess if the restore is fast due to the database size being small then the SQL succeeds, thus my remedy would be in just backing it up without setting it to single user mode if possible.Arthur My Blog

Hi, You can try putting a wait after restore for a few seconds and running the other codes. Regards satheesh

hi Both, The restore takes around 50 minutes. The problem is that on some occasions, the script will proceed at either a few seconds or a few mniutes into the restore. i did think about a wait, but what I really want to do is base that wait around a loop which looks to see if the restore is complete. I just haven't figured out a way to do that yet, particularly as the restore is happening on a different server. Iain

Why are you creating strings and using exec anyways? You are executing several out of process statements. This would be the correct way to do what you are attempting. Alter Database Rio_Daily_Extract SET SINGLE_USER With ROLLBACK IMMEDIATE DECLARE @DBBackupFile as nvarchar(128); DECLARE @LatestExtractFileName VARCHAR(500) SET @LatestExtractFileName = ( SELECT RioExtractFileName FROM @ETL_MONITOR_ExtractFileStore WHERE RANK = 1 ) SET @DBBackupFile = N'\\10.51.205.199\e$\SFTP\inbound\' + @LatestExtractFileName RESTORE DATABASE [Rio_Daily_Extract] FROM DISK = @DBBackupFile WITH FILE = 1, MOVE 'RiO_5LEC_Data' TO 'E:\SQL Data\Rio_Daily_Extract.mdf', MOVE 'RiO_5LEC_FullText' TO 'E:\SQL Data\Rio_Daily_Extract.ndf', MOVE 'RiO_5LEC_Log' TO 'D:\SQL Logs\Rio_Daily_Extract.ldf', NOUNLOAD, REPLACE, STATS = 10 Alter Database Rio_Daily_Extract SET MULTI_USER WITH ROLLBACK IMMEDIATE GO USE [Rio_Daily_Extract] CREATE USER [RAccess2] FOR LOGIN [RAccess2] GO USE [Rio_Daily_Extract] EXEC sp_addrolemember db_datareader, RAccess2 ----------------------- Chuck Pedretti | Magenic North Region | magenic.com

Hi Chuck, I believe that the reason that the code was broken down into exec strings was that the code is not running on the same server that is controlling the job. Iain

Hi Chuck, I believe that the reason that the code was broken down into exec strings was that the code is not running on the same server that is controlling the job. Iain Shouldn't matter - you select a server to run the SQL on when you set the execute SQL task.Chuck Pedretti | Magenic North Region | magenic.com