bcp creating a file with one query not with other?

[croiva25]

this is a query
DECLARE @query varchar(8000)
SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,
b.UnitsDiscarded,b.DateEntered,b.DateCompleted,b.CompiledBy FROM Ivana_test.dbo.Units b
INNER JOIN Ivana_test.dboHospitals a ON (a.HospitalID = b.HospitalID)
INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)
INNER JOIN Ivana_test.dboFateOfProducts d ON (d.FateID = b.FateID)
ORDER BY a.HospitalID" queryout "c:\test.txt" -c -SCenausttrim -Usa -Pcobalt123'
EXEC master.dbo.xp_cmdshell @query

that gives me all the bcp specs as a result
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

and then this query with is just a test query
DECLARE @query NVARCHAR(2000)
SELECT @query = 'bcp "SELECT * from Ivana_test.dbo.Products" queryout c:\test.txt -c -SCenausttrim -Usa -Pcobalt123'
EXEC master.dbo.xp_cmdshell @query

creates a file and everything goes through ok

Could somebody tell me what is the story,can bcp utility support more complex queries.

Thanks

 

[hmckillop]

If you remove the carriage return characters from the end of each line in the query, the bcp command needs to be executed on one line with no return/enter characters, try this and see if it works


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[croiva25]

Thanks for your help, I realized that the hard way too,it just didn't make sense.

Does anybody know how to use properly -f format_file paramater,I have tried just including it in the bcp line but it overwriting -c paramter.

What I want to do is to create a file that is as formatted as possible so that the client doesn't have to go through that once they receive the file.

any help appreciated....

 

[hmckillop]

I have used the format file many times to input different files from different sources. Your best bet is to look at books online, under bcp utility -> Format files. This gives you an example of a format file and allows the output to be in this specified style. If you give me an example of your required output, I should be able to work out a format file for your query (also would require the field types/sizes)


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[croiva25]

I have had a look on the Books Online but can't quite write it properly... so if you have time your help would be great

My fileds are:
HospitalName varchar 255
HospitalCode varchar 50
ProductName varchar 255
UnitsDiscarded numeric
DateEntered datetime
DateCompleted datetime
CompiledBy varchar 50

and these field names should also be column names and collation name is "SQL_Latin1_General_CP1_CI_AS"
and I need to have this in .csv so each filed has its own column properly formatted accordig to data types

If you can help with this that would be great

 

[hmckillop]

Having a look at this now, will post output when I get a chance.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[stapet]

Problem with newline character in SP.

SET @UploadDir = ‘\\MyServer\data\’
SET @UploadFileName = ‘test.txt’

SET @UploadData = ' echo ' + @HoldRecNo + ',' + @Name + ',' + @Terms + ',' + @Description + ',' + CONVERT(char(8), @DueDate,1) + ',' + CONVERT(char(8), @PayDate,1) + ',' + CONVERT(varchar(15), @Amount) + ',' + @InvoiceNo + ',' + CONVERT(char(8), @InvoiceDate, 1) + ',' + CONVERT(varchar(15), @Amount) + ',' + ‘ >> ' + @UploadDir + @UploadFileName
EXEC master..xp_cmdshell @UploadData



I am having a problem writing data to a text file, via a stored procedure. The problem is the variable @Name contains: ‘C&W Store’.
Once it hits the & sign, it treats it like a new line or return character. I thought about converting the & sign, but the problem is this text
file is going to be uploaded by another program and is uploading by @Name, so if I convert the & sign, the data will never be
uploaded because it won’t be able to find the matching name. Does anyone have any ideas on getting around this? Any help would be
greatly appreciated.

 

[stapet]

sorry , i did not mean to post the previous message here.