SQL Server Questions | SQL with Manoj

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.

Categories: SQL Server Questions Tags: MSDN TSQL forum, String Concat

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.

Categories: SQL Server Questions Tags: DELETE FK Records, Foreign Key, MSDN TSQL forum

Standardize TRY CATCH block across all Stored Procedures – MSDN TSQL forum

March 31, 2015 Leave a comment

–> Question:

I am trying to develop best try catch block to be standardized in all of the SPs. Below is what I have right now developed which is giving me proper formatted output, would like to add more details or remove unwanted items from it.

Thanks for all your help!

I am using 2008 R2 Version, would be migrating to 2012 soon.

BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
     ROLLBACK TRANSACTION
  END

DECLARE @ErrorMsg varchar(2100);

SET  @ErrorMsg =  ' DBName        = ' + DB_NAME() + CHAR(10)
+ ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047), LEFT(Error_Message(),2044))) + CHAR(10) 
+ ' Procedure     = ' + OBJECT_NAME(@@PROCID) + CHAR(10) + ' ErrorNumber   = ' + 
LTRIM(CONVERT(VARCHAR(9),Error_Number()))  + CHAR(10) 
+ ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State())) + CHAR(10)
+ ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))  + CHAR(10)
+ ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))  + CHAR(10)
+ ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121) + CHAR(10) + ' ErrorBy = ' + SUSER_SNAME();

RAISERROR(@ErrorMsg,16,1);
    RETURN -1
END CATCH

–> My Answer:

The way you’ve drafted your TRY-CATCH looks good, however you can also consider few things:

1. Use THROW instead of RAISERROR as recommended by Microsoft, this will also reduce unnecessary (at times) declaration and assignment of error related variables, link.

2. With THROW you can also catch multiple errors, RAISERROR will only throw last error raised, link.

3. Make use of XACT_ABORT only when you want to rollback the entire transaction, link.

Check all these and other stuff about TRY-CATCH in my blog posts.

Ref Link.

Categories: SQL Server Questions Tags: Exception Handling, MSDN TSQL forum, TRY-CATCH