Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2008
    Posts
    10

    Question Unanswered: how do Create stored Procedure to Create another Stored Procedure in another database

    Hello Every one;
    I need to Create stored Procedure to Create another Stored Procedure in another database.
    means i have a database say x now x has a stored procedure xq which must be able to create a stored procedure in another database which will be known to xq at the run time.

    Please Help!!!!!!!!!!
    thanks In advance.
    Last edited by sourav.bhargava@g; 11-24-08 at 06:57.

  2. #2
    Join Date
    Sep 2005
    Posts
    10
    try looking into linked servers in SQL - script your stored procedure then

    USE IP_ADDRESS.DatabaseName to run it inside the remote machine.

  3. #3
    Join Date
    Nov 2008
    Posts
    10
    thanks paul_anthony but don't i have some other option???
    Last edited by sourav.bhargava@g; 11-24-08 at 07:02.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    this sounds like a very questionable solution design. as a general rule, I do not create permanent database objects on the fly in application code because this can get chaotic quickly and is generally considered a very bad practice.

    What are you trying to accomplish exactly by doing this?

    All of that being said, what is wrong with the solution he provided you. Do you have some gripe with linked servers?
    “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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This sounds like a VERY bad idea to me. What are you trying to accomplish?

    -PatP

  6. #6
    Join Date
    Nov 2008
    Posts
    10

    Arrow

    i am using a procedure to create a data base at runtime now i want to create a procedure in this newly created database from within the 1st procedure it self. i dont want these databases to be linked .

  7. #7
    Join Date
    Sep 2005
    Posts
    10
    i am using a procedure to create a [remote?] data base at runtime
    How are you creating the remote Database? Post your script.

  8. #8
    Join Date
    Nov 2008
    Posts
    10

    Arrow

    Quote Originally Posted by paul_anthony
    How are you creating the remote Database? Post your script.
    i am using dynamic sql statements.

    create proc xyz @cctv Char(20)
    as
    begin
    declare @databaseName char(20)
    set @databaseName=@cctv
    Declare @SQL VarChar(1000)

    SELECT @SQL = 'Create database ' + @databaseName


    Exec (@SQL)
    DECLARE @cmd varchar(1000)
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create Table xx(xx_ID int NOT NUll Primary Key,Name char(50),ww_id int) "'

    exec master..xp_cmdshell @cmd, NO_OUTPUT
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create Table yy(yy_ID int NOT NUll Primary Key,Name char(50),xx_id int) "'

    exec master..xp_cmdshell @cmd, NO_OUTPUT
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create Table zz(zz_ID int NOT NUll Primary Key,Name char(50),yy_id int) "'

    exec master..xp_cmdshell @cmd, NO_OUTPUT
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create Table Booth(Booth_ID int NOT NUll Primary Key,Name char(50),SDO_id int) "'

    exec master..xp_cmdshell @cmd, NO_OUTPUT


    end

  9. #9
    Join Date
    Nov 2008
    Posts
    10
    now please help me to create stored procedure for the runtime database
    using an existing stored procedure in a previous database

  10. #10
    Join Date
    Sep 2005
    Posts
    10
    http://www.eggheadcafe.com/articles/20030609.asp

    Shows you how to generate a script for all the procs in your database. Build your statement from that, and send to XP_commandShell.


    Have you seen the microsoft database publishing wizard?

  11. #11
    Join Date
    Nov 2008
    Posts
    10
    Quote Originally Posted by paul_anthony
    http://www.eggheadcafe.com/articles/20030609.asp

    Shows you how to generate a script for all the procs in your database. Build your statement from that, and send to XP_commandShell.


    Have you seen the microsoft database publishing wizard?
    no i haven't i would preffer command prompt or query analyser rather than a wizard.

  12. #12
    Join Date
    Sep 2005
    Posts
    10

Posting Permissions

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