Connecting to FoxPro & Exporting to Excel

I am currently in the process of converting some Access functionality to T-S
QL.
What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
manipulate data in a SQL table and then export that table to Excel.
Can anyone tell me if this is possible.Youc can create a linked server to a FoxPro DBC:
EXEC sp_addlinkedserver
@.srvproduct='',
@.server='VFP01',
@.provider='VFPOLEDB',
@.datasrc='C:\xyz.DBC'
(I'm assuming VFP7.0 or later with the OLEDB driver installed)
Then reference your Fox tables directly:
SELECT * FROM VFP01...table_name
For the export to Excel you'll probably have to use DTS, which is also
another option for the FoxPro data: import it rather than link it.
David Portas
SQL Server MVP
--|||Hi Chubbly,
Just to add, the FoxPro and Visual FoxPro OLE DB data provider is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates .
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Chubbly Geezer" <ChubblyGeezer@.discussions.microsoft.com> wrote in message
news:2AF3439C-E2E5-45C8-B5D2-1FFFF1B6CEC9@.microsoft.com...
>I am currently in the process of converting some Access functionality to
>T-SQL.
> What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
> manipulate data in a SQL table and then export that table to Excel.
> Can anyone tell me if this is possible.
>
>|||Thanks David, almost there I think. However with the following code I am
getting an error message. Also in Enterprise Manager my linked server is
showing no tables.
The VB ADO string I used to connect previously is this:
OperaConn.ConnectionString = "Driver=Microsoft Visual Foxpro Driver; UID=;
SourceType=DBC; SourceDB=i:\operaii\data\comp_W.dbc"
EXEC sp_dropserver 'OPERA_SERVER'
EXEC sp_addlinkedserver
@.server='OPERA_SERVER',
@.srvproduct='',
@.provider='VFPOLEDB',
@.datasrc='c:\temp\Comp_W.dbc'
Select * From OPERA_SERVER...ssale
--
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'OPERA_SERVER' does not contain table 'ssale'. The table
either does not exist or the current user does not have permissions on that
table.
Chubbly
"David Portas" wrote:

> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>|||That's worked actually thanks very much David.
The error I had was caused by the fact I copied the files I beleived I
needed to a temp directory for testing. There were obviously files missing
which I also needed.
Many thanks.
"David Portas" wrote:

> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>

connecting analysis services to excel (client side)

Hi,

How is the configuration of connection for a client Excel pivot table connected to Analysis Services? Is there anyway in which when we copy the .xls with pivot table to client PC, it will automatically connect to Analysis Services?

thanks in advance.

cherrie

Cherrie,

Do you have analysis services database installed on each pc and want to connect allways locally? If so, then in connection string instead of specifying machine name, use localhost.

If this is not what you ask, could you please clarify your question. What do you mean automatically connect to Analysis Services?

Vidas Matelis

|||

Hi,

Let's say I have a server in which Analysis Services is located together with my SQL Server database. Since currently, SSAS resides in my local drive from which the local excel is also connected through setting it up in Data > Import External Data > New Database Query > OLAP Cubes and it's doing well. But I'm thinking if let's say I want users to access the SSAS cube from the server to their local Excel? How would I configure the connection?

Thanks!

cherrie

|||

Cherrie,

It looks like you are using Excel 2003. Steps for your users will be exactly the same. You go :

Data > Import External Data > New Database Query > OLAP Cubes > <New Data Source> > etc

Then you specify data source name, provider, click connect and specify "Analysis Server" name. This name could be name of your machine or SSAS Server.

Leave UserID & Password empty, then select database and you are done.

Vidas Matelis

|||

Vidas,

Can the connection be stored in the UDL instead or store the connection somewhere in the local drive of the user apart from the .xls file?

Cherrie

Connecting a Cube from Excel 2007

Hi,

i am working with a XP-Client and try to connect to a WinServer2003, (SP2, AS2005) to access an AS2005-cube by Excel 2007. Creating a new connection, i can choose either connect via windows authentification or by entering a user/PW.

The first way (windows authentification) always works fine, but when i try to connect through an sql user, i run on an error at last. The connection works fine, i can select the desired cube but after the last "OK"-Button i get an error message like "Initialization of Data source failed".

the sql user should have admin right on the server.

Do i make sth. wrong?

SQL Server 2005 Analyisis Services Supports only Windows Authentication. (SQL Authentication is only possible if you connect to the SQL Server 2005 Database Engine)

So the username and password you want to enter must be a valid windows nt user with permission on the analysis services database. (check save password otherwise you get errors in excel)

HANNES