Import Oracle Tables in SQL 2L

Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
RohitTo be able to dump Oracle, you'll have to do a complete migration. Most of
the times, this is a manual process. But I am aware of a company that
automated it: http://www.dbbest.com
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rohit Sharma" <lt_rohit_sharma@.hotmail.com> wrote in message
news:OziHQaD3DHA.1392@.TK2MSFTNGP11.phx.gbl...
Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
Rohit

Import Oracle Tables in SQL 2L

Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
RohitTo be able to dump Oracle, you'll have to do a complete migration. Most of
the times, this is a manual process. But I am aware of a company that
automated it: http://www.dbbest.com
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rohit Sharma" <lt_rohit_sharma@.hotmail.com> wrote in message
news:OziHQaD3DHA.1392@.TK2MSFTNGP11.phx.gbl...
Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
Rohit

Import multiple csv into multiple tables

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecietedChicagoboy27 (jeremy.bird@.gmail.com) writes:

Quote:

Originally Posted by

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted

You could use BCP or BULK INSERT, but it appears that you would have to
add quite some control code on top that.

A better alternative could be to turn to SQL Server Integration Services,
which is what the Import Wizard uses. Unfortunately, though, I am
completely unexperienced myself with SSIS, so I cannot assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Import MDB File

I have a current Access 03 application that has a 3 tables that I would like to import, can this be done?

Davids Learning

You can't import mdb file to Sql server. but for alternative you can import data from acces 03 to sql server. the 1st, you must make new database & table (same with mdb file).

You can do, right click your db file in database manager, and then click import data. Please follow instruction from Sql......

Thanks

Jebat

Import from MySQL db adds padding in CHAR (Text) fields

I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the field contents I find that the unused cha
racter positions within the fields have been padded with trailing spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy. I've tried a number of options in the Import wizard (ansi padding etc) and it
seems to have had no effect. Any ideas? Solutions? KB articles?
Thanks for ANY help.
-J
By definition, a char(10) is always padded with spaces up to the length of the datatype. This is why we call
char a fixed length datatype. If you want a variable length datatype, use varchar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JManton" <anonymous@.discussions.microsoft.com> wrote in message
news:D89560CC-AAAB-459C-A039-8073C17EB2ED@.microsoft.com...
> I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in
question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the
field contents I find that the unused character positions within the fields have been padded with trailing
spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy.
I've tried a number of options in the Import wizard (ansi padding etc) and it seems to have had no effect.
Any ideas? Solutions? KB articles?
> Thanks for ANY help.
> -J
|||That was it. I guess there are a few diffs between MySQL and MSSQL. I also had to tweak the recordID field and use IDENTITY attribute. Thanks for the quick reply and on-the-mark-help.
-J

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(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 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(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 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import from an ODBC data source into SQL Server

Hi,

I am trying to import tables from an ODBC data source into an SQL Server 2005 database. I presume that one way to achieve this is to create an Integration Services package, via Business Intelligence Studio (I already used DTS in SQL Server 2000, but not Integration Services) ?

Or is there a simpler way ? For instance, is there an import wizard that woult include an ODBC Data source ?

Thanks i advance.

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_frm/thread/2d0b1220a73e2894/f9adfb6af01a8306?hl=de#f9adfb6af01a8306

Import from adaptive server anywhere 9

Hi,

I'm trying to import data into sql 2005 from sybase sql anywhere 9
(9.0.2.3320) using DTS (using the asademo db). I can select the tables I
want to import but during the actual import on Setting Source Connection I
always get an OLE DB Error :

Could not connect source component.

Error 0xc0202009: Source - contact[1]: An OLE DB error has occured. Error
code : 0x80040E21

Error 0xc020204a: Source - contact[1]: Unabtle to retrieve column
information from the data source.
Make sure your target table in the database is available.

Does anybody have an idea of what is going wrong here ?
Is there another way of accessing sybase that will work and where I can select all tables I need ?

thanx

Did you install the actual OLE DB Provider on your box and is it configured correctly?

Does your login has the appropriate permissions to access the database?

Do you have only asademo.db running? Did you specify the instance name (I think -n in the startup parameter is the right one for naming the instance. You can have several databases running under one instance name in ASA)

If this is not a regular job, you can quickly transport the data by defining a linked server for this database instance and doing a insert into ... select ... from linked.server.databaase.owner.table

Regards

Norbert

|||

Did you install the actual OLE DB Provider on your box and is it configured correctly?
I have a complete installation of Sybase ASA 9.02 and it is configured correctly.

Does your login has the appropriate permissions to access the database?
yep.

Do you have only asademo.db running? Did you specify the instance name (I think -n in the startup parameter is the right one for naming the instance. You can have several databases running under one instance name in ASA)
yep.

If this is not a regular job, you can quickly transport the data by defining a linked server for this database instance and doing a insert into ... select ... from linked.server.databaase.owner.table
What do you mean ? That I connect to sybase from SQL Server Management Studio ?

Regards

Norbert

|||You can define a linked server in Manangement Studio for the ASA database. Then you can start the query insert into ... select ... from the query window. This should create a table and should move the data via the OLE DB Providersql

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?
|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

Import from access db to sql express db

How do I import tables from an access db to sql Express?
reidarTHi
I did some testing on DEV edition and it works just file
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\northwind.mdb";
User ID=Admin;Password='
)...Customers
Take a look at linked servers in the BOL as well
"reidarT" <reidar@.eivon.no> wrote in message
news:%23S9JQgPoGHA.680@.TK2MSFTNGP03.phx.gbl...
> How do I import tables from an access db to sql Express?
> reidarT
>

Import from access db to sql express db

How do I import tables from an access db to sql Express?
reidarTHi
I did some testing on DEV edition and it works just file
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\northwind.mdb";
User ID=Admin;Password='
)...Customers
Take a look at linked servers in the BOL as well
"reidarT" <reidar@.eivon.no> wrote in message
news:%23S9JQgPoGHA.680@.TK2MSFTNGP03.phx.gbl...
> How do I import tables from an access db to sql Express?
> reidarT
>

Import Export wizard

Hello,

I am having alot of problems with the import export wizard. I am trying to copy over around 315 tables to a new database. When the tables are copied over to the new database they are copied without any of the original constraints. Does anyone know why this could be happening? What I am trying to accomplish is a simple database transfer with the exception of about 10 to 15 tables from the original database. I do not understand why this is such a problem. I did not create a package, as this is going to be a one time shot.

Any help would be appreciated.

Thanks,

David

My understanding is that its not supposed to copy constraints. The import wizard is all about moving data, not schema objects. Of course, if you're moving data into a database then it has to have somewhere to put it so in this case, yes, it creates some tables for you. If you want to do move other schema objects then the simple answer is to script them out in SSMS and run the scripts on the target server. This is alot easier in SQL2005 than it used to be because in SSMS you can now change the connection so you dont have to copy and paste between differernt windows.

-Jamie

Import export wizard

In sql2k i can select bunch of tables in EM and export to a different db also I can schedule this activity.

How to do the same in SQL2K5?

Right-click on the database

Point to Tasks-->Export Data

Go thru the wizard, selecting the tables that you need.

You will be prompted to save the resultant SSIS package somewhere. Once you have saved it you can schedule it thru SQL Server Agent.

-Jamie

Import Excel Spreadsheet to SQL 7

Running SQL Server 7 and Excel 2002 on an Win XP Pro machine.
I created a database with two tables, ran a program that added some data to
both tables, then used the Data Transformation Services Export Wizard to
export the tables to an Excel workbook containing each table as a separate
spreadsheet. So far, so good.
I manually made a number of changes to the spreadsheets, altering some of
the values that had been exported, adding some more rows to one of the
spreadsheets and sorting the rows. Now when I go back to SQL Server and use
the Wizard to import the data back into the tables, not all of the data
comes back in. Of about 250+ rows in one of the tables, less than 200 of
them get imported. I don't see any errors when running the wizard. I did a
TRUNCATE TABLE in query analyzer before doing the import to clear all the
rows in each table.
Is there some kind of marker to put in the file that tells the import wizard
where to stop? For example, when printing a spreadsheet, you "set" the
print area. Is there a corresponding function to set the "import" area?
Thanks
John Schneider
When you export to Excel from SQL Server a named range is created on the
Excel worksheet. In Excel select Insert on the menu and then Name. You
should see a name in the dialog box. Click on it and update the range and
then the full data set should get imported to SQL server.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Bingo! Thanks for the reply. I NEVER would have found that setting.
John
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:cP9tbKX0EHA.764@.cpmsftngxa10.phx.gbl...
> When you export to Excel from SQL Server a named range is created on the
> Excel worksheet. In Excel select Insert on the menu and then Name. You
> should see a name in the dialog box. Click on it and update the range and
> then the full data set should get imported to SQL server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Import Excel data into MS SQL Server

Hi,
We are trying to import Excel data (Excel 2002) into some SQL server tables.
However, we have tried several things but they all seem to fail.
1) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=\\server\file.xls', [Sheet1$])
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give
any information about the error.].
2) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
(*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
open database '(unknown)'. It may not be a database that your application
recognizes, or the file may be corrupt.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
the following command on our production server then we get the Excel data.
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
4.0;Database=\\server\file.xls', [file$])
I've checked several sources on the internet and most of them seem to
conclude that this error is permission related but I'm sure that the
production server does have access to the Excel file. I have a feeling that
some setting (registry) is missing or is incorrect or that a file is missing
or has the wrong version (it seem that we can access older Excel files).
All help is appreciated,
Alain Sienaert
Cortex
Alain,
I am using DTS to export data from Excel. I have similar case like you, exporting data from sharedrive\Excel. However I am not using OpenRowset function. I have just mapped Each excel column to SQL table. we are using special Id when DTS schedular invokes
that job, So major special setting we did is to have access for DTS schedular ID to that Shared drive and excel.
just sharing my thought..not sure it will help..
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling that
> some setting (registry) is missing or is incorrect or that a file is missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
>
>
>
|||Alain,
I am using DTS to export data from Excel. I have similar case like you, exporting data from sharedrive\Excel. However I am not using OpenRowset function. I have just mapped Each excel column to SQL table. we are using special Id when DTS schedular invokes
that job, So major special setting we did is to have access for DTS schedular ID to that Shared drive and excel.
just sharing my thought..not sure it will help..
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling that
> some setting (registry) is missing or is incorrect or that a file is missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
>
>
>

Import Excel data into MS SQL Server

Hi,
We are trying to import Excel data (Excel 2002) into some SQL server tables.
However, we have tried several things but they all seem to fail.
1) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=\\server\file.xls', [Sheet1$])
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give
any information about the error.].
2) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
(*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver
] Cannot
open database '(unknown)'. It may not be a database that your application
recognizes, or the file may be corrupt.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
the following command on our production server then we get the Excel data.
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
4.0;Database=\\server\file.xls', [file$])
I've checked several sources on the internet and most of them seem to
conclude that this error is permission related but I'm sure that the
production server does have access to the Excel file. I have a feeling that
some setting (registry) is missing or is incorrect or that a file is missing
or has the wrong version (it seem that we can access older Excel files).
All help is appreciated,
Alain Sienaert
CortexAlain,
I am using DTS to export data from Excel. I have similar case like you, expo
rting data from sharedrive\Excel. However I am not using OpenRowset function
. I have just mapped Each excel column to SQL table. we are using special Id
when DTS schedular invokes
that job, So major special setting we did is to have access for DTS schedula
r ID to that Shared drive and excel.
just sharing my thought..not sure it will help..
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server table
s.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS SQ
L
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS SQ
L
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driv
er] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initializ
e
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling tha
t
> some setting (registry) is missing or is incorrect or that a file is missi
ng
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
>
>
>

Import Errors from MS Access

I encountered errors while importing tables from an Access. The only tbales
that errored had date/time fields and it seems SQL didn't like them. The tab
les work just fine in Access so I'm surprised SQL didn't like them. Anyone s
ee this before?Alan,
Most likely cause: MS Access supports dates as early as Jan 1, 100, while
SQL Server only supports dates back to Jan 1, 1753. You probably have a date
prior to Jan 1, 1753. Before you dismiss this idea, keep in mind that a
simple data entry error can morph Jul 15, 1999 to Jul 15, 199 :-)
Use an Access query to locate dates earlier than Jan 1, 1753.
Chief Tenaya
"Alan Fisher" <anonymous@.discussions.microsoft.com> wrote in message
news:838D8D52-BB99-416A-A4D2-4781DCE7040F@.microsoft.com...
> I encountered errors while importing tables from an Access. The only
tbales that errored had date/time fields and it seems SQL didn't like them.
The tables work just fine in Access so I'm surprised SQL didn't like them.
Anyone see this before?|||On Tue, 23 Mar 2004 16:01:06 -0800, "Alan Fisher"
<anonymous@.discussions.microsoft.com> wrote:
Different versions of Access have different levels of support for
upsizing. Try with Access XP or 2003.
When push comes to shov, it's not so hard to build those few tables
yourself, and write append queries to copy over the data.
-Tom.

>I encountered errors while importing tables from an Access. The only tbales that er
rored had date/time fields and it seems SQL didn't like them. The tables work just f
ine in Access so I'm surprised SQL didn't like them. Anyone see this before?

Import Design Question

I have at least three tables into which the user can import data.
The three tables are 1) Portfolio 2) Account 3) Transactions. The user
will provide us three files - one for each entity (in the file) related
by a userdefined NUMBER field.
These three tables are linked by foreign keys but the key columns in
the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
TRANSACTIONID).
The files can be really big so the import has to be quick. Right now I
first insert the rows into PORTFOLIO, get the generated IDs , loop
through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
ID and then import the TRANSACTIONS. This process of retreiving and
setting the IDs is consuming a long time and making the import very
slow. I am wondering if using IDENTITY is the right design for the
above mentioned tables. Is it better to generate the key values myself
using a SEED table?
Is there a more elegant solution to the problem?
Thanks.Looping? In SQL?
Don't you have any natural keys in the source files? If there is a natural
key (or at least one or more candidate columns) use that to achieve a
set-based solution.
Whether you use identity or any other kind of key generation is IMHO
irrelevant, since there really should be a more natural way to uniquely
identify each row of data (i.e. a natural relationship between the sets).
ML
http://milambda.blogspot.com/|||S Chapman wrote:
> I have at least three tables into which the user can import data.
> The three tables are 1) Portfolio 2) Account 3) Transactions. The user
> will provide us three files - one for each entity (in the file) related
> by a userdefined NUMBER field.
> These three tables are linked by foreign keys but the key columns in
> the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
> TRANSACTIONID).
> The files can be really big so the import has to be quick. Right now I
> first insert the rows into PORTFOLIO, get the generated IDs , loop
> through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
> ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
> ID and then import the TRANSACTIONS. This process of retreiving and
> setting the IDs is consuming a long time and making the import very
> slow. I am wondering if using IDENTITY is the right design for the
> above mentioned tables. Is it better to generate the key values myself
> using a SEED table?
> Is there a more elegant solution to the problem?
> Thanks.
>
Sounds like your relationships are like this:
Portfolio -> AccountID -> TransactionID
meaning a single portfolio links to multiple Account ID's, a single
account links to multiple Transaction ID's.
You might consider changing this. Look for natural keys to link on
instead of manufacturing one (using an ID value). For instance, a
transaction should link to an account via ACCOUNT NUMBER. A portfolio
should contain multiple ACCOUNT NUMBERS, not account ID's.
What this will allow you to do is to bulk import all three tables
without having to mess with finding ID's, updating ID's, etc. The data
is already naturally linked together.|||Unfortunately there are no natural keys. The Portfolio Number and
Account Number can be gauranteed to be unique only within a batch.
Also, I am NOT looping through in Sql, looping through rows in the
dataset inside the program.
Tracy McKibben wrote:
> S Chapman wrote:
> Sounds like your relationships are like this:
> Portfolio -> AccountID -> TransactionID
> meaning a single portfolio links to multiple Account ID's, a single
> account links to multiple Transaction ID's.
> You might consider changing this. Look for natural keys to link on
> instead of manufacturing one (using an ID value). For instance, a
> transaction should link to an account via ACCOUNT NUMBER. A portfolio
> should contain multiple ACCOUNT NUMBERS, not account ID's.
> What this will allow you to do is to bulk import all three tables
> without having to mess with finding ID's, updating ID's, etc. The data
> is already naturally linked together.