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

 

[craigber]

Look at SQL Server Integration Services (SSIS). You can schedule jobs that will do the extract for you and drop the file onto the server.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[JRB-Bldr]

extracting data from a table that I can convert into a TAB delimited file for import into a VF9 table.

Rather than doing all of that, why don't you just use ODBC to 'directly' open the SQL Server data table(s) into a VFP Cursor.

From there you can:
1. Manipulate the field contents if needed.
2. Copy the data to a 'real' VFP data table if so desired and/or work with the data 'as is'.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Hi Steve,

There are several ways of doing this.

Within SQL Server, you can use the bcp utility to directly convert a table (or, more precisely, a result set) to a text file. That's probably the simplest solution. Check the SQL Sever Help file for details.

Alternatively, within Visual FoxPro, you can create a remote view into the table. Once you've done that, the view behave pretty much like an ordinary DBF, so you can use COPY TO to convert it to a text file. (Or, simply copy it directly to the target DBF.)

Also in VFP, you can use SQLEXEC() to get the data into a FoxPro cursor. Again, you can then use ordinary Foxpro commands to copy the data whereever you like.

If you need any help with any of the above, just yell.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Some very positive replies there guys which I thank you for. I will explore these avenues and post back.

Steve

 

[Steve-vfp9user]

All, I have taken an example from the help file and tried to adapt it but without success (keeping in mind I have changed the actual user ID and password etc for this thread):

CLEAR
LOCAL lnConn
LOCAL lnPercent AS Int  && Input parameters must be typed.
LOCAL lnOutput 
lnPercent = 50
lnOutput = 0
lnConn = SQLCONNECT('.\sqlxxxxxxxxx', 'myuserid', 'mypassword')
IF m.lnConn > 0  && Success.
   WAIT "Connected" WINDOW NOWAIT

*  Set the active database

   SQLEXEC(m.lnConn, 'use ourtable')

*  Execute SELECT statement.

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

   BROWSE

   SQLDISCONNECT(m.lnConn)

ELSE

   WAIT "Unable to connect to SQL Server" WINDOW

ENDIF
RETURN

Any ideas why I cannot connect?
Thank you
Steve

 

[craigber]

Did you mean to use SQLSTRINGCONNECT() instead of SQLCONNECT()?

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

Waht is '.\sqlxxxxxxxxx'? And, in particular, what do the initial dot and backslash represent?

For SQLCONNECT() to work, this first paramater should either be a connection object (stored in the active DBC), or a DSN (that you created in the ODBC applet).

If it is a connection object, make sure that the DBC is open and current. If necessary, go into the connection designer and use the Verify Connection button to make sure it is correct.

Similarly, if it is a DSN, use the Verify Connection button in the ODBC applet.

If that doesn't help, add a call to AERROR() immediately after your WAIT. The resulting error array will give you the text of the error message.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

For your SQLEXEC() commands, look into your VFP Help file to see how they should be syntaxed.

Example:

* --- Note that The Query Syntax MUST Be [u]T-SQL Syntax[/u], not necessarily same as VFP ---
cSQLCommand = "SELECT * FROM SQLTable WHERE Custno = '1'"
nRet = SQLEXEC(nConnHandle,cSQLCommand,'ResultCursor')
IF nRet = 1
  * --- Command Executed Correctly ---
  SELECT ResultCursor
  *< do whatever >
ELSE
  * --- T-SQL Command DID NOT execute correctly ---
  *< do whatever >
ENDIF

Good Luck,
JRB-Bldr

 

[Steve-vfp9user]

Hello

My apologies for not replying on this thread sooner.

The data appears to download itself on a local PC and after looking a bit deeper found that it is stored in (local).\sqlxxxxxxxxx

In answer to Mike lewis, as far as I am aware, this is where the data is stored.
In answer to craigber, the example I posted was from the VFP9 help file.

We have also noticed that in the background of the music title software application we use every 30 minutes or so, zip files are downloaded and then appear to be converted into XML files. I'm not exactly sure but I can only go by what we are seeing in the bottom left hand corner. The company who gave us permission to look at this, initially supplied the instructions as per my initial post.

I hope this doesn't sound negative and we are in no way giving up as such, but as new users to VFP9 we are going to look at another way the company gave by using Excel to extract the data. Manually we can do this:

Open Excel
Go to Data
From other Sources
From SQL Server
Type: .\sqlxxxxxxxxx
Next
Select OURTABLE from the list
Select abc.ourdata from the list
Finish

As long as "Compatibility" is off in Excel, you can download unlimited records (instead of the usual 65000 etc)

I appreciate your time and posts.


Thank you

Steve

 

[Mike Lewis]

The data appears to download itself on a local PC and after looking a bit deeper found that it is stored in (local).\sqlxxxxxxxxx

That does not explain why you are passing '.\sqlxxxxxxxxx' as the first parameter to SQLCONNECT(). As I explained in my post, that first parameter should be either the name of a connection object stored in a DBC, or a DSN created in the ODBC applet. From what you say, it is appears to be neither of those, which explains why the code that you posted doesn't work.

If you are not comfortable with creating connection objects or DSNs, we can walk you through the steps; or, we can help with the alternative method of using a connection string.

I appreciate that you are now looking for an alternative approach involving Excel. But it seems a pity that you didn't go just a little further with the solutions we proposed. I still feel that using SQLEXEC() would be the best solution, as it would be contained completely within your VFP app, with no dependencies and no need for any additional componenets.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Hello Mike

Thank you for your post. Well we haven't given up yet! What I need to establish is where exactly the actual data we need is stored. Is it on a local machine or is it on a web server somewhere.

Perhaps I could explain a little more....

The software application we use (supplied by a company) allows us to search over 800,000 music and DVD titles. The company gave us the information and permission allowing us to extract data so we can use the lists to update our own in house software application (ordering system written in VFP9)

There were two ways to do this, one by using Microsoft SQL Management Server and Excel. Both work perfectly but what we were trying to do is automate the process so we don't have to do this manually.

I hope that makes a bit more sense and once I get the additional information I will post back.


Thank you

Steve

 

[Steve-vfp9user]

And just one other thing, I found the link below which describes what I am trying to achieve but I would much prefer it to be automated as it is our intention as previously described to use Windows Scheduler to get regular updates:

http://support.microsoft.com/kb/306125

Thank you

Steve

 

[BillPSU]

I have a program which imports .txt files from a mainframe download directory and imports this data into SQL 2008 tables. The file names are contained in the importing database in a file location table. It lists the file import URL and the import table location. The file sizes generally are 80,000-130,000 kb in size and are imported daily.

This process is done by a 24/7 application which monitors the creation of new import files based on file creation date/time. This program also does updating of files once a day at 9:00AM. This program could also download and schedule updates every hour if necessary. The primary problem is maintaining the network 24/7. The program uses SQL Native Client to connect to the database and complete the import functions using VFP and then runs the updates with stored porcedures on the SQL server and some procedures in the VFP client.

You could automate all the functions you are talking about with a VFP 24/7 application running on a PC.

Bill

 

[Steve-vfp9user]

Hello Bill

Your post is more or less describing what we would like to do. The first thing I have to find out now is where the actual data source is.

We are going to contact the music company tomorrow and ask them for further information.

Appreciate your time.

Thank you

Steve

 

[JRB-Bldr]

Process #1:
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

Process #2:
Open Excel
Go to Data
From other Sources
From SQL Server
Type: .\sqlxxxxxxxxx
Next
Select OURTABLE from the list
Select abc.ourdata from the list
Finish

Obviously the 2 processes are quite different using very different approaches.
But who gave you these processes to use?

Since, by themselves as written, they do not clarify what you are being asked to do - did the music company give them to you without further explanation?

From Process #1, it would appear that the data is coming from a SQL Server data table and somehow you are expected to import the data into a VFP data table (which is what we were advising you about above).

The following is a 'standard' method of opening the MS SQL Server via the Management Studio:

Open Microsoft SQL Management Server 2008
At the prompt:
Server type: Database Engine
Server name: .\sqlxxxxxxxxx
Authentication: Windows Authentication
Click on Connect command button

However the next part is more confusing:

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

because in SQL Server you do not use the USE command to access a data table.
And we have no idea what Pressing F5 will accomplish.

But Process #2 is even more confusing, since Go to Data, From other Sources, From SQL Server are not explained in any way.

Maybe when you get more information from the music company, you can help us understand better thereby enabling us to help you better.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

jrbbldr

USE is a T-SQL command, read the online manuals. It selects a database, it doesn't open a table.
F5 is just a refresh key in the SSMS, like in many apps.

It's still unclear what .\sqlxxxxx is. This can't be a server name, this looks like a path, so it may be a file DNS.

You need the connection credentials, that's your main hurdle. What is .\sqlxxxxx. We don't need to know what the Xess stand for, but in principle, is it a server name or a DNS file name or what?

SQLStringConnect in general is to be preferred, as you can easily put a string with all the connection infos needed inside a configuration INI file or XML or TXT or DBF, whatever you like. For on site admins it might be preferred to use a DSN preconfigured in odbc manager.

So I'd look there. You can't use a file DSN, though, AFAIK.

If it's a DSN name you can use SQLStringConnect with the syntax given in the help example1:
SQLSTRINGCONNECT('dsn=MyFoxSQLNT;uid=myUserID;pwd=myPassword')

I doubt .\ can be the start of a DSN name, though.

Bye, Olaf.

 

[Mike Lewis]

Steve,

In the page you referenced ("How to import data from Microsoft SQL Server into Microsoft Excel"), under the heading "Creating the connection", you will see this code:

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

Whatever string you put in the double quotes in the second line, that string is what you need to pass to SQLSTRINGCONNECT(). The actual string shown in the above code is just an example. You need to get the actual string from someone who knows about your SQL Server database. (Ask him for the "connection string"; it will probably include a user name and password, amongst other things.)

Once you have that, go back to the code you posted above (at 10 Jan 14 16:47). Delete the line that starts "nConn = SQLCONNECT(", and replace it with this:

nConn = SQLSTRINGCONNECT("xxx")

where xxx is the connection string referred to above.

All being well, that should get you past the problem you had with that code. If the code still doesn't work, we can deal with the other problems as we get to them.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Good morning

To answer some questions, the .\sqlxxxxx is actually .\sqlexpress

I have emailed the music company this morning so I'll reply back when I have heard from them. I appreciate your efforts all.

Thank you

Steve

 

[Olaf Doschke]

Ah, I see, then that's the instance SQLEXPRESS on the local computer "." It's always \SQLEXPRESS for an express server, that's no secret you need to hide.

But the server is just one part of the connection string you need.

Connection Strings for any database can be found at

http://www.connectionstrings.com

You take the connectionstring to a normal SQL Server instance and simply add \SQLEXPRESS to the server name for an Express version of the connection string.

Eg lcConnstring = "Driver={SQL Server Native Client 11.0};Server=.\SQLEXPRESS;Database=OURTABLE;Trusted_Connection=yes;"
Use that in m.lnConn = SQLStringConnect(lcConnstring) and then you can SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata')

That also depends on the drivers present and the one you want to use. It has to be a 32bit ODBC driver.

Bye, Olaf.