Home » Posts tagged 'SQL Query'

Tag Archives: SQL Query

Error while loading Excel data into database

Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –

 

Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:02:37 AM  Error: 2016-12-19 02:02:37.91     Code: 0xC0209303     Source: ExcelImpot Connection manager “SourceConnectionExcel”     Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC001002B     Source: MAPTImpot Connection manager “SourceConnectionExcel”     Description: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC020801C     Source: Data Flow Task 1 Source – Sheet1$ [38]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “SourceConnectionExcel” failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source – Sheet1$ failed validation and returned error code 0xC020801C.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:02:37 AM  Finished: 2:02:37 AM  Elapsed:  0.733 seconds.  The package execution failed.  The step failed.

Cause: This error occurs when you try to execute 32-bit environment package in 64-bit system.

Solution 1: If you have project file of SSIS package or If you are running package other than SQL Agent jobs like calling from CMD.

Step 1: Navigate to Project-> [PROJECT_NAME] Properties.

Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.

Solution 2: If you need to execute package from SQL agent job only then you have readymade option available. Go to Job step and check below option.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

How to Check SQL Server Evaluation Version Expire Date ?

SQL Server Evaluation Edition is available free of cost. It’s a trial version & valid for limited time of period. The expiration date is always 180 days from the initial installation date.

The following query will give you the expiration date of evaluation instance :

SELECT

@@SERVERNAME SERVERNAME,

CREATE_DATE ‘INSTALALTIONDATE’,

SERVERPROPERTY(‘EDITION’) ‘Version’,

DATEADD(DD, 180, CREATE_DATE) AS ‘EXPIRY DATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is associated with login “NT AUTHORITYSYSTEM” & which is created at the time of installation only.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-13 : Finding locking & blocking……..sys.dm_tran_locks

sys.dm_tran_locks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms190345.aspx

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: 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.

This DMV is very useful in helping to identify locking and blocking issues on your data­base instances.

Query 1 : Details of currently active locks

SELECT

CASE DTL.REQUEST_SESSION_ID

WHEN -2 THEN ‘ORPHANED DISTRIBUTED TRANSACTION’

WHEN -3 THEN ‘DEFERRED RECOVERY TRANSACTION’

ELSE DTL.REQUEST_SESSION_ID END AS SPID,

DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,

SO.NAME AS LOCKEDOBJECTNAME,

DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,

DTL.REQUEST_MODE AS LOCKTYPE,

ST.TEXT AS SQLSTATEMENTTEXT,

ES.LOGIN_NAME AS LOGINNAME,

ES.HOST_NAME AS HOSTNAME,

CASE TST.IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

AT.NAME AS TRANSACTIONNAME,

DTL.REQUEST_STATUS

FROM

SYS.DM_TRAN_LOCKS DTL

JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID

JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID

JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.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 EC ON EC.SESSION_ID = ES.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST

WHERE

RESOURCE_DATABASE_ID = DB_ID()

ORDER BY DTL.REQUEST_SESSION_ID

Sample Result

Remarks

1. Column request_session_id display the session ID owns the lock or raise request.

If value display is -2 indicates that the request belongs to an orphaned distributed transaction.

If value display is -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

2. Column request_owner_type details :-

· TRANSACTION = The request is owned by a transaction.

· CURSOR = The request is owned by a cursor.

· SESSION = The request is owned by a user session.

· SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.

· EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.

· NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-12 : Retaion in Session & Transaction……..sys.dm_tran_session_transactions

sys.dm_tran_session_transactions DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188739.aspx

Returns correlation information for associated transactions and sessions.

This DMV helps in correlating the relation between session & transaction. We can get several details about transaction by correcting it will other DMVs & system catalogs.

Query 1 : Query to check Transaction & Session details

SELECT

ST.SESSION_ID,

ST.TRANSACTION_ID,

DB_NAME(SP.DBID) DB_NAME,

CASE IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

CASE IS_LOCAL

WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’

WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT

FROM

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

Sample Result

Remarks

1. Meaning of int value for is_user_transaction column

a. 1 = The transaction was initiated by a user request.

b. 0 = System transaction.

2. All possible state of transactions for is_local coulmn

a. 1 = Local transaction.

b. 0 = Distributed transaction or an enlisted bound session transaction.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: