August 5, 2009 at 6:57 am
I am looking at two servers: one is a test SQL 2005 server that I set up to play around with SQL while I'm learning it, the second one is a test server for one of our projects which I did not set up.
On my server, I am able to run the Copy Database Wizard without any problems. I am logging in to the server as my domain account, running the wizard and accepting all the defaults which includes using the SQL Server Agent Service Account as the Integration Services Proxy account. In order for the job to succeed however, I have to go into the job that gets created and change the owner from my domain account to sa.
On the test server, I am doing the exact same thing (logging in as my domain account, setting the Integration Services Proxy account as the SQL Server Agent Service Account) but it's failing. I have also gone in to the job and changed the owner to sa. The error I receive is: "The directory 'LocalApplicationData' does not exist."
The only thing I can tell that is different between the two systems is that on my system I had the SysAdmin set up a special account to run the SQL Server Agent while on the other system, they chose to use the Local System account to run the agent service.
Does anyone else think this difference could be causing the error I'm seeing on the one system and if so, do you have any ideas about what to check to fix it?
Since I'm not a Windows Admin, I don't really understand the nuances of local accounts vs. domain accounts vs. active directory accounts so if you think it's an account issue, please be as clear as possible so that I can ask the Admin to check the right things.
Thanks!
August 5, 2009 at 8:27 am
I picked another server that is using the Local System account for the SQL Agent service account and tried the database copy wizard on it. It also failed, but got further along in the process and I got a different error this time:
"errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (134)" and "output column "ErrorCode" (14)"."
It appears that the database (and data files) were created, but none of the user tables got created.
So, unless the LocalSystem account on Server 1 can be different than LocalSystem account on Server 2, I guess I need to look for another possible source of the problem.
August 5, 2009 at 8:38 am
It might be because it partially ran before? Are you sending to a brand new database? That error looks like an error with a naming collision.
August 5, 2009 at 10:51 am
I agree that's what the error appears to be hinting at, but I just tried again and used a completely new database name and set of database files and it gave me the same error.
August 7, 2009 at 8:22 am
Ok, I've located the problem related to server 2 (the one spewing errors about ErrorCode). I thought this was a generic error message but after checking around the internet once more, I found a forum post (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a6512d6a-12f5-4122-bd00-fb898218ebb2) that suggested ErrorCode was a specific column name. Lo and behold, I have 2 tables in my database that have a column named ErrorCode. I renamed them both (for testing purposes only) and the error went away.
Here are two bug reports about this problem which Microsoft says is "by design":
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252471
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125866
I guess since my tables are vendor-created, Copy Database Wizard is a no-go.
August 7, 2009 at 12:52 pm
I haven't yet determined the source of the error referring to the 'LocalApplication' directory not being found, but I did figure out that if I do the attach/detach method instead of the online method, it works without giving me that error.
September 25, 2009 at 10:42 am
I also ran into this same issue and was getting the error "The directory 'LocalApplicationData' does not exist.".
Some observations I encountered in my trial and error testing:
1) If I made the proxy account a member of the Administrators group on the local server the error went away. That was not ideal but helped narrow some things down.
2) I noticed the Integration Services Proxy account was different from the SQL Service account. The proxy account was already an SA on the local SQL server but once I made it the same as the account running SQL services (or changed the SQL service account to the proxy) and removed the account from the Administrators group then the error also went away.
Option 2 was sufficient in our environment to correct this issue. It doesn't help explain why the error occurs but is a workaround. I hope that helps.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply