Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: importing sql procedures into sql server

    Hi

    I need to write a script to import stored procedures into database.

    If anyone can give me any help on this it would be great.

    I have tried searchng but have had nio luck so far.

    Cheers

    I tried xp_cmdshell but it doesnt work.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    There are a bunch of ways to skin this cat. I have Redgate SQL Compare and I never do database development without it. Both it and Visual Studio GDR let you synchronize objects pretty easily.

    However those tools cost money. You can always right click on your database and look for the "Generate Scripts" option and have Query Analyzer or Management studio create a script for you.

    If you are in a sane development environment, all of your stored prcs should be in source control and then you could use osql or sqlcmd to apply those files to another database.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree wholeheartedly, but just how many "sane" development environments have you seen in your life?

    GuestUser31, please explain what you have now and what you want to get. Your question doesn't make sense to me. Where did your script come from? Can you play the script using either SSMS (SQL Server Management Studio) or SQLCMD.EXE? What problem(s) are you having?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2009
    Posts
    13
    Hi guys

    Thanks for the replies.

    The scenario is that we have 3 stored procedures sitting in a folder on a SQL Server.

    Now we need to write a sql script which when run in SQL Management Studio it takes those stored procedures and loads them into a database.

    I tried the following:

    exec master.dbo.xp_cmdshell 'osql -E -SERVERNAME -d DATABASE10 -i ..\CP_PROCEDURE.prc'

    but it shows the error message 'cannot generate sspi context'

    Hopefully this makes things a bit clearer!

    Thanks

  5. #5
    Join Date
    Aug 2009
    Posts
    13
    I have managed to get rid of the error message stated above.

    however now the script can't find the file.

    If I give the full file path it works fine but if I shorten the path it doesnt work.

    I have used ../Filename which I thought was correct.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From SSMS:
    1) Connect to the target SQL server
    2) Cntrl-N to open a new query window
    3) Cntrl-O to open a file in the window. Navigate to open your script as needed.
    4) Select target database in drop-down
    5) Cntrl-E to execute the script
    6) If more scripts to execute, return to step #2 above

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Pat Phelan View Post
    I agree wholeheartedly, but just how many "sane" development environments have you seen in your life?
    Oh you got me. The answer is the 18 month gig with Big Oil and maybe the 3 years with the project management training company. So less than half of my of the time, but more than half of my gigs have used source control.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •