Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

update sql table with foxpro with remote view, connectivtiy error

JICGreg (TechnicalUser) (OP)
4 Feb 13 16:37
I often update sql tables from foxpro using a remote view. The sql programmer takes over after that -- so fox is just used to get data into sql.

I'm trying to do this with a new sql table and I am running into problems. I used the foxpro upsizing wizard to get the table created in sql.

I have an existing odbc connection that I use for other tables and I have created my remote view -- I can browse the sql table via this remote view.

I then follow my "normal" process to update a table, but I'm getting this error:

Connectivity error: [MICROSOFT][odbc SQL Server Driver] Numeric value out of range.

Below is my code -- any insights as to why I'm getting this error would be really appreciated.



PROCEDURE upload_data_to_sql
CLOSE TABLES all

SELECT g
USE n:\research\trading\transaction_data\trading
DELETE ALL
PACK

SELECT c
USE n:\research\trading\transaction_data\&manager_allocation_name
SCAN
SCATTER memvar
SELECT g
APPEND blank
GATHER memvar
ENDSCAN

STORE "n:\research\trading\transaction_data\transaction_data" to dbasename
STORE "trading" TO foxpro_source_table
STORE "trading" TO sql_source_table
STORE "n:\research\trading\transaction_data\" to directory
STORE "view_trading" TO table_view

STORE "delete FROM " + sql_source_table TO table_select

lnConn = SQLCONNECT("JicReturns",.T.)
nret=SQLEXEC(lnConn,table_select)
IF nRet = 1
CLEAR
@10,10 say "success"
ELSE
@10,10 say "failure"
fdsa
endif
SQLDISCONNECT(lnConn)


CLOSE TABLES all
OPEN DATABASE &dbasename
USE &table_view

STORE directory + foxpro_source_table + ".dbf" TO file_name
APPEND FROM &file_name
TABLEUPDATE(.T.)

endproc
jrbbldr (Programmer)
4 Feb 13 16:49
Have you tried to run the SQL Command directly within the SQL Server Maintenance utility?

CODE -->

dbasename = "n:\research\trading\transaction_data\transaction_data"
foxpro_source_table = "trading"
directory = "n:\research\trading\transaction_data\"
table_view = "view_trading"
sql_source_table = "trading"

* --- Define SQL Query Command String ---
table_select = "delete FROM " + sql_source_table

SET STEP ON <=== Added For VFP Debugging

lnConn = SQLCONNECT("JicReturns",.T.)
nret=SQLEXEC(lnConn,table_select) 

When the code does its Debug Break at the SET STEP ON, copy the SQL Command (table_select) and then paste it into a New SQL Query window within the SQL Server Maintenance Utility to confirm its syntax.

Looking at the command, it appears as though you are missing either a '*' (asterix) to Delete everything or a WHERE clause.

Good Luck,
JRB-Bldr
danfreeman (Programmer)
4 Feb 13 17:01
There's a lot about your code that could be fixed. (Don't hard-code workareas. Why DELETE ALL/PACK when ZAP would do it in one command? Why scan/scatter when you could do it with one SQL-INSERT command?)

But the overriding question I'm wondering about is why you're asking about a view when you're sending SQL pass through.

I wonder what else is being left out of the question.

Based on the error message you're reporting, I'd bet the upsizing wizard created a numeric of the wrong type on the back end and your actual data is overflowing it but since the command you're sending is a DELETE it seems you're not updating either.

Can you clarify?
OlafDoschke (Programmer)
4 Feb 13 17:44
From what Dan said and from the error being a numeric overflow, I guess the problem is just with the final TABLUPDATE(.T.):

CODE --> Foxpro

OPEN DATABASE &dbasename
USE &table_view

STORE directory + foxpro_source_table + ".dbf" TO file_name
APPEND FROM &file_name
TABLEUPDATE(.T.) 

so the problem is in the data you append from &file_name and the numeric range of the sql server prohibits to send over some data.

The problem is not seen in your code, it's about the data. If you have eg an N(3,1) field in VFP you can store values like 1.1, but also 999, because mainly you have 3 places. In SQL Server only values from -9.9 to 9.9 would be allowed. There's a mismatch in definitions. Easiest solution would be to change some numeric field that could cause this on the SQL Server side with float or double float type fields.

Bye, Olaf.
OlafDoschke (Programmer)
4 Feb 13 18:05
And by the way: Even simple SQL syntax errors or type errors or any other problem with a query you run starts with "Connectivity Error", but isn't necessarily a connectivity problem, LAN or connection handle lost or such, but simply a problem with the query itself or the data. The detail message about a value out of range couldn't occur, if no values would arrive at the server side, could it?

Bye, Olaf.
JICGreg (TechnicalUser) (OP)
4 Feb 13 20:35
Thank you all for your responses. I will go through the sql table and check the data structure as well as my foxpro data to try and locate the problem data. I'll post any additional questions.

Greg

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close