Problem
While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.
Problem
While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to find the failover time and node name where SQL Server was running before the failover over.
There will be situation while creating the database diagram, you got the below error message.
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
Above error is self explanatory where it is stating that the “set the database owner to valid login” To fix the issue please follow below one of the solution.
Solution 1:
Execute the below script by mentioning the database name.
ALTER AUTHORIZATION ON DATABASE::MentionDatabaseName TO sa GO
OR, you can change the owner by executing the below query.
EXEC sp_changedbowner 'sa'
Solution 2:
Right Click on the database -> Database Properties -> click on files page -> change the owner to SA
When designing tables, It is a good practice of having one column that is unique and can be a primary key. You can include one of the below type column as Primary Key
– Add Auto Increment Column
– Identify the column which unique for all the rows
Make sure Primary keys should be as small as necessary. Prefer a numeric data type because numeric types are stored in a much more compact format than character formats. Most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache.
You can execute below script to identify the tables without Primary Key and add the Primary Key into tables as per the above suggestions..
Use <Database Name> SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name] FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 Order by name GO
Problem: Recently one of my user reported the issue that, He is getting password change message while login to SQL Server. But he does not want to change the password as he has already configured at the many places. While turning off the Password Expiration message, I got the below error.
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)
Solution:
To fix the issue you MUST have to change the password first without changing in password policy options. The good thing it that you can change the password to be the same as the existing one. -:)
But still you have to change the password -:) what a logic!!! You can do it from SSMS GUI as well, but I always prefer to use the scripts as it is giving me flexibility of customization.
That was the another reason I am in the support of open source software you have flexibility to add your ideas. Let’s go to fix the issue.
In the below step I am giving the same password, to fix “User Must Change Password” flag.
USE Master GO ALTER LOGIN [Login Name] WITH PASSWORD = 'Same Password' GO
Now let’s turn off the passowrd policy.
ALTER LOGIN [Login Name] WITH
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
aiDeeva.com 