The Venue will be:
406, Empire State Building, Ring Road, Nr Udhna Darwaja
Security! This is the word comes in mind of every concerned person when it come...
This post will show you how to change the collation settings in MS SQL Server for specific database...
In this post I will show you how you can resolve collation conflict error...
In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...
Generally, we create views and stored procedures (proc here after) ...
|Jan 5 2016||SQL Server 2016 – AlwaysOn Basic Availability Group||http://www.pythian.com/blog/alwayson-basic-availability-group-sql-server-2016/|
|July 31 2015||SQL Server and OS Error 1117, Error 9001, Error 823||http://www.pythian.com/blog/sql-server-and-os-error-1117-error-9001-error-823/|
|July 9 2015||Reading System Logs on SQL Server||http://www.pythian.com/blog/reading-system-logs-on-sql-server/|
|July 20 2015||Reading System Logs on SQL Server - Part 2||http://www.pythian.com/blog/reading-system-logs-sql-server-part-2/|
|Sep 30 2015||Import / Export Multiple SSIS Packages||http://www.pythian.com/blog/importexport-multiple-ssis-packages/|
|July 28 2014||Unexpected Shutdown caused by ASR||http://www.pythian.com/blog/unexpected-shutdown-caused-by-asr/|
|Jan 23 2014||Script to Collect Database Information Quickly||http://www.pythian.com/blog/script-to-collect-database-information-quickly/|
|Nov 22 2015||Script to Collect ALL Database Information wtih VLF Count||https://gallery.technet.microsoft.com/Script-to-Collect-ALL-82664699|
|May 3 2013||Collect Cluster Information using TSQL||https://gallery.technet.microsoft.com/scriptcenter/COLLECT-CLUSTER-INFORMATION-9a75e4a7|
|Mar 9 2013||Configure Auto Growth in Fixed MB||https://gallery.technet.microsoft.com/scriptcenter/Configure-AutoGrowth-in-f4f3d7d1|
|Jun 26 2015||Script to Monitor Database Mirroring Health||https://gallery.technet.microsoft.com/scriptcenter/Script-to-monitor-database-0f35c5d7|
|Jun 26 2015||Script to Monitor AlwaysOn Health||https://gallery.technet.microsoft.com/scriptcenter/TSQL-for-AlwaysOn-Health-6aae827d|
Last month, I have requested my manager for an approval so that I can attend SQL Server Geeks Annual Summit 2015, and now when I hear back from him, I am really very excited that I will be attending.
You must be wondering what is so exciting about this Summit ? Well, let me tell you, there are number of reasons, at least for me to be that excited.
There are some personal reasons as well for me to attend this event, as I already mentioned – to meet few friends in-person, and to make few more friends. I have been knowing few of these friends while I was aspiring DBA, when I was MVP and when I was being transformed as a professional.
I have been knowing Satya Shyam K Jayanti when I was aspiring DBA, I learned many things from him. Amit Bansal, I know him since 2007 when I was an MVP, though we didn’t meet each other personally we have had many occasion to talk over phone, email and chat. Prashant Kumar is my colleague and friend, we’ve already met in-person and we talk to each other often as he is working as technical escalation, one level up then me. I will also meet Pinal Dave ( Blog | Twitter ) if he is in city, he don’t need introduction in SQL Server community, he is like brother to me. There are others whom I have read and it would be fun and exciting to meet if time permits, they are Benjamin Nevarez, Sarabpreet Singh and Manohar Punna.
I would like to thank them all and wish belated Guru Purnima, all as I have learned things from them all. I have always admired dedication and zest that Pinal and Amit showed for community, I wish someday I will be able to match that level of energy and dedication. Sorry I missed to name few, if I write about them all this list and post will not end.
In last, I would like to thank Amit once again for organizing this event and providing a platform to learn and socialize with alike minded community people. I would also like to thank my manager – John Eisbrener and CEO – Paul Vallee and my company Pythian for sponsoring me so that I can enjoy the company of alike minded geeks and learn something new.
See you there at #SSGAS2015!!!!
I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS. Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance.
With lot of excitement I tried connecting to SSAS – it was unsuccessful attempt!! It shows me an error message which says - A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
As we always do – Googled and it lead me to the connect item filed by Aaron Bertrand ( Blog | Twitter ). Reading through the post I came to know that if I try to connect to the named instance SSAS it’s chance that I get this very error. I also have the similar case, I have renamed my machine hence this error showed up with the named instance.
Workaround: As suggested in the comment section, I have granted NTFS permission on folder program files (x86)\microsoft sql server\90\shared\asconfig for the SQL Browser service account and I am sorted!!
Reference: Microsoft Connect ID 679792photo credit: JLaw45 via photopin cc
—Hemantgiri S. Goswami (http://www.sql-server-citation.com)
Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me.
I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified. I can say, I am lucky, but all I know that I have worked hard – a lot. I have tried to learn a lot, from friends, from colleagues, from online web forums, books online, books and MS Evangelists, and still learning. And, that pays me well, I participate in community activities, speaks, blog and have been able to put my thoughts together to publish a book on SQL Server Cluster – this way I am surviving as a professional.
To be honest, during my career I have interviewed a lot many people and many of them are certified but they don’t have exposure and practical troubleshooting skills which was expected; apart from this, there are dumps available that can made it easy to clear an exam. Please, Neither I am not criticizing those who has passed nor I am saying all who haven’t passed aren’t good as professional, but I am just trying to make a point why I haven’t attempted one. With this kind of experience it never interest me to pass the certification.
And, then, MCM was introduced. I have heard a lot about Oracle exams which has lab test as well and MCM is similar kind of exams. Brent Ozar ( Blog | Twitter ) blogged about What is MCM in 2010. I have followed all his posts about MCM and has developed my interest in attending MCM training followed by an exam. Event though the exam and training fees are way too high for me, I have made my mind that I will utilize the money my employer provide each of us for training + some from my pocket to get train and earn this title – but, sadly, it is gone now. Since MCITP is a pre-requisite for MCM, I have made my mind, attempted and failed. However, I was sure, I was reading more and will clear it in next attempt. But, I am not sure, whether I will be taking an MCITP now.
I will be more than happiest person, if this decision for MCM exam are reversed or some relevant / at par exams are introduced. In our country, it is said that “whatever happened has happened for the good, and whatever that is going to be happen in future will be for good”. I am hoping that this saying will hold true.
At the end, I would like to say, if you are believer and aspirant of this exam just like me please take some time and vote on the MS Connect site for reconsideration.
Hemantgiri S. Goswami (http://www.sql-server-citation.com)
People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.
SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.
To use the BCP utility from TSQL, we need to execute “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -
Once successfully executed, the below script with the required changes could be executed to download the files -
Hope, this helps & save a lot of your valuable time.
/* IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx REMOVE sp_configure parameters if you are executing this script on SQL Server 2000 Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com Date : 24th March 2013 Version : 1.0 Tested ON: Windows Server >> 2003, 2008, 2008 R2 SQL Server >> 2000, 2005, 2008, 2008 R2, 2012 */ sp_configure 'show advanced options',1 RECONFIGURE WITH OVERRIDE GO sp_configure 'xp_cmdshell',1 RECONFIGURE WITH OVERRIDE GO DECLARE @TimeZone NVARCHAR(100) ,@ProductVersion SYSNAME ,@PlatForm SYSNAME ,@Windows_Version SYSNAME ,@Processors SYSNAME ,@PhysicalMemory SYSNAME ,@ServiceAccount SYSNAME ,@IPAddress SYSNAME ,@DOMAIN SYSNAME ,@MachineType SYSNAME ,@SQLServerIP VARCHAR(255) ,@CMD VARCHAR(100) ,@Node1 VARCHAR(100) ,@Node2 VARCHAR(100) ,@Node1IP VARCHAR(100) ,@Node2IP VARCHAR(100) ,@OSEdition VARCHAR(100) ,@OSVersion VARCHAR(100) ,@OSName VARCHAR(100) ,@OSPatchLevel VARCHAR(100) CREATE TABLE #TempTable ( [Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000) ) ; INSERT INTO #TempTable EXEC xp_msver; -- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008 EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', @value_name = N'StandardName', @value = @TimeZone output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer', @value_name = N'ObjectName', @value = @ServiceAccount output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions', @value_name = N'ProductType', @value = @MachineType output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters', @value_name = N'Domain', @value = @DOMAIN output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'CLUSTER\NODES\1', @value_name = N'NodeName', @value = @Node1 output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'CLUSTER\NODES\2', @value_name = N'NodeName', @value = @Node2 output EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion', @value_name = N'ProductName', @value = @OSName output create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100)) insert into #OSEdition EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions', @value_name = N'ProductSuite' SET @OSEdition = (SELECT TOP 1 OSedition FROM #OsEdition) EXEC master.dbo.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion', @value_name = N'CSDVersion', @value = @OSPatchLevel output set @cmd = 'ping ' + @Node1 create table #Node1IP (grabfield varchar(255)) insert into #Node1IP exec master.dbo.xp_cmdshell @cmd set @cmd = 'ping ' + @Node2 create table #Node2IP (grabfield varchar(255)) insert into #Node2IP exec master.dbo.xp_cmdshell @cmd set @cmd = 'ping ' + @@servername create table #SQLServerIP (grabfield varchar(255)) insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd SET @SQLServerIP=( SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) from #SQLServerIP where left(grabfield,7) = 'Pinging' ) SET @Node1IP =( SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) from #Node1IP where left(grabfield,7) = 'Pinging' ) SET @Node2IP =( SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) from #Node2IP where left(grabfield,7) = 'Pinging' ) SET @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2) SET @Platform = (SELECT Character_Value from #TempTable where [INDEX]=4) SET @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15) SET @Processors = (SELECT Character_Value from #TempTable where [INDEX]=16) SET @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19) SELECT ServerName = @@SERVERNAME ,OSName = @OSName ,OSEdition = @OSEdition ,OSPatchLevel = @OSPatchLevel ,SQLServerIP = @SQLServerIP ,IsClustered = SERVERPROPERTY('IsClustered') ,Node1_Name = @Node1 ,Node1_IP = @Node1IP ,Node2_Name = @Node2 ,Node2_IP = @Node2IP ,SQLServerEdition = SERVERPROPERTY('Edition') ,SQLServerLevel = SERVERPROPERTY('ProductLevel') ,ServerTimeZone = @TimeZone ,SQLServerVersion = @ProductVersion ,SQLServerPlatform = @PlatForm ,ProcessorCore = @Processors ,PhysicalMemory = @PhysicalMemory ,ServiceAccountName = @ServiceAccount ,WKS_Server = @MachineType ,Domain = @DOMAIN GO DROP TABLE #Node1IP DROP TABLE #NODE2IP DROP TABLE #SQLServerIP DROP TABLE #TempTable DROP TABLE #OSEdition GO sp_configure 'xp_cmdshell',0 RECONFIGURE WITH OVERRIDE GO sp_configure 'show advanced options',0 RECONFIGURE WITH OVERRIDE GOPlease do post back your feed back for this script, I will try my best to update and post back new version for this script.
DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar. DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar. --Find and set the Start & End Date of the said Month-Year DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR, @Year) + RIGHT('0' + CONVERT(VARCHAR, @Month), 2) + '01' DECLARE @EndDate AS DATETIME = DATEADD(DAY, - 1, DATEADD(MONTH, 1, @StartDate)); WITH Dates AS ( SELECT @StartDate Dt UNION ALL SELECT DATEADD(DAY, 1, Dt) FROM Dates WHERE DATEADD(DAY, 1, Dt) <= @EndDate ), Details AS ( SELECT DAY(Dt) CDay, DATEPART(WK, Dt) CWeek, MONTH(Dt) CMonth, YEAR(Dt) CYear, DATENAME(WEEKDAY, Dt) DOW, Dt FROM Dates ) --Selecting the Final Calendar SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday FROM ( SELECT CWeek, DOW, CDay FROM Details ) D PIVOT(MIN(CDay) FOR DOW IN ( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday )) AS PVT ORDER BY CWeekOutput:
DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted DECLARE @NoOfChars AS INT = LEN(@Value) DECLARE @Permutations TABLE (Value VARCHAR(20)) --Make sure the size of this Value is equal to your input string length (@Value) ; WITH NumTally AS ( --Prepare the Tally Table to separate each character of the Value. SELECT 1 Num UNION ALL SELECT Num + 1 FROM NumTally WHERE Num < @NoOfChars ), Chars AS ( --Separate the Characters SELECT Num, SUBSTRING(@Value, Num, 1) Chr FROM NumTally ) --Persist the Separated characters. INSERT INTO @Permutations SELECT Chr FROM Chars --Prepare Permutations DECLARE @i AS INT = 1 WHILE (@i < @NoOfChars) BEGIN --Store the Permutations INSERT INTO @Permutations SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated Chars. P1.Value + P2.Value FROM ( SELECT Value FROM @Permutations WHERE LEN(Value) = @i ) P1 CROSS JOIN ( SELECT Value FROM @Permutations WHERE LEN(Value) = 1 ) P2 --Increment the Counter. SET @i += 1 --Delete the Incorrect Lengthed Permutations to keep the table size under control. DELETE FROM @Permutations WHERE LEN(Value) NOT IN ( 1, @i ) END --Delete InCorrect Permutations. SET @i = 1 WHILE (@i <= @NoOfChars) BEGIN --Deleting Permutations which has not used "All the Chars of the given Value". DELETE FROM @Permutations WHERE Value NOT LIKE '%' + SUBSTRING(@Value, @i, 1) + '%' --Deleting Permutations which have repeated incorrect character. DELETE FROM @Permutations WHERE LEN(Value) - LEN(REPLACE(Value, SUBSTRING(@Value, @i, 1), '')) != LEN(@Value) - LEN(REPLACE(@Value, SUBSTRING(@Value, @i, 1), '')) SET @i += 1 END --Selecting the generated Permutations. SELECT Value FROM @PermutationsHope, this script helps!
USE < DBName > --Replace this with the actual DBName GO DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query --The below will list the TSQL Statements which could be copied & executed in a separate query window. SELECT 'IF EXISTS(SELECT 1 FROM ' + T.NAME + ' WHERE ' + @Criteria + ') ' + 'SELECT ''' + T.NAME + ''' TableName, * FROM ' + T.NAME + ' WHERE ' + @Criteria FROM sys.columns C INNER JOIN sys.tables T ON T.object_id = C.object_id WHERE C.NAME = @ColumnNameThe above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.