x

Moving records to Linked Server Db

HI

We have a job with 3 steps. The purpose of the job is to move the live data to OLAP db. Earlier the job was in a single step and it was working fine. Recently it was changed to 3 steps. 1st step for disabling indexes in the OLAP db; 2nd step for moving data and 3rd step to enabling indexes in the OLAP db.

Now the modified job with 3 steps is not working. Directly executing the script from Query windows works fine. But when executed as job the following error is thrown:

'EXECUTE AS USER' failed for the requested user 'dbo' in the database . The step failed.

The job owner & db owner is sa.

Kindly suggest. Regards Mohan Kumar VS

------- Script
use LiveDb 
go 

-- Step #1 : Disable Indexes
DECLARE @ConfigDb VARCHAR(500)
DECLARE @SQLQUERY NVARCHAR(2000)
-- get the OLAP DATABASE name 
SELECT @ConfigDb=config_value FROM tblConfiguration WHERE config_code='OLAP Database'
SET @SQLQUERY = 'EXEC '+ @ConfigDb + '.dbo.OLAP_Enable_Or_Disable_Indexes @IsDisable = 1 '
EXEC sp_executesql @SQLQUERY
GO

-- Step #2 : Copy data to OLAP
DECLARE @LastRunDate DATETIME
SET @LastRunDate = DATEADD(DD,-1,GETDATE())
EXEC dbo.OLAP_SP1 @LastRunDate
GO

-- Step #3 : Enable indexes using 
DECLARE @ConfigDb VARCHAR(500)
DECLARE @SQLQUERY NVARCHAR(2000)
SELECT @ConfigDb=config_value FROM config WHERE config_code='OLAP_DB'

SET @SQLQUERY = 'EXEC '+@ConfigDb+'.dbo.OLAP_Enable_Or_Disable_Indexes @IsDisable = 0 '
EXEC sp_executesql @SQLQUERY
go

-- 
ALTER PROCEDURE [dbo].OLAP_Enable_Or_Disable_Indexes(@IsDisable BIT = 0)
AS
BEGIN
    IF(@IsDisable = 0)
    BEGIN 
       IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[OLAP_Tbl1]') AND name = N'IX_OLAP_Idx1')
         ALTER INDEX [IX_OLAP_Idx1] ON [dbo].OLAP_Tbl1 REBUILD

    END
    ELSE
    BEGIN
       IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[OLAP_Tbl1]') AND name = N'IX_mdo_corporate_id')
         ALTER INDEX [IX_OLAP_Idx1] ON [dbo].OLAP_Tbl1 DISABLE
    END
END
more ▼

asked Nov 29, 2011 at 04:42 AM in Default

Mohan_Kumar_VS gravatar image

Mohan_Kumar_VS
3 1 2 2

Can you please PRINT each dynamic query results and post that. Moreover, please make sure that the user is same for all three steps and the queries are executing in the correct databases.
Nov 29, 2011 at 05:00 AM Usman Butt
Moreover, add the DDL of "OLAP_SP1" Procedure as well.
Nov 29, 2011 at 05:07 AM Usman Butt
The user is same for all the steps. Step#2 OLAP_SP1 is working fine..
Nov 29, 2011 at 05:09 AM Mohan_Kumar_VS
What about the PRINTED DYNAMIC QUERY? Is there any Impersonation/ EXECUTE AS in the OLAP_SP1? Moreover, at which task it gives error?
Nov 29, 2011 at 05:22 AM Usman Butt
It throws error for task #1 itself. I will print dynamic queries shortly.
Nov 29, 2011 at 05:31 AM Mohan_Kumar_VS
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The clue is probably in your statement

Directly executing the script from Query windows works fine. But when executed as job the following error is thrown

Jobs are executed either under the Agent Service Account, or as a user you specify in the job step. Executing in SSMS, you will be connecting as whatever user you are logged in as.

In which bit of code is the 'EXECUTE AS USER' ?

more ▼

answered Nov 29, 2011 at 05:23 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

No where 'EXECUTE AS USER' is mentioned. Agent Service account is set to Network Service.
Nov 29, 2011 at 05:32 AM Mohan_Kumar_VS
Which step fails?
Nov 29, 2011 at 06:19 AM Kev Riley ♦♦
Step #1 fails.
Nov 29, 2011 at 06:34 AM Mohan_Kumar_VS
And when this was one step, it was all in a stored proc or directly in the job step? Running in the same manner?
Nov 29, 2011 at 06:40 AM Kev Riley ♦♦
Please note: There are 3 steps. Step 1 fails. But when all the steps are combined in a single step the job completes successfully.
Nov 29, 2011 at 10:37 PM Mohan_Kumar_VS
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x23

asked: Nov 29, 2011 at 04:42 AM

Seen: 859 times

Last Updated: Nov 29, 2011 at 05:00 AM