Question: What is the best way to see all the options set for your SQL Server programmatically?
Answer:
Method 1: Using sp_configure
Run following script and it will provide all information about the SQL Server. SQL Server provides in detail information if Advanced Options are turned on.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure GO EXEC sp_configure 'show advanced options', 0 GO
To change any configuration run following script using values from the NAME column of the following result set. E.g. To change max server memory run following script.
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'max server memory', 2048 GO RECONFIGURE GO
Method 2: Using sys.configurations
You can run following script and it will display almost the same information as above query.
SELECT * FROM sys.configurations</pre> <pre>
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
My specific task Run Through Windows Scheduler at Night Time(configured by Client).Task scheduler run our exe , then this exe create connection with database server and executes round about 50 procedures one by one.During executing, given below error encounter.
SQLSTATE =08S02
Microsoft SQL SERVER Native Client 10.0
SMux PROVIDER: Physical CONNECTION is NOT usable[xFFFFFFFF]
Further more exe is placed on client side and database is placed on Server side.
Please provide a suitable solution for this error.
I have not seen such error earlier. Do you see something interesting in ERRORLOG file?
From where u got this ‘resource timeout’ configuration?! Checked in SQL 2014, it`s not there even with the Advanced Options enabled.
Thanks for your note. Yes, you are correct. It was for illustration purpose but I need to be more careful with that. I replaced it with correct example. Thank you!