September 3, 2009 at 3:37 pm
Currently we have multiple applications that archive data to the same database (and possibly the same table in the database). Every once and a while we will have one the applications create archive databases when our first archive isn't full, it doesn't happen when we only have one application archiving. Right now the stored procedure we use to get the record count of the db is
USE [DBNAME]
GO
/****** Object: StoredProcedure [dbo].[usp_CountRecords] Script Date: 09/03/2009 15:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_CountRecords]
-- Add the parameters for the stored procedure here
(
@databaseName nvarchar(100),
@tablename nvarchar(100),
@result int OUTPUT
)
AS
BEGIN
DECLARE @sql nvarchar(300)
SET @sql = N'SELECT @result = Count(*) FROM ' + @databaseName + N'.dbo.' + @tablename
EXEC sp_executesql @sql,
N'@result int OUTPUT',
@result OUTPUT
RETURN 0
END
I tried to modify the code found at http://qa.sqlservercentral.com/articles/T-SQL/67624/ to work for our needs and succeded in getting just the result returned when I ran it from a query. I then dropped in in the stored procedure
USE [GeoComm]
GO
/****** Object: StoredProcedure [dbo].[usp_CountRecords] Script Date: 09/03/2009 15:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_CountRecordsTest]
-- Add the parameters for the stored procedure here
(
@databaseName nvarchar(100),
@tablename nvarchar(100),
@result int OUTPUT
)
AS
BEGIN
DECLARE @sql nvarchar(max)
--SET @sql = N'SELECT @result = Count(*) FROM ' + @databaseName + N'.dbo.' + @tablename
SET @sql = N'SELECT @result = ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.NAME = ' + @tablename
EXEC sp_executesql @sql,
N'@result int OUTPUT',
@result OUTPUT
RETURN 0
END
[/code]
and I'm told that @tablename is not a valid table name, it doesn't matter what I pass to the variable when I execute the stored procedure. I want to make sure that count(*) being slow is not the cause of why we create multiple archive databases when we don't need to since I know selecting the count of up to 300,000 records can be very slow. I'm also aware sp_spaceused will return the number of rows used, but I'm not sure how to tell it to return just that and nothing else.
The SQL creation script the the stored procedure is being run against as this is the only one causing us issues.
[code]
USE [ArchiveDB1]
GO
/****** Object: Table [dbo].[ArchiveTable1] Script Date: 09/03/2009 16:52:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ArchiveTable1](
[UNIT_ID] [varchar](20) NOT NULL,
[UNIT_NAME] [varchar](50) NULL,
[UNIT_TYPE] [varchar](50) NULL,
[X] [float] NULL,
[Y] [float] NULL,
[LONGITUDE] [float] NULL,
[LATITUDE] [float] NULL,
[SPEED] [float] NULL,
[HEADING] [float] NULL,
[DIRECTION] [varchar](50) NULL,
[STREET_ADDRESS] [varchar](50) NULL,
[ESN] [varchar](50) NULL,
[COMMUNITY] [varchar](50) NULL,
[TIME_STAMP] [datetime] NOT NULL,
[STATUS] [varchar](50) NULL,
[STATUS_DESCRIPTION] [varchar](50) NULL,
[SYMBOL_ID] [varchar](50) NULL,
[AVL_MESSAGE] [text] NULL,
[OFFICER_ID] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[UNIT_ID] ASC,
[TIME_STAMP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[/code]
September 4, 2009 at 7:19 am
I think you're missing some quotes.
Try AND o.NAME = ''' + @tablename + ''''
September 4, 2009 at 7:25 am
dsdeming (9/4/2009)
I think you're missing some quotes.Try AND o.NAME = ''' + @tablename + ''''
Thank you, that fixed it. Always a good idea to have another set of eyes look at something 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply