>Hi,
>
>I've got some code that gets the available SQL servers and now I want to allow the user to create a database on a selected server. So I've got the name of the new database and some code that connects to the server. How do I now create the database? I tried:
>
>

>TEXT TO m.lcSQL NOSHOW TEXTMERGE
>USE [master]
>GO
>CREATE DATABASE [<<m.lcNewDatabase>>] ON  PRIMARY
>( NAME = N'<<m.lcNewDatabase>>', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<m.lcNewDatabase>>.mdf' , SIZE = 603456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
>( NAME = N'<<m.lcNewDatabase>>_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<m.lcNewDatabase>>_log.ldf' , SIZE = 1219712KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
>GO
>EXEC dbo.sp_dbcmptlevel @dbname=N'<<m.lcNewDatabase>>', @new_cmptlevel=90
>GO
>IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [<<m.lcNewDatabase>>].[dbo].[sp_fulltext_database] @action = 'enable'end
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ANSI_NULL_DEFAULT OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ANSI_NULLS OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ANSI_PADDING OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ANSI_WARNINGS OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ARITHABORT OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET AUTO_CLOSE OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET AUTO_CREATE_STATISTICS ON
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET AUTO_SHRINK OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET AUTO_UPDATE_STATISTICS ON
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET CURSOR_CLOSE_ON_COMMIT OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET CURSOR_DEFAULT  GLOBAL
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET CONCAT_NULL_YIELDS_NULL OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET NUMERIC_ROUNDABORT OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET QUOTED_IDENTIFIER OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET RECURSIVE_TRIGGERS OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET  DISABLE_BROKER
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET DATE_CORRELATION_OPTIMIZATION OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET TRUSTWORTHY OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET ALLOW_SNAPSHOT_ISOLATION OFF
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET PARAMETERIZATION SIMPLE
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET  READ_WRITE
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET RECOVERY FULL
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET  MULTI_USER
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET PAGE_VERIFY CHECKSUM
>GO
>ALTER DATABASE [<<m.lcNewDatabase>>] SET DB_CHAINING OFF
>ENDTEXT
>llSuccess = SQLEXEC(m.lnHandle,m.lcSQL)>0
>WAIT WINDOW TRANSFORM(m.llSuccess)
>

>
>which is basically copied from the CREATE script for the database in SQL Server Management Studio.
>
>Obviously there are some problems in that I have the hard coded path to the files. Is there a way that I can find out where the default path for files are on a particular server?
>
>However, I am getting llSuccess to be .F. all the time. Am I trying to do too much? Or is this possible? Anybody willing to show me an example of what they have done?
>
>Thanks,

Frank,
If you don't include the location it uses default location. If you need the location to create multiple file/fielgfroups and/or specify sizes then you might use physical_name from sys.database_files or sys.master_files view. ie:

lcMyDatabaseName = 'VFPWasHere'
Text to m.lcCreateDB textmerge noshow
USE [master]

IF DB_ID (N'<<m.lcMyDatabaseName>>') IS NOT NULL
  DROP DATABASE <<m.lcMyDatabaseName>>

-- Get the SQL Server data path
DECLARE @data_path nvarchar(256)
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
	    FROM master.sys.master_files
	    WHERE database_id = 1 AND file_id = 1)

Execute ('Create Database <<m.lcMyDatabaseName>>
  ON PRIMARY 
  (NAME = <<m.lcMyDatabaseName>>_Data,
  FILENAME = ''' + @data_path + '<<m.lcMyDatabaseName>>_Data.mdf'',
  SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=10MB)' )
EndText

lnHandle = SQLSTRINGCONNECT("Driver={SQL Native Client};Server=.\sqlexpress;Trusted_Connection=yes;")

SQLExec(m.lnHandle,m.lcCreateDB)
SQLDisconnect(m.lnHandle)

Above syntax is partially from Create database help on books online.

Is this possible? Yes it's possible. I've run much longer scripts but that might need that you edit the script for VFP. Instead you might utilize SQLCMD. ie:

Run sqlcmd -E -S .\sqlexpress "c:\myPath\MySQLScript.sql"

Cetin