Output table to tab-delimited file

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 2005 Forums

Transact-SQL (2005)

Output table to tab-delimited file

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 12:20:45

I found this code online of how to export a table to an output file, but I have a question about it.

select @sqloutput = 'bcp test..Concentra out
c:\bcp\concentra.txt -c -t| -T -S' + @@adm-vm-sql-02

exec Test..xp_cmdshell @sqloutput

My servername, you see, contains dashes. It doesn't like this. I tried placing it in brackets but it doesn't like this either.

How can I get it to recognize my servername?

Your help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:23:21

is @@adm-vm-sql-02 your server name?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 12:24:17

adm-vm-sql-02 is server name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:28:31

try


select @sqloutput = 'bcp test..Concentra out
c:\bcp\concentra.txt -c -t| -T -S [adm-vm-sql-02]'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 12:30:55

Hey, I think that will work. I also just realized that I may not even need to add the servername at all. If it isn't supplied, I think it would just default to the localhost which is what I'm working on.

Thanks so much for your help.

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 14:29:11

Well I think the syntax is correct. When I execute it, though, the file is not being created. Any ideas as to what I may be doing wrong?

select @sqloutput = 'bcp test..Concentra out
c:\bcp\concentra.txt -c -t| -T -S [adm-vm-sql-02]'

exec Test..xp_cmdshell @sqloutput

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-18 : 14:40:03

Try this:

select @sqloutput = 'bcp test..Concentra out c:\bcp\concentra.txt -Sadm-vm-sql-02 -c -t| -T'
exec Test..xp_cmdshell @sqloutput

You can't have a line break in a bcp command. I move the -S parameter and removed the brackets, I'm not sure the syntax you had would work.

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 14:50:17

Okay, I placed it all on one line as you suggested. But the file still will not show up.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 15:11:59

I've seen bcp throw up on all kinds of things...reserved words..

But who set up a server with a name like this?

And is there an instance?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 15:13:02

quote:
Originally posted by fralo

Okay, I placed it all on one line as you suggested. But the file still will not show up.

Try it with double quotes

-S"adm-vm-sql-0"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-18 : 15:13:02

My guess is it's a permissions issue. I have servers with dashes in the name and it works fine for me. Are you getting any error message from the xp_cmdshell call?

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 15:21:19

Double quotes around servernmae didn't change anything. I'm not getting any errors when I run it. I'm beginning to think it may be permissions. I checked my login and it has public and sysadmin roles.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-18 : 15:26:45

Try this:

select @sqloutput = 'bcp test..Concentra out c:\bcp\concentra.txt -S. -c -t| -T'
exec Test..xp_cmdshell @sqloutput

If that doesn't work from xp_cmdshell, try running just the bcp command on the command line on that server, see if you get an error.

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 15:37:41

I tried various variations of running bcp from command prompt and it didn't seem to like the option -T.

'-T' is not recognized as an internal or external command.

Same with -S

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 15:39:29

post your code that you executed

you're doing something wrong

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 15:45:36

Thanks so much for your help. I really appreciate it. Here's my store proc.


USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[ConcentraPopulate]    Script Date: 11/18/2010 08:46:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ConcentraPopulate]
AS

DECLARE emp_cursor CURSOR FOR
 SELECT top 500 EMPID, SSN, LNAME, FNAME, MNAME
   FROM [ESO-CAD].EMPMAST.DBO.EMPMAST A
  WHERE A.AGENCY = 'ECSO' and a.hide = '0'

DECLARE @empid varchar(12);
DECLARE @ssn varchar(12);
DECLARE @lname varchar(20);
DECLARE @fname varchar(20);
DECLARE @mname varchar(20);
DECLARE @sqloutput varchar(8000);

BEGIN
	
	SET NOCOUNT ON;
	
	OPEN emp_cursor
	
	FETCH NEXT FROM emp_cursor INTO @empid, @ssn, @lname, @fname, @mname
	
	WHILE @@FETCH_STATUS = 0
	BEGIN


        SELECT COUNT(*)
          FROM [ECSO-VM-PMP].PowerDMS.dbo.SurveyResultAnswers b,
               [ECSO-VM-PMP].PowerDMS.dbo.SurveyResults c
         WHERE c.UserID = '612919' and c.SurveyResultId = b.SurveyResultID
           AND b.SurveyAnswerID = '1' and c.surveyid = 'Test 3000'
         
        IF @@ROWCOUNT > 0
          --Insert all Concentra info into table
        BEGIN
           INSERT INTO Concentra (SSN,lastname,firstname,middlename)
            VALUES (@ssn,@lname,@fname,@mname)      
        END
        ELSE
          --Insert basic Concentra info into table
        BEGIN
           INSERT INTO Concentra (ssn,lastname)
            VALUES (@ssn,@lname)
        END
        FETCH NEXT FROM emp_cursor INTO @empid, @ssn, @lname, @fname, @mname

	END
	
	CLOSE emp_cursor
	DEALLOCATE emp_cursor
	
--select @sqloutput = 'bcp test..Concentra out c:\bcp\concentra.txt -c -t| -T -S [adm-vm-sql-02]'
--select @sqloutput = 'bcp test..Concentra out c:\bcp\concentra.txt -S[adm-vm-sql-02] -c -t| -T'
select @sqloutput = 'bcp test..Concentra out c:\bcp\concentra.txt -S. -c -t| -T'
  
    exec Test..xp_cmdshell @sqloutput
   
END

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 16:56:30

Well I found something that works.

This command works:

select @sqloutput = 'bcp test.dbo.Concentra out c:\bcp\concentra.txt -c -T'

But if I insert the pipe delimited part it doesn't:

select @sqloutput = 'bcp test.dbo.Concentra out c:\bcp\concentra.txt -c -t| -T'

fralo
Posting Yak Master

161 Posts

Posted - 2010-11-18 : 17:05:36

I think I finally figured this thing out.

Coding the pipe-delimited parameter like this works.

select @sqloutput = 'bcp test.dbo.Concentra out c:\bcp\concentra.txt -c -t "|" -T'

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 19:00:51

cool...I use pipe tilda pipe as a delimiter...and I needed double quotes..just thought it was because of a 3 byte del

good luck and have a

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources