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....
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.
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();
SqlConnection.Open(); cmd.ExecuteNonQuery(); SqlConnection.Close(); RefreshData(); I am running Visual Studio C# Express 2005 and SQL Server Express 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
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
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.
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?
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.
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
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.
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
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
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?
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
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.
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 !
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)
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?
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.
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)?
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.
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
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).
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.
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!
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.
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.
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?
