AFIELDS() TYPE COMMAND FOR SQL SERVER tables

[WIREMESH]

I have a MS SQL Server 2008 database that I want to get the database schema and load into dbf tables for the purposes of creating remote views in VFP.

In essence, I want something similar to the AFIELDS() command in VFP.

 

[Olaf Doschke]

CREATE SQL VIEW REMOTE will guide you to connect and choose table/fields from SQL server and make it the correct field types, so there is no real need for knowing the field types, but of course it helps, if you don't want to do that interactively.

SQLTables() will help you find out the sql tables structures. First do an SQLStringConnect, then SqlTables(Handle,"TABLE","curSQLTables") and then browse curSQLTables.

Bye, Olaf.

 

[Mike Lewis]

Wiremesh,

SQLCOLUMNS() is the equivalent to AFIELDS(). You can create your remote views programmatically by first looping through the (remote) tables, using SQLTABLES(), and then through the fields, using SQLCOLUMNS().

However as Olaf correcly points out, you don't need to go that route. You say "I want to get the database schema and load into dbf tables for the purposes of creating remote views in VFP." If you're creating remote views, DBF tables don't come into the picture.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

WIREMESH,
One suggestion:
Do not use RVs with SQL Server 2008.
Views use only ODBC driver and VFP can't handle correctly new 2008 ODBC driver. Use CursorAdapter and ADO.
Of course you could use old SQL Server 2000 driver but that way you can't use new field types and possibilities of 2008.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Olaf Doschke]

Hi Mike, I forgot SQLColumns, thanks for adding that. It's true that Cursoradapters are preferable, so if the decision isn't final, look into that option. I haven't tried SQL2008 yet, but I'd trust Borislav, if he says you can't use SQL2008 ODBC with VFP.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

I'd trust Borislav, if he says you can't use SQL2008 ODBC with VFP.

I'd trust Borislav as well. I've got a client who is just moving over to 2008. I've asked them to test the new ODBC driver with VFP in order to judge the problems.

That said, if Wiremesh has already gone a long way down the remote view path, he shouldn't necessarily abandon that if he's using 2008. If it's a trade-off between the convenience of remote views and the new features of 2008, he might prefer to stick with what he's got.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

No, you can use it, but in some conditions you will get error where there is not such thing. Also I don't know why but VFP sometimes send DateTime as datetime2 type and SQL Server 2008 didn't like the format passed from VFP.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[WIREMESH]

Thanks guys. I have an application (and budget) that requires me to use remote views. I don't need to use any of the new SQL Server 2008 field types so I hope I can use the 2008 driver. However, it is nice to know I can also use the 2000 driver.