Solved

How do I debug MSSQL 2005-2008

Posted on 2008-10-15
4
923 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:TH3M4D0N3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 25 total points
ID: 22726869
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 25 total points
ID: 22728024
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 200 total points
ID: 22732875
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
 

Author Closing Comment

by:TH3M4D0N3
ID: 31506529
Well im just using mark's idea for printing variables during execution and using transactions, thanks for the help guys.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question