Bcp File Format...its Killing Me...pleaseeeeeeeee Help...
Sep 20, 2005
Hi,
i wanted to ask on the same context of bcp...i m trying to insert two rows into a table using the bcp command prompt and the bcp file looks as follow(format.fmt):
7.0
3
1 SQLCHAR 0 4 ""," 1 numbers
2 SQLCHAR 0 15 """ 2 values
3 SQLCHAR 0 2 "
" 3 finish
and the input file has the following contents
1234,"other"
5678,"column"
now when i m running the command as
bcp master.dbo.two_column in c: empinput.txt -fc: empformat.fmt -Sserver -Uuser -Ppasword -T
it gives me the error as sqlstate 07009 with native state =0 and the error as = [Microsoft][SQL Native Client] Invalid descriptor index
i m using sql server 2005 (YUKON)...can anyone pleaseeeeeeeeeeeeee tell me how can i solve this problem?????????????????so that i can insert these rows into my table two_column....
thanks ...pleaseeeeeeeeee help me....
View 5 Replies
ADVERTISEMENT
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
Jan 2, 2008
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
View 1 Replies
View Related
Jul 23, 2005
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies
View Related
Jan 16, 2008
I have a txt file with format as following
MailAddress:Kienpt@ifi.local
DomainName:ETH2K
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:
- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)
Can you help me?
View 1 Replies
View Related
Feb 20, 2007
Hi,
I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine).
I retrive the database values in .txt,XML format. Now i want to take in .bak format.
give the Suitable solution for this.
Subashini.G
View 4 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Apr 11, 2000
Hey,
How can we kill a process initiated by an Extended Stored Procedure.
For example, I issued
exec xp_cmdshell "C:Notepad.exe"
and scheduled as a job and it started running and it never finished.
I dunno watz goin on behind the scene and i couldnt kill the process.
Anybody who knows how to do it,please help me out. And my process is still
running in the server for more than two days. Do i have to restart the
server? If so, everytime when it get a problem like this, am i supposed to restart?
Please help me in this issue...
View 1 Replies
View Related
Mar 10, 1999
Backend: MSSQL 65
My datafiles are bcp out from Syabse 11.
Because the order of fields are different, I need to use format file with BCP.
I generated my fmt files by using MSSQL's bcp utility with the instructions from SQL Server books onLIne
(section: Using a Format File to Selectively Copy data)
I cannot bcp in with format file. I got
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 50
Since the datafile is small, I manually shift the order.
Then, I bcp it in without format file and it worked fine.
Now I got data in my SQL Server
I thought it may be the datafile. Though it's text file, it's generated by Sybase bcp utility. :-)
So, I bcp out the data to a new ASCII file. I change the ID to make those records different.
Then, I re-generate the format file.
Now, I have everything generated by MSSQL. I tried again and I got the same error.
This table contains 1 Blob field and 4 varchar(255) fields. Will this be a problem?
We have to solve this problem. When our applications go live, the datafiles will be huge and we cannot edit it manually.
Please advise
Thanks in advance.
Elizabeth Pickering
View 1 Replies
View Related
Feb 15, 2005
set @date = getdate()
set @month = convert (varchar[2], datepart(mm,@date))
gives me 2 instead of 02 for month of February
any clues ?
View 3 Replies
View Related
Jan 27, 2004
What are format files and how do they help us in Bulk Load????
I referred to many issues send to the forum but could not get sufficient information on this.
My main issue is to bulk insert from a .CSV file in hte following format
12,23,"blrnnr","Address",45677,"bangalore".............68 columns
After bulk insert I am getting the double qoutes also into the table.
Now my problem is, How do I remove these double qoutes?
I don't want to use replace() to remove these quotes!!!!!!
I have attached a Screen Shot to describe this issue with this mail
Any information is appretiated regarding this issue.
Thanking you
Naveen
View 1 Replies
View Related
Mar 8, 2006
I am trying to copy the data in excel file into a table using the bcpand this is the code that I have. However the bcp utility does notseem to create a format file, which I thought it should do. I amprobably going about this all wrong so any help would be useful.exec master..xp_cmdshell '(FOR %i IN ("E:WUTemp*") DO (bcp#ProspectImportTest in "%i" -fE:WUTempProspect.fmt)'bulk insert #ProspectImportTest from 'E:WUTemp*."' with (formatfile ='E:WUTempProspect.fmt')Thanks.KR
View 2 Replies
View Related
Oct 15, 2007
Hi.I was wondering if anybody could piont me to a reference for the .bakfile format.I need to upload a huge amount of data from a unix machine into an MSSQLServer database and have tried lots of different things with mixedresults.I occurs to me that if I can generate a .bak file on the unix side,move it over to the MS side and 'restore' the database, that wouldprobably be the fastest possible method.Thanks,-jim
View 2 Replies
View Related
Jul 20, 2005
A format file provides a way to bulk copy data selectively from a datafile to an instance of SQL Server. This allows the transfer of data toa table when there is a mismatch between fields in the data file andcolumns in the table.I take it this assumes the number of fields in the data file willalways be constant. What if it is not?My table has two columns but my datafile may have 2 to 4 columns and Iwant to always select only the first two. Is there a way to set up theformat file to accomplish that?
View 3 Replies
View Related
Jul 8, 2005
Hi, i was try a very simple transaction, but it show me error: Exception Details: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. Below is my code, wat i did wrong for tis... i tried 2 days just for transaction alrd.... pls help.Sub bt1_click(sender as object, e as eventargs) dim i as integer Dim myTrans As SqlTransaction dim strExDate,StrSAPNum,strID,StrPartNum,strRemark,strWAID,strQty,strQty1,StrPartNum1 as string '================ loop thru n insert data=============================== try for i=0 to DgData.items.count-1 strExDate = CType(dgData.Items(i).FindControl("tbExDate"), textbox).text strRemark = CType(dgData.Items(i).FindControl("tbRemark"), textbox).text strQty = CType(dgData.Items(i).FindControl("tbQty"), TextBox).text StrSql="Insert into tbl_GrDE(Qty, ExDate, Remark, EntBy) Values " & _ (@Qty, @ExDate, @Remark, @EntBy)" ObjCmd=New SqlCommand(StrSql, ObjConn) With ObjCmd.Parameters: .Add(New Sqlparameter("@qty", strQty)) .Add(New Sqlparameter("@ExDate", strExDate)) .Add(New Sqlparameter("@Remark", strRemark)) .Add(New Sqlparameter("@EntBy", session("User_ID"))) End with
ObjCmd.Connection.Open() myTrans = ObjConn.BeginTransaction ObjCmd.Transaction = myTrans ObjCmd.ExecuteNonQuery() ObjCmd.Connection.Close() next myTrans.Commit() catch ex as exception response.write("error") myTrans.Rollback() end tryEnd SubRegardslife's Ng
View 4 Replies
View Related
May 30, 2001
Hi
One DTs package job running and we stop the job.Job was stoped but process not killing.We tried using kill spid but no use.please any body give suggestion. this production server.
View 3 Replies
View Related
Jan 10, 2001
Please could anyone help I run an restore on a specific database overnight, in order to do so I have to kill all user connections. When I try to kill all user SPIDs some still remain ? , why please can anyone help me !
Heres a typical example of what I am doing:
sp_who snapshot of before the kill:
61,sleeping,TDI.CO.UKSUZANA, ,0,RPT,AWAITING COMMAND
63,runnable,TDI.CO.UKNEILL, ,0,RPT,SELECT
Attempting to disconnect 2users
KILL 61
KILL 63
sp_who snapshot of After the kill:
63,runnable,TDI.CO.UKNEILL, ,0,RPT,SELECT
I would greatly appreciate any help , im using SQL Server 7 currently
Many Thanks,
Marcus Stuart
View 4 Replies
View Related
Dec 22, 1999
I'm going to try to explain my situation.
I have a spid that I'm not able to kill, this were a select statement from with in access 97 application using a DNS connection.
So even that I reboot the client pc and kill the spid it still shows as active with status RollBack.
We have similar problem before and the only way that it disappear were re - starting Sql.
(system Sql 7.0 with sp1 and Access 97)
Thanks.
View 2 Replies
View Related
Apr 15, 2003
I have a script that I use to see if someone has been logged in for too long. Does anyone know how to take a varible spid and kill that login. I tried using kill @spid, but that does not work. Any Suggestions?
View 1 Replies
View Related
Aug 28, 2002
Dear SQLGuru,
I hope you can help me to find the way to resolve this issue.
When I've accidentally triggered a process in SQL 6.5 without knowing that it's going to hold a lot of resources and making the network very slow and end users will be started complaining about the slowness.
I've no other way other than the killing the process kill <spid>. Funniest part is, even it is not get killed even when I tried multiple times and the process is still active and still running.
When I tried to find the way out, One another guy stopped the SQL server and restarted again. That's it, it took a long time to stop and restart and ends up with the recovery mode of the database and it was running more than 3-4 hours to get into the usual mode.
Based on the scenario, what would be your suggestion when I've encoutered the same situation. I've triggered some application like DBCC checkdb and it's keep running for a long time. But I need to kill the process immediately without affecting any other process. Pls. advise me.
Thanks a lot,
Naga
View 1 Replies
View Related
Apr 15, 2002
I have a problem. I am trying to kill a spid that is blocking updates to a table.
The spid number is '-2'. I am using KILL with UOW and I am getting this error:
Server: Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {FCF8D536-27ED-11D6-9CF2-0002A56BDA54} is in prepared state.
Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.
Users are connecting through MTS server. I am runnnig SQL2000 sp2 +hotfix, on NT4.0.
Has anyone encountered this problem before, and has a solution for it (besides rebooting the MTS and SQL Server)?
Thanks,
Peter
View 1 Replies
View Related
Dec 2, 2004
Hey all,
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
Any ideas ?
Thanks in advance,
Kilka
View 7 Replies
View Related
May 25, 2008
If we have a deadlock we will check in the error log and and find the spids which are involving in the deadlock.
We will kill one of the process by using SPID (no) KILL.
Is there specific steps to consider while killing a process
View 3 Replies
View Related
Apr 28, 2002
do anyone know the format of the *.ldf file?
View 2 Replies
View Related
Oct 3, 2007
I have AdventureWorks installed. I am using SQL Server 2005.
I need to import some large tab delimited text files into SQL. From the research i did I thought that using BCO would be the best solution becausr I will have to import these files and export in the same format.
I am attempting to follow the example at:
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
B. Creating a non-XML format file for character data
In my command window at C: I enter:
bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T
I was expecting to be prompted with questions about the source file and have a format file created. When i press enter nothing hallende. No errors. Nothing.
Can come one give a beginner the step by step on how to creat the format file and use it to import (and export would be nice too).
View 2 Replies
View Related
Oct 25, 2007
Using sql server
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
Im not sure about the best way to go about this?
View 1 Replies
View Related
Jul 20, 2005
I have a format file which is working but not correctly. It is, forsome reason, dropping the first line of the input .csv file. Theproblem is something with the second coulumn of data having quotes init. Any ideas? Below is some info.Format file (I use firstrow=2 in Bulk Import command:8.061 SQLCHAR 0 3000 ","" 1 Provider_Raw_ID Latin1_General_CI_AS2 SQLCHAR 0 3000 ""," 0 none_name Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS6 SQLCHAR 0 3000 "
" 4 NetworkComparedTo Latin1_General_CI_ASSample input file:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompare dTo1,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp12,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp13,"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp14,"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp15,"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp16,"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp17,"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp18,"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1Thanks a lot!!!Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies
View Related
Dec 20, 2006
I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.
For example : "1234",abc,"id"
I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.
View 4 Replies
View Related
Jun 12, 2015
when installing sql server error showing file format is not vaild
View 3 Replies
View Related
Apr 23, 2008
The data file contains the answers given to questions asked during the course of an interview. This file contains data only for completed interviews. The file is ASCII based, and contains data in a card column format. This means that each record is spread over several rows in the file €“ called cards. Currently 39 cards make up one completed record, although this may change over time. There are 80 columns per row.
sample of data is as follows
record 1
0000301 108033135 ds880783 100325080327000451334779005133477900 1 001
000030251334779000 0 LISA E 002
0000303MCCARTHY est 11000000000000000000000000100 003
0000304DODGE GRCARA 2008 004
0000305Northgate Chrysler Dodge Jeep 005
00003068536 Colerain Ave Cincinnati 45251006
00003072914OHDEMOSS,MIKE 03-26-200811 007
00003081 1 120529101010 9 008
0000309 009
0000310 010
0000311 011
0000312 2 012
0000313 013
0000314 014
0000315120642 1 015
0000316 016
0000317 017
0000318 018
0000319 019
0000320 020
0000321 021
0000322 022
0000323 023
0000324 024
0000325 025
0000326 026
0000327 027
0000328 028
0000329 029
0000330 030
0000331 031
0000332 032
0000333 033
0000334 034
0000335 035
0000336 120557 036
00003373108629008110.000 North-gate Chrysler Dodge Jeep 037
0000338 1 3 038
0000339 039
Record 2
0000401 108033135 ds880609 300192080327000931868842133184587707 1 001
000040231868842131 0 MARK 002
0000403MURPHY cst 10010000000000000000000000100 003
0000404DODGE 2005 004
0000405Hebert's Town and Country Dodge Chrysle 005
00004061155 E Bert Kouns Shreveport 71105006
00004075628LA436 03-25-200811 007
00004083 1 120608 008
0000409 090909 009
0000410 4 010
0000411 011
0000412 2 012
0000413 013
0000414 014
0000415120759 1 015
0000416 016
0000417 017
0000418 018
0000419 019
0000420 020
0000421 021
0000422 022
0000423 023
0000424 024
0000425 025
0000426 026
0000427 027
0000428 028
0000429 029
0000430 030
0000431 031
0000432 032
0000433 033
0000434 034
0000435 035
0000436 120642 036
000043731086290081 09.000A-Bears Town and Country Chrysler Jeep 037
0000438 1 3 038
0000439 039
i need to convert this into a coma delimtited file ,file and i am failing whats need to be done to achieve it
its quite a new concept for me
please advise
View 4 Replies
View Related
Jul 22, 2007
Hi all,
I have a flat file which the first row contains the certain info. about this file. I want to read the first line from this file to determine if continue to next step. what's the task/transformation can be used to do this?
thanks
Patrick
View 1 Replies
View Related
Mar 24, 2004
hi, what is a good way to kill the duplicates from a table. when i say killing duplicated, i mean killing all the rows for the repeated row.
WorkTempID ItemNo Seq
100196 RTP-22 1
100197 RTP-22 2
100198 RTP-22 3
100199 RTP-22 3
100200 RTP-22 4
100201 RTP-22 4
100202 RTP-22 5
100203 RTP-22 5
********************************************************
see how Seq 3, 4 and 5 are repeated? so for the output i want.
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
********************************************************
i DO NOT want this as the output. i already know how to achive this using DISTINCT keyword
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
100198RTP-223
100200RTP-224
100203RTP-225
View 12 Replies
View Related
Oct 19, 2000
Is there a way to kill all user connections to a database using T-SQL?
Thanks!
View 2 Replies
View Related
