create sql server database from vfp

[jennyflower]

Hi, I have installed MS SQL Express and have set up the sa user but I cannot get the user to be able to create a database in SQL Server from VFP. I can create the database within SQL Server management tools however. The code I am using is:

lnconn = SQLSTRINGCONNECT("dsn=mseurospec;UID=sa;PWD=password;")

IF lnconn < 0 THEN
MESSAGEBOX("Could not Connect to MSSQL Database on localhost", 0, "WARNING!")
ELSE

lnresult = SQLEXEC(lnconn, "CREATE DATABASE eurospec")

IF lnresult < 0 THEN
MESSAGEBOX("Could not create database 'eurospec' on localhost")
ENDIF

ENDIF

SQLDISCONNECT(0)

I can connect OK but then I just a message saying I couldn't create the database.

Thanks

 

[Mike Lewis]

Jenny,

Modify the following code:

IF lnresult < 0 THEN
  MESSAGEBOX("Could not create database 'eurospec' 
  on localhost")
ENDIF

as follows:

IF lnresult < 0 
  [b]AERROR(laErr)[/b]
  MESSAGEBOX("Could not create database 'eurospec' 
  on localhost" [b]+ chr(13) + ;
  laErr(3)[/b])
ENDIF

That way, your Messagebox will include an informative error message from the server, which should tell you what went wrong.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[jennyflower]

The error message I got was:

[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction.

Thanks

 

[mkrausnick]

I have SQL 2005, not SQL Express, but your code worked for me. Have a look at the Remote Data tab in the Options dialog. I have Batch Processing and Automatic Transactions checked.

Good Luck.

Mike Krausnick
Dublin, California