Wednesday, October 11, 2017

Two ways to reset the identity of a table in sql server.

I know of the following two ways to reset the identity of a table in sql server.

You often tend to use dummy data during the course of your development.  If you have identity columns in your table, the identity number grows. 
So you might want to reset the identity to 0 before you go to production.

This can be done in two ways.

First way is to use the DBCC CHECKIDENT command.

To check what the next identity number is going to be   you can use the below command
DBCC CHECKIDENT (tablename)
This will give you a message – sample below

Checking identity information: current identity value '746', current column value '746'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


If you want to reset the identity number to 0 you can use the below command

DBCC CHECKIDENT (tablename, Reseed, 0)

Second way is to use the Truncate command.
We always tend to use the delete command for deleting records.  This is oggd when you have child relationships.

You can use Truncate table tablename command after a delete command.  Truncate command will ensure that the identity of the table is also reset.

Do you know of any other ways to achieve this ?

Thursday, October 05, 2017

My presentation at the Power BI User Group

Had a very good time presenting at the Power BI User Group .

My presentation was about Power BI Report Server --

I covered Installation, tips and tricks while installing Power BI Report Server and created a Power VI Report and published to the Power BI Report Server. 

Since this is a new product, there were a lost of questions asked.

The link to the meetup is Power BI Meetup

Thanks for the opportunity @Reza and @Leila Here is a link for the slide deck https://www.slideshare.net/secret/iP3JrBRS9R8Ch4




Wednesday, October 04, 2017

Speaking at the Power BI User Group Auckland today

I will be speaking at the Power BI User Group Auckland on 04 October 2017 on "Getting Started with Power Report Server" at 5:30 pm. This session will cover • Tips on getting your Power BI Report Server installed • Create a power BI report • Publish the report on the Power BI Report Server You can see the full details here

Sunday, September 17, 2017

Could not load folder contents - SSRS Report Manager

When I tried to access the report manager after a fresh installation of Sql Server Reporting Serivces 2016 using the url http://localhost/Reports , I received the below error.



By default the installation gives access to Builtin\Administrators. In order to resolve this error below are the steps that you can follow.
Step 1 Run internet explorer as Administrator and open the Report Manager URL http://localhost/Reports



Step 2 Click on Site Settings icon and then click on Security as shown.  You can see only one assignment of roles here for Builtin\Administrators

Step 3 Click on Add group or user assign the role assignment as shown below. 



Re-open internet explorer (non-admin) and recheck the url. You will not get the error anymore.

Friday, September 08, 2017

SSIS Error -- Exception deserializing the package "Access to the path ...... denied'

I was trying to build an SSIS package that one of my colleagues has created.

The first error that was thrown was  as below:
Exception deserializing the package "Access to the path 'C:\BACKup\Test\Test\bin\Development\Tcopy.ispac' is denied.".

Here are the steps that I have followed to resolve this.

Step 1  Check whether the package user has access to the path of the solution.
Step 2. Close the solution.
Step 3  Open Task Manager and end any process of DTS.  as shown below.







Step 4  Delete the .ispac file that is created in the bin/Development folder
Step 5  Reopen the solution and rebuild the package.

Hope these steps will help you resolve the build issues 

Monday, September 04, 2017

My presentation at SQL Saturday Auckland #655

I had an awesome day presenting at #sqlSaturday Auckland on 2nd September 2017.  My topic was on Predictive Analytics using  R  in SQL Server.



Link for my presentation below

Predicitve Analytics using R in SQL Server 2016




Friday, September 01, 2017

Rerun failed SSRS subscriptions

Below is the script I have used to rerun the SSRS Report subscriptions that have failed today.



use ReportServer
go
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
--------------------------------------------------------
DECLARE @RunAllReport CURSOR
SET @RunAllReport = CURSOR FAST_FORWARD
FOR
SELECT 
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus

FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON CAT.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON CAT.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
WHERE
sub.LastRunTime > GETDATE() - 1 and  Cat.Name LIKE 'reportname%' 
>convert(date,GETDATE()-2)
ORDER BY U.UserName, RptName 
OPEN @RunAllReport
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
END
CLOSE @RunAllReport
DEALLOCATE @RunAllReport 

Two ways to reset the identity of a table in sql server.

I know of the following two ways to reset the identity of a table in sql server. You often tend to use dummy data during the course of y...