SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 create a variable-length text file using bcp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skc40
Starting Member

29 Posts

Posted - 11/09/2013 :  20:14:46  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 11/09/2013 :  20:28:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/09/2013 :  21:09:40  Show Profile  Reply with Quote
[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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/10/2013 :  01:50:23  Show Profile  Reply with Quote
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
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/10/2013 :  13:26:25  Show Profile  Reply with Quote
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

Edited by - skc40 on 11/10/2013 13:27:40
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 11/10/2013 :  19:31:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/10/2013 :  20:02:24  Show Profile  Reply with Quote
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
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/10/2013 :  20:46:53  Show Profile  Reply with Quote
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
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/10/2013 :  20:58:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/11/2013 :  01:43:38  Show Profile  Reply with Quote
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 11/11/2013 :  12:23:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000