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

Import CSV using “Microsoft Access Database Engine 2010” to SQL Server


sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

SET NOCOUNT ON

EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo [Test.txt] > C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo ColNameHeader=True >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Format=Delimited(^|) >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo MaxScanRows=50 >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo CharacterSet=OEM >> C:\Temp\Schema.ini'

EXEC master..xp_cmdshell 'echo Col1=ID TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col2=LastName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col3=FirstName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col4=MiddleName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col5=MailingAddressLine1 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col6=MailingAddressline2 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col7=MailingAddressCity TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col8=MailingAddressState TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col9=MailingAddressZIP TEXT >> C:\Temp\Schema.ini'

EXEC ('INSERT INTO ImportTest (ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP)
SELECT ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\Temp\;HDR=YES'',''select * from person.txt'')
')
EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'

How to Backup Microsoft SQL Server (MSSQL) Databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
 
-- 2. Setting the backup path

SET @path = 'X:\MSSQLDB\Backup\'  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- ignore system databases

--3. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

  -- 4. Defining the filename
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName 

    FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor