One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.
create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go
SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;
Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.
Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”
SA login account and the few policy certificates also come in SQL Login type, I have excluded them.
select * from sys.server_principals where name = 'sa' or substring(name, 1, 1) = '#'
select 'DROP LOGIN ' + [name] from sys.server_principals where type_desc = 'SQL_LOGIN' and sid <> 0x01 and substring(name, 1, 1) <> '#'
Next step is to execute the output of the above query.
Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.</code
Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.
Solution: You can check the list of port number used by the SQL Server instances using one of the below way.
Soln 1# Using SQL Server Configuration Manager
Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll
SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Soln 3# Error Log
Query the error log as below to get the port number.
EXEC xp_readerrorlog 0,1,”Server is listening on”,Null
Soln 4# Command Prompts
Execute the below command from the command prompt.
Netstat -abn
There are many scenarios where you have been alerted for the replication failure and you have to troubleshoot the issue. In this article I will guide you what should be your approach to get the detailed error message and transaction details in replication.
First check the replication monitor and click on the failed publisher. Next step is double click on the failed subscriber from All Subscriptions list.
Now next step is click on the error and check its description.
Error :
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000044100002D93000100000000, Command ID: 1)
From the above error message we have to identify which command is failed to execute on the subscriber.
To get the exact command, find out the distributer server and distribution database for the failed publisher.
Once you get the distribution database server, execute the below query against the distribution DB.
use distribution go SELECT * FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D93000100000000 AND command_id = 1
Once you execute the above query against the distribution database, you will get the more information about the error, for example Publisher database ID, Article ID and much more…
We have to use the above details, to get the exact command using either SP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column in msrepl_commands table.
We will check both the alternatives.
Using SP_BROWSEREPLCMDS
Please note CLR must be enabled for to use this procedure.
EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x0000044100002D930001', @xact_seqno_end = '0x0000044100002D930001', @publisher_database_id = 1033, @article_id = 12, @command_id= 1
By casting command column in msrepl_commands table
Please note if you want to see the better output use the Result to Text as output in SSMS (CTRL + T)
SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D930001 AND command_id = 1
Now you got the exact SQL Command. As a next step check the objects from both the publisher and the subscriber to see the violation of the keys or do the data comparisons etc.
