Solved

How do I debug MSSQL 2005-2008

Posted on 2008-10-15
4
919 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Log File Space 6 34
Problem with SqlConnection 5 117
Need Counts 11 42
t-sql splitting string column 5 26
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

18 Experts available now in Live!

Get 1:1 Help Now