SQL Server – insufficient free space on disk volume while restoring database

Sometime you may come across situation where you try to restore database having large transaction log for small database. We also faced issue of insufficient free space on disk that was related to transaction log size while restoring production database on development server.    There is insufficient free space on disk volume ‘O:\’ to create the database. The ...

SQL Server – CHARINDEX Function with example

SQL Server CHARINDEX function returns starting position of a pattern you specify. CHARINDEX takes two parameter, first parameter is specify pattern to be search in expression. Syntax SELECT CHARINDEX(‘pattern’,’Expression or string’) 1. Pattern -> Value to be search in string or expression 2. Expression or string -> Specified pattern to be under second parameter value. Example ...

SQL SERVER – Difference Between CHARINDEX and PATINDEX with example

CHARINDEX or PATINDEX can be use to search specify pattern in table column in sql server. Difference between CHARINDEX and PATINDEX is one use wild card character to search pattern in sql server CHARINDEX and PATINDEX gives you the starting position of a pattern in STRING or Experssion. Example of CHARINDEX SELECT CHARINDEX(‘Hello’,’Hello World’) as ...

BCP Error – bcp is not recognized as an internal or external command

There are lots of options available in BCP command, you can see by typing “bcp /”? in the command prompt. C:\>bcp northwind.dbo.customers out c:\custdb.txt -T -c While executing BCP command if it gives error saying “’bcp’ is not recognized as an internal or external command,operable program or batch file. Then go to “C:\Program Files\Microsoft SQL ...

SQL Server – CHAR() – Convert ASCII code to Character with example

SQL Server – CHAR() It will convert ASCII code to Character value, takes integer value as input parameter. Syntax: SELECT CHAR(integer_value) Input parameter ->ASCII code as Integer_value ranges from 0 to 255, returns NULL value if it exceeds more then 255. Example Here we are passing input parameter value 65 to CHAR() function that will ...

SQL Server – ASCII function with example

ASCII function ASCII will return ASCII value of left most character of the string or character expression. Syntax: SELECT ASCII(string or character_expression) It take input parameter as string or  single character value and returns integer as result. Example Here we will use ASCII function to find out the ASCII code value of Alphabet A, B ...

SQL Server – Disable and enable SA account

Received request to disable all sql server SA account from DEV, UAT and PROD, Can we modify permissions of SA account ? answer is not possible. We cant modify permissions of sql server SA account. SA account can be disable or enable using script or sql server management studio. How to disable or enable sql server SA ...

SQL Server 2008 – List of Character String function

Following string functions can be use only with string as input parameter and returns either string or numeric in output. 1. ASCII -> Returns ASCII value of leftmost character from string 2. CHAR -> Converts ASCII value to character 3. CHARINDEX -> Returns position of search expression or character from string 4. DIFFERENCE -> Returns ...

SQL Server – Simple Example to Create Database Role and grant permission to role

As sql server dba, you come cross number of request from developer asking about access to execute, select , drop and create permission on database. By creating database role and grant them appropriate permission is good example of  standard practice for sql server on database level. You can make changes at database role, it will ...

SQL Server Error 64 A connection was successfully established with the server

We got email from developer saying they are getting error while connecting to database server as shown below A connection was successfully established with the server, but then an error occurred during the login process ( provider: TCP provider, error:0 – the specified network name is no longer available) Microsoft Sql server, Error: 64) Checked ...