SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Overview Part 1


SQL Overview Part 1

Author
Message
Key DBA
Key DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 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
David Bird
David Bird
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1233
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
steve Schneider
steve Schneider
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 307
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!



Soren Nielsen
Soren Nielsen
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 352
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
David Bird
David Bird
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1233
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
Soren Nielsen
Soren Nielsen
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 352
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
Chris Clements
Chris Clements
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 105
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?
lucast
lucast
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 34
Any thoughts on changes that will allow this to now include SQL 2008?



David Bird
David Bird
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1233
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
AshaRRichardson2nd
AshaRRichardson2nd
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 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.
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