Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL Overview Part 1 Expand / Collapse
Author
Message
Posted Saturday, January 5, 2008 11:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:17 PM
Points: 184, Visits: 1,012
Comments posted to this topic are about the item SQL Overview Part 1

David Bird

My PC Quick Reference Guide
Post #439317
Posted Monday, January 7, 2008 8:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 12:38 PM
Points: 417, Visits: 1,147
Does this only report on other SQL Server 2005 databases, or can you use it against prior versions as well?
Post #439591
Posted Monday, January 7, 2008 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:17 PM
Points: 184, Visits: 1,012
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

My PC Quick Reference Guide
Post #439602
Posted Monday, January 7, 2008 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:33 PM
Points: 4, Visits: 81
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?
Post #439669
Posted Monday, January 7, 2008 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:17 PM
Points: 184, Visits: 1,012
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

My PC Quick Reference Guide
Post #439688
Posted Monday, January 7, 2008 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:33 PM
Points: 4, Visits: 81
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
Post #439711
Posted Monday, January 7, 2008 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:17 PM
Points: 184, Visits: 1,012
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

My PC Quick Reference Guide
Post #439719
Posted Monday, January 7, 2008 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:33 PM
Points: 4, Visits: 81
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?
Post #439748
Posted Monday, January 7, 2008 12:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 27, 2009 6:05 AM
Points: 98, Visits: 215
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.
Post #439755
Posted Monday, January 7, 2008 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:17 PM
Points: 184, Visits: 1,012
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

My PC Quick Reference Guide
Post #439762
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse