Automating the import of data using Microsoft SQL Management Server 2008 5

[Steve-vfp9user]

Hello

I am using VFP9 SP2 and Windows 7 64 bit together with Microsoft SQL Management Server 2008

I am trying to find a way to automate the process using VFP9 of extracting data from a table within a database that I can convert into a TAB delimited file for import into a VF9 table.

The code has already been written to import the .TXT file but I am looking for some guidance on how to automate the first part. The reason being is that we need to extract up to date information every hour. I can run this process (if it is possible) via Windows Scheduler. The importing of the TXT file into a VFP table can also be updated using a VFP app with WIndows Scheduler.

This is how we do it manually:
Open Microsoft SQL Management Server 2008
At the prompt:
Server type: Database Engine
Server name: .\sqlxxxxxxxxx
Authentication: Windows Authentication
Click on Connect command button

We then click on New Query and enter:
USE OURTABLE
SELECT * from abc.ourdata
Press F5

This works and after a minute or so we have the information we require. I then have to right click on the results and "Save as.." filename.txt

In other automation processes we have used something Mike Lewis posted to create and open Excel files and also to open PDF files and whilst this is not the same procedure I'm sure, this is what we are trying to achieve by automating if that makes sense.

My question is, how do I automate the process to extract the data and create the desired .TXT file (I am able to code the rest)

Thank you

Steve

 

[Steve-vfp9user]

JRB-Bldr

Thank you for your post. I did some more research and found quite a simple sollution to our requirements:

WAIT "Process started, please wait...." WINDOW NOWAIT

lcConnstring = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)

*	Set the active database to OURDATA

SQLEXEC(m.lnConn, 'USE OURDATA')

*	Execute SELECT statement.

SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata'')

WAIT CLEAR

WAIT "Now copying the SQL data to a workable format...." WINDOW NOWAIT

COPY TO MYNEWTABLE

SQLDISCONNECT(m.lnConn)

WAIT CLEAR

This works as it should and the simple COPY TO MYNEWTABLE was sufficient.

Seems as though sometimes the simplest ways are the easiest without the need for others complex methods.

I'm grateful for your time and post JRB-Bldr

Thank you

Steve

 

[Mike Lewis]

This works as it should and the simple COPY TO MYNEWTABLE was sufficient.

Yes, but the reason that works is that your SQLEXEC() places its result set in a default cursor (named SQLResult, by default). That cursor is in the active work area after the SQLEXEC() finishes. The COPY TO command operates on the cursor in the active work area, which is why the result from SQLEXEC() gets copied to the new table.

My point is that it is generally better to understand exactly what the code is doing, rather than simply looking for examples that happen to meet your needs.

By the way - and at risk of prolonging this thread beyond its natural span - you can slightly simplify your code by combining your USE OURDATA with your SELECT * FROM abc.ourdata. Assuming OURDATA is the name of both your database and your table (which is unusual, but perfectly legal), you can do this:

SQLEXEC(m.lnConn, 'USE OURDATA SELECT * FROM abc.ourdata')

or, better still:

SQLEXEC(m.lnConn, 'SELECT * FROM ourdata.abc.ourdata')

Also, I note you have a spurious apostrophe in your second SQLEXEC().

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Now thats what I call easy and exaplainable advice Mike Lewis. It's just a shame that not everyone can keep it simple for us VFP9 newbies.

I will change the coding accordingly but also take on board your advice about the understanding of how things work. It is early days for us so I am sure time will tell.

Thank you

Steve

 

[Mike Lewis]

Glad you found it useful, Steve. Remember, we were all beginners once.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Mike Lewis:

"Remember, we were all beginners once"

Looking at some replies I find that hard to believe. Seems to me they were brought up on VFP (and maybe the DOS version on the way!)

Thank you

Steve

 

[Olaf Doschke]

You don't need to mention OURDATA in your SQLEXEC, as you make your connection to that database, it's already active then.

Your main function call simply is

lcConnstring = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)
SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata','MYNEWTABLE')
SQLDISCONNECT(m.lnConn)

And then you can still save the cursor alias MYNEWTABLE to a DBF file, if that's what you want. Normally working with SQL Server means you don't write or read or copy or work with DBFs anymore, as you can get your data from SQL Server, it's your database now instead of DBFs. And you can work with cursor as if it was a dbf, you can bind controls to it, etc.

Bye, Olaf.

 

[Steve-vfp9user]

Hi Olaf

Once the process has run, I then have to add fields to it using:

alter table mytable add column myfield1 c(20)

and also drop columns etc

I then run a process that replaces myfield1 with others fields in the table.

There are quite a few fields but when it's completed we have a table just as we require it. I find it easier at the moment to manipulate and work with a dbf

Appreciate your last post

Thank you

Steve