I wrote a procedure today to copy data from an ODBC database to another empty VFP database with the same tables and structures to get a copy of data from SQL server to a VFP database for testing. Basically, I created a VFP database with empty tables for the copy and used SPT to "SELECT *" from each table in the source database into a cursor, then append from the cursor into the matching table in the copy database. Not an elegant solution but a very simple one (or so I thought).

This worked well when copying data from a SQL Server database to a VFP database, but when I tested it using a VFP source database, on several tables, seemingly at random, the SPT query gets the correct record count from the source table, but when I append from the cursor, I get a "Uniqueness of primary key violation" error and the append fails. I have checked it out and the source VFP table does not have duplicate records, but the "select *" SPT cursor does. I have validated the database and recreated all indexes. What is happening is that the SPT cursor is duplicating several records in the result set when I "select * " from the source table using SPT. It does not happen on the same table or same records when running the program over and over.

I created a dbc with remote views that "Select *" from the source and run the same procedure using the RV's and it gets all records, with no error, every time. There are about 63 tables in the database ranging in size from a few records to over 400,000 records. All tables have primary keys and do not have duplicate records.

This concerns me as I use SPT all the time to get cursors for reporting (though not using Select *) but I am now concerned that I may not be getting correct results. I am using VFP 6 SP5.

I have created a couple of test programs with data that will demonstrate this very strange behavior. With the source database it is pretty large even zipped (41 MB) but if anyone is interested in checking it out, I have a FTP site where you can download it. Let me know and I will set it up for download.

This is the program that is getting this strange result. You may be able to use the comments at the top to duplicate it using a database of your own.

*!* create a directory c:\testcopydata\copyofdatabase\
*!* copy your database to c:\testcopydata\copyofdatabase\ with all tables with indexes
*!* create an ODBC connection to your source database
*!* define m._odbcdatasource = a valid ODBC Source name 
*!* define m._odbcuserid = valid ODBC userID
*!* Define m._odbcpassword = valid ODBC password for Source
*!* change the name to the correct dbc name.

m._odbcdatasource = "testcopydata" 
m._odbcuserid = ""
m._odbcpassword = ""
m.lcDatabaseCopyLocation = "c:\testcopydata\copyofdatabase\"
m.lcDBCNAME = "FELDAR32"

=messagebox("This copies the database using SQL Pass Through to get the source data and append to the copy tables")

close data all

*!* check for existence of copyifdatabase folder and create it if needed
if !directory(m.lcDatabaseCopyLocation) or !file(m.lcDatabaseCopyLocation+m.lcDBCNAME+".DBC")
	*** could not find or create data directory
	=messagebox(getapplicationtext("Error locating the folder specified for the copy of your database."))
	return .f.
endif

m.lcCDBSetSafety = set("safety")
set safety off
on error

*!* get a list of tables in the database to update
select objectname distinct from (m.lcDatabaseCopyLocation+m.lcDBCNAME+".dbc") ;
	into array laTablelist where objecttype = "Table"

close data all
if _tally > 0 and type("laTablelist[1]") = "C"
	for m.ln_xy = 1 to alen(laTablelist)
		m.lcTablename = trim(laTablelist[m.ln_xy])
		m.lcCursorname = "c_"+m.lcTablename
		close data all
		wait window "Copying Table "+m.lcTablename+"  Please wait..." nowait
                *!* execute Select * from each table into a cursor using SQLEXEC()
		if executeSQLcommand("Select * from "+m.lcTablename,m.lcCursorname) ;
			and used(m.lcCursorname) 
			select 0
			use (m.lcDatabaseCopyLocation+m.lcDBCNAME+"!"+m.lcTablename) alias (m.lcTablename) exclusive
			=cursorsetprop("buffering",1,m.lcTablename)
			select (m.lcTablename)
			zap
                        if reccount(m.lcCursorname) > 0
                             select (m.lcTablename)
                             *!* this is where it will fail if any duplicate records in the cursor
			     append from (dbf(m.lcCursorname))		
                        endif
			if reccount(m.lcCursorname) <> reccount(m.lcTablename)
				=messagebox("Error copying "+m.lcTablename)
			endif
		endif
	next
	wait clear
	close data all
	set safety &lcCDBSetSafety
endif



*************************************

function executeSQLcommand
	parameters m.lSqlCommand2exec,m.lcSqlResultcursor
	*!* this is a wrapper for SQLEXEC function
	*!* parameter 1 = SQL command to execute via SQL Pass Through
	*!* parameter 2 = Name of cursor to create for the result set
	local m.lcSetnotify,m.lcSetEscape,m.lnConnHandle 

	if type("m.lSqlCommand2exec")  <> "C" .or. empty(lSqlCommand2exec)
		return .f.
	endif
	m.lnSqlEXEResult = 0
	m.lcSetnotify = set("notify")
	m.lcSetEscape = set("escape")
	set notify off
	set escape off

	m.lnConnHandle = SQLCONNECT(trim(m._odbcdatasource), trim(m._odbcuserid),trim(m._odbcpassword))
	if type("m.lnConnHandle") = "N" .and. m.lnConnHandle > 0
		=SQLSETPROP(m.lnConnHandle, "Asynchronous",.f.)
		=SQLSETPROP(m.lnConnHandle, "BatchMode",.t.)

		if type("m.lcSqlResultcursor") = "C" .and. !empty(m.lcSqlResultcursor)
			m.lnSqlEXEResult = SQLEXEC(m.lnConnHandle, m.lSqlCommand2exec,m.lcSqlResultcursor)
		else
			m.lnSqlEXEResult = SQLEXEC(m.lnConnHandle, m.lSqlCommand2exec)
		endif

		=SQLDISCONNECT(m.lnConnHandle)
	endif && type("m.nConnHandle") = "N" .and. m.nConnHandle > 0

	set notify &lcSetnotify
	set escape &lcSetEscape

	return iif(type("m.lnSqlEXEResult") = "N" and m.lnSqlEXEResult > 0,.t.,.f.)