Author |
Topic |
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 08:48:07
|
Hi,I need to output a tab-delimited file with column headers at the top of the file. By googling I found that there is no straight forward way of doing this. It would be nice if there was a simple parameter to add to the bcp command to accomplish this. Anyway, here's a block of code for what I have thus far. Your help is greatly appreciated.CREATE VIEW ConcentraOut AsSELECT 'Employee','LastName','FirstName','MiddleName','Address','[Union Name]','[Emergency Contact]',0 As SeqNoUNIONSELECT [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],1 As SeqNo From Concentra_Out exec master..xp_cmdshell 'bcp "select [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact]from ConcentraOut" out c:\bcp\concentra.txt -c -t "|" -T' |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 09:08:19
|
Think you were planning to have an order by SeqNo in the bcp query.Might also what to change the union to a union all.Is Employee a character column? Otherwise you will have to convert it.I always name views with something to distinguish them - like v_ConcentraOut. Just to make it clear to people that it's not a table and may cause issues.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 09:14:56
|
Yes I did mean to place an order by clause. And Employee is a character column.The error I'm getting is 'Incorrect syntax near the keyword 'VIEW'. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 09:33:36
|
create view needs to be the first statement in a batch - are you including something before it in the execution?I take it the exec is being executed separately.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 09:39:21
|
Yes, my code is part of a stored procedure. I have code which populates a table. After that, I need to extract the data from the table and generate an output file. Any ideas?Thanks for your help thus far. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 09:45:46
|
The create view shouldn't be part of the SP.The SP just populates Concentra_Out then uses the view for the bcp statement.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 10:03:59
|
So I should simply create a view against the database and then query from it within the SP? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 10:10:37
|
Yep.A view is a permanent object.You could also dispense with the view altogether and include the code in the bcp statement.Also could make the view (you need to put colname in the first select statement in the view)CREATE VIEW ConcentraOut Asselect top 100 percent [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact]from(SELECT [Employee]='Employee',LastName='LastName','FirstName','MiddleName','Address','[Union Name]','[Emergency Contact]',0 As SeqNoUNION ALLSELECT [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],1 As SeqNo From Concentra_Out) aorder by SeqNogothen you can treat it like a table in the bcp rather than using a query.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 10:44:37
|
My file is now not being generated. Any reason you can think of?exec Test..xp_cmdshell 'bcp "SELECT [Employee Number],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],From v_ConcentraOut order by SeqNo, [Employee Number]" out c:\bcp\concentra.txt -c -t "|" -T' |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 10:46:39
|
"out" should be "queryout"That should give you an error which you need to trap and alert.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 11:03:53
|
It still doesn't work. I tried running a variation of it from the dos prompt to see if that would work but I get an error there as well. Does this look right from dos prompt?bcp "Select * from test..v_concentraout" queryout c:\bcp\concentra.txt -c -t "|" -T |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 11:11:54
|
If it's run on the server against a default instance then I think it should be ok. I include the -S parameter to explicitely specify the server.What is the error you get.It's always best to try the simplest possible if you can't get it to workbcp master..sysobjects out c:\bcp\concentra.txt -c -T -Smyserver==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 11:15:12
|
The error is:SQLState = S1000, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 11:20:41
|
That's talking about the file you are tryong to create.Does the folder c:\bcp\ exist on the m/c you are runnin the bcp?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-04 : 11:23:24
|
Yep it does exist. |
|
|
lonely cowboy
Starting Member
1 Post |
Posted - 2014-03-24 : 18:20:19
|
EXEC DATABASE_NAME..xp_cmdshell 'bcp "select ''id'' as id, ''detail'' as detail, ''creation_date'' as creation_date UNION ALL select convert(varchar,id), detail, convert(varchar,creation_date) from DATABASE_NAME.dbo.TABLE" queryout "C:\file.txt" -c -t, -r \n -T -S "ServerName"'Try this |
|
|
|