Category: General

Microsoft R – SQL 2016 – Notes

“R is a free software environment for statistical computing and graphics” – source click here.

Useful links:

 

Quick notes:

  • You need Windows Server 2012 R2
  • JDK needs to be installed before running the SQL 2016 setup program
  • Enable TCP/IP and restart your SQL 2016 instance.
  • Connect to the instance.
  • Run:Exec sp_configure ‘external scripts enabled’, 1;reconfigure;
  • Restart all services, SDE, SSA, Polybase, Launchpad service.
  • Note. If you don’t restart the Launchpad service, you will get below error message.
  • Msg 39011, Level 16, State 1, Line 8
    SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.
    Msg 11536, Level 16, State 1, Line 8
    EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

r1 f2

SQL Server Fixed Server Role Permissions

SQL Server Fixed Server Role Permissions – run – sp_srvrolepermission

ServerRole Permission

bulkadmin Add member to bulkadmin

bulkadmin BULK INSERT

dbcreator Add member to dbcreator

dbcreator ALTER DATABASE

dbcreator CREATE DATABASE

dbcreator DROP DATABASE

dbcreator Extend database

dbcreator RESTORE DATABASE

dbcreator RESTORE LOG

dbcreator sp_renamedb

diskadmin Add member to diskadmin

diskadmin DISK INIT

diskadmin sp_addumpdevice

diskadmin sp_diskdefault

diskadmin sp_dropdevice

processadmin Add member to processadmin

processadmin KILL

securityadmin Add member to securityadmin

securityadmin Grant/deny/revoke CREATE DATABASE

securityadmin Read the error log

securityadmin sp_addlinkedsrvlogin

securityadmin sp_addlogin

securityadmin sp_defaultdb

securityadmin sp_defaultlanguage

securityadmin sp_denylogin

securityadmin sp_droplinkedsrvlogin

securityadmin sp_droplogin

securityadmin sp_dropremotelogin

securityadmin sp_grantlogin

securityadmin sp_helplogins

securityadmin sp_password

securityadmin sp_remoteoption (update)

securityadmin sp_revokelogin

serveradmin Add member to serveradmin

serveradmin dbcc freeproccache

serveradmin RECONFIGURE

serveradmin SHUTDOWN

serveradmin sp_configure

serveradmin sp_fulltext_service

serveradmin sp_tableoption

setupadmin Add member to setupadmin

setupadmin Add/drop/configure linked servers

setupadmin Mark a stored procedure as startup

sysadmin Add extended procedures

sysadmin Add member to bulkadmin

sysadmin Add member to dbcreator

sysadmin Add member to diskadmin

sysadmin Add member to processadmin

sysadmin Add member to securityadmin

sysadmin Add member to serveradmin

sysadmin Add member to setupadmin

sysadmin Add member to sysadmin

sysadmin Add/drop to/from db_accessadmin

sysadmin Add/drop to/from db_backupoperator

sysadmin Add/drop to/from db_datareader

sysadmin Add/drop to/from db_datawriter

sysadmin Add/drop to/from db_ddladmin

sysadmin Add/drop to/from db_denydatareader

sysadmin Add/drop to/from db_denydatawriter

sysadmin Add/drop to/from db_owner

sysadmin Add/drop to/from db_securityadmin

sysadmin Add/drop/configure linked servers

sysadmin All DDL but GRANT, REVOKE, DENY

sysadmin ALTER DATABASE

sysadmin BACKUP DATABASE

sysadmin BACKUP LOG

sysadmin BULK INSERT

sysadmin CHECKPOINT

sysadmin Complete SETUSER SQL user

sysadmin Constraints on System tables

sysadmin CREATE DATABASE

sysadmin Create indices on system tables

sysadmin Create/delete/modify system tables

sysadmin dbcc change ‘on’ rules

sysadmin dbcc checkalloc

sysadmin dbcc checkdb

sysadmin dbcc checkfilegroup

sysadmin dbcc checkident

sysadmin dbcc checktable

sysadmin dbcc cleantable

sysadmin dbcc dbreindex

sysadmin dbcc dropcleanbuffers

sysadmin dbcc freeproccache

sysadmin dbcc inputbuffer

sysadmin dbcc outputbuffer

sysadmin dbcc pintable

sysadmin dbcc proccache

sysadmin dbcc setcpuweight

sysadmin dbcc setioweight

sysadmin dbcc show_statistics

sysadmin dbcc showcontig

sysadmin dbcc showoptweights

sysadmin DBCC ShrinkDatabase

sysadmin dbcc shrinkfile

sysadmin dbcc traceon/off

sysadmin dbcc updateusage

sysadmin DELETE permission on any object

sysadmin DENY

sysadmin DISK INIT

sysadmin DROP DATABASE

sysadmin EXECUTE any procedure

sysadmin Extend database

sysadmin GRANT

sysadmin Grant/deny/revoke CREATE DATABASE

sysadmin INSERT permission on any object

sysadmin KILL

sysadmin Mark a stored procedure as startup

sysadmin Raiserror With Log

sysadmin Read the error log

sysadmin RECONFIGURE

sysadmin REFERENCES permission on any table

sysadmin RESTORE DATABASE

sysadmin RESTORE LOG

sysadmin REVOKE

sysadmin SELECT permission on any object

sysadmin SHUTDOWN

sysadmin sp_addapprole

sysadmin sp_addlinkedsrvlogin

sysadmin sp_addlogin

sysadmin sp_addrole

sysadmin sp_addrolemember

sysadmin sp_addumpdevice

sysadmin sp_adduser

sysadmin sp_altermessage

sysadmin sp_approlepassword

sysadmin sp_change_users_login

sysadmin sp_changedbowner

sysadmin sp_changeobjectowner

sysadmin sp_configure

sysadmin sp_dbcmptlevel

sysadmin sp_defaultdb

sysadmin sp_defaultlanguage

sysadmin sp_denylogin

sysadmin sp_diskdefault

sysadmin sp_dropapprole

sysadmin sp_dropdevice

sysadmin sp_droplinkedsrvlogin

sysadmin sp_droplogin

sysadmin sp_dropremotelogin

sysadmin sp_droprole

sysadmin sp_droprolemember

sysadmin sp_dropuser

sysadmin sp_fulltext_catalog

sysadmin sp_fulltext_column

sysadmin sp_fulltext_database

sysadmin sp_fulltext_service

sysadmin sp_fulltext_table

sysadmin sp_grantdbaccess

sysadmin sp_grantlogin

sysadmin sp_helplogins

sysadmin sp_password

sysadmin sp_recompile

sysadmin sp_refreshview

sysadmin sp_remoteoption

sysadmin sp_remoteoption (update)

sysadmin sp_rename

sysadmin sp_renamedb

sysadmin sp_revokedbaccess

sysadmin sp_revokelogin

sysadmin sp_tableoption

sysadmin sp_updatestats

sysadmin TRUNCATE TABLE

sysadmin UPDATE permission on any object

sysadmin USE to a suspect database

 

SQL Server Replication Options

Common configuration notes/options.

==============================

Snapshot publication: The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

Transactional publication: The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Transactional publication with updatable subscriptions: The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Merge publication: The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications

=============================

All merge articles must contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server adds a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.

Adding a new column will:      » Cause INSERT statements without column lists to fail      » Increase the size of the table      » Increase the time required to generate the first snapshot

SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.

============================================

Below shows one of the configuration wizard screens during setup of SQL Server Replication from SSMS SQL Server 2008 R2.

You have the option of configuring replication to support SQL 2008 and/or lower SQL server versions.

sql_replication

 

 

 

 

SQL Server Reporting Services [SSRS] 2008 Problem: Maximum request length exceeded

SQL Server Reporting Services [SSRS] 2008 Problem: Maximum request length exceeded

Error l get:

Error  : There was an exception running the extensions specified in the config file. —> Maximum request length exceeded.

Click on image below for larger version.

Solution:

Edit the file web.config, found in:

C$Program FilesMicrosoft SQL ServerMSRS10_50.RPTReporting ServicesReportServer

Change line:

<httpRuntime executionTimeout=”9000″ />

to:

<httpRuntime executionTimeout = “9000” maxRequestLength=”500000″ />

And then restart the SQL Server Reporting Services [SSRS] service.

This should fix the issue.