SQL Server Agent Job Verbose Logging

Please see useful page:


  1. In Job Step – Advanced – Output file
  2. Likely strategy is that on first step do not tick “Append output to existing file”
  3. On subsequent steps tick this and then you will get the full output of the series




Arguments with regard to SpareField1 or specific schema

Because each new feature or property addition requires database changes there is the idea that instead you hold data in fields such as SpareText1, SpareInt1 etc. and this post discusses the relative merits of this and its alternatives. Another place this may be used is to create a common Lookup Field table.

It is my opinion that this should normally be avoided and instead specific schema is used.

Advantages of specific schema

  1. Data type integrity
  2. Required and non-required control by product (Null or Non-Null)
  3. Strongly typed code
  4. Reporting is easier
  5. Discoverability is easier – stay in context
  6. For lookup tables, you will gain referential integrity, and in any case it is possible and not too difficult to create abstract data update forms which may update any of these with data-driven addition of which tables to edit.

Disadvantages of specific schema

  1. You have to change the schema for each change
  2. If you have multiple client databases you may need to change several. If you have many this may be difficult

Mitigations to disadvantages of specific schema

  1. Learn and use simpler design techniques to speed up development. E.g. Entity Framework, code generation
  2. Write forms which may edit any tables. For instance for Lookup tables

Disadvantages of SpareField1

  1. You have to keep a record of what each Spare Field represents. Control and communication of that is difficult, control of a specific, e.g. Length field is not necessary because it says what it is
  2. That may get confusing, or there is a danger that Spare Field may be used in different ways by different people. So in the end it is more complex

Data type integrity

If you have a decimal with precision 4 and scale 2, i.e. 2 dp, max 99.99, then this controls the data. A SpareDecimal1 may have any data type definition, probably too large so as to cope with the general case, and as such bad data may appear, so for instance if you know length is always less than 100 units, then a data entry of 101 is bad data.

Also data type integrity would include Null control

SQL Server hangs on drop foreign key

This actually happened on Azure during part of a SSIS SQL Server integration package, removal of key before delete and upload of data

Following post was very useful


To find sessions then

SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

then Kill session id, but you cannot kill your own session, so open another session to do the kill


SQL Server – Take database offline hangs

With thanks to following and other web searches:



New SQL Server on a new Azure VM, with a few new issues to learn from. I attempted to take a database offline but it hung.


USE master

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    FROM sys.dm_tran_locks

--Find troublesome session and kill it.
--You cannot kill your own process, so you may need to start another session

To take the database offline without waiting try this:

alter database DOTNETNUKE_EBC set offline with rollback immediate

To bring database online:

alter database DOTNETNUKE_EBC set online




Use Microsoft.Data.DataConnectionDialog in your projects to retrieve connection string

This is the use of the Visual Studio connection dialog in your own projects, for instance tool kits to inspect databases. Steps are:

  1. NuGet
  2. Search and add Data Connection (Windows Forms)
  3. Add code, sample is in VB, but C# is similar


Imports Microsoft.Data.ConnectionUI

    Private Sub GetDataConnection()

        Dim dcd As New DataConnectionDialog
        dcd.SelectedDataSource = DataSource.SqlDataSource

        If DataConnectionDialog.Show(dcd) = Windows.Forms.DialogResult.OK Then
            Select Case dcd.SelectedDataProvider.Name
                Case DataProvider.SqlDataProvider.Name
                    Dim cn As New SqlClient.SqlConnection(dcd.ConnectionString)
                    DataSourceToolStripTextBox.Text = cn.DataSource
                    DatabaseDefaultToolStripTextBox.Text = cn.Database
                    'MessageBox.Show(String.Concat("Found SQL: ", cn.ConnectionString))

                Case Else
            End Select
        End If

    End Sub

See also

Azure Virtual Machines – with SQL Server

Setting up a VM for SQL Server Web Edition

  1. Created my first virtual machine with SQL Server Web edition on it.
  2. Added a windows user. Added the equivalent SQL Server windows user.
  3. Checked could login to SQL Server OK
  4. Used sysprep as a starting point
  5. Then took an image.
  6. Then created a new virtual machine from that image
  7. Logged in to new VM ok – but error on logging in to SQL Server.
  8. Then after a weekend the error had changed to “A connection was successfully established with the server, but then an error occurred during the login process (provider: Shared Memory Provider: 0 – No process is on the other end of the pipe)(Microsoft SQL Server, Error: 233)
  9. This was because SQL Server Agent was not running – started it
  10. In fact in SQL Server Configuration SQL Server Agent and Browser had to be changed to automatic startup
  11. Then back to original error

Error Login failed for user (Microsoft SQL Server Error 18456) on VM with SQL Server

  1. It seems that the user who created the SQL login on the first machine is not the same as the user who created the new machine after the sysprep
  2. So destroyed the machine and started again.
  3. New strategy – put SQL Server into Mixed Authentication Mode
  4. Create a SQL Server Login
  5. Give that login sysadmin
  6. Create the image.
  7. Create the VM from the image.
  8. Confirm that the Windows logins do not work for SQL Server
  9. Login as that SQL Server login admin created before, rather than the Windows Authentication only.
  10. Then script to drop and create the windows logins
  11. Issue resolved

Mixed Authentication Mode is required if logging into the SQL Server from another machine in any case.

So if creating a new VM from the image then start from step 7 above


Error: Login failed for remote SQL server



In the VM > Network and Sharing Center > View your active networks

The network had been set to private – reset to public and it now works again



How to setup a Virtual Machine for connection to SQL Server from another Machine



Problem with Microsoft Report on Terminal Server




Error setting up SQL Server CDC – Change Data Capture

Error when using:
EXECUTE sys.sp_cdc_enable_db

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database <dbname> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.


Database > Properties > Files >
Check that the databse has an owner

For me this went wrong because ‘dbo’ existed on the server (pc) where this database was created, but did not exist on this server (pc) (SQL Server Developer Edition)

SQL Linked Server Notes (including to an IP)

Create linked server when on the actual server, not as a user on another PC.
If there is a VPN connection required then the VPN connection needs to be up and running on the server, not the local PC.

USE [master]
EXEC master.dbo.sp_dropserver @server=N'', @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteServerLogin',@rmtpassword=''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin='Domain\login',@rmtuser=N'RemoteServerLogin',@rmtpassword=''


@locallogin=NULL means any local login
You cannot use Windows NT group logins, does not work. Individual window users only.

SQL Linked Server and SQL Server Agent

When using SQL Server Agent to do a job that has a view or other that connects to a remote server then you may get the following issues:

Error “Linked servers cannot be used under impersonation without a mapping for the impersonated login”

Solution 1:

  1. Create a login on the end server as:
    USE [master]
  2. Then give this login the permissions on the objects required.
  3. Back on the host right-click on the remote server > Properties > Security >
  4. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked on – No remote user or password
    Suggest use “Be made using the login’s current security context”

Solution 2:

  1. Create a new SQL server login on the end server and give it the required permissions
  2. Back on the host right-click on the remote server > Properties > Security >
  3. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked off – Remote user = your new user, Password = new password
    Suggest use “Be made using the login’s current security context”