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 GLOBALFOR SELECT SitePersonnelID AS perIDListFROM SitePersonnelOPEN cursor_siteper;FETCH NEXT FROM cursor_siteper INTO @TempCount;WHILE @@FETCH_STATUS = 0BEGINset @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 OFFBEGIN TRANSACTIONBEGIN TRYexec sp_executesql @PersonsData;exec sp_executesql @TempStorage2;print @TempStorage;exec sp_executesql @AddressData;exec sp_executesql @EmailData;END TRYBEGIN 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 ONFETCH NEXT FROM cursor_siteper INTO @TempCount;ENDCLOSE cursor_siteperDEALLOCATE cursor_siteperGO
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.
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
TH3M4D0N3Author Commented:
Well im just using mark's idea for printing variables during execution and using transactions, thanks for the help guys.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.
If you are using SQL Server 2005, you need to use Visual Studio 2005/2008 to debug SPs.