November 10, 2009 at 6:48 am
All,
I'm having the following problem:
i have a simple package (some sql tasks, a data flow task, some more sql tasks. nothing exotic there). within this package there is a connection to a microsoft visual foxpro .dbc file.
now because i ran in some issues (i know, nothing exotic and still running into problems...) i started debugging and testing and now i face myself with the following 2 situations:
1. running the package via dtexec command in windows (start>run>cmd>dtexec)
2. running that same package,using the same config file, via that same command, using that same user (i come back on that later) on the same server but trough a sql server job (step: cmdexec)
situation 1 works just fine. awesome!
situation 2... not that awesome 🙁
so since a lot of problems in successfully executing a package under the sql server agent have to do with user logins i created a proxy for the user i'm logged in with. so say this user is 'domain\user1'. i now see in my sysdtslog90 table a couple of records for the dtexec that succeed (1) and a couple of records for the job that failed(2). they both have 'domain\user1' as operator (executing user). so no difference there.
since i use the same dtexec command in both situations, i guess they both use the same configuration file. (i specify the \config parameter in the dtexec command)
i really have no idea what sql server agent is doing to make this dtexec command fail.
has anyone any idea what the problem might be?
just for the record: this is the error i'm getting when execution via sql server job. keep in mind i don't get this error when typing it in cmd en run the command from there.
System.Data.Odbc.OdbcException: ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file drive:\folder1\folder2\folder3.dbc. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr). at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
November 10, 2009 at 7:53 am
Solved.
when running a package via sql server agent, make sure you do not use mapped network drives. always use the UNC notation. make sure this is so in your package, job parameters (file location, config file location) and within the config file itself.
dtexec called under windows knows the mapped network drives. sql server agent doesn't.
probably a lot of you out there allready knew this, (actually i did to, but overlooked one line in my config file) but just a tip for those who bump on this same problem and can't seem to figure out what's causing it.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply