File Import To SQL Server 2000
Jul 20, 2005
Hi all,
I have a file with an extension of .sdf. I "believe" it is a text file
of some sort but I am uncertain. The source agency hasn't returned any
of my calls so I'm wondering if anyone is familiar with this extension?
I'd like to import the file into my database - when I use DTS and chose
a text format, regardless of what delimiter I choose, the format is
still really ugly. when I pull it up in a huge text editor, it is hard
for me to tell what it is there.
I saw in one of my searches that it could be a comma delimited (it's
not) .. could be a unisys file? I know it's not much information to go
on - but where should I start in trying to get this into my database
without knowing the format? Any suggestions would be greatly
appreciated.
Thanks!
Bethany
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
View 1 Replies
ADVERTISEMENT
Aug 21, 2007
I need help importing a complex xml file using the XML Bulk Load component. I need there to be 2 tables as shown below. I just
cannot seem to figure out how to get this to work with such a complex XML structure. I have shown below my table structure, a
sample of one of the entries of the XML files and what I have so far for my XSD schema. Any help would be great!!!
My Tables:CREATE TABLE [dbo].[WPXML] ( [Part] [varchar] (100) PRIMARY KEY, [BaseVehicle] [int] NULL , [Qty] [int] NULL , [PartType] [int] NULL , [EngineBase] [int] NULL , [EngineDesignation] [int] NULL , [ImageURL] [varchar] (100) NULL , [ThumbURL] [varchar] (100) NULL) GOCREATE TABLE [dbo].[WPPRODUCT] ( [Part] [varchar] (100) PRIMARY KEY , [PartNumber] [varchar] (100) NULL , [BrandID] [varchar] (4) NULL , [BrandDescription] [varchar] (100) NULL , [Price] [varchar] (10) COLLATE NULL , [ListPrice] [varchar] (10) COLLATE NULL, [Weight] [varchar] (10) COLLATE NULL, [Popularity] [varchar] (10) NULL, [OEFlag] [varchar] (10) NULL, [ProductRemark] [varchar] (1000) NULL, [Note] [varchar] (5000) NULL ) GOSample of XML:<App action="A" id="1484266"> <BaseVehicle id= "5899"/> <EngineBase id= "555"/> <EngineDesignation id= "138"/> <Qty>0</Qty> <PartType id= "6192"/> <Part>W0133-1621038</Part> <Product> <PartNumber>W0133-1621038</PartNumber> <BrandID>FUL</BrandID> <BrandDescription><![CDATA[Full]]></BrandDescription> <Price>17.38</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <Weight>1.05</Weight> <Popularity>B</Popularity> </Product> <Product> <PartNumber>W0133-1611982</PartNumber> <BrandID>KN</BrandID> <BrandDescription><![CDATA[K&N Filters]]></BrandDescription> <Price>68.78</Price> <ListPrice>105.81</ListPrice> <Available>Y</Available> <Weight>1.80</Weight> <Popularity>E</Popularity> </Product> <Product> <PartNumber>W0133-1626304</PartNumber> <BrandID>ND</BrandID> <BrandDescription><![CDATA[Denso]]></BrandDescription> <Price>22.34</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <OEFlag>OEM</OEFlag> <Weight>1.05</Weight> <notes>Notes For This Part</notes> <Popularity>D</Popularity> </Product> <ImageURL><![CDATA[http://img.eautopartscatalog.com/live/W01331621038OES.JPG]]></ImageURL> <ThumbURL><![CDATA[http://img.eautopartscatalog.com/live/thumb/W01331621038OES.JPG]]></ThumbURL> </App>
My XSD Schema Thus Far:<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation> <xsd:appinfo> <sql:relationship name="test" parent="WPXML" parent-key="Part" child="WPPRODUCT" child-key="Part" /> </xsd:appinfo></xsd:annotation> <xsd:element name="App" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Qty" type="xsd:integer" /> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="BaseVehicle"> <xsd:complexType> <xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" /> </xsd:complexType> </xsd:element> <xsd:element name="PartType"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="PartType" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineBase"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineDesignation"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" /> </xsd:complexType> </xsd:element> <xsd:element name="ImageURL" type="xsd:string" /> <xsd:element name="ThumbURL" type="xsd:string" /> <xsd:element name="Product" sql:relation="WPPRODUCT" sql:key-fields="Part" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="PartNumber" type="xsd:string" > </xsd:element> <xsd:element name="BrandID" type="xsd:string" > </xsd:element> <xsd:element name="BrandDescription" type="xsd:string" > </xsd:element> <xsd:element name="Price" type="xsd:string" > </xsd:element> <xsd:element name="ListPrice" type="xsd:string" > </xsd:element> <xsd:element name="Weight" type="xsd:string" > </xsd:element> <xsd:element name="Popularity" type="xsd:string" > </xsd:element> <xsd:element name="OEFlag" type="xsd:string" > </xsd:element> <xsd:element name="ProductRemark" type="xsd:string" > </xsd:element> <xsd:element name="Note" type="xsd:string" > </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>
View 15 Replies
View Related
Nov 2, 2007
I am trying to import an Excel file - when I pick the file I get the message "Could not open file for reading. Close any other application that may be locking the file."
I have verified that the file is not open - I have even rebooted the machine - still the same message - what am I doing different?
Please advise.
Thanks
View 2 Replies
View Related
Oct 3, 2007
Hi All,
I am trying to import data via DTS from a CSV file. I have the "empty" tables already created with proper column names. Now, I have a subset data of each of these tables, which I am trying to import. I am facing problem in that, there is a datatype conflict between the source (CSV file) and destination (table already in the DB). All the data in the CSV file appears to be of DBTYPE_WSTR, where as in the table it is different (some are DBTYPE_WSTR, some are DBTYPE_DATE, and so on). Is there a way that I can import data successfully? This has become a work stoppage issue now. I had to actually go for this approach of creating the empty tables first and then importing data because, the backup file was very very large and could not be copied to our domain. Please help me out in this.
Thanks a lot.
Mannu.
View 4 Replies
View Related
Feb 23, 2005
i want ot import data from excel .xls file to sql server 2000
into an existing table.
should i use some stored procedures or else
View 5 Replies
View Related
Mar 2, 2005
Hi,
I do not know if it can be done:
how to import an Oracle dump file into SQL server ?
That dump file contains the content of a table, excported from Oracle.
Is there a way or another to import it to SQLServer 2000 ?
Thanks
View 2 Replies
View Related
Jul 20, 2005
Hello,I am receiving a text file that is produced from a mainframe that isout of my control. I am attempting to find a (hopefully clean) way toimport it into a SQL Server database in an automated fashion. I amnot really concerned about how many tables it requires or what theschema looks like as long as the data remains related and ends up inits respective fields (I will probably use scratch tables for this).The data is given to me in a format that is meant to be printed outand read by human eyes (in a text file). The format looks somethinglike this:Begin File:-------------------------------------------------------------------------------1234 1234 1234 1234 XYZ Company 01/01/2003.......More stuff related to XYZ company for a couple of lines ..............(this stuff can easily be parsed by position).......MCARD VISA AMEX DISC-------------------------------------------------------------------------------TOTAL 11111.11 4444.44 5555.55 30.01TRANS FEE .20 .20 .15 .15TRANS AMOUNT 2222.22 888.89 833.33 4.50DISC .0165 .0165 .0365 .0355-------------------------------------------------------------------------------ANOTHER HEADER............More stuff related to XYZ Company................End File:Well, this isn't the exact format, but just an example. The point isthat all of the data in each column is related and should end up inthe same record which is related to the parent record of XYZ Company(or all in a single record in a single table if that is the closest Ican get).Also, the rows are not always present. For example, if TRANS FEEdoesn't apply to anything in the row, then the entire row willcollapse and TRANS AMOUNT would be the next line after TOTAL.I was looking at the bcp utility and dts, but dts doesn't seem to havethe performance capabilities (or reliability for that matter) I amlooking for. Bcp seems like it might work if there is some advancedformatting commands that I can't find in the documentation - Anyone?The best I can come up with is to use a high level language such as C#or VB.NET to parse the text file into another text file that is commadelimited, and then use the bcp utility (or bulk insert) to import itinto SQL Server where I can then use TSQL to manipulate it how I want.I am trying to eliminate the high level language parse and just gostraight from file to database. Does anybody know an easier route?TIA
View 1 Replies
View Related
Jul 20, 2005
I must import some exemplary file to database (MS Srrver 2000) ofcourseusing procedure Transact SQL.This file must:1.Read the xml file2. Create table3. Import this date from xml file to my databasePs. I create procedure who File xml imports to base, but unfortunately sheonly schedule when earlier create a table or table is created.So I need (Ithink) create such mini parser in language transact SQL.Does someone have some ideas?For every help Thanks==== example file xml ===========================================<root><Cust><IDosoby>1</IDosoby><Imie>Lukasz</Imie><Nazwisko>Przypadek</Nazwisko></Cust><Cust><IDosoby>2</IDosoby><Imie>Dariusz </Imie><Nazwisko>Mroz</Nazwisko></Cust><Cust><IDosoby>3</IDosoby><Imie>Tomasz</Imie><Nazwisko>Kolo</Nazwisko></Cust></root>================================================== =========--Luk
View 3 Replies
View Related
Oct 17, 2005
My company designs our own banking software written in Progress. Currently twice a year (during our releases) we require our bank customers to run a program on their system that pulls certain data and dumps it to a .dat file. This .dat file was then imported (using another progress program) into a Progress database that our development team created (called stats.db) so we could have access to certain information regarding the customer's software/hardware set-up and utilization of programs,etc. This stats.db is now going away as we have a centralized SQL database for all customer information. The new process will be for our customers to send in their .dat files, they would be forwarded onto me, then I will need to import them into our SQL database. I am struggling in finding a way to do this. Our .dat files contain one field that holds the information, therefore the data is not delimited in such a way that I can just pull it into Excel, Access, CSV, etc. How can I go about pulling data out of these .dat files into a view/table in SQL for importing?
I am at a total loss and have spent hours researching this issue. Any help will be greatly appreciated. Thanks...
View 3 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
Sep 16, 2007
How can I import an mdf file or sql script in ms sql 2000 or
ms sql 2005, also how to do the same if it is am aceess database? Thank youDarlene
View 1 Replies
View Related
Jul 23, 2005
Hello,I am trying to import a data from a DB called Reference Manager. Itwas sent as an XML file.How do I import it? I only have the client tools.Thanks,Tmuld.
View 1 Replies
View Related
Mar 10, 2005
Help needed!
I am a final year uni student and as part of my project I've developed a .Net site with a SQL Server 2000 backend.
This week a virus hit our house and my PC got infected (despite anitvirus and firewall!). Anyway, I have had to format my hard disk and reinstall everything.
My site is back in the wwwroot folder and looks fine, trouble is I need to get the database back up and running.
Before I wiped the disk I took the .mdf and .ldf files from the data file in the SQL Server folder in Program Files.
How can I get my database backup and running.
Ive tried creating a new database (with the same name as the .mdf and .ldf files) and replacing the default created files with the copies I saved but this didnt work.
Any help would really be appreciated as I have to demo this to my lecturers soon and my whole grade is riding on it! (no pressure)
Matt
View 5 Replies
View Related
Jan 7, 2005
Hi I have a database in DB2 and i would like to import this data into sql server 2000. How can i do that?
Thanks.
View 5 Replies
View Related
Dec 28, 2003
Hi,
I'm trying to import a Paradox 8 db into SQL Server 2000 using DTS but the list of data sources in the connections dialog only caters for version 5.x or older. Does anybody know how to get the data source for v8?
If not, is there any other quick and efficient way to import a paradox 8 db as I have to import around 15 of them to do.
Thanks
View 4 Replies
View Related
Oct 22, 2007
hi,
i try to Import and export options in sql server 2000 but the fatal error is coming "Could not create instances of an DTS Package".
View 3 Replies
View Related
Mar 3, 2008
How can import sybase db (sql any where) to sql server 2000 ?
sybase can be imported to sql server how ?
I try to import but there is no option to import sybase db
by tool -> data transformation services -> import / export data
Regards
Mateen
View 2 Replies
View Related
Apr 17, 2007
I need to import some excel files in the Database via DTS. The problem is, that I don't know the names of the Excel Sheets (there are multiple sheets in the excel file). Is it possible to get the names of those sheets? (via activeX or SQL Query or anything, but from a DTS package)
View 1 Replies
View Related
Aug 30, 2006
I've got a situation in whcih I have to import PDF and MP3 files into SQL Server 2000 table.
Thanks,
YL
View 3 Replies
View Related
Jan 25, 2006
I am using the import/export wizard to import all the objects from one database into another.My problem is that any views that use User Defined Functions are failing because the view is being created before the UDF.I have searched for this error, and found the following microsoft article:http://support.microsoft.com/kb/300272/en-usThe article says that I should upgrade to the latest service pack. The problem with this is that I already have the latest service pack When I run the following:SELECT @@VersionI get:Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Could anyone please help me solve this problem.Jag
View 1 Replies
View Related
Jul 15, 2002
Hi All,
I am a new user of SQL Server 2000. Please point me where I am able to get good online sources to be familiar with the SQL Server 2000. I wanted to import the primary file (MDF)and a log file (LDF), which are stored on two different floppy disks into the local SQL server 2000. Please direct me where I should start. Your help is greatly appreciated.
Thanks in advance
Lenka.
View 2 Replies
View Related
Jun 20, 2007
hi all ,
i need to import some tables from Infomix database to the sql server 2000.
what is the easiest way to do it as im quite new at this area.
thanks
linda
View 4 Replies
View Related
Jul 20, 2005
Hi,One of my clients has the following situation. They use Access DB fordata update etc. some business functions while they also want to viewthe Access data quickly and more efficiently in SQL Server 2000. HugeAccess db with over 100 user tables, over 60 MB data.The DTS package that comes with SQL Server 2000 seems pretty "messy"in the sense that it assumes that one needs to do one time import onlyor accurately it does not seem to address periodical import well, likein this case, we need to fresh the Access data in the SQL Serverimport daily. And I don't see DTS package (saved or new) has anoption, that would allow one to delete existing destination datafirst, that is what we want for all freshes. For the saved package,the design screen is too busy, maybe, here one of the icon(function)provides what we need, however, I don't it's globaly applicable, Imean, the whole database vs. a particular table.So, what do you think of the following strategy?1) Create/save the Access import DTS process;2) Create a sql script that would check and drop the destinationdatabase, andrecreate the same new destionation database;The schedule the sql script a minute or two before the scheduledruntime ofthe saved DTS package.Does this approach make some sense or do you have a better idea? Now,a newquestion arises here, would this task of frequent drop and creation ofa reasonably large database contribute to disk fragmentation, whichwould slow down data retrieval among others.Thanks.
View 3 Replies
View Related
Aug 30, 2006
I know my question is kinda weird but it seems I have to do this way or threre are some better ideas.
Could you show me a sample C code to connect to a data server and call a stored procedure to import an image file into SQL Server 2000?
I am writing an extended stored procedure in VS 2003 .NET and it seems extended stored procedure can only work with C code.
Thanks!
YL
View 1 Replies
View Related
Oct 5, 2007
I'm importing a fixed or tab delimited text file into a SQL Server database. When I use SQL 2000 import wizard about 10,000 rows are missed or skipped, but when I use SQL 2005 all the rows are imported successfully.
If anyone has any ideas, I would greatly appreciate it :-)
Thanks!
View 4 Replies
View Related
May 17, 2007
Greetings,
I am unsure if this is the correct forum to send this question, but I can't seem to find any information regarding this problem. If this is the wrong place, please direct me to the correct spot.
I am attempting to import data from a free tables FoxPro database to SQL 2000 using a DTS Package which has worked correctly every day for the past 2 years. Yesterday, I got an error.
The package has around 10 tables that it deletes, re-creates, and populates with data from the Foxpro. All of the tables except one work correctly.
When I try to do an explicit import using the ODBC connection to populate that one table, I get the following error: Context: Error calling Openrowset on the provider.
I created an access database on my local computer and setup an ODBC connection and link tables to the database to see if it would work, and it did. So I thought there might be something wrong with the ODBC data source on the SQL Server, so I deleted it and created a new one, used it and I receive the same error.
I thank you in advance for any assistance or direction you can provide me for finding an answer.
View 4 Replies
View Related
Jun 15, 2007
Hi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
View 17 Replies
View Related
Feb 22, 2005
Hi all,
I have exported all data(including database, tables wiith data) into an XML File.
How to import this xml file into MSDE.?
Can anyone suggest me with good coding in C#?
Thanks and with regards,
Madhusudhan
View 1 Replies
View Related
Sep 18, 1998
I just loaded SQL Server on my local NT Server. Client gave me several .dat files. I created a new device and database with the same name (catalog.dat) shut down the server, overwrote the new .dat file with the client`s file, opened up SQL Server and now the catalog database says "Suspect" and it cannot be accessed via SQL/w....
What am I doing wrong?
I have a solid Access background, but I fear that SQL Server is a totally different animal... HeeeelP!??!?!
Thanks!
Dave McGrath
View 3 Replies
View Related
Jul 15, 2004
Can anyone tell me how to import an mdf file into SQL Server 2000?
Thank you, your help is very much appreciated.
View 3 Replies
View Related
Dec 5, 2004
Hi.
I have my *.mdf and *.ldf file and need to import it to my new sqlserver 2000.
Can anybody tell me how to do it
BEST REGARDS
Badleif
View 1 Replies
View Related
Jun 4, 2008
Hi,
I have a file reg.sql how can I import that file to SQL Server??Can anyone tell me the step??
Thanks
Mbeeee...
View 3 Replies
View Related
Mar 15, 2006
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies
View Related
