Small but unforgettable Questions of SQL Server

1. How to run a query on a remote SQL Server? Ans: To do the same use OPENROWSET:Syntax: SELECT * FROM OPENROWSET(‘SQLOLEDB’,REMOTE_SERVER_NAME’;’sa’;’password’,’SQL STATEMENT’)Example: Select * from OPENROWSET(‘SQLOLEDB’,’local’;’sa’;”,’Select * from employees’) 2.How can execute operating system comman from within SQL Server? Ans: The xp_cmdshell stored procedure helps to do the same.Example:EXEC MASTER..xp_cmdshell ‘Dir C:’ 3. If

Read More

SQL Server Script to Create Windows Directories

The script is originally written by : Tim Ford One can attain the same with the use of two inbuild stored procedure master.sys.xp_dirtree and master.sys.xp_create_subdir USE Master;GOSET NOCOUNT ON — 1 – Variable declarationDECLARE @DBName sysnameDECLARE @DataPath nvarchar(500)DECLARE @LogPath nvarchar(500)DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT) — 2 – Initialize variablesSET @DBName = ‘Foo’SET @DataPath

Read More

How to export data from SQL Server to Excel

Exporting data from SQL Server to Excel can be achieved in a variety of ways: Data Transformation Services [DTS] SQL Server Integration Services [SSIS] Bulk Copy [BCP] Rest of above GUI operation there is another option available via the T-SQL language is the OPENROWSET command.This command can be called directly in any stored procedure, script

Read More