Posted on

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]
Posted on

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?

Posted on

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

Posted on

How to enable “Windows Authentication” for your websites?

There are few things which we need to do before access the website using windows authentication.

Web.config:

Add or replace existing Authentication Tag

IIS Express with Visual Studio:

  • Click on your project in the Solution Explorer to select the project.
  • Open Properties pane suing F4.
  • In the Properties pane for your project:
    a) Set “Anonymous Authentication” to “Disabled”.
    b) Set “Windows Authentication” to “Enabled”.

IIS 7 or later:

  • Open IIS Manager and navigate to your website.
  • In Features View, double-click Authentication.
  • On the Authentication page, select Windows authentication. If Windows authentication is not an option, you’ll need to make sure Windows authentication is installed on the server.

To enable Windows authentication on Windows Desktop:

  • In Control Panel open “Programs and Features”.
  • Select “Turn Windows features on or off”.
  • Navigate to Internet Information Services > World Wide Web Services > Security and make sure the Windows authentication node is checked.

To enable Windows authentication on Windows Server:

  • In Server Manager, select Web Server (IIS) and click Add Role Services.
  • Navigate to Web Server > Security and make sure the Windows authentication node is checked.
Posted on

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.
Posted on

Remove Empty Directories using c#

private static void DeleteEmptyDirectory(string startLocation)
{
foreach (var directory in Directory.GetDirectories(startLocation))
{
DeleteEmptyDirectory(directory);
if (Directory.GetFiles(directory).Length == 0 &&
Directory.GetDirectories(directory).Length == 0)
{
Directory.Delete(directory, false);
}
}
}

Posted on

To turn on BitLocker Encryption in Windows 10/11.

  • You must be login using Administrator Account.
  • Click Start button, and then under Windows System, select Control Panel. In Control Panel, select System and Security, and then under BitLocker Drive Encryption, select Manage BitLocker.
  • Select Turn on BitLocker and then follow the instructions.
  • Make Sure you Save the Key.
Posted on

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