We are experiencing issues when exporting data from SPSS datasets to our database. The issue began when we updated to SPSS 27 (no issues in prior versions). We've tried executing old syntax files that worked fine in prior versions of SPSS, exporting via the drop-down menus, etc. We can upload the data via other software applications without issues.

As soon as the coding to export the data is executed, the Case Counter begins at 2,000,000,000,000+. It then ticks up very slowly from there. If we're updating a small dataset (ex. few hundred cases), it is eventually successful but is taking way too long to get there. When dealing with something larger (ex. 90,000 records), it can run for hours and still not complete.

Below is an example of old coding that ran fine until SPSS 27 (removed info from CONNECT string, due to confidentiality). Any ideas?


SAVE TRANSLATE /TYPE=ODBC
/BULKLOADING BATCHSIZE=10000 METHOD=BATCH
/CONNECT="DSN=;UID=;PWD=;NA=;DB="
/UNENCRYPTED
/MISSING=IGNORE
/SQL='CREATE TABLE [scp] ([DOC_NUMBER] int , [CITIZENSHIP] varchar (50), '+
'[LAST_NAME] varchar (40), [FIRST_NAME] varchar (25), [MIDDLE_NAME] '+
'varchar (25), [DOB] datetime )'
/REPLACE
/TABLE='SPSS_TEMP'
/KEEP=DOC_NUMBER, CITIZENSHIP, LAST_NAME, FIRST_NAME, MIDDLE_NAME, DOB
/SQL='INSERT INTO [scp] ([DOC_NUMBER], [CITIZENSHIP], [LAST_NAME], '+
'[FIRST_NAME], [MIDDLE_NAME], [DOB]) SELECT [DOC_NUMBER], [CITIZENSHIP], '+
'[LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [DOB] FROM SPSS_TEMP'
/SQL='DROP TABLE [SPSS_TEMP]'.

------------------------------
Brenda Hurless
------------------------------
#SPSSStatistics

Hi, Brenda. Just so I'm clear, the same syntax and data ran without problem in versions < 27. You are seeing it in v27 (and perhaps 28 although we don't know that yet).

Have you by any chance upgraded your SPSS Data Access Pack recently?

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Original Message:
Sent: Wed March 23, 2022 08:03 AM
From: Brenda Hurless
Subject: SPSS 27 - Export to Database Issue

We are experiencing issues when exporting data from SPSS datasets to our database. The issue began when we updated to SPSS 27 (no issues in prior versions). We've tried executing old syntax files that worked fine in prior versions of SPSS, exporting via the drop-down menus, etc. We can upload the data via other software applications without issues.

As soon as the coding to export the data is executed, the Case Counter begins at 2,000,000,000,000+. It then ticks up very slowly from there. If we're updating a small dataset (ex. few hundred cases), it is eventually successful but is taking way too long to get there. When dealing with something larger (ex. 90,000 records), it can run for hours and still not complete.

Below is an example of old coding that ran fine until SPSS 27 (removed info from CONNECT string, due to confidentiality). Any ideas?


SAVE TRANSLATE /TYPE=ODBC
/BULKLOADING BATCHSIZE=10000 METHOD=BATCH
/CONNECT="DSN=;UID=;PWD=;NA=;DB="
/UNENCRYPTED
/MISSING=IGNORE
/SQL='CREATE TABLE [scp] ([DOC_NUMBER] int , [CITIZENSHIP] varchar (50), '+
'[LAST_NAME] varchar (40), [FIRST_NAME] varchar (25), [MIDDLE_NAME] '+
'varchar (25), [DOB] datetime )'
/REPLACE
/TABLE='SPSS_TEMP'
/KEEP=DOC_NUMBER, CITIZENSHIP, LAST_NAME, FIRST_NAME, MIDDLE_NAME, DOB
/SQL='INSERT INTO [scp] ([DOC_NUMBER], [CITIZENSHIP], [LAST_NAME], '+
'[FIRST_NAME], [MIDDLE_NAME], [DOB]) SELECT [DOC_NUMBER], [CITIZENSHIP], '+
'[LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [DOB] FROM SPSS_TEMP'
/SQL='DROP TABLE [SPSS_TEMP]'.

------------------------------
Brenda Hurless
------------------------------
#SPSSStatistics

Thank you for your response, Rick! Correct; the same syntax and data ran without issue in version < 27.

We currently use SPSS Data Access Pack 8.0, which was installed at the same time as SPSS 27. I will speak with management about upgrading to version 8.1, and then test the steps you outlined. (Note this might take a bit of time, as we don't have admin rights to our computers  - i.e. I'll have to open a case within our department's tech area, wait for them to push the update to us, etc). I will post an update as soon as I have one.

Thank you again - greatly appreciate your assistance!

------------------------------
Brenda Hurless
------------------------------

Original Message:
Sent: Wed March 23, 2022 11:06 AM
From: Rick Marcantonio
Subject: SPSS 27 - Export to Database Issue

Hi, Brenda. Just so I'm clear, the same syntax and data ran without problem in versions < 27. You are seeing it in v27 (and perhaps 28 although we don't know that yet).

Have you by any chance upgraded your SPSS Data Access Pack recently?

------------------------------
Rick Marcantonio
Quality Assurance
IBM

Original Message:
Sent: Wed March 23, 2022 08:03 AM
From: Brenda Hurless
Subject: SPSS 27 - Export to Database Issue

We are experiencing issues when exporting data from SPSS datasets to our database. The issue began when we updated to SPSS 27 (no issues in prior versions). We've tried executing old syntax files that worked fine in prior versions of SPSS, exporting via the drop-down menus, etc. We can upload the data via other software applications without issues.

As soon as the coding to export the data is executed, the Case Counter begins at 2,000,000,000,000+. It then ticks up very slowly from there. If we're updating a small dataset (ex. few hundred cases), it is eventually successful but is taking way too long to get there. When dealing with something larger (ex. 90,000 records), it can run for hours and still not complete.

Below is an example of old coding that ran fine until SPSS 27 (removed info from CONNECT string, due to confidentiality). Any ideas?


SAVE TRANSLATE /TYPE=ODBC
/BULKLOADING BATCHSIZE=10000 METHOD=BATCH
/CONNECT="DSN=;UID=;PWD=;NA=;DB="
/UNENCRYPTED
/MISSING=IGNORE
/SQL='CREATE TABLE [scp] ([DOC_NUMBER] int , [CITIZENSHIP] varchar (50), '+
'[LAST_NAME] varchar (40), [FIRST_NAME] varchar (25), [MIDDLE_NAME] '+
'varchar (25), [DOB] datetime )'
/REPLACE
/TABLE='SPSS_TEMP'
/KEEP=DOC_NUMBER, CITIZENSHIP, LAST_NAME, FIRST_NAME, MIDDLE_NAME, DOB
/SQL='INSERT INTO [scp] ([DOC_NUMBER], [CITIZENSHIP], [LAST_NAME], '+
'[FIRST_NAME], [MIDDLE_NAME], [DOB]) SELECT [DOC_NUMBER], [CITIZENSHIP], '+
'[LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [DOB] FROM SPSS_TEMP'
/SQL='DROP TABLE [SPSS_TEMP]'.

------------------------------
Brenda Hurless
------------------------------
#SPSSStatistics

Me again. I talked with a colleague and he sent me this:
I'd think it is possible we have changed the way we structure SAVE TRANSLATE commands over the years.

• Install the latest SPSS Data Access Pack (version 8.1) - IBM SPSS Data Access Pack 8.1 Multiplatform English (M03HDEN)
• Create a new data source based on the latest driver for your database supplied by the above.
• Use the 'File -> Export - Database' wizard to re-create the SAVE TRANSLATE command and paste the syntax.
• Test the export using your old command syntax
• Test the export using the newly pasted syntax

If that doesn't resolve this problem, we'll need to open a case with Support.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Original Message:
Sent: Wed March 23, 2022 08:03 AM
From: Brenda Hurless
Subject: SPSS 27 - Export to Database Issue

We are experiencing issues when exporting data from SPSS datasets to our database. The issue began when we updated to SPSS 27 (no issues in prior versions). We've tried executing old syntax files that worked fine in prior versions of SPSS, exporting via the drop-down menus, etc. We can upload the data via other software applications without issues.

As soon as the coding to export the data is executed, the Case Counter begins at 2,000,000,000,000+. It then ticks up very slowly from there. If we're updating a small dataset (ex. few hundred cases), it is eventually successful but is taking way too long to get there. When dealing with something larger (ex. 90,000 records), it can run for hours and still not complete.

Below is an example of old coding that ran fine until SPSS 27 (removed info from CONNECT string, due to confidentiality). Any ideas?


SAVE TRANSLATE /TYPE=ODBC
/BULKLOADING BATCHSIZE=10000 METHOD=BATCH
/CONNECT="DSN=;UID=;PWD=;NA=;DB="
/UNENCRYPTED
/MISSING=IGNORE
/SQL='CREATE TABLE [scp] ([DOC_NUMBER] int , [CITIZENSHIP] varchar (50), '+
'[LAST_NAME] varchar (40), [FIRST_NAME] varchar (25), [MIDDLE_NAME] '+
'varchar (25), [DOB] datetime )'
/REPLACE
/TABLE='SPSS_TEMP'
/KEEP=DOC_NUMBER, CITIZENSHIP, LAST_NAME, FIRST_NAME, MIDDLE_NAME, DOB
/SQL='INSERT INTO [scp] ([DOC_NUMBER], [CITIZENSHIP], [LAST_NAME], '+
'[FIRST_NAME], [MIDDLE_NAME], [DOB]) SELECT [DOC_NUMBER], [CITIZENSHIP], '+
'[LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [DOB] FROM SPSS_TEMP'
/SQL='DROP TABLE [SPSS_TEMP]'.

------------------------------
Brenda Hurless
------------------------------
#SPSSStatistics