How do I create a new Database and new tables in SQL MSDE 2000 programmatically with C#.net?

Question

I am writing a program that will create a new datatbase, then add tables to that datatbase. Here is my code....

InsertTable("Create Database iBlast", "Null");
InsertTable("Create Table iBlast.tblBoreHoles (HoleID uniqueIdentifier, HoleName nvarchar(40), JobID uniqueidentifier, CreateDate datetime, Longitude float, Latitude float, Altitude float, HoleDia real, ExpectedDepth float)", "iBlast");


static void InsertTable(String sqlQuery, string InitialCatalog)
{
    SqlConnection sqlConn = new SqlConnection();
    //sqlConn.ConnectionString = "Data Source=VIRTUAL2KB;Initial Catalog=PCS6000SQL;User ID=sa;Password=password;Integrated Security=False";
    if (InitialCatalog == "Null")
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Integrated Security=True";
    }
    else
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Initial Catalog=" + InitialCatalog + ";Integrated Security=True";
    }   
    sqlConn.Open();
    SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConn);
    sqlCommand.ExecuteNonQuery();
}

The database creation works fine but I get an error when the code trys to create the table.

Error = "The specified schema name "iBlast" either does not exist or you do not have permission to use it."

Any help would be appreciated.

The program is running on a PLC that only runs Windows 2000. — MagnumPI, Jan 18 '12 at 15:38

score 3 · Accepted Answer · answered Jan 18 '12 at 15:35

3

You are specifying "iBlast" as the initial catalog, so you don't need to specify it in the query:

 InsertTable("Create Table tblBoreHoles (...)", "iBlast");

If you did need to specify it, the syntax would be iBlast..tblBoreHoles or iBlast.dbo.tblBoreHoles.

answered Jan 18 '12 at 15:35

Blorgbeard

101,031
48
228
272

I was leaving it out originally but was getting the same error. That is why I added it. That and because the function will add databases as well as tables. However, the iBlast.dbo.tblBoreHoles... worked. Thanks, I feel stupid that I didn't realize this myself! – MagnumPI Jan 18 '12 at 15:45

score 2 · Answer 2 · answered Jan 18 '12 at 15:35

2

In your case you're not specifying the database with iBlast on the second line, you're specifying the schema of the current database. Should be something like [database].[schema].[table]

answered Jan 18 '12 at 15:35

linkerro

5,318
3
25
29

I cannot mark two answers as the correct one, but this was also the correct answer...thank you – MagnumPI Jan 18 '12 at 15:48

score 1 · Answer 3 · answered Jan 18 '12 at 15:34

1

you need to do iblast.dbo.tblBoreHoles or iBlast..tblBoreHoles. In SQL's naming convention, the part right before the table name is always the schema, which it doesn't appear you're trying to reference with "iBlast"

answered Jan 18 '12 at 15:34

CD Jorgensen

1,361
9
8

I cannot mark two answers as the correct one, but this was also the correct answer...thank you. – MagnumPI Jan 18 '12 at 15:47

score 0 · Answer 4 · answered Jan 18 '12 at 15:37

Try this:

    else
    {
        sqlConn.ConnectionString = "Data Source=VEEMER11;Initial Catalog=" + InitialCatalog + ";Integrated Security=SSPI";
    }

Changing to SSPI may give you the permission you need. Check in your server that your windows user has full DBO privileges.

also, change your insert to this:

InsertTable("Create Table tblBoreHoles (HoleID uniqueIdentifier, HoleName nvarchar(40), JobID uniqueidentifier, CreateDate datetime, Longitude float, Latitude float, Altitude float, HoleDia real, ExpectedDepth float)", "iBlast");

How do I create a new Database and new tables in SQL MSDE 2000 programmatically with C#.net?

4 Answers4