Subscribe to this blog
SQLStudies
Archives: October 2012
Looping through multiple servers in SSIS
This is just a quickie post to answer a question on how to loop through multiple servers in SSIS. There are a lot of options I’m not going to go into here due to lack of time. If someone expresses interest I’ll try to do a more detailed post later. Read more
9 comments, 985 reads
Posted in SQLStudies on 31 October 2012
How do I find all stored procedures or views that use a specific table?
Many years ago when I was still working on SQL 2000 I occasionally needed to find all stored procedures and views that referenced a certain table. After some research I found the system table syscomments. This table has a column “text” that contains the “Actual text of the SQL definition… Read more
0 comments, 550 reads
Posted in SQLStudies on 26 October 2012
I can connect to my instance locally but not remotely
Every now and again (today for example) I’ll get a call that one of our SQL Server instances is down. I’ll try to connect from my machine with no success. Then I’ll remote to the server itself and first check if the server is up, then if it is, try… Read more
0 comments, 386 reads
Posted in SQLStudies on 23 October 2012
fn_OBJECT_TYPE
I recently started using OBJECT_NAME, OBJECT_SCHEMA_NAME and OBJECT_ID functions; unfortunately I really could have used OBJECT_TYPE but it doesn’t exist (yet?). So for now I wrote one of my own.
FYI I originally wrote it as sp_ and created it in the master db thinking that this would cause it… Read more
2 comments, 423 reads
Posted in SQLStudies on 19 October 2012
A review of SQL Interoperability / Joes 2 Pros Volume 5 by Rick A Morelan
I recently passed my MCTS 2008 Development (70-433). Yay me! That made my 3rd 2008 certification. This one was probably the easiest so far since I’ve been a DBA Developer (as opposed to Administrator) for the vast majority of my career (16 of the last 22 years). After reviewing… Read more
1 comments, 542 reads
Posted in SQLStudies on 15 October 2012
Multiple CTEs
Multiple CTEs
It’s somewhat obscure in BOL and I wasn’t able to find any examples but it turns out you can use multiple CTEs in a single query.
I couldn’t come up with a good example on my own, but while researching a memtoleave problem I found a good candidate.… Read more
8 comments, 2,468 reads
Posted in SQLStudies on 9 October 2012
Active flag on the TCP/IP Properties page of SQL Server Configuration Manager
I’ve been unable to connect to a server via one of its aliases for about a week. When I looked at the TCP/IP Properties for the instance I saw the following:
Note that the Active flag for IP1 is “No”. BOL defines the Active flag as “Indicates that the IP… Read more
1 comments, 509 reads
Posted in SQLStudies on 5 October 2012
When I get stumped I ….
I work with a reasonably large team of DBAs. There are 9 of us and we support not only 70+ SQL Server instances but DB2, IMS and Sybase. We have SMEs for each DBMS and we cross train as much as possible, but even so there are some large gaps.… Read more
2 comments, 420 reads
Posted in SQLStudies on 2 October 2012