Wednesday, March 7, 2012

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--