Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Overview Part 1


SQL Overview Part 1

Author
Message
David Bird
David Bird
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 1186
Comments posted to this topic are about the item SQL Overview Part 1

David Bird

My PC Quick Reference Guide
rlondon
rlondon
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1160
Does this only report on other SQL Server 2005 databases, or can you use it against prior versions as well?
David Bird
David Bird
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 1186
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
rsconnolly
rsconnolly
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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?
David Bird
David Bird
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 1186
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
rsconnolly
rsconnolly
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
David Bird
David Bird
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 1186
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
rsconnolly
rsconnolly
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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?
Cat-253986
Cat-253986
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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.
David Bird
David Bird
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 1186
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search