I've installed SQL Server 2000 trial on a standalone XP Pro machine as a local server, however the debugger does not work correctly. I will not allow me to step into the code of stored procedures. Can anyone provide me assistance in resolving this.
Before we go into the details, the looming question is:
"Why have you chosed SQL 2000 to eval, rather than SQL 2005 -that seems odd?"
|||Arnie, because I am a student at University of Cincinnati and that is what my database programming class is based on. I wanted to use the same version, get ahead in class and learn more than what the class will be covering. Also evaluating SQL Server will hopefully help me decide if I want to persue a career on the database side of things versus more of a visual studio or .net concentration.|||
More the reason to get a copy of SQL Server 2005. Most job opportunities (by the time you are looking) will be concerned with SQL 2005 skills. You will be able to accomplish just about everything using SQL 2005 Express that your class will be doing with SQL 2000. It's too bad that education instutitions are so moribund and change so slowly, When you finish, you will know a lot about a deprecated technology. I highly recommend learning SQL 2005.
Download SQL 2005 Express (free), or for about $50 you can buy the Developer's Edition of SQL Server (equilivent to the Enterprise Edition)
SQL Express is available here: http://msdn2.microsoft.com/en-us/sql/aa336346.aspx
SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx
SQL Server 2005 Express Edition (Advanced/SSMS/BI Toolkit)
http://tinyurl.com/yelwr9 (SSMSE)
http://tinyurl.com/ovcx3(Advanced Services)
http://tinyurl.com/23hg7n (BI Toolkit)
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
http://msdn2.microsoft.com/en-us/library/ms365247.aspx
SQL Server 2005 Express –Installation Details
http://msdn2.microsoft.com/en-us/library/ms143441.aspx
SQL Server 2005 Express Overview
http://msdn2.microsoft.com/en-us/library/ms345154.aspx
http://www.pcw.co.uk/personal-computer-world/software/2155087/review-microsoft-sql-server
SQL Server 2005 Express System Requirements
http://www.microsoft.com/sql/editions/express/sysreqs.mspx
|||I certainly appreciate your advice, however if you could see fit to help me with the Trial 2000 problem, I'd appreciate that. and if you choose not to, I understand and am thankful for your opinion|||
One of the reasons I was trying to direct you away from SQL 2000, is that the 'Debugger' is somewhat tempermental and problematic. To paraphase a recent series of drug and alcohol ads, most of us 'Just say No!'
Are you attempting to access the Debugger by right clicking on the stored procedure name in the Object Explorer?
And if so, what errors or other indicators are you getting that it is not working?
|||
Arnie, I wish I could just say no, however I don't have any spare cycles to deal with taking something I've done in 2005 to UC's 2000. I've got a rather heavy class load this quarter. I will take your advice and learn 2005 after this quarter's over.
Yes I'm accessing the debugger by right clicking on the name of the stored procedure from the object explorer. And what it does is open the debugger then the code executes immediately upon the debugger window dispalying without allowing me to start or step into the code.
Is there another way to start the debugger for a store procedure?
|||Could you post the procedure code that you are attempting to debug?|||yes i can, do I just copy and paste it to a post message?|||
Here is the code
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE spPFW_Load_Financial_Reporting_Data
AS
-
-- spPFW_Load_Financial_Reporting_Data
--
-- Is the main procedure that executes and controls the loading of Monthly PFW data into the tblPFW_Account_Header and
-- tblPFW_Financial_Reporting_Data tables.
--
-- It retrieves values from the tblPFW_Report_Line_Master, tblPFW_Account_Header & tblPFW_Monthly_Load_Data tables in
-- order to create new rows in both the tblPFW_Account_Header and tblPFW_Financial_Reporting_Data
--
-- It may execute the following store procedures if necessary:
-- master.dbo.xp_cmdshell
-- 1. to determine if there is a PFWDataLoad.txt file in the Load folder to process
-- 2. to execute the DTS PFW Data Load package that populates the tblPFW_Monthly_Load_Data from the PFWDataLoad.txt file
-- 3. to copy the PFWDataLoad.txt file to the Load History folder at end successful processing
-- 4. to delete the PFWDataLoad.txt file from Load folder at end successful processing
-- spPFW_Create_tblPFW_Data_Load_Message_Log to create the message log if it does not exist
-- spPFW_Write_Data_Load_Message to log load processing and/or error messages to tblPFW_Load_Data_Message_Log
-- spPFW_Create_tblPFW_Account_Header to create the Account Header table if it does not exist
-- spPFW_Create_tblPFW_Financial_Reporting_Data to create the Financial Reporting Data table if it does not exist
-- spPFW_Reset_tblPFW_Monthly_Load_Data to create an empty table for the DTS xxxxxx package to populate
-- spPFW_Update_Account_Header to determine if any new accounts were added to PFW & creates and inserts new Account
-- Header rows if necessary.
-- spPFW_Insert_Financial_Reporting_Data to insert all new Financial Reporting Data rows
--
-
--
-- The procedure first checks if a PFWDataLoad.txt file exists in the L:\Home\PFWtoNoetix\Load folder. If it does not,
-- there is nothing to process & it returns 0.
--
-- If a PFWDataLoad.txt file exists, The procedure either executues or does not execute the following 17 processing
-- steps based on @.LOAD_Status set in a previous step. It logs processing and/or error messages as it proceeds.
-- The first 7 steps check if the required tables and stored procedures exists. In some case, it will create the
-- non-existant tables. Step 8 drops & re-creates a new tblPFW_Monthly_Load_Data table. Step 9 runs the DTS PAckage
-- xxxxx to load it from the PFWDataLoad.txt file. Step 10 checks if the data being load is already present in the
-- tblPFW_Financial_Report_Data table. The steps 11 thru 15 are executed if the data has not been previously loaded.
-- Step 11 updates the tblPFW_Account_Header table with any new accounts found in the tblPFW_Monthly_Load_Data table.
-- Steps 12 thru 15 insert rows into the tblPFW_Financial_Report_Data for the new months data. Step 16 moves and renames
-- PFWDataLoad.txt to L:\Home\PFWtoNoetix\Load_History folder named as PFWDataLoad.YYYY.MM.txt with YYYY & MM equaling
-- the PFW_Year & PFW_Month just processed.
--
-- Step 17 finishes up the load process.
-- It only commits log messages to the tblPFW_Load_Data_Message_Log if a processing error occurs. All inserts
-- into the tblPFW_Account_Header & tblPFW_Financial_Report_Data tables were previously rolled back.
--
-- If no errors occur, then all inserts into the tblPFW_Load_Data_Message_Log, tblPFW_Account_Header &
-- tblPFW_Financial_Report_Data tables are committed to end the process.
--
-
--
-- The procedure does not update values in either the tblPFW_Account_Header or tblPFW_Financial_Report_Data tables, it
-- only inserts a new month of PFW financial data.
--
-
--
-- Load Processing Steps executed if a PFWDataLoad.txt file exists
-- 1. Check if tblPFW_Load_Data_Message_Log exists, if it does continue to step 2
-- If it does not exist, check if spPFW_Create_tblPFW_Data_Load_Message_Log exists, if that does not, abort load process
-- If spPFW_Create_tblPFW_Data_Load_Message_Log exists, create tblPFW_Data_Load_Message_Log
-- If tblPFW_Data_Load_Message_Log did not get created, abort the load process
--
-- 2. Check if spPFW_Reset_tblPFW_Monthly_Load_Data exists, if it does continue to step 3
-- If it does not exist, it's a hard stop, abort load process
--
-- 3. Check if spPFW_Update_Account_Header exists, if it does continue to step 4
-- If it does not exist, it's a hard stop, abort load process
--
-- 4. Check if spPFW_Load_Financial_Reporting_Data exists, if it does continue to step 5
-- If it does not exist, it's a hard stop, abort load process
--
-- 5. Check if tblPFW_Report_Line_Master exists
-- If it does exist, check it to make sure it's not empty, if it's empty abort the load process, else continue to step 6
-- If it does not exist, it's a hard stop, abort load process
--
-- 6. Check if tblPFW_Account_Header exists, if it does continue to step 7
-- If it does not exist, check if spPFW_Create_tblPFW_Account_Header exists, if it does not, abort load process
-- If spPFW_Create_tblPFW_Account_Header exists, create tblPFW_Account_Header
-- If tblPFW_Account_Header did not get created, abort the load process
--
-- 7. Check if tblPFW_Financial_Reporting_Data exists, if it does continue to step 8
-- If it does not exist, check if spPFW_Create_tblPFW_Financial_Reporting_Data exists, if it does not, abort load process
-- If spPFW_Create_tblPFW_AFinancial_Reporting_Data exists, create tblPFW_Financial_Reporting_Data
-- If tblPFW_Financial_Reporting_Data did not get created, abort the load process
--
-- 8. Drop existing tblPFW_Monthly_Load_Data, then recreate it
-- If it gets created, continue to step 9
-- If it does not get created, abort the load process
--
-- 9. Run DTS Package PFW Data Load to Load PFWDataLoad.txt file into tblPFW_Monthly_Load_Data
-- If it gets load successfully, continue to step 10
-- If it does not get loaded successfully, abort the load process
--
-- 10. Check if this month has already been added to tblPFW_Financial_Reporting_Data
-- If its already in tblPFW_Financial_Reporting_Data, abort load process, else continue to step 11
--
-- 11. Update Account Header table
-- If an error occured inserting Account Header rows, execute rollback, & abort load process
--
-- 12. Insert Balance Sheet - Account & Company Level rows into tblPFW_Financial_Reporting_Data
-- If an error occured while inserting Balance Sheet - Account or Company Level rows, execute rollback, & abort load process
--
-- 13. Insert Income Statement - Account Level rows into tblPFW_Financial_Reporting_Data
-- If an error occured while inserting Income Statement - Account Level rows, execute rollback, & abort load process
--
-- 14. Insert Income Statement - Company Level rows into tblPFW_Financial_Reporting_Data
-- If an error occured while inserting Income Statement - Company Level rows, execute rollback, & abort load process
--
-- 15. Insert Income Statement - BGI Consolidated Level rows into tblPFW_Financial_Reporting_Data
-- If an error occured while inserting Income Statement - BGI Consolidated Level rows, execute rollback, & abort load process
--
-- 16. Moves & renames PFWDataLoad.txt file to Load History folder as PFWDataLoad.YYYY.MM.txt
--
-- 17. Complete the Load Process
-- Log either Successfully Load Process or Error Occurred message
-- Commit any message rows and/or Financial Reporting Data Rows
--
-
-- Revisions
-
-- 1. Revision: Initial creation
-- Date: 3/20/2007
-- By: Greg Ouellette
-
-- Declare general use variables
DECLARE @.test int
DECLARE @.syserror int
DECLARE @.id int
DECLARE @.SP_Return int
DECLARE @.Report_Line_Master_Count int
DECLARE @.Load_Status int
DECLARE @.Load_PFW_Year int
DECLARE @.Load_PFW_Month int
DECLARE @.Load_YYYYMM int
DECLARE @.MoveCmd varchar (250)
SET @.Load_Status = 0
SET @.Load_PFW_Year = 9999
SET @.Load_PFW_Month = 99
EXEC @.Load_Status = master.dbo.xp_cmdshell 'dir L:\Home\PFW\Load\PFWDataLoad.txt', NO_OUTPUT
IF (@.Load_Status <> 0) -- There is no new PFWDataLoad.txt file to load
Return 0
--
-- Check if all the tables and stored procedures exist before loading the new PFWDataLoad.txt file
--
IF @.Load_Status = 0
-- 1. Check if tblPFW_Load_Data_Message_Log exists, if it does continue to step 2
-- If it does not exist, check if spPFW_Create_tblPFW_Data_Load_Message_Log exists, if it does not, abort load process
-- If spPFW_Create_tblPFW_Data_Load_Message_Log exists, create tblPFW_Data_Load_Message_Log
-- If tblPFW_Data_Load_Message_Log did not get created, abort the load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[tblPFW_Data_Load_Message_Log]'))
IF @.id = null
BEGIN
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[spPFW_Create_tblPFW_Data_Load_Message_Log]'))
IF @.id = null
SET @.Load_Status = -1
ELSE
BEGIN
EXECUTE @.sp_Return = spPFW_Create_tblPFW_Data_Load_Message_Log @.syserror
IF @.sp_Return = 0 -- tblPFW_Load_Log created successfully, log msg
BEGIN
EXECUTE @.sp_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG001A'
IF @.sp_Return <> 0
SET @.Load_Status = -1 -- Write message failed
END
ELSE -- tblPFW_Load_Log was not created, can not insert message, it's a hard stop, abort load process
SET @.Load_Status = -1
END
END
END
IF @.Load_Status = 0 -- Log Load Process Started msg
BEGIN
EXECUTE @.sp_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0000'
IF @.sp_Return <> 0
SET @.Load_Status = -1 -- Write message failed
END
IF @.Load_Status = 0
-- 2. Check if spPFW_Reset_tblPFW_Monthly_Load_Data exists
-- If it does not exist, it's a hard stop, abort load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[spPFW_Reset_tblPFW_Monthly_Load_Data]'))
IF @.id = null /* spPFW_Reset_Monthly_Load_Data does not exist, log msg, set @.Load_Status to abort load process */
BEGIN
SET @.Load_Status = -2
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG002A'
END
END
IF @.Load_Status = 0
-- 3. Check if spPFW_Update_Account_Header exists
-- If it does not exist, it's a hard stop, abort load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[spPFW_Update_Account_Header]'))
IF @.id = null /* spPFW_Update_Account_Header does not exist, log msg, set @.Load_Status to abort load process */
BEGIN
SET @.Load_Status = -3
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG003A'
END
END
IF @.Load_Status = 0
-- 4. Check if spPFW_Insert_Financial_Reporting_Data exists
-- If it does not exist, it's a hard stop, abort load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[spPFW_Insert_Financial_Reporting_Data]'))
IF @.id = null /* spPFW_Create_tblPFW_Monthly_Load_Data does not exist, log msg, set @.Load_Status to abort load process */
BEGIN
SET @.Load_Status = -4
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG004A'
END
END
IF @.Load_Status = 0
-- 5. Check if tblPFW_Report_Line_Master exists
-- If it does not exist, it's a hard stop, abort load process, else check it to make sure it's not empty
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[tblPFW_Report_Line_Master]'))
IF @.id = null /* tblPFW_Report_Line_Master does not exist, log message, set load status to abort */
BEGIN
SET @.Load_Status = -5
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG005A'
END
ELSE
BEGIN
SELECT @.Report_Line_Master_Count = COUNT(DISTINCT [Report Line]) FROM dbo.tblPFW_Report_Line_Master
IF @.Report_Line_Master_Count = 0 -- tblPFW_Report_Line_Master exists, however no rows in table, log message, set load status to abort
BEGIN
SET @.Load_Status = -5
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG005B'
END
END
END
IF @.Load_Status = 0
-- 6. Check if tblPFW_Account_Header exists, if it does continue next step
-- If it does not exist, check if spPFW_Create_tblPFW_Account_Header exists, if it does not, abort load process
-- If spPFW_Create_tblPFW_Account_Header exists, create tblPFW_Account_Header
-- If tblPFW_Account_Header did not get created, abort the load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[tblPFW_Account_Header]'))
IF @.id = null /* tblPFW_Account_Header does not exist, create it */
BEGIN
SELECT @.id = id FROM dbo.sysobjects WHERE (id = object_id(N'[dbo].[spPFW_Create_tblPFW_Account_Header]'))
IF @.id = null /* spPFW_Create_tblPFW_Account_Header does not exist, abort load process */
BEGIN
SET @.Load_Status = -6
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG006A'
END
ELSE -- spPFW_Create_tblPFW_Account_Header exists, create tblPFW_Account_Header
BEGIN
EXECUTE @.SP_Return = spPFW_Create_tblPFW_Account_Header @.syserror
IF @.SP_Return <> 0 -- Error creating tblPFW_Account_Header, log msg, set Load Status to abort Load Process
BEGIN
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -6
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG006B'
END
ELSE -- tblPFW_Account_Header created, log msg,
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG006C'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -6
END
END
END
END
IF @.Load_Status = 0
-- 7. Check if tblPFW_Financial_Reporting_Data exists, if it does continue next step
-- If it does not exist, check if spPFW_Create_tblPFW_Financial_Reporting_Data exists, if it does not, abort load process
-- If spPFW_Create_tblPFW_Financial_Reporting_Data exists, create tblPFW_Financial_Reporting_Data
-- If tblPFW_Financial_Reporting_Data did not get created, abort the load process
BEGIN
SET @.id = null
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[tblPFW_Financial_Reporting_Data]'))
IF @.id = null /* tblPFW_Financial_Reporting_Data does not exist */
BEGIN
SELECT @.id = id FROM dbo.sysobjects WHERE id = (object_id(N'[dbo].[spPFW_Create_tblPFW_Financial_Reporting_Data]'))
IF @.id = null /* spPFW_Create_tblPFW_Financial_Reporting_Data does not exist, log msg, set @.Load_Status to abort load process */
BEGIN
SET @.Load_Status = -7
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG007A'
END
ELSE -- spPFW_Create_tblPFW_Financial_Reporting_Data does exist, create tblPFW_Financial_Reporting_Data
BEGIN
EXECUTE @.SP_Return = spPFW_Create_tblPFW_Financial_Reporting_Data @.syserror
IF @.SP_Return <> 0 -- Error creating tblPFW_Financial_Reporting_Data, log msg, set Load Status to abort Load Process
BEGIN
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -7
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG007B'
END
ELSE -- tblPFW_Financial_Reporting_Data created, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG007C'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -7
END
END
END
END
--
-- If all the tables and stored procedures exist, then load tblPFW_Monthly_Load_Data from PFWDataLoad.txt file
--
IF @.Load_Status = 0
-- 8. Reset tblPFW_Monthly_Load_Data so new month's data can be loaded
BEGIN
EXECUTE @.SP_Return = spPFW_Reset_tblPFW_Monthly_Load_Data @.syserror
IF (@.SP_Return <> 0) -- Error resetting tblPFW_Monthly_Load_Data, log msg, set status to abort load process
BEGIN
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -8
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG008A'
END
ELSE -- tblPFW_Monthly_Load_Data was reset successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG008B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -8
END
END
IF @.Load_Status = 0
-- 9. Run DTS Package to Load PFWDataLoad.txt file into tblPFW_Monthly_Load_Data
--call DTSRun to execute it (via xp_cmdshell), etc. Hope this helps.
--dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
--dtsrun /Ffilename /Npackage_name /Mpackage_password
BEGIN
EXECUTE @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /SBLUEMOON\BIGO /UBigO /P74654o /NPFW Data Load /M74654o'
--EXEC @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /DTSRun /~Z0x2F3FF8E9164A7A7241D0849BD24CA7247E86CB4EECC129B5E26FD37D413B9B2ADBDE9C1D917AFF317DA388EF1B0A2FB233E9E765955B37B1BEE4CE9BA73460D04C312F8FFD2B8127516D0D5F546781F994DBE96BFA75E30E2D313C194341153CF1B3D539E1FEE5A4B28AD6A300303D06AFEBF82ABBA7AAB5B7F278F5984E3C44F9374B'
IF (@.SP_Return <> 0) -- Load of tblPFW_Monthly_Load_Data from PFWDataLoad.txt failed, log msg, set load status to abort process
BEGIN
SET @.Load_Status = -9
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG009A'
END
ELSE -- tblPFW_Monthly_Load_Data was created successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG009B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -9
END
END
IF @.Load_Status = 0
-- 10. Load of tblPFW_Monthly_Load_Data successful, get PFW Year & PFW Month, check if this month has already been added to
-- tblPFW_Financial_Reporting_Data, if so, log msg & set load status to abort load process
BEGIN
SELECT @.Load_PFW_Year = MAX(DISTINCT [PFW Year]) FROM dbo.tblPFW_Monthly_Load_Data
SELECT @.Load_PFW_Month = MAX(DISTINCT [PFW Month]) FROM dbo.tblPFW_Monthly_Load_Data
SELECT @.Load_YYYYMM = MAX(DISTINCT YYYYMM) FROM dbo.tblPFW_Financial_Reporting_Data WHERE ([PFW Year] = @.Load_PFW_Year) AND ([PFW Month] = @.Load_PFW_Month)
IF @.Load_YYYYMM <> null /* Monthly data has already been loaded, log MSG & set load status */
BEGIN
SET @.Load_Status = -10
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG010A'
END
END
COMMIT -- All log messages to this point
--
-- If all the tables and stored procedures exist, and tblPFW_Monthly_Load_Data was loaded successfully, then determine if there have been
-- any accounts added to PFW during the month and update tblPFW_Account_Header if so, then create & insert the new PFW Year & PFW Month rows
-- into tblPFW_Financial_Reporting_Data
--
IF @.Load_Status = 0
-- 11. Update Account Header table
BEGIN
EXECUTE @.SP_Return = spPFW_Update_Account_Header @.syserror
IF @.SP_Return <> 0 -- Error occured inserting Account Header rows, execute rollback, log msg & set load status
BEGIN
ROLLBACK TRANSACTION
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -11
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0011A'
END
ELSE -- Account Header table was updated successfully log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0011B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -11
END
END
IF @.Load_Status = 0
-- 12. Load Balance Sheet - Account & Company Level rows into tblPFW_Financial_Reporting_Data
BEGIN
EXECUTE @.SP_Return = spPFW_Insert_Financial_Reporting_Data 'Balance Sheet', 'All', @.syserror
IF @.SP_Return <> 0 -- Error occured while inserting Balance Sheet - Account or Company Level rows, execute rollback, log msg & set load status
BEGIN
ROLLBACK TRANSACTION
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG011B' -- Rewrite previous msg dropped in Rollback
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -12
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG012A'
END
ELSE -- Balance Sheet - Account & Company rows were inserted successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0012B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -12
END
END
IF @.Load_Status = 0
-- 13. Load Income Statement - Account Level rows into tblPFW_Financial_Reporting_Data
BEGIN
EXECUTE @.SP_Return = spPFW_Insert_Financial_Reporting_Data 'Income Statement', 'Account', @.syserror
IF @.SP_Return <> 0 -- Error occurred inserting Income Statement - Account Level rows, execute rollback, log msg & set load status
BEGIN
ROLLBACK TRANSACTION
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG012B' -- Rewrite previous msg dropped in Rollback
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -13
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG013A'
END
ELSE -- Income Statement - Account Level rows were inserted successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0013B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -13
END
END
IF @.Load_Status = 0
-- 14. Create & insert Income Statement - Company Level rows into tblPFW_Financial_Reporting_Data
BEGIN
EXECUTE @.SP_Return = spPFW_Insert_Financial_Reporting_Data 'Income Statement', 'Company', @.syserror
IF @.SP_Return <> 0 -- Error occurred inserting Income Statement - Company Level rows, execute rollback, log msg & set load status
BEGIN
ROLLBACK TRANSACTION
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG013B' -- Rewrite previous msg dropped in Rollback
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -14
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG014A'
END
ELSE -- Income Statement - Company Level rows were inserted successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG0014B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -14
END
END
IF @.Load_Status = 0
-- 15. Create & insert Income Statement - Consolidated Level rows into tblPFW_Financial_Reporting_Data
BEGIN
EXECUTE @.SP_Return = spPFW_Insert_Financial_Reporting_Data 'Income Statement', 'Consolidated', @.syserror
IF @.SP_Return <> 0 -- Error occurred inserting Income Statement Consolidated Level Data, execute rollback, log msg & set load status
BEGIN
ROLLBACK TRANSACTION
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG014B' -- Rewrite Msg, dropped in Rollback
IF @.syserror <> 0 -- log the @.@.error and sysmessages description
EXECUTE spPFW_Write_Data_Load_Message @.syserror, @.Load_PFW_Year, @.Load_PFW_Month, 'sysmsg'
SET @.Load_Status = -15
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG015A'
END
ELSE -- Income Statement - Consolidated Level rows were inserted successfully, log msg
BEGIN
EXECUTE @.SP_Return = spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG015B'
IF @.SP_Return <> 0 -- Write message failed
SET @.Load_Status = -15
END
END
IF @.Load_Status = 0
-- 16. Copy PFWDataLoad.txt file to Load History folder
BEGIN
SET @.MoveCmd = 'move L:\Home\PFW\Load\PFWDataLoad.txt L:\Home\PFW\Load_History\PFWDataLoad.' + cast(@.Load_PFW_Year as char(4)) + '.' + cast(@.Load_PFW_Month as varchar(2)) + '.txt'
EXEC @.SP_Return = master.dbo.xp_cmdshell @.MoveCmd
IF (@.SP_Return <> 0) -- Copy of PFWDataLoad.txt to Load History folder failed
BEGIN
SET @.Load_Status = -16
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG016A'
END
ELSE -- Copy of PFWDataLoad.txt to Load History folder successful
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG016B'
END
-- 17. Complete the Load Process
IF @.Load_Status = 0 -- Load Process completed successfully, log msg
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG017B'
ELSE -- Error occurred in Load Process, log msg
EXECUTE spPFW_Write_Data_Load_Message @.Load_Status, @.Load_PFW_Year, @.Load_PFW_Month, 'Load MSG017A'
COMMIT -- Commit all inserts into tblPFW_Financial_Reporting_Data any remaining inserts into tblPFW_Data_Load_Message_Log
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|||Is there anyone that can assisted me|||
Have you configured as in this article?
http://msdn2.microsoft.com/en-us/library/6ksc0a82(vs.80).aspx
|||Thanks for getting back to me, yes the config has been set to allow for both the system and the user Greg|||
Arnie, This is the message that is entered into the event viewer after I run the debugger.
SQL Server when started as service must not log on as System Account. Reset to logon as user account using Control Panel.
I tried adding another account as an administrator, then changing my initial account (Greg) to limited, but that didn't work either
|||
It sounds like SQL Server is using the wrong account.
Perhaps these will help.
Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
Jamaz wrote: