Periodical Import Of Access Db Into SQL Server 2000

Jul 20, 2005

Hi,

One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in SQL Server 2000. Huge
Access 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 only
or accurately it does not seem to address periodical import well, like
in this case, we need to fresh the Access data in the SQL Server
import daily. And I don't see DTS package (saved or new) has an
option, that would allow one to delete existing destination data
first, 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, I
mean, 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 destination
database, and
recreate the same new destionation database;

The schedule the sql script a minute or two before the scheduled
runtime of
the saved DTS package.

Does this approach make some sense or do you have a better idea? Now,
a new
question arises here, would this task of frequent drop and creation of
a reasonably large database contribute to disk fragmentation, which
would slow down data retrieval among others.

Thanks.

View 3 Replies


ADVERTISEMENT

Import Data From MS Access Databases To SQL Server 2000 Using The DTS Import/Export

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

SQL Server Compact 3.5 Beta Import From MS Access 2000

Sep 5, 2007

Greetings to all!
Can MS Access 2000 databases be imported into SQL Server Compact 3.5 Beta?

View 6 Replies View Related

Error Trying To Import MS Access 2003 Database Via SQL Server Import And Export Wizard - Too Many Sessions Already Active

Nov 29, 2006

I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.

I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.

Trouble is that it gets most of the way through the import until it spews forth the following error messages:

- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).

There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.

Does anyone know how I can get the import to work?

View 2 Replies View Related

Import-export (MS-Access To SQL SERVER)

Mar 11, 2000

Hi everybody,

Do all the relationship between the tables
breaks while importing from MS-Access to
MS-SQL Server

Thanks in Advance
Harish

View 1 Replies View Related

Import Some Data From Access To SQL Server

Aug 17, 2004

I have created some DB tables on SQL Server. Now I want to import some data from Access using Data Transformation Services.

The APPLICATION table I have created has the following columns:

AppID, AppName, AppMnem, GripsID, Decommissioned, Last Modified, Modified By

AppID is the primary key and is an identity column with an autoincrement on insert.

The query I used to import the data from Access is:


select `T>Application>General`.`Application Name`, `T>Application>General`.`Application Mnem`, `T>Application>General`.`GripsID`, `T>Application>General`.`Decommissioned`, `T>Application>General`.`Last Modified`, `T>Application>General`.`Modified By`
from `T>Application>General`
where `T>Application>General`.`Application Mnem`='TMS'
order by `T>Application>General`.`Application Mnem`


The transformation I used is:


'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("AppName") = DTSSource("Application Name")
DTSDestination("AppMnem") = DTSSource("Application Mnem")
DTSDestination("GripsID") = DTSSource("GripsID")
DTSDestination("Decommissioned") = DTSSource("Decommissioned")
DTSDestination("Last Modified") = DTSSource("Last Modified")
DTSDestination("Modified By") = DTSSource("Modified By")
Main = DTSTransformStat_OK
End Function


However, I keep gettting the following error:


Error at destination for row number 1. Errors encountered so far in this task: 1.
Instert error, column 1 ('AppID', DBTYPE_I4), status 10: Integrity violation; attempt to insert null data or data which violates constraints.
Unspecified Error


Any ideas as to what the problem is?

Thank in advance

View 3 Replies View Related

Use C# To Import Access Tables Into SQL Server

Apr 30, 2007

Hi



I have to import some Access 2003 tables into a SQL Server 2005. Since I have to do this every 6 months (different Access db into same SQL-Server) and since I have to check every single row I can't do it with the import data wizzard. I need to import the access tables into "temporary" tables in sql server and from there I'll put the rows into the correct tables...



Does anyone know how to import Access tables into SQL-Server 2005 with C#?



Thank you for your help!



Michael

View 9 Replies View Related

Import Data From Sql Server Or Access

May 8, 2008

Hello,

I have been looking for a way to import data from sql server 2005 or access into sql compact edition.

The short end of the story is that our product supports 3 databases, sql server, mysql, and access(for standalone deployments)

if we can use sql compact instead of access i believe it woudl simplify our support and maintenance, however i am struggling to find a way to get our existing data into compact.

This product is not a mobile application, and the .sdf files will be deployed to desktops, the only way ive seen to convert data so far is to use activesync over a mobile device.

is there any other way to get our existing sql server or access databases into compact?

Thanks

View 4 Replies View Related

Performance Issues - Access 2000 Frontend SQL Server 2000 Backend

Jul 23, 2005

Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike

View 4 Replies View Related

Maximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000

May 27, 2008











Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000

Number of instances per server
n/a
n/a
16
16

Number of databases per instance / server
n/a
32,767
32,767
32,767

Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647

Number of users per database
n/a
16,379
16,379
16,379

Number of roles per database
n/a
16,367
16,367
16,367

Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB

Number of columns per table
255
1024
1024
1024

Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage

Number of bytes per row





(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB

Number of columns per query
255
4,096
4,096
4,096

Number of tables per query
32
256
256
256

Size of procedure / query
64 KB
250 MB
250 MB
250 MB

Number of input params per procedure / query
199
1,024
2,100
2,100

Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB

Depth of subquery nesting
50
32
32
32

Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)

Number of columns per index
10
16
16
16

Number of characters per object name
64
128
128
128

Number of concurrent user connections
255
32,767
32,767
5

View 1 Replies View Related

Problem Using Access 2000 As A Front-end To SQL Server 2000 Tables

Jul 23, 2005

I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana

View 5 Replies View Related

SQL Server 2005 Express Import Of Access

Jan 15, 2006

  

  I have installed SQL Server 2005 Express on a laptop for development purposes and would like to import some Access databases that I was using coupled to programs written in VB 6.

 If using SQL Server 2000 I could use the management studio to automate the import but I can not find any way to do this in Management Studio Express. Would have thought that this would be the typical migration route - Access to Server Express

   Is there some way to automate this procedure?

Thanks

 

 

View 8 Replies View Related

SSIS Access Import To SQL Server 2005

Feb 22, 2007

Hi,


I have created a SSIS dtsx package file on my file system that I want to manipulate in .NET

The package gets an access DB file and the a Preparation SQL Task creates the necessary sql statements for the data flow task. The data flow maps the tables from the source to the destination and then if you execute the package, it copies the Access DB into SQL Server.

My problem is, I want to manipulate this package in code, so that I can change the path to the source Access DB and then re execute the Preparation SQL Task so it recreates the new SQL statements, and then I want to update the data flow so that I it uses the new SQL and creates the necessary tables.


How is this possible in .NET

I have looked everywhere and have found some topics but with no luck.

Can anybody please help me

View 3 Replies View Related

How To Import MS Access Database Into SQL Server 2005

May 24, 2007



Hello,

I have a database MS access: test.mdb.

Is it possible to attach to SQL SERVER 2005 (or 2000 than to 2005), or any other solution ?

I need to transfer all the application:

structure and data.

How to do it?

View 5 Replies View Related

SQL Server 2014 :: How To Import Data To Server From Access Database Using Script

Sep 22, 2015

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO

[code]...

The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

Secondly I need to make 2 more changes to the code posted above.

1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.

2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

View 7 Replies View Related

Converting Data From Access 2000 To SQL Server 2000

Oct 18, 2004

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks

View 1 Replies View Related

Access 2000 On Windows 2000 Can't Interact With SQL Server 7.0

Mar 13, 2001

We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new
server which is Windows 2000 based and the domain is different from
the SQL servers domain. We then installed Access 2000 on Windows 2000
to use with terminal server. But I noticed that there was a problem
with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but
I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem.

In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.

View 1 Replies View Related

Import XMS Into SQL Server 2000

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

Import Data From Access 2003 To SQL Server 2005 Via T SQL ?

Oct 5, 2007

I have a customer using a web app where the data is drawn from a database in SQL Server 2005.

In addition to its normal tables, the database has two lookup tables which are deleted, recreated, and repopulated once a week. The data for these two tables comes from two queries in an Access database which essentially turn many related tables into two flat tables.

I know this can be done with SSIS, I've done it, I suppose it could be done witth SSMA for Access, but could this operation be done with a stored prodecure ? Most steps would not be a problems. They would be:

- Drop the two tables in SQL Server (no problem)
- Recreate them (no problem, maybe better to just delete all existing records, either way)

- Connect to the query in the Access database (here is where I have questions)
- Simply do an INSERT from the Access query to the SQL table.

So my question is whether we can connect to an Acess query from a stored proc, I suppose using ODBC.

Can this be done? If so how? Are there any papers on this topic?

Msny thanks,
Mike Thomas

View 5 Replies View Related

Import Data Form Access To Sql Server 2005

Feb 20, 2007

I have a database in ACCESS and i want everytime that there is a change made in Access the cude in SQL server to be automatically updated .Is there a way that this can be done with Integration Services?

View 1 Replies View Related

SQL Server 2000 Database Import

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

Import Db2 Data Into Sql Server 2000

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

Import Paradox 8 DB Into SQL Server 2000 Using DTS

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

Err-sql Server 2000 While Using Import And Export

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

Import Sybase Db To Sql Server 2000

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

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 fileof some sort but I am uncertain. The source agency hasn't returned anyof 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 chosea text format, regardless of what delimiter I choose, the format isstill really ugly. when I pull it up in a huge text editor, it is hardfor me to tell what it is there.I saw in one of my searches that it could be a comma delimited (it'snot) .. could be a unisys file? I know it's not much information to goon - but where should I start in trying to get this into my databasewithout knowing the format? Any suggestions would be greatlyappreciated.Thanks!Bethany*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Import Excel To SQL Server 2000

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

Can We Use DTS To Import PDF And MP3 Into SQL Server 2000 Table?

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

Sql Server 2000 Linked Server To Access 97/2000 Incompatability

Jul 20, 2005

We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.

View 1 Replies View Related

Cannot Import/link Data From DBase With SQL Server DTS Nor Access Anymore..

Jul 20, 2005

Hi,SQL Server 2000 SP3Windos 2000 Server SP4I have a DTS package that imports data from a dBase IV databse withfiles located in two folders (dBF1 and dBF2). I use a transform datatask to transform the data.They were running properly, but last week we installed W2K SP4, andnow the transform task for files from dBF2 are not working properly.I have two tranform tasks to extract data from files in dBF2 folder.If I double click to open the transform data task of either of them,Enterprise Manager crashes with the errrormmc.exe applicatio ErrorThe instruction "xxxx" referenced memory at "xxx". The memory couldnot be read.Althoug the transform task for one of the files will run, the otherwill not run giving the messageError Source: DTS packageCatastrophic failureAlso, I have an Access database that has links to the same dBasefiles. For files from dBF2 folder, I'm able to see the data from oneof the files, but if I double click to see the data from the other,access crashes with no specific error message. Nothing has changed ondBase related files (permission wise).The transform tasks to extract data from the other dBase folder (dBF1)files are working fine, and data is accesible from Access.Any advice how to tackle this one?

View 2 Replies View Related

MS Access 2000 Querying SQL Server 2000

Mar 15, 2004

I am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.

When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.

This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.

I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.

I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.

Here is the exact scenario:

Problem #1:
A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made.
I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)

Problem #2:
A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.

SQL Server version is 2000, with SP3a applied.

I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:

Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI;
Recordset Cursor type: adOpenDynamic
Recordset LockType: adLockOptimistic

I open the recordset using:
rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

Problem # 1

After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:

rst.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Problem # 2
I set the recordsource using: (in the form_open)
Me.RecordSource = "Select * from mytable where myfield = 'data'"

In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.

Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.

Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?

Thanks!

View 3 Replies View Related

Migrating From Access 2000 To SQl Server 2000

Jul 20, 2005

Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin

View 1 Replies View Related

Import Complex XML File Into Sql Server 2000

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







Copyrights 2005-15 www.BigResource.com, All rights reserved