Professional Web Applications Themes

changing the current database within a stored procedure - Microsoft SQL / MS SQL Server

I prefer to keep raw SQL out of my code and in stored procedures, but some built-in SQL functionality ("create procedure", etc) only works in the 'current' database. Unfortunately, executing dynamically generated SQL to change the database with USE doesn't work within stored procedures, so I would seem to be stuck keeping the raw SQL in code after all, or (not likely) duplicating the stored procedure in every database. Is there any way around this? TIA, Bob...

  1. #1

    Default changing the current database within a stored procedure

    I prefer to keep raw SQL out of my code and in stored procedures, but some
    built-in SQL functionality ("create procedure", etc) only works in the 'current'
    database. Unfortunately, executing dynamically generated SQL to change the
    database with USE doesn't work within stored procedures, so I would seem to be
    stuck keeping the raw SQL in code after all, or (not likely) duplicating the
    stored procedure in every database.

    Is there any way around this?

    TIA,
    Bob


    Bob Guest

  2. #2

    Default Re: changing the current database within a stored procedure

    What about

    EXEC('EXEC '+dbname+'.dbo.procedureName')


    "Bob" <net> wrote in message
    news:phx.gbl... 
    'current' 
    to be 
    the 


    Aaron Guest

  3. #3

    Default Re: changing the current database within a stored procedure

    If you are using multiple databases in your code, one option would be to use
    distributed queries using openquery/link server. This way you can connect to
    multiple databases from single stored procedure.


    "Bob" <net> wrote in message
    news:phx.gbl... 
    'current' 
    to be 
    the 


    Mohanjit Guest

  4. #4

    Default Re: changing the current database within a stored procedure

    Could you show me a simple example of getting openquery to work from within a
    stored procedure?

    This attempt only produces an error:

    ---BEGIN SQL---
    CREATE PROCEDURE [TMPquery]
    AS

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

    DECLARE SQL varchar(1000)
    SET SQL = '
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SELECT * FROM OPENQUERY(MyServer, ''SELECT TOP 1 * FROM MyDataBase..MyTable'')
    '
    PRINT 'executing dynamic sql {' + sql + '}'
    EXEC (SQL)
    GO
    ---END SQL---

    ---BEGIN OUTPUT---
    executing dynamic sql {
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SELECT * FROM OPENQUERY(MyServer, 'SELECT TOP 1 * FROM MyDataBase..MyTable)
    }
    Server: Msg 7405, Level 16, State 1, Line 4
    Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
    for the connection. This ensures consistent query semantics. Enable these
    options and then reissue your query.
    ---END OUTPUT---

    I'd put 'GO' in the dynamic SQL, but that just doesn't work...

    Bob


    "Mohanjit" <uc.edu> wrote in message
    news:phx.gbl... 
    > 'current' 
    > to be 
    > the 
    >
    >[/ref]

    Bob Guest

  5. #5

    Default Re: changing the current database within a stored procedure

    I can't use GO in dymamic SQL, I can't use it directly in the body of any stored
    procedure. In addition, it appears that ANSI_NULLS is reset when a stored
    procedure is called, and has no effect when set within a stored procedure.

    Let me clarify by stating the problem definition for the example code I
    posted... how do I create a stored procedure in Databa using a stored
    procedure in DatabaseY, on the same server?

    As in...

    EXEC DatabaseY.dbo.SPCreator 'Databa', 'NewSp'

    Bob

    "Mohanjit" <com> wrote in message
    news:eoi%phx.gbl... 
    > within a 
    > MyDataBase..MyTable'') 
    > MyDataBase..MyTable) 
    > be set [/ref]
    > use [/ref]
    > connect to [/ref]
    > some [/ref]
    > the [/ref]
    > seem [/ref]
    > duplicating 
    > >[/ref]
    >
    >[/ref]

    Bob Guest

Similar Threads

  1. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  2. Current time in a procedure ??!!
    By problems in forum Informix
    Replies: 4
    Last Post: November 3rd, 08:27 PM
  3. Stored Procedure Bug?
    By Keith in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 20th, 04:32 AM
  4. how to search a database with a stored procedure?
    By wilco in forum ASP.NET General
    Replies: 2
    Last Post: August 5th, 02:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139