Can't connect to Integration Services: "Access is denied." Only administrators have access to the Integration Services service

Try to connect to Integration Services:

Error message:

TITLE: Connect to Server

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

Cannot connect to ServerName.

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

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 " ServerName" 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.

Solution:

First of all check that SQL Server Integration Services is running on the server.

Second, ensure that the user is an administrator on the server.

After those 2 checks, the user should connect to Integration Services locally on the server.

In order to let the user connect from other computers, connect to the server (remote control) and:

Go to  Component services (under Administrative tools) --> Computers --> My Computer --> Dcom config

(you should be a local administrator in order to be able to open it).

Find "Microsoft SQL Server Integration Services (version #) --> right click, properties --> Security tab:

Press each one of the "Edit" buttons, add the user-group and let it local and remote permissions.

Then restart SSIS service.

https://msdn.microsoft.com/en-us/library/hh213130(SQL.110).aspx

If still not, check that the user/user group is assigned to the "Distributed Com Users" group:

Of course – restart SSIS services.

Now it really should work!

Export and Import / move a maintenance plan from one server to other

The maintenance plan stored in the sql server database engine menu under Management --> maintenance plan:

In order to move it to other server, we should do few steps:

1. Connect to the Integration Services of the server where the maintenance plan:

2. Right click on the maintenance plan --> Export Package:

3. Fill the package path (where the dtsx file will be saved) and press OK:

4. Connect to the Integration Services of the server where the maintenance plan will be imported to,

As before – with the same ordering – Right click on the maintenance plan --> Import Package

5. Add the package (press on the "…" button near the pacage path:

That it!

Connect to the database engine and make sure that you see the maintenance plan also there.

Note: if the maintanance plan run from a job – the job should be imported also (of course...).

SQL database groth by backupset sizes

What we want?

To get the databases groth.

We don't have a DMV that save the databases size history, so we can't know it exactly.

We can create a process that get in each day/week/... the current size and save it to a table that we'll create, but if we don't have such a process and we want to know how our databases are encreased, it won't help us.

So, what we can do?

Get the database groth by backupset sizes, it's not exact numbers, but it can help us to analize the databases groth.

; with DbSizeLog as

(

       SELECT

              [database_name] as DatabaseName,

              DATEPART(month,[backup_start_date]) as BackupMonth,

              DATEPART(year,[backup_start_date]) as BackupYear,

              AVG([backup_size]/1024/1024) as BackupSizeMB,

              LAG (AVG([backup_size]/1024/1024), 1, 0)

                     OVER (PARTITION BY [database_name] ORDER BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(month,[backup_start_date]))

                     AS PrevBackupSizeMB

       FROM msdb.dbo.backupset

       WHERE  [type] = 'D'

       --AND         [database_name] = 'UniformStructure_Employers'

       GROUP BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(mm,[backup_start_date])

       --ORDER BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(mm,[backup_start_date])

)

SELECT DatabaseName, BackupYear, BackupMonth,

              cast(BackupSizeMB as decimal(20,2)) as BackupSizeMB,

              cast(PrevBackupSizeMB as decimal(20,2)) as PrevBackupSizeMB,

              cast(((case PrevBackupSizeMB when 0.0 then NULL else (BackupSizeMB - PrevBackupSizeMB) end)) as decimal(20,2)) as GrothMB,

              cast((((case PrevBackupSizeMB when 0.0 then NULL else (BackupSizeMB/PrevBackupSizeMB) end) -1) *100) as decimal(5,2)) as GrothPrecentage

FROM   DbSizeLog

--WHERE BackupYear = 2017

--AND         DatabaseName IN ('DatabaseName', 'AnotherDatabaseName')

ORDER BY DatabaseName, BackupYear, BackupMonth

reate a Database Mail account and profile

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'account_name',

    @description = '',

    @email_address = 'mail@mail.com',

    @replyto_address = 'mail@mail.com',

    @display_name = 'Display_Name',

    @mailserver_name = '10.200.10.10';

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'MyProfile',

    @description = 'Profile for bla bla bla';

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'MyProfile',

    @account_name = 'account_name',

    @sequence_number = 1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'MyProfile',

    @principal_name = 'guest',

    @is_default = 0; -- or 1 

Get email profiles accounts in the database

EXEC msdb.dbo.sysmail_help_configure_sp;

EXEC msdb.dbo.sysmail_help_account_sp;

EXEC msdb.dbo.sysmail_help_profile_sp;

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

Get current database backups

SELECT r.session_id as SPID,

       r.command,

       a.text AS Query,

       r.start_time,

       r.percent_complete,

       dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time

FROM          sys.dm_exec_requests r

CROSS APPLY   sys.dm_exec_sql_text(r.sql_handle) a

WHERE r.command = 'BACKUP DATABASE'

The query processor ran out of internal resources and could not produce a query plan

SELECT ........

FROM .....

JOIN .....

JOIN .....

JOIN .....

JOIN .....

JOIN .....

WHERE .....

..... (GROUP, HAVING, ETC.)

AND t.SomeColumn IN ( many thousands of values separated by commas )

Error message:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query.

If you believe you have received this message in error, contact Customer Support Services for more information.

Cause and solution:

the cause and the solution is written in msdn:

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632.

To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

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

So, bottom line:

1. create table #ATmpTable (SomeColumn nvarchar(16) )

2. insert into #ATmpTable (SomeColumn ) values ('204025191')

                * many thousands of values insertion...

3. Select with join to the temp table:

SELECT ........

FROM .....

JOIN .....

JOIN .....

JOIN #ATmpTable tmp on t.SomeColumn = tmp.SomeColumn

JOIN .....

JOIN .....

JOIN .....

WHERE .....

..... (GROUP, HAVING, ETC.)

The name is not a valid identifier (dynamic SQL)

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'

CREATE TABLE #TempTableDynamicSQLIn

(

       ID INT NOT NULL,

       SomeString NVARCHAR(50) NULL

)

INSERT INTO #TempTableDynamicSQLIn

       (      ID, SomeString)

       VALUES

       (      1, ''Some String'')

SELECT * FROM #TempTableDynamicSQLIn

';

EXECUTE sp_executesql @SQL;

EXEC @SQL;

Error message:

Msg 203, Level 16, State 2, Line 16

The name '

CREATE TABLE #TempTableDynamicSQLIn

(

       ID INT NOT NULL,

       SomeString NVARCHAR(50) NULL

)

INSERT INTO #TempTableDynamicSQLIn

       (      ID, SomeString)

       VALUES

       (      1, 'Some String')

SELECT * FROM #TempTableDynamicSQLIn

' is not a valid identifier.

Solution:

EXECUTE sp_executesql @SQL;

Temp tables created in EXEC can't use temp table caching mechanism.

Outer 2 statuses

The case:

2 tables, with a FK from one to a statuses log table.

We need to select for each row in the first table the last status with it's datetime and the description of it, and also - in the same row - the datetime of the last specific stauts (in the example - 303).

there is no one absolut answer, it depend on the data in the tables and exists indexes, constraints, etc om the table.

-- create the Demo tables and data:

CREATE TABLE TableA (ID int identity(1,1) NOT NULL, SomeFkID int)

CREATE TABLE TableFk (SomeFkID int, SomeFkStatus int, SomeFkDatetime datetime, SomeFkDesc nvarchar(50))

INSERT INTO TableA (SomeFkID) 

VALUES (1), (2), (3), (4)

INSERT INTO TableFk (SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc)

VALUES 

(1, 1, '2017-01-01', N'FK 1 1 1'),

(2, 1, '2017-01-02', N'FK 2 1 1'), (2, 303, '2017-01-03', N'FK 2 303 2'), (2, 3, '2017-01-04', N'FK 2 3 3'), 

(2, 303, '2017-01-05', N'FK 2 303 4'), (2, 4, '2017-01-06', N'FK 2 4 5'),

(3, 1, '2017-01-01', N'FK 3 1 1'), (3, 2, '2017-01-03', N'FK 3 21'),

(4, 1, '2017-01-05', N'FK 4 1 1'), (4, 303, '2017-01-08', N'FK 4 303 1')

SELECT * FROM TableA

SELECT * FROM TableFk

--SELECT SomeFkID, SomeFkStatus, max(SomeFkDatetime) as maxSomeFkDatetime

--FROM TableFk f

--GROUP BY SomeFkID, SomeFkStatus

--ORDER BY SomeFkID, SomeFkStatus

--SELECT ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,

-- ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,

-- SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

--FROM TableFk f

--ORDER BY SomeFkID, rn

-- option 1: CTE with 2 ROW_NUMBERs

; with rnFK as

(

SELECT ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,

ROW_NUMBER()  OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,

SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

FROM TableFk f

) 

SELECT a.ID, a.SomeFkID, 

f303.SomeFkDatetime AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

JOIN rnFK f ON a.SomeFkID = f.SomeFkID

AND f.rn = 1

LEFT JOIN rnFK f303 ON a.SomeFkID = f303.SomeFkID

AND f303.rn303 = 1

AND f303.SomeFkStatus = 303

-- option 2: two OUTER APPLYs - one for the last status and one for 303 status

SELECT a.ID, a.SomeFkID, 

f303.SomeFkDatetime AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

order by SomeFkDatetime desc

) f303

-- option 3: one OUTER APPLY for the last status and Subquery (select top 1 order by) for the 303 status column

SELECT a.ID, a.SomeFkID, 

( select top 1 SomeFkDatetime

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

order by SomeFkDatetime desc

) AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

-- option 4: one OUTER APPLY for the last status and Subquery (max(SomeFkDatetime)) for the 303 status column

SELECT a.ID, a.SomeFkID, 

( select max(SomeFkDatetime)

from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303

) AS YatzranStatusDate, 

f.SomeFkDesc AS ErrorDesc,

f.SomeFkStatus as FileStatusID,

f.SomeFkDatetime

FROM TableA a

OUTER APPLY

( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc

from TableFk where SomeFkID = a.SomeFkID

order by SomeFkDatetime desc

) f

-- option 4: FIRST_VALUE and MAX functions:

SELECT DISTINCT

a.ID, a.SomeFkID, 

MAX (case when f.SomeFkStatus = 303 then f.SomeFkDatetime else NULL end) OVER (PARTITION BY f.SomeFkID) AS YatzranStatusDate,

FIRST_VALUE(f.SomeFkDesc) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) AS ErrorDesc,

FIRST_VALUE(f.SomeFkStatus) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as FileStatusID,

FIRST_VALUE(f.SomeFkDatetime) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as SomeFkDatetime

FROM TableA a

LEFT JOIN TableFk F ON A.SomeFkID  = f.SomeFkID

-- try it with and without :

CREATE NONCLUSTERED INDEX [IX_TableFk] ON TableFk

(

[SomeFkDatetime] DESC

) 

INCLUDE ([SomeFkID])

ON [PRIMARY]

DROP TABLE TableA 

DROP TABLE TableFk