January 14, 2008 at 9:51 am
David,
I have some case-sensitive SQL Server 2000 databases, running Lawson 9. A few adjustments are needed in some of the script syntax, if anyone else is running a case-sensitive database.
(Need to change 'Name' to 'name' for all occurances in the query below.)
Collect Database Status Container
Step 7
Sub-Step 1
Sub-Sub-Step 3
SQL Command Text:
SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY('Servername')) AS [Server],
ISNULL(RTRIM(CONVERT(NVARCHAR(128),
SERVERPROPERTY('InstanceName'))), 'Default') AS InstanceName,
master..sysdatabases.name AS DatabaseName ,
CONVERT(sysname,DATABASEPROPERTYEX(name,'Status')) AS DatabaseStatus,
CONVERT(sysname,DATABASEPROPERTYEX(name,'Recovery')) AS [Recovery],
CONVERT(sysname,DATABASEPROPERTYEX(name,'UserAccess')) AS User_Access,
CONVERT(sysname,DATABASEPROPERTYEX(name,'Updatability')) AS Updatability
FROM master..sysdatabases
Hopefully this will help others in a similar situation.
Happy T-SQLing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 14, 2008 at 10:15 am
Justin,
"OLE DB Source" can only connect to a one server at a time.
The Foreach Loop container is used to retrive the server values from the variable SQL_RS.
The server name is passed one at a time to the "OLE DB Source" task in the variable SQL_Conn.
Once the Data Flow Task "Load Database Status" finishes it's two tasks, the For Each Loop passes the next server value from SQL_RS and to "OLE DB Source".
David Bird
January 14, 2008 at 3:27 pm
David,
Highly excellent work! Thank you for doing this! I have two questions for you:
1. the "sqlcmd -Lc" gets a list of over 100 "sql servers" in my domain. Many of the instances are of the type 'servername\express'. These are not recorded into the sql table... have you run against this and considered any workaround? ( I would like to keep track of how many sql\expresses are around my domain.)
2. When the servername is passed to "collect database Status" module, there are numerous timeouts as many of the 'sql instances' don't respond. This makes the package run for quite a while (~40 non-answers * ~30 sec per...means over 20 minutes). Permission to access servers may be some of the answer...but i wonder if the "sqlcmd -Lc" isn't giving some false positive responses?
Again, great work!
January 14, 2008 at 4:11 pm
Hi Steve
I changed the table and package to contain and use connections strings, since all my servers are in different domains, so integrated security is not use full for all. And I have a unique login account on all boxes anyway, so I just use this. In the package I of cause set the connection string variable instead of the server one.
If I want the package to gather server specific data like @@Version, I never found a nice way to avoid getting it to return a result for all db's on a server. The crappy solution I finally came up with, was to change the select in the get data OLEDB source to "select distinct.....". Any ideas how to change this behavior?
//SUN
January 15, 2008 at 7:37 am
Steve,
I get similar results using the SQLCMD. Most of those SQL Express instances are from Visual Studio installations on desktops. I just delete them from the table because I don’t have access. Sybase SQL Server installations will also be listed using the SQLCMD. I delete those as well.
Soren,
I use EXEC( 'master.dbo.xp_msver') to get server specific information. In Part III, I use it retrieve information from the remote instances. It only works for SQL Server 2000 and 2005. This command returns a row for each value. For example, ProductVersion is on row and PhysicalMemory is on another row. In the package, I combine these values into a single row for each server.
David Bird
January 15, 2008 at 1:54 pm
Hi David
That can be used as well, I wanted to use @@version, since it worked on earlier versions as well, I still have a few 7.0 installs running. But then I ran into problems with my OLEDB source. For some reason this won't connect to the 7.0 instances from within the SSIS package.
Creating an empty SSIS project with a similar OLEDB source, that uses exactly the same connection string works and connects in GUI mode. I never managed to find the reason for this, status on two 7.0 installs was not that important anyway.
//SUN
June 9, 2008 at 10:52 am
I ran into that same case-sensitivity issue too. It didn't occur to me what was going on at first but the lightbulb came on as soon as I saw your post. Thanks!
Have you ever imagined a world without hypothetical situations?
November 26, 2008 at 8:41 am
Any thoughts on changes that will allow this to now include SQL 2008?
December 1, 2008 at 1:18 pm
I have yet to install SQL Server 2008, because I have been waiting for the official DVD's to be received by my IT department. I see no reason why this package cannot connect to a SQL 2008 instance.
David Bird
March 21, 2009 at 3:09 pm
I am setting stuck on step 6 and 7. Can someone help me. Basically, I did what it said, I double clicked on the data flow tab for data flow task, but I am stuck when it says "OLE DB Source" from toolbox. Does that mean that in teh toolbox on the right hand side there is suppose to be an ole db source? I went to teh MultiServer connection that was set earlier, but I don't see where to add the sql command text. I guess I am just lost.
March 21, 2009 at 8:56 pm
I am getting the following error message. I just implemented the code that was posted exactly as it was posted. It was ran locally. I should be an administrator on all boxes so I don't understand this:
Error at SQl_Overview_Package [Connection Manager "multiserver]: SSIS error code DTS_E_OLedberrOR. An error has occurred Error code 0x80004005 Description: "Login failed for user 'a662783'..
An OLE DB record is available . Sourdce. "Microsoft SQl Native client" Hresult 0x80004005 DEscription: Cannont open database Master" requeste dby teh login. The logfin failed.
It doesn't give me much to go off of so I am curious as to why it is failing. Please help.
April 26, 2009 at 2:42 am
Hello,
I just want to say that there is a small error in the Database Status script.
It reads:
CONVERT(sysname,DATABASEPROPERTYEX(Name,'Updatability')) AS Updatability
and it should read:
CONVERT(sysname,DATABASEPROPERTYEX(Name,'Updateability')) AS Updateability
The problem is, that 'Updateability' is misspelled, and the script does not return any data.
Otherwise, I must say that you have done a great job!
Make everything as simple as possible, but not simpler.
Albert Einstein
August 10, 2009 at 11:15 am
cy.harrild (1/7/2008)
This is a great tool but i'm having a problem. It all works fine except that all the databases that the package finds throughout the network are listed against one server. Is this caused by the fact i'm building this on my workstation and not on the server?
I'm having the same issue as cy.harrild. My dbo.database_status table only has info for the server I'm running the package from, instead of info for all servers on this domain. Looks like the loop is working because I'm getting the same 12 lines repeating for each server. Just seems that the loop is not grabbing the next server name into the variable SRV_Conn each time. ANy thoughts?
Dave Coats
August 10, 2009 at 11:31 am
Dave Coats (8/10/2009)
cy.harrild (1/7/2008)
This is a great tool but i'm having a problem. It all works fine except that all the databases that the package finds throughout the network are listed against one server. Is this caused by the fact i'm building this on my workstation and not on the server?I'm having the same issue as cy.harrild. My dbo.database_status table only has info for the server I'm running the package from, instead of info for all servers on this domain. Looks like the loop is working because I'm getting the same 12 lines repeating for each server. Just seems that the loop is not grabbing the next server name into the variable SRV_Conn each time. ANy thoughts?
I figured out what my problem was. I had left out the step about configuring the Multiserver data connection. These are the steps I left out the first time:
Now we need to customize this connection
Right Click on MultiServer
Select Properties
Change the Following Properties
Expressions click ... box
Click Property
Click Drop Down Arrow
Select ServerName
In the expression box type @[User::SRV_Conn]
Click OK
Initial Catalog change to Master
Dave Coats
September 1, 2009 at 7:41 am
Hi. I have had the package working great for sometime now. I have a group of servers which do not live on our domain. Is there a way to get the connection manager to see those servers in addition to the ones I have currently working or is it better to create a seperate database and instance?
Thanks.
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply