UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Yes/no Conversion, Any Versions    
 
   
FJM
post Mar 27 2020, 07:39 AM
Post#1



Posts: 15
Joined: 4-August 11



I have a problem with my access 2016 database continually crashing. It has a split back and front end. I have been advised to move the back end tables onto a SQL server and this will sort out the problem. I have imported some tables from an access 2016 database into a SQL SERVER MANAGEMENT STUDIO 17 database so I can now use the SQL tables as the back.
When I try to enter data into a front end form which is linked to the tables, I have started to instantly get a warning message about the fields which contain yes/no in the original access tables.
How do I deal with this type of conversion
Go to the top of the page
 
GroverParkGeorge
post Mar 27 2020, 08:06 AM
Post#2


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


Please be more specific. What is that warning message? What does it say?


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Mar 27 2020, 08:12 AM
Post#3


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


"SQL SERVER MANAGEMENT STUDIO 17"

SQL Server is a relational database. SSMS 17 is a tool for working with SQL Server databases. The actual version of the SQL Server to which you are connected could be any one of a number of versions. Anything more specific that you can tell us by looking here?

Attached File  SQLServerinSSMS.jpg ( 177.07K )Number of downloads: 6



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Mar 27 2020, 09:56 AM
Post#4



Posts: 967
Joined: 21-September 14
From: Tampa, Florida USA


SQL-Server doesn't know what an Access Yes/No fields is, so you will have to convert those fields to a SQL-Server Bit(1) field that can store a 1 for TRUE and a 0 for FALSE.
This post has been edited by FrankRuperto: Mar 27 2020, 10:55 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post Mar 27 2020, 10:53 AM
Post#5


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


SSMA should handle that conversion properly.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Mar 27 2020, 12:37 PM
Post#6


UtterAccess VIP
Posts: 2,308
Joined: 4-June 18
From: Somerset, UK


If I understand you correctly, you've already exported your tables with yes/no fields to bit fields in SQL Server.
Make sure that you set a default value - either 0 or -1 as preferred

This is essential as bit fields in SS can have null values (unlike Access).
Leaving any bit fields null will result in a Write Conflict error if you try to run an update query in Access on those records


--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FJM
post Mar 30 2020, 06:37 AM
Post#7



Posts: 15
Joined: 4-August 11



I have tried to import 19 tables from an existing access database into a SQL server management database. The version is V17.8.1
Once I run through all of the wizard instructions and execute, it goes so far, then a number of red circles start appearing and the following message is displayed

- Copying to [dbo].[ANALYSIS] (Error)
Messages
Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 4 - CHANGEOVER" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 3 - ANALYSISCODE" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 2 - Analysis Code Lookup" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination - ACTIONS" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination - ACTIONS" has ended.
(SQL Server Import and Export Wizard)

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with Destination 1 - ANALYSIS.Inputs[Destination Input].Columns[ORDERDESPATCHDATE] on Destination 1 - ANALYSIS.Inputs[Destination Input]. The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination 1 - ANALYSIS.Inputs[Destination Input]" failed because error code 0xC020907A occurred, and the error row disposition on "Destination 1 - ANALYSIS.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 1 - ANALYSIS" (210) failed with error code 0xC0209029 while processing input "Destination Input" (223). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source 1 - ANALYSIS returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)


I thought that the wizard would automatically adjust between the 2 programs.
Have I done something basically wrong ?

Go to the top of the page
 
FJM
post Mar 30 2020, 06:52 AM
Post#8



Posts: 15
Joined: 4-August 11



In case it helps, I have attached (I hope) a screenshot of what I see....

Attached File  Doc18.zip ( 74.45K )Number of downloads: 3


Go to the top of the page
 
GroverParkGeorge
post Mar 30 2020, 06:52 AM
Post#9


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


There are a few different problems there, but without seeing the actual source tables, and even some sample data in the case of the date field error, it's hard to know what might be wrong with any one of them.

With regard to the date errors, such as this:

"Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format".
(SQL Server Import and Export Wizard)"

one possibility is that the date field in Access contains "dates" that Access tolerates but which SQL Server won't. I have seen that a couple of times. The source table had some "dates" that were like #1/1/200# or something like that, where one of the date digits was dropped. Clearly invalid logically, but acceptable in the Access field because there IS such a date.

We'd have to see each of the fields in question, as noted, to be able to help you figure out why they don't migrate. My guess is that there is data in most of those fields that Access tolerates which won't migrate properly.




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FJM
post Mar 30 2020, 08:15 AM
Post#10



Posts: 15
Joined: 4-August 11



Although I have absolutely no experience of working with SQL, I thought exactly the same as you that perhaps the data in the access table was causing the problem.
Unfortunately when I looked at the data in the access table, although there is a column available to enter the date, none of the records have actually had a date entered into them.
How does this fit with the error message ?
Go to the top of the page
 
GroverParkGeorge
post Mar 30 2020, 08:17 AM
Post#11


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


No values.

Did you check the destination table to see if it accepts Null in that Date field?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Mar 30 2020, 09:25 AM
Post#12



Posts: 967
Joined: 21-September 14
From: Tampa, Florida USA


So what happened with the Access Yes/No fields?.. They should have converted with no problems to SS Bit(1) fields since Access doesn't allow null values in them. And now you're having issues with dates. By default, when you import Access datetime fields into SS, it allows null values, so it looks like you will have to do data cleansing on the Access side, or use SSIS to replace bad datetime values with null or a placeholder values.

I am amazed that SQL-Server import wizard doesn't put problem records in a "rejects" file and continues importing the rest of the good records.
This post has been edited by FrankRuperto: Mar 30 2020, 09:58 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
FJM
post Mar 30 2020, 10:50 AM
Post#13



Posts: 15
Joined: 4-August 11



Here is another example of when table detail will not be imported into the sequel database.
I have attached details of the access table, the table in SQL which compares the detail and the series of error messages I get once I have executed the transfer
Attached File  FIREDRILLTABLER.zip ( 52.13K )Number of downloads: 3
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 12:02 AM