SQL Overview Part 1

  • Comments posted to this topic are about the item SQL Overview Part 1

    David Bird

  • Does this only report on other SQL Server 2005 databases, or can you use it against prior versions as well?

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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?

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • David, Thanks for the cool tool. I look forward to parts II and III.

  • 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