Tag: how to

Fix “Not unique table/alias” in MySQL (Error 1066)

Posted on by Ian

If you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.

Table names and aliases must be unique when doing queries in MySQL.

The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.

To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).

Continue reading →

How to Disable a Constraint in SQL Server

Posted on by Ian

Disabling constraints in SQL Server can be useful for various operations, such as bulk data loading or certain maintenance tasks. But not all constraints are created equal. The method we use to disable a constraint, and whether that’s even possible, depends on the type of constraint.

In this article, we’ll explore how to disable different types of constraints and consider the potential impacts.

Continue reading →

Fixing Error 4121 When Using a CTE with FOR XML in SQL Server

Posted on by Ian

If you’re trying to use a common table expression (CTE) that produces XML in SQL Server, but you’re getting error 4121, which reads something like “Cannot find either column “EmployeeData” or the user-defined function or aggregate “EmployeeData.query”, or the name is ambiguous” it might be that you’re trying to query the XML in the outer query, but your CTE isn’t outputting the XML as an actual xml type.

The FOR XML result must be an xml type in order to process the result on the server.

To fix this issue, make sure the CTE outputs the result using the xml data type.

Continue reading →

Fix “Unknown table … in HANDLER” in MySQL (Error 1109)

Posted on by Ian

If you’re getting MySQL error 1109 which reads something like “1109 (42S02): Unknown table ‘products’ in HANDLER” in MySQL, it appears that you’re trying to reference a table that’s not currently open when using MySQL’s HANDLER statement.

This can happen when you try to read from a table that you haven’t yet opened. It can also happen when you’ve assigned an alias to the table but you try to reference it without the alias. And it can happen if you try to close a table that isn’t actually open.

Continue reading →

4 SQL Server Dynamic Management Views That Return Memory Usage Data

Posted on by Ian

Dynamic Management Views (DMVs) in SQL Server can be quite handy for monitoring and troubleshooting database performance. When it comes to memory management, understanding how our SQL Server instance uses memory can help us identity performance issues, as well as potential fixes.

In this article, we’ll explore four essential DMVs that provide valuable insights into memory usage in SQL Server.

Continue reading →

Using the ORDER BY Clause Inside a CTE Query in SQL Server

Posted on by Ian

A common table expression (CTE) in SQL Server is used to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can simplify complex queries and improve readability. However, there are specific rules for using the ORDER BY clause inside a CTE.

Continue reading →

Fix “Column names in each table must be unique…” in SQL Server (Error 2705)

Posted on by Ian

If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.

Column names must be unique within each table.

Continue reading →