MS SQL Server Express (connect local via dot)

Для подключения через SSMS к дефолтному инстансу по . (точке)

при установке MS SQL Server Express edition
необходимо указать Named instance: MSSQLSERVER

MS SQL Maintance plan export

Если SSIS не установлен, то можно вытащить XML с помощью показанного ниже сценаоия и сохранить каждый пакет в тестовом файле с расширением ".dtsx"

SELECT 
     name
    ,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)

FROM msdb.dbo.sysssispackages

WHERE name NOT IN 
( 
       'SqlTraceCollect','SqlTraceUpload',
       'TSQLQueryCollect','TSQLQueryUpload',
       'PerfCountersCollect','PerfCountersUpload',
       'QueryActivityCollect','QueryActivityUpload'
)

FROM

Настройка Database Mail

Открываем конфигуратор Database Mail

Next

 

Next

Yes

Заполняем поля (приблизительно так)

Настраиваем профиль для отправки

Next

Default Profile - Yes

Next

Finish

The type of column Weight conflicts with the type of other columns specified in the UNPIVOT list.

DECLARE @PID INT = 777
SELECT
 [Параметр],
 [Значение]
FROM
(
 SELECT 
  Name,
  CAST(Size AS NVARCHAR(50)) AS Size,
  CAST(Weight AS NVARCHAR(50)) AS Weight,
  CAST(Color AS NVARCHAR(50)) AS Color
 FROM AdventureWorks2008R2.Production.Product
 WHERE ProductID = @PID
) AS Src

UNPIVOT
(
 [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
) AS UnPvt

Возвращает:

Msg 8167, Level 16, State 1, Line 19
The type of column "Weight" conflicts with the type of other columns specified in the UNPIVOT list.

SOLUTION:

Получаем Collation для данных столбцов таблицы

USE master;
SELECT DB_NAME() AS Database_Name,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DB_Collation,
       SQL_VARIANT_PROPERTY(Name, 'Collation') AS Name_Collation,
       SQL_VARIANT_PROPERTY(CAST(Size AS NVARCHAR(50)), 'Collation') AS Size_Collation,
       SQL_VARIANT_PROPERTY(CAST(Weight AS NVARCHAR(50)), 'Collation') AS Weight_Collation,
       SQL_VARIANT_PROPERTY(CAST(Color AS NVARCHAR(50)), 'Collation') AS Color_Collation
FROM AdventureWorks2008R2.Production.Product
WHERE ProductID = 777;

Видим:

Database_Name  DB_Collation     Name_Collation                Size_Collation                Weight_Collation  Color_Collation
-------------- ---------------- ----------------------------- ----------------------------- ----------------- -----------------------------
master         Ukrainian_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  Ukrainian_CI_AS   SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

При этом:

USE AdventureWorks2008R2;
SELECT DB_NAME() AS Database_Name,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DB_Collation,
       SQL_VARIANT_PROPERTY(Name, 'Collation') AS Name_Collation,
       SQL_VARIANT_PROPERTY(CAST(Size AS NVARCHAR(50)), 'Collation') AS Size_Collation,
       SQL_VARIANT_PROPERTY(CAST(Weight AS NVARCHAR(50)), 'Collation') AS Weight_Collation,
       SQL_VARIANT_PROPERTY(CAST(Color AS NVARCHAR(50)), 'Collation') AS Color_Collation
FROM AdventureWorks2008R2.Production.Product
WHERE ProductID = 777;

Видим:

Database_Name         DB_Collation                  Name_Collation                Size_Collation                Weight_Collation              Color_Collation
--------------------- ----------------------------- ----------------------------- ----------------------------- ----------------------------- ------------------------------
AdventureWorks2008R2  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

• Если выполняем запрос с уровня master, то указываем COLLATE

  DECLARE @PID INT = 777
  SELECT
   [Параметр],
   [Значение]
  FROM
  (
   SELECT 
    Name,
    CAST(Size AS NVARCHAR(50)) AS Size,
    CAST(Weight AS NVARCHAR(50)) COLLATE SQL_Latin1_General_CP1_CI_AS AS Weight,
    CAST(Color AS NVARCHAR(50)) AS Color
   FROM AdventureWorks2008R2.Production.Product
   WHERE ProductID = @PID
  ) AS Src
  
  UNPIVOT
  (
   [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
  ) AS UnPvt

  Вывод:

  Параметр  Значение
  --------- -----------------------
  Name      Mountain-100 Black, 44
  Size      44
  Weight    21.13
  Color     Black
  
  (4 row(s) affected)

• Если выполним с уровня AdventureWorks2008R2, то проблема уйдет

  USE AdventureWorks2008R2 
  DECLARE @PID INT = 777
  SELECT
   [Параметр],
   [Значение]
  FROM
  (
   --DECLARE @PID INT = 777
   SELECT 
    Name,
    CAST(Size AS NVARCHAR(50)) AS Size,
    CAST(Weight AS NVARCHAR(50)) AS Weight,
    CAST(Color AS NVARCHAR(50)) AS Color
   FROM AdventureWorks2008R2.Production.Product
   WHERE ProductID = @PID
  ) AS Src
  
  UNPIVOT
  (
   [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
  ) AS UnPvt

  Вывод тотже

  Параметр  Значение
  --------- -----------------------
  Name      Mountain-100 Black, 44
  Size      44
  Weight    21.13
  Color     Black
  
  (4 row(s) affected)

FROM

SELECT * FROM full_server.db.table

SELECT 
    * 
FROM SQL-PC.AdventureWorks2008R2.Sales.Currency

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '-'.

SELECT @@SERVERNAME

------------------------------------------------
SQL-PC

(1 row(s) affected)


SOLUTION

SELECT 
    * 
FROM [SQL-PC].AdventureWorks2008R2.Sales.Currency

How to set memory limit SQL

How to: Set a Fixed Amount of Memory (SQL Server Management Studio)

To set a fixed amount of memory
In Object Explorer, right-click a server and select Properties.
Click the Memory node.
Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB). The minimum amount of memory you can specify for max server memory is 16 MB.

FROM

MS SQL: How to attach db

USE [master]
GO
CREATE DATABASE [DBname] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\DBname.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\DBname.ldf' )
FOR ATTACH ;
GO