Friday, March 29, 2013

Fix : An error occurred while obtaining the dedicated administrator connection (DAC) port


Troubleshooting DAC Issues

We may encounter below error messages while working with DAC.

An error occurred while obtaining the dedicated administrator connection (DAC) port.

Resolution :
1. Make sure TCP/IP is enabled.
2. The browser service should be in running mode.
3. While connecting using DAC pass port number along with Instance name if it is named instance.


Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Note  - In DAC mode you wont be able to access object explorer

Resolution :
1. If you are trying to connect to DAC using like below
SSMS - Connect Datbase Engine - Server Name  - Admin:ServerName -- Then it throws error because it was trying to open object explorer whereas DAC doesn't have access to object explorer

2. You need to try like this
SSMS - New Query -  Admin:ServerName -- It allows you to login using DAC Connection.

 

Thursday, March 28, 2013

DAC -- Dedicated Administrator Connection

 
SQL Server introduced DAC on SQL Server 2005 which is a special type of connection where database administrators can connect to SQL Server when regular connections are not possible. In other words it is a backdoor to access to SQL Server resources to troubleshoot some serious issues. 
 
There is one reserved scheduler which allows us to login into SQL Server using DAC. There is one specific endpoint reserved for DAC that is DAC Endpoint and it is #1.
 
The default behavior of SQL Server DAC is it wont allow other clients to login into servers using DAC, to do so we need to configure 'remote admin connections' to 1 from 0.
 
Note : Only one connection made using DAC, also only Sysadmin has right to connect to DAC
 
To connect using DAC
Open SSMS -- New Query (On ServerName Type ADMIN:ServerName)
Server Name  -- ADMIN:ServerName (or)
Server Name  -- ADMIN:ServerName\NamedInstance (or)
Server Name  -- ADMIN:ServerName[\NamedInstace], PortNumber
 
(OR)
 
cmd > sqlcmd -SServerName[\NamedInstance] -E -A
 
-S  -- ServerName
-A -- Enable DAC
-E -- Windows Autentication
-U -- UserName
-P -- Password
 
Run the command to enable DAC to client connections
sp_configure 'remote admin connections',1
go
Reconfigure
 
(OR)
 
Right click on server name -- Facets -- Select Surface Area Configuration from Facet dropdown
Remote DAC Enabled  -- True
 
-- Query will return 1 row, if you are connected using DAC.

SELECT ec.login_name, ep.name, ec.session_id, ep.endpoint_id
FROM sys.dm_exec_sessions ec, sys.endpoints ep
WHERE ec.endpoint_id = ep.endpoint_id AND session_id=@@spid
 

Thursday, March 21, 2013

The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

To execute any job in SQL Server Agent you must have the below three database roles on MSDB Database,

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

The user has all the above three db roles on MSDB eventhough that user is getting permission deined error while executing agent jobs.

Use the below query to verify if there are any issues with permissions


use msdb
go
SELECT  OBJECT_NAME(so.object_id) AS obj_Name, spr.name, sdp.permission_name, sdp.state_desc
FROM sys.database_permissions sdp, sys.objects so, sys.database_principals spr
WHERE sdp.major_id = so.object_id AND spr.principal_id = sdp.grantee_principal_id
and so.name = 'sp_start_job'
If no rows returned then we need to execute the below command to assign execute permission on sp_start_job to SQLAgentUserRole

Grant execute on sp_start_job to SQLAgentUserRole

If two rows returned with Grant for SQLAgentUserRole and Deny for TargetServerRole, then

MSDB - Security - Users - Properties - Add Target Server Role to that user
Grant execute on sp_start_job to TargetServerRole

Now execute the job it will be executed by that user.

 

Wednesday, March 20, 2013

Fix : Could not obtain exclusive lock on database 'model'

Could not obtain exclusive lock on database 'model'

MS-SQL Database Error
---------------------------
Create failed for Database 'DbName1'. An exception occurred while executing a Transact-SQL statement or batch.Could not obtain exclusive lock on database 'model'. Retry the operation later.CREATE DATABASE failed. Some file names listed could not be created. Check related errors.


As Model database is a template for new databases hence SQL Server will place a Exclusive lock on Model database while creating any new databases. So we need to make sure there wont be any active connections to Model database.

Solution:
Use the below query to identify who connected to model database,

SELECT spid, loginame, DB_NAME(dbid) FROM master..sysprocesses WHERE DB_NAME(dbid)='Model'

Disconnect all sessions from Model Database and try creating new database.

 

Tuesday, March 19, 2013

Row_Number Example in SQL Server

Row_number -- Returns sequential number for the resultset returned by select statement

Syntax
Row_Number() (Over [Partition by], [Order by])

Partition By – It divides the resultset into partitions produced by from clause to which sequential number will be assigned.

Order by clause is used to assign sequence for the column which you mentioned. It is required when you use partition by clause.

CREATE TABLE rownumber_test (ServerName VARCHAR(20), Server_Type VARCHAR(20), Server_Location VARCHAR(20))

INSERT INTO rownumber_test VALUES ('Server1', 'VM', 'India')
INSERT INTO rownumber_test VALUES ('Server2', 'VM', 'India')
INSERT INTO rownumber_test VALUES ('Server3', 'VM', 'AUS')
INSERT INTO rownumber_test VALUES ('Server4', 'VM', 'Bangkok')
INSERT INTO rownumber_test VALUES ('Server5', 'Physical', 'South America')
INSERT INTO rownumber_test VALUES ('Server6', 'Physical', 'South America')
INSERT INTO rownumber_test VALUES ('Server7', 'Physical', 'Japan')
INSERT INTO rownumber_test VALUES ('Server8', 'Physical', 'Bangkok')

-- Assign Sequential Number for the result set.

SELECT Row_Number()over(ORDER BY ServerName) AS SlNo,
ServerName, server_type FROM rownumber_test

Note - Normally Partition by clause will be used to generate sequence numbers for subset in other words on a repetitive values, for example in the above example
we have two different types of server types, one is VM and other is Physical. We need to get sequential number by server type, below is the example

SELECT Row_Number()over(PARTITION BY server_type ORDER BY server_type) AS SlNo,
ServerName, server_type FROM rownumber_test

SELECT Row_Number()over(PARTITION BY server_location ORDER BY server_type) AS SlNo,
ServerName, server_type, Server_Location FROM rownumber_test

-- Example to specify multiple columns in partition by clause

SELECT Row_Number()over(PARTITION BY server_type, server_location ORDER BY server_type) AS SlNo,
ServerName, server_type, Server_Location FROM rownumber_test

-- Example to remove duplicates using row_number()

WITH duplicates(servername, row_cnt, servertype, serverlocation)as
(
SELECT servername, ROW_NUMBER()OVER(PARTITION BY server_type ORDER BY server_type), server_type, server_location
FROM rownumber_test
) delete FROM duplicates WHERE row_cnt>1

 --- Example for pagination using row_number function

DECLARE @records_per_page INT = 25;
DECLARE @currentPage INT = 10;
PRINT @records_per_page;
PRINT @currentPage;
WITH persondetails AS
(
SELECT TOP (@currentPage * @records_per_page) RowNumber= row_number() OVER (ORDER BY businessentityID), FirstName, LastName FROM person.Person
)SELECT RowNumber, firstname, LastName FROM persondetails
WHERE RowNumber BETWEEN ((@currentPage - 1) * @records_per_page + 1) and (@currentPage) * @records_per_page
 

Wednesday, March 13, 2013

Insert Formula in excel cell using VBA

Below syntax is used to insert formula into excel sheet

ActiveSheet.Cells(f, 6).Formula = "=DATE(YEAR(B2),MONTH(B2),DAY(B2))"

Range(c2).formula = "=A1 + B1"

for i = 1 to 10 step 1
           Range(c2).formula =  "=A"& i & "+B" & i
next
 

Monday, March 11, 2013

Sending Query Results to Email in HTML Format

Sending T-SQL Query Results to email in HTML Format


begin
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>Status Report of Databases</H1>' +
N'<table border="1">' +
N'<B> ServerName :' + CAST ( (select @@SERVERNAME for xml PATH , Type) As Nvarchar(MAX)) +
N'<br><th>Name</th> <th>Status></th>' +
CAST ( ( SELECT td = name, '',
td = DATABASEPROPERTYEX(name, 'status'), ''
FROM sys.databases where database_id > 4
FOR XML PATH ('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients='hello@gmail.com',
@subject = 'Databases Status',
@body = @tableHTML,
@body_format = 'HTML' ;

end

Sending Query Results To Email Using SP_Send_DbMail

Sending T-SQL Results as attachment mail

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'hello@gmail.com',
    @body='Query Results Are Attached',
    @subject ='Query Results',
    @profile_name ='Kalyan_Notifications',
    @query ='select name, createdate from master..syslogins',
    @attach_query_result_as_file = 1,
    @query_result_separator =',',
    @query_result_no_padding=1,
    @query_attachment_filename ='QueryResults.txt'
Sending Stored Procedure Results as attachment mail

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'hello@gmail.com',
@body='Query Results Are Attached',
@subject ='Query Results',
@profile_name ='Kalyan_Notifications',
@query ='exec usp_getLoginInformation',
@attach_query_result_as_file = 1,
@query_result_separator =',',
@query_result_no_padding=1,
@query_attachment_filename ='QueryResults.txt'


@recipients - senders address, Ex:@recipients =hello@gmail.com;hello1@gmail.com
@body -- body of the message
@subject  -- subject line of email
@profile_name -- Mail Profile name, if you don't specify profile it uses default profile name
@query -- A valid T-SQL Statement
@attach_query_result_as_file -- It is used to control whether the result of the query will be attached as file or included in the body of the email.
@query_result_separator -- It is used to seperate the columns in the query output.
@query_result_no_padding -- Default value is 0, when you specified 1 the query results are not padded.
@query_attachment_filename -- Name of the attachment file

 

Wednesday, March 6, 2013

Error: 1474, Severity: 16, State: 1. Database mirroring connection error 4 'An error occurred while receiving data: '64(for 'TCP://servername:5022'.

While configuring database mirroring I encountered the below errors

Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'databasename'. This is an informational message only.
Error: 1474, Severity: 16, State: 1. Database mirroring connection error 4 'An error occurred while receiving data: '64(for 'TCP://servername:5022'.


Workaround on our environment to fix the issue.

1. Verify whether you are able to register the server instance on both principal and mirror servers
2. Add entry to hosts.etc file on both servers (principal,mirror)
3. Local Area connection - status - properties - select IPV4 - properties - advanced - DNS
Under Append these DNS suffixes - Click Add and add the DNS Suffix of the principal server on mirror server and vice versa.
4. Try configuring mirroring

 

Friday, March 1, 2013

LOGPARSER Examples


LogParser  -- It is a powerful tool from Microsoft and describes it provides universal query access to text based log files such as event viewers, xml files, IIS logs, csv files, registry, filesystem and so on.
Here are few examples of logparser.

-i –Input Type “EVT (Event Viewer), FS(FileSystem), ADS(Active Directory), IIS (Microsoft IIS
Log file format), REG(Registry Input), TSV (Tab Seperated), XML etc”

-o – Output Type – “NAT (Tabulated Column format), CSV (Comma Seperated), SQL (uploads records into SQL Database, TSV (Tab Seperated or Space Seperated), XML etc.,

Examples to access Event ViewerCreate an output.txt file to pull events from event viewer’s application section whose source name is outlook

C:\Log Parser 2.2>LogParser -i:EVT -o:NAT "select EventID, TimeGenerated, SourceName, Message into output.txt from application where SourceName like 'outlook'"

C:\Log Parser 2.2>LogParser -i:EVT -o:NAT "select EventID, TimeGenerated, SourceName, Message into output.txt from security"

C:\Log Parser 2.2>LogParser -i:EVT -o:NAT "select EventID, TimeGenerated, SourceName, Message into output.txt from application where eventId  in (1904; 1001)"

C:\Log Parser 2.2>LogParser -i:EVT -o:NAT "select EventID, TimeGenerated, SourceName, Message into output.txt from application where eventId in (1904; 1001) and TimeGenerated>'2012-
10-19 12:00:00' and TimeGenerated<'2013-01-01 12:00:00'"

C:\Log Parser 2.2>logparser "SELECT TO_DATE(TimeGenerated), TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ), SourceName into output.txt FROM System" -o:NAT

Examples to access File System

Example to retrieve top 10 largest files in from the C:\Kalyan Folder
C:\Log Parser 2.2>logparser -i:FS "select top 10* from C:\kalyan\*.* order by size desc"

C:\Log Parser 2.2>logparser -i:FS "select top 10 name,path,size into largefiles.txt from C:\DBA\*.*
order by size desc" -o:NAT

C:\Log Parser 2.2>logparser -i:FS "select top 10 name,path,size from C:\DBA\*.* order by size desc" -o:DATAGRID – Displays in a grid format.