Recently I upgraded one of our production servers to SQL Server 2008 on a Windows Server 2008 R2.Once the migration was complete, all processes,jobs looked fine except for one.
I have a daily process that checks for a file on a ftp server,downloads it to the local file server and then bulk inserts the file into multiple databases located on multiple servers.The file is a csv file and I am updating 7 sql server databases located on 7 servers.Some of the servers are managed by other teams and the data is used for multiple processes.
The process started failing and in order to isolate the problem I took the file and manually started bulk inserting into the 7 databases on 7 servers.All the 7 servers are Windows Server 2008 R2 running SQL Server 2008.The account used to bulk insert was a SQL Server account with BulkAdmin,SysAdmin permissions.
I noticed this error only on one of the servers and the rest of the servers the bulk inserts were fine.
So after we isolated the problem we checked for permissions to the file which was located on a network share which had access to everyone and sql account on all the servers.The next step was to check the default protocol used and that led us to the problem.
On the server where the bulk insert was failing the default protocol used was changes to Named Pipes and on the rest of the servers TCP was the protocol.
Bulk Insert failure: server protocol
Bulk Insert success: server protocol
Once the Named pipes was disabled bulk insert was successful.
No comments:
Post a Comment