Execute a stored procedure on SQLServer Startup

For a project we needed to run some T-SQL scripts when the SQL Express Database Engines on the client machines started (we need to have data available offline).
To get the job done,  SQL Server has the possibility to execute a stored procedure at it's startup.

For this you will need to execute:

1: sp_procoption  @ProcName  =  'stored procedure name',  

2:               @OptionName  =  'startup',  

3:               @OptionValue  = 'on'  
4: 

To turn it off again: 

1: sp_procoption  @ProcName  =  'stored procedure name',  

2:               @OptionName  =  'startup',  

3:               @OptionValue  = 'off'  
4: 

To see which procedures will be executed at startup:

1: SELECT  Name  

2: FROM  sysobjects   

3: WHERE  objectproperty(id, 'ExecIsStartup' )  =  1

4: 

Have fun
J.

SQL Server to Active Directory

A few days ago, I needed to access the Active Directory to get a list of users into a database. When I was looking out for a solution I stumbled upon the following solution, which is pretty simple to use in SQL Server:

1. Create a linked server

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI', @useself=N'True', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

2. Query your active directory


SELECT * FROM OPENQUERY (ADSI, 'SELECT userPrincipalName, displayName, distinguishedName,

sAMAccountName, mail, company, accountExpires, whenCreated

FROM ''LDAP:///OU=myou,OU=myou2,OU=myou3,DC=myDC,DC=myDC2''

WHERE objectCategory = ''Person'' AND objectClass = ''user''

AND NOT accountExpires=0

AND NOT company = ''a company name''

ORDER BY company')


 There is one drawback however, by default you can only get the first 1000 records from your active directory, but no worries, there is a workaraound! Follow this link to see the solution

Have fun!