SQL Server Long Drive!

Every one of us must have started their career path in SQL server from some version. But very few of us hold the knowledge of when Microsoft gave birth to the SQL Server. Even I was not the exception for this, but long back I attended one session in Ahmadabad by Pinal Dave who shared the very great information on SQL Server birth and its childhood. Today I am thankful to him and also feeling honour to share such history with all of you. It not a big deal if you don’t know the history (“How many of us know the history of our great leaders?). But I am telling you guys Microsoft has taken a very long drive with SQL server. Rather than saying anything I would prefer go through this link this speaks more than my words…


How to execute SQL files using T-SQL?

Sometimes ago I read an article on how to execute the sql file using powershell by Martin Bell. After reading it the question came into my mind is “Why not just use T-SQL? Would that be simpler? “. Indeed, powershell gives you a wide approach of doing the database activities, the same can be achieved using T-SQL as well. The below code snippet gives a overview of “How to execute sql using T-SQL”

DECLARE @fPath VARCHAR(4000) = 'C:\SQL\Script1.sql'
DECLARE @outPath VARCHAR(4000) = 'C:\SQL\Script_exec_log.txt'
DECLARE @serverPath VARCHAR(20) = 'localhost'
DECLARE @sqlString VARCHAR(4000)
DECLARE @dbList VARCHAR(1000), @dbName VARCHAR(100), @rowCount INT
SELECT @dbList=COALESCE(@dbList + ',', '')+ name FROM sys.databases WHERE name like '%Test'
SET @rowCount = @@rowcount WHILE 1= 1
SET @dbName = CASE WHEN (@rowCount = 1 OR @dbList != @dbName) THEN @dbList ELSE SUBSTRING(@dbList, 1, CHARINDEX(',', @dbList)-1) END
SET @sqlString = 'SQLCMD -E -S'+@serverPath+' -d'+@dbName+' -i'+@fPath +' -o'+ @outPath
PRINT @sqlString
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))