How to overcome the error message "Types don't match between the anchor and the recursive part in column..." for varchar columns in a recursive CTE query

SQL

SQL-Server

Dev

DBA

Prevent Brute Force Attacks on SQL Server

1 Jul 2020 by Trond Lind

Prevent brute-force login attacks on a remotely accessible SQL Server database using T-SQL

SQL-Server

SysAdmin

Use of columns belonging to tables referenced on main query in a subquery

17 Feb 2011 by John Gathogo

Catastrophic results associated with unqualified use of columns belonging to tables referenced on main query in a subquery

Loop through all tables in SQL Server

25 Feb 2011 by pankajupadhyay29

Use this when you want to perform a task on all tables in a database

SQL-Server

Sp_MSForeachTable to update tables having common columns

19 Jul 2011 by Niladri_Biswas

SQL-Server

How to fetch data from the previous or next rows in the resultset

31 Aug 2014 by Wendelius

This tip demonstrates the use of LAG and LEAD functions in SQL Server.

SQL

SQL-Server

List blocking processes in SQL server

11 Oct 2011 by Chris Maunder

A quick script to enable you to find the processes that are blocking in SQL server

SQL

SQL-Server-2008

SQL-Server

Debugging DataSet Constraint Errors

18 Jun 2012 by cjb110

How to identify the cause of the DataSet Constraint Exception

How to update a row with another row if column values are null in SQL

17 Aug 2012 by ManojKumar19

Update row column with another row column in same table SQL only if it is null.

Multiple SQL INSERT Operations with a Single Command

23 Sep 2012 by OriginalGriff

Inserting a number of rows in a database isn't difficult, but how do you do it in a single command without concatenating strings?

SQL-Server

SQL Server: How to Refresh Intellisense Cache

20 Oct 2012 by aasim abdullah

How to Refresh Intellisense Cache in SQL Server

SQL-Server-2008

SQL-Server

Microsoft SQL System View Version Map

13 Dec 2012 by Paw Jershauge

MS SQL system views, mapped over version.

Check if a number is a power of 2 with Sql Server 2012

19 Jan 2013 by Wendelius

This tip shows how to get rows from a table where a field contains a number that is a power of 2 using LOG function.

SSIS-Fuzzy lookup for cleaning dirty data

15 Jan 2013 by mparvez

SSIS fuzzy lookup for cleaning dirty data.

SQL-Server

Dev

OLAP CUBE Optimization Technique and a Small Trade off

25 Sep 2014 by SERokon

OLAP cube performance optimization technique

SQL-Server

OLAP

SQL Query with C# Typed Library - CooQ

2 Nov 2015 by Guni Van

Mapping your database with C# object class, then make typed-safe query like SQL syntax

TrimChar to Remove Desired Character

30 Sep 2016 by Wendelius

TrimChar function which removes occurences of the desired character from both ends of a string.

T-SQL

SQL-Server

Dev

Selecting Valid Records

7 Dec 2016 by Wendelius

This tip demonstrates how to select valid records from a table that contains validity date limits.

SQL

SQL-Server

Dev

Amount to Words

18 Dec 2016 by NightWizzard

Convert amounts to their spoken equivalents

TIP: Managing SqlGeography class from C#

4 Sep 2010 by Jaime Olivares

How to Insert or Update an SqlGeography object in SQL Server 2008 from a C# application

SQL-Server-2008

SQL-Server

A better way to count(*)

8 Jun 2010 by basementman

Or, assuming your table has an index, you could do this "old-school" as:select MAX(rows) from sysindexes where id = Object_ID('{tablename}')

SQL

LINQ

SQL-Server

Default keyword in SQL Server

12 Jun 2011 by Mel Padden

Default keyword in SQL Server

T-SQL

SQL-Server

Parameter

I don’t know why my boss always gets angry

14 Jun 2011 by jwood_denver

Why don't you just use a query like this?SELECT CAST(serverproperty(N'servername') AS varchar(50)) AS [Server Name], db_name() AS [Database Name], SCHEMA_NAME(schema_id) AS [Schema Name], [name] AS [Object Name], [type_desc] AS [Object Type], [create_date] AS [Create...

SQL-Server

Preparing “Data Dictionary” using SQL Query

7 Oct 2011 by Chirag Vidani

SQL-Server

Always close your cursor

18 Oct 2011 by Elina Blank

To see the default value of the cursor scope:select * from sys.databases(if value of "is_local_cursor_default" is 0 then scope id global, otherwise it is local).The scope is GLOBAL by default (for compatibility reasons).To change default scope use following:ALTER DATABASE...

SQL

SQL-Server

Getting a list of SQL Server modules to refresh

25 Dec 2011 by PIEBALDconsult

Using a Common Table Expression to produce a list of Views, Procedures, and Functions

SQL-Server

CTE

Sorting 'Total' After Data Values

1 Jun 2016 by PIEBALDconsult

Interspersing 'Total' rows among summarized data rows in the correct order

SQL

SQL-Server

Creating SQL Joins in Simple Steps

20 Sep 2012 by Sreedhar Puligundla

Simple steps to create SQL joins by using SQL Server 2008 Management Studio

SQL Server: Changed data capture without using Triggers

23 Oct 2012 by aasim abdullah

How to use DELETED and INSERTED virtual tables to capture data change in result of any INSERT, UPDATE or DELETE statement.

SQL Server : How to Keep Database Restore History

24 Oct 2012 by aasim abdullah

Backup and then restore of a database is a normal job for developers and DBAs. This script will be helpfull to findout, from which path, and which backup file a database was restored.

How to execute multiple database script files from a specific directory

18 Feb 2013 by S. M. Ahasan Habib

I will explain very simple way to execute multiple script files (*.sql) from a directory.

SSIS 2008 drop last data row from *.csv or *.txt file while import data using SSIS DataFlow Task..

19 Feb 2013 by Rajesh Manjarekar

I found out that a possible cause is the difference between the SSIS 32 bits and 64 bits environment

How to implement ASP.NET Membership into a new database

29 Apr 2013 by ChienVH

This article will show you how to implement Asp.Net membership into a new database

SQL-Server

ASP.NET

Load GridView from Excel

15 May 2013 by Manikandan Sekar

Loading Data from Excel

Run All Subscription Reports At Once in SSRS Manually

5 Sep 2013 by Mubin M. Shaikh

Manually Run SSRS Scheduled Reports

Introduction to Named Query in Sql Server Analysis Services

29 Sep 2013 by Mubin M. Shaikh

Create Named Query in Data Source View While Designing OLAP Cube in SSAS

Create CSV File in Microsoft SQL

27 Jan 2017 by Rajesh_K_Sharma

How to create CSV file in SQL

SQL

SQL-Server-2008

SQL-Server

Instead of Triggers in Object-Relational (O/R) Mapping

31 Mar 2014 by h_wiedey

Usage of instead of triggers in Object-Relational (O/R) Mapping

SQL

SQL-Server

Dequeue workitems using MERGE

25 Feb 2014 by Darek Danielewski

This article demonstrates how to safely dequeue items from a shared table

Save your Data from LDAP Injection Attack

9 Jul 2014 by Jason Parms

Lightweight Directory Access Protocol (LDAP) Injection

Entity and Mapping Class Generator for EF

29 Dec 2015 by Daniel Miller

This tip provides a simple solution to automate the generation of entity and mapping classes for Entity Framework.

Replace Cyrillic Letters with Visually Similar Latin Letters Using T-SQL

28 Oct 2016 by Pavel Sinkevich

If data source is populated manually, there is a typical problem: a mix of visually similar Cyrillic and Latin letters. This tip describes how to fix it.

Pay Attention to What "Script...To" Generates

22 Sep 2017 by #realJSOP

It doesn't always happen the way you expect.

Node.js Connect to Local SQL (Trusted User)

25 Nov 2017 by A.Ebrahimi

Connect to local database on MS SQL Server in local machine and develop node.js code

Node.js

SQL-Server

Node.js-tools

Issues after Upgrade to SQL Server 2017 – Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

11 Jul 2018 by Satnam Singh CapGemini

Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

How to Create and Use a Self-referencing Hierarchical Table

8 Jan 2020 by E. Scott McFadden

This article explains how to create and use a self referencing key in a SQL Server Table.

EF6 Migrations and TimeStamp Bug with CodeFirst

2 Nov 2021 by Herman<T>.Instance

Migrations does not apply to T-SQL rules for ALTER TABLE ALTER COLUMN

Record Count of Tables in SQL Server

26 Aug 2010 by tarun_j200

Also use either this one:SELECT OBJECT_NAME(SYSINDEXES.id), SYSINDEXES.rowcntFROM SYSINDEXES INNER JOIN SYSOBJECTS ON SYSINDEXES.id = SYSOBJECTS.idWHERE SYSINDEXES.indid

SQL-Server

SQL-Server-2005

database

Generate Script with Data from Database

13 Nov 2010 by tarun_j200

SSMS also provides the option to generate SQL script file which contains both schema and data.Right click the database and select Generate Scripts under Tasks Menu.Click Set Scripting Options on the left hand menu (in SQL Server 2008 R2) then click Advanced button, there is an option under...

SQL-Server

SQL-Server-2005

Use SQL to generate large data sequence

19 Feb 2015 by DrABELL

Generate large data series by using Cartesian Product and small data table

"Column name Create Date contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault."

5 Jan 2011 by Umair Feroze

To resolve the invalid chracter from FOR XML PATH clause

SQL-Server

REMOTING using SQL Server

7 Jan 2011 by Member 3128846

Example to implement remoting concept using SQL Server database

C#3.0

SQL-Server

Clearing Transaction Logs

23 Jan 2011 by sx2008

The root cause why the transaction log file grows so big is the wrong recovery model.The recovery model is set to full by default.99 percent of all SQL Servers never make use of this model because there is no backup of the transaction log file.So the great majority of all databases should...

SQL-Server

SQL-Server-2005

How to remove duplicate rows in SQL Server 2005 when no key is present

22 Feb 2011 by Costica U

Here is another solution:IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL BEGIN DROP TABLE #TestTable ENDCREATE TABLE #TestTable ( Column1 varchar(1), Column2 int )INSERT INTO #TestTable VALUES ('A', 1);INSERT INTO #TestTable VALUES ('A', 1); --...

SQL

SQL-Server

SQL-Server-2005

Convert CSV list into a single column multiple rows

28 Mar 2011 by Sunasara Imdadhusen

SQL-Server

Find paricular word or text in all database objects on the server

16 Feb 2013 by 1Lam

Create the following table on a local database:CREATE TABLE [dbo].[TB_TEMP_TABLESTORE]( [SEARCH_STRING] [nvarchar](128) NULL, [DB_NAME] [nvarchar](128) NULL, [OBJECT_NAME] [nvarchar](128) NULL, [OBJECT_TYPE] [nvarchar](50) NULL) ON [PRIMARY]Rename Yourdatabase to the same...

SQL-Server

The Pain and Anguish of DTS Script Debugging

26 May 2011 by #realJSOP

When all else fails, fire off some events and bask in the glow of the IDE's Progress tab.

SQL-Server

dtsearch

debugging

Adding Time in HH:MM Format - Useful SQL Query

7 Jun 2011 by Shahriar Iqbal Chowdhury/Galib

Hi,Here is an alternative for your solutionSELECT CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(TMP.TotalTime as DATETIME))),0) as TIME)FROM( SELECT CAST(timeduration AS TIME) TotalTime FROM mytable) TMP

SQL

SQL-Server

SQL-Server-2005

Trailing spaces are ignored in SQL Server 2008

23 Jun 2011 by zvin

When comparing data stored in databases, you don't need to use RTRIM()

SQL-Server-2008

SQL-Server

Order by Clause with Column_Index instead of Column Name

18 Jul 2011 by RaviRanjanKr

A tip enable you to learn how to use Column_Index instead of Column Name with Order by clause

SMO 101 - Getting all DB's

17 Aug 2011 by bmckuhen

How to get a quick list of all DBs using SMO

Always close your cursor

18 Oct 2011 by Kabwla.Phone

You also might want to look into the "LOCAL" keyword.This limits the scope of the cursor to the context in which it is created.DECLARE IdCursor CURSOR LOCAL FOR SELECT Id FROM MyTableThis is beneficial for many reasons.

SQL

SQL-Server

Find all Stored Procedures having a given text in it

25 Oct 2011 by Jaume Pascual

SELECT DISTINCT so.name FROM dbo.sysobjects so inner join .dbo.syscomments sc on so.id=sc.id WHERE so.xtype='P' AND sc.text like '%SearchString%'Much faster!

SQL-Server

Execute SQL Query / SQL Command on SQL Server Using the Command Prompt

25 Jan 2012 by IkhwanKrisnadi

How to execute SQL Query / SQL Command on SQL Server using command prompt.

SQL-Server-2008

SQL-Server