Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

Import export failed : Data conversion failed

[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.

I am using flat file import method.

Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.

I am using SQL server 2005.

Please advise what need to be done to avoid this error ?

Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.

Please help.

regards,

kong

Check the Quoted_Identifier and text_qualifier values in SSIS, if you are running from a workstation then make sure SSIS server component is installed

Import data truncate my data

I am using SQL 2005. I have a table defined as decimal(12,4).
I import data like 1.99305 to it, and the value in the database becomes
1.9930 instead of 1.9931.
But, when I edit the the database like
update myTable set price = 1.99305 where id = '12345'
the value of the Price becomes 1.9931.
How can I make the import program import the data to be 1.9931 instead of
1.9930 ?
Thank you.
CREATE TABLE myTable (
id int
Price decimal(12,4) NULL
)
1.99305
It will be kind of hard to aanwer without knowing the importing tool.
Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am using SQL 2005. I have a table defined as decimal(12,4).
>I import data like 1.99305 to it, and the value in the database becomes
>1.9930 instead of 1.9931.
>But, when I edit the the database like
>update myTable set price = 1.99305 where id = '12345'
>the value of the Price becomes 1.9931.
>How can I make the import program import the data to be 1.9931 instead of
>1.9930 ?
>Thank you.
>CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
>)
>1.99305
>
|||I was using the Copy wizard in the Microsoft SQL Server Management
Studio (right click on the database - Tasks - Copy Database)
On Jun 22, 4:24 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> It will be kind of hard to aanwer without knowing the importing tool.
> Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fni...@.pfmail.com>
> wrote:
>
>
>
> - Show quoted text -
|||(fiefie.niles@.gmail.com) writes:
> I was using the Copy wizard in the Microsoft SQL Server Management
> Studio (right click on the database - Tasks - Copy Database)
This I don't get:
[vbcol=seagreen]
When you use the Copy Database Wizard, you are copying the entire database
definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
and this you cannot get this truncation issue. Or am I missing something?
Another story is that CDW offers to method of copying: Attach/Detach
and the SMO method, and the latter method is very unreliable, and nothing
you can use seriously. I think that by SP2, it has reached the state
where it can be called a beta, but it is far from being trustworthy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
Sorry, I was not clear. I was copying from an Access database to the SQL
Server database, and the datatype in the Access is Text(50).
The data in the Access database is 1.99305, and when it copied to SQL Server
it became 1.9930 instead of 1.9931.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99589C25392E0Yazorman@.127.0.0.1...
> (fiefie.niles@.gmail.com) writes:
> This I don't get:
>
> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
> Another story is that CDW offers to method of copying: Attach/Detach
> and the SMO method, and the latter method is very unreliable, and nothing
> you can use seriously. I think that by SP2, it has reached the state
> where it can be called a beta, but it is far from being trustworthy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:
[vbcol=seagreen]

>Sorry, I was not clear. I was copying from an Access database to the SQL
>Server database, and the datatype in the Access is Text(50).
>The data in the Access database is 1.99305, and when it copied to SQL Server
>it became 1.9930 instead of 1.9931.
I am still not clear, since the tool you say you used does not work
with Access.
When I open Microsoft SQL Server Management Studio, right click on a
database, and choose the Tasks / Copy Database... option, the Copy
Database Wizard opens. The message in that window is "You can use
this wizard to move or copy databases from an instance of SQL Server
2000 or SQL Server 2005 to an instance of SQL Server 2005."
Roy Harvey
Beacon Falls, CT
|||I am very sorry, I meant to say I was using the Microsoft SQL Server
Management Studio Import wizard, right click on a
database, and choose the Tasks / Import Data... option to import from Access
database to SQL Server database (I chose Access as the source database and
SQL Server as the destination database)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com...
> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
> wrote:
>
> I am still not clear, since the tool you say you used does not work
> with Access.
> When I open Microsoft SQL Server Management Studio, right click on a
> database, and choose the Tasks / Copy Database... option, the Copy
> Database Wizard opens. The message in that window is "You can use
> this wizard to move or copy databases from an instance of SQL Server
> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
> Roy Harvey
> Beacon Falls, CT
|||I am not familiar with the import tool in 2005, but I just played with
it a bit. I assume that the wizard did not assign decimal(12,4) as the
data type when Access has Text(50), so I assume the table definition
was done before the import.
It would appear that you have found a shortcoming of the import
process when crossing data types. The only workaround I can see would
be to import to a table that matches the original datatypes, and then
move the data to the tables with the new types using SQL INSERERT
commands. That would allow you to use explicit CONVERT and ROUND
commands.
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 12:54:00 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am very sorry, I meant to say I was using the Microsoft SQL Server
>Management Studio Import wizard, right click on a
>database, and choose the Tasks / Import Data... option to import from Access
>database to SQL Server database (I chose Access as the source database and
>SQL Server as the destination database)
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com.. .
>

Import data truncate my data

I am using SQL 2005. I have a table defined as decimal(12,4).
I import data like 1.99305 to it, and the value in the database becomes
1.9930 instead of 1.9931.
But, when I edit the the database like
update myTable set price = 1.99305 where id = '12345'
the value of the Price becomes 1.9931.
How can I make the import program import the data to be 1.9931 instead of
1.9930 ?
Thank you.
CREATE TABLE myTable (
id int
Price decimal(12,4) NULL
)
1.99305It will be kind of hard to aanwer without knowing the importing tool.
Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>I am using SQL 2005. I have a table defined as decimal(12,4).
>I import data like 1.99305 to it, and the value in the database becomes
>1.9930 instead of 1.9931.
>But, when I edit the the database like
>update myTable set price = 1.99305 where id = '12345'
>the value of the Price becomes 1.9931.
>How can I make the import program import the data to be 1.9931 instead of
>1.9930 ?
>Thank you.
>CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
>)
>1.99305
>|||I was using the Copy wizard in the Microsoft SQL Server Management
Studio (right click on the database - Tasks - Copy Database)
On Jun 22, 4:24 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> It will be kind of hard to aanwer without knowing the importing tool.
> Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fni...@.pfmail.com>
> wrote:
>
> >I am using SQL 2005. I have a table defined as decimal(12,4).
> >I import data like 1.99305 to it, and the value in the database becomes
> >1.9930 instead of 1.9931.
> >But, when I edit the the database like
> >update myTable set price = 1.99305 where id = '12345'
> >the value of the Price becomes 1.9931.
> >How can I make the import program import the data to be 1.9931 instead of
> >1.9930 ?
> >Thank you.
> >CREATE TABLE myTable (
> > id int
> > Price decimal(12,4) NULL
> >)
> >1.99305- Hide quoted text -
> - Show quoted text -|||(fiefie.niles@.gmail.com) writes:
> I was using the Copy wizard in the Microsoft SQL Server Management
> Studio (right click on the database - Tasks - Copy Database)
This I don't get:
>> >I am using SQL 2005. I have a table defined as decimal(12,4).
>> >I import data like 1.99305 to it, and the value in the database becomes
>> >1.9930 instead of 1.9931.
When you use the Copy Database Wizard, you are copying the entire database
definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
and this you cannot get this truncation issue. Or am I missing something?
Another story is that CDW offers to method of copying: Attach/Detach
and the SMO method, and the latter method is very unreliable, and nothing
you can use seriously. I think that by SP2, it has reached the state
where it can be called a beta, but it is far from being trustworthy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
Sorry, I was not clear. I was copying from an Access database to the SQL
Server database, and the datatype in the Access is Text(50).
The data in the Access database is 1.99305, and when it copied to SQL Server
it became 1.9930 instead of 1.9931.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99589C25392E0Yazorman@.127.0.0.1...
> (fiefie.niles@.gmail.com) writes:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
> This I don't get:
>> >I am using SQL 2005. I have a table defined as decimal(12,4).
>> >I import data like 1.99305 to it, and the value in the database becomes
>> >1.9930 instead of 1.9931.
> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
> Another story is that CDW offers to method of copying: Attach/Detach
> and the SMO method, and the latter method is very unreliable, and nothing
> you can use seriously. I think that by SP2, it has reached the state
> where it can be called a beta, but it is far from being trustworthy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>Sorry, I was not clear. I was copying from an Access database to the SQL
>Server database, and the datatype in the Access is Text(50).
>The data in the Access database is 1.99305, and when it copied to SQL Server
>it became 1.9930 instead of 1.9931.
I am still not clear, since the tool you say you used does not work
with Access.
When I open Microsoft SQL Server Management Studio, right click on a
database, and choose the Tasks / Copy Database... option, the Copy
Database Wizard opens. The message in that window is "You can use
this wizard to move or copy databases from an instance of SQL Server
2000 or SQL Server 2005 to an instance of SQL Server 2005."
Roy Harvey
Beacon Falls, CT|||I am very sorry, I meant to say I was using the Microsoft SQL Server
Management Studio Import wizard, right click on a
database, and choose the Tasks / Import Data... option to import from Access
database to SQL Server database (I chose Access as the source database and
SQL Server as the destination database)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com...
> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
> wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>Sorry, I was not clear. I was copying from an Access database to the SQL
>>Server database, and the datatype in the Access is Text(50).
>>The data in the Access database is 1.99305, and when it copied to SQL
>>Server
>>it became 1.9930 instead of 1.9931.
> I am still not clear, since the tool you say you used does not work
> with Access.
> When I open Microsoft SQL Server Management Studio, right click on a
> database, and choose the Tasks / Copy Database... option, the Copy
> Database Wizard opens. The message in that window is "You can use
> this wizard to move or copy databases from an instance of SQL Server
> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
> Roy Harvey
> Beacon Falls, CT|||I am not familiar with the import tool in 2005, but I just played with
it a bit. I assume that the wizard did not assign decimal(12,4) as the
data type when Access has Text(50), so I assume the table definition
was done before the import.
It would appear that you have found a shortcoming of the import
process when crossing data types. The only workaround I can see would
be to import to a table that matches the original datatypes, and then
move the data to the tables with the new types using SQL INSERERT
commands. That would allow you to use explicit CONVERT and ROUND
commands.
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 12:54:00 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>I am very sorry, I meant to say I was using the Microsoft SQL Server
>Management Studio Import wizard, right click on a
>database, and choose the Tasks / Import Data... option to import from Access
>database to SQL Server database (I chose Access as the source database and
>SQL Server as the destination database)
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:31kv73dpnmkfh7srad18og9de1alac8c4t@.4ax.com...
>> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
>> wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>Sorry, I was not clear. I was copying from an Access database to the SQL
>>Server database, and the datatype in the Access is Text(50).
>>The data in the Access database is 1.99305, and when it copied to SQL
>>Server
>>it became 1.9930 instead of 1.9931.
>> I am still not clear, since the tool you say you used does not work
>> with Access.
>> When I open Microsoft SQL Server Management Studio, right click on a
>> database, and choose the Tasks / Copy Database... option, the Copy
>> Database Wizard opens. The message in that window is "You can use
>> this wizard to move or copy databases from an instance of SQL Server
>> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
>> Roy Harvey
>> Beacon Falls, CT
>|||On Jun 23, 2:36 am, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL 2005. I have a table defined as decimal(12,4).
> I import data like 1.99305 to it, and the value in the database becomes
> 1.9930 instead of 1.9931.
> But, when I edit the the database like
> update myTable set price = 1.99305 where id = '12345'
> the value of the Price becomes 1.9931.
> How can I make the import program import the data to be 1.9931 instead of
> 1.9930 ?
> Thank you.
> CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
> 1.99305
Hi, I was wondering, instead of using decimal(12,4) why not use
decimal(20,5) or decimal(20,10)? Then you can round your data once it
is imported in the server and alter the column to decimal(20,4) later?

Import data truncate my data

I am using SQL 2005. I have a table defined as decimal(12,4).
I import data like 1.99305 to it, and the value in the database becomes
1.9930 instead of 1.9931.
But, when I edit the the database like
update myTable set price = 1.99305 where id = '12345'
the value of the Price becomes 1.9931.
How can I make the import program import the data to be 1.9931 instead of
1.9930 ?
Thank you.
CREATE TABLE myTable (
id int
Price decimal(12,4) NULL
)
1.99305It will be kind of hard to aanwer without knowing the importing tool.
Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am using SQL 2005. I have a table defined as decimal(12,4).
>I import data like 1.99305 to it, and the value in the database becomes
>1.9930 instead of 1.9931.
>But, when I edit the the database like
>update myTable set price = 1.99305 where id = '12345'
>the value of the Price becomes 1.9931.
>How can I make the import program import the data to be 1.9931 instead of
>1.9930 ?
>Thank you.
>CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
>1.99305
>|||I was using the Copy wizard in the Microsoft SQL Server Management
Studio (right click on the database - Tasks - Copy Database)
On Jun 22, 4:24 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> It will be kind of hard to aanwer without knowing the importing tool.
> Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fni...@.pfmail.com>
> wrote:
>
>
>
>
>
> - Show quoted text -|||(fiefie.niles@.gmail.com) writes:
> I was using the Copy wizard in the Microsoft SQL Server Management
> Studio (right click on the database - Tasks - Copy Database)
This I don't get:
[vbcol=seagreen]
When you use the Copy Database Wizard, you are copying the entire database
definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
and this you cannot get this truncation issue. Or am I missing something?
Another story is that CDW offers to method of copying: Attach/Detach
and the SMO method, and the latter method is very unreliable, and nothing
you can use seriously. I think that by SP2, it has reached the state
where it can be called a beta, but it is far from being trustworthy.
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|||> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
Sorry, I was not clear. I was copying from an Access database to the SQL
Server database, and the datatype in the Access is Text(50).
The data in the Access database is 1.99305, and when it copied to SQL Server
it became 1.9930 instead of 1.9931.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99589C25392E0Yazorman@.127.0.0.1...
> (fiefie.niles@.gmail.com) writes:
> This I don't get:
>
> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
> Another story is that CDW offers to method of copying: Attach/Detach
> and the SMO method, and the latter method is very unreliable, and nothing
> you can use seriously. I think that by SP2, it has reached the state
> where it can be called a beta, but it is far from being trustworthy.
>
> --
> 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|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:

[vbcol=seagreen]
>Sorry, I was not clear. I was copying from an Access database to the SQL
>Server database, and the datatype in the Access is Text(50).
>The data in the Access database is 1.99305, and when it copied to SQL Serve
r
>it became 1.9930 instead of 1.9931.
I am still not clear, since the tool you say you used does not work
with Access.
When I open Microsoft SQL Server Management Studio, right click on a
database, and choose the Tasks / Copy Database... option, the Copy
Database Wizard opens. The message in that window is "You can use
this wizard to move or copy databases from an instance of SQL Server
2000 or SQL Server 2005 to an instance of SQL Server 2005."
Roy Harvey
Beacon Falls, CT|||I am very sorry, I meant to say I was using the Microsoft SQL Server
Management Studio Import wizard, right click on a
database, and choose the Tasks / Import Data... option to import from Access
database to SQL Server database (I chose Access as the source database and
SQL Server as the destination database)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31kv73dpnmkfh7srad18og9de1alac8c4t@.
4ax.com...
> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
> wrote:
>
>
> I am still not clear, since the tool you say you used does not work
> with Access.
> When I open Microsoft SQL Server Management Studio, right click on a
> database, and choose the Tasks / Copy Database... option, the Copy
> Database Wizard opens. The message in that window is "You can use
> this wizard to move or copy databases from an instance of SQL Server
> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
> Roy Harvey
> Beacon Falls, CT|||I am not familiar with the import tool in 2005, but I just played with
it a bit. I assume that the wizard did not assign decimal(12,4) as the
data type when Access has Text(50), so I assume the table definition
was done before the import.
It would appear that you have found a shortcoming of the import
process when crossing data types. The only workaround I can see would
be to import to a table that matches the original datatypes, and then
move the data to the tables with the new types using SQL INSERERT
commands. That would allow you to use explicit CONVERT and ROUND
commands.
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 12:54:00 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am very sorry, I meant to say I was using the Microsoft SQL Server
>Management Studio Import wizard, right click on a
>database, and choose the Tasks / Import Data... option to import from Acces
s
>database to SQL Server database (I chose Access as the source database and
>SQL Server as the destination database)
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31kv73dpnmkfh7srad18og9de1alac8c4t@.
4ax.com...
>|||On Jun 23, 2:36 am, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL 2005. I have a table defined as decimal(12,4).
> I import data like 1.99305 to it, and the value in the database becomes
> 1.9930 instead of 1.9931.
> But, when I edit the the database like
> update myTable set price = 1.99305 where id = '12345'
> the value of the Price becomes 1.9931.
> How can I make the import program import the data to be 1.9931 instead of
> 1.9930 ?
> Thank you.
> CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
> 1.99305
Hi, I was wondering, instead of using decimal(12,4) why not use
decimal(20,5) or decimal(20,10)? Then you can round your data once it
is imported in the server and alter the column to decimal(20,4) later?

Import Data from excel file into Sql server 2005?

Hi all,

I try to import data from excel file, my excel file have column called Name, the value of this column is text: ex: Binh Chanh, 1,2,3,4,5.....When i import into sqlserver 2005, these rows which have value 1,2,3,4,5 (number) , now have Null value in SQL server 2005.

How can i fix this error?

Thanks,

Do you use the IMEX=1 switch in the Excel connection string?

(like:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source= <your_file_name> ;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

)

It did the trick for me, although my problem was the other way around: numbers were correct, text was NULL values.

So I'm not sure whether it will work for you.

Pipo