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.
| Author |
Topic |
|
partha
Starting Member
4 Posts |
 Posted - 2004-09-28 : 12:35:46
|
| can anyone tell me how to avoid generating blank .csv file using bcp command ?The other situation i have come across is, when the file location gets bigger, eg:\\webserver\folder1\folder2\folder3\Myfile_date,the name of the actual file gets truncated, Eg:Actual file name i am looking for is "Myfile_09-28-2004.csv" generated file name is "Myfile_09-28-2004" and .csv gets truncated. if i remove couple of folders(eg:\\webserver\folder1\Myfile_date), then the actual file(Myfile_09-28-2004.csv) is generated. otherwise, the file type is changed. Eg: if it .cs then file c# is generated.Anyway here is the stored procedure DECLARE @bcpCommand VARCHAR(8000), @FileName varchar(50) SET @FileName = REPLACE('\\webserver\folder\'+'myfile_'+ CONVERT(varchar,GETDATE(),101)+'.csv', '/','-') SET @bcpCommand = 'bcp "SELECT * FROM C2CColl..AcctInfo where ExcelExp Is Null order by branchno" queryout ' SET @bcpCommand = @bcpCommand + @FileName + ' -c -t "," -Uuserlogin -Ppassword' EXEC master..xp_cmdshell @bcpCommandHaving trouble with Where condition:can i give SET @bcpcommand = 'bcp "SELECT * FROM MYDB..MYTABLE WHERE MYFIELD="H" and MYFIELD1 = "" " queryout' |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-28 : 12:36:33
|
| Please post the bcp command that you are using.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-28 : 15:08:25
|
| The reason for the name truncation is @FileName being 50 chars - make it bigger.To stop an empty file being generatedif exists (SELECT * FROM C2CColl..AcctInfo where ExcelExp Is Null)begin do the bcpend==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-29 : 17:46:47
|
| String delimitter is a single quote not double quote.'bcp "SELECT * FROM MYDB..MYTABLE WHERE MYFIELD=''H'' and MYFIELD1 = '''' " queryout'==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
partha
Starting Member
4 Posts |
Posted - 2004-09-29 : 18:29:30
|
| Thank you so much for your help NR, i have one more request.the datetime field coming from the table is getting messed up in .csv fileEg: Actual date : 9/24/2004 the same is converted to "00:00.0" under .csv.can you tell me why it does that? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-29 : 21:06:30
|
| Ummmm no.The default format is probably something like yyyy-mm-dd hh:mm:ss.mmmLooks like you are getting the mm:ss.m for some reason.Try explicitely formatting the date.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-09-30 : 10:33:52
|
if you look help for cast and convert you will find a table with date converting styles quote:
Transact-SQL Reference CAST and CONVERTExplicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.SyntaxUsing CAST:CAST ( expression AS data_type ) Using CONVERT:CONVERT ( data_type [ ( length ) ] , expression [ , style ] )ArgumentsexpressionIs any valid Microsoft® SQL Server™ expression. For more information, see Expressions. data_typeIs the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types. lengthIs an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. styleIs the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
mm/dd/yyyy = style 101*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
|
|
|
|
|
Blank csv file