• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

SQL Agent 2008

Hello,

I have SSIS package include  "Transfer SQL Server Objects Task",when I use the task to transfer tables from other server,  SQL Agent gives me error.

Thanks

0
samprg
Asked:
samprg
  • 12
  • 8
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
By default, SQL Server Agent is set to start with Network service.

If you are exporting to a file, then make sure that SQL Server Agent service is set to start using either a Domain account with privileges to the folders associated or localsystem to get it work.
0
 
samprgAuthor Commented:


I am sure SQL Server Agent service is set to start ,and I can run this packag in the SQL BIDS, but I can not run the package via SQL Agent!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I am sure SQL Server Agent service is set to start

Yes, I understand that Agent Service is running but I am asking you to check the Startup account for that Service which might impact

>> but I can not run the package via SQL Agent!

Can you check the startup account of Agent Service from SQL Server Configuration Manager and let me know so that we can fix it accordingly.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
samprgAuthor Commented:
Thank you,

Yes,  Agent Service is running.
0
 
samprgAuthor Commented:
may be I need to make "Multi Server Administration"  in the SQL Agent?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Yes,  Agent Service is running.

Sorry for the confusion.
Follow the steps below and let me know the results:

1. Open SQL Server Configuration manager
2. Under SQL Server Services, Right Click SQL Server Agent -> Choose Properties
3. Check what is the value entered for Log on as either LocalSystem or Network service or a domain account.

If it is set to Network service, then change it to either domain account with privileges on the other server or Local system to get it work.

>> may be I need to make "Multi Server Administration"  in the SQL Agent?

You need to configure Multi server administration if you are going to control / run jobs from one server to multiple servers. Else it is not required for your current scenario.
0
 
samprgAuthor Commented:
It is  Network service
0
 
samprgAuthor Commented:
I changed it to Local system to get it work.and I get same error

0
 
samprgAuthor Commented:

Sorry,
I changed it to "Local system".and I get same error

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, try changing it to a domain account with privileges on both SQL Server instances.
0
 
samprgAuthor Commented:
It does not have " Domain account"
0
 
samprgAuthor Commented:
I work on SQL Server 2008 R2
Microsoft SQL Server Management Studio Complete (expires in 162 days)                                    10.50.1600.1
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to check the Radio Button, Use this account and provide your domain account.

>> Microsoft SQL Server Management Studio Complete (expires in 162 days)  

Seems like you are using Evaluation edition of SQL Server which might have not configured Agent properly.
Try reinstalling it to get it fixed..
0
 
Alpesh PatelAssistant ConsultantCommented:
Please make sure Agent has db_Creator, db_read, db_write, db_dllcreat permissions. and laos make sure directory read write permission also
0
 
samprgAuthor Commented:
I did and still gives me error .
Why this package work in SQL BIDS and does not work in SQL Agent?!

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Why this package work in SQL BIDS and does not work in SQL Agent?!

Are you sure that under SQL Agent job, the SSIS package is running under the same login.
0
 
samprgAuthor Commented:
Yes,I am sure.
When I use same task to trans data fer from same server, the sql Agent works very well, when I change the task to transfer from other server gives me error, very strange!
0
 
samprgAuthor Commented:
Do I need to make in Sql server "Lanked Servers" ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> when I change the task to transfer from other server gives me error, very strange!

Do you have permissions to script / transfer object in other server which might be the reason for issue.
0
 
samprgAuthor Commented:
You right,I changed the account in SQL Agent, now it works.

Thanks a lot
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
And glad to help you out..
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now