InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 05:43:05 GMT20http://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxIt looks like it's an issue with one of the varchar data columns.You might want to look at the table variable -- maybe bust out the varchar 5/6 to sysname. Then try it again.declare @format table ( rowId smallint identity primary key , colName sysname not null , terminator varchar(6) not null , colOrder varchar(5) not null , fileLength varchar(5) not null );I've got an update to this that I should post. I'll try to get it cleaned up and deliver it this weekend.Let me know how it works.Wed, 03 Nov 2010 17:45:22 GMTSQLNightOwlhttp://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxHi,I'd love to use your format file builder. I am running it on a SQL 2005 instance. I was able to create the stored proc but at run time I get:Msg 8152, Level 16, State 13, Procedure usp_BCPFormatFile, Line 188String or binary data would be truncated.The statement has been terminated.It seems to be referring to:insert @format values(@column, @terminator, @fieldNum, @length)Do you remember seeing that before?Wed, 03 Nov 2010 07:25:46 GMTKen Davishttp://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxThanks for the kind words. I'm in the process of building a utility that will read the file and do a sample "load" based on the format file. I thought about using xp_cmdshell to do this but decided against it in favor of the utility. Some of my reasons for this are: * some/many admins don't permit the use of xp_cmdshell * it would require a table to load the header row (another dependency)Both of those are overcome by using a stand alone application.Tue, 18 Aug 2009 19:52:54 GMTSQLNightOwlhttp://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxPaul, A great start. Any thoughts on extending it to read the first line of thefile on disk. Could use XPcmdshell or write some CLR creating a assemblyand have the function reference it to read the disk file for the header,also could enhance to prompt for field type and lengths. I have hand crafted many a format file for use with BCP. It always was a pain and takes a while. In the data I am dealing with we normally have in excess of a hundred columns so as you can see... very painfull to do it manually. We use BCP and in many cases Bulk Insert (Which is faster), using these sameformat files. We are normally loading 8 to 10 million rows, so we need somethingwith performance. At any rate great job !Tue, 18 Aug 2009 07:58:24 GMTanbeanhttp://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxLooks like I need to re-read my comments and use spell check before I post to the SQL Server Central. :-)Fri, 14 Aug 2009 06:27:49 GMTSQLNightOwlhttp://www.sqlservercentral.com/Forums/Topic765895-1613-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/SQL+2005%2f8/67764/">BCP Format File Builder</A>[/B]Wed, 05 Aug 2009 16:54:47 GMTSQLNightOwl