Connecting to SQL Server 2008 Express Databases from Visual FoxPro 9
Gene Wirchenko
The subject about says it all. How do I do this?
My SQL Server 2008 Express system is LOOP\SQLEXPRESS.
Apparently, I should create an ODBC connection. I have no idea
how to do this.
If you only know the SQL Server side, please reply with that
side. That is more than what I know.
Sincerely,
Gene Wirchenko
Erland Sommarskog
>
> The subject about says it all. How do I do this?
>
> My SQL Server 2008 Express system is LOOP\SQLEXPRESS.
>
> Apparently, I should create an ODBC connection. I have no idea
> how to do this.
>
> If you only know the SQL Server side, please reply with that
> side. That is more than what I know.
is requireed, you may also need to set up login mapping with
sp_addlinkedsrvlogin.
Once you have this set up, you can query the FoxPro database using
four-part notation, for instance "SELECT * FROM SERVER.db..tbl".
If there is an OLE DB provider for Foxpro, use that one. Else you can
use the MSDASQL provider - that is OLE DB over ODBC - and any the ODBC
driver for Foxpro there might be.
I suspect that Google can be helpful.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>Gene Wirchenko (ge...@ocis.net) writes:
>>
>> The subject about says it all. How do I do this?
>>
>> My SQL Server 2008 Express system is LOOP\SQLEXPRESS.
>>
>> Apparently, I should create an ODBC connection. I have no idea
>> how to do this.
>>
>> If you only know the SQL Server side, please reply with that
>> side. That is more than what I know.
>
>You use sp_addlinkedserver to define a linked server. If authentication
>is requireed, you may also need to set up login mapping with
>sp_addlinkedsrvlogin.
>
>Once you have this set up, you can query the FoxPro database using
>four-part notation, for instance "SELECT * FROM SERVER.db..tbl".
I want to query *from* VFP *to* SQL Server.
(Why is it that so many people do not read the title of posts?
Why is it that I forgot this?)
>If there is an OLE DB provider for Foxpro, use that one. Else you can
>use the MSDASQL provider - that is OLE DB over ODBC - and any the ODBC
>driver for Foxpro there might be.
>
>I suspect that Google can be helpful.
No. I do not know enough to know what to look for, so it just
gets me swamped.
Sincerely,
Gene Wirchenko
Jason Keats
I haven't used Visual Foxpro, but I have used VB with SQL Server. I'm
pretty sure it's similar.
You don't say whether you're trying to connect to SQL Server on a local
or remote machine. In either case, you'll probably want to configure the
TCP/IP protocol. You will probably also want to enable remote connections.
There are plenty of articles that discuss the above. For example:
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
http://blogs.msdn.com/b/bethmassi/archive/2008/09/17/enabling-remote-sql-express-2008-network-connections-on-vista.aspx
Part 10 of the above article discusses connection strings.
More information about connection strings can be obtained at:
http://www.carlprothman.net/Default.aspx?tabid=81
http://www.connectionstrings.com/
I tend to use ADO with an OLEDB provider, rather than ODBC.
HTH
Erland Sommarskog
> I want to query *from* VFP *to* SQL Server.
>
> (Why is it that so many people do not read the title of posts?
> Why is it that I forgot this?)
FoxPro?
I have no idea how you connect to remote data sources in Visual FoxPro. In
fact my knowledge about FoxPro is about zero.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Viorel Nacu
You should have a dbc created for all work with that sql server.
On the dbc you can create manually a connection to your server or using the
following command only once (with the dbc open)
create connection yourconn connstring "DRIVER=SQL
Server;SERVER=yourservername;UID=username;PWD=password;DATABASE=yourdatabasename"
Once you have the connection created you can create remote view on the dbc
and use like regular foxpro tables or use the following command to get data
from sql server on foxpro cursors
Open a connection to sql server
myconn = sqlconnect('yourconn')
To verify if connection is ok
? myconn
If positive is ok
If negative be sure that you enabled remote connection and at least the
TCP/IP protocol on the sql server.
Get data from sql server
=sqlexec(myconn, "select * from somesqltabel", "vfpcursor")
You can reuse the connection for multiple commands.
When you do not need it anymore close the connection with
=sqldisconnect(myconn)
If you need any other connection just pay a visit to
http://www.connectionstrings.com/
Regards,
Viorel
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9EA8648BE...@127.0.0.1...
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>Gene Wirchenko (ge...@ocis.net) writes:
>> I want to query *from* VFP *to* SQL Server.
>>
>> (Why is it that so many people do not read the title of posts?
>> Why is it that I forgot this?)
>
>Because this is an SQL Server newsgroup, and your question is about Visual
>FoxPro?
My question is about both. You just got the direction wrong.
>I have no idea how you connect to remote data sources in Visual FoxPro. In
>fact my knowledge about FoxPro is about zero.
I figure that it is similar between different languages, but I do
not know that it is not necessary to do something in SQL Server first
to make the data accessible. I need both pieces.
Sincerely,
Gene Wirchenko
Erland Sommarskog
> I figure that it is similar between different languages, but I do
> not know that it is not necessary to do something in SQL Server first
> to make the data accessible. I need both pieces.
SQL Server Management Studio or any other application. You need to
have a valid login and if you connect from a different machine, SQL Server
must accept connections over TCP/IP or named pipes, and there must not
be any firewall in the way.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>Gene Wirchenko (ge...@ocis.net) writes:
>> I figure that it is similar between different languages, but I do
>> not know that it is not necessary to do something in SQL Server first
>> to make the data accessible. I need both pieces.
>
>Connecting from Visual FoxPro is nothing different than connecting from
>SQL Server Management Studio or any other application. You need to
>have a valid login and if you connect from a different machine, SQL Server
>must accept connections over TCP/IP or named pipes, and there must not
>be any firewall in the way.
Thank you. Some of the blur that I tried to follow seemed to
indicate the opposite. Sometimes, the Web is rather sticky.
Sincerely,
Gene Wirchenko
i79...@fake.uwasa.fi.invalid
> Dear SQLers:
> The subject about says it all. How do I do this?
> My SQL Server 2008 Express system is LOOP\SQLEXPRESS.
> Apparently, I should create an ODBC connection. I have no idea
> how to do this.
Search VFP help for "ODBC". You should get more that enough information.
--
JS
Gene Wirchenko
Actually, no. I just did, and it is missing a lot. Even if it
did, "more than enough" means too much. I need the starting
information right now. (Well, a bit past that now, but still,
drowning me does not help me.)
Sincerely,
Gene Wirchenko
Henk van den Berg
What do you have so far?
What info are you missing?
Best,
Henk
Gene Wirchenko
I can query an arbitrary database, run multiple queries at a
time, and create a database.
I can catch the very basic errors, but not detailed error codes.
This is a function of Visual FoxPro, not SS.
>What info are you missing?
Error trapping.
How to suppress SS popping up for more data on an error. I would
like to handle these errors programmatically, even if only an error
message.
Sincerely,
Gene Wirchenko
i79...@fake.uwasa.fi.invalid
> I can catch the very basic errors, but not detailed error codes.
> This is a function of Visual FoxPro, not SS.
> How to suppress SS popping up for more data on an error. I would
> like to handle these errors programmatically, even if only an error
> message.
From VFP Help:
If a SQL pass-through function returns an error, Visual FoxPro stores the error message in an array.
The AERROR( ) function provides information about errors that are detected in any of the component
levels: Visual FoxPro, the ODBC data source, or the remote server.
By examining the values returned by AERROR( ), you can determine the server error
that occurred and its error message text.
You must call AERROR() immediately to obtain error information.
If you generate any other error before you call AERROR(), the error information is lost.
The steps to run a query or command from VFP to SQL Server:
1. Create a system DSN or create a connection string
2. Connect with SQLCONNECT() or SQLSTRINGCONNECT()
3. Set connection properties with SQLSETPROP()
4. Run query or send command with SQLEXEC()
5. Disconnect with SQLDISCONNECT()
The details and parameters of the functions depend on the case at hand.
There are lots of examples in VFP Help.
--
JS
Henk van den Berg
an ODBC connection. Now you're saying that you can query a database, run
queries and create a database.
Are you doing this from within VFP?
Gene Wirchenko
<hvand...@xs4all.nl> wrote:
>Huh? In your first message you state that you don't know how to set up
>an ODBC connection. Now you're saying that you can query a database, run
>queries and create a database.
Why be surprised? I have been able to figure out some of it.
Now, I need to figure out more.
>Are you doing this from within VFP?
Yes.
[snip]
Sincerely,
Gene Wirchenko
Henk van den Berg
> On Mon, 21 Mar 2011 09:40:09 +0100, Henk van den Berg
> <hvand...@xs4all.nl> wrote:
>
>> Huh? In your first message you state that you don't know how to set up
>> an ODBC connection. Now you're saying that you can query a database, run
>> queries and create a database.
>
> Why be surprised? I have been able to figure out some of it.
to create an ODBC connection
Best,
Henk
Henk van den Berg
>
>
> Error trapping.
>
> How to suppress SS popping up for more data on an error. I would
> like to handle these errors programmatically, even if only an error
> message.
Hi Gene,
So what's the latest status? Did you get your problem solved? Or do you
still need pointers to solutions?
Best,
Henk
>
> Sincerely,
>
> Gene Wirchenko
Gene Wirchenko
<hvand...@xs4all.nl> wrote:
>On 21-03-2011 05:40, Gene Wirchenko wrote:
>>
>>> What info are you missing?
>>
>> Error trapping.
>>
>> How to suppress SS popping up for more data on an error. I would
>> like to handle these errors programmatically, even if only an error
>> message.
>So what's the latest status? Did you get your problem solved? Or do you
>still need pointers to solutions?
Not yet. I have been busy on another job these past few days.
I could still use some pointers. At this point, I would really
prefer pointers rather than answers here. A simple but non-trivial
example that uses many of the features of ODBC would be great.
Failing that, a list of the statements and functions that I need
to know so I can go reading would be useful. (It is sometimes hard to
figure out which ones those are.)
Sincerely,
Gene Wirchenko
Henk van den Berg
> Not yet. I have been busy on another job these past few days.
>
> I could still use some pointers. At this point, I would really
> prefer pointers rather than answers here. A simple but non-trivial
> example that uses many of the features of ODBC would be great.
>
> Failing that, a list of the statements and functions that I need
> to know so I can go reading would be useful. (It is sometimes hard to
> figure out which ones those are.)
Best,
Henk
i79...@fake.uwasa.fi.invalid
OK. This is about the most simple sample that I know of.
http://fox.wikis.com/wc.dll?Wiki~BetterIntegrationOfVFPWithSQLServer~VFP
--
JS
Paul Pedersen
It's been a while... let's see what I remember.
In the Administrative Tools control panel, create a system ODBC source
and point it at the SQL server and database you're interested in.
Then in your FoxPro database, create a connection and point it to that
ODBC source. When you create remote views, use that connection.
Alternatively, skip the ODBC source and create a connection in the
FoxPro database using a connection string.