SQL Command works in SSMS, not through C# SqlCommand

Question

If I run the following in SSMS, the table gets created if it does not exist. However, in my SQLCommand code, it connects and sends the query through with no error but does not create the table if it doesn't exist. Any ideas?

string[] tables = new string[6];
    tables[0] += ("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AD_Authorization]') AND type in (N'U')) " + 
                  "CREATE TABLE [AD_Authorization]([ID] [bigint] IDENTITY(1,1) NOT NULL, " + 
                  "  [AD_Account] [varchar](255) NOT NULL, " + 
                  "  [AD_SID] [varchar](255) NOT NULL, " + 
                  "  [AD_EmailAddress] [varchar](255) NULL, " + 
                  "  [DateImported] [datetime] NOT NULL, " + 
                  "  [Active] [bit] NULL) ON [PRIMARY]");

 for (int i = 0; i <= 5; i++)
                        {
                            using (SqlConnection connection = new SqlConnection(ConnectionString))
                            {
                                using (SqlCommand cmd = new SqlCommand(tables[i], connection))
                                {
                                    connection.Open();
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery();
                                    connection.Close();
                                }
                            }
                        }

could it be down to the user you are using to run this via Code differnt to the user you log-in as in SSMS? if they do differ try connecting as the same user you use within your project and then see... — Squirrel5853
– Squirrel5853, Commented Dec 29, 2011 at 16:41
Also: I would always use the more precise and focused sys.tables rather than sys.objects (and having to specify what type of object you're interested in) - IF NOT EXISTS (SELECT * FROM sys.tables WHERE Name = 'AD_Authorization') .... - but I doubt this'll make any difference — marc_s
– marc_s, Commented Dec 29, 2011 at 16:44
There's 6 queries total just like this one. I didnt include them so not to be verbose, they are all having the same issue. — Dan C.
– Dan C., Commented Dec 29, 2011 at 17:01
I didn't see the indexer for tables. Thanks. In your connection string, do you specify a default database for the connection by chance? — Dracorat
– Dracorat, Commented Dec 29, 2011 at 17:09

Dracorat · Accepted Answer · 2011-12-29 19:39:51Z

1

Make sure that when you make the connection to the database, that you specify the correct database or that you ChangeDatabase the connection first. Otherwise, your objects will end up in the MASTER database.

answered Dec 29, 2011 at 19:39

Dracorat

1,1947 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user596075 · Accepted Answer · 2011-12-29 16:39:16Z

Try this:

SqlConnection DbConn = new SqlConnection(YourConnectionStringHere);
SqlCommand CreateTable = new SqlCommand();
CreateTable.Connection = DbConn;
CreateTable.CommandText = "IF NOT EXISTS 
    (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AD_Authorization]') 
    AND type in (N'U')) 
        CREATE TABLE [AD_Authorization]
        (
            [ID] [bigint] IDENTITY(1,1) NOT NULL,
            [AD_Account] [varchar](255) NOT NULL,
            [AD_SID] [varchar](255) NOT NULL,
            [AD_EmailAddress] [varchar](255) NULL,
            [DateImported] [datetime] NOT NULL,[Active] [bit] NULL
        ) 
        ON [PRIMARY]";

try
{
    DbConn.Open();
    CreateTable.ExecuteNonQuery();
}
catch (Exception ex)
{
    DbConn.Dispose();
    // Handle your error
}

user3224240 · Accepted Answer · 2014-01-22 15:52:52Z

0

You can't use sys.objects via SQLCommand. Access is specifically blocked for security purposes in SQL Server. I'm not sure if this is a default setting, or something that cannot be overwritten.

answered Jan 22, 2014 at 15:52

user3224240

1

Collectives™ on Stack Overflow

SQL Command works in SSMS, not through C# SqlCommand

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related