September 15, 2008 at 11:08 am
There are errors, associated with the query. The error Code is -1073548784.
Follows is the Error Description (note the 2 DECLARE Statements):
Executing the query "CREATE TABLE [tempdb].[dbo].[DASD_DataFile]([File Id] [smallint] NULL, [Group Id] [smallint] NULL,
[Total Extents] [int] NULL, [SpaceUsed(MB)] [real] NULL, [File Name] [nvarchar](128) NULL, [Physical File] [nvarchar](260) NULL) ON
[PRIMARY]
DECLARE ? nvarchar(60)
DECLARE ? nvarchar(2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
and status&32 = 0
OPEN c_db FETCH NEXT
FROM c_db
INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN
SET ? = 'USE [' + ? + '] DBCC SHOWFileStats WITH NO_INFOMSGS' INSERT [tempdb].[dbo].[DASD_DataFile] EXECUTE (?)
UPDATE [tempdb].[dbo].[HoldforEachDB]
SET [SpaceUsed(MB)] = s.[SpaceUsed(MB)] * 64 / 1024.00
FROM [tempdb].[dbo].HoldforEachDB f, [tempdb].[dbo].DASD_DataFile s
WHERE f.[FileId] = s.[File Id] AND f.[GroupId] = s.[Group Id] AND
f.[DatabaseName] = ? TRUNCATE TABLE [tempdb].[dbo].[DASD_DataFile] FETCH NEXT
FROM c_db
INTO @DBName END DEALLOCATE c_db" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
September 15, 2008 at 11:22 am
That looks like the SQL for "Update Data Used Size", but somehow many of the variables have been changed to "?". You might try downloading the package again.
Here is what it should look like in the SSIS package and when it is executed.
IF EXISTS
(SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[DASD_DataFile]')
)
DROP TABLE [tempdb].[dbo].[DASD_DataFile]
GO
CREATE TABLE [tempdb].[dbo].[DASD_DataFile](
[File Id] [smallint] NULL,
[Group Id] [smallint] NULL,
[Total Extents] [int] NULL,
[SpaceUsed(MB)] [real] NULL,
[File Name] [nvarchar](128) NULL,
[Physical File] [nvarchar](260) NULL
) ON [PRIMARY]
DECLARE @DBName nvarchar(60)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFileStats WITH NO_INFOMSGS'
INSERT [tempdb].[dbo].[DASD_DataFile]
EXECUTE(@SQLString)
--
UPDATE [tempdb].[dbo].[HoldforEachDB]
SET [SpaceUsed(MB)] = s.[SpaceUsed(MB)]*64/1024.00
FROM [tempdb].[dbo].HoldforEachDB f,
[tempdb].[dbo].DASD_DataFile s
WHERE f.[FileId] = s.[File Id]
AND f.[GroupId] = s.[Group Id]
AND f.[DatabaseName] = @DBName
TRUNCATE TABLE [tempdb].[dbo].[DASD_DataFile]
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
David Bird
September 17, 2008 at 9:02 am
I'm getting the following errors:
ServerTaskNameErrorCodeErrorDescription
MY_SQL2Collect Backup History3Variable "User::SQL_RS" does not contain a valid data object
MY_SQL2Collect Database Status3Variable "User::SQL_RS" does not contain a valid data object
MY_SQL2Collect Disk Space - requires OLE Automation enabled on 20053Variable "User::SQL_RS" does not contain a valid data object
MY_SQL2Collect ErrorLog3Variable "User::SQL_RS" does not contain a valid data object
MY_SQL22Collect Job History3Variable "User::SQL_RS" does not contain a valid data object
MY_SQL2Collect Job Info3Variable "User::SQL_RS" does not contain a valid data object
MySQL2 is the server with the package installed on it. There are several other servers in the list, but I'm not getting the error on them.
I've checked the SQL_RS & SQL_Conn variables in each step, they are all the correct case, etc.
Also, I could not determine where the error codes & messages are being set - can you explain.
Do you have any suggestions regarding the errors?
Thanks
September 17, 2008 at 12:48 pm
Sorry for answering your question with more questions.
What is the version and edition SQL server being used on the server returning the errors and the one running the package? In addition, what is the collation (dictionary) setting being used on both servers?
David Bird
September 17, 2008 at 1:15 pm
All servers are running W2k3 SP2
All but 1 server are running on is running Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Collation is SQL_Latin1_General_CP1_CI_AS
One server is running Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)
Collation is SQL_Latin1_General_CP1_CI_AS
Server with error is same server running package.
October 16, 2008 at 10:02 am
I am trying to use the package but have a few problems. It seems like all of the Foreach Loop Conatiners are not actually looping through each server. I have 3 servers set to be colloected in SSIS_ServerList ( if I can get it to work it will be about 100). However, it only runs for the first server in the list. It will run each container 3 times but still only use the first server in the list.
I am new to BI studio so how can I find the problem and correct it?
Bryan
October 16, 2008 at 2:32 pm
It sounds like the server value from the table SSIS_ServerList is not being mapped properly to the variable SQL_RS. Check the container Populate ADO Variable. Copy the SQL Statement and execute it a query window and confirm all three servers are being retrieved.
Next in the same container confirm the Result Set is mapping with Result Name = 0 and Variable Name User::SQL_RS.
You might want to try the smaller SQL Overview package in Part 1 of this series of article and see if you can get that package working with all three servers.
If the same server continues to be used, remove it from the table SSIS_ServerList and see if another server is processed.
If you find a solution to your problem please post it.
David Bird
November 4, 2008 at 3:35 pm
Hi,
I love this SSIS package. However because I have over a thousand SQL Servers that I would like to monitor I am breaking it in to smaller parts. I am working on one project that includes the Collect Job Info, the Collect Job History, and the Collect Backup History modules.
Somehow the Collect Job Info collects all the data, but the Collect Backup History fails to collect all the data because it fails to log on to some of the servers, and the Collect Job History fails to log on to any server. I am trying to figure out why this is the case. I have been checking the modules for differences. So far the only differences I can find are the way the servers are specified in the queries.
Collect Job Info:
SELECT @@SERVERNAME AS Server
In Collect Backup History:
SELECT substring(@@SERVERNAME,1,128) AS Server,
and in Collect Job History the servers are not specified.
However this is exactly how the queries appear in the source example.
What else might be causing the issue?
Thanks
March 3, 2009 at 2:14 pm
Great stuff.
I have a question if I may. This may be the wrong place for this.
I would like to create an email report relating to the disk sizes as a percentage graph. but would ultimately understand how to do this for future reports.
Simple html version:
_________________________________
|||||||||||||||||||||| |
_________________________________|
My more complex non working version:
DECLARE @TableHTML2 NVARCHAR(MAX) ;
SET @TableHTML2 =
N' ' +
N' ' +
N' ' +
N' ' +
CAST ( (
SELECT td=' ',''
FROM [Disk_Space]
ORDER BY [Server]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
I have also tried:
SELECT '<table width="100" border="0" frame="box" style="border-collapse: collapse"><tr><td width="' + CAST([Free(%)] AS VARCHAR) + '%" bgcolor="Red"></td><td width="' + CAST(100-[Free(%)]AS VARCHAR) + '%"></td></tr></table>"/>'
My email results is the raw html code.
If I was saving the results to a file which is something I plan on in the future this would be ok.
Is there a way for this to email as a an email?
Thank you.
March 3, 2009 at 3:29 pm
Nevermind. 🙂
I believe I figured it out. If anyone wants it. This will pull the percentages and create a percentage bar using html table cells. Simple really after I formatted the query right. Only wish you could make pie graphs like this rather than using SVG or the like.
DECLARE @TableHTML2 NVARCHAR(MAX) ;
SET @TableHTML2 =
CAST ( (
SELECT ' '
FROM [Disk_Space]
ORDER BY [Server]
FOR XML PATH(''), TYPE
) AS NVARCHAR(MAX) ) ;
--PRINT Replace(Replace(Replace(@TableHTML2, '<', ' '), '&', '&')
SET @TableHTML2 = Replace(Replace(Replace(@TableHTML2, '<', ' '), '&', '&')
Thank you again. Awesome package.
June 12, 2009 at 10:21 am
David - I wanted to thank you very much for sharing this package. It is very helpfull.
I am having one problem though. I have both SQL2K and 2K5 boxes and it collects all of the data for all of the servers in to the SQL_Servers list which is great but in the Disk_Space table it does not populate the 2K5 servers in to this table. I thought I read that the XP_Fixeddrives that the owner changed on 2K5 to sys from dbo. Do you have anythoughts about this?
Scott
June 12, 2009 at 12:53 pm
Please disregard my post because once I learned how to read I found the text "requires OLE Automation enabled on 2005".
Doh!
June 15, 2009 at 7:23 am
SQL_Overview SSIS package for SQL 2008
Hi,
I have been using the SQL_Overview SSIS package specifically SQL_OVERVIEW_Part3 containing SQL_Overview_Package.dtsx in SQL 2005 quite some time with no issues. I copied the package and Database over to my SQL server 2008 server, Ran the Visual Studio Conversion Wizard which ran the SSIS Package Upgrade Wizard,
I try to migrate it to a SQL server 2008 server that claims to have completed successfully. However, when I open the package itself, it just hangs.
Do you have a converted one for SQL 2008 that works well?
thanks,
YOak
June 15, 2009 at 9:47 am
I was successful in using SQL Server 2008 "SQL Server Business Intelligence Development Studio" to open the SQL 2005 solution/package and migrating it to SQL Server 2008.
I got some warnings about error counts. Looking at the package after the migrating, I fixed some mapping issues. I ignored the truncation issues.
I ran this all on a local instance of SQL Server 2008 Standard edition SP1 10.0.2531.
David Bird
June 15, 2009 at 10:06 am
David when I open the package on a 2005 Dev server it runs but when I try to run it as a job I get the following errors.
Message
Executed as user: OTGDEV04\SYSTEM. ...on 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:44:20 PM Error: 2009-06-12 13:44:24.83 Code: 0xC0202009 Source: SQL_Overview_Package Connection manager "MultiServer" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'FNBN\OTGDEV04$'.". End Error Error: 2009-06-12 13:44:24.94 Code: 0xC00291EC Source: Execute XP_MSVER Execute SQL Task Description: Failed to acquire connection "MultiServer". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2009-06-12 13:44:40.69 Code: 0xC0202009 Source: SQL_Overview_Package Connection manager "MultiServer" Description: SSIS Error Code DTS_E_OLEDB... The package execution fa... The step failed.
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply