>Hello,
>
>Im a newbie in MS SQL Server and I would like some ideas on the best way to proceed with an installation of databases and tables on a MS SQL Server through VFP.
>
>- Where can I download the odbc driver for MS SQL ?
Install MSDE which comes with VFP, and you'll have the driver. I never bothered to look for any specific install, since it was always there. I really don't know whether this driver is a regular part of a Windows install - never had a case that it wasn't there when I needed it, but then it was always in networks someone already set up before I came in.
>- Is it possible to configure an odbc driver with VFP ?
There must be a few dozen threads about this, I hope someone will chime in on this.
>Once configured, I would like with VFP through the MS SQL ODBC driver to :
>- Create databases on SQL Server
>- in theses databases, create tables
>All these things during a setup process, so as automatic as possible.
>If you have some code samples, it would be great !
The script to run serverside can be generated in Enterprise Manager (once you have the database stable and ready to be populated). To run that script, you only need to establish a connection to SQL server. You only need to cut the script into reasonable chunks, since there's a limit of 8K of text that you can push to SQL from VFP at once. Last time I did this, I used a script generated by the upsizing wizard, and chopped it to start each chunk with a comment, and end with a blank line (as these were regularly inserted by the wizard in proper places).
Anyway, the code would look something like this:
h=sqlstringconnect("<connect string here - build it>")
for i=1 to nChunks
nRet=sqlexec(h, aChunks[i], "")
if nret<1
aerror(eErr)
messagebox("Error "+eErr(2))
endif
endfor
sqldisconnect(h)
To build a connect string, create a file DSN, save it and then open in fox via Modi Comm the.dsn. It's a simple textfile - just remove the line breaks and paste it (if there aren't semicolons at end-of-lines, add them).
Here I assumed you had pieces of the script in an array - they may be in memos, in a textfile, whatever. This is just metacode to give you some idea.