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.. .
>
Showing posts with label defined. Show all posts
Showing posts with label defined. Show all posts
Monday, March 19, 2012
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?
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?
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?
Wednesday, March 7, 2012
Import conversion error caused by spaces?
I am trying to import a fixed width file where some of the numeric columns are empty. The columns in question are defined as integer columns (of varying sizes) and I am guessing that "empty" columns come across as multiple spaces on the import.
Even though I have "Retain null values from source" checked off, I am still receiving the following error on these empty columns:
Error: 0xC02020A1 at Input Data, Flat File Source [1]: Data conversion failed. The data conversion for column "ToContractExpiryYear" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Input Data, Flat File Source [1]: The "output column "ToContractExpiryYear" (51)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ToContractExpiryYear" (51)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Input Data, Flat File Source [1]: An error occurred while processing file "\\Nastinus-01\ClearingData\OCC\20060320\ser2mst.20060317" on data row 1.
If it is truly the system treating the column as spaces (and not trimming the value), then the only solution I can think off is to source everything as strings, perform a transform that executes a Trim() (Derived Column or Script ?), THEN perform a transform that converts data types, then do whatever else I need...
Am I missing something? Is this the correct solution?
Hi,
On your Flat File Source, try to set the error Output, error column to "Ignore failure".
Then carefully review the result with a data viewer. It appears that you get NULL when there is no value.
Philippe
|||This solution appears to work well, but is not a realistic solution where you wish to catch true errors (such as alpha data in numeric fields).
One would assume there is an option for fixed width input, to treat blank numeric fields as null. But there are none I can find. Is there any additional information on this subject?
Chris
Import conversion error caused by spaces?
I am trying to import a fixed width file where some of the numeric columns are empty. The columns in question are defined as integer columns (of varying sizes) and I am guessing that "empty" columns come across as multiple spaces on the import.
Even though I have "Retain null values from source" checked off, I am still receiving the following error on these empty columns:
Error: 0xC02020A1 at Input Data, Flat File Source [1]: Data conversion failed. The data conversion for column "ToContractExpiryYear" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Input Data, Flat File Source [1]: The "output column "ToContractExpiryYear" (51)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ToContractExpiryYear" (51)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Input Data, Flat File Source [1]: An error occurred while processing file "\\Nastinus-01\ClearingData\OCC\20060320\ser2mst.20060317" on data row 1.
If it is truly the system treating the column as spaces (and not trimming the value), then the only solution I can think off is to source everything as strings, perform a transform that executes a Trim() (Derived Column or Script ?), THEN perform a transform that converts data types, then do whatever else I need...
Am I missing something? Is this the correct solution?
Hi,
On your Flat File Source, try to set the error Output, error column to "Ignore failure".
Then carefully review the result with a data viewer. It appears that you get NULL when there is no value.
Philippe
|||This solution appears to work well, but is not a realistic solution where you wish to catch true errors (such as alpha data in numeric fields).
One would assume there is an option for fixed width input, to treat blank numeric fields as null. But there are none I can find. Is there any additional information on this subject?
Chris
Import and Export foreign characters (non-DBCS only)
Hi
I have a table which stores the customer information...There is a Customer
Address column defined as nvarchar(50)...Data for the table is imported
using DTS Bulk Insert from a Customer.dat file...
My question: -
1. What are the requirements for me to view the foreign characters (Arabic,
Turkish, German, Italian etc...) using an editor. What setup needs to be
done on the server where the files reside.
2. How do I make the .dat file as a unicode file ?
3. Can I see the foreign characters by a simple select in query analyzer?
4. What are the requirements to export the file to contain all those foreign
characters?
Ps: - Focus is not on DBCS characters (Kanji characters etc...)
Any help would be much appreciated....
Thanks in advance...
ImtiazSee below.
Steve Kass
Drew University
Imtiaz wrote:
>Hi
>I have a table which stores the customer information...There is a Customer
>Address column defined as nvarchar(50)...Data for the table is imported
>using DTS Bulk Insert from a Customer.dat file...
>My question: -
>1. What are the requirements for me to view the foreign characters (Arabic,
>Turkish, German, Italian etc...) using an editor. What setup needs to be
>done on the server where the files reside.
>
As long as the data is stored as Unicode, and the client is Unicode
compatible, all you need is Unicode fonts in your editor.
>2. How do I make the .dat file as a unicode file ?
>
I don't know about DTS, but I think that bcp will leave out the
two-byte Unicode byte-order marker at the beginning of the file.
You may need to insert the two-byte sequence FFFE yourself
so that other applications will handle it correctly. For importing
back to SQL Server, this might be the wrong thing to do, though.
>3. Can I see the foreign characters by a simple select in query analyzer?
>
Yes. Just make sure the font in your results window is a Unicode font
(Arial MS Unicode, for example).
>4. What are the requirements to export the file to contain all those foreig
n
>characters?
>
Just export as Unicode and it should be fine.
>Ps: - Focus is not on DBCS characters (Kanji characters etc...)
>Any help would be much appreciated....
>Thanks in advance...
>Imtiaz
>
I have a table which stores the customer information...There is a Customer
Address column defined as nvarchar(50)...Data for the table is imported
using DTS Bulk Insert from a Customer.dat file...
My question: -
1. What are the requirements for me to view the foreign characters (Arabic,
Turkish, German, Italian etc...) using an editor. What setup needs to be
done on the server where the files reside.
2. How do I make the .dat file as a unicode file ?
3. Can I see the foreign characters by a simple select in query analyzer?
4. What are the requirements to export the file to contain all those foreign
characters?
Ps: - Focus is not on DBCS characters (Kanji characters etc...)
Any help would be much appreciated....
Thanks in advance...
ImtiazSee below.
Steve Kass
Drew University
Imtiaz wrote:
>Hi
>I have a table which stores the customer information...There is a Customer
>Address column defined as nvarchar(50)...Data for the table is imported
>using DTS Bulk Insert from a Customer.dat file...
>My question: -
>1. What are the requirements for me to view the foreign characters (Arabic,
>Turkish, German, Italian etc...) using an editor. What setup needs to be
>done on the server where the files reside.
>
As long as the data is stored as Unicode, and the client is Unicode
compatible, all you need is Unicode fonts in your editor.
>2. How do I make the .dat file as a unicode file ?
>
I don't know about DTS, but I think that bcp will leave out the
two-byte Unicode byte-order marker at the beginning of the file.
You may need to insert the two-byte sequence FFFE yourself
so that other applications will handle it correctly. For importing
back to SQL Server, this might be the wrong thing to do, though.
>3. Can I see the foreign characters by a simple select in query analyzer?
>
Yes. Just make sure the font in your results window is a Unicode font
(Arial MS Unicode, for example).
>4. What are the requirements to export the file to contain all those foreig
n
>characters?
>
Just export as Unicode and it should be fine.
>Ps: - Focus is not on DBCS characters (Kanji characters etc...)
>Any help would be much appreciated....
>Thanks in advance...
>Imtiaz
>
Friday, February 24, 2012
Import Access table to SQL Server
I have an Access 2002 database that I am importing into SQL Server. I have t
rouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message)
. Any ideas.
thans,
StanYou can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:
>I have an Access 2002 database that I am importing into SQL Server. I have
trouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message
). Any ideas.
>thans,
>Stan
rouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message)
. Any ideas.
thans,
StanYou can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:
>I have an Access 2002 database that I am importing into SQL Server. I have
trouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message
). Any ideas.
>thans,
>Stan
Import Access table to SQL Server
I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
thans,
Stan
You can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:
>I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
>thans,
>Stan
thans,
Stan
You can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:
>I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
>thans,
>Stan
Subscribe to:
Posts (Atom)