I'd like to go arounfd the block, but the Shuttle is grounded, so I guess I'll have to build a new one...Or...create a sproc like the one below and create a scheduled job on the server...CREATE mySPROC @FilePath varchar(200) @File_Name varchar(55) AS BEGIN DECLARE @filePathAndName varchar(255), @cmd varchar(2000), @Command_String (3000) TRUNCATE TABLE myTable1 TRUNCATE TABLE myTable2 Select @FilePathAndName = @FilePath + '\' + @File_Name SET @cmd = 'bcp ' + @db_name + '..LOAD_TEMP in ' + @FilePathAndName + ' -t"" -c -S' + @@servername + ' -Utaxuser -Ptaxuser' SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' Exec(@Command_String)/* Some SQL like the following to follow the logic you need to populate your final tables */ INSERT INTO FINAL_TABLE ( TAx_Year , Company_Cd , Admin_Sys_Cd , BUF_Cd , Admin_Unit_Cd , Contract_No , TIN , TIN_Type , Return_Type_1099 , W2_Type , State_Cd , Tax_Event_Dt , State_Tax_WH , Prev_State_Tax , Fed_Tax_WH , Prev_Fed_Tax , Wages , Soc_Sec_Wages , Soc_Sec_Withheld , Medicare_Wages , Medicare_Withheld , ADV_EIC , Emp_First_Name , Emp_Last_Name , Total_Record_Count ) SELECT Substring(DataRow,1,4) AS TAx_Year , Substring(DataRow,5,3) AS Company_Cd , Substring(DataRow,8,3) AS Admin_Sys_Cd , Substring(DataRow,11,3) AS BUF_Cd , Substring(DataRow,14,1) AS Admin_Unit_Cd , Substring(DataRow,15,20) AS Contract_No , Substring(DataRow,35,9) AS TIN , Substring(DataRow,44,1) AS TIN_Type , Substring(DataRow,45,1) AS Return_Type_1099 , Substring(DataRow,46,2) AS W2_Type , Substring(DataRow,48,2) AS State_Cd , Substring(DataRow,50,26) AS Tax_Event_Dt , CONVERT(money,Substring(DataRow,76,14))/100 AS State_Tax_WH , CONVERT(money,Substring(DataRow,90,14))/100 AS Prev_State_Tax , CONVERT(money,Substring(DataRow,104,14))/100 AS Fed_Tax_WH , CONVERT(money,Substring(DataRow,118,14))/100 AS Prev_Fed_Tax , CONVERT(money,Substring(DataRow,132,14))/100 AS Wages , CONVERT(money,Substring(DataRow,146,14))/100 AS Soc_Sec_Wages , CONVERT(money,Substring(DataRow,160,14))/100 AS Soc_Sec_Withheld , CONVERT(money,Substring(DataRow,174,14))/100 AS Medicare_Wages , CONVERT(money,Substring(DataRow,188,14))/100 AS Medicare_Withheld , Substring(DataRow,202,14) AS ADV_EIC , Substring(DataRow,216,15) AS Employee_First_Name , Substring(DataRow,231,40) AS Employee_Last_Name , Substring(DataRow,271,8) AS Total_Record_Count FROM LOAD_TEMP WHERE Substring(DataRow,1,1) <> 'H' AND Substring(DataRow,1,14) <> 'CONTROL_TOTALS'GO
All I wanted was a cup of coffee....Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! 
In Desparate need of an Import Tool
Posted - 2003-09-25 : 02:53:31
Whenever someone suggests XML for a simple task, remind them we've put men on the moon and built the atomic bomb without XML, or even object-oriented programming, and have done nothing equivalent since with either.