Exporting large amounts of Data from SQL

31/8/2006

Exporting Large amounts of Data from SQL If you have ever had an issue transfering large amount of data from SQL you can use the command line tool to solve this problem. From the command line ||''''''|| is an optional parameter specifying the name of the database. It can only be omitted if the desired database is the default database. || ||''''''|| the name of the table owner. || ||''''''|| the name of the table. || ||''''''|| The name of the comma separated file to generate. || ||''''''|| The SQL Server username to use. This can be left blank if NT authentication is used. || ||''''''|| The password to use for the account. || ||''''''|| The name of the server on which the database resides. This can be omitted if the database is on the local machine (i.e. the same machine. as the bcp command is issued from). || For example This is less bulking and a lot faster than using the built in function within Enterprise Manager.

DTS, Excel and mixed format columns

14/6/2006

DTS, Excel and mixed format columns I recently had a problem DTS importing an Excel spreadsheet which contained columns with mixed text and data values. After a bit of hunting around, I found an extended property IMEX=1. The IMEX=1 property forces everything to text when reading from a source. Easy to change in your connection string, but how do you edit it in DTS? Answer: Dynamic Properties. '''In DTS:''' * Add a Dynamic Properties step (before the copy data step) * Add a new "change" for "Your Connection > OLE DB Properties > Extended Properties" * Give it a constant value of '''Excel 8.0;HDR=YES;IMEX=1'''