Mashed Articles

BOL says that this function returns the base data type and other information about a sql_variant value. Have you ever looked what is datatype of GETDATE() or perhaps DB_ID() function or what is the data type of SYSTEM_USER??

DECLARE @var sql_variant
SET @var =GETDATE() –SYSTEM_USER
SELECT SQL_VARIANT_PROPERTY(@var , ‘BaseType’) as BaseType
, SQL_VARIANT_PROPERTY(@var , ‘Precision’) as Precision
, SQL_VARIANT_PROPERTY(@var , ‘Scale’) as Scale
, SQL_VARIANT_PROPERTY(@var , ‘TotalBytes’) as TotalBytes
, SQL_VARIANT_PROPERTY(@var , ‘MaxLength’) as MaxLength
, SQL_VARIANT_PROPERTY(@var , ‘Collation’) as Collation;

I found that very usefull as you can easily retun the basic info about even system objects and how SQL Server interpetes it.

Hi folks. I would like to share with you the following simple script to show us jobs duration report in SQL Server 2005. I manipulated with INTERGER values stored by SQL Server to convert them into DATETIME/CHAR(8) datatypes to represent the data.
Thanks to SQL Server MVP Peter Ward provided me with StartTime calculation.

WITH job_duration_view
AS
(
SELECT name,
StartTime = CONVERT(DATETIME, RTRIM(last_run_date)) +
(last_run_time * 9 + last_run_time % 10000 * 6 + last_run_time % 100 * 10 + 25 * last_run_duration) / 216e4 ,
CONVERT(CHAR(8),DATEADD(ss,last_run_duration,CAST(last_run_date AS CHAR(8))),114)
AS duration
FROM msdb.dbo.sysjobservers js
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE last_run_date >0 AND last_run_time >0
) SELECT name AS job_name,StartTime,
StartTime -’19000101′+Duration AS EndDate ,Duration
FROM job_duration_view

Hi folks.
I named this article with this pretty famous error message. I’m sure that everybody has seen this error at least once.This week I visited our client who asked me very intresting question. They have a table with many columns that most of them defined as VARCHAR(n) datatype. One of the query has been failing with below error.

“Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.”

Well ,we knew for sure that the user supplied a string which does not match with column datatype but real question is WHICH of so many columns? As I said, they have more than 50 columns and it was pretty difficult to identify on which column is failed.
My point is that it would be nice to have more information from this error message about which column is failing and I hope that MS will do something for the matter.