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 «««23456»»»

SQL Overview Part 1 Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 9:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
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
Post #442530
Posted Monday, January 14, 2008 10:15 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 10:31 AM
Points: 185, Visits: 1,035
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

My PC Quick Reference Guide
Post #442541
Posted Monday, January 14, 2008 3:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:40 AM
Points: 332, Visits: 302
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!



Post #442729
Posted Monday, January 14, 2008 4:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:45 AM
Points: 329, Visits: 334
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
Post #442753
Posted Tuesday, January 15, 2008 7:37 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 10:31 AM
Points: 185, Visits: 1,035
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

My PC Quick Reference Guide
Post #442997
Posted Tuesday, January 15, 2008 1:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:45 AM
Points: 329, Visits: 334
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
Post #443237
Posted Monday, June 9, 2008 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:42 AM
Points: 3, Visits: 93
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?
Post #513854
Posted Wednesday, November 26, 2008 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 7:49 AM
Points: 2, Visits: 34
Any thoughts on changes that will allow this to now include SQL 2008?


Post #609170
Posted Monday, December 1, 2008 1:18 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 10:31 AM
Points: 185, Visits: 1,035
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

My PC Quick Reference Guide
Post #611639
Posted Saturday, March 21, 2009 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 18, 2010 11:20 AM
Points: 10, Visits: 30
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.
Post #680973
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse