SQL

Free download Microsoft Press eBook: Microsoft SQL Server 2012 Pocket Consultant.

Hello all,

I just want to share a link to download a free copy of Microsoft Press eBook ‘Microsoft SQL Server 2012 Pocket Consultant’.
This could be a great eBook for you to learn all about how to manage SQL Server .

Currently, Microsoft Press just posted the first  two chapters within this free copy as Managing SQL Servers and Policy-Based Management.

So, SQL Geeks !!! Grab your digital copy and learn all about how to manage SQL Server!

I have already downloaded and have started to read and learn the things.Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 and to manage SQL Server.

http://blogs.msdn.com/b/microsoft_press/archive/2012/07/25/free-download-managing-sql-servers-and-policy-based-management-an-excerpt-from-microsoft-sql-server-2012-pocket-consultant.aspx

Contents at a Glance

Chapter 1: Managing SQL Servers 

  • Using SQL Server Management Studio
  • Managing SQL Server Groups
  • Managing Servers
  • Using Windows Power Shell for SQL Server Management
  • Starting, Stopping, and Configuring SQL Server Agent
  • Starting, Stopping, and Configuring MSDTC
  • Managing SQL Server Startup
  • Managing Server Activity

Chapter 2: Implementing Policy-Based Management

  • Introducing Policy-Based Management
  • Working with Policy-Based Management
  • Configuring Central Management Servers
  • Managing Policies Throughout the Enterprise

Thanks,

Anil Maharjan

How can we rollback mistakenly UPDATE statement change in SQL Database.?

Hello all,

Beside than my regular helpful blog posts, it’s just a discussion type blog post that might be useful to all of us.

Let’s say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

* backup the transaction log

* restore the database from a full backup

* restore all transaction logs (in sequence) to the time right before the accident occurred.

Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven’t tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below: http://www.apexsql.com/sql_tools_log.aspx

So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012  then really want to here.

Regards,

Anil Maharjan

Help on installing the SQL Server 2012 integrated along with SharePoint 2010.

Hello all,

After long time gap, I just want to post a blog that might be helpful to the one who is going to learn, try and setup these great products of Microsoft as SQL Server 2012 and SharePoint 2010.

If you are going to learn, try and install SQL Server 2012 integrated along with SharePoint 2010 then this blog might be very helpful.

Some of the things you should be consider before installing and some steps for proper installation are as:

Please read the requirements of your machine and requirement needed for setup clear fully before you began to install.

Step 1. Firstly you need to install the Microsoft SQL Server 2012 either standard or enterprise version .The installation setup can be easily found in the link below for 180 days as Evaluation version.

http://www.microsoft.com/sqlserver/en/us/default.aspx

For installing the SQL Server 2012 properly you can see the nice video in

http://www.youtube.com/watch?v=-6D69P6O9AI

Step 2. After successful installation of the SQL Server 2012, you can install SharePoint 2010 either in Standalone mode or in Server Farm mode. Here is the link to download

http://technet.microsoft.com/en-us/evalcenter/ee388573.aspx

Here, The Standalone SharePoint installation is supported and common for development environments of Reporting Services.

However a SharePoint farm installation is recommended for a production environment. A farm is enabled when you choose the Server Farm option in SharePoint setup. You must have a farm installation if you want to use PowerPivot for SharePoint or Power View.

Note: You need to install the SharePoint 2010 Enterprise Edition for Server Farm option or to use PowerPivot for SharePoint. We can’t simple install the Server Farm mode within Windows7 OS .If you want to try then you should have to create the Virtual Machine’s for Windows Server 2008 within Windows 7 OS. Also, the user must be the domainuser, and we can’t use the local user.

Step 3. If you are trying to install the SharePoint 2010 in Standalone mode for development purpose then the link below shows the Step by step guide to install SharePoint 2010 in Standalone mode.

http://jeanpaulva.com/2012/02/22/sharepoint-2010-installation/

After installation of SharePoint 2010 as per above, while configuring SharePoint 2010 central admin (SharePoint product configuration wizard) after configuring the IIS 7 as needed you will get the errors as.

i. Could not load file or assembly ‘Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17331

ii. An exception of type Microsoft.SharePoint.Upgrade.SPUpgradeException was thrown. Could not load file or assembly ‘System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

http://go.microsoft.com/fwlink/?LinkID=122517

After this you will successfully install and configure the SharePoint 2010 in Standalone mode. If required you can update the Hotfix for SharePoint 2010 SP1 from Microsoft too.

Step 4. Basically you just need to be careful with Reporting Services configuration, you need to choose SharePoint Integrated Mode in report server mode option while configuration and create the report server DB for SharePoint integrate mode, if you plan to embed reports within your SharePoint.

It’s not possible anymore with SQL Server 2012 to configure Reporting Services for SharePoint in the Reporting Services Configuration Manager. We have to use the SharePoint Central Administration to Configure Report Server Integration in SharePoint mode.

If you have chosen the Reporting Services Add-In for SharePoint Products while installing then you don’t need to install again Add-in.

If you are configuring a report server to run within a deployment of a SharePoint product, you must specify integration settings that determine the connection between the SharePoint technology instance and a Reporting Services report server

Here is the link to Configure Report Server Integration in SharePoint Central Administration

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

Step 5. Also, if you plan to use Power Pivot for SharePoint then you need to run install SQL Server 2012 again and choose for SQL Server Power Pivot for SharePoint mode which requires the Domainuser for analysis services to install. Also, for this you need to install the SharePoint 2010 in Server Farm Mode and also needs to configure as similar .Here is the link to setup in Server Farm mode.

http://msdn.microsoft.com/en-us/library/hh231680.aspx#bkmk_sp1

Also, to learn PowerPivot more here’s the link below and also you need Ms-Office 2010.

http://www.microsoft.com/en-us/bi/powerpivot.aspx

Besides these you also need to download the Visual Studio 2010 from link below

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=28992

References:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/ab8046fb-0cf4-4891-9165-02ca1ec91e31

Hope this Blog post will surely helpful to the one who wants to learn, try and setup these great products of MicrosoftSQL Server 2012 and SharePoint 2010.

Regards,

Anil Maharjan

Download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’

Hello all,

After a long time gap, I just want to share a link to download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’.

This could be a great eBook for you to learn and know about what’s inside the ‘ SQL Server 2012 ’  released by Microsoft.

I have already downloaded and have started to read and learn the things .Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 , virtual lunch event-sessions and more.

http://www.microsoft.com/sqlserver/en/us/default.aspx

Contents at a Glance

Part I Database Administration (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

Part II Business Intelligence Development (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Regards,

Anil Maharjan

How to drop/delete all the user define tables within a SQL database.

Hello all,

Firstly on this lovely Valentine’s day esp, I wish Happy Valentine’s day to all the loved ones 🙂 .

I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete all the user define tables from a Database in SQL. So, If any one just need to delete all the user define tables from a SQL DB then you can use just a simple Query as below:

USE Database:--replace with your Database name.
GO
DECLARE @tbl_name as varchar(256)
DECLARE @flag as int
DECLARE @name as varchar(256)
set @flag=(SELECT count(*) FROM sysobjects where type='U')
while(@flag>0)
BEGIN
set @name=(SELECT top 1 name FROM  sysobjects where type='U')
set @tbl_name = 'drop table ' + @name
exec(@tbl_name)
print @tbl_name
set @flag=@flag-1
end

within a database we can have objects as TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there. So, If anyone just want to delete only User Define TABLES without dropping the entire Database and other objects the above query is the best one. If someone drops the database that means those objects needs to be recreated. So,in order to save the time and for quick approach I found this useful .

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan

How to delete a particular row from a table in SQL.

Hello all,

After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete the particular row from a table in SQL Database.

While working someone might mistakenly  insert the duplicate same data multiple times within a table that might contains a huge amount of data and after populating that table .He/she wants to delete that duplicate data inserted from a table.

So, for this we might drop and recreate the table or truncate the table and insert again. But, if it is a large table then it would be better to find the duplicate row within a table and delete duplicate one which helps in time consumption and server utilization too.

In order to obtain this , we can simple used a simple query as

WITH newtbl as(SELECT *, 'row number'=ROW_NUMBER() over (order by FirstName) FROM CustomerList)
DELETE FROM newtbl WHERE [row number]=2

--where CustomerList is a user table having dublicate record.

This is the tested script for Test Database where CustomerList table have duplicate records which needs to be delete.

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan

How to move the tables without having clustered index from one file group to another file group simply in SQL 2008.?

Hello All,

This might not be the proper for the blog post but it helps me to understand more regarding the transfer of tables within different file groups in MS-SQ L. So, I think it will be more helpful to the others who indeed needs such information related to file groups .

I had posted as below within MSDN

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d35ab8ff-a6a1-458c-b3e4-b2c9f6a1ff83

I have few issues regarding the transfer of the tables from one file group to another file group  in SQL 2008 and also How can we  backup and restore the particular database based on file group level.

Let’s say I have a tables stored within the different FG. such as

Tables                                                                                                                           File group

Dimension tables                                                                                                              Primary

Fact tables                                                                                                                           FG1

                                                                                                                                            FG2…

zzz_tables                                                                                                                           DEFAULT_FG

dim.table1                                                                                                                           DEFAULT_FG

dim.table2                                                                                                                           DEFAULT_FG

…                                                                                                                                             ….

Here all I want to transfer the dim.table1 ,dim.table2  from  DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2  from one FG to another .I have tried somewhat but I couldn’t get the exact way .So if someone have better idea please share your knowledge that would be really appreciated.

Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing  the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?

Currently I have no any clustered index defined within the dim.table1 ,dim.table2 and so on.

Cannot we transfer the tables from DEFAULT_FG to another FG without having clustered index on that tables or without select/insert operation so that I couldn’t have to recreate the tables. I have researched somewhat and found that we couldn’t  move the tables without having the clustered index to another file group simply in MS-SQL so sad :(..!!!

Hope to hear from the one who knows better approach for this kind of task .Your simple help will be much appreciated.

Some helpful links regarding transfer of tables between different FG’s and restore/backup FG’s.

http://www.sqlservercentral.com/articles/Administration/64275/

http://www.sqlservercentral.com/Forums/Topic695367-146-1.aspx#bm695717

http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups

Well after all my full day research on this topic had paid off, I finally got the solution and am so happy to research on these things. It makes us feel really happy after all our research and hard work doesn’t goes as waste.

Finally I got what I am looking for and want to make sure that I am able to transfer the tables from DEFAULT_FG to another FG without tables having clustered index on that tables .

With the help of the link below I finally got my solution where Roberto’s coded store procedure simply works for this.

Really thanks to him for his great post and thanks to everyone for their response in MSDN and their valuable time.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Regards,

Anil Maharjan

How Can I know who has changed anything in SQL?

Hello all,

After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL.

I need to find the one who has changed the tables within my SQL Database that I have created.

For certain reason ,we may need to find /to know who has done any changes within SQL. Such as add view, delete table, modify table, create database, add column, stopped SQL agent job, create new job, modify rules, add roles, new login, and etc.

In order to trace this information and find out my solution, I have researched somewhat and find the solution as.

we can enable and disable the default trace in SQL Server. Meanwhile we can use the default trace enabled option to enable or disable the default trace log files.

If we want to capture the information about who changed the objects in SQL Server. Please try to run the script, which works for me as below:

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('', REVERSE(path))+1) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check who dropped and created objects, altered objects
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       gt.IntegerData, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
	gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
where gt.ObjectName ='DimTable' and gt.DatabaseName ='Database'
ORDER BY StartTime;
 --DimTable -replace with the trace table and Database -replace with your DB

I am able to find the one who have changed my tables that I have created.

Also we should have to know that, SQL Server only shows you the IP address of the Terminal Server machine. It has no knowledge about how the user connected to that server. You would have to find the RDP logs and enter them into the database somehow to correlate the information. Which still can be inconclusive if there are more than one user connected through RDP at the same time.

Reference from the link:

http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing/

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ef5f82e3-71a6-4e65-989f-5ab7b84bbc82

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SQL.

Regards,

Anil Maharjan

Load 1TB data in just 30 Minutes with SSIS

Today , I came to read some nice blog post by Microsoft regarding the data loading process.

If your company needs to load the maximum data then I think this post would be really helpful.
If you want to load 1TB data in just 30 Minutes with SSIS then.. Plz read this post by Microsoft.
http://msdn.microsoft.com/en-us/library/dd537533(v=SQL.100).aspx

since, the article was posted long time ago but might be helpful for the one who is just looking for this kind of approach.
Though ,I could not tried it out but sooner or later I would definitely like to test this process too.

Thanks,
Anil Maharjan