<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by David Bird
»
SQL Overview Part 1
56 posts, Page 1 of 6
1
2
3
4
5
»
»»
SQL Overview Part 1
Rate Topic
Display Mode
Topic Options
Author
Message
David Bird
David Bird
Posted Saturday, January 05, 2008 11:26 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182,
Visits: 952
Comments posted to this topic are about the item
SQL Overview Part 1
David Bird
My PC Quick Reference Guide
Post #439317
rlondon
rlondon
Posted Monday, January 07, 2008 8:00 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, May 03, 2013 12:41 PM
Points: 417,
Visits: 1,122
Does this only report on other SQL Server 2005 databases, or can you use it against prior versions as well?
Post #439591
David Bird
David Bird
Posted Monday, January 07, 2008 8:17 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182,
Visits: 952
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
rsconnolly
rsconnolly
Posted Monday, January 07, 2008 9:30 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:07 PM
Points: 4,
Visits: 73
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
David Bird
David Bird
Posted Monday, January 07, 2008 9:57 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182,
Visits: 952
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
rsconnolly
rsconnolly
Posted Monday, January 07, 2008 10:59 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:07 PM
Points: 4,
Visits: 73
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
David Bird
David Bird
Posted Monday, January 07, 2008 11:26 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182,
Visits: 952
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
rsconnolly
rsconnolly
Posted Monday, January 07, 2008 12:21 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:07 PM
Points: 4,
Visits: 73
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
Cat-253986
Cat-253986
Posted Monday, January 07, 2008 12:34 PM
SSC 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
David Bird
David Bird
Posted Monday, January 07, 2008 12:48 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182,
Visits: 952
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 »
56 posts, Page 1 of 6
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
