I have observed this question in the interview question few days ago. I had a fun time to see candidates failing to answer this question. The most common answer candidate gave us was that they will right click on the server and see the properties. Well, that option will not give all the details which actual question intended. Let us see the question about SQL Server Configuration:
Question: What is the best way to see all the options set for your SQL Server programmatically?
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)
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.
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!