MSSQL - Database Growth - Crescita DB

---------------------------------------
-- PARAMETRI
---------------------------------------
DECLARE @DB NVARCHAR(100) = 'GVDOC'
---------------------------------------


-- https://peter-whyte.com/2018/05/get-database-growth-events-sql-server/

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace 
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
	AND property = 2;

-- rip apart file name into pieces 
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.', @filename);
SET @ec = CHARINDEX('_', @filename) + 1;
SET @efn = REVERSE(SUBSTRING(@filename, 1, @bc));
SET @bfn = REVERSE(SUBSTRING(@filename, @ec, LEN(@filename)));
-- set filename without rollover number 
SET @filename = @bfn + @efn


-- process all trace files 
;WITH BOSO AS
(
    SELECT ftg.StartTime
	    , te.name AS EventName
	    , DB_NAME(ftg.databaseid) AS DatabaseName
	    , ftg.Filename
	    , (ftg.IntegerData * 8) / 1024.0 AS GrowthMB
	    , (ftg.duration / 1000) AS DurMS
    FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg
    INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
    WHERE (
		    ftg.EventClass = 92 -- Date File Auto-grow     
		    OR ftg.EventClass = 93
		    ) -- Log File Auto-grow 
    AND DatabaseName = @DB

)
, TOTALI AS
(
    SELECT    X.DatabaseName
            , CONVERT(DATE, X.StartTime) AS GIORNO
            , COUNT(*) AS CONTA
            , SUM(X.GrowthMB) AS MB
            , SUM(X.DurMS) MSeconds
    FROM    BOSO X
    GROUP BY X.DatabaseName, CONVERT(DATE, X.StartTime) 
)
SELECT  *
FROM    TOTALI T
ORDER BY T.GIORNO

SQL Error - Cannot Generate SSPI context

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/cannot-generate-sspi-context-error

https://dba.stackexchange.com/questions/93389/error-cannot-generate-sspi-context#93465

https://github.com/microsoft/CSS_SQL_Networking_Tools

SQL - Shrink all user databases

USE MASTER
GO

CREATE OR ALTER PROCEDURE Boso_ShrinkAllUserDB_sp
(
    @DBName AS sysname = NULL
)
AS
BEGIN
    --##2023-15-24 - Boso -             @DBName
    SET NOCOUNT ON

    DECLARE @SQ NVARCHAR(MAX)

    DECLARE #CUR CURSOR FOR
    SELECT  CONCAT('; PRINT ''** ', NAME ,' '' ; DBCC SHRINKDATABASE(', QUOTENAME(NAME) ,')') AS COMANDO
    FROM    SYS.DATABASES
    WHERE   STATE = 0           --> ONLINE DB ONLY
        AND DATABASE_ID > 4     --> SKIP SYSTEM DBS
        AND (@DBName IS NULL OR name = @DBName)


    OPEN #CUR
    FETCH NEXT FROM #CUR INTO @SQ

    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC SP_EXECUTESQL @SQ

            FETCH NEXT FROM #CUR INTO @SQ
        END


    CLOSE #CUR
    DEALLOCATE #CUR

END
GO

MS SQL - Generate insert script for selected records

If possible use Visual Studio. The Microsoft SQL Server Data Tools (SSDT) bring a built in functionality for this since the March 2014 release:

1. Open Visual Studio
2. Open "View" → "SQL Server Object Explorer"
3. Add a connection to your Server
4. Expand the relevant database
5. Expand the "Tables" folder
6. Right click on relevant table
7. Select "View Data" from context menu
8. In the new window, viewing the data use the "Sort and filter dataset" functionality in the tool bar to apply your filter. Note that this functionality is limited and you can't write explicit SQL queries.
9. After you have applied your filter and see only the data you want, click on "Script" or "Script to file" in the tool bar
10. Voilà - Here you have your insert script for your filtered data

Notes:

1. Be careful, the "View Data" window is just like SSMS "Edit Top 200 Rows": you can edit data right away!
2. Remember to add SET DATEFORMAT YMD before the INSERT command.

Via https://stackoverflow.com/a/51186767/14507440

SQL Server: how to disable a Linked Server

DECLARE @Linked_Server_Name sysname = 'SRVZUC02\SQLEXPRESS'

-- check the existent linked servers
SELECT s.is_data_access_enabled, *
FROM SYS.SERVERS s
WHERE s.NAME = @Linked_Server_Name


-- Disable data access
EXEC sp_serveroption 
      @server   = @Linked_Server_Name
	, @optname  = 'data access'
	, @optvalue = 'FALSE'


-- check the existent linked servers
SELECT s.is_data_access_enabled, *
FROM SYS.SERVERS s
WHERE NAME = @Linked_Server_Name

Found here.

About procedure [sp_serveroption] on SQL Docs.

HOWTO configure Windows Firewall for SQL Server

New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

NOTE: Remeber to activate the SQLServer Browser service.

SQL: How to determine free space and file size for SQL Server databases

DECLARE @FileSize AS TABLE (
	dbName NVARCHAR(128)
	, FileName NVARCHAR(128)
	, type_desc NVARCHAR(128)
	, CurrentSizeMB DECIMAL(10, 2)
	, FreeSpaceMB DECIMAL(10, 2)
	);

INSERT INTO @FileSize (
	dbName
	, FileName
	, type_desc
	, CurrentSizeMB
	, FreeSpaceMB
	)
EXEC sp_msforeachdb 
	'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0                                                           AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0    AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);'
	;

SELECT dbName
	, FileName
	, type_desc
	, FORMAT(X.CurrentSizeMB, 'N0') AS CurrentSizeMB
	, FORMAT(X.FreeSpaceMB, 'N0') AS FreeSpaceMB
FROM @FileSize X
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
	AND FreeSpaceMB > 1000
ORDER BY FreeSpaceMB DESC

Found here.

SQL Server Database Growth and Autogrowth Settings

Identifying Databases that are using the Default Auto-growth Settings:

-- Drop temporary table if it exists
IF OBJECT_ID('tempdb..#info') IS NOT NULL
       DROP TABLE #info;
 
-- Create table to house database file information
CREATE TABLE #info (
     databasename VARCHAR(128)
     ,name VARCHAR(128)
    ,fileid INT
    ,filename VARCHAR(1000)
    ,filegroup VARCHAR(128)
    ,size VARCHAR(25)
    ,maxsize VARCHAR(25)
    ,growth VARCHAR(25)
    ,usage VARCHAR(25));
    
-- Get database file information for each database   
SET NOCOUNT ON; 
INSERT INTO #info
EXEC sp_MSforeachdb 'use ? 
select ''?'',name,  fileid, filename,
filegroup = filegroup_name(groupid),
''size'' = convert(nvarchar(15), convert (bigint, size) * 8) + N'' KB'',
''maxsize'' = (case maxsize when -1 then N''Unlimited''
else
convert(nvarchar(15), convert (bigint, maxsize) * 8) + N'' KB'' end),
''growth'' = (case status & 0x100000 when 0x100000 then
convert(nvarchar(15), growth) + N''%''
else
convert(nvarchar(15), convert (bigint, growth) * 8) + N'' KB'' end),
''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end)
from sysfiles
';
 
-- Identify database files that use default auto-grow properties
SELECT databasename AS [Database Name]
      ,name AS [Logical Name]
      ,filename AS [Physical File Name]
      ,growth AS [Auto-grow Setting] FROM #info 
WHERE (usage = 'data only' AND growth = '1024 KB') 
   OR (usage = 'log only' AND growth = '10%')
ORDER BY databasename
 
-- get rid of temp table 
DROP TABLE #info;

Identifying How Often an Auto-growth Event has Occurred:

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);
 
-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;
 
-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));
 
-- set filename without rollover number
SET @filename = @bfn + @efn
 
-- process all trace files
SELECT 
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB 
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

Via: https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/sql-server-database-growth-and-autogrowth-settings/

Poor Man's T-SQL Formatter

Hi guys again, I fixed the files to work with SMSS 19 and 20, also changed visualstudio shell to 14 so you don't need manually changes smss config file, Enjoy

https://geogensoft.com/PoorMansTSqlFormatterEditedbyGeoGenSoft.Setup.msi

Via: https://github.com/TaoK/PoorMansTSqlFormatter/issues/283#issuecomment-2393744502

Plugin for SQL Management Studio, Visual Studio, Visual Studio Code, ...

http://architectshack.com/PoorMansTSqlFormatter.ashx

---

If, after an SSMS update, you have this exception:

Could not load 'The 'FormatterPackage' package did not load correctly.' - cannot find the file Microsoft.VisualStudio.Shell.12.0

This is the solution:

The issue seems to be with a binding redirect missing from SSMS. If you edit ssms.exe.config (by default at C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE) and add the following line inside the assemblyBinding element the package will load:

There's one for shell 9.0, 10.0, and 14.0 just not one for 12.0. I took the above from the visual studio exe.config. This will get cleared and have to be re-done any time you update SSMS.

2023-11-15 update - If copy/pasting the above xml doesn't work:
copy the same block directly from the ssms.exe.config file, and change it to .Shell.12.0

---

If modifyng the .config file doesn't work, try replacing Poor Man's DLL with those recompiled ones.

---

SQL Management Studio 2019 Setup

1. Install the Extension in a previous version of SSMS (tested on v18 -- install it, if needed)
2. Copy all files from

   C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Extensions
   

   to

   C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions 

3. Add this in ssms.exe.config:

4. Uninstall Mangement Studio 18 (if installed only for this fix).

User gggirj on github made a working version for SMSS v19: https://simul-europe.com/PoorMansTSqlFormatterSSMSPackage.Setup.msi


Via GitHub and SSMS 19 and working version for SMSS v19

Generate rows with random data in SQL Server

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
 DROP TABLE #tmp


SELECT   TOP 1000 
    IDENTITY(INT, 1, 3) AS ID
  , RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate
  , ABS(CHECKSUM(NEWID())) AS randomBigInt
  , (ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt
  , RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec
  , RAND(CHECKSUM(NEWID())) AS randomTinyDec
  , RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec
  , CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #tmp
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3


SELECT *
FROM #tmp

Extracting a .NET Assembly from SQL Server 2005

-------------------------------------------------------------------------------
-- TURN Ole Automation Procedures ON
-------------------------------------------------------------------------------
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  


PRINT ''
PRINT ''



-------------------------------------------------------------------------------
-- PARAMTERS
-------------------------------------------------------------------------------
DECLARE @rootPath nvarchar(100); SET @rootPath = 'C:\MyAssembly\'




-------------------------------------------------------------------------------
-- EXTRACT ALL USER-DEFINED ASSEMBLIES
-------------------------------------------------------------------------------
DECLARE @name nvarchar(max)
DECLARE @obj varbinary(max)

DECLARE #cur CURSOR FOR 
--SELECT af.name, af.content 
SELECT   REPLACE(
     REPLACE(af.name, '\', '_')
     , '/', '_')
  , af.content 
FROM sys.assemblies a
  INNER JOIN sys.assembly_files af 
   ON a.assembly_id = af.assembly_id 
WHERE is_user_defined <> 0

OPEN #cur

FETCH NEXT FROM #cur INTO @name, @obj


DECLARE @ObjectToken INT
DECLARE @path nvarchar(max)

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @path = @rootPath + @name
  PRINT @name + ' --> ' +  @path


  -------------------------------------------------------------------------------
  -- WRITE FILE TO DISK
  -------------------------------------------------------------------------------
  EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
  EXEC sp_OASetProperty @ObjectToken, 'Type', 1
  EXEC sp_OAMethod @ObjectToken, 'Open'
  EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @obj
  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @path, 2
  EXEC sp_OAMethod @ObjectToken, 'Close'
  EXEC sp_OADestroy @ObjectToken


  FETCH NEXT FROM #cur INTO @name, @obj
 END


CLOSE #cur
DEALLOCATE #cur


PRINT ''
PRINT ''



-------------------------------------------------------------------------------
-- TURN Ole Automation Procedures OFF
-------------------------------------------------------------------------------
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Ole Automation Procedures', 0;  
GO  
RECONFIGURE;  
GO  

Testato su SQL 2005. Via.

Visual Studio - SQL Schema Comparison Error “Source/Target is unavailable”

1. Close all Visual Studio instances.
2. Delete the saved connection keys in
   

   HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio\14.0\ConnectionMruList
   

   (14.0 = Visual Studio 2017)
3. Reopen VS and try again.

Via.

Bulk Inserts via TSQL in SQL Server

Via


PDF

Pass A User-Defined Table to a Stored Procedure

/* Create a table type. */
CREATE TYPE MyTableType AS TABLE 
( Column1 VARCHAR(50)
, ........ );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. ValidateInputXML
    @TVP MyTableType READONLY
    AS 
     -- Do what ever you want to do with the table received from caller
    GO

/* Declare a variable that references the type. */
DECLARE @myTable AS MyTableType;

-- Fill @myTable with data and send it to SP. 
insert into @myTable SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';


/* Pass the table variable data to a stored procedure. */
EXEC ValidateInputXML @myTable ;
GO

https://stackoverflow.com/questions/30515297/pass-a-user-defined-table-to-a-stored-procedure

SQL - Find last SQL Server restart (SQL Server uptime)

To find the SQL Server uptime:

SELECT   crdate AS startup 
  , + CAST((DATEDIFF(hh, crdate, GETDATE())) / 24 AS varchar(3)) + ' days and '
    + CAST((DATEDIFF(hh, crdate, GETDATE())) % 24 AS varchar(2)) + ' hours' AS days
FROM master..sysdatabases
WHERE name = 'tempdb'

Returns:

startup                 days
----------------------- ---------------------
2018-02-04 15:51:37.653 50 days and 20 hours

Works on Sql Server 2005 onward.

Notifica Job Falliti / sys_NotifyFailedJob_sp

CREATE PROCEDURE [dbo].[sys_NotifyFailedJob_sp]
(
   @recipients AS nvarchar(MAX)
 , @subjectPrefix AS nvarchar(100) = NULL
)
AS
BEGIN
 --##2018-02-14 - Boso -        sysjobsteps  // @body
 SET NOCOUNT ON

 DECLARE @JobID AS uniqueidentifier
 DECLARE @JobName AS nvarchar(255)
 DECLARE @subject AS nvarchar(255)
 DECLARE @body AS nvarchar(MAX) = ''
  

 DECLARE #JOBS CURSOR FOR
 SELECT job_id AS j
 FROM msdb..sysjobs j
 WHERE j.enabled <> 0


 OPEN #JOBS

 FETCH NEXT 
 FROM #JOBS 
 INTO @JOBID

 WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE @PrevInstance AS int
   DECLARE @ErrCount AS int

   
   -- CERCA LA FINE DELLA History DELL'ULTIMA ESECUZIONE DEL JOB
   SELECT TOP 1 @PrevInstance = sjh.instance_id
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.step_id = 0    --> 0 = (Job outcome)
   ORDER BY
     sjh.instance_id DESC
     

   SELECT @PrevInstance = ISNULL(@PrevInstance, 0)


   -- CERCA PER I JOB ANDATI IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.instance_id >= @PrevInstance AND 
     sjh.step_id = 0 AND   --> 0 = (Job outcome)
     sjh.run_status = 0
   


   -- CERCA SE UNO STEP DEL JOB E' ANDATO IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0



   SELECT @ErrCount = ISNULL(@ErrCount, 0)


   -- MANDA LA MAIL
   IF @ErrCount > 0
    BEGIN
     SELECT   @jobName = j.name
       , @subject = LTRIM(ISNULL(@subjectPrefix, '') + ' Job "' + j.name + '" terminato CON ERRORI!')
     FROM msdb..sysjobs AS j WITH (NOLOCK)
     WHERE j.job_id = @JobID 



     -- COMPONE IL BODY DELLA MAIL
     SET @body = N'Job name:   ' + @JobName


     SELECT TOP 1 
       @body += N'
Step name:  ' + x.step_name + '

Message:
--------
' + x.message
     FROM msdb..sysjobhistory x
     WHERE x.run_status = 0
      AND x.job_id = @JobID
     ORDER BY 
       x.run_date DESC, x.run_time DESC



     SELECT TOP 1 
       @body += N'

Subsystem:  ' + s.subsystem + '
Command:    ' + s.command
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0
   ORDER BY 
     s.last_run_date DESC, s.last_run_time DESC



     EXEC msdb..sp_send_dbmail
        @recipients = @recipients
      , @subject =  @subject
      , @body_format =  'TEXT'
      , @body =  @body
    END
   
   
   FETCH NEXT 
   FROM #JOBS 
   INTO @JOBID
  END

 CLOSE #JOBS
 DEALLOCATE #JOBS

END
GO

Il provider 'Microsoft.ACE.OLEDB.12.0' non è registrato nel computer locale. (System.Data)

In SQL Management Studio, facendo una "Importazione/Esportazione guidata":

Impossibile completare l'operazione.

Il provider 'Microsoft.ACE.OLEDB.12.0' non è registrato nel computer locale. (System.Data)

Installare questi driver (32/64 bit in funzione della versione di SQL Management Studio!)

Questa sembra la versione precedente (non testata).

---

Nuovi URL per donwload:

32-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine.exe

64-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_X64.exe

Find the Size of Database File – Find the Size of Log File

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

Output:

name           log_size_mb  row_size_mb   total_size_mb
-------------- ------------ ------------- -------------
xxxxxxxxxxx    512.00       302.81        814.81

Trovato qui.

SQL - Restore database backup over the network

How do you restore a database backup using SQL Server over the network? You have few options to use a network file as a backup source

1) Map network drive/path, hosting file, under SAME user as MS-SQL Server.

2) Use xp_cmdshell extended stored procedure to map network drive from inside of MS SQL

-- allow changes to advanced options 
EXEC sp_configure 'show advanced options', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO
-- To enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO
EXEC xp_cmdshell 'NET USE Z: "\\Srv\Path password1 /USER:Domain\UserName /PERSISTENT:NO "'


--> Afterwards drive Z: will be visible in Server Managment studio, or just

RESTORE DATABASE DataBaseNameHere FROM DISK = 'Z:\BackNameHere.BAK'



-- DISABLE FLAGS

-- To enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO

-- allow changes to advanced options 
EXEC sp_configure 'show advanced options', 0
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO

via.

SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Se si incontra questo errore:

Msg 242, Level 16, State 3, Procedure MyProcedure_sp, Line 35
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

Succede quando la lingua del sistema operativo o di SQL Server è diversa da quella utilizzata nell'applicazione; bisogna modificare la lingua dell'utente:

- Security
- Logins
- Proprietà dell'utente:
- Cambiare la lingua: Italiano o British English (stesso formata data/ora, ma messaggi in inglese)


Oppure usare SET DATEFORMAT DMY prima di ogni EXEC...