TH3M4D0N3
asked on
How do I debug MSSQL 2005-2008
I need to debug the below code to see why its not working properly.
i want to see what records are inserted. currently all it says is (1 row(s) affected)
not very informative, how do i see what data worked and what data didnt?
i want to see what records are inserted. currently all it says is (1 row(s) affected)
not very informative, how do i see what data worked and what data didnt?
DECLARE @PersonsData nvarchar(250), @TempCount nvarchar(250), @AddressData nvarchar(250), @EmailData nvarchar(250), @TempStorage nvarchar(1024), @TempStorage2 nvarchar(1024);
DECLARE cursor_siteper CURSOR GLOBAL
FOR SELECT SitePersonnelID AS perIDList
FROM SitePersonnel
OPEN cursor_siteper;
FETCH NEXT FROM cursor_siteper INTO @TempCount;
WHILE @@FETCH_STATUS = 0
BEGIN
set @PersonsData = 'INSERT INTO Persons (LastName, Firstname, MiddleName, yearofbirth ) SELECT [BackupKeySymSQL_ltd].[dbo].[SitePersonnel].LastName, Firstname, Middlename, Birthday FROM [Backup_ltd].[dbo].[SitePersonnel] WHERE SitePersonnelID = ' + @TempCount + '';
Set @TempStorage2 = 'SELECT MAX(SID) AS TempStorage FROM persons';
set @AddressData = 'INSERT INTO Addresses (SID, AddressPrimary, AddressLine1, AddressLine2, City, State, Country, PostalCode, Phone, PhoneExt, Fax, JobTitle) ' + @TempStorage + ', 1, Select [Backup_ltd].[dbo].[SitePersonnel].addrline1, addrline2, City, State, Country, PostalCode, Phone, PhoneExt, Fax, JobTitle FROM [Backup_ltd].[dbo].[SitePersonnel] WHERE SitePersonnelID = ' + @TempCount + '';
set @EmailData = 'INSERT INTO Emails (SID, EmailPrimary, EmailAddress) ' + @TempStorage + ', 1, Select [Backup_ltd].[dbo].[SitePersonnel].Email WHERE SitePersonnelID = ' + @TempCount + '';
SET IDENTITY_INSERT Persons OFF
BEGIN TRANSACTION
BEGIN TRY
exec sp_executesql @PersonsData;
exec sp_executesql @TempStorage2;
print @TempStorage;
exec sp_executesql @AddressData;
exec sp_executesql @EmailData;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
SET IDENTITY_INSERT Persons ON
FETCH NEXT FROM cursor_siteper INTO @TempCount;
END
CLOSE cursor_siteper
DEALLOCATE cursor_siteper
GO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER