Remote View -- Line Too Long

[gregjohn]

I am in the process of converting vfp tables to sql server express. I have the connection made and have used remote views. For several tables this works fine.

However, I have a few tables that, have very long field names. When I try to create the remote view I receive an error that "line is too long". I am assuming this means the field name as opposed to the number of fields which is about 245. Is that correct? (In looking at the "view sql", I see it has truncated several of the field names).

Also, if it is the length of the field is there any way around it without having to go through and rename everything?

Thanks for any help.

Greg

 

[JimWWinter]

You didn't say what version of VFP you're using, but in VFP9 the maximum length of a field name is 128 characters, the maximum number of fields in a select statement is 255, and the maximum number of characters in a command line or macro substituted line is 8,192.

When you say the number of fields in the view is about 245, are they all coming from one table? If so, I'd take a long, hard look at normalising that table. And if they're coming from multiple tables, what are you going to do with all that data in one view?

Good luck,
Jim

 

[gregjohn]

I am using vfp 8 and a single table. Do you know if the lengths you described for vfp 9 are the same for 8?

While my fields are long, they are not 128 characters. The fields exist in a table right now.

 

[JimWWinter]

Yes, those are the same in VFP8. Probably you're bumping up against the command line limitation of 8,192 characters. You might need to do something like the following.

LOCAL lnH1, lcSql, lnResult

lnH1 = SQLSTRINGCONNECT(Add in your connection string here)

TEXT TO lcSql NOSHOW PRETEXT 7
*(Create SQL View text goes here)
ENDTEXT

lnResult = SQLEXEC(lnH1, lcSql)

But again, I'd urge you to look at normalising that data.

Regards,
Jim