Tuesday, January 29, 2013

Stored procedure for exporting query results to text file

First you need to enable xp_cmdshell( I realize it can be a security issue so I always enable it on demand, do what I need to do and disable it immediately)


sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'xp_cmdshell', 1
reconfigure
go

And now the procedure -

CREATE PROCEDURE dbo.ExportText
AS
BEGIN
DECLARE @bcp AS VARCHAR (8000)
DECLARE @status AS INT

PRINT ''
PRINT 'Lets generate the text file'

SELECT @bcp = 'bcp "SELECT * FROM master..sysdatabases" QUERYOUT "D:\output.txt" -T -c'

EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
IF @Status <> 0
BEGIN
 PRINT 'Something went wrong. No text file for you!'
 RETURN
END ELSE
 PRINT 'Successful! Go check your text'
END


SSRS Report Access Error: User 'DOMAIN\LOGIN' does not have required permissions. UAC issue

I didn't configure reporting services on my local machine during the initial installation. I recently had to test something on my machine and configured everything from 'Reporting Services Configuration Manager' and when I tried to access reports on IE, I initially had to enter my windows login and password (which is silly, how hard is it to get the current login and security token without having to enter manually?)  I was greeted with the below message:

User 'DOMAIN\LOGIN' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed



Really? I had to launch IE using "Run As Admin" to get this to work. But that doesn't solve the problem forever. You either set your IE to run as an admin or disable UAC. But what if you access this server from different machines?

There is a workaround though: Once logged into report server, go to

Site Settings => Security => New Role Assignment => and Grant your admin login a System Admin role.


Wednesday, January 23, 2013

TIL: What makes a query relational?

1.The relational result set does not any order by default. If your query has an ORDER BY clause, your resultset ceases to be relational.
2. For any query to be relational, it must satisfy a number of requirements - No ORDER BY clause, All attributes must have names, all attributes must be unique, duplicates must not appear in the result.