How to browse the database objects through Linked Servers
Without actually connecting to another instance of SQL server, is it possible to browse the database objects like tables & views? The answer is YES. How do I do that? Well, for a working scenario we need at least 2 SQL instances either on same server or on a different servers. If you have already not created the Linked server, click here to see this article and then do the below steps.
1. Connect to the 1st instance of SQL Server in Management Studio.
2. Browse through the folders to see “Server Objects” and look for “Linked Servers”. Click the plus sign on it to expand.
3. Now click on the plus (+) sign that is already configured and choose the name of the linked server and then expand Catalogs.
4. Here you can see the number of databases and expand it.
5. You will only see Tables & Views and not the stored procedures or any other objects.
6. You can right click on any table and select “Script Table as” and click on “Select to” => New Query window.
7. You can see a query automatically created pointing to the table in a 4 point convention (…).
Note: Writing any linked server queries for production usage will obviously have performance problems. If the SLA is agreeable then you can proceed or you need to take extra measure. If you have any issue in tackling these type of issues, do get in touch with info@yoursqlman.com for further information.
Group Container in SSIS
A group container helps to have more than 2 data flows to be grouped in one container but does not have any properties for itself. Also there is no precedence constraint in or out to this group container, instead it needs to be used with the tasks itself.
To have this group, select more than 2 tasks and right click and select the group option. This also helps in hiding and showing the tasks within the group.
If you have any questions drop me an email on mani@yoursqlman.com
Data Warehousing – Definition Plus
The data warehousing market consists of tools, technologies, and methodologies that allow for the construction, usage, management, and maintenance of the hardware and software used for a data warehouse, as well as the actual data itself.
In order to clear up some of the confusion that is rampant in the market, definitionplus provides you with fact:
Data Warehouse:
The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: “A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows:
Subject Oriented:
Data that gives information about a particular subject instead of about a company’s ongoing operations.
Integrated:
Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
Time-variant:
All data in the data warehouse is identified with a particular time period.
Non-volatile:
Data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.
(Source: “What is a Data Warehouse?” W.H. Inmon, Prism, Volume 1, Number 1, 1995).
This definition remains reasonably accurate almost ten years later. However, a single-subject data warehouse is typically referred to as a data mart, while data warehouses are generally enterprise in scope. Also, data warehouses can be volatile. Due to the large amount of storage required for a data warehouse, (multi-terabyte data warehouses are not uncommon), only a certain number of periods of history are kept in the warehouse.
Ralph Kimball provided a much simpler definition of a data warehouse. As stated in his book, “The Data Warehouse Toolkit”, on page 310, a data warehouse is “a copy of transaction data specifically structured for query and analysis”. This definition provides less insight and depth than Mr. Inmon’s, but is no less accurate.
List of Business Intelligence (BI) tools
Business Intelligence (BI) tools are widely used for reporting, dashboarding and analysis. The following BI tools were thoroughly examined on 70 criteria considered important for high productivity and Business Intelligence systems that actually add value to your organization. In random order.
| No. | Tool | Version | Vendor |
| 1. | Oracle Enterprise BI Server | 7.8 | Oracle |
| 2. | Business Objects Enterprise | XI r2 | Business Objects (now SAP) |
| 3. | SAP NetWeaver BI | 7.0 | SAP |
| 4. | SAS Enterprise BI Server | 9.1.3 | SAS Institute |
| 5. | IBM Cognos TM/1 & Executive Viewer | 9.1 | Applix (now IBM) |
| 6. | BizzScore Suite | 7.2 | EFM Software |
| 7. | WebFocus | 7 | Information Builders |
| 8. | Excel, Performance Point, Analysis Server | 2007/2005 | Microsoft |
| 9. | QlikView | 8.5 | QlikTech |
| 10. | Microstrategy | 8 | Microstrategy |
| 11. | Hyperion System | 9 | Hyperion (now Oracle) |
| 12. | Actuate | 9.1 | Actuate |
| 13. | IBM Cognos Series 8 | 8.3 | Cognos (now IBM) |
Instances in SQL Server
The port used by default instance is always 1433 and named instances use dynamic allocation.
The total number of instances including the default instance of SQL Server supported by all editions differ. The following gives a clear idea of the supported instances for each editions.
Enterprise – 50
Developer – 50
Standard – 50
Workgroup – 16
Express – 16
Note: Windows Failover Cluster supports only 25 instances.
Add a new column to every table in a database
This seems quite a painful job when so many start opening a table and add the new column name, datatype and save. But what about you have to do this for 200 tables in a database and say you have 10 databases. Hmmm…. Such a crap work doing manually but SQL Server infamiliar stored procedure comes to rescue at this point.
The below query can be used to add a new column to every single table in a database.
Use [db]
go
exec sp_MsForEachTable ‘alter table ? add Load_date datetime null’
Thats it. You are done. Change the database name for the other databases.
So it is not just adding a column to all table, but modifying anything similar for each table in a database. You can change the SQL statement within the single quotes.
Best of luck
Become a Sponsor
This Data Awareness Programme, brings the enlightenment to every individuals in
identifying the data and analyse them. Click here to become a sponsor.
SQL Server Advanced Options
Part -II – Configurations – Management Studio and sp_configure
In my previous article you should have been familiar with the query sp_configure to see the default values of all the list
of 16 options. Let us see the Advanced options here.
By default the show advanced options will have 0 in its value. To see and set the advanced options this has to be changed
to 1 and it can be changed back to 0 once done. The query for changing the option is
sp_configure ‘show advanced options’, 1
go
reconfigure
This query will change the config_value option to 1 and reconfigure will send the command to reload all of the values. Once
the value is changed to 1 and by running sp_configure again will display an extended list of 68 in SQL Server 2008 and 70
in SQL Server 2008 R2.
I am going to take you through some of the most commonly used options.
AWE