October 8, 2009 at 8:01 am
The issue I'm experiencing with the LAST_RECORDS table is that it is not populating with any real data whereas the COMPLETED_RECORDS table is showing real data. The setup is supposed to grab data from the IMPORT_FROM_FILE table and populate 2 tables, somehow, only 1 of the 2 is getting it.
Lots of code below for you investigate as I've been trying for a while now to resolve this issue.. Any suggestions or guidance is greatly appreciated.
See stored procedure below for code which may or may not be setup correctly....
-- =============================================
-- Description:<this SP is used to move the records
-- from IMPORT_FROM_FILE table into COMPLETED_RECORDS
-- and LAST_RECORDS tables.
-- It also matches the MasteryNet codes with the LMS Codes>
-- =============================================
ALTER PROCEDURE [dbo].[populate_completed_and_last_records]
AS
BEGIN
--clean the Last_records table
DELETE FROM LAST_RECORDS
Declare @SAP As Varchar(8)
Declare @MasteryNet_code As Varchar(8)
Declare @LMS_code As Varchar(25)
Declare @finish_date As Varchar(10)
DECLARE cUNIQUE_ID CURSOR FOR
SELECT a.SAP, a.MasteryNet_code, b.LMS_code, a.finish_date
FROM IMPORT_FROM_FILE a, CROSS_REFERENCE b
WHERE a.MasteryNet_code = b.MasteryNet_code
ORDER BY a.SAP ASC, a.MasteryNet_code ASC
OPEN cUNIQUE_ID
FETCH NEXT FROM cUNIQUE_ID INTO @SAP, @MasteryNet_code, @LMS_code, @finish_date
WHILE @@FETCH_STATUS=0
BEGIN
Declare @rez As Int
DECLARE @QUERY AS Varchar(8000)
SET @rez = (SELECT count(*) As num_rows FROM COMPLETED_RECORDS WHERE SAP = @SAP AND
MasteryNet_code = @MasteryNet_code)
if(@rez = 0)
begin
INSERT INTO COMPLETED_RECORDS VALUES(@SAP,@MasteryNet_code,@LMS_code,@finish_date,CONVERT(varchar, GETDATE(), 101))
INSERT INTO LAST_RECORDS VALUES(@SAP,@MasteryNet_code,@LMS_code,@finish_date,CONVERT(varchar, GETDATE(), 101))
end
FETCH NEXT FROM cUNIQUE_ID INTO @SAP, @MasteryNet_code, @LMS_code, @finish_date
END
CLOSE cUNIQUE_ID
DEALLOCATE cUNIQUE_ID
END
---------------------
---------------------
See code below for table: LAST_RECORDS which doesn't seem to be populating with any records, except the NULL information below...
SAP MasteryNet_code LMS_code finish_date added_date
NULLNULL NULL NULL NULL
---------
When I open the table, LAST_RECORDS, to edit, this is what the code looks like...not sure if the problem exists in this table or if it's elsewhere...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LAST_RECORDS](
[SAP] [varchar](8) NOT NULL,
[MasteryNet_code] [varchar](8) NOT NULL,
[LMS_code] [varchar](25) NULL,
[finish_date] [varchar](10) NULL,
[added_date] [varchar](10) NULL,
CONSTRAINT [PK_LAST_RECORDS] PRIMARY KEY CLUSTERED
(
[SAP] ASC,
[MasteryNet_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
---------------
---------------
See code below for table: COMPLETED_RECORDS which seems to be populating just fine as it shows 3005 records, while the LAST_RECORDS table shows one record with all NULL values...
USE [MasteryNet]
GO
/****** Object: Table [dbo].[COMPLETED_RECORDS] Script Date: 10/08/2009 09:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COMPLETED_RECORDS](
[SAP] [varchar](8) NOT NULL,
[MasteryNet_code] [varchar](8) NOT NULL,
[LMS_code] [varchar](25) NULL,
[finish_date] [varchar](10) NULL,
[added_date] [varchar](10) NULL,
CONSTRAINT [PK_COMPLETED_RECORDS] PRIMARY KEY CLUSTERED
(
[SAP] ASC,
[MasteryNet_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------
I know this is alot of information for this early in the day, but an early start is the best thing in cases like this.
Thanks!
October 8, 2009 at 8:47 am
Are you getting any errors? While this is not how I'd go about it, it looks like it should be working.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2009 at 8:54 am
Hi Jack,
The strange thing is that I'm not getting any errors...the LAST_RECORDS table seems to be stuck in limbo and not able to receive the information that's being passed from the COMPLETED_RECORDS table...
I ran the DBCC SHOWCONTIG command a few minutes ago on the MasteryNet database to see what kind of results it would find....this is what came back: (i.e. the four tables associated with MasteryNet are COMPLETED_RECORDS, CROSS_REFERENCE, IMPORT_FROM_FILE, and LAST_RECORDS)
--
DBCC SHOWCONTIG(2137058649)PRINT ' '
DBCC SHOWCONTIG(2041058307)PRINT ' '
DBCC SHOWCONTIG(3)PRINT ' '
DBCC SHOWCONTIG(12)PRINT ' '
--
Results below...
---
DBCC SHOWCONTIG scanning 'COMPLETED_RECORDS' table...
Table: 'COMPLETED_RECORDS' (2137058649); index ID: 1, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 45
- Extents Scanned..............................: 9
- Extent Switches..............................: 42
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 13.95% [6:43]
- Logical Scan Fragmentation ..................: 51.11%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 3350.0
- Avg. Page Density (full).....................: 58.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'IMPORT_FROM_FILE' table...
Table: 'IMPORT_FROM_FILE' (2041058307); index ID: 1, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 26
- Extents Scanned..............................: 6
- Extent Switches..............................: 19
- Avg. Pages per Extent........................: 4.3
- Scan Density [Best Count:Actual Count].......: 20.00% [4:20]
- Logical Scan Fragmentation ..................: 38.46%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 3126.2
- Avg. Page Density (full).....................: 61.38%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 3504.7
- Avg. Page Density (full).....................: 56.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 400.0
- Avg. Page Density (full).....................: 95.06%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
---
October 8, 2009 at 9:01 am
2 things I'd try:
1. If it is not a production system run Profiler against the database while you run the procedure. You'll want to make sure you filter on database and add the sp:stmtcompleted event.
2. Again assuming it is not a production system, just run the insert into last_records to make sure it works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2009 at 9:19 am
It's worth a try...I'll give both options a run and see what happens, and whatever results I get, I'll reply back to this forum session.
thanks for your help!
Steve
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply