SQL Server Dev @SQLServerDev

[SSIS] Import csv with YYYYMMDD field: Right click flat file source -> Show Advanced Editor -> Input and Output Properties -> Output Columns -> Change FastParse of the date field to "True"

Insert large amount of records into a table -> Using batches ("while" loop)

"The query did not run, or the database table could not be opened." error when using SQL containing temporary tables in Excel -> Put "SET NOCOUNT ON" at the beginning of the script.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Execute SSIS package using a different account: 1) Create a credential in Security->Credentials; 2) Create a proxy in SQL Server Agent->Proxies using the credential and check "SQL Server Integration Services Package"; 3) In "Job Step Properties", select the proxy under "Run As:"

For SQL Server Execution Plan, we need to concentrate on the higher percentage operator, the thickest arrow, largest subtree cost, not ordered data, and the SCAN operators.

SSIS: Extract flat file with float column -> In Flat File Connection Manager, set DataType for the column to 'numeric [DT_NUMERIC]' and specify DataPrecision & DataScale.

Access SQL Server without knowing admin password (but with local admin right on the computer): Restart SQL Server in single-user mode by using the startup option -m (docs.microsoft.com/en-us/sql/data…)

To enable another computer to access SQL Server, enable SQL Server Browser (under SQL Server Services) and TCP/IP (under SQL Server Network Configuration) then open the UDP port 1434 (Windows Firewall->Advanced settings->Inbound Rules->New Rule).

Error when calling a stored procedure containing temporary tables from Excel -> Use the SET NOCOUNT ON statement in the stored procedure (after the AS keyword).

Convert UTC to Local Time: SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, MyTable.UTCColumn), DATENAME(TZoffset, SYSDATETIMEOFFSET()))) FROM MyTable

The transaction log for database '...' is full due to 'LOG_BACKUP' -> Change Recovery model to 'Simple' (in Database Properties -> Options). This applies to dev environment.

"The visual studio component cache is out of date. Please restart visual studio. (mscorlib)" error (when opening SSMS) -> Delete "Temporary files" in Windows "Disk Cleanup" utility.

You have to rely on ODBC or ADO.NET when defining SSIS connections for Azure SQL Databases. Also ensure the schema complies with all relevant Azure PaaS restrictions, such as the presence of clustered indexes or the ROWGUIDCOL and NOT FOR REPLICATION options.

DelA: EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'DELETE FROM ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?' GO

Drop All Constraints and Tables: exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name"; exec sp_MSforeachtable "drop table ?";

SSIS with Oracle: "cannot convert unicode to non-unicode string data types" error -> use Attunity Connector

CSV Standard (RFC 4180): Fields having CRLF, double quotes (dq) & "," must be enclosed in dq. Dq in a field must be preceded with another dq

Restore failed ... (file) cannot be overwritten ... - Use T-SQL: RESTORE DATABASE ... WITH MOVE ... REPLACE serverfault.com/questions/2446…

Move logins between servers: Back up master db and restore on destination server (only if you have same version & SP on destination server)