extracting a query into a text file (equivalent to Oracle's spool command)
in [Using MS SQL Server]
|
Prev: Keeping rows with the minimum difference between a column and a given parameter
Next: SQL Database Jobs for DBA'a with VMWare and ESX in TV Networks
From: George Lewycky on 3 May 2010 09:58 Hello. Im an Oracle transplant to the SQL Server 2008 adjusting and enjoying many new features but missing some Oracle features I cant find the equivalent too. One of them being the best secure technique to extract a query into a text file to then be FTPd to another machine. This will be a daily scheduled or triggered task. In my Oracle days a simple spool command did the trick From what I can find so far BCP seems to be best solution, but Im trying to keep this in a T-SQL procedure without using the xp_cmdshell. BCP.EXE "select CustomerID, CompanyName from Northwind..Customers where City='London'" queryout LondonCustomers.csv -c -CACP -t, -T Any ideas, suggestions or websites would be really appreciated George
From: Plamen Ratchev on 3 May 2010 11:07 There is not a straight forward method to extract data into text file in SQL Server. Especially if you want to do this from T-SQL and avoid using xp_cmdshell. You can build something using CLR, here is one example: http://www.mssqltips.com/tip.asp?tip=1662 -- Plamen Ratchev http://www.SQLStudio.com
From: Erland Sommarskog on 3 May 2010 17:30 George Lewycky (gelewyc(a)nyct.com) writes: > I�m an Oracle transplant to the SQL Server 2008 adjusting and enjoying > many new features but missing some Oracle features I can�t find the > equivalent too. > > One of them being the best �secure� technique to extract a query into > a text file to then be FTP�d to another machine. This will be a daily > scheduled or triggered task. > > > In my Oracle days a simple �spool� command did the trick > > From what I can find so far BCP seems to be best solution, but I�m > trying to keep this in a T-SQL procedure without using the > xp_cmdshell. > > BCP.EXE "select CustomerID, CompanyName from Northwind..Customers > where > City='London'" queryout LondonCustomers.csv -c -CACP -t, -T > The best solution may be to use SQL Server Integration Services, but I have never used SSIS myself. If this is to be scheduled, you could run it from SQL Server Agent as a command-line task that invokes BCP. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: George Lewycky on 3 May 2010 17:48 On May 3, 5:30 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > George Lewycky (gele...(a)nyct.com) writes: > > Im an Oracle transplant to the SQL Server 2008 adjusting and enjoying > > many new features but missing some Oracle features I cant find the > > equivalent too. > > > One of them being the best secure technique to extract a query into > > a text file to then be FTPd to another machine. This will be a daily > > scheduled or triggered task. > > > In my Oracle days a simple spool command did the trick > > > From what I can find so far BCP seems to be best solution, but Im > > trying to keep this in a T-SQL procedure without using the > > xp_cmdshell. > > > BCP.EXE "select CustomerID, CompanyName from Northwind..Customers > > where > > City='London'" queryout LondonCustomers.csv -c -CACP -t, -T > > The best solution may be to use SQL Server Integration Services, but > I have never used SSIS myself. > > If this is to be scheduled, you could run it from SQL Server Agent as > a command-line task that invokes BCP. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text - > > - Show quoted text - for some reason my manager doesnt want me using SSIS for this task but ill re-iterate to him
From: Erland Sommarskog on 3 May 2010 18:01
George Lewycky (gelewyc(a)nyct.com) writes: > for some reason my manager doesnt want me using SSIS for this task > but ill re-iterate to him There may be sound reasons for it. If you don't use SSIS elsewhere, and this is a tiny fringe thing, adding SSIS adds a whole of complexity and requirements on staffing. As I said, I'm not using SSIS myself, but I felt obliged to mention it, since it seems to be the standard solution for this kind of thing. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |