>Is it possible to create a database within MSSQL-server from a VFP-client by for example using SQLEXEC()-function. I have tried the following with no success:
>gcMydatabaseName="MyName"
>lcMap="\\myServer\mySQLDataFiles"
>lcCreateDatabaseString1="CREATE DATABASE "+gcMydatabaseName+" ON PRIMARY (NAME = "+gcMydatabaseName+"_Data,"+;
> "FILENAME = "+lcMap+gcMydatabaseName+"_Data.mdf',"+;
> "SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=10MB)"
>lcCreateDatabaseString2= " LOG ON (NAME = "+gcMydatabaseName+;
>"_Log,FILENAME = "+lcMap+gcMydatabaseName+"_Log.ldf',"+;
> "SIZE=5MB,MAXSIZE=15MB,FILEGROWTH=1MB)"
>lnTest1=SQLEXEC(gnConSQL,lcCreateDatabaseString1)
>? lnTest1 returns -1
>lnTest2=SQLEXEC(gnConSQL,lcCreateDatabaseString2)
>? lnTest2 returns -1
>gnConSQL handle is created by connecting to the SQL-server master database and also tested by connecting to the SQL-server without any database specification.
You could create provided you have right to do so. Check aerror() why it fails. Also use text..endtext for easier formatting of commands. ie:
Local lnHandle, lcDatabaseName,m.lcSQLDbCreate,m.lcSQLCreateTables
lcDatabaseName = "myTestData"
Text to m.lcSQLDbCreate textmerge noshow
IF EXISTS (SELECT name FROM master.dbo.sysdatabases
WHERE name = N'<<m.lcDataBaseName>>')
DROP DATABASE [<<m.lcDataBaseName>>]
Create Database [<<m.lcDataBaseName>>]
EndText
Text to m.lcSQLCreateTables textmerge noshow
USE [<<m.lcDataBaseName>>]
CREATE TABLE [dbo].[myTable1] (
[myF1] [int] NOT NULL ,
[myDate] [datetime] NOT NULL )
CREATE TABLE [dbo].[myTable2] (
[myF1] [varchar] (3) NOT NULL ,
[myF2] [int] Not null,
[myDate] [datetime] NOT NULL )
EndText
lnHandle=SQLStringConnect('DRIVER=SQL Server;'+;
'SERVER=servername;Trusted_connection=Yes')
SQLExec(m.lnHandle, m.lcSQLDbCreate)
SQLExec(m.lnHandle, m.lcSQLCreateTables)
SQLDisconnect(m.lnHandle)
Cetin