February 27, 2017 at 12:10 pm
Hi,
I can't seem to create or drop an index via SQL Server Agent Job. I can do so directly in the SSIS package. But, when I execute the SSIS package via Job, I get an error stating that the indexed table does not exists or I do not have permission.
Is there a good reason why I can't create or drop an index via SQL Agent Job?
February 27, 2017 at 2:33 pm
jake.dabrowski - Monday, February 27, 2017 12:10 PMHi,I can't seem to create or drop an index via SQL Server Agent Job. I can do so directly in the SSIS package. But, when I execute the SSIS package via Job, I get an error stating that the indexed table does not exists or I do not have permission.
Is there a good reason why I can't create or drop an index via SQL Agent Job?
Quick question, why are would you want to do that?
😎
February 27, 2017 at 2:48 pm
Hi,
I would like to drop indexes before inserting data to table then recreate afterwards. To improve performance.
February 27, 2017 at 3:02 pm
Thom A - Monday, February 27, 2017 12:55 PMDoes the account that the SQL Agent is running under have permission to access that database and drop indexes on it?
I think so.
February 27, 2017 at 3:03 pm
jake.dabrowski - Monday, February 27, 2017 2:48 PMHi,I would like to drop indexes before inserting data to table then recreate afterwards. To improve performance.
As Thom already indicated, you'll need to be sure that the service account for the SQL Agent service has the permissions needed to do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 27, 2017 at 5:15 pm
jake.dabrowski - Monday, February 27, 2017 3:02 PMThom A - Monday, February 27, 2017 12:55 PMDoes the account that the SQL Agent is running under have permission to access that database and drop indexes on it?I think so.
"Think"? If you only think, it's worth checking, as that's probably the answer (it doesn't) 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 28, 2017 at 6:02 am
Hardly worth mentioning as I'm sure you have done this, but are you setting the database context? for example, selecting the correct database from the dropdown list or putting a 'USE' statement before your 'CREATE INDEX' statement?
February 28, 2017 at 6:55 am
webtekkie - Tuesday, February 28, 2017 6:02 AMHardly worth mentioning as I'm sure you have done this, but are you setting the database context? for example, selecting the correct database from the dropdown list or putting a 'USE' statement before your 'CREATE INDEX' statement?
Yes, thank you. I have the DB Setting set up in SSIS package. Not in Job steps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply