Category: SQL

MS SQL General Instance Information

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 ‘’, ‘’ 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 Server Reporting Service (SSRS) – Use email friendly name

I had my SSRS running smoothly, but there was one thing bothering me. That was email sender friendly name. In RS Configuration Manager you can setup e-mail settings, and enter senders e-mail address. But every time I got an email with report, the sent from stated But I need an friendly name. So, I …

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] …

SQL – Table comparison

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