Sql Server Error : An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

Interesting one today:

Occasionally, when we restore databases from backups, we need to fix orphan users. Recently, a few years ago, we discussed about fixing orphan users using script. Here we’ll look at a scenario, where the regular orphan users script will not work. 

Error Message:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214 [Batch Start Line 61]
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

The script “EXEC sp_change_users_login 'Auto_Fix', 'user'” only works for Sql Authentication based logins and not for Windows-level principals or non-login based users. See MSDN article for more details.

A user without login would look like this, in SSMS:

Sql Users Without Login

In such scenarios,

• sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
• sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : View or function has more column names specified than columns defined

Interesting one today:

In our development environment, sometimes we run into this error that’s a bit nebulous.

Error Message:
SQLException for SQL []; SQL state [S0001]; error code [4502]; View or function has more
column names specified than columns defined.;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
View or function has more column names specified than columns defined.

Occasionally, we make changes to the tables schema and when you have Views that depend on these tables, they become stale. While the definition is still intact, the meta-data becomes stale, so they need to be refreshed.

This refresh uncovers if there are any conflicts between view definitions and the underlying tables. 

--
-- Refresh View metadata 
--
EXEC sp_refreshview 'dbo.View_SomeTable'
GO 

Once the underlying conflicts are resolved, if any, the view will execute correctly from application. Just refreshing the meta-data helps sometimes.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : When was the last time a table was updated

Quick one today:

Sometimes we need to find out the last time a table was touched, either for INSERT/UPDATE or for READ. This is not a regular use case.

In any given second, a busy database processes thousands of transactions; so it a rare scenario where we’ll stop the activity to find the time when a table was touched.

While it is rare, the need arises, mostly in DEV & TEST environments, where we need to query the last time a table was updated.

The below T-SQL helps us uncover the details:

--
-- Create temporary table for the test
--
CREATE TABLE dbo.TempUpdate (ID INT, Place VARCHAR(20))
GO

--
-- Perform INSERT and check the timestamp
--
INSERT INTO dbo.TempUpdate (ID, Place) VALUES (1, 'Test')
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'

 

INSERT Timestamp

--
-- Perform UPDATE and check the timestamp
--
UPDATE dbo.TempUpdate SET Place = 'Test2'
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'
GO

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Updated Timestamp

Side Note: As notice, table scan was performed while performing UPDATE

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Enable a Sql Agent Job using T-SQL query

Quick one today:

Recently, we saw a time-saving technique in starting job using T-sql query.

Today, we’ll look at another one. This comes in handy when dealing with multiple jobs — where handling them is more efficient using T-SQL than UI. Even during deployments, where we need to disable jobs at the beginning of the deployment and then enable them at the end.

--
--	Enable a Sql Agent Job
--
USE msdb
GO
EXEC dbo.sp_update_job    @job_name = N'SomeJobName'
						, @enabled = 1
GO  

 

This is simple and elegant way to enable/disable one or many jobs at the same time, making interaction with SQL Server more efficient. Just query job names from

--
--    Get list of job names
--
SELECT * FROM msdb..sysjobs
GO

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Change Login Password using TSQL Query

Quick one today:

Occasionally, we need to change the password of a login within SQL Server. Following T-SQL template is handy query to carry out that operation.

--
-- Change login password using T-SQL
--
ALTER LOGIN [Login] 
WITH PASSWORD = 'new password'
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Replication Error: Specify all articles when subscribing to a publication using concurrent snapshot processing

Interesting one today:

In our lab environment, while setting up replication on a few nodes, we ran into this interesting error message.

Error:

 

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 15
Specify all articles when subscribing to a publication using concurrent snapshot processing.

 

At first glance, it did not make much sense — as we did not make any changes to the code. But upon further digging, it became obvious the minor change that was made. @sync_method parameters.

The section where we define the publication, looks like this:

Replication Add Publication

While defining the publication, along with all other parameters, we also mention the way in which the articles & data from Publisher is sent to each Subscriber. @sync_method defines, which approach to take.

The associated ‘add_subscription’ (which threw the error) looks like this:

Replication Add Subscription

Solution:

 

Bingo !!

When a Publication is defined with @sync_method as ‘concurrent‘, then it creates all articles/tables as one chunk of data (not as individual tables) in native mode. So we need to be sure to define @article parameter in ‘sp_addSubscription‘ with ‘all’ as value (and not individual table names) — since the articles are not available individually.

Replication Add Subscription

 

Viola !! Now the add subscription works !!

 

Side note:

 

If you want to add individual tables/articles in the ‘sp_addsubscription‘, then you want to consider using ‘native‘ for @sync_method.

Replication Add Publication

Replication Add Subscription

Hope this helps,

_Sqltimes

Read Full Post »

DBCC CHECKDB Error Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.

Interesting one today:

On a lab machine, when CHECKDB was executed, this seemingly tricky error popped up. This was a lab machine, and there was no traffic or any one connected to it.

Code that we ran:

--
-- CHECKDB error
--
DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
	, TABLOCK
GO

Error Message:

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to 
perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot 
could not be created and the database or table could not be locked. 
See Books Online for details of when this behavior is expected and what workarounds 
exist. Also see previous errors for more details.

After checking that there was no traffic or any connections to the database, I re-ran the same CHECKDB a few more times and still ran into the same error.

Reading this MSDB article, some ideas came to mind. The two conditions mentioned in the article are not the root of the problem.

• At least one other connection is using the database against which you run the DBCC CHECK command.
• The database contains at least one file group that is marked as read-only.

Once the TABLOCK is removed, the command worked.

DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
GO

 

Hope this helps,
_Sqltimes

 

 

Read Full Post »

Sql Server : Split String using T-SQL

Interesting one today:

In Sql Server, for a long time, we’ve needed a quick way to split a string based on some delimiter. Starting Sql Server 2016, a new table-valued function was introduced called STRING_SPLIT.

Simple Example: Split a string to return a table

--
-- Split string into a table
--
SELECT * 
FROM string_split('Apple, Pineapple, Orange, Cherries', ',')
GO

DECLARE   @Delimiter VARCHAR(1) = ','
		, @SourceString	VARCHAR(100) = 'Apple, Pineapple, Orange, Cherries'
SELECT * 
FROM string_split(@SourceString, @Delimiter)
GO

String Split

NOTE: A minimum of Compatibility level 130 is required for this feature to be available.

JOIN with other tables:

Also, in situations where you want to convert the string and join with another table at the same time, use CROSS_APPLY

Let’s take the below code as example:

--
-- JOIN string_split with another table
--
CREATE TABLE dbo.TestTable(
	  ID			TINYINT			NOT NULL IDENTITY(1,1)
	, FoodType		VARCHAR(10)
	, SampleValues	VARCHAR(100)
)
GO

INSERT INTO dbo.TestTable (FoodType, SampleValues) 
VALUES ('Fruit', 'Apple, Pineapple, Orange, Cherry, Tomato')
	 , ('Vegetable', 'Carrot, Okra')
GO

SELECT * 
FROM dbo.TestTable

SELECT ID, FoodType, T.value
FROM dbo.TestTable
CROSS APPLY string_split(SampleValues, ',') AS T
GO

Join String Split

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server: Query to check Database Mail was successful

Quick one today:

While not used every time or ever where, Database Mail is a valuable tool in Sql Server. It elevates any in-house monitoring solutions — and provides a key piece to send alert emails. Today, we’ll look at how to check if the emails queued are sent or failed.

--
-- Check the status of emails in MSDB
--
SELECT mailitem_id, subject, recipients, send_request_date, send_request_user, sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC
GO

DB Mail Status

There are more system tables that helps capture the status of unsent, failed & sent emails.

--
-- Other system tables to check status of emails in MSDB
--
SELECT * 
FROM msdb..sysmail_sentitems
GO
SELECT * 
FROM msdb..sysmail_unsentitems
GO
SELECT * 
FROM msdb..sysmail_faileditems
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : How to start a Sql Agent Job using T-SQL query

Quick one today:

A quick technique to run Sql Agent job without using UI has come in handy several times in the past, so it deserves a post by itself.

--
--    Start a Sql Agent Job
--
USE msdb
GO

EXEC dbo.sp_start_job N'JobName'
GO

This is simple and elegant way to run one or many jobs at the same time making
interactions with SQL Server more efficient. Just query job names from
--
--    Get list of job names
--
SELECT * FROM msdb..sysjobs
GO

Hope this helps,
_Sqltimes
					
Read Full Post »