SQL Server agent wont write to table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL Server agent wont write to table

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 10:37:26

Hi i have the following log table
CREATE TABLE [dbo].[dataload_logging](
[Log_key] [int] IDENTITY(1,1) NOT NULL,
[log_date] [datetime] NULL,
[package_name] [nvarchar](100) NOT NULL,
[starttime] [datetime] NULL,
[executed_by] [nvarchar](60) NULL,
[status] [nvarchar](10) NULL
) ON [PRIMARY]

I also have a SSIS package that writes to the log table.
this is the expression

CONNECTION TYPE IS OLE DB and Resultset is None

"Insert into dbo.dataload_logging
([log_date],[package_name],[starttime],[executed_by] ,[status]) values('"+ (DT_WSTR,30) MONTH(GETDATE())+
"-"+(DT_WSTR,30) DAY(GETDATE())+"-"+(DT_WSTR,30) YEAR(GETDATE())+"','" +@[System::PackageName]+"'
, '" + (DT_WSTR,30)@[System::StartTime] +"'
, '" + (DT_WSTR,60)@[System::UserName] +"','Success')"

This works when i ran the SSIS package (it writes to the table), but when i add the SSIS package to SQL Server Agent, it executes fine but doenst write to the table. I have a lot of jobs doing the same thing with the same code, but some work and some dont. I need help. Is there a setting i missing

This is the sql job
Type is Operating System

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe /DTS "\MSDB\Web_Analytics\Loading_Dimension_Tables" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

Loading_Dimension_Tables is the name os the SSIS package

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 11:37:18

Is there any error in the log file?
When does this tep execute? Is there any condition around it?
What are the permissions on the table? Or the user account running the job?

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 11:45:44

This rans after doing some data transfer. Im trying to write to a table when the process completes successfully.
There are no conditions on it, except success on the preceding constraint.
The tables permissions and all are fine, because other packages doing the same thing write to the table.

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 12:25:28

OK i found a error
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login failed

where do i fix this

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 13:08:26

Nice one.
Look in the ErrorLog on the SQL Server, for an error that occured at the same time as the "Login failed" error was reported above. It should have something like Error: 18456, Severity: 14, State
What is the state of your error message?
This will help resolve the issue. Match it to the states in this table: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 13:10:47

This is the SQL State number
IM004

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 13:13:31

Can you post the two lines from the SQL Server Errorlog?

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 13:15:35

I cant find a error message number. I cant see it anywhere

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 13:16:46

Date,Source,Severity,Message
09/10/2009 01:45:07,,Information,[098] SQLServerAgent terminated (normally)
09/10/2009 01:45:07,,Error,[382] Logon to server '(local)' failed (DisableAgentXPs)
09/10/2009 01:45:07,,Error,[165] ODBC Error: 0<c/> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
09/10/2009 01:45:06,,Error,[382] Logon to server '(local)' failed (SaveAllSchedules)
09/10/2009 01:45:06,,Error,[165] ODBC Error: 0<c/> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 13:21:32

In the SSMS, check the:
- SQL Server ErrorLog
- SQL Agent log

Where did you get the IM004 from?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 13:22:26

Hmm... Is the SQL Agent service running?

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 13:42:22

Yes

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-09-10 : 13:45:51

that error is from this morning, sorry. This is the error in the agent log (changed too ... for security purposes)

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login failed for user 'SQLServers'.". End Error Error: 2009-09-10 12:53:36.28 Code: 0xC020204A Source: DFT staging_... SRC staging_... [1] Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. End Error Error: 2009-09-10 12:53:36.28 Code: 0xC004706B Source: DFT staging_... SSIS.Pipeline Description: "component "SRC staging_..." (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Progress: 2009-09-10 12:53:36.28 Source: DFT staging_... Validating: 50% complete End Progress Error: 2009-09-10 12:53:36.28 Code: 0xC004700C Source: DFT staging_... SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-09-10 12:53:36.28 Code: 0xC0024107 Source: DFT staging_... Description: There were errors during task validation. End Error Error: 2009-09-10 12:53:36.28 Code: 0xC00220DE Source: Execute dim_..._staging Description: Error 0xC0012050 while loading package file "C:\Users\...\Documents\Visual Studio 2008

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 17:18:29

quote:
This is the error in the agent log

Really? That format is not what I expect from the Agent log.
These are the logs I mean:
in SSMS --> ServerName --> Management --> SQL Server Logs --> Current
in SSMS --> ServerName --> SQL Server Agent --> Error Logs --> Current

The extract above seems to be from the Job log. This shows the "Login failed for user" message.
Check in the SQL Server Log for any error around 2009-09-10 12:53:36.28. It should look like "Error: 18456, Severity: 14, State"
Can you post that error message?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources