Working with MSSQL

[barrymavin]

Well VFP does not have virtual tables so perhaps you have the uid and psd somewhere in your code such as the named connection.

[barrymavin]

As I mentioned in your ticket and for the benefit of others.

You need to put the uid and password in the connstr.

This is not the only way to do it but you will allow you to proceed past this stage by doing that.

A VT is the equivalent in VFP terms as combining:

- a connection
- a view
- a cursoradaptor

But looks like a normal table so can be used in desktop/web/mobile apps without any messy coding required.

They can be paramerized and can be Requeried
They handle fetch on demand of individual rows or pages or rows from any database type
They handle syntax translation
They handle conflict resolution
Etc...

[yvonne.milne]

Slightly old screenshot, but the entry fields still apply:

Modify Virtual Table with uid/pwd

From:

Virtual Tables - Further Configuration and Troubleshooting


Regards,

Yvonne

[barrymavin]

Normally the simplest solution to get started is to put the credentials in the odbc DSN itself. Once you have everything working you can revisit this.

[CharlieDenver]

Thanks very much both
I assumed that when the virtual tables are created from the menu - As I had to enter the UID & Password - it would somehow propagate that detail to the vt
I understand now that each virtual table has to be manually set up with the UID & PWD after they have been created using that method
So I'm guessing I can issue a line of code that cteates a vt with the UID/PWD, I'll look at that
I get it now, thanks for clearing this up

[CharlieDenver]

I'll revisit the DSN, that seems to be the issue, Local MS-SQL works fine, I only get the issue for remote MS-SQL
I can see why now so I'll leave you in peace - thanks

[hmischel@diligentsystems.com]

Not sure if this is helpful, but I have created several procs that I use when connecting to MSSQL, which I do for a good portion of my applications.

Here is an example of one.

This is a proc called getremotefulltable.

param tablename, cursorname


gnConnHandle =sqlconnect("matchbackODBC","servicename","service pwd")
lianja.writelog(str(gnConnHandle))
strw1 =' select * from '+tablename+' '
=SQLPREPARE(gnConnHandle,strw1,'cresult')
=SQLEXEC(gnConnHandle)
macro = 'select * from cresult into cursor '+cursorname+' readwrite'
&macro
use in cresult
=sqldisconnect(gnConnHandle)

I use it like this.

getremotefulltable('exceptions','c_exceptions')

So then in Lianja, I have a cursor called c_exceptions which exists after the connection string is closed.

I the same thing for filtered tables where I also pass in a where condition.

Just my 2 cents.

Herb