Skip to content

ErcanOPAK.com

  • ASP.Net WebForms
  • ASP.Net MVC
  • C#
  • SQL
  • MySQL
  • PHP
  • Devexpress
  • Reportviewer
  • About

Category: SQL

SQL

How to use OUTPUT for Insert, Update and Delete in SQL

- 19.09.23 - ErcanOPAK comment on How to use OUTPUT for Insert, Update and Delete in SQL

The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version. The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED […]

Read More
SQL

How to get the first and last date of the current year in SQL

- 18.08.23 - ErcanOPAK comment on How to get the first and last date of the current year in SQL

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear, DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear Thanks to Jamie F for the answer.

Read More
SQL

How to get difference between 2 tables in MSSQL

- 17.06.23 - ErcanOPAK comment on How to get difference between 2 tables in MSSQL

If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]

Read More
SQL

How to solve the stucking on “Loading Packages” phase for SSMS installation

- 10.03.23 - ErcanOPAK comment on How to solve the stucking on “Loading Packages” phase for SSMS installation

to fix this problem: remove temp files from %temp% folder (C:\Users<user name>\AppData\Local\Temp) open the register and remove “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” or execute via cmd (with admin rights): reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32 run install with admin rights NOTE: Just for those who don’t know register just open CMD as an […]

Read More
SQL

How to reset identity seed after deleting records in SQL

- 14.12.22 - ErcanOPAK comment on How to reset identity seed after deleting records in SQL

The DBCC CHECKIDENT management command is used to reset the identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT (‘[TestTable]’, RESEED, 0); IMPORTANT: The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value […]

Read More
SQL

How to create a single string from multiple rows in T-SQL and MySQL

- 05.12.22 - ErcanOPAK comment on How to create a single string from multiple rows in T-SQL and MySQL

To create a single string from multiple rows in MySQL, you can use the GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separated by a specified delimiter. Here is an example of how you can use this function: SELECT GROUP_CONCAT(column_name ORDER BY column_name ASC SEPARATOR ‘,’) FROM […]

Read More
SQL

SQL Tip: “@@IDENTITY” should not be used

- 03.12.22 - ErcanOPAK comment on SQL Tip: “@@IDENTITY” should not be used

@@IDENTITY returns the last identity column value created on a connection, regardless of the scope. That means it could return the last identity value you produced, or it could return a value generated by a user-defined function or trigger, possibly one fired because of your insert. In order to access the last identity value created […]

Read More
SQL

SQL Tip: “NOCOUNT” should be activated on “PROCEDURE” and “TRIGGER” definitions

- 03.12.22 - ErcanOPAK comment on SQL Tip: “NOCOUNT” should be activated on “PROCEDURE” and “TRIGGER” definitions

NOCOUNT is by default deactivated (OFF) at the server level. It means by default, the server will send to the client the number of rows affected by the SQL query executed which is, in most cases, useless because no one will read this information. Deactivating this feature will save some network traffic and improve the […]

Read More
SQL

SQL Tip: “NULL” should not be compared directly

- 03.12.22 - ErcanOPAK comment on SQL Tip: “NULL” should not be compared directly

“NULL” is never equal to anything, even itself. Therefore comparisons using equality operators will always return False, even when the value actually IS NULL. For that reason, comparison operators should never be used to make comparisons with NULL; IS NULL and IS NOT NULL should be used instead. Bad example: UPDATE books SET title = ‘unknown’ WHERE title = NULL — Noncompliant […]

Read More
SQL

How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()

- 20.07.22 | 21.07.22 - ErcanOPAK comment on How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()

In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]

Read More
SQL

How to get stored procedure parameters details in SQL

- 23.06.22 - ErcanOPAK comment on How to get stored procedure parameters details in SQL

select ‘Parameter_name’ = name, ‘Type’ = type_name(user_type_id), ‘Length’ = max_length, ‘Prec’ = case when type_name(system_type_id) = ‘uniqueidentifier’ then precision else OdbcPrec(system_type_id, max_length, precision) end, ‘Scale’ = OdbcScale(system_type_id, scale), ‘Param_order’ = parameter_id, ‘Collation’ = convert(sysname, case when system_type_id in (35, 99, 167, 175, 231, 239) then ServerProperty(‘collation’) end) from sys.parameters where object_id = object_id(‘MySchema.MyProcedureName’)  

Read More
ASP.Net MVC / ASP.Net WebForms / C# / SQL

How to add default value for Entity Framework migrations for DateTime and Bool

- 19.06.22 - ErcanOPAK comment on How to add default value for Entity Framework migrations for DateTime and Bool

Just add defaultValue parameter in CreateTable method for property: public partial class TestSimpleEntity : DbMigration { public override void Up() { CreateTable( “dbo.SimpleEntities”, c => new { id = c.Long(nullable: false, identity: true), name = c.String(), deleted = c.Boolean(nullable: false, defaultValue: true), }) .PrimaryKey(t => t.id); } public override void Down() { DropTable(“dbo.SimpleEntities”); } } After that, run update-database -verbose command, […]

Read More
SQL

How to get triggers create and update date in SQL

- 07.06.22 - ErcanOPAK comment on How to get triggers create and update date in SQL

SELECT o.name as [Trigger Name], CASE WHEN o.type = ‘TR’ THEN ‘SQL DML Trigger’ WHEN o.type = ‘TA’ THEN ‘DML Assembly Trigger’ END AS [Trigger Type], sc.name AS [Schema_Name], OBJECT_NAME(parent_object_id) as [Table Name], o.create_date [Trigger Create Date], o.modify_date [Trigger Modified Date] FROM sys.objects o INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id WHERE (type = […]

Read More
SQL

How to enable, disable and check if Service Broker is enabled on a database in SQL Server

- 14.01.22 - ErcanOPAK comment on How to enable, disable and check if Service Broker is enabled on a database in SQL Server

To enable Service Broker run: ALTER DATABASE [Database_name] SET ENABLE_BROKER; To disable Service Broker: ALTER DATABASE [Database_name] SET DISABLE_BROKER; To check if Service Broker is enabled on a SQL Server database: SELECT is_broker_enabled FROM sys.databases WHERE name = ‘Database_name’; If the result is ‘1’, the Service Broker is enabled. Thanks to zarez.net for this useful […]

Read More
SQL

How to check if recursive triggers are enabled in SQL

- 25.12.21 - ErcanOPAK comment on How to check if recursive triggers are enabled in SQL

Recursive triggers are set at the database level. It’s part of the database metadata information and is available through “sys.databases” view. You can use the below query to check whether the recursive triggers are enabled or not on your database: SELECT name AS ‘Database’, is_recursive_triggers_on AS ‘Recursive Trigger Enabled’ FROM sys.databases Here is the output:

Read More
SQL

How to hide letters with asterisks except first letter in SQL

- 08.11.21 - ErcanOPAK comment on How to hide letters with asterisks except first letter in SQL

The easiest way to do that is using a function CREATE FUNCTION [dbo].[HideNameWithAsterisks](@Name varchar(max)) RETURNS varchar(MAX) AS BEGIN DECLARE @loop int = LEN(@Name) WHILE @loop > 1 SELECT @Name = STUFF(@Name, @loop, 1, CASE WHEN SUBSTRING(@Name, @loop – 1, 2) like ‘% ‘ THEN ‘ ‘ WHEN SUBSTRING(@Name, @loop – 1, 2) like ‘ %’ […]

Read More
C# / SQL

How to use SQL RAISEERROR() messages in C#

- 21.10.21 - ErcanOPAK comment on How to use SQL RAISEERROR() messages in C#

DECLARE @MyValue int SELECT @MyValue = Column1 WHERE Column2 > 50 IF @MyValue IS NULL BEGIN RAISEERROR(‘This is my custom message from SQL.’, 16, 1) END ELSE SELECT MyValue END Use an error code within 11-16, or just use 16 for a “general” case. RAISERROR(‘This is my custom message from SQL.’,16,1) Why? Here’s summary of […]

Read More
SQL

How to check column data types of a table in SQL

- 12.10.21 - ErcanOPAK comment on How to check column data types of a table in SQL

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’your_table_name’;  

Read More
Page 1 of 4
1 2 3 4 Next »

Posts navigation

Older posts
September 2023
M T W T F S S
 123
45678910
11121314151617
18192021222324
252627282930  
« Aug    

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (665)
  • Get the User Name and Domain Name from an Email Address in SQL (657)
  • How to select distinct rows in a datatable in C# (511)
  • Add Constraint to SQL Table to ensure email contains @ (428)
  • Average of all values in a column that are not zero in SQL (348)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (328)
  • Find numbers with more than two decimal places in SQL (306)
  • Confirm before process with ASPxButton in Devexpress (304)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (277)
  • ASPxGridView – Disable CheckBox based on condition in GridViewCommandColumn (277)

Recent Posts

  • How to remove all non alphanumeric characters from a string in C#
  • How to get the Xth Day of the Week of the Year in C#
  • How to get formatted JSON in C#
  • How to convert JSON to XML or XML to JSON in C#
  • How to use OUTPUT for Insert, Update and Delete in SQL
  • How to get the first and last date of the current year in SQL
  • How to solve extra blank page at end of Microsoft Reportviewer
  • How to Use Picture-in-Picture in Chrome Browser
  • How to add some content to the right side of CardHeader on Bootstrap
  • How to change star rating color on mouseover/out, mouseenter/leave with Javascript

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (665)
  • Get the User Name and Domain Name from an Email Address in SQL (657)
  • How to select distinct rows in a datatable in C# (511)
  • Add Constraint to SQL Table to ensure email contains @ (428)
  • Average of all values in a column that are not zero in SQL (348)

Recent Posts

  • How to remove all non alphanumeric characters from a string in C#
  • How to get the Xth Day of the Week of the Year in C#
  • How to get formatted JSON in C#
  • How to convert JSON to XML or XML to JSON in C#
  • How to use OUTPUT for Insert, Update and Delete in SQL

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter

© 2023 ErcanOPAK.com

Proudly powered by WordPress | Theme: Xblog Plus by wpthemespace.com