Tag: SQL

SRSS – Get list of Reports and Its Data sources and Datasets

I needed to get the list of my reports and what data source and dataset they use. use ReportServer; WITH XMLNAMESPACES ( DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition’, ‘http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd ) SELECT distinct ReportName = name ,DataSetName = x.value(‘(@Name)[1]’, ‘VARCHAR(250)’) ,DataSourceName = x.value(‘(Query/DataSourceName)[1]’,’VARCHAR(250)’) ,CommandText = x.value(‘(Query/CommandText)[1]’,’VARCHAR(250)’) FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML FROM ReportServer.dbo.Catalog C WHERE C.Content is …


SQL – Linked Server xp_prop_oledb_provider error

SQL- Creating linked server to AS400

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you. First create linked server, then change options to it. EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’ Change option: USE [master] …


MSSQL – how to send notification to multiple users

Recently, I was asked a question, how to send notification to multiple users. First you must create operator in SQL Agent. In field “E-mail name:” you enter multiple e-mails, divided by semicolon (;). And then you just have to set notifications in Agent Job Good Luck


SQL SMSS connect as another Windows NT user

I tried to find out how to connect through SMSS to SQL server as another domain user. You must run SMSS as “RUN AS..” so the code is as follows: runas /netonly /user:domain\user “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”   EDIT:!! Due to version changes: %windir%\System32\runas.exe /netonly /user:domain\user “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -S SERVER” EDIT!! When …


T-SQL: Get number from string

Mail merge from SQL Database mail

E-mails in MSSQL– How to setup Database Mail

TSQL – working with folders and output to file

Rename SQL Server 2008 – standalone