dcsimg
Results 1 to 23 of 23

Thread: Stored procedure with master

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    12

    Stored procedure with master

    It is possible to use a stored procedure that starts with
    Code:
    USE master
    The usual code

    Code:
    Dim cmd            As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = Cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "strRestore"
    cmd.Parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 215, name)
    Set rs = cmd.Execute
    he does not recognize it.
    Is there any blockage or some logical error on my part?
    Thanks in advance

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    Moved to database section

    Given that you're talking about "master" I assume this is SQLServer. In which case, yes, it's entirely possible to have a sproc that starts with "Use Master". That would just tell the sproc that any table references, functions etc that it calls will be found in master unless qualified with 3 part naming. It would be unusual because you wouldn't normally want to do your work in a system database but there are some use cases where it might the right thing to do.


    What do you mean by "he does not recognize it"? Are you getting an error? If so, what's the error message?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    12

    Re: Stored procedure with master

    The error message is
    The store procedure 'strRestoreM' could not be found.
    Here is the aforementioned stored procedure that really exists in the master database :
    Code:
    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[strDBRestoreM]    Script Date: 11/09/2018 11:55:51 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[strDBRestoreM]
    @name VARCHAR(MAX) = '', 
    @filebak VARCHAR(MAX) 
    AS
    BEGIN
    
    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = ''
    SET @SQL ='RESTORE DATABASE ' + @name
    SET @SQL = @SQL + ' FROM DISK = ''' + @filebak + ''''
    
    EXECUTE(@SQL)
    END
    In the "W2018" database there are these strDBRestore stored procedures (without the M!), But it does not do its job and no exit message appears: simply the database is not restored
    I know that overlapping one database with another is not advisable, but that's exactly what I'd like to accomplish.

    Code:
    USE [W2018]
    GO
    / ****** Object: StoredProcedure [dbo]. [StrDBRestore] Script Date: 11/09/2018 12:00:52 ****** /
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo]. [StrDBRestore]
    @name VARCHAR (MAX) = '',
    @filebak VARCHAR (MAX)
    AS
    BEGIN
    
    DECLARE @SQL VARCHAR (MAX)
    
    SET @SQL = 'ALTER DATABASE' + @name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    SET @SQL = @SQL + ' RESTORE DATABASE' + @name
    SET @SQL = @SQL + ' FROM DISK =''' + @filebak + ''''
    SET @SQL = @SQL + ' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE' + @name + 'SET MULTI_USER'
    
    EXECUTE (@SQL)
    END

  4. #4
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: Stored procedure with master

    Is there really a stored procedure called "strRestore" in the Master database? Should that be:

    cmd.CommandText = strRestore
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    12

    Re: Stored procedure with master

    The VB6 code is as follows

    Code:
    Dim rs As ADODB.Recordset
         Dim cmd As ADODB.Command
         Set cmd = New ADODB.Command
         cmd.ActiveConnection = Cnn
         cmd.CommandType = adCmdStoredProc
         cmd.CommandText = "strDBRestore"       '-- if I call the stored procedure in W2018
         ' cmd.CommandText = "strDBRestoreM"   '-- if I call the stored procedure in master
         cmd.Parameters.Append cmd.CreateParameter ("name", adVarChar, adParamInput, 50, Tx)
         cmd.Parameters.Append cmd.CreateParameter ("filebak", adVarChar, adParamInput, 200, LbNomeFile)
         Set rs = cmd.Execute

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    So StrDBRestore is in a database called W2018 and is found. strDBRestoreM is in master and is not found. You're using the same ADODB connection in your code.

    I'm guessing but I suspect your connection is pointing to W2018. It won't find strDBRestoreM because strDBRestoreM is not in W2018. You need to point your connection at master.



    Edit> It's also possible that you don't have permission to the stored procedure. That can result in it being "invisible". So check 2 things:-
    1. Your ADODB connection is pointing to the database that contains the sproc.
    2. The credentials you're connection under have the appropriate permission to execute the sproc.
    Last edited by FunkyDexter; Sep 11th, 2018 at 05:46 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: Stored procedure with master

    Quote Originally Posted by FunkyDexter View Post
    So StrDBRestore is in a database called W2018 and is found. strDBRestoreM is in master and is not found. You're using the same ADODB connection in your code.

    I'm guessing but I suspect your connection is pointing to W2018. It won't find strDBRestoreM because strDBRestoreM is not in W2018. You need to point your connection at master.
    If they are on the same instance wouldn't "USE MASTER" find it? That was the question.
    Please remember next time...elections matter!

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    USE and GO are not T-SQL commands.

    They are commands that drive functionality in SQL Server Management Studio. USE changes database context and GO sends a BATCH to the SERVER.

    Open a connection to the MASTER database - that way credentials can be given and what not.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    Use Master is in the sproc. so all it's doing in the code from post 3 is saying "find the sproc called strDBRestoreM which is in the master database and alter it". It won't affect how he's connecting to it from his VB code, which is what I think he's saying is throwing the error.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: Stored procedure with master

    Quote Originally Posted by szlamany View Post
    USE and GO are not T-SQL commands.

    They are commands that drive functionality in SQL Server Management Studio. USE changes database context and GO sends a BATCH to the SERVER.

    Open a connection to the MASTER database - that way credentials can be given and what not.
    I know for a fact in the SQL I pass to the database with "USE SOMETHINGELSE" works to point at a different database regardless of being a T-SQL command or not. I'm confused by your response because I thought that was the nature of this post.
    Please remember next time...elections matter!

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    I believe USE in a stored procedure makes little sense.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    Perhaps we're miss-understanding the problem. My reading was that his VB6 code is throwing an error when it tries to execute the sproc.

    The code in post 3 is just the sproc definitions generated by scripting the sprocs for alter. So the Use there will give the database that contains the sproc but he would still need to ensure the ADODB connection is pointing at the right database.

    Perhaps AlpVir2 could clarify that or we could be giving him completely the wrong advice.



    Edit> I'm going to get a little pedantic here but hopefully it'll help. That Use isn't part of the sproc (and as SzLamany says, it's not strictly speaking part of the sql either). All it's doing is saying "change the context to master" so that's where the subsequent commands (i.e. the Alter) will be run. I'm inferring that strDBRestoreM must be in the Master database but I don't know if I'm correct in that assumption.
    Last edited by FunkyDexter; Sep 11th, 2018 at 06:41 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: Stored procedure with master

    Quote Originally Posted by FunkyDexter View Post
    Perhaps we're miss-understanding the problem. My reading was that his VB6 code is throwing an error when it tries to execute the sproc.

    The code in post 3 is just the sproc definitions generated by scripting the sprocs for alter. So the Use there will give the database that contains the sproc but he would still need to ensure the ADODB connection is pointing at the right database.

    Perhaps AlpVir2 could clarify that or we could be giving him completely the wrong advice.



    Edit> I'm going to get a little pedantic here but hopefully it'll help. That Use isn't part of the sproc (and as SzLamany says, it's not strictly speaking part of the sql either). All it's doing is saying "change the context to master" so that's where the subsequent commands (i.e. the Alter) will be run. I'm inferring that strDBRestoreM must be in the Master database but I don't know if I'm correct in that assumption.
    Could be...We have an application that has the data connections predefined. A database we have called "Notes" is on the same Instance as the database "Accounts" but isn't predefined. To get to it I'll put "Use Notes" at the top of the SQL and use the "Accounts" connection string. Works every time

    That's what I thought the OP was talking about.
    Please remember next time...elections matter!

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    I'll put "Use Notes" at the top of the SQL and use the "Accounts" connection string.
    Yeah, that'll work and if you need to go across databases on a single connection it's really just shorthand to avoid needing to use 3 part naming.

    Here, though, the Use isn't being issued over the ADODB connection so it isn't redirecting the execution of the sproc. Instead it's redirecting the definition of the sproc.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: Stored procedure with master

    Quote Originally Posted by FunkyDexter View Post
    Yeah, that'll work and if you need to go across databases on a single connection it's really just shorthand to avoid needing to use 3 part naming.

    Here, though, the Use isn't being issued over the ADODB connection so it isn't redirecting the execution of the sproc. Instead it's redirecting the definition of the sproc.
    Got it now...
    Please remember next time...elections matter!

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    Thinking about it, that might well be the mistake AlpVir2 is making. @AV, are you thinking that the Use will change where the stored procedure is run? Because it won't, it changes where the stored procedure is "saved" and therefore where you need to point your ADODB connection in order to find it.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    I would imagine that USE is caught by the provider (ADO or SQLClient) and a new connection string is established and pushed to the server to see if it will open. It must use the credentials of the existing connection in some way.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    12

    Re: Stored procedure with master

    I followed your discussions, even without understanding them 100%
    They helped me make some changes to my code. Thank you.
    I confirm that the VB code does not find the "strDBRestoreM" stored procedure because the cnn connection is pointed to the W2018 database.
    By means of this code VB6, vice versa, the desired result is obtained: a restore of a database.
    It can be noted that the connection to W2018 is closed and a new one is opened up as a master.

    Code:
     Dim cn As ADODB.Connection
       Set cn = New ADODB.Connection
       CnnSQL.Close: Set CnnSQL = Nothing
       cn.CursorLocation = adUseServer
       cn.Open Replace(StringaConnessione, "W2018", "master")
       
        Dim rs As ADODB.Recordset
        Dim cmd            As ADODB.Command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "strDBRestoreM"
        cmd.Parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 50, Tx)
        cmd.Parameters.Append cmd.CreateParameter("filebak", adVarChar, adParamInput, 200, LbNomeFile)
        Set rs = cmd.Execute
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    All OK but, given the inconvenience of using a store procedure in master, how could I get the same result with a stored procedure in the W2018 database ?
    By launching this it is signaled that a database in use can not be superimposed.
    But I close the connection cnn ! Furthermore there is a REPLACE

    Code:
    USE [W2018]
    GO
    /****** Object:  StoredProcedure [dbo].[strDBRestore]    Script Date: 11/09/2018 15:24:18 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[strDBRestore]
    @name VARCHAR(MAX) = '', 
    @filebak VARCHAR(MAX) 
    AS
    BEGIN
    
    DECLARE @SQL VARCHAR(MAX)
    
    SET @SQL = 'ALTER DATABASE ' + @name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE '
    SET @SQL = @SQL + ' RESTORE DATABASE ' + @name
    SET @SQL = @SQL + ' FROM DISK = ''' + @filebak + ''''
    SET @SQL = @SQL + ' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE ' + @name + ' SET MULTI_USER'
    
    EXECUTE(@SQL)
    END

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    You are trying to restore the database W2018 - is that correct?

    If that is the case then you cannot be accessing any objects in that DATABASE when the RESTORE happens - as it destroys the physical file on disk and re-works it from the backup.

    So using a STORED PROCEDURE in the DESTINATION DATABASE for a RESTORE would make no sense.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Stored procedure with master

    it destroys the physical file on disk
    ^That says it all. So the sproc can't be in the database you're restoring.

    I can see two obvious choices:-
    1. Keep the sproc in Master and live with the inconvenience
    2. Don't use a sproc. You could just issue the SQL directly.

    I would say that I'm not mad keen on putting sprocs (or anything else) in Master. It's a system database, not a user one. When I have a need for an "admin" database on a server I'll typically create one dedicated to the purpose.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    If you are looking to have 100% of the functionality in the W2018 database then the SPROC within that database should CREATE a new DATABASE - CREATE a SPROC in that DATABASE.

    Then in code you can switch to that database and run that new sproc (which will do the backup)

    When done - it should re-attach to the newly restored W2018 database and run a cleanup sproc that kills the new DATABASE that was created for the purpose of this restore.
    Last edited by szlamany; Sep 11th, 2018 at 09:25 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    12

    Re: Stored procedure with master

    Quote Originally Posted by szlamany View Post
    If you are looking to have 100% of the functionality in the W2018 database then the SPROC within that database should CREATE a new DATABASE - CREATE a SPROC in that DATABASE.

    Then in code you can switch to that database and run that new sproc (which will do the backup)

    When done - it should re-attach to the newly restored W2018 database and run a cleanup sproc that kills the new DATABASE that was created for the purpose of this restore.
    What has been proposed seems to me to be very complex.
    I found a good solution this way, all side VB6 (so the strDBRestoreM stored procedure is no longer needed).
    Thank you all for helping!

    Code:
       Cnn.Close: Set Cnn = Nothing
        
       Dim cn      As ADODB.Connection
       Dim cmd     As ADODB.Command
       Dim rs      As ADODB.Recordset
       Dim sql     As String
       '
       sql = "ALTER DATABASE " & Tx & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE  "
       sql = sql & " RESTORE DATABASE " & Tx
       sql = sql & " FROM DISK = '" & LbNomeFile & "'"
       sql = sql & " WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE " & Tx & " SET MULTI_USER"
       Debug.Print sql
       '
       Set cn = New ADODB.Connection
       cn.CursorLocation = adUseServer
       Set rs = New ADODB.Recordset
       Set cmd = CreateObject("ADODB.Command")
       cn.Open Replace(StringaConnessione, Tx, "master")
       cmd.ActiveConnection = cn
       cmd.CommandText = sql
       cmd.CommandType = adCmdText
       Set rs = cmd.Execute
       Set rs = Nothing
       cn.Close: Set cn = Nothing

  23. #23
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: Stored procedure with master

    That is a much better solution.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width