March 15, 2015 at 8:24 pm
Hi everyone
This is the most simplest SP but i dont know how to run it on SSMS
I have this store procedure script and I want to run this.
Exec dbo.sp_AskBrent @ExpertMode=1, @Second=10;
My question is, how to run this sp using as per above statement? Where to put those sp_AskBrent script in order for "Exec" to run it....
If double click, it will go straight and using SSMS and just execute it, this one I know
I think, those SP_AskBrent must be save first but i dont how to save it before i can execute it
March 15, 2015 at 10:17 pm
Did you create the stored procedure by executing the code to create it? Something like...
CREATE PROCEDURE myProcName
@param1 VARCHAR(20)
, @param2 INT = 10
AS
SELECT field1, field2, field3
FROM MyTable
WHERE field1 = @param1
AND field2= @param2
Then just
EXEC myProcName @param1 = 'Some text', @param2 = 15;
or
EXEC myProcName @param1 = 'SomeText'
the second one will work because @param2 already has a default (10).
March 16, 2015 at 3:59 am
Yep, as Piet says, you need to CREATE a sproc before you can execute it. Think of the CREATE statement as "installing" the stored procedure in one of the databases on one of your SQL servers.
If you're doing what I think you're doing, which is trying to use some of Brent's educational and diagnostic stuff, the script he sent you will have the CREATE statement already in it.
So, you make a connection to your chosen server using SSMS, and then open a connection to the specific database where you want to install Brent's sproc. For his stuff, the "master" system database is usually the intended location.
Simply press "play" on the script (the green triangle), and it will run, which "installs" the sproc in the master database.
Note that you have not actually executed it yet!
Now, close that script window down, and open up a new query window connected to the master db. In there you can type the statement that Piet has shown you and, once again, press "play". Because the sproc is now installed, it will execute and you will see all sorts of wonderful things.
Extra check: to make sure that the sproc has indeed been installed, expand Server > Databases > master > Programmability > Stored Procedures and you should see it in there, listed amongst the others in alphabetical order.
March 17, 2015 at 10:44 pm
Yes you are right
The script sp_AskBrent already include with create store procedure statement. I just notice this.
I just double click sp_AskBrent and execute it using SSMS.
Once execute, the SP will store automatically into the intended instance.
And when open a new query again and "execute dbo.sp.askbrent bla bla (another parameter)", the SP is installed there and wallahhhhh....command query successfully
March 17, 2015 at 10:49 pm
Hi EMarkM
Tq for the below tips :
"Extra check: to make sure that the sproc has indeed been installed, expand Server > Databases > master > Programmability > Stored Procedures and you should see it in there, listed amongst the others in alphabetical order."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply