Tuesday, May 28, 2013

Java - TSQL - Error - The statement did not return a result set

Problem:
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

Background:
Stored Procedure is inserting few records in temporary table and at the end returning record set. (See code snippet-01)

Environment:
SQL Server version - MS SQL Server 2008 R2
Java version - 1.7

Solution:
Use 'SET NOCOUNT ON' as very first statement inside stored procedure to stop returning insert/update/delete/select confirmation message. To demonstrate that I have switched output format to text in 'SQL Server Management Studio' (shortcut key is cntl+T). After that SP execution output looking similar as following where first and third lines are the confirmation message for INSERT and second SELECT. When 'SET NOCOUNT ON' is used, output should include only the table.


EXECUTE SPTest;

(3 row(s) affected)
id
-----------
13
14
15

(3 row(s) affected)


Code snippets:

Snippet-01: dummy stored procedure created to test purpose

IF OBJECT_ID('dbo.SPTest', 'P') IS NOT NULL
DROP PROCEDURE dbo.SPTest;
GO
CREATE PROCEDURE dbo.SPTest
AS
BEGIN

select ID from wk_ctr
create table #test(id INT);
INSERT INTO #TEST
select ID from wk_ctr;

select * from #TEST;

END;

Snippet-02: Java class to invoke the Stored Procedure

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTempTableOutput {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.println("before class load2");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

System.out.println("before connection creation2");

conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=dbname","username","password");

System.out.println("Before statement creation2");
stmt = conn.createStatement();
String sql = "EXECUTE SPTest";
rs = stmt.executeQuery(sql);

if(rs.next())
System.out.println("resultset found");
else
System.out.println("resultset not found");

if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} catch (Exception e) {
e.printStackTrace();
try{
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception ex){}
}
System.out.println("finished");
}
}

Tips:
1. To change SQL Server Management studio output format to text, use shortcut (ctrl+t)
2. To change SQL Server Management studio output format to table, use shortcut (ctrl+d)

Tuesday, April 16, 2013

TSQL - Coding - Dynamic query


Backgroundsp_executesql is used to execute dynamic query in SQL Server. Below few examples are given to demonstrate the use.

Table structure and data script:

DROP TABLE test;
GO


CREATE TABLE test (group_id INT, name VARCHAR(100));
GO
INSERT INTO test VALUES (1, 'First');
INSERT INTO test VALUES (1, 'Second');
INSERT INTO test VALUES (2, 'Third');
INSERT INTO test VALUES (2, 'Fourth');
INSERT INTO test VALUES (2, 'Fifth');
INSERT INTO test VALUES (3, 'Sixth');
GO

SELECT * FROM test;


Example-01: Use sp_executesql to execute a SELECT query

DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value INT;

SET @query = N'SELECT * FROM wk_be_customer WHERE web_reference_id=@in';
SET @paramDifinition = N'@in INT';
SET @value = 233;

EXECUTE sp_executesql @query, @paramDifinition, @value;

Example-02Use sp_executesql to execute a UPDATE query


-- Dynamic query - UPDATE
DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 INT, @value2 INT;

SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id=@in2';
SET @paramDifinition = N'@in1 INT, @in2 INT';
SET @value1 = 1;
SET @value2=2;

EXECUTE sp_executesql @query, @paramDifinition, @value1, @value2;


Example-03Use sp_executesql to execute a UPDATE query with IN clause (string concatenation)


DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 NVARCHAR(100), @value2 INT;

SET @value1 = '1,2';
SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id IN ('+@value1+')';
SET @paramDifinition = N'@in1 INT';
SET @value2=3;

EXECUTE sp_executesql @query, @paramDifinition, @value2;


Notes:
1. sp_executesql - input parameters have to be NVARCHAR type

Resources:
1. http://msdn.microsoft.com/en-us/library/ms188001.aspx

Thursday, March 28, 2013

MS SQL Server - DBA - DMV - Find out DB/table lock


Environment:
SQL Server 2008 R2 - DMV (Dynamic Management View)


Query to see the locks across database:

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        L.request_status AS LockStatus,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Note:
  1. This query will run using selected database - db_id().
  2. sys.dm_tran_locks - system table has two types of columns (resource and request). The resource group describes the resource on which the lock request is being made, and the request group describes the lock request. 
  3. Values in sys.dm_tran_locks.resource_type - KEY, PAGE, DATABASE, FILE, OBJECT etc
  4. Values in sys.dm_tran_locks.request_mode - 
    • NULL = No access is granted to the resource. Serves as a placeholder.
    • Sch-S = Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
    • Sch-M = Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object
    • S = Shared. The holding session is granted shared access to the resource.
    • U = Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
    • X = Exclusive. The holding session is granted exclusive access to the resource.
    • IS = Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
    • IU = Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
    • IX = Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
    • SIU = Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
    • SIX = Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
    • UIX = Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
    • BU = Bulk Update. Used by bulk operation
    • There are few others about range related
  5. Values in sys.dm_tran_locks.request_status - 
    • GRANT: the lock request was granted
    • WAIT: The request to acquire a particular lock type is waiting.
    • CONVERT: the request was granted earlier with a particular lock status but now is trying to upgrade to another status and is being blocked
Query to find lock on a table:

SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID FROM
sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_iD('name_of_the_table'))
ORDER BY request_mode;

Note:
  1. Have to change 'name_of_the_table' to correct table
Kill a open session:
Command - KILL ;

Note: 
  1. sessionid can be found by running above queries.  
  2. Most of the time killing exclusive (X) type request solves table/db lock issue
Resources
1. http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/cae67b92-1f17-4f97-b68e-cfaba1207253/
2. http://msdn.microsoft.com/en-us/library/ms190345.aspx

Wednesday, March 20, 2013

Windows Administration - Error - External Hard Drive access denied

Error: 
I have Western Digital HDD My Passport. I always plug it in before work and properly remove/eject (using windows 'Safely remove hardware and eject media' feature). All on a sudden I have started getting error 'Access Denied' (see screenshot-01).


Screenshot-01: Access Denied













Solution:

Option-04 below is the solution. But I had to do that in 'Safe Mode' to make it work. 

Options tried to get rid of this error:

Option-01: Removed and plugged-in again
Result: Didn't work

Option-02: Restart the machine
Result: Didn't work

Option-03: Change drive letter
Steps:

  1. Go to Computer Management (Start - Control Panel - Administrative tools - Computer Management)
  2. Go to Disk Management option (From left navigation panel, click Disk Management under Storage)
  3. Select the disk which is having access denied error
  4. Right Mouse button - Change drive letter and paths - Change...
  5. Select different drive letter from option under 'Assign the following drive letter' (in my case I have selected H)
Screenshot-02: Computer Management
Result: Didn't work



Option-04: Take ownership and change permission using windows command

Steps:

  1. I have another post to set ownership and permission 'Setup ownership and permission to file/folder/drive'. 
Result: getting 'Access Denied' error in the command prompt for both the commands


Resources:

Windows Administration - Setup - Set ownership and permission to file/folder/drive letter




Environment: Windows 7



Trying to set ownership and permission to my external drive

Take ownership - using command prompt 

Steps:
  1. Click Start , click All programs , and click Accessories
  2. Right-click Command Prompt and click Run as administrator
  3. In Administrator: Command Prompt window
  4. Type command "takeown /F G:"

Take ownership - using ui 
Steps:
  1. Select drive (G) - right mouse button - select Properties
  2. Go to 'Security' tab - Click 'Advanced' button (see screenshot-01)
  3. It will open 'Advanced Security Settings' wizard 
  4. Select Owner tab 
  5. Click 'Edit...' button (see screenshot-02)
  6. Next screen might display 'Administrator' and current user in the 'Change owner to' list
  7. If it's needed to set owner to different user, click 'Other users and groups' button (see screenshot-03)
  8. Enter correct username or group name and hit enter in 'Select User, Computer, Service Account or Group' screen

Set permission - using command prompt 

Steps:
  1. Click Start , click All programs , and click Accessories
  2. Right-click Command Prompt and click Run as administrator
  3. In Administrator: Command Prompt window
  4. Type command "CACLS G: /G Administrator:F" [meaning of command: give full permission (F) to user (Administrator) for drive G)

Set permission - using ui 
Steps:
  1. Select drive (G) - right mouse button - select Properties
  2. Go to 'Security' tab - Click 'Advanced' button (see screenshot-01)
  3. It will open 'Advanced Security Settings' wizard 
  4. Go to 'Permission' tab
  5. Click 'Configure' button (see screenshot-04)
  6. In next screen, to add new user, click 'Add' button (see screenshot-05)
  7. To to modify permission attributes, select the user from the list and click 'Edit...' button (see screenshot-06)

Screenshots:

Screenshot-01:






















Screenshot-02:



















Screenshot-03:















Screenshot-04: 



















Screenshot-05:




Screenshot-06:




























Resources: