How to script out data export

Do you want to export data to a file ?

Attachment missing.

I will write it for you. Provide more details.

just in case -check BCP utility
BULK INSERT statement
more
Bulk Import and Export of Data (SQL Server)
https://msdn.microsoft.com/en-us/library/ms175937.aspx

As noted, the attachment is missing but you can create an SSIS package using the Import/Export Wizard without running it at that time. If that is not what you mean then I don't understand. The import/export wizard is based upon SSIS. There is no option to not use SSIS with the wizard. It is sometimes called the SSIS Wizard.

To generate a package without running it, at the end of the wizard uncheck the "Run immediately" checkbox and check the "Save SSIS package" checkbox. You can save the package in the file system or in MSDB ("SQL Server"). If you save it in the file system, you will have a "script" but it will be an SSIS package, which is an XML file, and you can run it using the DTEXEC command line utility.

If you save the package in MSDB ("SQL Server" option), you can run it from the SSIS server (connect to it using SSMS to view saved packages).

I want an export script to  flat file for a table. No SSIS package. Attached file again.
CaptureExport.JPG

Try this

EXEC xp_cmdshell 'bcp "select * FROM tableName" queryout "C:\Pawan.txt" -T -SERVERNAME\INSTANCENAME -c -t,'

BCP is a good utility for do what you want but please don't use xp_cmdshell to execute the utility since it's disable by default for security reasons and you'll need to enable it to perform the BCP.
A good alternative is to have a job to run the BCP (change the type to CmdExec) so you can schedule it or run it manually whenever you want. The example below is with SQLCMD but you can replace it with BCP:
Other option is to run BCP directly from the Command Prompt.

there are many ways
you can use sqlcmd https://msdn.microsoft.com/en-us/library/ms180944.aspx
Connecting to a named instance by using Windows Authentication and specifying input and output files:
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>  
--

you can use Power shell code (.NET, etc)..
Invoke-Sqlcmd -Query "Select doc_id from a_doc_macro" -ServerInstance "YourSqlServer" -Database "YourDbName" > \\somenetworkshare\output\a_doc_macro.txt

I haved the following code, but the output returned bad data like the image I attached

	DECLARE @exec_statement AS NVARCHAR(2000)

	SET @exec_statement = 'bcp "SELECT TOP 100 * from table1 " queryout "\\In234\share\Text\testme.txt" -T -c -t"|"'

	EXEC xp_cmdshell @exec_statement

Select allOpen in new window

how to fix it?
Capturebad.JPG

It looks like "|" causing issue. But , I want vertical bar, like this image requirement I attached
CaptureExport.JPG

This should work.

--
       DECLARE @exec_statement AS VARCHAR(2000)

	SET @exec_statement = 'bcp "' + ' Select TOP 100 * from table1" queryout "' + "\\In234\share\Text\testme.txt" + '"-t^| -T -c ' 
		
	EXEC xp_cmdshell @exec_statement

--

Select allOpen in new window

I have this error

Msg 207, Level 16, State 1, Line 4
Invalid column name '\\In234\share\Text\testme.txt'.

Checking ..pls wait.

Below is working on my machine. Pls try and let me know.
To gain access..

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO

Select allOpen in new window

DECLARE  @Command NVARCHAR(4000)=''
	
SET @Command = 'bcp " ' + 'SELECT * FROM [PAWAN].[ABCInvestment].[data].[DateSloter] ' + '"'
                + ' queryout "E:\' + '.csv' + '"' + ' -t^| -T -N'
PRINT @Command
 
EXEC xp_cmdshell @Command

--

Select allOpen in new window

Change below code for your machine.

--
DECLARE  @Command NVARCHAR(4000)=''
	
SET @Command = 'bcp " ' + 'SELECT * FROM [ServerName].[DatabaseName].Schema.TableName ' + '"'
                + ' queryout "E:\' + '.csv' + '"' + ' -t^| -T -N'
PRINT @Command
 
EXEC xp_cmdshell @Command

GO

--

Select allOpen in new window

Don't work. The output file is still wrong code.

Didnt get , what wrong code?

Are you able to get the file as output ?

Have you tried the above one ?

Yes, I did. I have outut file with your code. But if I use built-in export Wizward, the output content is not the same as scripting.

So which is coming correctly?

the built-in is correct

can you post the output of the file you are getting. as an attachment..?

Attached file is the data format output look like. This is what I want
Capturegood.JPG

And wht are you getting with my code?

Here is your output file. See the attached file
Capturenotgood.JPG

if you need '|' separated
check this example

exec xp_Cmdshell 'bcp "select col1, co2  from mytable" queryout "c:\output\youTable.csv"  /c /t"|" /T'

ohhh you need pipe separation. try below please.

--
DECLARE  @Command NVARCHAR(4000)=''
	
SET @Command = 'bcp " ' + 'SELECT * FROM [ServerName].[DatabaseName].Schema.TableName ' + '"'
                + ' queryout "C:\' + '.csv' + '"'  + ' /c /t"|" /T'

PRINT @Command
 
EXEC xp_cmdshell @Command

GO

Select allOpen in new window

I just tried it. It becomes like the attachemnt again
Capturenotgood2.JPG

can you pls post some of your data and  table structure , I want to try it out.

try to add -N

https://technet.microsoft.com/en-us/library/ms162802(v=sql.110).aspx

exec xp_Cmdshell 'bcp "select col1, co2  from mytable" queryout "c:\output\youTable.csv"  /c /t"|" /T /N'

I cannot provide real data here.

The data output should look like this

xxx | xxxxx| xxxx| xxxxx|xxxx
xxx | xxxxx| xxxx| xxxxx|xxxx
xxx | xxxxx| xxxx| xxxxx|xxxx
xxx | xxxxx| xxxx| xxxxx|xxxx


I have colume whose data is a directory like beow

\\Integration\abc\wer\p

It seems the problem is from

select * from

If I lay down each field into select, the output is fine. But I ahve 100 fields for this table.

how to not lay down each field into select?

In that case you have to provide column names like i have given..

Here is the complete working code for you ..

DECLARE  @Command NVARCHAR(4000)=''
	
	SET @Command = 'bcp " ' + 'select Id,Id  from [pawan452801].[master].dbo.testFloats15 "' 
                   + ' queryout "E:\Ramesh' + '.csv' + '"' + ' -c -t^| -T'
 
	PRINT @Command
 
	EXEC xp_cmdshell   @Command

Select allOpen in new window

Ouptut
-----------

NULL
Starting copy...
NULL
13 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (13000.00 rows per sec.)
NULL

Exported files


Hope it helps..

It seems the error doesn't come from select * or select fields, it comes from select how many fields. The table has 50 fields. When I select up to 22 fields, the data export looks good to me. When I select 23 fields output or choose one more field from one of the rest, the data export has unnrecognized data.

My question is if the text file has certain limit? or EXEC xp_cmdshell has certian limit to the output???

how to fix it?

It looks like we have the same issue like this one

http://stackoverflow.com/questions/10995117/xp-cmdshell-query-length-too-large

Well this is the first time I see this. How many columns you have in your table and how many rows you have.?

This table has 41 fields, has millions row. I just export 100 row first and see if it works