SQL Server Performance Forum – Threads Archive

Bulk Insert Error

Hi, I have created a stored procedure which truncates a table, and then populates it from a text file using by bulk insert. It all works perfectly fine when I execute the stored procedure from within Query Analyzer, however, when I attempt to execute if as a SQL Agent Job, it fails. Profiler gives me the following error: OLE/DB Provider ‘STREAM’ IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error. I suspect that the problem may be caused by security implications. The Job is configured to run as the same user as when I ran it through Query Analyzer. Please can anyone enlighten me to what I may be overlooking?

Hi, Hope your SQl server instance has latest service packs, seen couple of smilar issues where the SP2 or SP3 was not applied. can you run the follwing query and post the output to confirm the version and SP. SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
Cheers
Sat

Many thanks for your prompt response. It surprises me how I can seem to spend ages trying to solve a problem, and then when I post a request for assistance, I often seem to be able to solve it myself within minutes after. I enabled the Job Step logging (history), and expanded the details of the log file to find that there was a problem parsing date values. Its the famous DATEFORMAT problem. It would seem that when I run it from Query Analyzer that the date format is ‘dmy’, yet when run it as SQL Agent Job that the format is ‘mdy’. So I simply added ‘SET DATEFORMAT dmy’ to the Stored Procedure. Yes, I am using SP 4 for SQL 2000. Thanks again <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

Thx for posting your solution and problem ….I have a lot to learn in coming years!!!! Cheers
Sat

]]>