What does this code do: `dbo.sp_set_sqlagent_properties @local_host_server`

Question

When I was finding an SQL server command to create a server alias (cf.), a user suggested this piece of code:

use msdb
go
dbo.sp_set_sqlagent_properties @local_host_server=N'Test'
go

I tried running it and had "Query executed successfully", but the server alias doesn't seem to be created.

What exactly does the code above do?

How do I undo what the code above does?

Try re-running that statement with N'' (to reset it) if you can't just undo it from server management tools in the agent properties. — Mat
– Mat, Commented Aug 15, 2011 at 14:28
@Mat, mine is NULL, I would probably try NULL before empty string. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 15, 2011 at 14:30

Aaron Bertrand · Accepted Answer · 2014-03-13 13:37:33Z

12

Don't ever just run commands recommended by some person online without first trying to understand what they do. You can do a few things to understand this stored procedure before running it, e.g.:

EXEC msdb.dbo.sp_helptext 'sp_set_sqlagent_properties';

For all of my machines, local_host_server property is NULL according to:

EXEC msdb.dbo.sp_get_sqlagent_properties;

So to undo this, you might try:

EXEC msdb.dbo.sp_set_sqlagent_properties @local_host_server = NULL;

However it seems like this stored procedure is undocumented, so that is the best I can offer. I would point you to online documentation (which you may also consider searching for yourself the next time someone offers that you run some unrecognized piece of code), but the search will turn up empty. If someone says to run something and your search turns up empty, that is probably even more reason to be hesitant about just running it.

And just as an observation, since it seems like you didn't notice any behavior change, you may be jumping the gun on scolding anyone for "screwing up your computer." If I told you to fix your iPhone by smashing it with a hammer, would I be to blame for that too? Even if there was no damage because you didn't swing hard enough?

edited Mar 13, 2014 at 13:37

answered Aug 15, 2011 at 14:29

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pacerier Over a year ago

heys btw how could it be that stored procedures are undocumented? doesn't that mean that there are many stored procedures out there that no one knows how to use / what will happen if they use it?

Aaron Bertrand Over a year ago

Many stored procedures are created for internal use only. They become known when people use profiler, for example, to see what Management Studio and other tools are calling behind the scenes. You are more than welcome to use these procedures, but at your own risk - Microsoft's stance is that their use is unsupported (and their behavior can change between releases or even service packs). If someone tells you to run x and you can't find x documented in Books Online, better do more research before just blindly running the command.

vasek · Accepted Answer · 2020-10-16 16:24:36Z

1

Documentation is now available (albeit not very descriptive).

As I understand it, this sproc is for setting up the SQL Server Agent properties. If you right-click on your SQL Server Agent node in SSMS, and then choose properties, and then make some random changes without clicking OK to save those changes, and then you Script Action To New Query Window, you will see that this sproc is used to make those changes.

For example, here are some changes I set up (without clicking OK to actually make them), and then chose Script Action To New Query Window to see what the proposed changes looked like as a script:

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, 
        @databasemail_profile=N'(myservername)', 
        @use_databasemail=1
GO

edited Oct 16, 2020 at 16:24

vasek

2,8491 gold badge29 silver badges33 bronze badges

answered Nov 30, 2015 at 20:55

Speedcat

341 bronze badge

1 Comment

Aaron Bertrand Over a year ago

FWIW, that "documentation" doesn't even mention the stored procedure, it just talks about all of the options on the UI dialog. The page also existed 6+ years before this question even appeared, and even back then, you could have done the same thing with the script button when making fake changes to SQL Server Agent properties. I don't think this answer really reveals anything new or useful, and I think the warnings I gave below about about using undocumented procedures directly still stand in spite of it.

Collectives™ on Stack Overflow

What does this code do: `dbo.sp_set_sqlagent_properties @local_host_server`

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related