locked
Executing DDL Script RRS feed

  • Question

  • User1649146661 posted

    I am using the Microsoft.ApplicationBlocks.Data to access my SQL 2000 database.

    Can I execute DDL script through the Sql client? 
    Visio generates the script.  If I paste it in the Query Analyzer everything works fine.  It creates the database, the tables, and everything works great.

    I want to execute this through my asp.net app.
    The first lines of my DDL are:

    use master
    go
    create database "PP_Chris"
    go
    use "PP_Chris"
    go
    create table bla bla....

    This entire script is in a string variable and passed through the SqlHelper.ExecuteNonQuery()
    Now I get the errors:

    Line 2: Incorrect syntax near 'go'
    Line 4: Incorrect syntax near 'go'
    Could not locate entry in sysdatabases for database 'PP_Chris'

    I am having a difficult time trouble shooting this because it works perfectly fine in Query Analyzer.

    Any suggestions or ideas?

    Friday, December 30, 2005 5:17 PM

All replies

  • User1649146661 posted

    Figured it out.
    Apprently I am the only one that didn't know 'GO' wasn't TSQL, the Query Analyzer just uses it to seperate commands....when I read that I did the following (incase anyone else runs into the same situation):

    string newScript = QueryAnalyzerScript.Replace("\r\n\GO",";");
    string[] cmds = newScript.Split(';');

    SqlCommand sCmd;
    SqlConnection sCon = new SqlConnection(connectionString);
    sCon.Open();
    try{
    foreach(string cmd in cmds){
    sCmd = new SqlCommand(cmd.Trim(), sCon);
    sCmd.ExecuteNonQuery();}
    } finally {sCon.Close(); sCon.Dispose();}

    It worked perfectly....so for I've been able to run all the scripts that I have generated from SQL Enterprize Manger.

    I know...why am I running the script if they are alreay in SQL, but this administration app needs to create databases for different clients, so replace a couple of values before executing the script....whatever...

    Friday, December 30, 2005 10:48 PM