Importing Access DB: export

Showing posts with label export. Show all posts

Friday, March 30, 2012

imports table structures in SQL 2000 into Excel

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

AgustinaRun this in Query Analyzer: (common data types, add the the case statement for more)


select name,
case xtype
 when 56 then 'Int'
 when 127 then 'BigInt'
 when 167 then 'VarChar'
 when 175 then 'Char'
 when 60 then 'Money'
 when 58 then 'SmallDateTime'
 when 104 then 'Bit'
 when 173 then 'TimeStamp'
 when 61 then 'DateTime'
 when 48 then 'TinyInt'
 else 'Other' end,
length
from syscolumns
where id = (
select id
from sysobjects
where name = 'TheTableName')
order by colid

|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '<DATABASE NAME>' AND TABLE_SCHEMA = '<DB OWNER>' AND TABLE_NAME = '<YOUR TABLES NAME>'

sql

Wednesday, March 28, 2012

Importing text file problem using Import and Export Wizard

Hi,

I'm trying to import text file (generated by UNIX - collation ISO LATIN 2) into the database using SQL SERVER 2005 Import and Export Wizard. I have got a problem with importing a decimal number, because in that column are not only decimal numbers (that's OK), but there are also spaces (not null, the column is filled by spaces and it looks like | |). When I'm trying import that file, then will occur the problem of truncation and import stops.

I can import that data by BULK INSERT, but I would like to import it by Import and Export Wizard at once without using subsequent conversions.

I *think* you'll have to do this in two steps. One to load that field as a varchar field. Two to convert that data (excluding spaces) to decimals.|||You could use the package generated by the import/export wizard, and the edit it with BIDS to add the logic necessary to convert/replace those column before the insertion...

Monday, March 26, 2012

importing store procedures, How-to

Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > Hi,
> > Can anyone tell me how I can import/export stored procedures in MS SQL
> > 2005
> > from a different SQL server? Used to use the right click menu on a
> > database
> > (tasks, import/export) to do the job using DTS, and update all the SP and
> > UDF at once. Now I cannot seem to see a way of doing it using IS
> > (Integration Services) in 2005, only able to import/export tables and
> > views.
> >
> > Can anyone please advise, there's got to be an easy way, but I've had no
> > luck looking on the web or books online.
> >
> > Thanks
> > Pritchie
> >
> >
> >
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
> > If someone knows how I can export the procedures then re-import them, it
> > would be appreciated.
> >
> > Thanks
> >
> > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > Hi,
> > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > 2005
> > > from a different SQL server? Used to use the right click menu on a
> > > database
> > > (tasks, import/export) to do the job using DTS, and update all the SP
and
> > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > (Integration Services) in 2005, only able to import/export tables and
> > > views.
> > >
> > > Can anyone please advise, there's got to be an easy way, but I've had
no
> > > luck looking on the web or books online.
> > >
> > > Thanks
> > > Pritchie
> > >
> > >
> > >
> >
> >
> >|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> > Why not generate the scripts and then load into the new database? If
> 2000,
> > right click DB & choose "Generate SQL Script...", then choose stored
> > procedures, and click preview. From there, you can grab the scripts.
> Then
> > go over to your new database and copy the script into the query window and
> > load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
> then
> > choose what you need, etc.
> >
> > "Shayaan Siddiqui" wrote:
> >
> > > If someone knows how I can export the procedures then re-import them, it
> > > would be appreciated.
> > >
> > > Thanks
> > >
> > > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > > Hi,
> > > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > > 2005
> > > > from a different SQL server? Used to use the right click menu on a
> > > > database
> > > > (tasks, import/export) to do the job using DTS, and update all the SP
> and
> > > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > > (Integration Services) in 2005, only able to import/export tables and
> > > > views.
> > > >
> > > > Can anyone please advise, there's got to be an easy way, but I've had
> no
> > > > luck looking on the web or books online.
> > > >
> > > > Thanks
> > > > Pritchie
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

Friday, March 23, 2012

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
Thad
DId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad
|||Hey Jens,
It worked! It was great help!
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in m
y
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>sql

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
--
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens SÃ¼Ã?meyer" wrote:
> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> > Hi all,
> > I am having problems importing data from Access 97 .mdb files into
> > my SQL Tables.
> > Using DTS, I invoked the Import/Export wizard.
> >
> > When prompt for the source, I specified Microsoft Access and it's
> > path/filename.
> > I did not enter username and password for the source.
> >
> > But when DTS executes the query to import data, it prompts me this error:
> > "Records cannot be read, no read permission on tablename"
> >
> > When i entered the userid and password for the workgroup file(mdw),
> > it says that "cannot start application. Workgroup information file is
> > missing or open exclusively"
> >
> > How can I work around this? Or Is there other way to import data into SQL
> > table? I tried to perform export from Access 97. It creates a new table in
> > my
> > SQL Server instead, without keys, indexes, relationship.
> >
> > Any thoughts/help greatky appreciated.
> >
> > --
> > With Thanks,
> > Thad
>
>

Importing NULL into table SQL Server 2005

I've tried to import a text file into a table using the Import/export wizard.

My problem starts with some columns that should have NULL values (i.e. zero-length string) but the wizard doesn't recognize that it's NULL.

How do I solve the problem?

Thanks,

Mich

First, a zero-length is not NULL. NULL is a special character.

If you want NULLs to be inserted, open up the package and edit it (that the import/export wizard created for you) and add a derived column transformation before going to the destination.

Use an expression similar to this for each of your columns:

[myColumn] == "" ? NULL(DT_WSTR,20) : [myColumn]

I'm going by memory, but I believe that would work. The above assumes myColumn has a length of 20 bytes.

|||

Thank you for your answer.

My knowledge of the import/export wizard is limited.

I would like to know whether the expression should be added in the "Suggest Types" button or elsewhere.

Is the expression related to the destination column names or the columns the Wizard created?

I'd appreciate it if you could give me a step-by-step guide on how to do it, since I found the sql help file very unhelpful.

Thank you again.

Wednesday, March 21, 2012

Importing into Test Database

SQL 2000, Windows 2000. I need to find a way to export our data from
our production database and import it into a test database. I can do
it to a freshly created test database I just have trouble doing it
once the test database is already populated. Is there a script I can
write that will automate it by
1) Deleting the test database
2) Creating a new one
3) Exporting the production tables and views
4) Importing into the new test database

What are my options here?

Thank you in advance."Justin Allen" <contactjustinallen@.hotmail.com> wrote in message
news:3b993d0.0402130629.581cecb4@.posting.google.co m...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

See my reply to your previous post.

Simon|||contactjustinallen@.hotmail.com (Justin Allen) wrote in message news:<3b993d0.0402130629.581cecb4@.posting.google.com>...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

If you are looking to simply bring all the data over from the source
to the destination db, you may try to dump the tables to a sql file
and run the load on the destination db.

Also have a look at sqlporter database migration tool
from realsoftstudio.com

Hope it helps.

Importing from PL/SQL to MSSQL

Hi there,

I've done some searching around on this subject and can't find
anything helpful or non-expensive so far.

I need to export some tables from my companies Oracle server locally
so I can work on a reporting tool which (hopefully) will speed some
things up on our weekly report runs. I know I could just use an ODBC
link and get the data directly from our server but unfortunatly it's
not the newest or fastest of things and these are pretty big reports,
so my idea is to export the tables and run the queries through an
MSSQL database on a faster machine.

However, not being an Oracle or MSSQL developer I'm not too sure how
to export the tables I need. I've looked all over for a script or a
tool to help me do this, but all I have found are tools that cost a
hell of a lot of money, and for something that I don't even know will
work just yet, I don't want to put a PO through for nothing.

I was wondering if anyone has ever used a script that is available or
a GPL'd tool that might help me out here? Or at least point me in the
direction so that I can get started on this.

Thanks in advance for any suggestions

--
StuartIIRC there is a SPOOL command that will redirect the ouput of a SELECT
statement to a file. It should be fairly easy to import that to MSSQL
using DTS or BCP. However, there are probably several ways of doing
this and I'm not an Oracle expert so for better advice you may want to
repost in an Oracle group.

--
David Portas
SQL Server MVP
--|||SQL Server DTS is your best bet for this.

importing from access error

Let me describe my situation

I am running SQL Server 2005. I created all the tables/columns/keys I needed. I export the data to Access.

In Access, I add all the data for the tables. Then I use SSIS to import-append the data back in to SQL Server 2005.

I get this error:
-
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - tbl_name" (19) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
--

I can use the import tool (SSIS) to create new tables, but not append the data. Creating new tables makes all the tables to lose information about field lengths etc, so this is not an option

any help would be greatly appreciated.

Thanks!

Could you verify the metadata of your existing tables matches the tables you want to import. The error message hints a problem in that area.

Thanks.

|||yea..turns out the metadata was in error...but the access DB's metadata is the result of an export of the SQL Server DB.

Why doesn't Microsoft let you import what you export? just seems to make sense to me.
|||

It is because the data type spectrum is much richer for SQL Server than for Access. By transferring data from SQL Server to Access you are basically downgrading richness of your data and it makes the reverse process difficult without the user intervention.

Even for equally rich data type spectrums of heterogeneous data sources you will end up having to map more than one type to the single destination type. When going in the opposite direction you will have to pick one of possible types as a best match, and the best match will not always be the proper one.

HTH.

Monday, March 19, 2012

Importing Excel data into SQL Server 2005 with a twist!

Hi,

I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.

However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.

So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.

TIA,

Graham.

I would approach the problem by importing all of the spreadsheet rows and then process to the required table.

Importing dBase files with the SSIS Import/Export Wizard

I saw this post by dterrie in the Wishlist thread and I just wanted to second it:

"How about bringing back a simple dBase import. The SSIS guys are clearly FAR out of touch with reality if they think people who handle data no longer need to work with dbf files. I've seen alot of dumb stuff in my day, bit this is just sheer brilliance. I just love the advice of first importing into Access and then importing the Access table. Gee, why didn't I think of such a convenient solution. I could have had a V-8."

I've been struggling with this the last couple days and finally decided to import the dBase III file into Access and then import that into SQL Server 2005. Imagine my surprise when I discovered this was the current recommended method.

That's just ridiculous. Can someone tell me why they would reduce some of the functionality of SQL Server from 2000 to 2005? This was a very easy process in SQL Server 2000...

Philip,

Could you record your request here:

http://lab.msdn.microsoft.com/productfeedback/default.aspx

That way a request will be passed directly to our bug system. It will increase a chance to address it sooner, and you will be informed about the progress.

Thanks.

|||

Thanks Bob! That's a great idea.

I know you guys have been catching some flack over the anemic ODBC support.

Here's hoping there is a service pack for it soon!

Friday, March 9, 2012

Importing data into SQL Server 2005 Eval version

Just installed SQL Server 2005 Eval version and Management Studio does not display any Import/Export functions to load data into tables of an existing database. I thought that this feature was turned off only in Management Studio Express.

Using the Object Explorer, right-click on the database, select [Tasks], then [Import Data...] or [Export Data...]

Importing data into SQL

With SQL 2000 there was an Import/Export facility for importing data into a Sql database. Could somebody tell me how to import an old database which could be in Csv text or Paradox into my new SQL 2005 tables. Thanks

In Management Studio, connect to any sql server instance, right-click on a database > tasks > import data

This will take you do the import/export wizard

|||

Start -> Run -> dtswizard.exe will also bring up the Import Export Wizard for you.

Importing Data from Oracle 8i/9i to SQL Server 2005 using SQL Server Import and Export Wizard (A

Hi All,

I have become frustrated and I am not finding the answers I expect.

Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.

So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.

I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good

Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.

When I get to the end it progresses to about 20% and then throws this error about 300 or so times:

Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!

Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.

This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!

This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.

Help!!!

Hi Irish

I Don't have an answer for you, but since we are in the same situation as you I thought that I would share my thoughts on this.

At first I tried to use Microsoft SSMA tool to do the migration of both schema and data, but soon I came to the conclusion that it still has too many bugs to be to any help in migrating the data. And according to the SSMA team, they won't release a new version until they have rewritten the current java version to .Net.
So I tried the "Import Data" like you did. But because of the AlwaysUseDefaultCodePage issue and because I need to change the Oracle schema to dbo which I can't do for all tables at once in the import wizard, I have moved to the SSIS package solution like you have.

Creating a Data Flow task 600 time (a little less in my case though) is not what I had planned to use a week for so I will now try to create the SSIS package programatically.
I have found this documentation:
http://msdn2.microsoft.com/en-us/library/ms135946.aspx
about how to create a dtsx package in code which I will try to do the next days.

I'm sorry that I wasn't for much help, but hopefully we can find a solution to our mutal problem together.

Best regards
Claus Pedersen

|||

Claus,

Thanks for the reference. I will try that block of code as well, but I'm not optimitic.

What ever happened to "DTS will be easier in 2005"?

|||

Generally I like the new features of Sql 2005 SSIS, but I agree that for tasks like this they still have a lot to do. I don't think that Sql 2000 would be easier.

Did you try to create a SSIS programmatically?

I'm working on it at the moment, but get some strange errors with the connection. It fails with a HRESULT: 0xC0048021 error as soon as it tries to connect to a OleDb connection (to populate columns). I have tried to load a working package in my code and proceed with that, but it still fail.

Best regards
Claus Pedersen

|||

Hi Irish

Have you tried to use the ForEach control?

I have experimented a little with it, and it seems like it could work. There are some problems with update of column names though.

Claus

|||

Hey Claus,

I have not had a single oppourtunity to work on this since making the last post.

I agree that SQL 2000 would not have been any less of a challenge. However, based on the documentation I've read this is supposed to be a simple process allowing a full conversion of an Oracle Database to SQL 2005. I've not seen it so far. That's the whole point of my excursion.

I will try the ForEach and see what I get. Just seem like a major pain to have to go through all of the objects piecemeal.

|||

I agree. I can imagine a developer on Microsoft Sql server team telling his boss that "it could be worse" which converted to marketing language is "extreemly simple".

Wait a moment with the foreach solution. It might not work anyway because of the different metadata from table to table. I'll keep you updated.

/Claus

|||

Run through all of the steps, but uncheck Execute Immediately, and check Save SSIS package. Save it to the file system, and then edit the AlwaysUseDefaultCodePage property.

Or are you saying it won't even create the package for you?

|||

Sean,

Yes, that's exactly what I am saying. When I was building this the plan was to create the SSIS Package and then see how I could edit it to apply to a variety of different Oracle Instances and versions.

So, I would change the AlwaysUseDefaultCodePage property if I could, but since the package is never created because of the messages received I am not able to do so.

Importing Data from Oracle 8i/9i to SQL Server 2005 using SQL Server Import and Export Wizar

Hi All,

I have become frustrated and I am not finding the answers I expect.

Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.

So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.

I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good

When I get to the end it progresses to about 20% and then throws this error about 300 or so times:

This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!

Help!!!

Hi Irish

I Don't have an answer for you, but since we are in the same situation as you I thought that I would share my thoughts on this.

I'm sorry that I wasn't for much help, but hopefully we can find a solution to our mutal problem together.

Best regards
Claus Pedersen

|||

Claus,

Thanks for the reference. I will try that block of code as well, but I'm not optimitic.

What ever happened to "DTS will be easier in 2005"?

|||

Generally I like the new features of Sql 2005 SSIS, but I agree that for tasks like this they still have a lot to do. I don't think that Sql 2000 would be easier.

Did you try to create a SSIS programmatically?

Best regards
Claus Pedersen

|||

Hi Irish

Have you tried to use the ForEach control?

I have experimented a little with it, and it seems like it could work. There are some problems with update of column names though.

Claus

|||

Hey Claus,

I have not had a single oppourtunity to work on this since making the last post.

I will try the ForEach and see what I get. Just seem like a major pain to have to go through all of the objects piecemeal.

|||

I agree. I can imagine a developer on Microsoft Sql server team telling his boss that "it could be worse" which converted to marketing language is "extreemly simple".

Wait a moment with the foreach solution. It might not work anyway because of the different metadata from table to table. I'll keep you updated.

/Claus

|||

Run through all of the steps, but uncheck Execute Immediately, and check Save SSIS package. Save it to the file system, and then edit the AlwaysUseDefaultCodePage property.

Or are you saying it won't even create the package for you?

|||

Sean,

So, I would change the AlwaysUseDefaultCodePage property if I could, but since the package is never created because of the messages received I am not able to do so.

Importing data from Ms Access to SQL server 2000

Hi,
I want to import a database in Access 2003 to SQL server. I used import and
export wizard to do that, but in one step it wants me a username and password
to continue. It doesn't accept any users of windows active directory that has
been defined. Is there any other way to deliver my database to sql? if not
what should I do exactly to complete the import wizard?
Thank you very muchGuess you are prompted for SQL credentials, try this frist. If you don´t
want to use the Access Upsizer use the DTS from SQL Server to import the
data from Access.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"nkh" <nkh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:212B585A-4892-4CFA-842B-A0F2446AC9A2@.microsoft.com...
> Hi,
> I want to import a database in Access 2003 to SQL server. I used import
> and
> export wizard to do that, but in one step it wants me a username and
> password
> to continue. It doesn't accept any users of windows active directory that
> has
> been defined. Is there any other way to deliver my database to sql? if not
> what should I do exactly to complete the import wizard?
> Thank you very much|||just as an aside, after you use the upsizing wizard, ensure that the fields
are the correct size... I had to reduce the length of many, many fields
afterwards, which gave me a big boost in performance...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"nkh" <nkh@.discussions.microsoft.com> wrote in message
news:212B585A-4892-4CFA-842B-A0F2446AC9A2@.microsoft.com...
> Hi,
> I want to import a database in Access 2003 to SQL server. I used import
and
> export wizard to do that, but in one step it wants me a username and
password
> to continue. It doesn't accept any users of windows active directory that
has
> been defined. Is there any other way to deliver my database to sql? if not
> what should I do exactly to complete the import wizard?
> Thank you very much

Wednesday, March 7, 2012

Importing Data from Excel 2003 to sql server 2005

Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:

EXECsp_configure'show advanced options', 1;

RECONFIGURE;

EXECsp_configure'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

Insertinto Pamphlet

Select*FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Pamphlet.xls;HDR=YES',

'SELECT * FROM [Sheet3$]')

But it is giving the following error:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.

Please help me.

Please make sure the file path is correct and MS Excel 2003 is closed.

Checkout these links as well:

http://sqljunkies.com/WebLog/madhivanan/archive/2005/07/27/16233.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=4

Good luck.

Friday, March 30, 2012

imports table structures in SQL 2000 into Excel

Wednesday, March 28, 2012

Importing text file problem using Import and Export Wizard

Monday, March 26, 2012

importing store procedures, How-to

importing store procedures, How-to

importing store procedures, How-to

Friday, March 23, 2012

Importing only data from Access to SQL Tables

Importing only data from Access to SQL Tables

Importing only data from Access to SQL Tables

Importing NULL into table SQL Server 2005

Wednesday, March 21, 2012

Importing into Test Database

Importing from PL/SQL to MSSQL

importing from access error

Monday, March 19, 2012

Importing Excel data into SQL Server 2005 with a twist!

Importing dBase files with the SSIS Import/Export Wizard

Friday, March 9, 2012

Importing data into SQL Server 2005 Eval version

Importing data into SQL

Importing Data from Oracle 8i/9i to SQL Server 2005 using SQL Server Import and Export Wizard (A

Importing Data from Oracle 8i/9i to SQL Server 2005 using SQL Server Import and Export Wizar

Importing data from Ms Access to SQL server 2000

Wednesday, March 7, 2012

Importing Data from Excel 2003 to sql server 2005

Importing Access DB

Blog Archive

About Me