Quickly output a stored proc to Excel with SQLCMD

Now XP_CMDSHELL raises one too many red flags, and we dont encourage its use.

But lets not forget the trusty ol’ tool that we have : SQLCMD.

Try the following line to get the output of the proc to a CSV file (which can be reasd by Excel) :

sqlcmd -S server_name -d database_name -E -Q “exec stored_proc_name;” -o E:\OUTPUT.csv -s, -W

In the code above, -S is followed by the server name, then the database name, -E stands for integrated security (Windows authentication), -Q will execute your stored proc and exit the command prompt, -s is the column separator which is a comma and -W will remove trailing spaces.

Advertisements
Posted in Uncategorized | Leave a comment

Extended Events : Why is “retrieving event information from server” empty ?

Extended Events is a very powerful new feature of SQL Server 2012. The best thing about this is that its so powerful, yet very lightweight (in terms of resource utilization) and extremely easy to set up and use.

Except for one gotcha.

I had set up an Extended Events session and fired it up. A few minutes later, it was still showing “retrieving event information from server” and the  panes were empty. What was wrong? Why wasnt it populating data?

I had a hunch and changed the case of the database name filter. Bingo!

Here in the SQL Server world, we are used to typing away without checking uppercase / lowercase. This was the issue. A small thing, but knowing this can help avoid going after unnecessary code checks!

Posted in Uncategorized | Leave a comment

Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Ok, this is usually seen when reading Job history details through SSMS. So what are your options?

One way is to go and see how many records you have in the history table. It is usually safe to remove some which go back several years:

EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate

You could also use a filter on the SSMS window to return a range of dates. Perhaps you only want to know if the job was successful yesterday. In SQL 2005, you can only graphically select a date, but not the time. Instead, open the same in SQL 2008 and now you can specify a time also.

Now if you are really short of time and you need the results, you have two approaches. Either write a complex query to get a quick look at the details. Or else, create an index on the sysjobhistory system table to get the details fast. Lets look at the index first :

CREATE NONCLUSTERED INDEX IX_SysJobHistory
ON [dbo].[sysjobhistory] ([job_id])
INCLUDE ([instance_id],[step_id],[sql_message_id],[sql_severity],
[run_status],[run_date],[run_time],[run_duration],[operator_id_emailed],
[operator_id_netsent],[operator_id_paged],[retries_attempted],[server])

The code above returned more than 50k records without any filters. Thx, @SQLFool ! ref: http://sqlfool.com/2009/04/a-look-at-missing-indexes/

The other approach is to write a lengthy code :

SELECT top 10

CONVERT(DATETIME, RTRIM(run_date)) +

((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime

, CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime,

server,

step_id, step_name, message,

case run_status

when 0 then ‘Failed’

when 1 then ‘Succeeded’

when 2 then ‘Retry’

when 3 then ‘Cancelled’

when 4 then ‘In Progress’

end as Run_Status

FROM msdb.dbo.sysjobhistory

where job_id = ‘50947C95-DE44-4BD7-AA06-92D419004F5F’

and step_name = ‘(Job outcome)’

order by run_date desc

Code courtesy Timothy J Hartford :

http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx

Hopefully, one of these will work for you!

Posted in Uncategorized | 1 Comment

Find blocking query details from sysprocesses, including text, host, time, etc

Here is a query that I wrote to find details of the blocked queries :

SELECT

— Get Blocked details :

‘spid ‘ + cast(A.spid as varchar(100))       + ‘ is being blocked by spid ‘ + cast(A.blocked as varchar(100)) as Blocked_SPIDs,

A.loginame as Blocked_Loginame, A.open_tran as Blocked_Open_transaction, T.text as BlockedText,       DB_NAME(A.dbid) as Blocked_DatabaseName,

A.program_name as Blocked_Program_name, A.nt_domain as Blocked_NT_Domain, A.nt_username as Blocked_OS_Username, A.login_time as Blocked_LoginTime,

A.status as Blocked_Status, A.waittime as Blocked_Time_In_Milliseconds, SUBSTRING(A.hostname,1, 12) as Blocked_Host,

— Get Blocking details :

B.loginame as Blocker_Loginame, B.open_tran as Blocker_Open_transaction, R.text as BlockerText,       DB_NAME(B.dbid) as Blocker_DatabaseName,

B.program_name as Blocker_Program_name, B.nt_domain as Blocker_NT_Domain, B.nt_username as Blocker_OS_Username, B.login_time as Blocker_LoginTime,

B.status as Blocker_Status, B.waittime as Blocker_Time_In_Milliseconds, SUBSTRING(B.hostname,1, 12) as Blocker_Host

FROM sys.sysprocesses A

OUTER APPLY sys.dm_exec_sql_text(sql_handle) T — this is the blocked text

INNER JOIN sys.sysprocesses B

OUTER APPLY sys.dm_exec_sql_text(sql_handle) R — this is the blocker text

ON B.spid = A.blocked

and A.spid>50

and A.blocked ! = 0 — Identifies any blocking spids

Posted in Uncategorized | Leave a comment

Invoke or BeginInvoke cannot be called on a control until the window handle has been created.

A frustrating error.. and the solution is even more so!

If you get this error during SQL Server 2008 installation, ensure the following :

1. All other windows are closed. That includes IE tabs.

2. The currently active installation window (which shows you the status of the SQL Server installs) is always maximized.

That fixed the problem.

Is it a GUI bug? Beats me… where is that SQL Server 2012 Core installation now…..

Posted in Uncategorized | Leave a comment

Drop a temp table

Well folks we know that temp tables are closed when the user closes his/her session. In fact, BOL explicitly states :

“Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.”

Now here is the problem. One of our users complained that he had disconnected from SQL Server. When he came back, the temp table that he had created was still present. It was preventing him from running a query successfully.

Note that though he disconnected from the instance and then re-connected, the temp table was still present.

We could see that his sessions were still active in many databases. After getting his permission, we proceeded to close all his sessions.

As expected, it dropped the temp table also.

Hope this helps!

Posted in Uncategorized | Leave a comment

Impersonation error when processing a cube (related to SSAS)

When you build and deploy the Cube (in Business Intelligence Development Studio, now superseded by SQL Server Data Tools), the next step is to process the Cube.

I ran into an error which went similar to this : “A connection could not be made to the datasource with the DataSourceID of xxxx.”

The problem is well known and simple to resolve. When we first create the Data Source, we must specify the Impersonation option. I had given it as “Inherit”. However, this fails as this cant login to the Analysis Server database.

The solution is to give a more privileged user (depending on your security settings). For me, I could give the Windows login that connects to my SQL Server here. Therefore in the Design View of BIDS, right click your Data Source in Solution Explorer and choose Open. In the “Impersonation Information” tab, pls give the username/password in “Use a specific Windows username and password”. Now rebuild, deploy and process the cube.

I hope this helps. Pls let me know if there are any doubts.

Posted in Uncategorized | Leave a comment