March 7, 2011 at 3:38 am
Hi there!
In short, the code below works in SQL 2008, but generates the following error when executed on SQL 2005, for every insert that fails:
Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
..
Code:
-- Note:Some (unimportant) parts of the code are left out on purpose. E.g:
--The table [dbo].[DatabaseSizes] that saves the collected database sizes
--exists and has the correct columns and data types.
-- If not exists create temporary table
CREATE TABLE #AllocTable
(
Database_idsmallintPRIMARY KEY,
Database_Namesysname,
AllocPagesint
);
-- Cursor inserts database names and the SUM of total_pages from sys.allocation_units, for all databases. E.g:
-- INSERT #AllocTable (Database_id,Database_Name,AllocPages) values (1,'master', (SELECT sum(total_pages) FROM [master].sys.allocation_units))
-- Summarize the data files
WITH SumData AS
(
SELECT d.database_id, SUM(d.size) AS [DataSize]
FROM sys.master_files d
WHERE d.type = 0
GROUP BY d.database_id
),
-- Summarize the log files
SumLog AS
(
SELECT l.database_id, SUM(l.size) AS [LogSize]
FROM sys.master_files l
WHERE l.type = 1
GROUP BY l.database_id
)
-- Here something goes wrong on SQL 2005, but works on 2008
INSERT INTO [dbo].[DatabaseSizes]
SELECT
LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize) + CONVERT (DEC (15,2), sl.[LogSize])) * 8192 / 1048576,15,2)) 'Size'
,LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize)) * 8192 / 1048576,15,2)) 'Reserved'
,LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize) - CONVERT (DEC (15,2), at.AllocPages)) * 8192 / 1048576,15,2)) 'Unused'
,DB_NAME(sd.database_id) 'Name'
FROM SumData sd
JOIN SumLog sl
ON sd.database_id = sl.database_id
JOIN #AllocTable at
ON sl.database_id = at.Database_id
If anyone has any idea and an answer on why this doesn't work, I would be very thankful!
Sincerely,
Gord
March 7, 2011 at 5:51 am
I cant see anything wrong, the only issue may be the DB_NAME function, out of curisoity have you tried substituting it with the at.Database_Name reference from the Temp Table?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2011 at 5:51 am
Well, the code you've posted is correct in terms of syntax, but it's fairly difficult for anyone to work out what's wrong without the full code (and DDL to create any tables, insert data etc).
Off the top of my head, is there an insert trigger on DatabaseSizes?
March 7, 2011 at 7:06 am
Thank you for your answers!
Jason: Tried substituting the function with at.Database_Name, but unfortunately it didn't help... Good point though to use an already existing value, instead of the overhead of using a function! Your suggestion is included in the code sample below.
Howard: No, there's no insert trigger on DatabaseSizes.
Yes, I understand that it's difficult to find a solution to the problem, when not all code is presented. I noticed when modifying the code that it's not in the INSERT statement the error is generated, but in the SELECT.
The complete code for generating the error on SQL 2005, just copy paste, F5:
SET NOCOUNT ON
IF(OBJECT_ID('tempdb..#AllocTable')) > 0
BEGIN
TRUNCATE TABLE #AllocTable
END
ELSE
BEGIN
CREATE TABLE #AllocTable
(
Database_idsmallintPRIMARY KEY,
Database_Namesysname,
AllocPagesint
)
END
DECLARE @dbid smallint, @dbname sysname, @stmt nvarchar(1000)
-- Cursor for looping through allocated pages
DECLARE AllocationCur CURSOR FAST_FORWARD FOR
SELECT database_id, name FROM sys.databases
OPEN AllocationCur
FETCH NEXT FROM AllocationCur INTO @dbid, @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = N'INSERT #AllocTable (Database_id,Database_Name,AllocPages) values (' + convert(nvarchar(5),@dbid) + ',''' + @dbname + ''', (SELECT sum(total_pages) FROM ' + QUOTENAME(@dbname) + '.sys.allocation_units))'
EXEC sp_executesql @stmt
FETCH NEXT FROM AllocationCur INTO @dbid, @dbname
END
CLOSE AllocationCur
DEALLOCATE AllocationCur;
-- Summarize the data files
WITH SumData AS
(
SELECT d.database_id, SUM(d.size) AS [DataSize]
FROM sys.master_files d
WHERE d.type = 0
GROUP BY d.database_id
),
-- Summarize the log files
SumLog AS
(
SELECT l.database_id, SUM(l.size) AS [LogSize]
FROM sys.master_files l
WHERE l.type = 1
GROUP BY l.database_id
)
SELECT
LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize) + CONVERT (DEC (15,2), sl.[LogSize])) * 8192 / 1048576,15,2)) 'Size'
,LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize)) * 8192 / 1048576,15,2)) 'Reserved'
,LTRIM(STR((CONVERT (DEC (15,2),sd.DataSize) - CONVERT (DEC (15,2), at.AllocPages)) * 8192 / 1048576,15,2)) 'Unused'
,at.Database_Name 'Name'
FROM SumData sd
JOIN SumLog sl
ON sd.database_id = sl.database_id
JOIN #AllocTable at
ON sl.database_id = at.Database_id
More suggestions and ideas are appreciated!
Thanks in advance!
Gord
March 7, 2011 at 7:21 am
The problem isnt in the CTE its in the Dynamic SQL thats within the cursor, where you have SUB Select in the Insert statement.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2011 at 7:45 am
For SQL Server 2005 you need to do a little more work,
SET @stmt = N'DECLARE @AllocPages int;
SELECT @AllocPages=sum(total_pages) FROM '
+ QUOTENAME(@dbname) + '.sys.allocation_units;
INSERT #AllocTable (Database_id,Database_Name,AllocPages)
values (' + convert(nvarchar(5),@dbid) + ',''' + @dbname + ''',@AllocPages )'
as you can see I've created a variable in the Dynamic SQL statement and assigned the sum to it, then run the insert using the value.
Hope this helps.
PS : SQL 2008 does allow for sub selects in the Values clause of an insert statement.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2011 at 8:41 am
Worked like a charm! 🙂
Thank you very much Jason, for your fast reply and nice solution of my problem!
Btw, now I definitely know the importance of posting the whole code snippet! 😉
March 7, 2011 at 9:28 am
Glad to help, I did find the problem interesting hence the quick response. 🙂
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply