Hi All,

For support people:

We can find the legacy DTS Packages logs and Status information using the below scriot

select

pklog.name as PackageName ,

stlog.stepname as StepName ,

stlog.stepexecutionid as StepExecutionID ,

stlog.starttime as StepStartTime ,

stlog.endtime as StepEndTime ,

stlog.errorcode as StepErrorCode ,

stlog.errordescription as StepErrorDescription

from

msdb.dbo.sysdtssteplog stlog

left join

msdb.dbo.sysdtspackagelog pklog

on stlog.lineagefull = pklog.lineagefull

where

stlog.errorcode <> 0

and stlog.starttime > DATEADD (day, -15, getdate())

order by

stlog.starttime

thanks for viewing

Regards,

Chaitanya

Visit site: https://mssqlbuzz.wordpress.com

Send an Email : mssqlbuzz

Below is the Procedure for checking the views that are defined for the table.

–Creating the procedure:

CREATE PROCEDURE VIEWS_IN_TABLE

@TABLENAME VARCHAR(200)

AS

SELECT VIEW_SCHEMA, VIEW_NAME FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

WHERE TABLE_NAME = @TABLENAME;

GO

–Testing the procedure

EXEC VIEWS_IN_TABLE

ADDRESS;

GO

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

After cube is deployed using SSAS, when we are trying to browse the cube, we are getting the below error

Temporary fix for this issue is change the Language option to English United States above the error

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

Today I wrote the script to find the primary key and foreign key for the table and Below is the script

select distinct sc.name+’.’+so.name as TableName,so2.name as ReferencedTable,so1.name ‘primary key Constraint name’,fc.CONSTRAINT_NAME as ‘Foreign Key constraint name’,col.COLUMN_NAME as ‘Primary Key Column’,

co2.COLUMN_NAME as ‘foreign key column’

from sys.objects so with (nolock)

inner join

sys.objects so1 (nolock)

inner join

sys.schemas sc(nolock)

on so1.schema_id=sc.schema_id

on so.[object_id]=so1.parent_object_id

and so.[type]=’u’

and so1.[type]=’PK’

inner join

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fc (nolock)

on fc.UNIQUE_CONSTRAINT_NAME=so1.name

inner join

sys.foreign_keys sfo (nolock)

on sfo.name=fc.CONSTRAINT_NAME

inner join

sys.objects so2(nolock)

on so2.object_id=sfo.parent_object_id

inner join

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col(nolock)

on

col.CONSTRAINT_NAME=fc.UNIQUE_CONSTRAINT_NAME

inner join

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE co2 (nolock)

on co2.CONSTRAINT_NAME=fc.CONSTRAINT_NAME

Result:

Please check and suggest the best way for doing this

Thanks for viewing.

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

We have the one procedure sp_MSforeachdb which is used to execute the script in all the databases at a time.

Below is the script for getting the strored Procedures defintion in all the databases.

EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; select @@SERVERNAME as servername,DB_NAME() as [Database],so.name,sc.text

from sysobjects (nolock) so inner join

syscomments(nolock) sc

on

sc.id=so.id

where so.type=”p”’

Result:

but the Procedure sp_MSforeachdb is un documented stored Procedure and there us no Gaurranty from Microsoft for future versions usage and this is Microsoft internal purpose only

if you want stored procedure definition from database, use the below script

use YouRdatabase

select @@SERVERNAME as servername,DB_NAME() as [Database],so.name,sc.text

from sysobjects (nolock) so inner join

syscomments(nolock) sc

on

sc.id=so.id

where so.type=’p’

Result:

Thanks for viewing and let us know if you have other options also .

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

Changing the database compatibility level

1) Using t-Sql

Syntax:

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

Arguments

database_name

Is the name of the database to be modified.

COMPATIBILITY_LEVEL {80 | 90 | 100 }

Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80. This applies to both system and user databases. Use ALTER DATABASE to change the compatibility level of the database. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

Using Compatibility Level for Backward Compatibility

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2008, convert the application to work properly. Then use ALTER DATABASE to change the compatibility level to 100. The new compatibility setting for a database takes effect when the database is next made current (whether as the default database on login or on being specified in a USE statement).

Best Practices

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, we recommend the following procedure to change the compatibility level of a database:

Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.

Change the compatibility level of the database.

Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

Ex:

2) Using SSMS to change the compatibility level of data base

Right Click on the database you want to change compatibility level

Select options and change the compatibility level and click on OK

We can see the compatibility level of data base using the SSMS

And using the t-sql

Thanks for viewing.

Reference :http://msdn.microsoft.com/en-us/library/bb510680.aspx

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

If you want to see the jobs status with out going to sql server agent, please run the below query

use msdb

go

select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate,

case h.run_status

when 0 then ‘Failed’

when 1 then ‘Successful’

when 3 then ‘Cancelled’

when 4 then ‘In Progress’

end as JobStatus

from sysJobHistory h, sysJobs j

where j.job_id = h.job_id and h.run_date =

(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)

order by 1

And if you want to see the dtspackages that are installed in your sql server, run the below query

select * from msdb..sysdtspackages

If you have other options, please post, I will post in the thread.

Thanks for viewing.

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

Below is the query used to find the mirroring status in sql server using t-sql

select * from sys.database_mirroring (nolock)dm

inner join sys.databases(nolock)db

on db.database_id=dm.database_id

and name=’AdventureWorks’

where adventure works is the name of database

Example:

Please check and let us know if you have any doubts.

Thanks for viewing.

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi Folks,

Today we will discuss about transactions in SSIS (sql server integration services)

Transactions in SSIS allows you to commit the group of data flow tasks (or) allows you to roll back the group of dataflow tasks as single logical unit of work

Transactions supported by every executable (i.e. package level, task level, container level)

For this 2 properties have to be set for executable

1. Transaction option

2. Isolation level

Transaction option is of 3 types

1. Not supported: executable will not support transactions

2. Supported: executable will join the transaction if transaction exists.(default)

3. Required: executable join the transaction if exists else it creates new transaction

Process:

If any tasks are executed, then tasks are executed under transaction if transaction option for executable is set to supported.

Suppose sequence container having 3 sql execute tasks and it is sequence container is having transaction option =required

Isolation level =serilizable(default).

Then it creates new transaction when executed the sequence container.

All the tasks in sequence container is having transaction option set to supported means these asks run under transaction created by sequence container.

If the tasks having transaction option set to Required, it will join the existing transaction , if no transaction is there, it will create new transaction and executes

Example:

Create 3 tables with names as

1.Customer

2.Email

3.Vehicle

Syntaxes:

create table customer(custno int,custname varchar(10))

create table email(custno int,emailid varchar(20))

create table vehicle(custno int,vin char(5) not null,model char(3))

Where VIN-Vehicle identification number

Open Business intelligence development studio and click on file->new->project->integration services project

Place sequence container and drag 3 execute sql tasks like below

Double click on first execute sql task and set the properties

In general page, connection type =OLEDB

Connection: select new connection, a window occurs, specify the data source information give server name and database information and test the connection

Click OK and we got below screen

General page,

Sql source type=Direct input

Sql statement=insert customer values(10,’chaitu’)

This is looks like below.

Same can be done for other execute sql tasks with same connection but different sql statement

Set Execute sql task 1 Properties

Set Execute SQL task 2 properties

insert vehiclecustno,model) values(10,’abc’)

We have passed values to the insert statement for 3rd task but the table is having not null column. We are not passing information to that column. So this task must fail.

Transaction in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running in services. MSc. MSDTC also allows you to perform distributed transactions e.g. updating a any RDBMS database. If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message like the following:

Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Execute the package by pressing f5

We got below error like this

First two execute sql tasks are success. 3rd task is failed. So transaction is roll backed. Because these 3 tasks are running under single transaction

To implement package scope transactions, place another sequence container move execute sql task 2 to that sequence container. Go to properties window of package by clicking f4

Transaction property: required

Isolation: serilizable(default)

Sequence containers properties are:

Transaction property: Supportes

Isolation: serilizable(default)

It will be looks like below

Execute the package by clicking on F5

It will be failed as 3rd task is failed.

So first and second task inserts data, that is roll backed.

This is all about transactions in SSIS.

Thanks for viewing.

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Hi All,

Microsoft introduced 2 DMV’s(Dynamic management view) for viewing sql services information and system registery information in sql server denali

SELECT *
FROM sys.dm_server_services

And for the registery information , we have another DMV

SELECT *
FROM sys.dm_server_registry

Reference : Pinal Dave(http://blog.sqlauthority.com)

Please check and let us know if you have any concerns on this.

Thanks for viewing.

Regards,

Chaitanya

Visit site: https://mssqlbuzz.wordpress.com

Send an Email : mssqlbuzz