SQL Server Operators – SQL Agent Alert System – Notify Operators

After adding SQL Operators you need to restart SQL Agent

ERROR in SQL Agent Jobs
… NOTE: Failed to notify ‘<operator name>’ via email.

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to
[475] Database Mail is not enabled for agent notifications.

May have log in SSMS > SQL Server Agent > Error Logs

SOLVED
Credit to:
http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

This was set up on one of my work servers. For a different company this needed to be set up.

On SQL 2005+ you have to tell SQL Server Agent / Properties / Alert System, which profile that you’d like to enable. Here’s what it does.
e.g. and don’t forget to restart SQL Agent

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.
  2. Right-click SQL Server Agent, and then click Properties.
  3. Click Alert System.
  4. Select Enable Mail Profile.
  5. In the Mail system list, choose Database Mail.
  6. In the Mail profile list, select a mail profile for Database Mail.

Or less likely Transact SQL

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'MyDatabaseProfile'
GO

SQL Server Allow Remote Connections – Including Blocked by McAffee

A lot of websites have the basics of this. Applied steps and still did not work. Steps were

  1. Allow remote connections
  2. Start SQL Browser
  3. Ensure that Protocol for TCP/IP was enabled
  4. For new PC only adjustment to Firewall was addition of inbound port rule. Had to include Private network.

SOLVED However removed McAffee from the server (laptop) and remote connection worked immediately. Think this was the deciding ultimate solving step. Other Links http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx http://support.microsoft.com/kb/914277 http://www.dummies.com/how-to/content/how-to-open-a-port-in-the-windows-7-firewall.html http://www.sevenforums.com/system-security/58817-remote-access-sql-server-express-2008-windows-7-a.html

For remote connection over the internet also need to use port forwarding on the router.

Microsoft Visual Studio LightSwitch – Installation

There is a lot of web talk on problems with existing SQL installations before installing LightSwitch

  1. LightSwitch 2011 requires SQL Server Express to be installed. It is looking for SQLEXPRES instance.
  2. If you already have SQL Developer edition you may have a problem. I tried to trick it by adding an instance to SQL Developer named SQLEXPRESS. This got past the LightSwitch install.
  3. However still could not use the program because it also requires User Instances, which are not possible in Developer. So Removed my SQLEXPRESS instance. To do this > Programs and Features > Microsoft SQL Server 2008 R2 (64-bit) then Remove. Don’t panic it asks if you want to uninstall an instance, so you can leave previous instances on there.
  4. Then installed SQLExpress side-by-side with my developer edition. All OK
  5. LightSwitch 2011 good to go!

SQL Linked server – Change of password

TODO confirm this works then mark as Solved

A linked server using VFPOLEDB has worked for sometime here. Then the server related user password was changed and it all stopped working. Perhaps consider following page

http://msdn.microsoft.com/en-us/library/ms175537.aspx

“Linked server login mappings can be added by using sp_addlinkedsrvlogin and removed by using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server.”

How to remove serer name items from history of sql server management studio

 

http://stackoverflow.com/questions/1059105/how-to-remove-server-name-items-from-history-of-sql-server-management-studio

For SQL 2005, delete the file:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

For SQL 2008, the file location, format and name changed:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

How to clear the list:

  1. Shut down all instances of SSMS
  2. Delete/Rename the file
  3. Open SSMS

This request is registered on Microsoft Connect

https://connect.microsoft.com/SQLServer/feedback/details/424800/ssms-expose-connect-to-server-mru-list-to-users

 

Connection to SQL Server over VPN

 

Get VPN connection IP address from IT server administrator
Set up VPN and login
Connect to SQL on local PC using SSMS or Visual Studio using SQL Authentication login and password
The server name to use in SSMS will be an IP address on the Network that you have used VPN to get to. You may or may not need to add \InstanceName.
It is not uncommon for this internal IP address system to be 10.0.0.x where x is the computer number
So format may be 10.0.0.x or 10.0.0.x\InstanceName

Special Cases
If the VPN internal IP address system uses 192.168.1.x then be careful that your local PC is not using 192.168.1.x
If it is then need to change the local PC IP address system to use 192.168.0.x

See Change IP address of a local (home) pc

ODBC connection to SQL Server 2008 R2

On ODBC  using SQL Native Client.
Many possible problems. But consider trying with Server Firewall off. If this is successful then consider the following changes on firewall which have worked for me.

Firewall settings on server are:

http://technet.microsoft.com/en-us/library/ms175043.aspx

  • On the Start menu, click Run, type firewall.cpl and then click OK.
  • In the Windows Firewall dialog box, click the Exceptions tab, and then click Add Port.
  • In the Add a Port dialog box, in the Name text box, type SQL Server <instance name>.
  • In the Port number text box, type the port number of the instance of the Database Engine, such as 1433 for the default instance.
  • Verify that TCP is selected, and then click OK.
  • To open the port to expose the SQL Server Browser service, click Add Port, type SQL Server Browser in the Name text box, type 1434 in the Port Number text box, select UDP, and then click OK.
    NoteNote
    To allow named pipes access through the firewall, you must also enable File and Printer Sharing through the firewall.
  • —————

    Also Firewall on server must add program SQLServer.exe which is at <program files><Microsoft SQL Server><MSSQL10_50.Instance Name\MSSQL\Binn\sqlservr.exe

    ————-

    Prefer not using older ODBC SQL server, but if do then on client configuration dynamically chosen ports is relevant

    Installing SQL Server 2008R2 on SBS 2008 Standard

    SBS Server Standard 2008 has SQL Server Management Studio Express tools on it.
    When installing SQL Server 2008 R2 Standard one of the checks was for the above and would not let you pass until this was removed. My approach was to uninstall SSMS Express. Checks then passed.

    As SBS is using SBSMonitoring as a database, websites suggest installing SQL Server as a new instance. I installed it as SQL2008 instance. SUCCESS.

    SQL Server Replication

    PUBLISHER
    SQL Server Agent account must be running on publisher
    Snapshot folder must be a UNC folder which is shared

    SUBSCRIBER
    Agent process account could be Subscriber Computer or Domain name \ User Name
    Distributer connection and Publisher connection I used a SQL Authentication account which will already work for normal login

    ERROR: On Subsciber
    Msg 14058, Level 16, State 1, Procedure sp_addmergesubscription, Line 381
    Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

    SOLUTION: On Publisher
    SELECT * FROM sysmergesubscriptions
    DELETE FROM sysmergesubscriptions WHERE subscriber_server = ‘ServerName’

    ERROR: On Publisher
    Unable to start execution of step 2 (reason: Error authenticating proxy ComputerName\UserName, system error: Logon failure: unknown user name or bad password.).  The step failed.

    SOLUTION: On Publisher
    Agent Security use a windows account. Password is the windows password for that account.
    This account must have at least db_owner for the database being published.
    It must also have write permissions on the publication folder.
    http://msdn.microsoft.com/en-us/library/ms186515(v=SQL.105).aspx
    STATUS: Should now have files on Publisher in Snapshot folder

    ERROR: On Subsriber
    the schema script ‘….sch’ could not be propagated to the subscriber
    Go to subscriber view job history. Message in there may be above plus
    ‘The process could not read file ‘…\MSSQL\ReplData\unc\…’ due to OS error 3.

    SOLUTION: On Publisher
    Use a UNC path on the publication. To do this > Publisher > Publication > Right click properties > Snapshot > Put files in the following folder
    Then use \\ server or computer name \ .. \ MSSQL \ReplData or which ever folder
    Should now work

    Build server automated tests fail SQL authentication

    If unit tests are testing against the database using Integrated Security then when these automated tests are put onto the build machine authentication may fail.
    SOLUTION. Create a SQL login for a machine which is possible.
    CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS;
    http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/315b5780-c131-4867-88b9-ebac82e68d77