You can use the sp_get_sqlagent_properties undocumented stored procedure to retrieve the SQL Agent properties of a SQL Instance. It is available in SQL Server MSDB database. This procedure only works if the SQL Agetn Service is started.
You can use the below script to identify the data, log and backup file drive.
@@SERVERNAME as svrName,
select distinct SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
select distinct SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
select distinct SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.
--documents table will store files into varbinary field
--drop table documents
create table documents
documentID int identity(1,1),
--script to store/save document into table
insert into documents
Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
select * from documents
Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.
Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011
--method - 1
select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary
-- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout
select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion
select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion
Sp_Configure procedure is used to display or change the SQL Server setting. Once you execute the SP_Configure procedure it will display the below columns in the output.
name – Name of the configuration parameter minimum – Minimum value setting that is allowed maximum – Maximum value that is allowed config_value – value which currently configured run_value – value which currently running
How to update the configuration value?
Here I will show you how to enable the XP_CmdShell using SP_Configure. Please note don’t update configuration values until you are sure, otherwise it will affect the your SQL Server performance and behavioral.
--XP_Cmdshell is an andvanced option, enbale the advanced option
EXEC sp_configure 'show advanced options', 1
--Enable the advance option
--enable the xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
--Reconfigure the xp_cmdshell value
What is the difference between Config_Value and Run_Value?
When we change the Configuration Parameter value as above it will update the Config_Value filed only, but wouldn’t be in effect until you run reconfigure command. Once the reconfigure command execute or SQL Server restarted, SQL Server will run as per the new configured value.
You can get the description of the configuration parameters from books online or you can query sys.configurations and check for the description column.
select * from sys.configurations Output of the Sp_Configure