How to list SQL Jobs using Query?

SELECT
   [sSVR].[name] AS [OriginatingServerName]
   , [sJOB].[name] AS [JobName]
    , [sSCH].[name] AS [JobScheduleName]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
FROM
   [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

How to update the Max Memory at SQL Server?

From the command prompt start sqlCmd using the Dedicated Administrator Connection (DAC). :

net start MSSQLServer /mSQLCMD /f

C:>sqlcmd -S ServerName -U sa -P –A


Once you are connected, execute the following to set the memory to 4 GB, it should be enough to allow you to reconnect using the Management Studio tool.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO


Issues can be fixed by above process:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .

There is insufficient system memory to run this query.

Accidentally turned down SQL memory too low to log in, how do I fix?

How to use CLR function at SQL Server?

Creating a CLR function in SQL Server involves the following steps:
1. Define the function as a static method of a class in a language supported by the .NET Framework
2. Register the assembly in SQL Server by using the CREATE ASSEMBLY statement
3. Create the function that references the registered assembly by using the CREATE FUNCTION statement

1. a. Create a SQL Server Project using Visual Studio 2019 or Higher, Named the Project SQLExternalMethods
b. Create a Class MySQLFunctions
c. Add a static Method with SQL function Attribute
i.e.
namespace SQLExternalMethods
{
public static class MySQLFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Test(string myString )
{
return myString .ToUpper();
}
}
}


2. a. Build the Above project and copy the SQLExternalMethods .dll to SQL server Installed Server.
b. Open SSMS and Register the assembly
CREATE ASSEMBLY SQLTitleCase
FROM 'C:\Program Files (x86)\LLM\ SQLExternalMethods .dll ';
GO


3. Now create a SQL function to use the c# methods
CREATE FUNCTION MyToUpper(@myString nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME SQLExternalMethods . MySQLFunctions .Test ;
GO

How to ALTER all tables columns in SQL Database

declare @cols table (i int identity, tablename varchar(100), colname varchar(100))

insert into @cols
select TABLE_NAME, COLUMN_NAME
from information_schema.COLUMNS
where DATA_TYPE='nvarchar'

select * from @cols

declare @i int, @maxi int
select @i = 1, @maxi = MAX(i) from @cols

declare @sql nvarchar(max)

while(@i <= @maxi)
begin
    select @sql = 'alter table sub_history.'+tablename+' alter column ' + colname + ' varchar(255) NULL'  from @cols where i = @i
    exec sp_executesql @sql
    select @i = @i + 1
end

Exception- The definition for user-defined data type ‘MyTypeList’ has changed.

The definition for user-defined data type ‘MyTypeList’ has changed. Exception when we rename or changed any User defined TYPE and it used in stored procedures.
Stored Procedures which is using this type variable will not run and throwing the exception. If we have large numbers of SP then it will to time consuming or irritating to re-compile all SP one by one.
Instead of using this manual process this script will help to fix all references.

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTypeList', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
	PRINT @Name
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

Search in MSSQL tables

Sometime we need to reverse engineering in the database tables to find a specific values, we can find the column name easily but in large database, to search a specific key value is too hard. From below query we can easily search value in all tables, it will list column with table in the result.

DECLARE @SearchStr nvarchar(100) = 'test'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'numeric')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT distinct ColumnName, ColumnValue FROM @Results order by ColumnValue

How to read DBF to Datatable

OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp;Extended Properties=dBase III");
OleDbCommand command = new OleDbCommand("SELECT * FROM Test.DBF", oConn);
oConn.Open();
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
oConn.Close();

SQL SERVER – Installation Failure Database engine services failed.

Sometimes when we install SQL Server or Add any new SQL Server Instance on Windows Machine, we can see the database errors listed below.
To resolve these errors we are trying to repair existing installation or do something else.
If nothing happens then we are trying to install it completely from control panel, but it is not 100% sure that the fresh new installation will works. Some of the SQL Server links existing at system registry so we are not able to get it success.

Installation error list
Installation error

Installation error list
Installation error list

To resolve these registry things we can un-install all SQL server related things by theirs ID.

The ID we can get using “WMIC” command. So run this command on console:

WMIC PRODUCT Where "Caption like '%SQL%'" GET Caption, IdentifyingNumber

WMIC Result
WMIC Result

You will get a list of objects with their identifier.

Now un-install these all things one by one using below command:

msiexec /x {GUID}

i.e..

msiexec /x {ABB6AC00-F1D8-4EBF-8128-830D090B76C0}

 

I think after that restart the machine and try fresh installation, will works.