Tuesday, June 21, 2016

SQL Server Intigration Services Connectivity Issue

I am trying to connect SSIS 2012 from management studio on my local system.

But I am receiving below error while connecting

TITLE: Connect to Server
------------------------------

Cannot connect to D-113069721.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


For help, click: http://go.microsoft.com/fwlink/?LinkId=220763

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


------------------------------
BUTTONS:

OK
------------------------------


I followed below method to over come this access issue:

Below is the source URL:

https://msdn.microsoft.com/en-us/library/hh213130.aspx

  1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
  2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
  3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  4. On the Security tab, click Edit in the Launch and Activation Permissions area.
  5. Add users and assign appropriate permissions, and then click Ok.
  6. Repeat steps 4 - 5 for Access Permissions.
  7. Restart SQL Server Management Studio.
  8. Restart the Integration Services Service.

    Same process has to be followed all the options in security tab(Access Permissions and Configuration Permissions) .

    This resolved my connectivity issue.

Saturday, June 11, 2016

Shrink all databases log file at a time

drop table #f
go
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
go
exec sp_MSforeachdb 'declare @s varchar(MAX); use [?];
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
--select * from #f

 select @s = ''use [?]; DBCC SHRINKFILE ('' + name +'',0,truncateonly )'' from #f where usage = ''log only''
print @s'