none
unable to run sql statement RRS feed

  • Question

  • Hi guys,

     

    I have the following sql script:

     

    IF EXISTS( SELECT * FROM sys.databases WHERE name = 'mydummydb')
    BEGIN
    DROP DATABASE mydummydb
    END
    GO
     USE MASTER
    CREATE DATABASE mydummydb 
    GO
     USE mydummydb
    GRANT ALL TO PUBLIC
    GO
    
    

     


    It runs well in Query Analizer, however when I try to run it as an ADO.Net statement, I get the error message below:

    Incorrect syntax near the keyword 'USE'.
    Incorrect syntax near 'GO'.
    Could not locate entry in sysdatabases for database 'mydummydb'. No entry found with that name. Make sure that the name is entered correctly.

     

    Here's the statement as submitted to ADO.NET:

     

                                                   "IF EXISTS( SELECT * FROM sys.databases WHERE name = 'mydummydb') \n" +
                                                    "BEGIN \n" +
                                                        "DROP DATABASE mydummydb \n" +
                                                    "END " +
                                                    "\nGO\n " +
                                                    "USE MASTER \n" +
                                                    "CREATE DATABASE mydummydb  " +
                                                    "\nGO\n " +
                                                    "USE mydummydb \n" +
                                                    "GRANT ALL TO PUBLIC " +
                                                    "\nGO\n ";

     


    Thanks for any help
    Thursday, July 7, 2011 2:22 AM

Answers

  • After further test, this is the real problem:

    "USE mydummydb;"

    I just created it so I must have CONNECT permission on it right?


    Thank you for posting.

    It looks that your question isn't very more than ADO.NET data provider, but than some SQL. I copied your sql script and run it on my side. Everything looks good until GRANT statement and I got the following information.

    The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.

    I'm not very sure what SQL version you used, but I suggest you can check this following article about Behavior Changes to Database Engine Features in SQL Server 2008. http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx In this document it suggest we can consider using CONTROL permission to grant ownership-like permissions on an object.

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 8, 2011 9:17 AM

All replies

  • how do you execute this query? sqlcommand? could you paste your code of executing?
    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Thursday, July 7, 2011 7:06 AM
  • The reason for that is simple - GO command is not a part of Transact SQL syntax but command for Query Analyzer or other tools to start batch of statements. It will not be recognized by providers. To make it work, you need to remove all GO commands and separate statements with the semicolon or execute statements one-by-one. Your statement would look something like

    "IF EXISTS( SELECT * FROM sys.databases WHERE name = 'mydummydb') \n" +
    "BEGIN \n" +
    "DROP DATABASE mydummydb \n" +
    "END;" +
    "USE MASTER;" +
    "CREATE DATABASE mydummydb;" +
    "USE mydummydb;" +
    "GRANT ALL TO PUBLIC";

     


    Val Mazur (MVP)

    http://www.xporttools.net

    Thursday, July 7, 2011 10:28 AM
    Moderator
  • Thanks for the replies guys. I made the changes VMazur. The first two errors are gone, I'm now left with this one:

    Could not locate entry in sysdatabases for database 'mydummydb'.

    No entry found with that name. Make sure that the name is entered correctly.

     

    Here's the calling c# code Durna:

     

     

    private void ExecuteNonQuery(Context context, String sqlCommand)
    {
    SqlConnection myConn = new SqlConnection(this._connectionString );
    SqlCommand myCommand = new SqlCommand(sqlCommand, myConn);
    try
    {
    myConn.Open();
    if (context == Context.DatabaseCreateOrDrop)
    myConn.ChangeDatabase("master");
    myCommand.ExecuteNonQuery();

    }
    catch (System.Exception ex)
    {
    //TODO: Log error message
    throw;
    }
    finally
    {
    if (myConn.State == ConnectionState.Open)
    {
    myConn.Close();
    }
    }
    }



    The connectionString is:


    Data Source=***;Initial Catalog=testdb;User ID=sa;Password=**;Pooling=False

    After doing a few test. I have determined that it's the last portion of the script that causes the issue:

    "USE mydummydb;" +
    "GRANT ALL TO PUBLIC";

    If I remove it, the database gets created.

     

    Thanks for your help.


    Thursday, July 7, 2011 12:48 PM
  • After further test, this is the real problem:

    "USE mydummydb;"

    I just created it so I must have CONNECT permission on it right?

    Thursday, July 7, 2011 1:34 PM
  • After further test, this is the real problem:

    "USE mydummydb;"

    I just created it so I must have CONNECT permission on it right?


    Thank you for posting.

    It looks that your question isn't very more than ADO.NET data provider, but than some SQL. I copied your sql script and run it on my side. Everything looks good until GRANT statement and I got the following information.

    The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.

    I'm not very sure what SQL version you used, but I suggest you can check this following article about Behavior Changes to Database Engine Features in SQL Server 2008. http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx In this document it suggest we can consider using CONTROL permission to grant ownership-like permissions on an object.

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 8, 2011 9:17 AM
  • Yes, it could be related to permission.


    Val Mazur (MVP)

    http://www.xporttools.net

    Friday, July 8, 2011 10:25 AM
    Moderator