Solved

How do I debug MSSQL 2005-2008

Posted on 2008-10-15
4
920 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
4 Comments
 
LVL 69

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now