[SQL 2012] bcp unicode |
| Posted: 14 Sep 2013 04:38 AM PDT Hello, I have a great mystery about bcp command.I have this table:CREATE TABLE myTable ( PersonID smallint, FirstName nvarchar(50) , LastName nvarchar(50) );I create a format file with bcp:exec xp_cmdshell 'bcp AdventureWorks2012.dbo.myTable format nul -f D:\Test\MyTableFormatFile.fmt -S instance\SERVER -w -t, -T'The file with data is:1,skip,firstname1,lastname11,skip,firstname2,lastname2I've changed the format file to include the skiped column([b]the data file has one extra column and I want to skip this column on import, the data file is Unicode[/b]):9.041 SQLNCHAR 0 14 ",\0" 1 PersonID ""2 SQLNCHAR 0 1000 ",\0" 0 FirstName SQL_Latin1_General_CP1_CI_AS3 SQLNCHAR 0 1000 ",\0" 2 FirstName SQL_Latin1_General_CP1_CI_AS4 SQLNCHAR 0 1000 "\r\0\0" 3 LastName SQL_Latin1_General_CP1_CI_ASWhen I execute:exec xp_cmdshell 'bcp AdventureWorks2012.dbo.myTable in D:\Test\MyTableData.dat -S instance\SERVER -T -e D:\Test\error_bcp.txt -f D:\Test\MyTableFormatFile.fmt'I get the error:NULLStarting copy...SQLState = 22018, NativeError = 0Error = [Microsoft][SQL Native Client]Invalid character value for cast specificationNULL0 rows copied.Network packet size (bytes): 4096Clock Time (ms.) Total : 1 NULLinstead this query works perfectly:select * from openrowset(bulk 'D:\Test\MyTableData.dat', FormatFile = 'D:\Test\MyTableFormatFile.fmt') t1[b]Which might be the problem?[/b] If I generate the format file with "-c" instead of "-w" and the file from which I load the data in Saved As "ANSI" the bcp works fine.The test was made in SQL 2012 Evaluation, i used bcp version 100(sql2008R2) but if I switch to "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" the result is the same. |
| How to add multiple stopwords to a stopwords table ? Posted: 14 Sep 2013 10:02 PM PDT I keep getting this error message, when i use insert into or update.insert into sys.fulltext_stopwordsvalues('5', 'resume', 'English', '1033')I have 10k more words to add to the stopwords table, and i dont want to do it one at a time!Msg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.ALTER FULLTEXT STOPLIST stoplistian91413 ADD 'resume' LANGUAGE 'English';I found the above command that works for one word. Is the only way to add 10k words by adding a loop? Thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment