update sql server data

[kenndot]

hi all

i've read a bunch of posts on querying sql tables and returning info, but I just want to update the SQL tables and that is all. I don't need to check anything or bring anything down from SQL, I simply have a table that I create in fox and I have an identical table in SQL that I want to send that to. What is the best way?

I am reading about cursoradapters now

I've used the "UPDATE" function before to just update a few fields in a remote DB, but I'm not sure if that is silly to do in this case - one by one i mean, it's one big dump of a table, I'm positive that everything is a compatible data type and ready to go and I've issued an

INSERT INTO mkt_headcount
SELECT *
FROM myFoxTbl

but this isn't working, I get
Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mkt_headcount'."

I can query the sql table and I know it's there, I'm thinking I'm forgetting a step somewhere

I guess my main question here is what's the best way to approach this? Then, why isn't that query working?

I tried to think of all details to tell, but please tell me if you need more information to respond, thank you much

 

[kenndot]

Mike,

Yes that did help very much, thank you for your input, we've now decided to go the text file DTS route

thanks again

 

[zhavic]

DTS is good choise, expect there may some problems if you are using fields like GENERAL or MEMO

I want show example from my previous post in "your language"
Offcourse, there are more situations, in which it could not be
used,by try it.

DECLARE nHandle

DECLARE cServer_name, ;
		cDatabase_name, ;
		cSQL_table_name, ;
		cFoxPro_table_name, ;
		cFoxPro_table_location, ;
		cUser_ID, ;
		cPassword

DECLARE aErrors
DIMENSION aErrors(1)

cServer_name = "YOUR_SQL_SERVER"					&& SQL server name
cDatabase_name = "YOUR_DATABASE"				&& destination database name on SQL server
cSQL_table_name = "mkt_headcount"			&& destination_table_name
cFoxPro_table_name = "myFoxTbl"
cFoxPro_table_location = "C:\my_dir\my_tables\"		&& foxpro table location
cUser_ID = "sa"		&& user login name for SQL server
cPassword = ""		&& user password for SQL server

nHandle = SQLSTRINGCONNECT( "driver=SQL Server;" + ;
							"server=" + cServer_name + ";" + ;
							"UID=" + cUser_ID + ";" + ;
							"PWD=" + cPassword + ";" + ;
							"DATABASE=" + cDatabase_name + ";" )
							
IF nHandle > 0
	SQLSETPROP( nHandle, "Transactions", 2 )		&& set manual transactions
	
	IF SQLEXEC( nHandle, ;
				"INSERT INTO " + cSQL_table_name + " " ;
					+ "SELECT a.* " ;			&& ofcourse, you can there list only column names you want, in SQL server syntax
					+ "FROM OPENROWSET( 'VFPOLEDB.1'," ;
					+ "'" + cFoxPro_table_location + "';;,"
					+ "'SELECT * FROM " + cFoxPro_table_name + "') AS a" ) = 1		&& ofcourse, there you can list only column names you want too, in FoxPro syntax
		IF SQLCOMMIT( nHandle ) = 1
			MESSAGEBOX( "Insert successfull !" )
		ELSE
			AERROR( aErrors )
			MESSAGEBOX( "Could not commit transaction !" + IIF( TYPE( "aErrors(2)" ) = 'C', CHR(13) + CHR(10) + aErrors(2), "" ) )
		ENDIF
	ELSE
		AERROR( aErrors )
		SQLROLLBACK( nHandle )
		MESSAGEBOX( "An error occured while inserting rows !" + IIF( TYPE( "aErrors(2)" ) = 'C', CHR(13) + CHR(10) + aErrors(2), "" ) )
	ENDIF
ELSE
	MESSAGEBOX( "Could not connect to SQL server !" )
ENDIF
	
IF nHandle > 0
	SQLDISCONNECT( nHandle )
ENDIF

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Mike Lewis]

Zhavic,

expect there may some problems if you are using fields like GENERAL or MEMO

You're right that there would be problems with General fields, but that would apply to whatever method Kenndot adopted.

As for memo fields, I have successfully imported these many times with DTS. The only problem I am aware of is if the target field on the server does not accept nulls. In that case, you cannot send an empty memo -- it has to have at least one space in it.

What other problems do you know about?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

Yes, please if you know of other problems, I would love to hear it because I am new to this whole angle and am looking for as much info as possible

 

[Mike Lewis]

Kenndot,

Well, there will always be issues with default values and nulls. This applies no matter what mechanism you are using to upload the data.

Essentially, for an insert to work, you must either (i) provide an explicit value for a given field, or (ii) ensure the field can contain nulls, or (iii) ensure the field has a default value (a default 'constraint' in SQL Server parlance).

An exception is if the field has an IDENTITY attribute (an auto-incrementing value). In that case, you must definitely not supply a value for it. Just leave it out of the upload.

There are probably other issues to consider as well, but I suggest you get started with DTS or whatever tool you settle on, and let us know when you have some specific questions.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[fluteplr]

I agree for what you are doing I would look at DTS.

On an earlier point that someone made however I disagree. SQLCommit and SQLRollback work just fine with views you just need to identify what handle the view is using as I poined out inan earlier thread.

 

[Mike Lewis]

Flutepir,

On an earlier point that someone made however I disagree. SQLCommit and SQLRollback work just fine with views you just need to identify what handle the view is using as I poined out inan earlier thread.

That's true. I didn't say that you couldn't use them with views -- just that it was another complication to worry about.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

Mike,

I have a question regarding something you wrote about DTS. You mentioned you can invoke a DTS package from VFP, how do you do this? Have you got an example of a sqlexec (assuming that's how it's done) command that shows this?

Thanks

 

[Mike Lewis]

Kenndot,

The way to invoke the DTS package from VFP is to use COM Automation. I don't have all the details to hand, but going from memory, it goes something like this:

oDTS = CREATEOBJECT("dts.package&quot
oDTS.Execute

Actually, there must be a more to it than that, because you obviously need to specify which package you want to run. You should be able to find it in the SQL Server Help (I'm not at my SQL Server system at the moment). None of this is VFP-specific.

There are other ways of running DTS packages. You can do it from inside the Enterprise Manager, or you can do it from a command prompt by running DTSRUN.EXE (you should be able to do that from within VFP using the RUN command).

Hope this helps.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

Hi Mike,

I tried the run command, it seemed the quickest way... I generated the run command from the dts utility, i get no errors, but it just doesn't run anything, here's the command i used

RUN DTSRun /S "DWPROCESS" /N "SiteCount_1_LoadTextFiles" /G "{00B299BE-E99A-4CA2-AC5D-2254B3D5F0DB}" /W "-1" /U dts_test /P test

there is a param here that foxpro doesn't like

we tried it like that... and

RUN DTSRun /S "DWPROCESS" /N "SiteCount_1_LoadTextFiles" /G "{00B299BE-E99A-4CA2-AC5D-2254B3D5F0DB}" /W "-1" /E

this doesn't generate any errors, it simply doesn't run anything

i can't think of what is missing, can you help me?

i know I can create a dts object, i'm doing that now, but i would really like to know if there's something else I can do to run this using the run command since it's so much easier.

 

[Mike Lewis]

Kenndot,

I don't know enough about running DTS like that to diagnose the problem.

Have you tried using ShellExecute() instead of RUN? It might make a difference.

Mike'

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com