ALTER computed column

There is no special command in SQL Server to ALTER any computed column.
But we can do by using below two ways:

1. Drop computed column and recreate
2. GO To Table in Object Explorer , right click on table and open in DESIGN mode.
Identify the column and edit "COMPUTED COLUMN Specification > Formula " property .

Second method will loose all existing value in computed column so best is to go for first method, it will drop, recreate and populate column again.
But never forget to note computed column expression before dropping column.

Thanks,
Randhir

List All Objects Using Linked Server

While deployment a project on Production server I got an requirement to update my all LINKED Server references which is a very boring  task to me.
I found a very useful function on MSDN to check cross-database dependencies.

/*-----List objects using Linked Server-------*/
SELECT
    Distinct
    referenced_Server_name As LinkedServerName,
    referenced_schema_name AS LinkedServerSchema,
    referenced_database_name AS LinkedServerDB,
    referenced_entity_name As LinkedServerTable,
    OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
And referenced_Server_name = 'Enter LinkedServerName here'

Thanks

All About BIG Data

I have found many articles on Big Data but below are few selected and categories.

Hope you will like .

 

What is Big Data

http://www.mckinsey.com/insights/business_technology/big_data_the_next_frontier_for_innovation

http://www.ibmbigdatahub.com/blog/paving-new-roads-through-unexplored-terrain-big-data

http://www.webopedia.com/TERM/B/big_data.html

http://www.teradatamagazine.com/v11n01/Features/Big-Data/

http://tdwi.org/blogs/philip-russom/2011/06/big-data-analytics-faq.aspx

http://tdwi.org/blogs/philip-russom/2013/03/hadoop-technologies-in-use-today-and-tomorrow.aspx

 

Why Big Data

http://www.ft.com/intl/cms/s/2/3c59d58a-43fb-11e2-844c-00144feabdc0.html#axzz2ZIuzIkdY

http://www.brw.com.au/p/tech-gadgets/why_big_data_is_becoming_big_problem_poHkIOIyFvy0arij4456jL

http://www.mike-walsh.com/blog/bid/60043/Why-Big-Data-Is-About-Making-Better-Decisions

http://bits.blogs.nytimes.com/2013/06/01/why-big-data-is-not-truth/

http://www.wired.com/insights/2013/06/why-big-data-will-drive-the-next-big-boost-in-productivity/

http://www.cio.com/article/733698/5_Reasons_to_Move_to_Big_Data_and_1_Reason_Why_It_Won_t_Be_Easy_

http://venturehire.co/blog/why-big-data-is-next-big-thing

http://www.forbes.com/sites/christinemoorman/2013/03/17/the-utilization-gap-big-datas-biggest-challenge/

 

 

Why Not Big Data

http://www.forbes.com/sites/silberzahnjones/2013/07/02/three-reasons-why-big-data-doesnt-make-you-smarter-lessons-from-the-world-of-intelligence/

http://management.fortune.cnn.com/2013/06/20/big-data/

http://www.manufacturing.net/articles/2013/06/why-big-data-is-meaningless

 

Big Data Challenges

http://innaxis.org/big-data-challenges-and-benefits/

http://forwardthinking.pcmag.com/pc-hardware/297514-the-challenge-of-analytics-in-a-big-data-world

http://washingtontechnology.com/articles/2013/02/28/big-data-challenges.aspx

http://washingtontechnology.com/articles/2013/02/28/big-data-challenges.aspx

 

Opportunities

http://www.baselinemag.com/analytics-big-data/slideshows/big-data-challenges-and-opportunities/

http://www.gartner.com/technology/research/big-data/

http://www.crn.in/search?indexCatalogue=test&searchQuery=Growing%20Opportunities%20In%20Big%20Data&wordsMode=0

http://blog.startupprofessionals.com/2013/04/big-data-is-getting-bigger-as-startup.html

 

 

Career

http://www.cnbc.com/id/100792215

http://management.fortune.cnn.com/2013/05/21/big-data-jobs-2/

http://www.eweek.com/it-management/slideshows/big-data-job-opportunities-spread-across-u.s.-industries-dice/

http://www.computerworld.com/s/article/9235565/Big_data_means_big_IT_job_opportunities_for_the_right_people

http://www.smh.com.au/it-pro/business-it/big-data-scientists-who-needs-them-20130716-hv0qb.html

http://www.forbes.com/sites/meghanbiro/2013/06/23/big-data-is-a-big-deal/

http://venturebeat.com/2013/04/29/want-a-career-in-big-data-youll-need-more-than-a-degree/

http://www.infoworld.com/t/it-jobs/how-get-hot-job-in-big-data-188629

http://www.computerworld.com/s/article/9235565/Big_data_means_big_IT_job_opportunities_for_the_right_people

http://www.cloudthat.in/big-data-career-opportunities/

 

Technical Articles

http://hackerspace.lifehacker.com/do-you-speak-big-data-511378990

http://readwrite.com/2013/05/29/the-real-reason-hadoop-is-such-a-big-deal-in-big-data#awesm=~obQQTMTJGXBWCt

http://www.sqlservercentral.com/articles/Big+Data/95647/     --- Big Data for sql folks

http://sqlmag.com/database-administration/microsofts-david-campbell-discusses-big-data

http://sqlmag.com/sql-server/clearing-big-hurdles-big-data

http://www.itbusinessedge.com/cm/blogs/vizard/an-sql-based-alternative-to-managing-big-data/?cs=49225

http://www.calsoftinc.com/bigdataexpertise.aspx

 I LIKE :

http://www.davidmantilla.com/2012/03/02/cloudera-hadoop-developer-training-is-it-worth-it/

http://www.linkedin.com/groups/My-experience-on-Hadoop-developer-2258445.S.257354155?qid=6b6ea725-1ff1-4218-9b24-2487c023d810&trk=group_items_see_more-0-b-ttl

 http://hortonworks.com/blog/translate-sql-to-hadoop/
 

Tutorials :
http://hadoopnet.com/tutorial/
 

Open Report Manager without Run As Administrator option

Below are steps to avoid "Run As Administrator" option while opening Report Manager

Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.

1. Click Allow to continue.
   
2. Open Report Manager link
   
3. Click Tools.
   
4. Click Internet Options.
   
5. Click Security.
   
6. Click Trusted Sites.
   
7. Click Sites.
   
8. Add http://.
   
9. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
   
10. Click Add.
    
11. Click OK.
    
12. In Report Manager, on the Home page, click Folder Settings.
    
13. In the Folder Settings page, click Security.
    
14. Click New Role Assignment.
    
15. Type your Windows user account in this format: \.
    
16. Select Content Manager.
    
17. Click OK.
    
18. Click Site Settings in the upper corner of the Home page.
    
19. Click security.
    
20. Click New Role Assignment.
    
21. Type your Windows user account in this format: \.
    
22. Select System Administrator.
    
23. Click OK.
    
24. Close Report Manager.
    
25. Re-open Report Manager in Internet Explorer, without using Run as administrator
    

Click to get more information:

http://msdn.microsoft.com/en-us/library/bb630430.aspx

Type column description of ReportServer Database

Here is the Type column description of ReportServer.Dbo.Catalog Table :

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)

Thanks,
Randhir

Usefull queries of ReportServer Database

http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

Thanks,
Randhir

Date Source's Dependent Report

Use ReportServer

GO

 

SELECT
    C2.Name AS DataSourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.DataSource AS DS
        INNER JOIN
    ReportServer.dbo.Catalog AS C
        ON
            DS.ItemID = C.ItemID
                AND
            DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
                        WHERE Type = 5) -- to  identifies data sources
        FULL OUTER JOIN
    ReportServer.dbo.Catalog C2
        ON
            DS.Link = C2.ItemID
WHERE
    C2.Type = 5
ORDER BY
    C2.Name ASC,
    C.Name ASC;

 

 

Thakns,

Randhir

Linked Report's Data Set ReportServer Reporting Service

Use ReportServer
GO

;WITH XMLNAMESPACES (

DEFAULT

'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rs

)

SELECT

LinkedReport,

x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,

x.value('CommandText[1]','VARCHAR(50)') AS CommandText

FROM (

SELECT

    c1.Name AS LinkedReport, CAST(CAST(C2.content AS VARBINARY(MAX)) AS XML) AS reportXML

FROM dbo.Catalog c1

INNER JOIN dbo.Catalog AS c2 ON c1.LinkSourceID=c2.ItemID

WHERE

c1.Type = 4

 

) a

CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

Thanks,
Randhir

 

 

 

Get only Linked Report from ReportServer database Reporting Services

Using Type 4 we can get all linked report from catalog table.

SELECT ItemID, Path, Name, ParentID, LinkSourceID
FROM Catalog
WHERE Type = 4

Thanks,
Randhir

Compare Date Range columns with Date Range Parameters

Comparing Date Range columns with Date Range Parameters always confuse us.
Here is the simple solution  using below query:


Declare @DateFrom DateTime, @DateTo DateTime
Set @DateFrom = '2012/10/01'
Set @DateTo = '2012/10/30'

Select *
From Schedule
Where
ScheduleFrom < = @DateTo And
ScheduleTo >= @DateFrom


 This is also described as Allen's Interval Algebra. 
http://en.wikipedia.org/wiki/Allen%27s_Interval_Algebra


Randhir

Get Agent Job Name for SSRS Report Subscription

Every time when we create Reporting Service Report subscription , a new Agent Job creates.
Here is the useful script to get Agent Job Name for each subscription :

Use ReportServer
GO
SELECT    
Schedule.ScheduleID AS SQLAgent_Job_Name,
Subscriptions.Description AS SubscriptionName,
Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath
FROM         ReportSchedule
INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID






Or More Advance ...


  SELECT    
 Schedule.ScheduleID AS AgentJobName,
 Subscriptions.Description AS SubscriptionName,
  Subscriptions.DeliveryExtension AS DeliveryExt,
  [Catalog].Name AS ReportName,
  [Catalog].Path AS ReportPath,
 SUBSTRING(ExtensionSettings, LEN('TO') + CHARINDEX('TO', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('TO', ExtensionSettings) + 1) - (LEN('TO') + CHARINDEX('TO', ExtensionSettings))) AS 'To Email recipient List',
CASE CHARINDEX('CC', ExtensionSettings) WHEN 0 THEN
  ''
 ELSE
   SUBSTRING(ExtensionSettings, LEN('CC') + CHARINDEX('CC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('CC', ExtensionSettings) + 1) - (LEN('CC') + CHARINDEX('CC', ExtensionSettings)))
END AS 'CC Email recipient List',
CASE CHARINDEX('BCC', ExtensionSettings) WHEN 0 THEN
 ''
 ELSE
 SUBSTRING(ExtensionSettings, LEN('BCC') + CHARINDEX('BCC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('BCC', ExtensionSettings) + 1) - (LEN('BCC') + CHARINDEX('BCC', ExtensionSettings)))

END AS 'BCC Email recipient List'

FROM        
  ReportSchedule
INNER JOIN Schedule  ON   ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions  ON   ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]  ON   ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE
  Subscriptions.DeliveryExtension = 'Report Server Email'

Remove Milisecond from DateTime value.

I like to use following two ways to get rid of Milisecond from DateTime value:

Select GetDate()


1. Select CONVERT(Varchar,GetDate(), 120)

2. Select DateAdd(MS, -DatePart(MS,GetDate()), GetDate())

How to get Computed column definition ?

It is easy..using sp_Helptext system stored procedure we can get computed column definition .

Create Table ComputedColumn
(
AmountA INT,
AmountB INT,
AmountC AS (ISNULL(AmountA,0) + IsNull(AmountB,0))
)

INSERT INTO ComputedColumn
SELECT 1, 2

SELECT * FROM ComputedColumn

sp_Helptext ComputedColumn , AmountC 
 
OUTPUT
----------
(isnull([AmountA],(0))+isnull([AmountB],(0)))
 

Table Variable in Execute SQL Task SSIS

Yesterday one of my colleague ask me to resolve an issue with table variable in SSIS.

What was that issue, she was trying to use Table variable in "Execute SQL Task", everything was working but no data was loading in table.

After reading an article what we found that by just putting "SET NOCOUNT ON" statement on the top of TSQL script will resolve that issue.

Means when we simply run any SELECT statement then SQL Server returns two sets as an output , one is result of Select statement and second is "no of rows affected by a Transact-SQL statement".

SSIS treat last result as an original output which is not in actual , so by putting  "SET NOCOUNT ON" statement we can stop the last output .


Thanks,
Randhir

Excel File Connection Manager Connection String Problem

Today I encountered a following error on Sql Server 2012 while creating a basic SSIS package to load multiple Excel Files in database.

[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

I spent a couples of minutes to identify issue ,finally found solution:

Choose "Excel File Path" instead of "ConnectionString"while setting expression on Excel Connection Manager .


Hope this full help others who are facing the same problem.

Thanks,
Randhir

How to list Stored procedure parameters ?

One of the simple way to use sp_Help system stored procedure to get all parameters properties for a particular stored procedure but what if we have to list all stored procedures parameters using TSQL .
Here is a simple query :

Select OBJECT_NAME(SP.OBJECT_ID) AS ProcedureName,
PR.name AS Parameters, TY.name AS DataType, TY.max_length AS Length, TY.precision AS Precision
From sys.procedures SP
Inner Join sys.parameters PR On SP.object_id = PR.object_id
Inner Join sys.types TY On PR.user_type_id= TY.user_type_id
----Where OBJECT_NAME(SP.OBJECT_ID)  = ?

Cheer...

Correlated Subquery SQL Server

Correlated subquery is also known as repeating subquery where subquery depends on outer query of its values. Correlated subquery executes repeatedly once for each row that are selected by outer query.

e.g.
Select *
From Employees E
Where E.EmpID In (Select EmpID From EmployeeSalary Where EmpID = E.EmpID)

Database mail log using TSQL (Just Learned)

SYSMAIL_ALLITEMS is a view in MSDB database is used to check database mail log .
Contains one row for each message processed by Database Mail. This is really helpfull view to troubleshoot notifications .

Count String Occurrence SQL Server

Sometime we have to count the occurrence of any specific string like :

Declare @Input NVarchar(Max)
Set @Input = 'A92GPGCatherineGeorgeB2VZ9VCARMENNAVARROARAUZ'

Here we have to count the occurrence.
so we can get this by using simple script :

Select (LEN(@Input) - LEN(REPLACE(@Input, '', ''))) / LEN('')

Thanks...

Null value is eliminated by an aggregate or other SET operation

This warning comes usually while using aggregated function on column containing one or more NULL values.

To understand run following code :

Declare @R Int

Set @R = (Select SUM(N) From (Select NULL AS N UNION Select 1) aa)

---- OUTPUT
----- Warning: Null value is eliminated by an aggregate or other SET operation.
 
There are two solutions to get rid of this warning :
1. SET ANSI_WARNINGS OFF
Turn Off the warning using command.
 
2. ISNULL
Use ISNULL function on column.
e.g :
 
Declare @R Int

Set @R = (Select SUM(ISNULL(N,0)) From (Select NULL AS N UNION Select 1) aa)

--- OUTPUT
--- Command(s) completed successfully.

I like to use second method always , what you use ?
Note : Make sure to use the ISNULL function before using aggregate function otheriwe you will face the same warning again.