Wednesday, February 17, 2010

SQL SERVER 2005 Permissions to View Stored Procedures

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master
GO
REVOKE VIEW ANY DEFINITION TO User1

-- or

USE AdventureWorks
GO
REVOKE VIEW Definition TO User1


If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Thursday, February 11, 2010

Linked Server >>> Unable to begin a distributed transaction

Error Message:

OLE DB provider "SQLNCLI" for linked server "agosqltqtest01_link" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "test01_link" was unable to begin a distributed transaction.



Resolution: Change to No Authetication Required as follows
















Monday, January 4, 2010

Multiple SSIS SQL Server Instances


Multiple SSIS installs of SQL server on the same machine.

There is a configuration change that needs to be made to the SSIS file MsDtsSrvr.exe. You will need to add an entry for each of the installed instances on the server so that it can look at all of the msdb’s available. Once you have made the changes, you will need to stop and restart the ssis service.

Friday, December 25, 2009

SQL 2005 Blocking Issues

sp_who2 active
sp_blocked
dbcc inputbuffer(74)

Thursday, December 24, 2009

Coping SSIS Jobs to different Server

SELECT 'dtutil /COPY SQL;' + NAME + ' /SourceS servera /SQL ' + NAME + ' /DestS serverb
FROM msdb.dbo.sysdtspackages90

Disable and Enable Jobs

set nocount on
set quoted_identifier off
select "exec sp_update_job @job_name = '" + name + "', @enabled = 0 " + "
" + "" from sysjobs where enabled=1 order by 1

Tuesday, October 6, 2009

List of users in a Windows Global group


C:\>net group [GroupName] /domain

ex: NET GROUP DBAGroup /DOMAIN

SQL 2005 - Query to retrive SQL Agent Jobs scheduled time.

select j.name,sc.active_start_time,active_end_time,*
from sysjobs j
inner join dbo.sysjobschedules jsc
on j.job_id = jsc.job_id
inner join dbo.sysschedules sc
on jsc.schedule_id = sc.schedule_id
where j.name like '%ssis%'

Thursday, July 2, 2009

MDAC - SNAC components

MDAC is a two side component. i.e; on infrastructure (windows) side and driver (sqlserver) side. MDAC is replaced by SNAC. SNAC is on the "drivers" side.

In a nutshell, SQL Native Client is a stand alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic link library (DLL) while also providing new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC). SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

MDAC now ships as a component of the Windows operating system and as such there are a number of setup, redistribution, and deployment issues that have occurred as a result of this. you have developed an application based on the latest MDAC release only to discover that when you deploy it, the users in your organization do not have the latest MDAC release so your app won’t function correctly. By wrapping the OLE DB and ODBC technologies into a single library, we are able to avoid these issues by making a clean break from MDAC so that you can effectively deploy SQL Native Client as needed, without concern about if it will “play nicely” with other versions of MDAC.

Data Access Technology Road Map
http://msdn.microsoft.com/en-us/library/ms810810.aspx

Tuesday, June 30, 2009

SQL Server Consolidation

What is SQL Server Consolidation
Reducing the Number of Physical Sql Servers and also Sql Server Instances.

What are Business Drivers for Sql Server Consolidations.
1. Reduction of Total Cost of Ownership. (Maintaining Personal / Patch Management / Data centre Costs/ Energy Consumption (GreenIT)).
2. Best practices like database maintaince, back up,high availbility,DR strategies etc.

-----------------------------------------
Features of SQL SERVER 2005 for Consolidation

1. Large Scale Performance (Scale UP)
2. non-uniform memory access (NUMA) architecture
3. Work Load Governerance.
4. Multiple applications connecting through two different IP Address.

-----------------------------------------

Microsoft Consolidation Options

1. Hardware Partioning
High Throughput Applications
Complete Isolation
Reboot after resizing


2. Resource Management
Medium-High Throughput Applications
Manage Resource Usuage
Same Operating system level

3. Virtualization
Medium-Low throughput
Isolating legac applications
Complex management
Limed scale-up

Tuesday, June 16, 2009

Sunday, June 14, 2009

Data Quality Process

Profiling (Identifying data quality issues).
Generalized Cleansing (tests to meet business rules).
Parsing and standardization (restructing data into a common format).
Matching (finding unique identifiers and performint de-duplication).
Enrichment (phone and email validation)
Monitoring (checking conformance to data quality requirements).

----------------------------------------------------------------------------------------

The construction of data warehouses involves data cleaning, data integration, and data transformation.

OLAP operations such as roll-up, drill-down, slicing, and dicing.

A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process

-----------------------------------------------------------------------------------------

Thursday, March 12, 2009

SSRS 2008 Tablix -- Repeating Column Header for Tablix (Table)


Go to the Tablix advanced mode, select the static member from the Row Groups and set the properties as shown in the figure

Thursday, January 8, 2009

Relational VS MultiDimensional (OLAP) Databases

The key difference between OLAP dimensions and simple relational dimensions is the central role played by hierarchies in OLAP implementations.An OLAP dimension is strongly structured around its hierarchies, and the metadata of a cube definition includes the hierarchical levels.
This is one of the great strengths of an OLAP implementation.

OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.

The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.

Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server

Example explaining the advantage of Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.

Advantages are as follows
1. It provides an intuitive user interface for browsing data.
2. It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3. Parent-child dimension structures are easy and intuitive to implement.
3. It gives you server-defined rules for handling semiadditive and nonadditive measures.


The above explanation holds good for SSAS vs Sql Server Relational, Sql Server Relational vs Microstrategy,

Fact and Dimenisional Tables Relationships

Star schema, which maintains one-to-many relationships between dimensions and a fact table is widely accepted as the most viable data representation for dimensional analysis.

Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.

Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.

Two ad-hoc methods to resolve

1. Denormalizing the dimension tables.
2. Building the Bridge Table.

To find more information : go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling

Dimensional Modeling - Design Changes

The following changes are anticipated to the design after the data warehouse is up and running

1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.

2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.

3. Adding new, unanticipated dimensional attributes.

4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

--------------------------------------------------------------

Monday, November 24, 2008

OLAP Versus Data Mining

OLAP is a data summarization/aggregation tool that helps simplify data analysis, while data
mining allows the automated discovery of implicit patterns and interesting knowledge hidden in large amounts of data.
Key is OLAP

OLAP tools are targeted toward simplifying and supporting interactive data analysis,
whereas the goal of data mining tools is to automate as much of the process as possible,
while still allowing users to guide the process. In this sense, data mining goes one step beyond traditional on-line analytical processing.
Key is OLAP tools

OLAP functions are essentially for user-directed data summary and comparison
(by drilling, pivoting, slicing,dicing, and other operations). Data mining covers a much broader spectrum than simple OLAP operations because it performs not only data summary and comparison but also association, classification, prediction, clustering, time-series analysis, and other data analysis tasks.
Key is OLAP Operations

Data mining is not confined to the analysis of data stored in data warehouses. It may
analyze data existing at more detailed granularities than the summarized data provided
in a data warehouse. It may also analyze transactional, spatial, textual, and multimedia
data that are difficult to model with current multidimensional database technology. In
this context, data mining covers a broader spectrum than OLAP with respect to data
mining functionality and the complexity of the data handled.
Key is OLAP data analysis

Because data mining involves more automated and deeper analysis than OLAP,
data mining is expected to have broader applications. Data mining can help business
managers find and reach more suitable customers, as well as gain critical
business insights that may help drive market share and raise profits. In addition,
data mining can help managers understand customer group characteristics
and develop optimal pricing strategies accordingly, correct item bundling based
not on intuition but on actual item groups derived from customer purchase patterns,
reduce promotional spending, and at the same time increase the overall net
effectiveness of promotions.

Tuesday, November 11, 2008

Table Expressions

Four types of table expressions:
1. Derived tables
2. Common table expressions (CTEs),
3. Views
4. Inline table-valued functions (inline TVFs) also known as Parametrized views.

Beneifts.

Table expressions are virtual Tables.
The benefi ts of using table expressions uses a modular approach and not to performance.

Any Table Expression should meet the following three requirements.

1. Order is not guaranteed because the rows in a relational table are not ordered except with one restriction using Top Clause with Order By. Here the Order by is used for restricting the rows.
2. All columns must have names.
3. All column names must be unique.

To reuse the definitions , use views and inline TVFs.
Not to reuse the definitions, use dervied tables and CTEs.

APPLY operator

Multiple instances of the same CTE The APPLY operator operates on two input tables, the second of which may be a table expression; I’ll refer to them as the left and right tables. The right table is usually a derived table or an inline TVF.

If the right table expression returns an empty set, the CROSS APPLY operator does not returnthe corresponding left row. To return the empty set use the OUTER Apply.

--------------------------------------------------------------------------------

In its non-recursive form, a CTE is an alternative to derived tables, views, and inline user-defined functions (UDFs).

A derived table is a named table expression that exists for the duration of a query.
Views are also named table expressions that persist in the database until you explicitly drop them. Unlike with derived tables
1. a query can refer to a view name multiple times;
2. the view can't refer to variables.

Inline UDFs have the same characteristics as views except that they can refer to the function's input arguments.

CTEs have the best features of derived tables, views, and inline UDFs—they are named table expressions that exist only for the duration of the query, that an outer query can refer to multiple times, and that can refer to variables defined in the calling batch. . . .