What Permissions do I need if any to remove the following Error: The ExistsOnSQLServer method has encountered OLE
Hello,
I am trying to import data from a database to another database on the same server using the Import Wizard of SQL Server 2005. I would like to be able to save this package so that I re-execute it in a Job created with the SQL Server Agent.
Before today I was receiving an error when I tried to save the database (old error, see below). My DBA was able to fix this by granting the SQLAgentOperator role. So after the fix I attempted once agaiin to create and save a package for the database. In the import wizard I specified that I wanted the package to be saved in SQL Server and I executed the package. I received the error listed below (current error) and then attempted to re run the package except this time I elected to save it to the file system. This worked. However, I believe that I am going to need the ability to save the package to the SQL Server in order to run this same package in a scheduled job in the
future. So I am trying to figure out why I am getting this error. Before today I have been receiving the
following error:
Error Prior to 1/28/10:
This old error occured whenever the Save to SQL server occurred. See image for details.
Error Details
Save to SQL Server failed.
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
--------------------------
ADDITIONAL INFORMATION:
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
Current Error:
The current error occurred prior to directly after the "execute" button was pushed. See image for details
Error Details
The operation could not be completed.
--------------------------
ADDITIONAL INFORMATION:
The ExistsOnSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_checkexists', database 'msdb', schema 'dbo'.). The SQL statement issued has failed.
Initially I was encouraged that I was now getting a different error because it seemed that the first error may have
been resolved when the DBA gave me the SQLAgent Operator role. However, I then noticed what I pointed out above: the second error occurs earlier in the process than the first, which makes me think that it is a "new" error that has to do with something I specified differently in package data flow.
I have read a bunch of forums with people posting about identical errors, but in not one of them does anyone have clear solution to my problem.
One solution in one of the forums gave a very vague definition of what the problem was:
"That error is caused by oe of two issues. Either you do not have an SQL Server save location created on msdb or
you do not have permissios to save there. Instead, save to the File System - not the SQL Server. A file is what you
need to open in BIDS anyway."
After reading this reply I don't really know what to do. It could still be a permissions error, or it could be a
problem with the SQL Server save location. Either way I don't know what the next step to take is.
NEW-SQL-ERROR.PNG
Old-Error.PNG
I am trying to import data from a database to another database on the same server using the Import Wizard of SQL Server 2005. I would like to be able to save this package so that I re-execute it in a Job created with the SQL Server Agent.
Before today I was receiving an error when I tried to save the database (old error, see below). My DBA was able to fix this by granting the SQLAgentOperator role. So after the fix I attempted once agaiin to create and save a package for the database. In the import wizard I specified that I wanted the package to be saved in SQL Server and I executed the package. I received the error listed below (current error) and then attempted to re run the package except this time I elected to save it to the file system. This worked. However, I believe that I am going to need the ability to save the package to the SQL Server in order to run this same package in a scheduled job in the
future. So I am trying to figure out why I am getting this error. Before today I have been receiving the
following error:
Error Prior to 1/28/10:
This old error occured whenever the Save to SQL server occurred. See image for details.
Error Details
Save to SQL Server failed.
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
--------------------------
ADDITIONAL INFORMATION:
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
Current Error:
The current error occurred prior to directly after the "execute" button was pushed. See image for details
Error Details
The operation could not be completed.
--------------------------
ADDITIONAL INFORMATION:
The ExistsOnSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_checkexists', database 'msdb', schema 'dbo'.). The SQL statement issued has failed.
Initially I was encouraged that I was now getting a different error because it seemed that the first error may have
been resolved when the DBA gave me the SQLAgent Operator role. However, I then noticed what I pointed out above: the second error occurs earlier in the process than the first, which makes me think that it is a "new" error that has to do with something I specified differently in package data flow.
I have read a bunch of forums with people posting about identical errors, but in not one of them does anyone have clear solution to my problem.
One solution in one of the forums gave a very vague definition of what the problem was:
"That error is caused by oe of two issues. Either you do not have an SQL Server save location created on msdb or
you do not have permissios to save there. Instead, save to the File System - not the SQL Server. A file is what you
need to open in BIDS anyway."
After reading this reply I don't really know what to do. It could still be a permissions error, or it could be a
problem with the SQL Server save location. Either way I don't know what the next step to take is.
NEW-SQL-ERROR.PNG
Old-Error.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.