January 5, 2008 at 11:26 pm
Comments posted to this topic are about the item SQL Overview Part 1
David Bird
January 7, 2008 at 8:00 am
Does this only report on other SQL Server 2005 databases, or can you use it against prior versions as well?
January 7, 2008 at 8:17 am
It can be used for both SQL Server 2000 and 2005 databases.
Most of the columns don't exist in SQL Server 7, so it would need to be customized for those instances. For the few SQL Server 7 instances I support, I created custom version of this package.
David Bird
January 7, 2008 at 9:30 am
I tried to follow this example, when I tried to "Click Preview to verify the SQL" on step 7.1.3 in the section on "Collect Database Status Container" I got an OLE DB error. It says "An OLE DB error has occurred. Error code: 0x80004005. Any ideas?
January 7, 2008 at 9:57 am
Try running the SQL through SQL management studio for the same instance QASRV.SQL_Overview is setup. This will confirm the syntax and verify that you have access.
The OLE DB Source should reference MultiServer
The instructions should be revised to add a step
7.1.1.a OLE DB connection manager: MultiServer
Sorry for any problems.
David Bird
January 7, 2008 at 10:59 am
Okay I got past the error I was getting (thanks for responding). The OLE DB Connection Manager had apparently defaulted to MultiServer. Based on your response I assume it should have been set for QASRV.SQL_Overview? I went ahead and changed it and I am no longer getting the error, but nowI have another problem: I got to the section where it was "Ready to be tested" and when I ran it, it just reports all the databases in the QASRV.SQL_Overview server multiple times (one time for each server in the SSIS_ServerList table) instead of reporting on the databases in each of the servers that I have in the SSIS_ServerList table. I know I must have typed something wrong. Sorry - I am new to SSIS and was hoping that going through this exercise would help me learn it! Can you give any further suggestions?
Thanks
January 7, 2008 at 11:26 am
Sounds like your connecting to the same server.
1) Check the MultiServer Connect and verify its propery setting expresssions is ServerName @[User::SRV_Conn]
2) Now check Populate ADO Variable properties.
The Result Set should be
Result Name 0
Variable Name User::SQL_RS
This variable was my default value. I should of documented that it should be selected.
3) Run this query to confirm the server names in the table
SELECT RTRIM(Server) AS servername
FROM SSIS_ServerList
WHERE (Skip_SQL_Overview is null or Skip_SQL_Overview = 'FALSE')
ORDER BY 1
Let me know how it goes
David Bird
January 7, 2008 at 12:21 pm
Yes all that checks out: I rightclick the MultiServer Connect, click on expressions, click on the "..." button and see that the Property is "ServerName" and the Expression is "@[User::SRV_Conn]". I doubleclick the "Populate ADO Variable" Execute SQL Task box, I click on the Result Set Property and see that the "Result Name" is 0 and "Variable Name" is "User::SQL_RS". I paste the following query into a query window on the SQL Server Management Studio:
SELECT RTRIM(Server) AS servername
FROM SSIS_ServerList
WHERE (Skip_SQL_Overview is null or Skip_SQL_Overview = 'FALSE')
ORDER BY 1
When I run this query it returns a list of our 8 servers. But like you say it sounds like I am connecting to the same server. Is there anything else you can think of that I need to check?
January 7, 2008 at 12:34 pm
I am getting a similar error as the previous poster. It "Previews" under the QASRV.SQL_Overview connection manager, but not under MultiServer. When I look back at the MultiServer Connection Manager, no server name is specified (and I specified the one where SQL_Overview exists). I will try to figure it out, but just thought I would let you all know I was experiencing a similar issue. Thanks.
January 7, 2008 at 12:48 pm
MultiServer gets its server name from the expression ServerName @[User::SRV_Conn]
This variable is empty.
When I use the preview it connects to my local default instance.
Because it is empty it may behave differently on your PC/Server.
David Bird
January 7, 2008 at 1:28 pm
If you have not, try setting the default value for the SVR_Conn variable to (local) or to your server name \ instance name . That worked for me.
I also set the default server name to use the instance name where I created the SQL_Overview database. I did this for all of the connections. My default instance is not running so the connections fail. However, since the MultiServer connection has an expression, that required setting the variable to a valid server.
January 7, 2008 at 1:42 pm
BTW, if you can not see the variables in the list, right click on the designer surface and click variables. I downloaded the zip and SVR_Conn did not show up by default on my system.
January 7, 2008 at 1:56 pm
Tony Mungor (1/7/2008)
If you have not, try setting the default value for the SVR_Conn variable to (local) or to your server name \ instance name . That worked for me.I also set the default server name to use the instance name where I created the SQL_Overview database. I did this for all of the connections. My default instance is not running so the connections fail. However, since the MultiServer connection has an expression, that required setting the variable to a valid server.
Much better - you solved my issue! Thanks so much for everyone's help!
I may have been entering an invalid server name for the MultiServer instead of a valid one, therefore the servername was blank.
January 7, 2008 at 2:33 pm
David, Thanks for the cool tool. I look forward to parts II and III.
January 7, 2008 at 2:36 pm
BTW, the only other thing I changed in the dowloaded package was setting the MaximumErrorCount to 99. I ended up adding a bunch of servers and instances that where not running so I needed to handle the exceptions and move on.
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply