Stored procedure with master

[AlpVir2]

It is possible to use a stored procedure that starts with

USE master

The usual 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

[FunkyDexter]

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?

[AlpVir2]

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 :

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.

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

[TysonLPrice]

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

cmd.CommandText = strRestore

[AlpVir2]

The VB6 code is as follows

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

[FunkyDexter]

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.

[TysonLPrice]

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.

[szlamany]

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.

[FunkyDexter]

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.

[TysonLPrice]

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.

[szlamany]

I believe USE in a stored procedure makes little sense.

[FunkyDexter]

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.

[TysonLPrice]

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.

[FunkyDexter]

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.

[TysonLPrice]

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...

[FunkyDexter]

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.

[szlamany]

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.

[AlpVir2]

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.

 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

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

[szlamany]

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.

[FunkyDexter]

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.

[szlamany]

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.

[AlpVir2]

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!

   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

[szlamany]

That is a much better solution.