Incorrect Host-column Number Found In BCP Format-file
Jul 20, 2005
Hi guys, would appreciate if you can shed some light on this.
Sorry to be a pain, can you tell me what is wrong with the following:
for /F %%i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in
%%i -fc:cpcp.fmt -T -S CHICKYy
where CHICKYy is the server
bcp.fmt
8.00.194
6
1 SQLCHAR 0 20 ", " 0 filler_1 ""
2 SQLCHAR 0 8 "
" 1 computer_name ""
3 SQLCHAR 0 20 ", " 0 filler_2 ""
4 SQLCHAR 0 16 "
" 2 ip_address ""
5 SQLCHAR 0 20 ", " 0 filler_3 ""
6 SQLCHAR 0 60 "
" 3 operating_system ""
pc1.txt and other *.txt format is:
JW_193801,
192.168.1.1,
Windows XP,
when I run it I get:
C:cp>for /F %i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in
%i -fc:cpcp.fmt -T -S CHICKYy
C:cp>bcp Inventory..pc in pc1.txt -fc:cpcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
C:cp>bcp Inventory..pc in pc2.txt -fc:cpcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
C:cp>bcp Inventory..pc in pc3.txt -fc:cpcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
C:cp>bcp Inventory..pc in pc4.txt -fc:cpcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
C:cp>bcp Inventory..pc in pc5.txt -fc:cpcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
The sql table has 3 columns:
Sorry to be a pain.
--
---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com
View 1 Replies
ADVERTISEMENT
Jun 5, 2015
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
col1 [varchar](10) NOT NULL,
col2 [datetime] NOT NULL,
col3 [date] NOT NULL,
col4 [varchar](6) NOT NULL,
col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
1 SQLCHAR 0 10 "@|@" 3 Col3 ""
2 SQLCHAR 0 6 "@|@" 4 Col4 Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
Nothing works ....
View 7 Replies
View Related
Jul 20, 2005
Here's a tricky SQL question that has definitely driven me to the end ofmy rope. I'm using Oracle 9i and I need to perform some simplemultiplication on a field and then display it with a percent sign usingthe COLUMN command. Here's the code thus far:COLUMN price format 9,999.99 HEADING 'Charged%'SELECT pricecharged * .231 as priceFROM VT_examdetailThe output from this reads:Charged%---------23.1034.6534.65....The kicker here is that I need to add a percent sign to the right of theoutput, so that it reads:Charged%---------23.10%34.65%34.65%....I thought I could do this by just adding "|| ('%')" into the SELECTstatement, but when I do this the decimal position defined in the COLUMNcommand is lost. Does anyone know another way around this?Thanks,Alex
View 3 Replies
View Related
Feb 6, 2008
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
View 6 Replies
View Related
Jan 8, 2007
Hello,
I have an SSIS package that basically runs a query and exports to an Excel destination. Everything works fine except for a date field that I use. When I preview the export it the date looks fine, as in a 1/7/07 2:05 PM for example. However, when I look at the actual Excel file/Export it will list the date as 1/7/07 without the time. I then have to format the column to the right date format in Excel for it to display correctly. I want to prevent the manual formatting due to the fact that this file needs to be automated.
Is there any way to make this happen?
Thanks,
Marco
View 5 Replies
View Related
Oct 8, 2006
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
View 4 Replies
View Related
Apr 14, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
[Code]....
View 2 Replies
View Related
Sep 4, 2007
While Running a SSIS package after migrating it from DTS to SSIS , in MS SQL Server 2005 ,
it gives error while execution :
DTS_DTSTASK_DATAPUMPTASK_2
The number of columns is incorrect.verify the column metadata is valid.
"OLEDB Destination "(22) Failed the pre execution phase and returned error code 0xC0202025
Thanks for the response .....
View 2 Replies
View Related
Dec 9, 2007
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
View 1 Replies
View Related
May 22, 2007
OK - truck loads of folk have reported this problem - but I cannot solve it - so apologies for repeating the question...
My SQL Server 2005 Report is developed on the same server as it is run.
I have SQL Server 2005 SP2 installed. The report language is set as en-GB.
The browser language setting is en-NZ.
The report previews fine - I pick May 22 from the picker and it appears as 22/05/2007 in the report parameter text box - which since I am in NZ is exactly as I want it.
I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.
I run the deployed report from the report server virtual directory and here is where the problem occurs - selecting May 22 from the date picker produces 05/22/2007 in the report parameter text box and produces the error
"The value provided for the report parameter 'StartScheduledDate' is not valid for its type. (rsReportParameterTypeMismatch)"
thanks for any help here - this problem has made this a ridiculously expensive report!
View 4 Replies
View Related
Feb 23, 2007
I've installed ADS on a windows mobile 5.0 device and am trying to run the sample Wizard application. It connects to the test database on the device OK, but fails to connect to the database on the desktop with the error:
"The HTTP request failed due to incorrect format or content. Try restarting the IIS server"
I've tried changing the port from 1024 in case it was already in use, but this doesn't change the error. Nor does restarting IIS (v5.1). IIS appears to be running correctly on the desktop.
Any ideas what to try next?
thanks
irs
View 8 Replies
View Related
Jan 24, 2008
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.
sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub
What am I doing wrong?
View 8 Replies
View Related
Oct 13, 2004
Hello,
I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:
The user can make a selection from the categories listed below:
Geographic Area
Time Period
Topic
Record Type
If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:
Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)
Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.
Thanks
View 6 Replies
View Related
Dec 20, 2006
I want to format a number like "#,##0.00" in order to handle it in Excel as a number (i.e. compute a sum).
Excel is able to show a number in a specail format and still allow to compute with ...
Thanks in advance,
Peter
View 3 Replies
View Related
Mar 26, 2010
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies
View Related
Oct 29, 2001
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Jun 14, 2001
Hi All,
How can I run a host command (OS command ) from ".sql" script of sql server?
-- Rajesh
View 2 Replies
View Related
Feb 13, 2007
I am having trouble with BCP. I get the same error with xp_cmdshell as I do with entering bcp as a DOS command. I have checked and rechecked the file names and permissions and even restart the PC.
Does BCP even work when SQL Server 2005 Express on a PC?
declare @sql varchar(8000)
select @sql = 'bcp SYMITAR..ACCOUNT in C: estEXTRACT.ACCOUNT -T -f C: estACCOUNT.FMT -S'+@@servername
exec SYMITAR..xp_cmdshell @sql
GO
Volume in drive C has no label.
Volume Serial Number is 08E5-2414
Directory of C: est
02/13/2007 08:44 AM <DIR> .
02/13/2007 08:44 AM <DIR> ..
08/31/2006 09:11 AM 27,503,161 EXTRACT.ACCOUNT
08/31/2006 09:12 AM 6,879 FMT.ACCOUNT
02/07/2007 08:46 AM 220 ACCTTYPE.FMT
02/13/2007 08:44 AM 0 filelisting.txt
02/07/2007 08:33 AM 220 ACCTTYPE.xml
5 File(s) 27,510,480 bytes
2 Dir(s) 54,344,847,360 bytes free
Could the fact that it's actually pulling BCP from c:Program FilesMicrosoft SQL Server80Toolsinn be a problem? I know this because it doesn't recognize the -x extensionof the bcp command.
PATH:
%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesCommon FilesGTK2.0in;C:Program FilesMicrosoft SQL Server80ToolsBINN;c:Program FilesMicrosoft SQL Server90Toolsinn;c:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;c:Program FilesMicrosoft SQL Server90DTSBinn;c:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesQuickTimeQTSystem
...
I added the c: est to the path and removed c:Program FilesMicrosoft
SQL Server80Toolsinn from the path. While it recognizes the -x
extension now it still gets the same error message:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
View 11 Replies
View Related
May 10, 2007
Hello,
I have checked file names, and I have checked permissions on the files. I do not see any reason why the BCP should fail. Any ideas how I can debug this further?
Thanks
Tom
View 1 Replies
View Related
Nov 16, 2006
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
View 2 Replies
View Related
Mar 20, 2002
I need to change a number that looks like 1365 and make it look like 1,365 without any decimal or trailing zeros. Then number can even be a 7 digit number. It seems like the only way I know is the money to varchar conversion using style 1, but this adds trailing zeros and a decimal point. Can anyone help on this
Thanks,
Hemal
View 1 Replies
View Related
Apr 21, 2004
I am doing some simple arithmatic for a query that I am working on. What I have below describes the arithmatic that is taking place withing a portion of the query:
select(o.sales_val/o.qty_sales) as 'Unit_Price', (o.acctng_cost_val/o.qty_sales) as 'Unit_Cost'
from opcsahf as o
go
The division is correct. My only issue is that the results for 'Unit Price' or 'Unit Cost' may be formatted like example: 4.25000000.
How can I have my nubers show up with only to 2 decimal places instead of all the zeros at teh end?
View 2 Replies
View Related
Jan 12, 2007
When setting the format property for a textbox containing a number the first parameter relates to a positive number, the second negative and the third to zero.
eg #,###; (#,###); 0
Is there a way of also formatting Null values in the same way without writing custom code?
View 4 Replies
View Related
Jul 27, 2007
In the report Layout, if I right-click a cell that is located in the table details row, click Properties, and select the Format tab, there is an option for the Format code. I click on the little dot-dot-dot icon (...) and it brings up the Choose Format option. The choices are Default, Number, Date, Time, Percentage, and Currency. The format that I am interested in is Number, so I click on Number. Now, it shows '1234' ; '1,234.00' ; '1234' ; '1234.00' ; and finally '1.234123e+003'. The choice that I am looking for (or desire) is something that would show 9,452 and that is it. No decimals, but want the comma. I do not see an option for this. The option '1,234.00' has decimals. The two options '1234' and '1234' appear to not have decimals, but they do not appear to have commas either. Further, what is the difference between the first '1234' and the second '1234'?
*** Also, I have a couple formulas that I have entered. I have tried to change the Number Format for these cells in the table details row and nothing changes. Still the same format.
View 22 Replies
View Related
Mar 14, 2008
I have number field that I am trying to format. The number currently shows -7899.99. I would like it to show (7,899.99) for negative and 7,899.99 for positive.
Any ideas on how to do this?
View 3 Replies
View Related
Mar 12, 2008
I have this
use dbWebsiteLO
SELECT A.vehicleref,A.manufacturer,A.cvehicle_shortmodtext,A.derivative,min(a.ch) as price,A.capid,B.studioimages,(SELECT term FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleref = a.vehicleref and ch = price) FROM vwAllMatrixWithLombardAndShortModel A
LEFT OUTER JOIN dbPubMatrix..tblCarImages B on A.capid = b.capid
WHERE a.source <> 'LOM' AND a.type = 'car' GROUP BY a.vehicleref,a.manufacturer,a.cvehicle_shortmodtext,a.derivative,a.capid,b.studioimages
I get Invalid column name 'price'. I'm trying to reference the "min(a.ch) as price"
Thanks
View 1 Replies
View Related
May 31, 2007
Hi,I don't know if I missed anything. I have 2 member tables and onepartition view in SQL 2000 defined as followingCREATE VIEW Server1.dbo.UTableASSELECT*FROMServer1..pTable1UNION ALLSELECT*FROMServer2..pTable2CREATE TABLE pTable1 ([ID1] [int] IDENTITY (1000, 2) NOT NULL ,[ID2] [int] NOT NULL ,...<other columns>.........CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED([ID1],[ID2]) ON [PRIMARY] ,CHECK ([ID2] = 1015)) ON [PRIMARY]CREATE TABLE [pTable2] ([ID1] [int] IDENTITY (1001, 2) NOT NULL ,[ID2] [int] NOT NULL ,...<other columns>.........CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED([ID1],[ID2]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CHECK ([ID2] <1015)) ON [PRIMARY]SELECT is working fine. However, I got error message if I issue anupdate command such asUPDATE UTableSET somecol = somevalWhere somecol2 = somecondServer: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'UTable' is not updatable because a partitioning columnwas not found.Anyone have any idea? ID2 is my partition column, why the SQL 2Kdoesn't see it. It is a part of primary key, having checkingconstrain, and no other constrain on it. Am I missing something?Thanks a lot.
View 8 Replies
View Related
Feb 14, 2008
CREATE VIEW [dbo].[pv_mainQAStatus] as
SELECT
[QAAccountID], [FunctionalAreaID],
[EvaluationStartDate], [EvaluationEndDate]
FROM
[PA_mainQAStatus]
UNION ALL
SELECT
[QAAccountID], [FunctionalAreaID],
[EvaluationStartDate], [EvaluationEndDate]
FROM
[CS_mainQAStatus]
UNION ALL
SELECT
[QAAccountID], [FunctionalAreaID],
[EvaluationStartDate], [EvaluationEndDate]
FROM
[GV_mainQAStatus]
In all the 3 tables I have QAAccountID and FunctionalAreaID as Primary key. I am getting this error when I try to insert
'UNION ALL view 'pv_mainQAStatus' is not updatable because a partitioning column was not found.'
How to over come this. I am used to writing an INSTEAD OF Trigger.
View 7 Replies
View Related
Jul 21, 2006
Hi Everyone,
I have a phone number coming from the database coming in the format of 2132563111. How can I do this in sql query213-563-3111
Please let me know if there is any function that does it.
Thanks.
View 1 Replies
View Related
May 9, 2005
How to format phone numbers in sql? in current fields some phone number entered like this:
1 800 7894564
8001237878
1237878
1800blue etc...
I am trying to get into uniform like this:
8007834444
Thanks
View 9 Replies
View Related
Dec 26, 2007
I have a price field that when I query it, it returns the price as"5.0000". What function can I use to return the price as "5.00"?THANKS
View 4 Replies
View Related
Jun 1, 2015
I have a table that has an Associate id for each agent. The id is 8 digits sometimes starting with 0 (06956461) and sometimes not 0 (98039585). How would I format this?
View 2 Replies
View Related
Apr 21, 2008
Hi,
I have phone number column in Excel file. The phone number is in this format: 523-349-0212. When this data imported in to SQLServer file, it is not keeping the format, storing it as 5233490212. The data type of this column is varchar. How to keep this format?
Thanks in advance
View 4 Replies
View Related
