February 19, 2010 at 9:41 am
I'm trying to create a job to run every hour and empty a set of tables on ServerA. Then I want to populate those tables with information from ServerB. ServerA is a network server where I am only a db_owner on a certain database, ServerB is my local server where I am a local Admin.
Environment: both Servers are SQL2005 and my workstation is XP. SSIS isn't installed on any of the machines and most likely won't be. Windows Authentication all the way around.
What I've tried:
1. building a SSIS package that can be called by a job on ServerB (since I don't have privileges on ServerA). SSIS package Truncates tables in a SQL Task, then imports the data, keeping the Identity Keys. Works great in BIDS mode, but when I put it into a job I get the infamous 'The product level is insufficient for component' error. Which basically means I need to install the full blown version of SSIS on both Servers. As I've stated earlier, probably not going to happen.
2. creating a stored procedure (sp) on ServerB that can be called by a job on ServerB. If I use 'Truncate', I have to have admin or admin-like powers (db_ddladmin, sysadmin, db_owner). Even as db_owner, I still get a 'You do not have permission' error.
3. creating a stored procedure (sp) on ServerB that can be called by a job on ServerB. If I use 'Delete From' then 'Insert Into With (KeepIdentity)' I have to have admin or admin-like powers (db_ddladmin, sysadmin, db_owner). Even as db_owner, I still get a 'You do not have permission' error.
I feel like I'm running out of options. Is there something else I can try?
February 19, 2010 at 9:54 am
Is the truncate the right option in this case? You said that when you move the data you keep the keys, but (in case of an identity column) the truncate returns your key back to the original seed value. If this happens, you then need to SET IDENTITY_INSERT ON in order to get your key back to where it should be by inserting a bogus record and then deleting it. All of this seems dangerous, and indeed, SHOULD require advanced privileges.
Tim Januario
February 19, 2010 at 9:56 am
Can I ask why you can't get SSIS installed on Server B? You indicate that you are the admin on this server.
February 19, 2010 at 10:41 am
Tim - I was using WITH (KEEPIDENTITY) on my INSERT INTO statement after my TRUNCATE to keep my keys.
Lynn - in my current environment, I don't have access to the installation media. It was installed for me and would appear to take an act of God to get it installed.
February 19, 2010 at 11:10 am
Sorry I had your source mixed up with your destination.
You said that you were db_owner of only a certain database which I am interpreting as being the database in question (else I guess it would be irrelevant). db_owner should have permission to truncate data so that is slightly confusing to me. If that is the case, is it possible to just drop and recreate the table? It seems strange to have that role but not have permissions to work with schema in such a way.
You mentioned that the process works perfectly in BIDS but that you need to run it as a job. This suggests that in fact you do have the necessary permissions but that the job agent does not which, would cause this exception. If you are the dbo of the database, are you able to give the account that runs the agent permission to alter the schema of that table? It is good practice to limit the permission of the agent to avoid back door shenanigans so that seems like a plausible scenario.
Not sure if any of this helps, I'm just hoping that maybe I can get something spinning in your head.
Tim Januario
February 19, 2010 at 11:45 am
I need you to do two things. On Server B, go to the Program Files directory and drill down into the Microsoft SQL Server directory. As Maintenance Plans are actually a subset of SSIS, you should still have the necessary components available to run SSIS packages. In th Microsoft SQL Server directory, you will see a 90 directory, under that there should be a DTS directory, then a Binn directory, This is your ultimate destination. Once there, find the DTExec.exe program, right click on it and select properties. Go to the version tab to see the version of this program. On my system it is 2005.90.4035.0.
Then, on your desktop system where you have BIDS, do the same thing. If the version on your desktop is newer thaan the version on your server, that is probably part of your problem and you need to see about getting the server updated. If not, I'll have to think a little harder on a possible solution.
February 19, 2010 at 12:43 pm
Tim: I know the setup/environment is strange, I'm not part of an IT team, but I am pretty fluent in SQLServer2005. I'm more of an IS guy. I'm not a supra-genius or anything, but I can usually figure things out, if not, I reach out to these forums. I don't think our IT dept. wants anyone using our SQL box to it's maximum potential. SSAS, SSRS, and SSIS are not installed on the network servers, nor on our local instances. That way information cannot flow freely. ;-). That's why I have to try these "work-arounds".
Lynn:
1. ServerB (local) version: 2005.90.4035.0.
2. BIDS version: "Microsoft SQL Server Integration Services Designer
Version 9.00.4035.00"
It appears they are the same.
BTW, I appreciate you guys taking time out of your day to help me out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply