Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

MongoDB -Access different databases and Collections

You can use db.getSiblingDB() method to access another database without switching the database.

To List Collections of PP database and query collection named “restaurants”

db.getSiblingDB('PP').getCollectionNames()

The PP Database has three collections

  1. first
  2. restaurants
  3. second

Accessing PP database temporarily from Test database

To access… Read more

0 comments, 1,114 reads

Posted in PowerSQL By Prashanth Jayaram on 7 January 2016

XenApp/XenDesktop 7.6 FP3 VDA Deployment Issue

During the upgrade to XenApp 7.6 FP3 VDA encountered the following message.

I was wondered on seeing this popup to restart the server during the upgrade. After restarting the server for couple of times I was encountered with the same popup window. Well , after further investigation found there is… Read more

0 comments, 186 reads

Posted in PowerSQL By Prashanth Jayaram on 6 January 2016

Install MongoDB 3.2 on Windows

We can install MongoDB as a windows service. The steps are given below

Prerequisite –

Install Hotfix kb2731284 on the MongoDB Server on Windows Box

STEP1 – Download MongoDB

Download the latest release of MongoDB from http://www.mongodb.org/downloads and select the Mongo package based on an OS version

Have downloaded the… Read more

2 comments, 1,037 reads

Posted in PowerSQL By Prashanth Jayaram on 5 January 2016

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 48,000 times in 2015. If it were a concert at Sydney Opera House, it would take about 18…

Read more

0 comments, 156 reads

Posted in PowerSQL By Prashanth Jayaram on 4 January 2016

Learn How to Insert Data From Stored Procedure Into Table?

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also important to know how to retrieve data they return. Now,… Read more

0 comments, 257 reads

Posted in PowerSQL By Prashanth Jayaram on 26 December 2015

SQL to generate Asset Information – Configuration Manager SCCM 2012

SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName,  
            s.Operating_System_Name_and0 AS OSName,  
            pr.Name0 AS ProcessorTypeSpeed,  
            pr.Manufacturer0 Manufacturer, 
            pr.NumberOfCores0 Cores, 
            pr.NumberOfLogicalProcessors0 LgicalProcessorCount, 
            case when pr.DataWidth0=64 then '64 bit' else '32 bit' end DataWidth, 
            m.TotalPhysicalMemory0/1024.00 AS MemoryMB,  
            GS1.TotalVirtualMemorySize0 VirtualMemory, 
            GS1.TotalVisibleMemorySize0 VisibleMemory, 
            ip.IPAddress0,  
            T1.COL AS TotalDriveSize, 
            LastBootUpTime0, 
            DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]           
FROM…

Read more

0 comments, 227 reads

Posted in PowerSQL By Prashanth Jayaram on 11 December 2015

Use FILEPROPERTY to find free space in all the database

The use of SpaceUsed property of the FILEPROPERTY function gives how much space is used also we can derive lot of other attributes of it such as free space and percentage of free space.

For other versions of SQL you can refer the below SQL.

DECLARE @command VARCHAR(5000)…

Read more

0 comments, 274 reads

Posted in PowerSQL By Prashanth Jayaram on 26 August 2015

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

Added few more examples

Prashanth Jayaram

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

Read more

0 comments, 446 reads

Posted in PowerSQL By Prashanth Jayaram on 17 March 2015

How to Replace Multiple Strings in a File using PowerShell

Replace the Data Source and Initial Catalog values of WebConfig.XML

Content of XML file

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[ConnStaging].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>Data Source=localhost;Initial Catalog=Stage;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>
</Configuration>

PARAM(
[String]$DatabaseName='DCTarget',
[String]$XML='c:\webconfig.XML',
[String]$DatabaseServer='

Read more

0 comments, 7,820 reads

Posted in PowerSQL By Prashanth Jayaram on 13 March 2015

T-SQL – How to get the Financial Quarter details of a date field

declare @table table
(
[Paid Date] date
)

insert into @table
values('20150102'),('20150512'),('20150830'),('20151231'),('20141230')

;WITH Quarters AS (
   SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
   SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
   SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION
   SELECT…

Read more

4 comments, 6,690 reads

Posted in PowerSQL By Prashanth Jayaram on 11 March 2015

T-SQL :- How to Search String in all Stored Procedures across All User Defined Databases

There are many different ways to accomplish this tasks.

Download Link https://gallery.technet.microsoft.com/T-SQL-How-to-Search-String-a1704fc6

The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string

CREATE TABLE  #ProcSearch  (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate date)


DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?''…

Read more

0 comments, 6,524 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2015

T-SQL – Read CSV files using OpenRowSet

We can directly access a CSV file using T-SQL.

Input file

Configure server to run Ad Hoc Distributed Queries

sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO

Check for Microsoft Access Driver on your system

SQL

select 
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select *…

Read more

3 comments, 8,733 reads

Posted in PowerSQL By Prashanth Jayaram on 9 February 2015

T-SQL – Query to get Distinct,Sorted, Comma Separated values in to a variable

CREATE  TABLE  #temp(name char(3))

INSERT INTO #temp VALUES ('CD')
,('AB')
,('LM')
,('BC')
,('GH')
,('KJ')
,('AB')

DECLARE @cols AS NVARCHAR(MAX);

SELECT @COLS =  substring(list, 1, len(list) - 1)
FROM   (SELECT list = 
          (SELECT DISTINCT name + ','
           FROM  #temp
           ORDER BY name + ','
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T…

Read more

10 comments, 8,254 reads

Posted in PowerSQL By Prashanth Jayaram on 2 February 2015

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

The above undocumented SP’s  iterate through… Read more

1 comments, 352 reads

Posted in PowerSQL By Prashanth Jayaram on 26 January 2015

SQL- Querying XML attributes from XML Column

Read XML column attributes in to two columns DimType and Dimvalue  from the below example also retrieve the data matching values[DimTypes (WIDTH or Height)].

‘<dimensions>

<dimension name=”width”  value=”12.77″/>

<dimension name=”height”  value=”0.14″/>

<dimension name=”depth”      value=”12.92″/>

</dimenstions>’

Download SQL :-  https://gallery.technet.microsoft.com/SQL-Querying-XML-f700bbb0

 SQL:-

DECLARE @xml XML
SELECT @xml = 
'<dimensions>
  <dimension…

Read more

0 comments, 8,258 reads

Posted in PowerSQL By Prashanth Jayaram on 22 January 2015

T-SQL to Display Weekends Between two Dates

Different ways to find the weekend between two given dates. The SQL requires @begindate and @endate paramteters to be entered in the below SQL

Download T-SQL WeekendBetweenTwoValidDates

DECLARE @beginDate Date='20150101',
		@endDate Date='20150131'


DECLARE @Calendar Table
(CalendarDate Date Primary key, IsWeekend Bit)

WHILE @beginDate <= @endDate
BEGIN
INSERT INTO @Calendar
SELECT…

Read more

2 comments, 536 reads

Posted in PowerSQL By Prashanth Jayaram on 14 January 2015

T-SQL – How to find Next Business day from a given date

There are many methods to find next business day. One common way is to use of calendar table.  This post is to get an output using T-SQL Or UDF function.

Download T-SQL  NextBusinessDay

The details are given below

The script can be executed by feeding Input values to SQL. In… Read more

1 comments, 7,532 reads

Posted in PowerSQL By Prashanth Jayaram on 12 January 2015

SQL – Split delimited columns using XML Or UDF function

The requirement is to convert the delimited column into rows

Input data and required output details are given below

Eno Ename Esloc EDept
1 ABC NJ 10,20,30,40,50

Output:-

Eno Ename Esloc EDept
1 ABC NJ 10
1 ABC NJ 20
1 ABC NJ 30
1 ABC NJ 40
1 ABC

Read more

0 comments, 592 reads

Posted in PowerSQL By Prashanth Jayaram on 9 January 2015

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 50,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 19…

Read more

0 comments, 308 reads

Posted in PowerSQL By Prashanth Jayaram on 30 December 2014

T-SQL – How to Find Number of Words in a Given String

TSQL: To find number of words in a given string

Download the code https://gallery.technet.microsoft.com/T-SQL-How-to-Find-Number-533b6b39

DECLARE @DemoTable Table
    (Col varchar(500));

INSERT  INTO @DemoTable (Col)VALUES ('JOHN M SMITH ABC COMPANY')
 

SELECT col,
  len(Col) - len(replace(col, ' ', '')) + 1 No_Of_Words
  from @DemoTable

When the string has more spaces

DECLARE @DemoTable Table…

Read more

4 comments, 7,772 reads

Posted in PowerSQL By Prashanth Jayaram on 26 December 2014

Newer posts

Older posts