Oracle Provider Failing When Scheduled With SQL Agent

Sep 25, 2007

Hi

I have created a package that extracts data from an Oracle 10g database into a database on SQL Server 2005 SP2.

This package run fine when run inside BIDS and also if run from DTExecUI or DTExec
(This is done while logged in using the same domain account that runs both the SQL Service and the SQL Agent Service. This account is also a local admin on the box.)

When I try to schedule this with SQL Server Agent (running under the SQL Agent Service Account) it returns an MSDAORA error

"Oracle client and network components were not found"

The same error occurs if I try to use CmdExe step to try and execute the DTExec command.

The interesting part however is that I use a lower privaleged proxy account then

(a) Executing the package directly still fails but
(b) Running a DTExec command will succed

Can anyone provide an explanation for this behaviour. While I can work around this "feature" by using the DTExec and a proxy account it does make the packages harder to configure/debug and defeats many of the configuration advantages provided by SSIS

TIA

Nadreck



View 1 Replies


ADVERTISEMENT

Provider String For LinkedSrvr OLE DB Provider For ODBC (to Oracle RDB)

Oct 10, 2007



I have an ODBC connection setup for Oralce RDB. This works fine in say MSAccess. Now I would like to add a SQL Server Linked Server to this ODBC driver (it is for RDB not regular Oracle), but I am not able to get this to work. What is the Provider String and Location / Catalog for using this type of ODBC? I setup DSN as 'name', Provider String as 'UID=user;PWD=pass;' and it seems it tries to connect but gives an error. "%SQL-F-SCHNOTDEF, Schema USER is not defined"

Any help is greatly appreciated.

View 3 Replies View Related

Scheduled DTS Failing

Mar 6, 2000

When I create a DTS to import data from Visual FoxPro it will work if I run immeadiately, but when I schedule it to run at a specific time it will Fail.
Any ideas why??

Thanks for any input,
Tom

View 1 Replies View Related

Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle

Sep 12, 2007

I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.

View 4 Replies View Related

Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1

Feb 22, 2006

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

View 17 Replies View Related

Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle

Aug 23, 2007

Hi everybody,

I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.

Please suggest me wht should i do know?

Thanks in advance

Regards
Arvind L

View 3 Replies View Related

Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider

Jan 11, 2007

Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".

 

This is how I set up my Linked server:

Provider: "Oracle Provider for OLE DB"

Product Name: SomeServer

Data Source: SomeServer

Provider String:  "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"

 

 

The query I run is:

Select * from [Someserver].[schema or database]..[tbl_name]

 

Any help???  What am i missing?

View 3 Replies View Related

Why Are My Scheduled DTS Packages Failing???

Jun 20, 2007

I have a section of about 6 DTS packages that started failing recently. They all write to a network share using a UNC path. The failure coincides with a change to the account SQL Server and SQL Agent run under. I know security context is very important and I have verified the new service account they run under is a Domain account that can execute the DTS packages and has full permissions to the network share.

But when the Agent Job tries to run it fails everytime. Two of us have been looking at this and can't find the issue.

NOTE: I also made the service accout a Server Administrator (fixed server role) to make sure it wasn't a SQL permissions issue.

Anybody have any suggestions about where to look?

View 3 Replies View Related

Failing Scheduled Jobs - Help - Urgent

Jul 27, 2001

When I change the SA password on my server the current Scheduled SQL Jobs no longer work. If I run DTS packages by themselves and not from the Scheduled job window they work fine. I am using the sa as the Owner of the jobs and my SQL Agents services use a Domain Account. If I rebuild the jobs after the sa password is change they work fine. However, I don't want to rebuild 100 jobs every time I change my SA password.How can I fix or get around this problem without rebuilding my jobs?

View 5 Replies View Related

Scheduled SSIS Failing All The Time - Help!

Jun 11, 2008

Hi all. We recently migrated to SQL Server '05 from 2000 and I'm trying to learn SSIS by crash course.

I just wrote an SSIS for one of our nightly processes in Studio 2005. It built with no errors, and the solution and resulting package are saved on the server housing SQL Server.

I ran the package in debug and it completed 100% with no errors. However, when I scheduled the package in SQL Server, it starts and fails.

Now, I'm very new to SQL '05 and I'm trying to figure out how to log the SSIS jobs, but is there any way to find out why the scheduled packages fail?

I'm desperately needing help on this, and any help would be greatly appreciated!

View 3 Replies View Related

SSIS Package Failing Via Scheduled

Apr 8, 2008



I have 3 SSIS packages I had in a project and exported and scheduled via an SQL Agent job. 2 are working great and one is failing. I have noticed on this one its also having issues in BIDS. It runs sucessfully once then next day when I go in it fails. If I just go into the Data flow and open up the SQL Destimation and then hit ok and try re-running it works. It is like there is something that it doesn't remember.

Same thing happens in SQL Agent if before I promote I go into SQl destination editor hit ok save then

create my build run it via agent job it works 1st time but fails on the following time. I don't see anything different between this SSIS and the others but there must be something.

All of these are simple copy data from as/400 to SQL server with some data conversions.


Here is the error msg I am receiving.

Message
Executed as user: PERFORMANCEstacyadmin. ....3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:55:11 AM Error: 2008-04-08 05:56:00.88 Code: 0xC0202009 Source: Load WeeklySalesHistory Current Week WeeklySalesHistory [184] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". End Error Error: 2... The package execution fa... The step failed.

What am I missing.

Stacy

View 3 Replies View Related

Scheduled TRN Backups Failing (sql2000)

Mar 9, 2007

I have created a backup that does once weekly BAK and every night it does TRN backup. This is in the same maintaince plan, the jobs appear as seprate jobs as they should. I have noticed my TRN is always failing, after looking into the event logs i see this.

please note this is lastest time to early:

4:22pm
this is the warning that the event log gives:

SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'xxxxxxxx TRN'' (0xB87B2FA07E4FB74CA01A1D38134C9C4F) - Status: Failed - Invoked on: 2007-03-09 16:19:40 - Message: The job failed. The Job was invoked by User domainAdministrator. The last step to run was step 1 (Step 1).


the next is
4:22pm
18265 :
Log backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), first LSN: 24624:14008:1, last LSN: 24624:14021:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:program filesmicrosoft sql serverMSSQLBACKUPStagingStaging_tlog_200703091622.TRN'}).

last is 4:21pm
18264 :
Database backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), pages dumped: 93982, first LSN: 24624:14008:2, last LSN: 24624:14018:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:program filesmicrosoft sql serverMSSQLBACKUPStagingStaging_db_200703091618.BAK'}).

So for some very strange reason its running a full backup then a Transactional, for this reason i would say its failing but why is it even doing this backup? the job is only for TRN????

A manual TRN backup works like a treat

Thanks

BRAD

View 4 Replies View Related

Scheduled Tasks Failing Because Of Email Notification

Jun 2, 1999

Good morning,

I have a strange problem with a scheduled task failing with the following:

"Unable to send completion notification email to operator with email name '' for task 2780, 'Scheduled Update'"

This job is the same across several servers and the job runs on the other servers. This is a bit frustrating... I cannot seem to find the difference that is causing the problem.

The funny thing is that I am not using SQLMail or anything to notify anyone regardless if the job succeeds or fails.

Any pointers?

Thanks much,

Don

View 2 Replies View Related

DTS Job Failing Execution When Scheduled, Works Fine Manually.

Feb 6, 2004

My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Help!!!

View 3 Replies View Related

Connection Failing Randomly TCP Provider Error: 0

Mar 4, 2008

Here is what I got:

Sql 2005 std on windows 2003 server r2

tcp port 1433 set for connections in Sql 2005

Port open on firewall from DMZ 1433 can connect from web farm telneting from all IP address on port 1433 to sql both by name and IP.

the connection works 99.9% of the time.

No errors in the sql logs showing anything Firewall logs show no blocks. Windows firewall service is disabled to move that out of the troubleshooting.

connection string:

<add key="CommonConnectionString" value="server=servernameinstancename,1433;database=DBname;uid=username;pwd=mypass;Connection Lifetime=30" />



An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)



View 3 Replies View Related

Using Connection Managers Inside Custom Log Provider Failing!

Apr 3, 2006

Hi,

I'm trying to use an OLE DB connection manager from inside a custom log provider, but can't get the code to work - I get an exception "A first chance exception of type 'System.InvalidCastException' occurred in SSEALogProvider.dll" on the last line of the code below.

I know the connection is OLE DB from looking at .CreationName.

I've tried both with & without ToConnectionManager90(), but appearently not in the right way, both cases gives the same exception.

Any tips on what I'm missing? All the examples I've seen are either creating (instead of using) connection managers, or are file based connection managers. Any and all help much appreciated!



Thanks/Kristian



if (this.ConfigString.Length == 0 || connections.Contains(ConfigString) == false ||

connections[ConfigString].CreationName != "OLEDB")

{

infoEvents.FireError(0, "SSEALogProvider", "The ConnectionManager " + ConfigString + " specified in the ConfigString property cannot be found in the collection, or is of the wrong type (expected CreationName='OLEDB'.)", "", 0);

return Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure;

}

else

[...]



public override void OpenLog()

{

if (!connections.Contains(this.ConfigString))

throw new Exception("The ConnectionManager " + this.ConfigString + " does not exist in the Connections collection.");

ConnectionManager connectionManager = connections[ConfigString];

IDTSConnectionManager90 connectionManager90 = DtsConvert.ToConnectionManager90(connectionManager);

object connObj = connectionManager90.AcquireConnection(null);

//ConnectionManagerOleDb connectionManagerOleDb = (ConnectionManagerOleDb)(connectionManager.InnerObject);

//object connObj = connectionManagerOleDb.AcquireConnection(null);

if (connObj == null)

{

events.FireError(0, "SSEALogProvider", "Could not connect to the log database with connection string " + ConfigString + ".", "", 0);

return;

}

// Exception on next line: "A first chance exception of type 'System.InvalidCastException' occurred in SSEALogProvider.dll"

OleDbConnection oledbConnection = (OleDbConnection)connObj;

View 3 Replies View Related

Can't Run The Scheduled SQL Agent Job?

Feb 17, 2007

Hi,

I'm tried to run the job that created from maintenance plan. It fail and give me the following message:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAINMYNAME', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)


We are using the Windows Autheniciaton mode only and my id is set as sysadmin, setadmin, processadmin in the server roles.

I don't why I can't run the job, I check the SQL agent services is login using LOCALSYSTEM.


I try to the NT admin account to login the box, but still getting the same error message.

Any idea?





Thanks!

View 5 Replies View Related

Sql Agent Service Failing

Feb 9, 2001

Whenever i change the SA password to anything else, my SQL Agent services
like jobs, alerts starts falling, and if i attempt to stop and then restart
sql agent service, it will not start again.

My server uses sql authentication for everything...

help...

thanks

vinod

View 3 Replies View Related

DTS Job Fails When Scheduled From SQL Agent

Mar 31, 2004

Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..

Executed as user: mydomainmylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.

How come it loses the path to the file when I dont run it directly?
Cheers
Mick

View 4 Replies View Related

Who Can Run The Server Scheduled Agent Job?

Feb 26, 2007

Ray writes "Hi,

I tried to ask the NT admin to login the SQL server and setup a scheduled job to "Shrink the db" for testing purpose.

I tried my ID even I'm not the NT admin, but setup as SQL server as sysadmin, setupadmin, processadmin.


Then the job start and got the error.

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAINMYNAME', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)


I have no idea why that happen to NT admin. then I check the Services for SQL Agent under the control panel- admin tools- services.

It shows using the LOCALSYSTEM as login for the SQL Agent service.


Any one have any idea. what security requirement to run a job other than use 'sa' account, since we are using on Windows authenciation mode only.


I must be something is missing? hope some experts here to answer my question.


Thanks!"

View 1 Replies View Related

Should We Allow SSIS To Be Scheduled Via SQL Agent

May 5, 2006

Hi,

Can we allow SSIS to be scheduled via SQL Agent. Is there any security concerns.

Thanks & Regards

Shri.DBA

View 4 Replies View Related

FTP Task Failing When Using Agent Proxy

Jan 11, 2008

Hi,

I am having problems with an SSIS package whereby the first step of the package is failing. I am getting the following error message:

Unable to connect to FTP server using "FTPLocation".

This only happens when I use a Agent Proxy as the 'Run as' option. If I change it back to SQL Agent Service Account it works perfectly.

For the credentials of the proxy, I have used my own windows login as the identity. And I have full priveleges on the server, ie if I log on to the server I can successfully ftp to the same site and download the file without any problems. So shouldn't the Agent be able to do so too when it uses my credentials?

Any help would be much appreciated.

DrBrain

View 3 Replies View Related

SQL Agent Failing When Trying To Run My SSIS Package

Feb 6, 2006

I found a similar thread posted under the same title as this by dba123. Unfortunately, his solution does not match my problem.

I have a SSIS package that will run fine in debug mode, will run fine when published to the server when I right click and execute it. However, when I run it through a job, either scheduled or running the job manually, the job fails. There is an entry in the servers application log telling me that "login failed for user xxxxx" where xxxxx is the name of SQL login that I am using. I know that the login works (logged in manually to the server via management studio, and it runs fine when I run it manually). Both connection managers are SQL, no flat files or other data source types. All the commands are T-SQL. The SQL Agent has full database access to both servers that the connection manager connects to - and its NOT the profile that is reporting as failing - it is a domain account.

This is totally bizzare - ANY help would be appreciated.

View 7 Replies View Related

SQL Agent Failing When Trying To Run My SSIS Package

Feb 21, 2006

Hi,
I have a problem with an Access db
connection in my package when run by SQL Agent. I have problem running a job with my package so i used the
log and found out that when i change the connection property of the
access connection manager in my package to a local Access file on my
machine, it works just fine but if i change the connection to that of
an Access db on my Y drive (which has been set up on my machine), it
doesnt execute. I checked the security property of the network folder
in which the access db exist and I do have rights. That is to
say, i dont know why it cannot access it then. The funny part is that
when i run the packae in SSIS (VS.NET) or Intergration services in
Managment studio, it runs just fine but when i execute the job through
Sql Server Agent, it generates the following error:

OnError,CYPRESS0927,CYPRESSebuah,CLMH
from ocan
access,{b1f7035e-919c-434b-8a1d-d0f6267a13aa},{6ED748E8-16C0-4E9A-9DBD-882641657572},2/20/2006
6:15:15 PM,2/20/2006 6:15:15 PM,-1071611876,0x,The AcquireConnection
method call to the connection manager "ocan_conn" failed with error
code 0xC0202009.

ocan_conn is the name of my connection object to the access db in my package.

Is there anything Im missing.

View 3 Replies View Related

SSIS Packages Failing To Run. In SQL Agent

Apr 28, 2008


They wre runnning fine until 4/21/2008 then all SSIS packages started to fail.

Simple SQL jobs are still running fine thouhgt and reporting success.

Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

View 1 Replies View Related

SQL Agent Failing When Trying To Run My SSIS Package

Jan 4, 2006

My SQL Agent was working fine.  Now when I try to run it, it failes when trying to run my SSIS package.  I looked in the logs, and job history, no luck in finding any critical errors.

My SSIS package runs fine through VS 2005 in debug mode.

Here are the only errors:

01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)
01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control
01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache
01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 1
01/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected
01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.1399
01/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)
01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860

View 37 Replies View Related

SQL Server Agent: Job Not Running As Scheduled

Mar 6, 2007

I've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.

It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.

DTS DETAILS
The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.

The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:

JOB DETAILS
Name: TransferData
Enabled, Target = Local Server
Category: Uncategorized Local
Description: Execute package: TransferData

STEPS
ID: 1
Step Name: TransferData
Type: Operating System Command
On Success: Quit with Success
On Failure: Quit with Failure

PROPERTIES OF STEP
Step Name: TransferData
Type: Operating System Command (CmdExec)
Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137

Nothing special in the advanced tab

SCHEDULES TAB
ID: 20
Name: TransferData
Enabled: No
Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM
and is setup as Recurring
Nothing in Notifications Tab

I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.

I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.

Thanks in advance for any assistance with this.

View 8 Replies View Related

SQL Server Agent Running; Jobs Failing

Jul 9, 2007

Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.

The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Unable to open shim database version registry key - v2.0.50727.00000."

Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."

We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.

This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.

Has anyone else had this problem?

Thanks!

View 1 Replies View Related

Pump From Access Failing Validation When Run By SQL Agent

Jan 30, 2008

Hello:

I've created a package that runs fine from BIDS when logged in with my domain account. I have created a SQL Agent Proxy on the server with that same account. In the Job Step on the server, I edit the connection strings so that username and password is there for both my source Access connection and the destination SQL Server. Here is the connection string I create for MS Access:




Code SnippetData Source=\10.210.226.202OTM Reports for SymmetricsCDRD001.MDB;User ID=admin;Password=;Provider=Microsoft.Jet.OLEDB.4.0;


Here is the error:


Code SnippetExecuted as user: DOMAINMRUSER. ethod call to the connection manager "MSAccessDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2008-01-30 09:49:19.66 Code: 0xC0047017 Source: Cost DTS.Pipeline Description: component "Cost" (1) failed validation and returned error code 0xC020801C. End Error Error: 2008-01-30 09:49:19.66 Code: 0xC004700C Source: Cost DTS.Pipeline


I have tried with various settings in the package for "ProtectionLevel" such as "DontSaveSensitive" and "EncryptSensitiveWithUserKey". I would think that using my account with the proxy the last option would work when running it on the server, since it is essentially the same user running the package, but I'm new to playing with the proxy.

I tried using package configurations but got an error there too, think it couldn't access the file, event though it was on an accessible share--accessible to my account.

Any idea what might be happening here?

Thanks,
Kayda



View 1 Replies View Related

Send Mail Task Failing Under SQL Agent

Nov 9, 2006

Using SQL 2005, SSIS, I have an SSIS package which is scheduled as a SQL Server Agent job. The package contains a Send Mail task. This task fails when SQL Agent runs the package. The task succeeds when executed interactively from the package, logged on as the SQL Agent service account. The package as a whole succeeds when executed from "Start Debugging". An Execute SQL Task in the same package containing an sp_send_dbmail statement succeeds when SQL Agent runs the job, and the same sp_send_dbmail succeeds in Mgmt Studio query window. All these events all logged onto the server as the SQL Server/SQL Agent service account.

I would appreciate ideas on what else to check for here, so that I can use the Send Mail SSIS task in packages which are run by SQL Agent jobs.

Thanks, Holly

View 1 Replies View Related

SQL Agent Failing When Running SSIS Package

Mar 9, 2006

I deployed my SSIS package to SQL Server 2005.

I have done the following:

1) Gave the sql agent account permissions to the msdb database and other databases

2) Checked the paths for "Data Sources" under properties of my agent in the SSIS properties

3) Set the authentification to Windows and used the sql agent account (a domain account that I setup) to run the agent. I also tried my login. So it's not a permission issue, because I do not get permission errors...it's past this.

What works:

1) Other SQL Agent packages...they are not running an SSIS package, just SQL

2) Running the SSiS package by right-clicking on it in Management Sudio. No errors found in the execution and the package does it's intended function

3) Package runs fine with absolutely no errors in VS 2005

What doesn't work:

1) My agent that points to my SSIS package. All this agent does is run that package. When I run the agent, I get a minimal error message in the job history logs stating the following error message:

03/09/2006 12:49:58,Run EN Process,Error,0,BG-22SQL,Run EN Process,(Job outcome),,The job failed. The Job was invoked by User domainmy_account. The last step to run was step 1 (Run EN SSIS Package).,00:00:02,0,0,,,,0


03/09/2006 12:49:58,Run EN Process,Error,1,BG-22SQL,Run EN Process,Run EN SSIS Package,,Executed as user: domainsqlagent_account. The package execution failed. The step failed.,00:00:01,0,0,,,,0

View 4 Replies View Related

SQL Server Agent Scheduled Jobs Question

Apr 30, 2007

Hi,

I have couple of questions about the scheduler:

I'd like my packages to run only on weekdays and disable a package if one of the first two packages fail. is it possible to do it?



Thanks

Gulden

View 1 Replies View Related

Merge Replication Failing With Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server

Sep 28, 2007



Hi,

I have merge replication configured over VPN and the replication is failing to connect to the subscriber through Named pipes with the error Named Pipes Provider, error: 40 - Could not open a connection to SQL Server ......

Actually , I am having issues with Named pipes connection from publisher to subcriber. I have enabled the protocols and also enabled the remote connections using both tcp/ip and named pipes. I have created the client network alias using named pipes

Also, due to security reasons only few ports have been released on the subscriber. Please provide me some information on the following.
1) Does any port specifically be released for listening on named pipes? i am able to connect using tcpip with out any issues as 1433 has been released.
2) How to configure the replciation to use tcpip and not named pipes (guess by default it will use named pipes)?




View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved