Archive for February, 2013

Enable Transactions From Remote Locations

February 27, 2013

Open up component services on your server
expand component Services > My Computer > Distributed Transaction Coordinator > Local DTC
Right Click Local DTC
Check: Network DTC Access, Allow Remote Clients, Allow Inbound, Allow Outbound

In your TSQL transaction on your remote machine you need to set XACT_ABORT ON

View SSRS reports in Chrome

February 26, 2013

1. Run a report in chrome, the web page returned will be blank

2. Search for the following string:   style=”height:100%;width:100%;overflow:auto;position:relative;”

3. make a note of the div name which contains the string in this example the name is:  ctl31_ctl09

4. Find the file ReportingServices.js, in this example the location is:

“C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js”

5. Append the following to the bottom of the film:

function pageLoad() {    
var element = document.getElementById("ctl31_ctl09");
if (element) 
{
    element.style.overflow = "visible"; 
} }

call bcp using tsql

February 26, 2013

exec master..xp_cmdshell ‘bcp “SELECT * FROM OPENQUERY(SERVER,”select * from DBASE.SCHEMA.TABLE’) ” queryout c:\test.txt -U LOGIN -P PASSOWRD -c -t “|*|”‘ –,NO_OUTPUT

This works with a sql login, had login issues using windows authentication, i use |*| as a column delimiter in this example

Import it using

BULK INSERT destination_table FROM ‘c:\test.txt’ WITH(FIELDTERMINATOR=’|*|’,ROWTERMINATOR=’0x0a’,FIRSTROW=1,TABLOCK,batchsize=50000,MAXERRORS=0)

lead function 2012 vs 2008R2 tsql

February 23, 2013

This is quick example of how we can use the lead function to produce simpler and more efficient code than we were able prior to SQL 2012.

I’ve created a dumbed down version of the package change report, Ive also provided code samples to produce identical datasets using both SQL 2008 and the improved 2012 TSQL code.

In this example what we are going to do is show every tariff that a customer has had together with start and end dates and new tariff name if the customer has changed. This is dummy data using customer 1 and 2 and tariffs A,B,C and D.

The old method involved creating a row number against every row in the dataset in a temporary table and then performing a self join on this table against rownumber = rownumber + 1, the new method is simpler to read and much,much more efficient. It uses the lead function which allows us to refer to the next row in the current partition. The old method performs 6 table scans (1+1 scan per row) whilst the new method produces only 1 scan. (Use set statistics io on to see this)

Here is the code

/*set up some sample data*/

declare @t TABLE (cust int, pack VARCHAR(1), tran_date date)

insert into @t VALUES

(1,’A’,’1-Jan-2013′),
(1,’B’,’1-Apr-2013′),
(1,’C’,’2-May-2013′),
(2,’A’,’6-Feb-2013′),
(2,’C’,’2-May-2013′)

/*old method utilizing a cte and self join*/

;with a as(
Select *,row_number() over (partition by cust order by tran_date ) row  from @t)

select
a.cust ‘customer’
,a.pack as ‘package’
,a.tran_date as ‘start date’
,b.tran_date ‘end date’
,b.pack as ‘new package’

from a
left join a b on a.cust = b.cust and a.row +1 = b.row

/*new method using lead function*/

select
a.cust ‘customer’
,a.pack ‘package’
,a.tran_date ‘start date’
,lead(tran_date) OVER (PARTITION BY cust ORDER BY tran_date) ‘end date’
,lead(pack) over (partition by cust order by tran_date) ‘new package’

from @t a

After Installing SQL 2012 Management Studio F7 no longer opens Object Explorer Details Pane

February 12, 2013

Problem: After Installing SQL 2012 Management Studio F7 no longer opens Object Explorer Details Pane

Solution: Tools -> Options -> Environment -> Keyboard -> Keyboard

Apply the following additional keyboard mapping scheme: Visual Studio 2010 Compatible

Force Deadlock

February 11, 2013

taken from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49692

 

/*
--First run this code to create the tables
create table ##temp1 (col1 int)
create table ##temp2 (col1 int)

insert ##temp1 
Select 1 union select 2 union select 3

insert ##temp2
Select 1 union select 2 union select 3
*/

/*
--Paste this code in an other QA window
--QA window #2
begin tran
	update ##temp2 set col1 = 4 where col1 = 3

	--delay long enough to lock ##temp2 in this process 
	--and allow ##temp1 to be locked in other process
	waitfor delay '0:0:05'

	--this proc is holding lock on ##temp2 waiting for ##temp1 to be released
	update ##temp1 set col1 = 4 where col1 = 3
commit tran
*/

--QA window #1
begin tran
	update ##temp1 set col1 = 4 where col1 = 3

	--delay long enough to lock ##temp1 in this process 
	--and allow ##temp2 to be locked in other process
	waitfor delay '0:0:05'

	--this proc is holding lock on ##temp1 waiting for ##temp2 to be released
	update ##temp2 set col1 = 4 where col1 = 3
commit tran

/*
sElect @@trancount
drop table ##temp1
drop table ##temp2
*/

SSAS cube processing time out

February 4, 2013

Problem: SSAS cube processing time out

Solution: Extend the time out period.

In Management Studio, right click the server and select properties. Click Show Advanced (All) properties.

Locate the ExternalCommandTimeout property, by default it is set to 3600 seconds.