Skip to content

ErcanOPAK.com

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

Tag: sql

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 […]

Continue Reading ..
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:

Continue Reading ..
SQL

How to get only second or Nth row in Sql Query

- 23.12.21 | 23.12.21 - ErcanOPAK comment on How to get only second or Nth row in Sql Query

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 Nth 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 […]

Continue Reading ..
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 ‘ %’ […]

Continue Reading ..
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 […]

Continue Reading ..
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’;  

Continue Reading ..
SQL

How to use Nested Cursors in SQL

- 17.08.21 - ErcanOPAK comment on How to use Nested Cursors in SQL

Declare @Parameter1 int; Declare @Parameter2 int; DECLARE Cur1 CURSOR FOR SELECT Column1 From Table1; OPEN Cur1 FETCH NEXT FROM Cur1 INTO @Parameter1; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ‘Processing Column1: ‘ + Cast(@Parameter1 as Varchar); DECLARE Cur2 CURSOR FOR SELECT Column2 FROM Table2 Where Column2 = @Parameter1; OPEN Cur2; FETCH NEXT FROM Cur2 INTO @Parameter2; […]

Continue Reading ..
SQL

How To Get All Row Count For All Tables In SQL Server Database

- 04.08.21 - ErcanOPAK comment on How To Get All Row Count For All Tables In SQL Server Database

SELECT (SCHEMA_NAME(A.schema_id) + ‘.’ + A.Name) AS TableName , SUM(B.rows) AS RecordCount FROM sys.objects A INNER JOIN sys.partitions B ON A.object_id = B.object_id WHERE A.type = ‘U’ GROUP BY A.schema_id, A.Name ORDER BY RecordCount DESC  

Continue Reading ..
SQL

How to check for ‘IS NOT NULL’ And ‘IS NOT EMPTY’ string in SQL

- 09.04.21 - ErcanOPAK comment on How to check for ‘IS NOT NULL’ And ‘IS NOT EMPTY’ string in SQL

If you only want to match N” as an empty string SELECT COLUMN FROM TABLE WHERE DATALENGTH(COLUMN) > 0 If you want to count any string consisting entirely of spaces as empty SELECT COLUMN FROM TABLE WHERE COLUMN <> ” If you want to use DATALENGTH for any string consisting entirely of spaces then you […]

Continue Reading ..
SQL

Filter Rows By Max Date in SQL

- 13.01.21 - ErcanOPAK comment on Filter Rows By Max Date in SQL

SELECT m1.Column1, m1.Column2, m1.Column3, r.MaxTime FROM ( SELECT Column1, MAX(TimeColumn) as MaxTime FROM MyTable GROUP BY Column1 ) r INNER JOIN MyTable m1 ON m1.Column1= r.Column1AND m1.TimeColumn = r.MaxTime

Continue Reading ..
SQL

SQL Server CONCAT_WS Function (Concat with Separator)

- 13.08.20 - ErcanOPAK comment on SQL Server CONCAT_WS Function (Concat with Separator)

The SQL Server CONCAT_WS() function concatenates two or more strings into one string with a separator. CONCAT_WS() means concatenate with separator.

Continue Reading ..
SQL

Search text in all tables in SQL Server

- 27.05.20 | 27.05.20 - ErcanOPAK comment on Search text in all tables in SQL Server

Please note that with a little bit changing you can also use this query as a stored procedure.

Continue Reading ..
SQL

How to hide message window in MS SQL Server

- 16.04.20 | 16.04.20 - ErcanOPAK comment on How to hide message window in MS SQL Server

CTRL +R toggles (hides or shows) the Messages window.

Continue Reading ..
SQL

How to solve ‘incorrect syntax near the keyword WITH’ problem?

- 14.04.20 | 14.04.20 - ErcanOPAK comment on How to solve ‘incorrect syntax near the keyword WITH’ problem?

Always use with statement like ;WITH then you’ll never get this error. The WITH command required a ; between it and any previous command, by always using ;WITH you’ll never have to remember to do this.

Continue Reading ..
SQL

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

- 11.04.20 - ErcanOPAK comment on How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO

Continue Reading ..
SQL

How to create Local and Global Temp Tables in MS SQL

- 11.04.20 - ErcanOPAK comment on How to create Local and Global Temp Tables in MS SQL

Creating Local Temp Table [with #]: SELECT TOP(10) ID INTO #TempTable FROM MyTable Craeting Global Temp Table [with ##]: SELECT TOP(10) ID INTO ##TempTable FROM MyTable Reaching Local Temp Table with Query: SELECT name FROM tempdb..sysobjects WHERE name LIKE ‘#TempTable%’  

Continue Reading ..
SQL

Set Identity Insert On/Off in MSSQL

- 08.04.20 | 11.04.20 - ErcanOPAK comment on Set Identity Insert On/Off in MSSQL

SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable (IdentityColumn, col2, col3, …) VALUES (IdentityValue, col2value, col3value, …) SET IDENTITY_INSERT MyTable OFF Note that you can not insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.

Continue Reading ..
SQL

Reset the Identity on table in MSSQL

- 08.04.20 - ErcanOPAK comment on Reset the Identity on table in MSSQL

DBCC CHECKIDENT(‘TableName’, RESEED, 0) Note that after running DBCC CHECKIDENT(‘TableName’, RESEED, 0): – Newly created tables will start with identity 0 – Existing tables will continue with identity 1

Continue Reading ..
Page 1 of 3
1 2 3 Next »

Posts navigation

Older posts
May 2022
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
« Apr    

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (332)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (221)
  • Find numbers with more than two decimal places in SQL (191)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (190)
  • Confirm before process with ASPxButton in Devexpress (189)
  • ASPxGridView – Disable CheckBox based on condition in GridViewCommandColumn (189)
  • How to make some specific word(s) Bold or Underline in ReportViewer (189)
  • Devexpress ASPxGridview Column Grouping in Code (177)
  • Add Constraint to SQL Table to ensure email contains @ (174)
  • Tense Changes When Using Reported Speech (167)

Recent Posts

  • How to put text inside MVC Razor code block
  • How to solve 0xc0000135 application errors after Windows 11 Update
  • How to get the integrity value for a jquery version of script reference in a web page
  • How to get session value in Javascript
  • How to get value from resx file in C#
  • How to lock the ciritical code part in C#
  • How to make theater mode the default for Youtube
  • How to turn off YouTube annotations and cards
  • How to hide Youtube chat windows permanently
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server

Recent Posts

  • How to put text inside MVC Razor code block
  • How to solve 0xc0000135 application errors after Windows 11 Update
  • How to get the integrity value for a jquery version of script reference in a web page
  • How to get session value in Javascript
  • How to get value from resx file in C#

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (332)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (221)
  • Find numbers with more than two decimal places in SQL (191)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (190)
  • Confirm before process with ASPxButton in Devexpress (189)

Social

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

powered by XBlog Plus WordPress Theme