Hello.

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


Any ideas, suggestions or websites would be really appreciated

George

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

George Lewycky (gelewyc (AT) nyct (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

On May 3, 5:30*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:


for some reason my manager doesnt want me using SSIS for this task
but ill re-iterate to him

George Lewycky (gelewyc (AT) nyct (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

thanks Erland I'll mention this to my manager

I was hoping for someone from SQL Server & Oracle world to catch this