Export Select Query To Delimited File

The following code  will output the results of the query  (select * from  logic..temp1) to a CSV file

Please be aware that the output path will be to the sql machine if you use c:\.  Use \\servername\folder\filename.extention  to output the file to another machine.




declare @outputpath varchar(800) ----------Varchar(max) is not allowed
declare @sql varchar(8000)


set @outputpath = '\\SERVER\Shared_Drive\nick.csv'   --------- Set the Path


select @sql = 'bcp "select * from  logic..temp1"  queryout ' + @outputpath + ' -c -t, -T -S'  + @@servername

print @sql

exec master..xp_cmdshell @sql



the file will then appear in the appropriate folder.
To change the delimiter change the yellow Highlighted Character. for a pipe you will have to replace it with ^|  so the line looks like this ' -c -t^| -T -S'

Moving MS Sql to another sever.

I had the need to swop out a SQL server and after trawling the net finally came up with a plan and discarded it at first contact with the job and did this.

Get the new server ready by duplicating the drive set up ie if there is an cd drive on E then the new server should have the cd on E.

·         Do Not put it on the network.
call it the same name as the old one, give it the same ip(S).

·         Clean the data tables of any unwanted data shrink and defrag the tables.

·         Complete a SQL back up of the database.

·         Make sure that there are no users attached clear all locks.

·         Detach the database(s) including the MSDN database.

·         Copy the data and log files onto a portable drive.

·         Remove the server from the network.

·         Attach the new server to the network.

·         Copy the data and log files to the new server.

·         Attach the databases you will have to detach the MSDN database and rename the file before copying the new one into its correct folder.

·         Check that the user has come across properly and that all tables are correct.

·         check some clients so that you can see they are working correctly.

·         Before letting the users and processes loose check that SQL mail is configured and working, this should be done while no one is logged on as it requires a lock on the database.

I have used this to swop out a server it works there are some tweaks here and there that need to be done but nothing major.

Good luck