extracting query into a text file for SQL Server (equivalent to Oracle's spool command) Options?

Question

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

Answer 1

Have you looked into SSIS (SQL Server Intigration Services)? That may be better for what you are trying to do. I don't know Oracle, so I don't know exactly what SPOOL does. Welcome to the world of SQL Server. Glad to see you are no longer influenced by the Dark Side :) J/K...

Answer 2

Oracle Spool Options

Answer 3

in accordance to the question on uploading the Excel 2007 records to sq. Server 2005, i could prefer to furnish you the techniques as follows: a million. Connection string challenge: Please verify in case you may connect the two Excel 2007 and sq. Server 2005 with the aid of fact the information source effectively. The excelConnection seems to be locate as you presented. then you definately could additionally upload "HDR=confident" to show that the 1st row includes columnnames, no longer records. "HDR=No;" exhibits the choice. this would ward off the 1st row puzzled whilst uploading to the sq. tables. 2. Please try your ODBC sq. Server connection string in order that the relationship to sq. Server is actual. different than the ODBC, you additionally can use SqlConnection and execute the SqlCommand merchandise with the indisputable fact that incorporates the excelConnection on your VB.internet code, yet no longer purely combine the full in one sq. fact. right here article exhibits you the way: Import Excel sheet into table 3. different than the above methods, you may planned to apply SqlBulkCopy class to import. right here article exhibits you the occasion: Import Excel Spreadsheet records into sq. Server Database table using SqlBulkCopy 4. there's a KB article in MSDN on the subject be counted. in spite of the actuality that the article isn't utilized for sq. Server 2005, you additionally can learn on sq. Server records Transformation centers (DTS), Microsoft sq. Server 2005 Integration centers (SSIS), sq. Server related servers and sq. Server allotted queries. they're efficient on your paintings. a thank you to import records from Excel to sq. Server wish which could assist you.