update database through SQL script

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Leanne
    #1

    update database through SQL script

    I am doing customization for Microsoft Pos. I am working on
    installation package now. I added some new tables and stored
    procuedures and generated SQL Script for that. During the installation
    process, after user entered the server name and database name, how to
    open the script file (*.sql) and run that to update database? So they
    don't have to open Query Analizer to run it. The idea is to get
    everything done during the installation process. Anyone got an idea?

    Thanks in advance.

    Leanne

    Tags: None
    • Cor Ligthert [MVP]
      #2
      Re: update database through SQL script

      Leanne,

      You can run every script using the SQLCommand.exec utenonquery, therefore as
      well Create and Alter statements.

      http://msdn2.microsoft.com/en-us/lib...enonquery.aspx

      I hope this helps,

      Cor


      "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
      news:1163042560 .296016.144580@ f16g2000cwb.goo glegroups.com.. .
      >I am doing customization for Microsoft Pos. I am working on
      installation package now. I added some new tables and stored
      procuedures and generated SQL Script for that. During the installation
      process, after user entered the server name and database name, how to
      open the script file (*.sql) and run that to update database? So they
      don't have to open Query Analizer to run it. The idea is to get
      everything done during the installation process. Anyone got an idea?
      >
      Thanks in advance.
      >
      Leanne
      >

        Comment

        • Leanne
          #3
          Re: update database through SQL script

          Cor,
          Thank you for your reply. My problem is, the generated SQL script
          contains a lot " if exist...then drop...Go..." etc, so when I use

          ls=My.Computer. FileSystem.Read AllText(Dialog1 .FileName)
          sqlCommand.Comm andText = ls
          sqlCommand.Exec uteNonQuery()


          I got many syntex error. (such as "Go" should be the last). I set the
          command type to "text". I don't know what method I should use to read
          string from the file and how should I tell sqlCommand that this is SQL
          Script and not normal SQL Statement.

          Thanks again
          Leanne



          Cor Ligthert [MVP] wrote:
          Leanne,
          >
          You can run every script using the SQLCommand.exec utenonquery, therefore as
          well Create and Alter statements.
          >
          http://msdn2.microsoft.com/en-us/lib...enonquery.aspx
          >
          I hope this helps,
          >
          Cor
          >
          >
          "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
          news:1163042560 .296016.144580@ f16g2000cwb.goo glegroups.com.. .
          I am doing customization for Microsoft Pos. I am working on
          installation package now. I added some new tables and stored
          procuedures and generated SQL Script for that. During the installation
          process, after user entered the server name and database name, how to
          open the script file (*.sql) and run that to update database? So they
          don't have to open Query Analizer to run it. The idea is to get
          everything done during the installation process. Anyone got an idea?

          Thanks in advance.

          Leanne

            Comment

            • Cor Ligthert [MVP]
              #4
              Re: update database through SQL script

              Leanne,

              Did you look at the code in the Query analyzer from the SQL management tool,
              if you start with a new one, than that starts with statements to let it
              execute to be imported in the program libraries of SQL server.

              Cor

              "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
              news:1163051102 .106368.43250@h 48g2000cwc.goog legroups.com...
              Cor,
              Thank you for your reply. My problem is, the generated SQL script
              contains a lot " if exist...then drop...Go..." etc, so when I use
              >
              ls=My.Computer. FileSystem.Read AllText(Dialog1 .FileName)
              sqlCommand.Comm andText = ls
              sqlCommand.Exec uteNonQuery()
              >
              >
              I got many syntex error. (such as "Go" should be the last). I set the
              command type to "text". I don't know what method I should use to read
              string from the file and how should I tell sqlCommand that this is SQL
              Script and not normal SQL Statement.
              >
              Thanks again
              Leanne
              >
              >
              >
              Cor Ligthert [MVP] wrote:
              >Leanne,
              >>
              >You can run every script using the SQLCommand.exec utenonquery, therefore
              >as
              >well Create and Alter statements.
              >>
              >http://msdn2.microsoft.com/en-us/lib...enonquery.aspx
              >>
              >I hope this helps,
              >>
              >Cor
              >>
              >>
              >"Leanne" <leannewu@conne ctretail.com.au schreef in bericht
              >news:116304256 0.296016.144580 @f16g2000cwb.go oglegroups.com. ..
              >I am doing customization for Microsoft Pos. I am working on
              installation package now. I added some new tables and stored
              procuedures and generated SQL Script for that. During the installation
              process, after user entered the server name and database name, how to
              open the script file (*.sql) and run that to update database? So they
              don't have to open Query Analizer to run it. The idea is to get
              everything done during the installation process. Anyone got an idea?
              >
              Thanks in advance.
              >
              Leanne
              >
              >

                Comment

                • Leanne
                  #5
                  Re: update database through SQL script

                  Cor,

                  If I start Query analyzer, then open the sql file, I can run that
                  without any problem.
                  Now what I want is to open the sql file from VB, and then execute that
                  throuth sqlCommand. Can you tell me how to do that? Some code sample
                  maybe?

                  Thanks
                  Leanne

                  Cor Ligthert [MVP] wrote:
                  Leanne,
                  >
                  Did you look at the code in the Query analyzer from the SQL management tool,
                  if you start with a new one, than that starts with statements to let it
                  execute to be imported in the program libraries of SQL server.
                  >
                  Cor
                  >
                  "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
                  news:1163051102 .106368.43250@h 48g2000cwc.goog legroups.com...
                  Cor,
                  Thank you for your reply. My problem is, the generated SQL script
                  contains a lot " if exist...then drop...Go..." etc, so when I use

                  ls=My.Computer. FileSystem.Read AllText(Dialog1 .FileName)
                  sqlCommand.Comm andText = ls
                  sqlCommand.Exec uteNonQuery()


                  I got many syntex error. (such as "Go" should be the last). I set the
                  command type to "text". I don't know what method I should use to read
                  string from the file and how should I tell sqlCommand that this is SQL
                  Script and not normal SQL Statement.

                  Thanks again
                  Leanne



                  Cor Ligthert [MVP] wrote:
                  Leanne,
                  >
                  You can run every script using the SQLCommand.exec utenonquery, therefore
                  as
                  well Create and Alter statements.
                  >
                  http://msdn2.microsoft.com/en-us/lib...enonquery.aspx
                  >
                  I hope this helps,
                  >
                  Cor
                  >
                  >
                  "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
                  news:1163042560 .296016.144580@ f16g2000cwb.goo glegroups.com.. .
                  I am doing customization for Microsoft Pos. I am working on
                  installation package now. I added some new tables and stored
                  procuedures and generated SQL Script for that. During the installation
                  process, after user entered the server name and database name, how to
                  open the script file (*.sql) and run that to update database? So they
                  don't have to open Query Analizer to run it. The idea is to get
                  everything done during the installation process. Anyone got an idea?

                  Thanks in advance.

                  Leanne

                    Comment

                    • Cor Ligthert [MVP]
                      #6
                      Re: update database through SQL script

                      Not tested,

                      cmd.CommandText = _
                      "CREATE PROCEDURE dbo.whatever AS & vbCrLf & _
                      SELECT * FROM MyTable"
                      cmd.ExecuteNonQ uery()

                      If it is already in your database than CREATE is ALTER

                      I hope this helps,

                      Cor

                      "Leanne" <leannewu@conne ctretail.com.au schreef in bericht
                      news:1163053854 .124188.261540@ i42g2000cwa.goo glegroups.com.. .
                      Cor,
                      >
                      If I start Query analyzer, then open the sql file, I can run that
                      without any problem.
                      Now what I want is to open the sql file from VB, and then execute that
                      throuth sqlCommand. Can you tell me how to do that? Some code sample
                      maybe?
                      >
                      Thanks
                      Leanne
                      >
                      Cor Ligthert [MVP] wrote:
                      >Leanne,
                      >>
                      >Did you look at the code in the Query analyzer from the SQL management
                      >tool,
                      >if you start with a new one, than that starts with statements to let it
                      >execute to be imported in the program libraries of SQL server.
                      >>
                      >Cor
                      >>
                      >"Leanne" <leannewu@conne ctretail.com.au schreef in bericht
                      >news:116305110 2.106368.43250@ h48g2000cwc.goo glegroups.com.. .
                      Cor,
                      Thank you for your reply. My problem is, the generated SQL script
                      contains a lot " if exist...then drop...Go..." etc, so when I use
                      >
                      ls=My.Computer. FileSystem.Read AllText(Dialog1 .FileName)
                      sqlCommand.Comm andText = ls
                      sqlCommand.Exec uteNonQuery()
                      >
                      >
                      I got many syntex error. (such as "Go" should be the last). I set the
                      command type to "text". I don't know what method I should use to read
                      string from the file and how should I tell sqlCommand that this is SQL
                      Script and not normal SQL Statement.
                      >
                      Thanks again
                      Leanne
                      >
                      >
                      >
                      Cor Ligthert [MVP] wrote:
                      >Leanne,
                      >>
                      >You can run every script using the SQLCommand.exec utenonquery,
                      >therefore
                      >as
                      >well Create and Alter statements.
                      >>
                      >http://msdn2.microsoft.com/en-us/lib...enonquery.aspx
                      >>
                      >I hope this helps,
                      >>
                      >Cor
                      >>
                      >>
                      >"Leanne" <leannewu@conne ctretail.com.au schreef in bericht
                      >news:116304256 0.296016.144580 @f16g2000cwb.go oglegroups.com. ..
                      >I am doing customization for Microsoft Pos. I am working on
                      installation package now. I added some new tables and stored
                      procuedures and generated SQL Script for that. During the
                      installation
                      process, after user entered the server name and database name, how
                      to
                      open the script file (*.sql) and run that to update database? So
                      they
                      don't have to open Query Analizer to run it. The idea is to get
                      everything done during the installation process. Anyone got an idea?
                      >
                      Thanks in advance.
                      >
                      Leanne
                      >
                      >
                      >

                        Comment

                        Previous template Next
                        Working...