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?

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

Open in new window

TH3M4D0N3Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
2008 as per emoreau's comment

any of them... Use a print of the variables before you execute, and (from the messages tab) copy that command and manually run it interactively. Best way I have found so far - highly manual but highly visible and a very good way to check the format of dynamic sql (and those pesky imbedded quotes).

Also, start by setting up the expected results. Helps formulate the needed sql to achieve those outputs, then you just need to match the results.
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
If you are using SQL Server 2008, you have the Debug menu option right into SSMS.

If you are using SQL Server 2005, you need to use Visual Studio 2005/2008 to debug SPs.
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

You could use a trigger on the key one or two tables, and use that to write an audit trail, or evaluate lumigent's log explorer to view the transaction log.

HTH
  David
0
 
TH3M4D0N3Author Commented:
Well im just using mark's idea for printing variables during execution and using transactions, thanks for the help guys.
0
All Courses

From novice to tech pro — start learning today.