sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

To check and make sure that CLR is enabled, simply run this select statement:

select  *
from    sys.configurations
where   name = 'clr enabled'

Source: MSDN – Enabling CLR Integration

Could this help?

I thought that the On Screen Keyboard would work, but I heard that the break key on the On Screen Keyboard didn’t work. I didn’t have time to research, so I left the no break key issue to those that actually didn’t have a break key.
Fast forward a few weeks, and I myself got a Dell XPS 15 which, as luck would have it, does not have a break key. So, here I am again, tackling the same issue. This time, the solution takes a bit of software.
At work, I use a program called AutohotKey to script things out, like my signature. So, RR expands to
Thanks,
Rudy Rodarte

AutoHotKey can also remap keys send commands. Here are a few links showing the different things AutoHotKey can do:
Hot Keys
Remaping Keys
HotStrings (auto expanding text, like my signature)
Now, knowing all this, we can use AutoHotKey to map Control-Alt-Break to some other key. I use Control-Alt-C as my Control-Alt-Break replacement. Here is the code:

^!c::send ^!{CtrlBreak}

You can download the Control-Alt-Break file here:
CtrlAltBreak.zip

All you have to do is save this to a file, load the file in AutoHotKey, and use Control-Alt-C whenever you need your Remote Desktop session back to full screen.

Download the zip file here: INTERSECT_EXCEPT.ZIP

If you have any questions, you can always contact me, or send me tweet.

Incorrect Syntax Near go in SQL Server from Rudy Rodarte on Vimeo.

If you still have questions, please leave a comment and we can work through your particular issue.

C:\Users\YourUserName\AppData\Roaming\Microsoft\SQL Profiler\10.0\Templates\Microsoft SQL Server\100\

The built in templates are found here:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\

As always, the exact location(90,100,110, etc) changes, depending on your version of SQL Server. But this should get you in the correct folder.

=now()

as the expression. Next format the text box as you like and you’re done.
However, I hit a sang on the page x of y. SSRS was giving me an error with it’s built in field:

Globals!OverallTotalPages

After a bit of searching, I found that you have to use

Globals!TotalPages

instead of OverallTotalPages. Your text box expression will look like this:

="Page " & Globals!PageNumber & " of " & Globals!TotalPages

Now, your SSRS page footer is complete!

SELECT  *
FROM    #EmployeeJobs
 
Employee_ID	Job_id
100	500
100	501
100	502
100	503
100	504
191	501
191	502
191	503
191	505
188	503
188	504

But what if you, or your customer want’s the data horizontally? That is easy enough with a CTE. Here is a quick sample that creates a temp table with some employees and jobs.

--  Create a sample table
create table #EmployeeJobs
(     Employee_ID   int
    , Job_id        int
)    
 
--  Insert some values into the table
insert into #EmployeeJobs( Employee_ID, Job_id ) values
(100,500)
,(100,501)
,(100,502)
,(100,503)
,(100,504)
,(191,501)
,(191,502)
,(191,503)
,(191,505)
,(188,503)
,(188,504)
 
--  Use a CTE to build a list the employees and their jobs
;with CTE as
(SELECT   Employee_ID
        , Job_id
        , row_number() over (partition by Employee_ID order by Job_id) as Numbers
FROM    #EmployeeJobs
)
 
--  Display all of the user jobs
select    EMPLOYEE_ID
        , max(case when Numbers = 1 then Job_id else null end) as Job_1
        , max(case when Numbers = 2 then Job_id else null end) as Job_2
        , max(case when Numbers = 3 then Job_id else null end) as Job_3
        , max(case when Numbers = 4 then Job_id else null end) as Job_4
        , max(case when Numbers = 5 then Job_id else null end) as Job_5
        , max(case when Numbers = 6 then Job_id else null end) as Job_6
from    CTE
group by EMPLOYEE_ID

Now, the results will look like this:

EMPLOYEE_ID	Job_1	Job_2	Job_3	Job_4	Job_5	Job_6
100	500	501	502	503	504	NULL
188	503	504	NULL	NULL	NULL	NULL
191	501	502	503	505	NULL	NULL

This probably isn’t the best way to get results in this fashion, but it is certainly quick and easy. Thanks to the guys at SQLServerCentral.com for this one!

The problem was, each row was formatted differently. So I had several thousand rows that looked like this:

Client A (555)
Client B (789) Houston
Client Dallas (4444)

and so on.

I looked around on some Excel Guru sites and found the following formula.

=REPLACE(LEFT(A1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(A1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0


Caveat

Though not perfect, this was exactly what I needed. The one drawback to this code is any cell with non sequential numbers such as:
Client T1000 Loop 360
will not work.

I recently had to use back end data from SQL Server to deliver several reports in excel. All of my SQL code gave me results in seconds; however, the users had distinct formatting requests. I would have to use a few excel tricks to get things just right in each report.

Time in Seconds to HH:MM:SS

For the first request, I had to take the number of seconds and convert to HH:MM:SS. My script gave me results like 18000 seconds or so. To get the results in the desired format, I first divide the value by 86400. 86400 is the number of seconds in a day (60*60*24). Next, in custom formatting, choose [h]:mm:ss. It is important to choose [h]:mm:ss instead of HH:MM:SS because the h in brackets allows numbers greater than 23. So, our example of 18000 seconds becomes 5 hours.

Time to Decimal Hours

For the second request, I had to take the number of seconds and covert to decimal hours. Let’s look at 5 hours and 45 minutes or 20700 seconds. The first step is to divide the seconds by 86400 and convert the value to [h]:mm:ss. In our example, 20700 seconds appears as 5:45:00. Next, divide the value by 0.041666667. The number 0.041666667 = 1/24th of a day. Lastly, format the results as number with 2 decimal places. The result comes out nicely with number of hours before the decimal and fractional hours after the decimal. So, our example of 20700 seconds becomes 5.75 hours.

DateDiff, y u no Pretty?

Sometimes you just need hh:mm:ss

I ran into a case when I needed to display some results in the HH:MM:SS format. We all know and love DATEDIFF, which gives you whole number results. Using DATEDIFF, I was getting results like this:

--	Declare some variables for the requested timeframe
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME--	Set the timeframe variables
SET		@START_DATE = '2011-01-01 16:00:00'
SET		@END_DATE =   '2011-01-01 22:47:22'SELECT DATEDIFF(second,@Start_date, @END_DATE) AS Segundos

Segundos
24442
 
(1 row(s) affected)

The results are all well and good, but that isn’t something that I would want to hand over to the end users. So, I did a bit of hunting and found what I was looking for over on SQLServerCentral.com. The guy wanted to display milliseconds along with the rest of his results. That was overkill so, his script worked perfectly for me. Here is what I ended using:

--	Declare some variables for the requested timeframe
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME--	Set the timeframe variables
SET		@START_DATE = '2011-01-01 16:00:00'
SET		@END_DATE =   '2011-01-01 22:47:22'--	Use datediff to show the results in the
--	HH:MM:SS format
SELECT    CONVERT(varchar(6), DATEDIFF(second, @START_DATE, @END_DATE)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @START_DATE, @END_DATE) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, @START_DATE, @END_DATE) % 60), 2) AS 'HH:MM:SS'

HH:MM:SS
6:47:22

The results come back in a nicer, easily readable format.