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

RDP- Automatically sign out of disconnected users?

  • Open the ‘Group Policy Editor’ for your server.
  • Navigate to: Local Computer Policy / Computer Configuration / Administrative Templates / Windows Components / Remote Desktop Services / Remote Desktop Session Host / Session Time Limits.
  • Find the key ‘Set time limit for disconnected sessions’
  • Enable and set the Time Limit at dropdown, what ever you need, I have used 8Hours.

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.

Missing Windows Authentication Feature IIS and Windows 10

If you have windows 10 Home or SL version when might be you are unable to see Windows Authentication under IIS options when installing.

To enable “Windows Authentication” either you need to upgrade the OS to Pro version or you can run this command to enable it.

C:\WINDOWS\system32>dism /online /norestart /add-package:%SystemRoot%\servicing\Packages\Microsoft-Windows-IIS-WebServer-AddOn-2-Package~31bf3856ad364e35~amd64~~10.0.17134.1.mum

The file name will be different based on windows updates. So please update if requires.

How to Create Multiple SQL Server Instances on Same Server

When you install SQL Server, the installation creates an instance (DEFAULT OR SQLEXPRESS) of SQL Server. In my case I have created instanace named DEVSQL.

Some times we do need to create multiple instances based on requirements. To create more instances, start the setup again. The following screen appears when setup starts for the SQL Server 2012.

Click “New SQL Server stand-alone installation or add features to an existing installation”, the following screen appears:

Installation Start

As you can see DEVSQL instance already exists, it was created by the first installation, now select “New installation or add shared features”. Then click Next and accept the License Agreement.

Then select features you want to install for the new instance.

After that the installation offer you to specify the name of your new instance (MYTEST was chosen in this example):

Click Next to create the new instance and perform the remaining setup steps.

Now you are done!