Monthly Archives: February 2014

SQL Cannot resolve the collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation.

For example query

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode = l.PostalCode

results in error

Msg 468, Level 16, State 9, Line 6
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The solution is to use COLLATE in the line where it fails. In my case it was PostalCode field
Updated query:

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode COLLATE DATABASE_DEFAULT = l.PostalCode COLLATE DATABASE_DEFAULT

More complex example, where COLLATE had to be used not only in basic comparison

SELECT Name FROM dbo.DealerProfiles DP
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN 
(
	SELECT DealerName COLLATE DATABASE_DEFAULT
	FROM GEO.dbo.Dealers GD
	WHERE DP.Name COLLATE DATABASE_DEFAULT = gd.DealerName COLLATE DATABASE_DEFAULT
)

Enable remote connections in SQL Server 2008

Basically few things:

  • SSMS -> server properties -> Connections
    Allow remote connections to this server checked
  • SSMS -> server properties -> Security
    SQL Server and Windows Authentication mode checked
  • SQL Server Configuration Manager -> SQL Server Network Configuration TCP/IP enabled
  • TCP/IP -> Properties -> IP Addresses
    Every IP needs to have 1433 (default one) port set
  • sometimes Firewall needs extra inbound rule for 1433 port
    (Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Ruleā€¦ on the right hand side.)

Woulktrhough here

Executing PowerShell scripts via Task Scheduler

I used excellent script Delete files older than x-days – Cleanup Script which requires bunch of parameters.

Sample command:

.\deleteold.ps1 -FolderPath "D:\test" -FileAge 7 -LogFile "D:\log.txt" -autolog -VerboseLog -CleanFolders

Most important parameters:

  • FolderPath
    The path that will be recursively scanned for old files.
  • Fileage
    Filter for age of file, entered in days. Use -1 for all files to be removed.
  • AutoLog
    Automatically generates file name at path specified in -logfile. If a file name is specified in the LogFile parameter and the AutoLog parameter is used only the path specified in LogFile is used. The file name is created with the following naming convention:

    Autolog_<FolderPath><dd-MM-yyyy_HHmm.ss>.log
  • VerboseLog
    Logs all delete operations to log, default behavior of the script is to log failed only.
  • CleanFolders
    If this switch is specified any empty folder will be removed. Default behavior of this script is to only delete folders that contained old files.

Creating a task in Task Scheduler

GENERAL
Just give it a name
scheduler01

TRIGGERS
Use schedule when you want to run it
scheduler02

ACTIONS

  • Program/script:

    %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

    Check what PowerShell version you have and update the path

  • Arguments:
    -Command "& d:\scripts\deleteold.ps1 -FolderPath "D:\test" -FileAge 0 -LogFile "D:\log.txt" -autolog -VerboseLog -CleanFolders; exit $LASTEXITCODE"

    exit $LASTEXITCODE is used to get exit code from the script
    O learnt this from Dmitry’s

scheduler03
scheduler04