Best way to create a new SQL Server database

jeremyw · Post by **jeremyw** » Wed 16 Mar 2011 17:46

I'm totally new at developing with MS SQL Server, so forgive my ignorance in advance. I searched the forum for an answer but didn't find anything, so hopefully there is a quick answer for me.

I have our code connecting successfully to SQL Servers and opening existing databases, but when I try to create a new database I get the following error:

I figured the easiest way to create a new database was simply by invoking the SQL command "CREATE DATABASE [databasename]", but apparently I have to have another database already selected before calling this code. Why is that, and is there any way around this limitation?

When attempting to create a new database, I simply connect to a server with the TMSConnection::Database property left blank. Based on the SDAC docs, I thought the server still connected to whatever default database is set up for the user (perhaps the "master" database)? If that's true, then why am I getting this error?

Most importantly, is there a better way to create new databases via the SDAC components?

AndreyZ · Post by **AndreyZ** » Thu 17 Mar 2011 09:02

Hello,

You can create a database using one of the following approaches:
1) with the TMSConnection component:

Code: Select all

MSConnection.ExecSQL('CREATE DATABASE TEST', []);

2) with the TMSSQL component:

Code: Select all

MSSQL.Connection := MSConnection;
MSSQL.SQL.Text := 'CREATE DATABASE TEST';
MSSQL.Execute;

And you are right, you don't need to set the TMSConnection.Database property to create a new database.

jeremyw · Post by **jeremyw** » Fri 18 Mar 2011 19:20

Thanks for the quick reply. I should have looked a bit closer at my code before posting this topic. The error I was getting is spawned by my own error-checking code which normally makes sure a database is opened before allowing SQL commands to be issued. Initially I thought it was something being generated by SQL Server or SDAC.

Anyway, your clarification is still helpful. Thanks again!

AndreyZ · Post by **AndreyZ** » Mon 21 Mar 2011 08:09

It is good to see that this problem was solved. Feel free to contact us if you have any further questions about SDAC.