SQL Server SQL script to create operator

USE [msdb]

GO
EXEC msdb.dbo.sp_add_operator @name=N’OperatorName’,

@enabled=1,

@weekday_pager_start_time=90000,

@weekday_pager_end_time=180000,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=0,

@email_address=N’email1@abc.com; email2@abc.com; email3@abc.com; email4@abc.com; email5@abc.com;’,

@category_name=N'[Uncategorized]’

GO

Advertisements

SQL Server SQL To Create Proxy

USE [msdb]

GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N’Proxy Name’,@credential_name=N’Cred Name’,  @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=3

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=9

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=10

GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Proxy Name’, @subsystem_id=11

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 1′

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 2′

GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Proxy Name’, @login_name=N’Login name 3′

GO

SQL Server linked server to CSV file gives error The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) due to lack of EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER

We had created the linked server on SQL server to consume CSV files.

You can get details in this post.

The user could consume the files using query:

select * from [TEST-CSV]…[FileName#csv]

Though in object explorer of SQl management studio when try try to browse they use to get error:

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

The solution was to grant EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER using:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [DomainName\UserName];

or

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [LocalUserName];

On SQL Server to create Linked server to CSV file and query to use

For creating linked server to CSV file on a sql server use following syntax:

EXEC master.dbo.sp_addlinkedserver @server = N’TEST-CSV’, @srvproduct=N’CSVFLATFILE’, @provider=N’Microsoft.ACE.OLEDB.12.0′, @datasrc=N’\\FileServer\UNCPath\’, @provstr=N’Text;HDR=Yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST-CSV’,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation compatible’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’data access’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’dist’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’pub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc out’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’sub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’connect timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation name’, @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’query timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’use remote collation’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO

And to query use 3 periods/dots as follows:

select * from [TEST-CSV]…[FileName#csv]

SQL Script to get list of linked server on SQL Server

Reference: This is awesome script to get list of linked server on SQL server.

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

SQL Server profile name is not valid [SQLSTATE 42000] Error 14607

Created first account and then profile for SQL Server Emailing.

I opened SQL management Studio and ran the test script:

EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com’, @profile_name = ‘ProfilenameABC’,    @subject = ‘test email ‘,    @body = ‘test email’,    @body_format = ‘HTML’ ;

It worked fine.

But the developer from my team came back saying the job has failed with error:

profile name is not valid [SQLSTATE 42000] Error 14607.

He too is getting the same error.

So then we realized we have to change it security and make it Public.

So in SQL management Studio, navigate to Database mail => Configure Database mail, and select option=> Manage Profile Security.

Making it public profile allows guest access too.