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 Trace Flags 1211 and 1224

Interesting one today:

A while ago, we looked at the lock escalation patterns within Sql Server. Today, we’ll cover a couple of trace flags that impact that lock escalation. Thanks to my friend Bob for this idea.

• Trace Flag 1211
• Trace Flag 1224

Trace Flag 1211: 

In short, this flag prevents Sql Server from escalating locks under any circumstances.

Within Sql Server, as we saw in the previous article, Sql Server prefers to carry out the work with least number of locks at the least level of locks. If work could be carried out with Row Locks, then Sql Server would issue necessary row locks on the records and carry out the task. When the number of row locks goes beyond a threshold, then it escalates to page-lock or table lock.

This default behavior could be altered with the trace flag 1211. When we enable this trace flag, Sql Server will not escalate locks. If it has row locks, it will carry out the work with row-locks, even if more and more row locks are needed (and even if its efficient to carry out the operation with fewer page locks or a single table lock).

More locks causes more memory pressure — as each lock takes certain amount of memory space. So even under memory pressure, it will work within current lock levels. If and when it runs out of memory, it issue an error (1204). 

This is a powerful trace flag, so its better to not use it. There is a better alternative. Trace Flag 1224.

Trace Flag 1224: 

In short, Sql Server prefers to complete the operation under current lock-level, but if that incurs memory pressure, then it escalates locks. This is a much better way to handle lock escalation than trace flag 1211.

Note: If both the flags are enabled, then trace flag 1211 takes precedence over 1224. So there will not be any lock escalation even under memory pressure.

Note: Use this script to enable or disable a trace flag or to check trace flags status. 

Hat tip to Bob for the idea.

 

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 : CPU TIME vs. Elapsed Time

Interesting one today:

When we run a query with SET STATISTICS TIME ON, we see CPU Time and Elapsed Time statistics for a query. What is the difference?

SET STATISTICS ON

 

CPU Time is amount of CPU cycles (in time) spent by the workload thread on a particular processor/CPU. This is not an indication of the duration since the query began running on that CPU (there are some nuances — we’ll cover below)

Elapsed Time is the total duration of that thread, the time it took from start to end.

Simple Example:

Let’s say there is a workload thread for a simple query. The query runs for a bit (let’s say 2 seconds) and gets put on hold for a bit (let’s say 5 seconds), and then runs again (let’s say for 1 second) all the way to completion. The time the thread actually spent on CPU is counted towards CPU time (the two times, 2 & 1 seconds — 3 seconds total). Elapsed time is the total duration from when the query was issued to be executed to the time the result was generated — total 8 seconds.

Parallel Processing:

Let’s say there are 4 thread carrying out the workload of a query. Each run on a processor for 1 second and complete all the work in one shot.

Total CPU Time = 4 * 1 = 4 seconds (4 CPU’s with 1 second each)

Elapsed Time = 1 second (from start to finish)

Summary

CPU Time and Elapsed Time measure different time metrics for the same query. One measure the CPU cycles spent (in time) and the other measures the duration. The difference is important in figuring out what problem we are trying to solve.

1. If a query has high Elapsed Time, but low CPU Time, that means, there is some CPU contention or other resource contention (waiting for all resources to be available before the query could be given a CPU).
   1. We want to look for ways to optimize the the way query is written — is it requesting for more resources (rows) than it needs. End user will feel the pain in waiting — if this happens to majority of the queries. 
   2. Adding to this, if the CPUs are all busy all the time, that definitely means we need bigger CPUs for the workload or optimized queries.
   3. Conversely, if the CPU’s are not too busy at this time, that means, the thread is just waiting for other resources (concurrency — locked tables) to be released for this query to progress. Optimize the queries.
2. If CPU Time is higher than Elapsed Time, then there is good amount of parallelism in play.
   1. As long as the CPUs are optimally utilized, this is not a problem, but if you notice more than 80% utilization all the time (the threshold is even lower if they are virtual CPUs), then we need to add extra CPUs to alleviate the situation.
   2. This may not be a problem right now, but it will evolve into a problem soon. One extra long query will snowball everything into high delays — to the point where a user will notice the delays. 

 

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 »
				

Sql Server: Multiple ways to query definition of procedures, functions, views, etc

Interesting one today:

In one of the recent posts, we saw a technique to query definition of several database objects using sys.sql_modules. As with anything in Sql Server, there are more than one ways to accomplish the same task.

Today, we’ll uncover a couple more.

Object_Definition Function:

This is one of the easiest way to query an object definition. This system function provides definitions of several database objects

• Check Constraint
• Default
• Stored Procedure
• Functions
• Rule
• Trigger
• View
• etc (yes there are more)

Example:

--
-- Query definition of database objects
--
--
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeProcedure'))
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeView'))
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeTrigger'))
GO

HelpText Procedure

This one goes even further; sp_helptext procedure helps us query definitions of system objects as well, along with the list in above section.

• Rule
• Default
• CHECK Constraint
• Stored Procedure
• Function
• Trigger
• View
• System Objects
• etc (yes there are more)

Example:

--
-- Query object definition
--
EXEC sp_helptext 'SomeProcedure'
GO
EXEC sp_helptext 'SomeTrigger'
GO
EXEC sp_helptext 'SomeView'
GO

Sql_modules system table:

In a recent post, we saw more details, so this will be just a short mention (for completeness), for more details please refer to the old article here.

 

Query objects using object name (but not table name):

Example:

--
-- Query objects using object name (but not table name)
--
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE O.name LIKE '%Object_Name%'
OR O.name LIKE '%Object_Name_but_not_table_name%'
GO

Query objects using any object name, including table name:

 
-- 
-- Query objects using any object name in the definition (including table name)
-- 
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE M.definition LIKE '%ViewName%'
OR M.definition LIKE '%TableName[.]%'
GO

More information is available on MSDN

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Get table row count without COUNT(*). Non invasive

Quick one today:

Quite frequently, we need to get a sense of the size of tables in our databases. While the default approach is running a COUNT(*) query, which is too invasive on the tables, there are better ways.

Sql Server always knows more about the objects in its databases than us. So its to our advantage to use some of those hidden gems.

Drumroll !!

Enter system table sys.sysindexes. It has loads of data about each table and its indexes. Below is a query that gets row count for all tables while ignoring many system tables. 

Three Options:

1. Query row counts on all tables including system tables
2. Query row counts on all user tables
3. Query row counts on a given user table

 

Query row counts on all tables including system tables

Query all tables in the database:

--
--  Query row count on all tables
--
SELECT OBJECT_NAME(id), rowcnt 
FROM sys.sysindexes 
WHERE indid <= 1 
ORDER BY rowcnt DESC
GO

 

Query row counts on all tables including system tables

Query all user tables

--
--  Query row count on user tables without running COUNT(*)
--
SELECT OBJECT_NAME(id), rowcnt
FROM sys.sysindexes
WHERE indid <= 1
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND OBJECT_NAME(id) NOT LIKE 'queue%'
AND OBJECT_NAME(id) NOT LIKE 'sqlagent%'
AND OBJECT_NAME(id) NOT LIKE 'plan%'
AND OBJECT_NAME(id) NOT LIKE 'filestream%'
AND OBJECT_NAME(id) NOT LIKE 'filetable%'
ORDER BY rowcnt DESC
GO

 

Query row counts on all tables including system tables:

If you want to filter it down further, just add a filter for your table.

--
--  Query row count on MyTable (particular table)
--
SELECT OBJECT_NAME(id), rowcnt
FROM sys.sysindexes
WHERE indid <= 1
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND OBJECT_NAME(id) NOT LIKE 'queue%'
AND OBJECT_NAME(id) NOT LIKE 'sqlagent%'
AND OBJECT_NAME(id) NOT LIKE 'plan%'
AND OBJECT_NAME(id) NOT LIKE 'filestream%'
AND OBJECT_NAME(id) NOT LIKE 'filetable%'
AND OBJECT_NAME(id) LIKE '%MyTable%'       -- put your table name here
ORDER BY rowcnt DESC
GO

 

 

Hope this helps,

_Sqltimes

Read Full Post »