Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for the ‘Common’ Category

checkDB detail

Posted by Simon Cho on 01/25/2016

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

 

http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

 

Thanks, Paul

Posted in Common | Leave a Comment »

understading status column of sysfiles and sysaltfiles view

Posted by Simon Cho on 01/04/2016

First of all, let’s take a look system view

sp_helptext ‘sys.sysfiles’
sp_helptext ‘master.dbo.sysaltfiles’

<Sys.sysfiles – SQL2014>

CREATE VIEW sys.sysfiles AS
SELECT
fileid = convert(smallint, fileid & 0x7fff),
groupid = convert(smallint, grpid),
size = isnull(FilePropertyById(fileid, ‘size’), size),
maxsize, growth,
status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
name = lname,
filename = pname
FROM sys.sysprufiles
WHERE filetype IN (0, 1) — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)
AND filestate NOT IN (1, 2, 3) — x_efs_Dummy, x_efs_Dropped, x_efs_DroppedReusePending

 

<master.dbo.sysaltfiles – SQL2014>

CREATE VIEW sys.sysaltfiles AS
SELECT fileid = convert(smallint, f.fileid & 0x7fff),
groupid = convert(smallint, f.grpid),
f.size, f.maxsize, f.growth,
status = convert(int,
case f.filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (f.status & 8) * 2 — FCB_READONLY_MEDIA
+ (f. status & 16) * 256 — FCB_READONLY
+ case when f.filestate in (1, 2, 3, 6) then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (f.status & 256) * 2097152 — FCB_SPARSE_FILE
+ (f.status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
dbid = convert(smallint, f.dbid),
name = f.lname, filename = f.pname
FROM master.sys.sysbrickfiles f
WHERE f.filetype IN (0, 1) AND has_access(‘MF’, 1) = 1 — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)

 

You can find out the source code looks like below for status

status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH

 

This is bit operation. So, it need to convert back using same operation.

SELECT *
, status
, status & 2 AS [FCB_DSK_DEVICE]
, status & 66 AS [FCB_LOG_DEVICE]
, status & (8 * 2) AS [FCB_READONLY_MEDIA]
, status & (16 * 256) AS [FCB_READONLY]
, status & (268435456) AS [OFFLINE, FCB_OFFLINE]
, status & (256 * 2097152) AS [FCB_SPARSE_FILE]
, status & (32 * 32768) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

This query equivalent with below query

/*
SELECT CONVERT(VARBINARY(8), 2)
SELECT CONVERT(VARBINARY(8), 66)
SELECT CONVERT(VARBINARY(8), (8 * 2))
SELECT CONVERT(VARBINARY(8), (16 * 256))
SELECT CONVERT(VARBINARY(8), 268435456)
SELECT CONVERT(VARBINARY(8), (256 * 2097152))
SELECT CONVERT(VARBINARY(8), (32 * 32768))
*/
SELECT *
, status
, status & 0x2 AS [FCB_DSK_DEVICE]
, status & 0x42 AS [FCB_LOG_DEVICE]
, status & 0x10 AS [FCB_READONLY_MEDIA]
, status & 0x1000 AS [FCB_READONLY]
, status & 0x10000000 AS [OFFLINE, FCB_OFFLINE]
, status & 0x20000000 AS [FCB_SPARSE_FILE]
, status & 0x100000 AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

BOL saying “0x40 = Log file.” since this is bit operation.

I realized it. It’s due to “FCB_DSK_DEVICE” included. So, 0x40+2 = 0x42.

So, 0x40 is for FCB_LOG_DEVICE

https://msdn.microsoft.com/en-us/library/ms178009.aspx

 

Final query

SELECT *
, status
, CONVERT(BIT, status & 0x2) AS [FCB_DSK_DEVICE]
, CONVERT(BIT, status & 0x40) AS [FCB_LOG_DEVICE]
, CONVERT(BIT, status & 0x10) AS [FCB_READONLY_MEDIA]
, CONVERT(BIT, status & 0x1000) AS [FCB_READONLY]
, CONVERT(BIT, status & 0x10000000) AS [OFFLINE, FCB_OFFLINE]
, CONVERT(BIT, status & 0x20000000) AS [FCB_SPARSE_FILE]
, CONVERT(BIT, status & 0x100000) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

 

 

 

Posted in Common | Tagged: , | Leave a Comment »

Between 4096 and 512 sector size issue.

Posted by Simon Cho on 11/16/2015

There are 2 issues in case of primary server has 512 sector size, and secondary server has 4096 sector size

  • You may see below message in the SQL server error log

There have been 170613760 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file

  • If you are using Log Shipping with standby mode on secondary server, standby mode may fail frequently.

Msg 9004, Level 16, State 6, Line 7
An error occurred while processing the log for database ‘MessageExchange_Sanpedro’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

 

Please check 4k and 512 basic concept in below url.

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx

 

Solution

https://support.microsoft.com/en-us/kb/3009974 : I don’t know how it work internally. It might force the primary to 4K alignment. Please fully test it out and apply it.

Note After you apply this hotfix, you have to turn on the trace flag 1800 to make this hotfix work correctly.

https://support.microsoft.com/en-us/kb/2987585 : This one is great. Need to turn on trace flag 3057

 

Thanks Bob ward and Robert Dorr for investigation.

Posted in Common | Tagged: , , , , | Leave a Comment »

Speak at Huntington Beach 4/11/2015

Posted by Simon Cho on 08/20/2015

download link

SQLSaturday_HuntingtonBeach_20150411

Posted in Common | Leave a Comment »

SSIS package unexpected Termination

Posted by Simon Cho on 07/08/2015

SQL server 2012 or 2014.

1. Please check service pack first.

SQL server 2012
https://support.microsoft.com/en-us/kb/2837964

SQL server 2014
https://support.microsoft.com/en-us/kb/2993859

 

2. If you have still problem, please check that memory pressure. You may can find warning on event log.

<Unexpected termination>

Unexpected_Termination

Unexpected_Termination

<EventLog>

Unexpected_termination_EventLog

Unexpected_termination_EventLog

Posted in Common | Tagged: , , | Leave a Comment »

AlwaysOn AG group status check

Posted by Simon Cho on 06/15/2015

SELECT replica_server_name
, d.role_desc
, d.connected_state_desc
–, endpoint_url
–, availability_mode_desc
–, failover_mode_desc
–, session_timeout
–, backup_priority
–, secondary_role_allow_connections_desc AS Secondary_Readable
, Pri_Check.pri_Status
–, Pri_Check.Sec_Status
–, Pri_Check.Sync_Status
, d.operational_state_desc
, d.recovery_health_desc
, d.synchronization_health_desc AS Sync_Status
, d.last_connect_error_description AS ErrorMsg
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
FROM sys.availability_replicas r
OUTER APPLY (
SELECT 1 AS IsPrimary
, c.ip_address AS listnerIP
, c.state_desc AS listnerStatus
, a.primary_recovery_health_desc AS pri_Status
, a.secondary_recovery_health_desc AS Sec_Status
, a.synchronization_health_desc AS Sync_Status
FROM sys.dm_hadr_availability_group_states a
JOIN sys.availability_group_listeners B
ON A.group_id = B.group_id
JOIN sys.availability_group_listener_ip_addresses c
ON b.listener_id = c.listener_id
WHERE primary_replica = r.replica_server_name
) Pri_Check
JOIN sys.dm_hadr_availability_replica_states d
ON r.replica_id = d.replica_id
/*
WHERE d.role NOT IN (1,2) — 1:Primary, 2:Secondary, 0:Resolving
OR d.operational_state <> 2 –0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
OR d.recovery_health <> 1 –0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
OR d.synchronization_health <>2 — 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
— 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
— 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
OR d.connected_state <> 1 –0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
— On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
— On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
— 1 Connected
OR d.last_connect_error_number IS NOT NULL
*/

Posted in Common | Leave a Comment »

SQL 2014 service pack for SP1 or CU7?

Posted by Simon Cho on 06/15/2015

https://support.microsoft.com/en-us/kb/2936603

SQL service pack SP1 is announced.

https://support.microsoft.com/en-us/kb/2936603

And CU7 and SP1 both of the latest build for now.

 

The problem is, SP1 has higher version. But, it looks not containing CU6 and CU7.

CU6 and CU7 include very critical KB as well.

You may not want to apply SP1. Please check CU6 and CU7 first.

 

 

ServicePack VSTS bug number KB article number Description Fix area
CU6 4067265 3016165 FIX: Arithmetic overflow error occurs when you add manually initialized subscriptions for publication in SQL Server SQL service
CU6 4067300 3021757 FIX: Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function SQL service
CU6 3986465 3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014 SQL performance
CU6 3915402 3025845 FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014 SQL service
CU6 4067260 3026082 FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014 SQL service
CU6 4067263 3026083 FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014 SQL service
CU6 3856439 3029762 FIX: Access violation occurs when you delete rows from a table that has clustered columnstore index in SQL Server 2014 SQL service
CU6 4067312 3029825 FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014 SQL performance
CU6 3885366 3030041 FIX: Error occurs when you connect to the database engine after you install CU4 for SQL Server 2014 Setup & Install
CU6 3749961 3030619 FIX: Incorrect data returned when you use DATE data type as a qualifier in a query in SQL Server 2014 SQL service
CU6 4072235 3034615 FIX: Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014 SQL service
CU6 4045776 3034679 FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING High Availability
CU7 4210782 3042544 FIX: A query that requires nested loops join takes longer to complete in SQL Server 2014 SQL performance
CU7 4326599 3044958 FIX: Rollback recovery on a snapshot fails when you run DBCC CHECKDB and then SQL Server shuts down unexpectedly SQL service
CU7 4326597 3032476 FIX: Memory leak in USERSTORE_SCHEMAMGR and CPU spikes occur when you use temp table in SQL Server 2012 or 2014 SQL service
CU7 4336264 3037624 FIX: Complex parallel query does not respond in SQL Server 2012 or SQL Server 2014 SQL performance
CU7 4265652 3042370 An AlwaysOn secondary replica crashes or raises error 3961 when the AlwaysOn database has CLR UDT in SQL Server 2014 SQL service
CU7 4326600 3042135 FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014 SQL service
CU7 4056944 3048752 FIX: A SELECT query run as a parallel batch-mode scan may cause a deadlock situation in SQL Server 2014 SQL service
CU7 4302739 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 SQL connectivity
CU7 4329649 3048856 FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014 SQL service
CU6 4067280 3011465 FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure SQL service

 

Posted in Common | Leave a Comment »

Parallelism

Posted by Simon Cho on 02/24/2015

http://blogs.msdn.com/b/craigfr/

Posted in Common | Leave a Comment »

misaligned log ios which required falling back to synchronous io between 4096 and 512e

Posted by Simon Cho on 02/13/2015

https://kb.netapp.com/support/index?page=content&id=1010881
https://kb.netapp.com/support/index?page=content&id=3011193
https://kb.netapp.com/support/index?page=content&id=1014111
http://www.netapp.com/us/system/pdf-reader.aspx?m=tr-3428.pdf&cc=us
http://www.netapp.com/us/system/pdf-reader.aspx?m=tr-3747.pdf&cc=us
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1009658

http://blog.jgriffiths.org/?p=592
https://www.vmware.com/support/developer/vddk/vmdk_50_technote.pdf?src=vmdk
http://www.randomnoun.com/wp/2013/09/12/determining-the-file-at-a-specific-vmdk-offset/

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx
https://msdn.microsoft.com/en-us/library/windows/desktop/hh182553(v=vs.85).aspx
http://support.microsoft.com/kb/982018?wa=wsignin1.0
https://msdn.microsoft.com/en-us/library/windows/desktop/hh182553(v=vs.85).aspx
http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx
https://technet.microsoft.com/en-us/sqlserver/aa365683(v=vs.100).aspx
http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx
https://msdn.microsoft.com/en-us/library/windows/desktop/hh848035(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/cc966412.aspx#EEAA
http://sqlcommunity.com/SQL-TEAMS/SAP-on-SQL
https://kb.netapp.com/support/index?page=content&id=1010803&actp=search&viewlocale=en_US&searchid=1423875704148

Posted in Common | 1 Comment »

SQL Saturday Presentation file – 9/20/2014

Posted by Simon Cho on 01/17/2015

Please download file from below link.

link

 

2014-09-20_SQLSat_Sandiego

Posted in Common | Leave a Comment »

SQL.la Presentation file – Transaction 1/15/2015

Posted by Simon Cho on 01/17/2015

 

 

Please download from below link to get presentation and Demo file.

Link
2015-01-15_SQL.LA

 

 

 

 

 

Posted in Common | Leave a Comment »

SQL Saturday – Presentation(Transaction) – San Diego 9/20

Posted by Simon Cho on 09/22/2014

This is first presentation in public.

I choose SQL Saturday in San Diego.

Here is Presentation file. Transaction

You can download it from this url as well.

http://www.sqlsaturday.com/viewsession.aspx?sat=340&sessionid=25091

Please download it. And test and use it as own your risk :)

Over all, it’s ok on production environment as well. But, Speaker do not grantee it on your system.

As I explained in there is no Generic best practice Stored Procedure template.

It need to change based on your business logic.

Posted in Common | Tagged: , | Leave a Comment »

Introduce Desired State Configuration in Windows Server 2012

Posted by Simon Cho on 08/08/2014

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/MDC-B302

Posted in Common | Tagged: , | Leave a Comment »

Basic powershell function call

Posted by Simon Cho on 08/08/2014

http://stackoverflow.com/tags/powershell/info

 

Common Gotchas

Executing EXEs via a path with spaces requires quoting the path and the use of the call operator – &

C:\PS> & 'C:\Program Files\Windows NT\Accessories\wordpad.exe'

Calling PowerShell functions does not require parenthesis or comma separated arguments. PowerShell functions should be called just like a cmdlet. The following examples demonstrates the problem caused by this issue e.g.:

C:\PS> function Greet($fname, $lname) {"My name is '$lname', '$fname' '$lname'"}
C:\PS> Greet('James','Bond') # Wrong way to invoke this function!!
My name is '', 'James Bond' ''

Note that both ‘James’ and ‘Bond’ are packaged up as a single argument (an array) that is passed to the first parameter. The correct invocation is:

C:\PS> Greet James Bond
My name is 'Bond', 'James' 'Bond'

Note that in PowerShell 2.0, the use of Set-StrictMode -version 2.0 will catch this type of problem.

Posted in Common | Tagged: , | Leave a Comment »

DBCC help(‘?’) want to see all hidden DBCC command

Posted by Simon Cho on 07/22/2014

SQL 2005

DBCC HELP(‘?’)
DBCC TraceOn(2580)
DBCC HELP(‘?’)

SQL 2008 and above

DBCC HELP(‘?’)
DBCC TraceOn(2588)
DBCC HELP(‘?’)

 

Posted in Common | Tagged: , , , | Leave a Comment »

DBCC SHRINKFILE isn’t working even simple recovery mode.

Posted by Simon Cho on 07/15/2014

Log file Shrink has several dependency.

1. Log backup(or Checkpoint in simple recovery mode)

2. Replication

3. Mirroring

4. VLF

#1,2,3 can be check by this script

Declare @DBName varchar(255) = ‘test’
select log_reuse_wait_desc
from sys.databases where name= @DBName

So, most case, it’s ok.
But, only #4 can’t be checked by above script.

Please use this file to be able to check “StartOffset” for second VLF since LDF required minimum 2 VLFs.

DBCC LOGINFO

If it’s too big, need to refresh VLF.

big_Offset_VLF

 

 

 

 

But, unfortunately, refresh VLF isn’t simple.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://technet.microsoft.com/en-us/library/aa933049%28v=sql.80%29.aspx
http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/
http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/

Ref. Log_reuse_wait (http://msdn.microsoft.com/en-us/library/ms178534.aspx)
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)

Posted in Common | Tagged: , , , | Leave a Comment »

SQL Agent Job server.(Centralized job server)

Posted by Simon Cho on 07/03/2014

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=315

http://msdn.microsoft.com/en-us/library/ms180992(v=sql.120).aspx

 

Automated Administration Across an Enterprise

 

Automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:

  • Manage two or more servers.
  • Schedule information flows between enterprise servers for data warehousing.

 

To take advantage of multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.

The following illustration shows the relationship between master and target servers:

Multiserver administration configuration

If you administer departmental servers across a large corporation, you can define the following:

  • One backup job with job steps.
  • Operators to notify in case of backup failure.
  • An execution schedule for the backup job.

 

Posted in Common | Tagged: , , , , | Leave a Comment »

Drop certain query plan

Posted by Simon Cho on 07/02/2014

DECLARE @ObjName varchar(255)
SET @ObjName = ‘abc’
SELECT c.usecounts
, c.cacheobjtype
, c.objtype
, c.size_in_bytes
, t.text
, p.query_plan
, DB_NAME(p.dbid) AS DBNAME
, p.encrypted
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE usecounts > 1 and t.text like ‘%’+@ObjName+’%’
AND t.text not like ‘%sys.dm_exec_cached_plans%’
GO

–DBCC FREEPROCCACHE (plan_handle);
–ex) DBCC FREEPROCCACHE (0x05000C00F01C44754021DDDA040000000000000000000000);
GO

Posted in Common | Tagged: , , | Leave a Comment »

Alert email sending

Posted by Simon Cho on 06/30/2014

Email send when @objName has data. @objName should include DB name as well.
Ex) @objName = ‘DBA.dbo.vw_Job_Status’

CREATE PROCEDURE [dbo].[dmp_send_email_HTML_format]
@objName VARCHAR(255)
, @Subject VARCHAR(255)
, @PreDescription varchar(8000) = NULL
, @Recipients VARCHAR(8000)
, @importance VARCHAR(255) = ‘High’
, @profile VARCHAR(255) = ‘Public_Profile’ –eMail profile name
as
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*
Return value @r
10001 : object is wrong
10002 : column is wrong
0 : Done or Nodata.
*/

DECLARE @tableHTML NVARCHAR(max)
, @HTML_TH NVARCHAR(max)
, @HTML_TD NVARCHAR(max)
, @sql NVARCHAR(4000)
, @param NVARCHAR(4000)
, @DBName VARCHAR(255)
, @rowcnt INT
, @r INT

DECLARE @tbl_datacheck TABLE (i INT)

SET @DBName = PARSENAME(@objName,3)

SET @r = -1

IF OBJECT_ID(@objName) IS NULL BEGIN
SET @r = 10001
GOTO ERROR;
END

IF @DBName = ” OR @DBName IS NULL BEGIN
SET @DBName = DB_NAME()
END
SET @sql = ‘select top 1 1 from ‘+ @objName

INSERT INTO @tbl_datacheck
EXEC (@sql)
SET @rowcnt = @@ROWCOUNT

IF @rowcnt <=0 BEGIN
SET @r = 0
GOTO ERROR;
END

IF OBJECT_ID('tempdb.dbo.#tbl_column') IS NOT NULL
DROP TABLE #tbl_column
CREATE TABLE #tbl_column (idx INT IDENTITY(1,1), NAME VARCHAR(255))

set @sql = '
INSERT #tbl_column
SELECT name
FROM '+@DBName+'.SYS.syscolumns
WHERE ID = OBJECT_ID(@objName)
ORDER BY colorder
'
set @param = ' @objName varchar(255) '
exec sp_executesql @sql, @param, @objName

SET @rowcnt = @@ROWCOUNT
–PRINT @rowcnt

IF @rowcnt = 0 BEGIN
SET @r = 10002
GOTO ERROR;
END

SELECT @HTML_TH =
CAST ((SELECT th = isnull(NAME,' ')
FROM #tbl_column
FOR XML PATH(''), TYPE
) AS NVARCHAR(MAX) )

–print @HTML_TH
SET @HTML_TD = ''
SELECT @HTML_TD = @HTML_TD + 'td = isnull('+NAME+','' ''), '''', '
FROM #tbl_column

SET @HTML_TD = SUBSTRING(@HTML_TD, 1, LEN(@HTML_TD)-1)

SET @sql = 'SELECT @HTML_TD = CAST ((SELECT '+ REPLACE(@HTML_TD,'','''') +'
FROM '+@objName+'
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )
'

–PRINT @sql

EXEC sp_executesql @sql, N' @HTML_TD NVARCHAR(MAX) OUTPUT ', @HTML_TD OUTPUT

–PRINT @HTML_TD

set @Recipients = isnull(@Recipients,'NowcomDBDevTeam@nowcom.com')

set @subject = @@servername +' – ' + convert(varchar(255), getdate(), 101) + ' ' + convert(varchar(5), getdate(), 108)+' ' + @Subject

SET @tableHTML = '

h1{
font-size:17px;
}
body{
font-size:15px;
}

table{
border-collapse:collapse;
border:1px solid black;
font-size:12px;
}

table th{
font:bold;
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px;
background-color:lightblue;
font-size:12px;
}

table td{
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px
font-size:12px;
}

‘+ @subject +’
‘+ ISNULL(@PreDescription,”) + ‘

‘+ @HTML_TH +’


+ @HTML_TD

set @tableHTML = @tableHTML + ‘

SELECT @tableHTML = ‘From Server :’ + ISNULL(@@SERVERNAME, ”) + char(13) + char(10) + ISNULL(@tableHTML, ”);

EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile
, @body_format = ‘HTML’
, @recipients = @Recipients
, @importance = @importance
, @copy_recipients =”
, @subject = @subject
, @body = @tableHTML
IF @@error 0 BEGIN
RAISERROR(‘Error in dmp_send_email_HTML_format. Failed to send Database Mail.’, 16, 1);
END
RETURN 0
ERROR:
return @r
END
Go

Posted in Common | Tagged: , | Leave a Comment »

Selecting Rows Randomly from a Large Table

Posted by Simon Cho on 06/12/2014

http://msdn.microsoft.com/en-us/library/cc441928.aspx

SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10 — Percent

SELECT * FROM Table1 TABLESAMPLE (1 percent) ORDER BY NEWID()

Posted in Common | Tagged: , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.