Select Object_Schema_name(ix.object_id) Schema_Name, Object_name(ix.object_id) Object_Name,ix.name,ix.type_desc , string_agg(Cast(c.name as nvarchar(MAX)) + ' (' + case when is_descending_key = 0 then 'ASC' Else 'DESC' END + ') ',',') within group(Order by ixc.key_ordinal asc) As KeyCols From sys.indexes ix inner join sys.index_columns ixc on ix.index_id = ixc.index_id and ix.object_id = ixc.object_id inner join sys.columns c on ixc.object_id = c.object_id and c.column_id = ixc.column_id Where objectpropertyex(ix.object_id,'IsMSShipped') =0 And ixc.is_included_column=0 and ix.index_id <> 1 Group by ix.object_id,ix.name,ix.type_desc Order by 1 asc,2 ascI’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
While configuring SQL Server Multi Server Administration: Master and Target Servers in SQL Agent, we encountered an error as below:
MSX enlist failed for JobServer ‘TargetServer’.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The enlist operation failed
(reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘MasterServer’.
Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)
Root cause Analysis & Resolution
As per the investigation, it is identified as an issue associated with a registry value on the target server MsxEncryptChannelOptions. When we configure the set up, it tries to establish the connection between master and targets in a secure channel with full SSL encryption. And if SSL encryption is not enabled between servers/instances, then this setting has to be changed in target servers using the registry. You can change the registry value here: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\SQLServerAgent. Please note the default value is 2. We need to change it to 0, however, this change needs to be verified and evaluated with you security norms and standards.
Create Table and Populate table data
Create Table AuditData (ID int identity(1,1) Primary Key not null, AuditDate Datetime not null) Insert into AuditData Values(getdate() - (365*5)) ,(getdate() - (365*4)) ,(getdate() - (365*3)) ,(getdate() - (365*2)) ,(getdate() - (365*1)) ,(getdate()) ,(getdate() + (365*5)) ,(getdate() + (365*4)) ,(getdate() + (365*3)) ,(getdate() + (365*2)) ,(getdate() + (365*1)) --Check the data Select * From AuditData
The above code will create a table called AuditData and inserts a single row for every previous and upcoming 5 years from now. Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.
Check the partition and its data allocation
select Object_name(p.object_id) Table_Name ,(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name', partition_number, lv.value leftValue, rv.value rightValue,p.rows, s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name' from sys.partitions p join sys.allocation_units a on p.hobt_id = a.container_id join sys.indexes i on p.object_id = i.object_id Left join sys.partition_schemes s on i.data_space_id = s.data_space_id left join sys.partition_functions f on s.function_id = f.function_id left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id where p.object_id = object_id('AuditData')From the above, its clear that the index created on the table as part of Primary key has only one partition and all the data is part of that partition. Now, let us quickly convert this existing table as as partitioned table. To do so, we need to create partition function and partition schema as below. The function fn_AuditDate has been created on Datetime field so that in our example, we can create partition based on AuditDate.
--Create partition function on Datetime Create partition function fn_AuditDate (Datetime) as Range right for values('20150101', '20160101','20170101','20180101', '20190101','20200101') /*Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.*/ --Create partition Schema to associate the function Create partition scheme sc_AuditDate As Partition fn_AuditDate ALL to ([Primary])On successful execution, we will get the below Message:
Partition scheme ‘sc_AuditDate’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘sc_AuditDate’.
Recreate clustered index to make the partition column as part of clustered index
ALTER TABLE dbo.AuditData DROP CONSTRAINT [PK__AuditDat__3214EC2760DC1A18] GO ALTER TABLE dbo.AuditData ADD CONSTRAINT [PK__AuditDat__3214EC2760DC1A18] PRIMARY KEY NONCLUSTERED (ID) ON [PRIMARY] GO CREATE CLUSTERED INDEX IX_AuditData_AuditDate_ID ON dbo.AuditData (AuditDate) ON sc_AuditDate(AuditDate) GO
Now, it is important to make sure that the clustered index to be recreated on partition schema to partition the AuditData table. If you look at the above example, since the clustered index is created as part of Primary key creation, we need to drop the primary key and re create the primary key as non clustered index and create a separate clustered index on AuditDate. Note that the clustered index is created on partition that we created recently – sc_AuditDate.
Check the partition and its data allocation
select Object_name(p.object_id) 'Table_Name',(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name', partition_number, lv.value leftValue, rv.value rightValue,p.rows, s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name' from sys.partitions p join sys.allocation_units a on p.hobt_id = a.container_id join sys.indexes i on p.object_id = i.object_id join sys.partition_schemes s on i.data_space_id = s.data_space_id join sys.partition_functions f on s.function_id = f.function_id left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id where p.object_id = object_id('AuditData')
Now, you can see the clustered index has 7 partitions as per the range that we defined in the schema and function. The last partition contains all the data right to the range (eg: 2020/2021/2022/2023/2024/2025); 6 rows in our AuditData table.Hope this explains how to partition an existing table simply. But wait, that may not be so easy as I explained for your real time scenario. You may want to implement partition on a table that has lots of data/ multiple indexes/foreign key relations defined etc. We need to carefully evaluate the steps to reduce the down time during the implementation of partition. I would suggest partitioning a table (especially bigger ones) should be an offline activity rather than an online for better performance and easy implementation and testing.
Clean up is very important, so my test objects.
DROP TABLE AuditData DROP PARTITION SCHEME sc_AuditDate DROP PARTITION FUNCTION fn_AuditDate
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).
There are two types of conversions – implicit and explicit.
Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.
Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.
SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:
Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98' Set @uniqueidentifier = @varchar Select @uniqueidentifier
Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.
The word of caution
Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.
Recently, we have an issue, FULL TEXT search was not working with CONTIANSTABLE. My friend was searching for a word in a document where the table is enabled with FULL TEXT feature, however the search was not bringing any records.
My first thought was there could be a missing join condition that could not get the results. The actual query in the issue was really a big one with lots of tables and filters involved.So the first thing that we did remove all joins or conditions and see if CONTAINSTABLE works good, but that attempt was also failing. It was a clear indication that there is something wrong that it could not return results at FULL TEXT functionality.
For everyone to understand better, Let us go through a sample script to replicate the issue:
1. Create a table for our example
CREATE TABLE [DBO].[DOCUMENTTABLE]( [DOCUMENTID] [UNIQUEIDENTIFIER] NOT NULL, [DOCUMENT_CONTENT] [IMAGE] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE INDEX IX_DOCUMENTTABLE ON DOCUMETTABLE(DOCUMENTID)
2. Load some data into Table – We uploaded the same file in question which is a .xlsx type
3. Query table with CONTAINSTABLE
Select * From CONTAINSTABLE (DocumentTable, DOCUMENT_CONTENT,'reporting')
Msg 7601, Level 16, State 2, Line 2
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘DocumentTable’ because it is not full-text indexed.
4. Enable FULL TEXT Indexing on the table
5. While executing the query, it is observed again no data returned
Further, we decided to check the document and its content for the search word manually and found the word was present and the document was also the same “.xlsx”. It is to be noted that we saved the data in an IMAGE datatype as shown in the example. With that information, we wanted to know the supporting file types in our full text enabled table with the below query.
select document_type, path from sys.fulltext_document_types
The above query was not returning the document type – “.xlsx” and it clearly indicated that the reason why the document could not search the word as the document type was a non-supporting one. [DOCUMENTTABLE] is a table that enabled FULL TEXT and the datatype of the field “[DOCUMENT_CONTENT]” was IMAGE. Then, my analysis took me a very important information that if we need to index a document type for which a pre-installed iFilter does not exists at OS level, we need to install the appropriate iFilter in the OS and then load those into SQL Server for a successful search functionality.
How to install the iFilter and Load into SQL Server
1. Download the iFilter , the below supports a wide range of document types
(Please note that MSmight retire the above as latest version is released, you may need to check for the latest.)
2. Install the downloaded set
3. Load the installed OS resources to SQL Server as below.
exec sp_fulltext_service 'load_os_resources', 1; exec sp_fulltext_service 'verify_signature', 0; exec sp_fulltext_service 'Update_languages' exec sp_fulltext_service 'Restart_all_fdhosts'
Once the above steps are completed, we were able to query the word and the results were returned as expected.
Hope this helps you in a similar situation and it was a good lesson learned to me!
How to register iFilters
While configuring Change Data Capture on a database that is restored from different environment, we might end up with an error message as below:
Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.
To resolve the issue, we need to change the owner of database and then enable CDC for the database as below. Please note that, the database would have restored irrespective of the error. You just have to run the below statement to avoid similar issues in future.
EXEC sp_changedbowner 'sa' EXEC sys.sp_cdc_enable_db ;
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.
Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.
DECLARE @hex VARCHAR(64) = '0x80070643' Set @hex = Right(@hex,4) DECLARE @rez BIGINT; SELECT @rez = ISNULL(@rez,0) * 16 + CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1 FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex) /* --To use NET HELPMSG, need to enable xp_cmdshell as below: EXEC sp_configure 'show advanced options',1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'xp_cmdshell',1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'show advanced options',0 RECONFIGURE WITH OVERRIDE */ Declare @Table Table(WindowsErrorMessage nVarchar(MAX)) Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX)) Insert into @Table exec xp_cmdshell @s Select @hex HexaVal,@rez DecimalVal,* From @Table where WindowsErrorMessage is not null