Archive

Archive for April, 2015

Error Msg 7302, Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server – MSDN TSQL forum

April 10, 2015 Leave a comment

–> Question:

Below is the script to export data from Access 2003 using SQL server 2012 in SQL Server Management Studio:

EXEC sp_addlinkedserver 
   @server = 'SNE_SNAP2014', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'T:\Data Management\Project -SNAP SNE\SNE SNAP2014 T-Drive.mdb'
GO

this results into following error:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “SNE_SNAP2014”.

 

–> My Answer:

Please make sure that both Access & SQL Server 2012 should be on same 32 or 64 bit architecture.

Check this blog post for resolution and more info: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/
 

Ref Link.


String concatenation resulting NULL values – MSDN TSQL forum

April 9, 2015 Leave a comment

–> Question:

The Payments field in the below update statement always return null even though the update statement executed successfully.

What is wrong here?

DECLARE @Temp nvarchar(200), @sql nvarchar(max),@Details_InvNo nvarchar(4000), @Details_Amt nvarchar(100),@ChkNo nvarchar(100)
set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = convert(nvarchar(max),Payments) + cast(@Temp as nvarchar(max)), 
AmtDue = 100, 
AmtPaid=0 
WHERE InvNo = @Details_InvNo
GO

 

–>My Response:

As the Payment columns would be NULL and you are concatenating it with another String, the result will be NULL only. So, just put an IsNULL() over Payments column after “=” operator, like this:

DECLARE @Temp nvarchar(200), 
	@sql nvarchar(max), 
	@Details_InvNo nvarchar(4000), 
	@Details_Amt nvarchar(100),
	@ChkNo nvarchar(100)

set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = ISNULL(convert(nvarchar(max),Payments),'') + cast(@Temp as nvarchar(max)), 
	AmtDue = 100, 
	AmtPaid=0   
WHERE InvNo = @Details_InvNo
go

Or use CONCAT() function like this:

SET Payments = CONCAT(convert(nvarchar(max),Payments), cast(@Temp as nvarchar(max))), 

 

Ref Link.


DELETE from Parent and all its child tables with Foriegn Key (no DELETE ON CASCADE) – MSDN TSQL forum

April 8, 2015 Leave a comment

–> Question:

I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).
Can any one tell how to write a generalized code for removing rows from both parent and child tables.

Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables.
 

–> My Answer:

I this case you need to create query by using system catalog tables/views, link.

Check this query below:

DECLARE @Deletestr VARCHAR(max) = ''
SELECT @Deletestr = @Deletestr +
'DELETE FROM ' + object_name(fk.referenced_object_id) + 'WHERE ParentId = 55
GO
'
from sys.foreign_keys fk
join sys.foreign_key_columns fc
on fk.object_id = fc.constraint_object_id
where object_name(fk.parent_object_id) = 'SalesOrderHeader'

PRINT (@Deletestr)

EXEC (@Deletestr)

Like above query get the linked tables (FK) info, and finally with the output create Dynamic DELETE statement.
 

Ref Link.


Index Usage Stats – Indexes Used, Unused and Updated

April 2, 2015 1 comment

While working on Performance Optimization and Index tuning many Developers ends up in creating some (or even many) unnecessary Indexes based upon various SQL Queries. Some or many of those Indexes might not be used at all by the SQL Query Optimizer. But even if they are used you may have to Trade-off with your ETL performance going down, as the CRUD (CREATE, READ, UPDATE, DELETE) operations are going to taking more time to update those new Indexes.

So, while creating new Indexes you will have to plan very carefully, decide and balance out things so that your Data retrieval is fast and on the same hand your ETLs are also not affected much.

SQL Server provides some DMVs (Dynamic Management Views) and DMFs (Dynamic Management Functions) to get this information from SQL engine.
 

–> To know how the Indexes are getting used we can use sys.dm_db_index_usage_stats DMV and this will provide us information on how many times the Index was used for SEEK, SCAN & LOOKUP operations. Check the Query and its output below:

SELECT 
	DB_NAME(database_id) AS DATABASE_NAME,
	OBJECT_NAME(ius.object_id) AS TABLE_NAME, 
	ids.name AS INDEX_NAME, 
	ius.user_seeks AS SEEK_COUNT, 
	ius.user_scans AS SCAN_COUNT, 
	ius.user_lookups AS LOOKUP_COUNT, 
	ius.user_seeks + ius.user_scans + ius.user_lookups AS TOTAL_USAGE, 
	ius.last_user_seek AS LAST_SEEK_COUNT, 
	ius.last_user_scan AS LAST_SCAN_COUNT, 
	ius.last_user_lookup AS LAST_LOOKUP_COUNT
FROM sys.dm_db_index_usage_stats AS ius
INNER JOIN sys.indexes AS ids
ON ids.object_id = ius.object_id
AND ids.index_id = ius.index_id 
WHERE OBJECTPROPERTY(ius.object_id,'IsUserTable') = 1 
ORDER BY DATABASE_NAME, TOTAL_USAGE

Index Usage Stats 02
 

–> Now if we want to know the maintenance overhead on the new Indexes we created, like every time a related Table is updated the Indexes are also updated. We can check by using sys.dm_db_index_operational_stats DMF and it will show how many INSERT, UPDATE & DELETE operations are happening on particular indexes. Check the Query and its output below:

SELECT 
	DB_NAME(database_id) AS DATABASE_NAME,
	OBJECT_NAME(ios.object_id) AS TABLE_NAME, 
	idx.name AS INDEX_NAME, 
	ios.leaf_insert_count AS INSERT_COUNT, 
	ios.leaf_update_count AS UPDATE_COUNT, 
	ios.leaf_delete_count AS DELETE_COUNT, 
	ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count AS TOTAL_COUNT 
FROM sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL ) ios
INNER JOIN sys.indexes AS idx
ON idx.object_id = ios.object_id
AND idx.index_id = ios.index_id 
WHERE  OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY DATABASE_NAME, TOTAL_COUNT

Index Usage Stats 01
 

–> So, by using these two Dynamic Management Views/Functions you can know:

1. What is the affect (maintenance overhead) of Indexes you created, and

2. Are the Indexes really used or not so that you can DROP them.
 

Please Note: that these stats could be wrong at times because of several reasons, like:

1. If SQL Server (MSSQLSERVER) service is re-started these counters are initialized to 0.

2. When a Database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

3. Index Rebuild resets these counters to 0 (Bug logged in MS Connect, link).