Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

AlwaysOn AG group status check

Posted by Simon Cho on 2015/06/15

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 2015/06/15

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 2015/02/24

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 2015/02/13

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 | 2 Comments »

SQL Saturday Presentation file – 9/20/2014

Posted by Simon Cho on 2015/01/17

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 2015/01/17

 

 

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 2014/09/22

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 2014/08/08

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 2014/08/08

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 2014/07/22

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 2014/07/15

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 2014/07/03

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 2014/07/02

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 2014/06/30

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 2014/06/12

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 »

SQL Server Job Agent – Invoke job sequence.

Posted by Simon Cho on 2014/06/11

1. [SQLAgent – Job invocation engine] : execute it based on active schedule and job at scheduled time.

 

exec sp_executesql N'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3',N'@P1 int,@P2 uniqueidentifier,@P3 int',1,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

 

2. [SQLAgent – Job Manager] : Update start time. Check Permission. Get Jobstep. And execute each step

exec sp_executesql N'DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = @P3 and session_id = @P4',N'@P1 int,@P2 int,@P3 uniqueidentifier,@P4 int',20140611,144430,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1',N'@P1 nvarchar(128)',N'sa'

exec sp_executesql N'EXECUTE msdb.dbo.sp_help_jobstep @job_id = @P1',N'@P1 uniqueidentifier','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT,
                                              'NO_TEST'

--Execute each step

3. [SQLAgent – TSQL JobStep(Job 0x…) or SSIS JobStep(Job 0x…)] : Execute each step.

4. [SQLAgent – Job Manager] : Update job history and status.

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',1,0,0,1,20140611,144430,0,0,0,0,0,14,N'Executed as user: NT AUTHORITY\NETWORK SERVICE. The step succeeded.'

5. [SQLAgent – Update job activity] – Update next schedule.

exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = @P3 AND job_id = @P4',N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',20140611,144440,14,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

6. [SQLAgent – Job Manager] – Update last run time

exec sp_executesql N'UPDATE msdb.dbo.sysjobservers SET last_run_date = @P1, last_run_time = @P2, last_run_outcome = @P3, last_outcome_message = @P4, last_run_duration = @P5 WHERE (job_id = @P6) AND (server_id = 0)',N'@P1 int,@P2 int,@P3 int,@P4 nvarchar(4000),@P5 int,@P6 uniqueidentifier',20140611,144430,1,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).',0,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',0,0,0,1,20140611,144430,0,0,0,0,0,14,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).'

Done.

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

OpenXML parentID

Posted by Simon Cho on 2014/05/19

I used long time ago.

To find the it again, I spent a lot of time.

This is really helpful to identify XML parentID and currentID.

OpenXML support it.

 

Metaproperty attribute Description
@mp:id Provides system-generated, document-wide identifier of the DOM node (element, attribute, and so on). This ID is guaranteed to refer to the same XML node as long as the document is not reparsed.An XML ID of 0 indicates that the element is a root element. Its parent XML ID is NULL.
@mp:localname Stores the local part of the name of the node. It is used with prefix and namespace URI (Uniform Resource Identifier) to name element or attribute nodes.
@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present
@mp:prefix Stores the namespace prefix of the current element name.If no prefix is present (NULL) and a URI is given, indicates that the specified namespace is the default namespace. If no URI is given, no namespace is attached.
@mp:prev Stores the previous sibling relative to a node, thereby, providing information about the ordering of elements in the document.@mp:prev contains the XML ID of the previous sibling that has the same parent element. If an element is at the beginning of the sibling list, @mp:prev is NULL.
@mp:xmltext This metaproperty is used for processing purposes. Is the textual serialization of the element and its attributes and subelements as used in the overflow handling of OPENXML.

 

Parent metaproperty attribute Description
@mp:parentid Corresponds to ../@mp:id
@mp:parentlocalname Corresponds to ../@mp:localname
@mp:parentnamespacerui Corresponds to ../@mp:namespaceuri
@mp:parentprefix Corresponds to ../@mp:prefix

 

http://technet.microsoft.com/en-us/library/aa226531%28v=sql.80%29.aspx

http://technet.microsoft.com/en-us/library/ms178088.aspx

 

DECLARE @X XML, @h INT
SET @X = '<root><element>test</element><element>test2</element></root>'

EXEC sp_xml_preparedocument @h OUTPUT, @x

select @h

SELECT * 
  FROM OPENXML (@h, './root/element',8)
  WITH 
  (
  id bigint '@mp:id'
  , parentid bigint '@mp:parentid'
  , element varchar(255) '.'
  )

 EXEC sp_xml_removedocument @h

 

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

Token-based server access validation failed with an infrastructure error, Error: 18456

Posted by Simon Cho on 2014/04/02

Login failed for user <Server name>\User1′. Reason: Token-based server access validation failed with an infrastructure error. Check

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

 

Posted in Common | Leave a Comment »

sp_rename will not change sys.syscomments table definition.

Posted by Simon Cho on 2013/08/05


exec sp_rename 'usp_test','uspp_test'
select text from sys.syscomments
where id=object_id('uspp_test')

The text column is still showing as “create procedure usp_test”.

It breaks to run “sp_refreshsqlmodule”

Invalid object name ‘dbo.uspp_test’.

To be able to resolve it, run again “alter procedure ” statement.

Posted in Common | Leave a Comment »

Excel import using XML and T-SQL

Posted by Simon Cho on 2013/05/08

http://www.sqlservercentral.com/articles/ETL/69339/

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

 
Follow

Get every new post delivered to your Inbox.