create a variable-length text file using bcp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

create a variable-length text file using bcp

Author

Topic

skc40
Starting Member

34 Posts

Posted - 2013-11-09 : 20:14:46

Hi all,
The [Data] field is varchar(100) in Temp_Table and contains records of various lengths. e.g. 100, 50, 25.
I want to create a text file (temp_data.txt) using sql cmd.

Below query will produce a text file of 100 characters for all records.
But what I want is a variable length file as indicated above.

sqlcmd -b -Q "SET NOCOUNT ON; SELECT [Data] from Temp_Table" -o "Temp_Data.txt"

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-09 : 20:28:38

Use bcp.exe instead:

bcp "SELECT [Data] from Temp_Table" queryout Temp_Data.txt -Sserver1\instance1 -T -t, -c -r\r\n

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

skc40
Starting Member

34 Posts

Posted - 2013-11-09 : 21:09:40

[quote]Originally posted by tkizer

Use bcp.exe instead:

bcp "SELECT [Data] from Temp_Table" queryout Temp_Data.txt -Sserver1\instance1 -T -t, -c -r\r\n

Tara, thanks for your help.

However, i am not that familiar with using bcp.

If the query can only be executed by a UserName on a specified server/database and saved in a specified location as Temp_Data.txt (11.11.11.11\Temp\Temp_Data.txt)
and what would be the new bcp query?

Something like this?

bcp -U "userName" -P "pwd" -S "ServerName" -d "DatabaseName" "SELECT [Data] from Temp_Table" queryout "11.11.11.11\Temp\Temp_Data.txt" -Sserver1\instance1 -T -t, -c -r\r\n

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-10 : 01:50:23

see
http://visakhm.blogspot.in/2013/10/different-ways-to-export-sql-server.html

the file path should be like \\11.11.11.11...
also -T is for trusted connection (Windows authentication)
If you use sql login you should use -U username -P password instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

skc40
Starting Member

34 Posts

Posted - 2013-11-10 : 13:26:25

bcp "SELECT [Data] FROM Temp_Table" queryout "\\11.11.11.11\Temp\Temp_text.txt" -S "110.11.100.00"\"Test" -U "User_Name" -P "pwd" -t, -c -r\r\n'
User name not provided, either use -U to provide the user name or use -T for Trusted Connection

Visakh, I followed your instructions, but somehow-- not able to get this working. Above is the error message that I received.

Following are the variables.

Query: SELECT [Data] from Temp_Table
Output File: \\11.11.11.11\Temp\Temp_Text
DB_Server: 110.11.100.00
Database: TEMP
User: User_Name
password: pwd
File Type: Variable Length

Could you please help here.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-10 : 19:31:39

bcp "SELECT [Data] FROM Temp_Table" queryout \\11.11.11.11\Temp\Temp_text.txt -S 110.11.100.00\Test -T -t, -c -r\r\n

Is Temp a shared folder? If you get an error regarding the path, then you need to fix it so that you are referencing a shared folder on the network resource. Otherwise, save it locally.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

skc40
Starting Member

34 Posts

Posted - 2013-11-10 : 20:02:24

TKizer,

I tried your solution and used SQL connection instead.

C:\WINDOWS\TEMP>bcp "SELECT [Data] FROM Temp_Table" queryout "\\11.11.11.11\Temp\Temp_text.txt" -S "110.11.100.00\Test" -U "User_Name" -P "pwd" -t, -c -r\r\n

But received error message as below...
ERROR MESSAGE

SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired

The Server Name is actually 110.11.100.00 and the Database Name is Test.

The file path is correct as well and need to save it so that the file can be attached and send it to the respective party (part of the job).

Could you please advise.

Thanks

skc40
Starting Member

34 Posts

Posted - 2013-11-10 : 20:46:53

TKizer, finally able to adjust the query below :)

C:\WINDOWS\TEMP>bcp "SELECT [Data] FROM Test.dbo.Temp_Table" queryout "\\11.11.11.11\Temp\Temp_text.txt" -S "110.11.100.00" -U "User_Name" -P "pwd" -t, -c -r\r\n

Output was as expected, however there's an extra line as below

HEADER RECORDCRLF--- RECORD LENGTH 60
CRLF
EMPLOYEE RECORDCRLF --RECORD LENGTH 100
CRLF

EMPLOYEE RECORDCRLF--RECORD LENGTH 100
CRLF
TRAILER RECORDCRLF--RECORD LENGTH 20
CRLF

skc40
Starting Member

34 Posts

Posted - 2013-11-10 : 20:58:37

TKizer/Visakh-- thank you all for your help. Finally was able to generate the desired result-set.

Final query below did the trick :)
bcp "SELECT [Data] FROM Test.dbo.Temp_Table" queryout "\\11.11.11.11\Temp\Temp_text.txt" -S "110.11.100.00" -U "User_Name" -P "pwd" -t, -c

TY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 01:43:38

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-11 : 12:23:43

quote:
Originally posted by skc40

TKizer/Visakh-- thank you all for your help. Finally was able to generate the desired result-set.

Final query below did the trick :)
bcp "SELECT [Data] FROM Test.dbo.Temp_Table" queryout "\\11.11.11.11\Temp\Temp_text.txt" -S "110.11.100.00" -U "User_Name" -P "pwd" -t, -c

TY

You're welcome, glad to help. The -r\r\n is my typical EOL option. Glad you figured out to remove it to get the formatting you needed.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources