Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim BuntonJim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move
data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in
the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton

DataConversion Problem

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5F419.25A61BD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) = into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to = be VarChar
These transformations were carried out using SQL EnterpriseManager > = DesignTable
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column = CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager = > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update = a text field BUT the server is still telling the access Front end that = the datatype is nVarChar [NOT VarChar - as currently reported by SQL = Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then = recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they = were before.
Jim Bunton
--=_NextPart_000_0008_01C5F419.25A61BD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
SQL Server 2000, Win 2000 Access = 97

I have an Access FrontEnd and = BackEnd
The backend tables have been imported = (Data Transformation Services) into ab SQL Server Database.

Much of the data was not of the type = required - e.g. nVarChar needed to be VarChar

These transformations were carried out = using SQL EnterpriseManager > DesignTable

The Access Front End worked = Fine.

PROBLEM
--
For security reasons one tables, = Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > = ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]This reports success.

The datatypes are now reported to be as = required (SQL EnterpriseManager > Design Table)

Inspecting the data in the ACCESS = 97 front end is fine

BUT - attempts to update = the data give an ERROR
The import of the error is that the = ACCESS front end is trying to update a text field BUT the server is still telling the = access Front end that the datatype is nVarChar [NOT VarChar - = as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first = deletes ALL the tableDefs then recreates them with an ODBC connection to the = Server
All the other tables (apart from = Companies) are still updateable as they were before.
Jim = Bunton
--=_NextPart_000_0008_01C5F419.25A61BD0--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C5F42C.B3E69A60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message =news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) =into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed =to be VarChar
These transformations were carried out using SQL EnterpriseManager > =DesignTable
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column =CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL =EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to =update a text field BUT the server is still telling the access Front end =that the datatype is nVarChar [NOT VarChar - as currently reported by =SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then =recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as =they were before.
Jim Bunton
--=_NextPart_000_002A_01C5F42C.B3E69A60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Jim
Do you have a last Service Pack =installed on the SQL Server?
Looks strange. I just did some testing =and it works just fine
Try to create a table first and =then run DTS to update the table.
"Jim Bunton" = wrote in message news:KWCif.=17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access =97

I have an Access FrontEnd and BackEnd
The backend tables have been imported =(Data Transformation Services) into ab SQL Server Database.

Much of the data was not of the type =required - e.g. nVarChar needed to be VarChar

These transformations were carried =out using SQL EnterpriseManager > DesignTable

The Access Front End worked =Fine.

PROBLEM
--
For security reasons one tables, =Companies, was dropped.
Now after re-importing the table =(Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > = ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]This reports success.

The datatypes are now reported to be =as required (SQL EnterpriseManager > Design Table)

Inspecting the data in the =ACCESS 97 front end is fine

BUT - attempts to =update the data give an ERROR
The import of the error is that the =ACCESS front end is trying to update a text field BUT the server is still telling =the access Front end that the datatype is nVarChar [NOT =VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first =deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from =Companies) are still updateable as they were before.
Jim Bunton

---

--=_NextPart_000_002A_01C5F42C.B3E69A60--|||This is a multi-part message in MIME format.
--=_NextPart_000_0009_01C5F42C.4FDF9BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in =the move data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message =news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) =into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed =to be VarChar
These transformations were carried out using SQL EnterpriseManager > =DesignTable
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column =CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL =EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to =update a text field BUT the server is still telling the access Front end =that the datatype is nVarChar [NOT VarChar - as currently reported by =SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then =recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as =they were before.
Jim Bunton
--=_NextPart_000_0009_01C5F42C.4FDF9BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > =begins to run - stops on ERROR 145 an error occurred in the move data =process
? Help ? !! Have done a reboot. =still no joy
"Uri Dimant" wrote in =message news:eeBjvvB9FHA.1032=@.TK2MSFTNGP11.phx.gbl...
Jim

Do you have a last Service Pack =installed on the SQL Server?

Looks strange. I just did some =testing and it works just fine

Try to create a table first and =then run DTS to update the table.


"Jim Bunton" = wrote in message news:KWCif.=17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97

I have an Access FrontEnd and BackEnd
The backend tables have been =imported (Data Transformation Services) into ab SQL Server Database.

Much of the data was not of the =type required - e.g. nVarChar needed to be VarChar

These transformations were carried =out using SQL EnterpriseManager > DesignTable

The Access Front End worked =Fine.

PROBLEM
--
For security reasons one tables, =Companies, was dropped.
Now after re-importing the table =(Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > = ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]This reports = success.

The datatypes are now reported to =be as required (SQL EnterpriseManager > Design Table)

Inspecting the data in the =ACCESS 97 front end is fine

BUT - attempts to =update the data give an ERROR
The import of the error is that the =ACCESS front end is trying to update a text field BUT the server is still =telling the access Front end that the datatype is nVarChar =[NOT VarChar - as currently reported by SQL Enterprise =Manager]
NOTE - the Access Front End first =deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from =Companies) are still updateable as they were before.
Jim Bunton

---

--=_NextPart_000_0009_01C5F42C.4FDF9BC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_001B_01C5F447.76A4E2E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred =in the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message =news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) =into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed =to be VarChar
These transformations were carried out using SQL EnterpriseManager > =DesignTable
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column =CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL =EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to =update a text field BUT the server is still telling the access Front end =that the datatype is nVarChar [NOT VarChar - as currently reported by =SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then =recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as =they were before.
Jim Bunton
--=_NextPart_000_001B_01C5F447.76A4E2E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Creating table first then loading data =works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but =SP 3a setup.exe > will not instal - =stops wth err 145 'an error occurred in the move data process']
"Uri Dimant" wrote in =message news:eeBjvvB9FHA.1032=@.TK2MSFTNGP11.phx.gbl...
Jim

Do you have a last Service Pack =installed on the SQL Server?

Looks strange. I just did some =testing and it works just fine

Try to create a table first and =then run DTS to update the table.


"Jim Bunton" = wrote in message news:KWCif.=17904$8G6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97

I have an Access FrontEnd and BackEnd
The backend tables have been =imported (Data Transformation Services) into ab SQL Server Database.

Much of the data was not of the =type required - e.g. nVarChar needed to be VarChar

These transformations were carried =out using SQL EnterpriseManager > DesignTable

The Access Front End worked =Fine.

PROBLEM
--
For security reasons one tables, =Companies, was dropped.
Now after re-importing the table =(Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > = ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]This reports = success.

The datatypes are now reported to =be as required (SQL EnterpriseManager > Design Table)

Inspecting the data in the =ACCESS 97 front end is fine

BUT - attempts to =update the data give an ERROR
The import of the error is that the =ACCESS front end is trying to update a text field BUT the server is still =telling the access Front end that the datatype is nVarChar =[NOT VarChar - as currently reported by SQL Enterprise =Manager]
NOTE - the Access Front End first =deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from =Companies) are still updateable as they were before.
Jim Bunton

---

--=_NextPart_000_001B_01C5F447.76A4E2E0--