Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I debug MSSQL 2005-2008

Posted on 2008-10-15
4
Medium Priority
?
932 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 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 75 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 75 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 600 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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

783 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