Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  SQL Server 2005

Author  Topic  Dev@nlkss 134 Posts Posted - 2009-01-22 : 02:30:07 Hi All I am posting my sql script and tell me is this the correct way of maintaining transactions in single sp. after running this script if i try to open any of object from object explorer i am getting following error "You might not have permission to perform this operation, or the object <My table name> might no longer exist in the database. (MS Visual Database Tools)" please tell me urgent i struck up with this Thanks in advance Script is --> SET TRANSACTION ISOLATION LEVEL SNAPSHOT GO BEGIN SET NOCOUNT ON; --Load the Records from Staging tables to master table BEGIN TRY BEGIN TRANSACTION; IF((SELECT COUNT(VendorID) FROM [dbo].[Vendor_SHAPark]) > (SELECT COUNT(VendorID) FROM [dbo].[VendorSSIS])) BEGIN TRUNCATE TABLE [dbo].[VendorSSIS] INSERT INTO [dbo].[VendorSSIS] SELECT VendorID,VendorName,VendorType,CircleID FROM [dbo].[Vendor_SHAPark] COMMIT TRANSACTION END ELSE ROLLBACK TRANSACTION BEGIN TRANSACTION; IF((SELECT COUNT(DeptID) FROM [dbo].[DeptInfo_HRMSPark]) > (SELECT COUNT(DeptID) FROM [dbo].[DeptInfo])) BEGIN TRUNCATE TABLE [dbo].[DeptInfo] INSERT INTO [dbo].[DeptInfo] SELECT DeptID,DeptDesc FROM [dbo].[DeptInfo_HRMSPark] COMMIT TRANSACTION END ELSE ROLLBACK TRANSACTION BEGIN TRANSACTION; IF((SELECT COUNT(DesgID) FROM [dbo].[Designations_HRMSPark]) > (SELECT COUNT(DesgID) FROM [dbo].[Designations])) BEGIN TRUNCATE TABLE [dbo].[Designations] INSERT INTO [dbo].[Designations] SELECT DesgID,DesgName FROM [dbo].[Designations_HRMSPark] COMMIT TRANSACTION END ELSE ROLLBACK TRANSACTION BEGIN TRANSACTION; IF((SELECT COUNT(DISTINCT EmpID) FROM [dbo].[empdetails_hrmspark]) > (SELECT COUNT(DISTINCT EmpID) FROM [dbo].[Employees])) BEGIN TRUNCATE TABLE [dbo].[Employees] INSERT INTO [dbo].[Employees] SELECT DISTINCT ED.EmpID,ED.EmpCode,CL.Display AS DisplayName,NULL,NULL,Gender,CL.BirthDay AS DOB,DOJ,DelFlag AS HRMSStatus,ED.CircleID,Designation,DeptID FROM [dbo].[empdetails_hrmspark] ED INNER JOIN [dbo].[contactslist_hrmspark] CL ON ED.EmpID=CL.ContactID INSERT INTO [dbo].[ITOEngineerList](EmpID) SELECT EmpID FROM [dbo].[Employees] WHERE EmpID NOT IN(SELECT EmpID FROM [dbo].[ITOEngineerList]) END ELSE ROLLBACK TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH END SET NOCOUNT OFF; Satya