N a g a s a i: Tips and Tricks

Showing posts with label Tips and Tricks. Show all posts

Wednesday, March 13, 2024

How to Review transaction order and lock acquisition in SQL Server

In SQL Server, you can review the transaction order and lock acquisition by analysing the queries and transactions that are being executed against the database. Here are some approaches to review transaction order and lock acquisition:

Transaction isolation levels:
- Review the transaction isolation levels used in your database transactions. Isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable can impact the order of lock acquisition and the behaviour of concurrent transactions.
Query execution plans:
- Use SQL Server Management Studio (SSMS) or other database management tools to analyse the query execution plans for your transactions.
- The execution plans can provide insights into the order in which data is accessed and the types of locks acquired during query execution.
Locking and blocking:
- Monitor and analyse the locking and blocking behaviour of concurrent transactions using tools like SQL Server Profiler, Extended Events, or dynamic management views (DMVs) such as sys.dm_tran_locks and sys.dm_os_waiting_tasks.
- Identify instances of blocking and analyse the lock types and resources involved to understand the order of lock acquisition.
Transaction log and history:
- Review the transaction log and history to understand the sequence of transactions and their impact on lock acquisition.
- SQL Server's transaction log and history can provide valuable information about the order in which transactions are executed and their associated locks.

By using these approaches, you can gain insights into the transaction order and lock acquisition behaviour in SQL Server, which can help in identifying potential issues related to deadlocks, blocking, and overall transaction concurrency.

Monday, March 11, 2024

Convert String to Title case using Javascript

Here is the function to convert string to title case, which can handle spaces and underscores. Below function will remove underscores from the string.

// Import the function
function convertToTitleCase(input) {
  return input.toLowerCase().replace(/_/g, ' ').replace(/\b\w/g, function(match) {
    return match.toUpperCase();
  });
}

You can call the convertToTitleCase function in HTML by including a script tag with the function definition, and then using JavaScript to call the function and display the result.

Here's an example of how you can call the convertToTitleCase function in HTML:

<!DOCTYPE html>
<html>
<head>
  <title>Convert to Title Case</title>
</head>
<body>

<p id="output"></p>

<script>
// Function definition
function convertToTitleCase(input) {
  return input.toLowerCase().replace(/_/g, ' ').replace(/\b\w/g, function(match) {
    return match.toUpperCase();
  });
}

  // Call the function and display the result
  let input = "Nagasai_Srinivas_Mudara";
  let convertedString = convertToTitleCase(input);
  document.getElementById("output").innerHTML = convertedString;
</script>

</body>
</html>

In this JavaScript function, the replace method is used with a regular expression to match the underscores and lowercase letters and convert the lowercase letters to uppercase when preceded by an underscore or at the beginning of the string.

You can use the convertToTitleCase function to convert any input string to title case in a generic and reusable way.

Wednesday, March 06, 2024

How to implement retry logic for DB Transactions

In SQL Server, you can implement retry logic for transactions using T-SQL and error handling. Here's an example of how you can create a stored procedure that includes retry logic for handling deadlock errors:

CREATE PROCEDURE usp_RetryTransaction
AS
BEGIN
    DECLARE @retryCount INT = 0
    DECLARE @maxRetries INT = 3

    WHILE @retryCount < @maxRetries
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION
            -- Your transactional logic goes here
            COMMIT TRANSACTION
            RETURN
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() = 1205  -- Deadlock error number
            BEGIN
                ROLLBACK TRANSACTION
                SET @retryCount = @retryCount + 1
                WAITFOR DELAY '00:00:01'  -- Wait for 1 second before retrying
            END
            ELSE
            BEGIN
                -- Handle other types of errors
                THROW
            END
        END CATCH
    END
    -- If the maximum number of retries is reached, handle the situation as needed
    -- For example, raise an error or log the issue
END

In this example, the stored procedure attempts the transaction logic within a retry loop, and if a deadlock error (error number 1205) occurs, it rolls back the transaction, increments the retry count, and waits for a short duration before retrying the transaction. If the maximum number of retries is reached, you can handle the situation as needed based on your application's requirements.

You can then call this stored procedure whenever you need to perform a transaction with retry logic for deadlock handling.

Sunday, March 03, 2024

How to find a view in database where its used in SQL Server

To find where a specific view is used in a SQL Server database, you can query the system catalog views. Here's a query to achieve this:

SELECT 
    referencing_schema_name, 
    referencing_entity_name
FROM 
    sys.dm_sql_referencing_entities('YourSchema.YourView', 'OBJECT');

Replace YourSchema with the schema of your view and YourView with the name of the view you want to find. This query will return the schema and name of the objects that reference the specified view.

Execute this query in your SQL Server management tool to find where a specific view is used in your database.

Hope this help!!

Thursday, September 14, 2023

How to locate and replace special characters in an XML file with Visual C# .NET

We can use the SecurityElement.Escape method to replace the invalid XML characters in a string with their valid XML equivalent. The following table shows the invalid XML characters and their respective replacements

Character Name	Entity Reference	Character Reference	Numeric Reference
Ampersand	&	&	&#38;
Left angle bracket	<	<	&#60;
Right angle bracket	>	>	>
Straight quotation mark	"	"	'
Apostrophe	'	'	"

Sample Usage of this Escape method.

//Usage
srtXML = SecurityElement.Escape(strXML);

For this you need to import System.Security namespace. Alternatively you can also use this simple replace method with all special characters in a single method like below

public string EscapeXml(string s)
{
    string toxml = s;
    if (!string.IsNullOrEmpty(toxml))
    {
        // replace special chars with entities
        toxml = toxml.Replace("&", "&amp;");
        toxml = toxml.Replace("'", "&apos;");
        toxml = toxml.Replace("\"", "&quot;");
        toxml = toxml.Replace(">", "&gt;");
        toxml = toxml.Replace("<", "&lt;");
    }
    return toxml;
}

Hope this is useful!

Tuesday, September 12, 2023

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

The "Microsoft.ACE.OLEDB.12.0" provider is a database connection manager that allows access to various databases, including Microsoft SQL Server, Oracle, and IBM DB2. If the provider is not registered on the local machine, it may be due to a missing or corrupted database redistribution package, or a problem with the Windows registry. You can try reinstalling the package or repairing the registry to resolve the issue.

If you have built your project under x86 platform, then in order to resolve you issue you should install the following packages on your machine:

In order to use the 'Microsoft.ACE.OLEDB.12.0' provider you must install the Microsoft Access Database Engine 2010 Redistributable first, this installation is available at: http://www.microsoft.com/download/en/details.aspx?id=13255 .

After the installation has complete, try running you application

Depending on the app(32/64bit) using the connection you could just install

Hope this helps!

Thursday, July 27, 2023

Moving Google Chrome Profiles to a New Computer

Are you tired of juggling between Incognito tabs or re-entering credentials and MFA codes every time you manage different client's Office 365 environments in Chrome? Discover the power of Chrome profiles, or "People," which allows you to efficiently manage multiple client environments simultaneously and retain your authentication sessions even after closing the browser window.

In this guide, we'll walk you through the step-by-step process of migrating Chrome profiles, ensuring a seamless transition to a new computer without losing any crucial data.

Step 1: Backing Up Chrome Profiles To start the migration process, we first need to back up the Chrome profiles on the computer where they are currently stored. Follow these steps:

Navigate to this path on your computer: C:\Users\%username%\AppData\Local\Google\Chrome\
Locate and copy the "User Data" folder, which contains all the necessary profile data.

Additionally, we need to export a specific registry key that holds essential information related to the profiles:

Press "Win + R" to open the Run dialog box, then type "regedit" and hit Enter.
In the Registry Editor, go to [HKEY_CURRENT_USER\Software\Google\Chrome\PreferenceMACs].
Right-click on "PreferenceMACs" and select "Export."
Save the exported registry key to the same portable media where you stored the "User Data" folder.

Step 2: Moving Chrome Profiles to a New Computer Now that you have your Chrome profile data backed up on portable media, let's proceed with the migration on your new computer:

Ensure that all Chrome browser windows are closed, and no instances of "chrome.exe" are running in the background.
Copy the "User Data" folder from the portable media to this path on your new computer: C:\Users\%username%\AppData\Local\Google\Chrome\
Double-click the exported registry key that you saved to the portable media during Step 1. This will merge the key into your new computer's registry.

Step 3: Embrace the Seamless Experience Congratulations! You've successfully migrated your Chrome profiles to the new computer. Now, open Chrome, and you'll find all your profiles conveniently present and ready to use. No more hassle of logging in multiple times or losing authentication sessions when switching between clients' Office 365 environments.

Final Thoughts: Chrome profiles, or "People," offer a powerful solution for managing different client environments efficiently. By following these simple steps, you can seamlessly migrate your Chrome profiles to a new computer without losing any crucial data. Embrace the convenience and organization that Chrome profiles bring to your workflow and say goodbye to unnecessary logins and wasted time. Enhance your productivity and enjoy a smooth browsing experience with Chrome profiles today!

Happy browsing!

Sunday, June 18, 2023

How to implement impersonation in SQL Server

To implement impersonation in SQL Server, you can follow these steps:

1. Create a Login:
First, create a SQL Server login for the user you want to impersonate. Use the `CREATE LOGIN` statement to create the login and provide the necessary authentication credentials.

Example:

CREATE LOGIN [ImpersonatedUser] WITH PASSWORD = 'password';

2. Create a User:
Next, create a user in the target database associated with the login you created in the previous step. Use the `CREATE USER` statement to create the user and map it to the login.

Example:

CREATE USER [ImpersonatedUser] FOR LOGIN [ImpersonatedUser];

3. Grant Permissions:
Grant the necessary permissions to the user being impersonated. Use the `GRANT` statement to assign the required privileges to the user.

Example:

GRANT SELECT, INSERT, UPDATE ON dbo.TableName TO [ImpersonatedUser];

4. Impersonate the User:
To initiate impersonation, use the `EXECUTE AS USER` statement followed by the username of the user you want to impersonate. This will switch the execution context to the specified user.

Example:

EXECUTE AS USER = 'ImpersonatedUser';

5. Execute Statements:
Within the impersonated context, execute the desired SQL statements or actions. These statements will be performed with the permissions and privileges of the impersonated user.

Example:

SELECT * FROM dbo.TableName;
-- Perform other actions as needed

6. Revert Impersonation:
After completing the necessary actions, revert back to the original security context using the `REVERT` statement. This will switch the execution context back to the original user.

Example:

REVERT;

By following these steps, you can implement impersonation in SQL Server. Ensure that you grant the appropriate permissions to the user being impersonated and consider security implications when assigning privileges.

Here is the full syntax:

EXECUTE AS LOGIN = 'DomainName\impersonatedUser'
EXEC  uspInsertUpdateGridSettings @param1, @param2
REVERT;

Additionally, be mindful of auditing and logging to track and monitor impersonated actions for accountability and security purposes.

Tuesday, June 06, 2023

Find tables or procedures that are associated in SQL Jobs via Query

Recently we need to look for a procedure where we are using in SQL Jobs. There is no easy way to find unless you script all jobs and find in the script.

But there is some easy way to find it using below query. You could also might have similar ask to find a procedure or table that you might have used in SQL Jobs in any of those steps. It could be any string like comment, procedure, function or table, this below query works.

USE msdb
GO

SELECT [sJOB].[job_id] AS [JobID]
	,[sJOB].[name] AS [JobName]
	,step.step_name AS JobStepName
	,step.command AS JobCommand
	,[sJOB].enabled AS ActiveStatus
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].dbo.sysjobsteps step ON sJOB.job_id = step.job_id
WHERE step.command LIKE '%uspPopulateAggregatorUsageData%' ----You can change here what you are searching for
ORDER BY [JobName]

Thank you

Friday, June 02, 2023

How to get comma separated values from SQL

There are few types where you can get comma separated values form SQL SERVER using SQL

1. XML PATH method:

SELECT 
   STUFF((SELECT ', ' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 2, '') AS csv_values;

2. COALESCE and FOR XML method:

SELECT 
   STUFF((
      SELECT ', ' + column_name
      FROM table_name
      WHERE conditions
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS csv_values;

3. FOR XML PATH method

SELECT 
   STUFF((SELECT ',' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 1, '') AS csv_values;

In these examples, replace column_name with the actual column name and table_name with the appropriate table name. Customize the WHERE clause to filter the desired rows if necessary.

When executing these queries, a single row with a single column will be returned, containing the comma-separated values from the specified column. Please note that the XML-related methods convert the values to XML and then manipulate them, resulting in a string of comma-separated values.

Hope this helps!!

Wednesday, May 31, 2023

How to Move Offline Outlook Data File (OST) Data

To move an Offline Outlook Data File (OST) data, you can follow these steps:

Close Outlook and make sure it is not running in the background.
Locate the OST file on your computer. By default, it is located in the following location: Windows 10, 8, 7: C:\Users%username%\AppData\Local\Microsoft\Outlook Windows Vista: C:\Users%username%\Local Settings\Application Data\Microsoft\Outlook
Copy the OST file to a new location, such as a USB drive or a different folder on your computer.
Open Outlook and go to File > Account Settings > Account Settings.
Select the email account that corresponds to the OST file you just moved and click Change.
In the Change Account window, click More Settings.
Go to the Advanced tab and click the Browse button next to the Offline Folder File Settings.
Select the OST file you just moved and click OK.
Click Next and Finish to complete the process.

It's important to note that when you move your OST file, you will lose any cached data, such as emails and calendar items, that have not been synced with the server.

Thursday, January 26, 2023

How do you auto format code in Visual Studio?

Visual Studio 2019 & 2022

Format Document, While you're holding down Ctrl button, first press K then D
Format Selection, While you're holding down Ctrl button, first press K then F

or just click Edit => Advanced => Format Document / Format Selection

Happy formatting!!

Saturday, November 19, 2022

How to Delete “BIN and OBJ” folders via Batch file

We had issues deleting bin and obj folders when we check in code or share code with in teams.

Here is simple batch file that we can use to delete Bin and Obj folders.

1. Create an empty file and name it DeleteBinObjFolders.bat

2. Copy-paste the below code into the DeleteBinObjFolders.bat

@echo off
@echo Deleting all BIN and OBJ folders...
for /d /r . %%d in (bin obj) do @if exist "%%d" rd /s/q "%%d"
@echo BIN and OBJ folders successfully deleted.
pause > nul

3. copy this file to your solution (*.sln) location.

4. Go to your project folder via command prompt and run this file DeleteBinObjFolders.bat file which is in the same folder with your solution (*.sln) file.

Hope this helps!

Wednesday, June 02, 2021

How can we bulk close issues on JIRA?

Here is how we can bulk close JIRAs

Step 1: Search all the issues/stories you wan to close (Using JQL or predefined filter of your own), something like below.

Step 2: Choose the issues that needs to be closed.

Step 3: Choose Operation, Since we are closing, we need to select transition issues and go to next.

Step 4: Choose the operation from available workflow actions

Step 5: Here I have choosed Resolved as i am closing these issues.

Step 6: Select Resolved and proceed further.

Step 7: Add comments (optional) and choose resolution

Step 8: Choose confirm to bulk close all the tickets.

Easy right!!

Tuesday, June 01, 2021

Visual Studio 2019 Tips and Tricks

Visual Studio 2019 has a new feature with visibility into how an external library or dependency resource is handling the data you are giving it can provide valuable insight. Debugging decompiled resources is a great feature that allows to step into external libraries.

Select Tools > Options. Type “decompile” into the search bar. The Advanced section of Text Editor for C# will appear. Click on Advanced.

Check the box that says Enable navigation to decompiled sources (experimental).

Now you can debug and step into the external packages you pull in from Nuget and elsewhere!

Happy Debugging!!

Friday, June 08, 2018

Fun with Keyboard Shortcut Keys

Ctrl+Alt+Down Arrow. This key command can magically rotate your monitor screens to 180 degrees. It would be very interesting to see someone try to log in to their PC and be surprised by an upside down screen. It would be pretty funny to see someone get dumbfounded by this shortcut. But do keep in mind is that this key only works with computers who have Intel graphics chipsets and may not work on all PCs.
Left Alt + Left Shift + Print Screen. This amusing shortcut is a great way to both irritate and puzzle your friends; this command turns on the high contrast visibility setting on any computer set.
Ctrl + F4. You can use this shortcut to sneak-up and wreck havoc on your pals who are working on just about any MS Office program. This shortcut immediately exits the document that is currently running and will most probably upset the victim. So be ready to run away just in case he or she gets violent.
Windows + M. This command is a bit more advanced than the one above, because aside from shutting your current program, it also closes all your opened programs in a blink of an eye. Again, be ready to run or to defend yourself when executing this prank.
Windows + D then Alt + F4 then press enter. This command is at the top rung when it comes to instantly closing programs. What does it do? Well, it just closes any version of windows as soon as you enter the shortcut, which makes it a very effective pal irritant.
Ctrl + H. If you are at your friend’s place and have access to his or her computer, you can use this keyboard shortcut to get easy access to his or her browsing history. Be warned though, that this can reveal way too much information about your friend, so be ready for anything before trying this out.
Left Alt + Left Shift then enter. This command will make your Num Lock key beep every time it is pressed. It doesn’t do any harm but it can make your friend’s computer seem broken by giving off an odd beep.
Windows key + “+” then Ctrl + Alt + F. This shortcut makes your screen bigger, which can annoy users who prefer a certain screen size.
PRT SC, Ctrl+V to make an unclick-able desktop. Press “Print Screen” on your keyboard (PRT SC) to make a screenshot of your desktop. Now go to Paint and Paste using (Ctrl+V). You will now have an image of your desktop which you must then save to your PC (not on your desktop) and save it as BMP. You’ll now have the file like this: C:/Desktop.bmp. Next, right-click on your desktop and select Properties. Select the tab Desktop and click Browse… Navigate to your screenshot and press OK to set it as your background. Right-click your desktop again and navigate to “Arrange icons by” and uncheck “Show desktop icons”. Your icons are now effectively unclickable, and the next user will probably give up in frustration.
Windows key + “+” then windows key + “-” then Ctrl + Alt + I. This shortcut can turn the colors on your monitor screen upside down; it’s another great prank to make it seem that the computer is busted.

Friday, April 20, 2018

Duplicate Menu Items in Visual Studio

Recently I had a situation with Visual Studio 2010 where menu items where duplicated like 3-4 times. Looks like some configuration file was corrupted. I have tried to restore current settings and setup new settings but none worked out.

To resolve this I ran the following from command line

devenv.exe /safemode /setup

Once this ran, I restarted Visual Studio I could able to see Visual studio with default factory settings.

PS: If you have any personal settings done before you have to redo those settings again.

Wednesday, January 31, 2018

How to get the IP Address of a Remote Socket Endpoint

Here is how we can IP address, you need to use below namespace library

using System.Net.Sockets;

IPHostEntry ipHostInfo1 = Dns.GetHostEntry(Dns.GetHostName());
// loop through list of system IP address 
// get the IP4 Address of the current machine
foreach (IPAddress ipaddr in ipHostInfo1.AddressList)
{
    if (ipaddr.ToString() == ConfigurationManager.AppSettings["SystemIPAddress"].ToString())
    {
        strIpAddress = ipaddr.ToString();
        break;
    }

}

                
IPAddress ipAddress = IPAddress.Parse(strIpAddress);

Hope this helps!

Thursday, December 15, 2016

How to turn your Windows 10 PC into a wireless hotspot

Windows 10 includes a feature called "Hosted Network" that allows you to turn your computer into a wireless hotspot.

Whether you're connecting to the internet using a wireless or wired adapter, similar to previous versions, Windows 10 allows you to share an internet connection with other devices with a feature called "Hosted Network".

Hosted Network is a feature that comes included with the Netsh (Network Shell) command-line utility. It's was previously introduced in Windows 7, and it allows you to use the operating system to create a virtual wireless adapter – something that Microsoft refers to "Virtual Wi-Fi" — and create a SoftAP, which is a software-based wireless access point.

Here is nice tutorial that is use full with step by step process. Blogger has given full details for setting up this process

Friday, November 04, 2016

Outlook: Copy email signatures to another computer

Recently i have switched my computers i could able to get outlook backup files abut not signatures. But i found a way to get all previous signatures to new computer.

Here is how you can do it, If you have created email signatures that you add to outgoing messages, these signatures can be copied to another computer. You can then use the signatures with Outlook on another computer.

Step 1: Copy email signatures from the original computer

Exit Outlook.
Open the folder where signatures are saved.

Because the Signatures folder is a hidden folder, the easiest way to open the folder is to use the command %APPDATA%\Microsoft\Signatures on the Start menu.

On Windows 7/8.1/10, Click Start. Next to the Shut down button, in the Search programs and files box, type %APPDATA%\Microsoft\Signatures and then press Enter.
Windows Vista Click Start. Next to the Shut Down button, in the Search box, type %APPDATA%\Microsoft\Signatures and then press Enter.
Windows XP Click Start, click Run, type %APPDATA%\Microsoft\Signatures and then press Enter.

Copy the signature files.
There are three files for each email signature — an HTML Document (.htm), Rich Text File (.rtf), and Text Document (.txt).

Step 2: Copy signatures to the destination computer

Exit Outlook.
On the destination computer, open the folder %APPDATA%\Microsoft\Signatures.

Because the Signatures folder is a hidden folder, the easiest way to open the folder is to use the command %APPDATA%\Microsoft\Signatures on the Start menu.

Windows 7 Click Start. Next to the Shut down button, in the Search programs and files box, type %APPDATA%\Microsoft\Signatures and then press Enter.
Windows Vista Click Start. Next to the Shut Down button, in the Search box, type %APPDATA%\Microsoft\Signatures and then press Enter.
Windows XP Click Start, click Run, type %APPDATA%\Microsoft\Signatures and then press Enter.

Start Outlook.
Your stationery is now available in Outlook on the destination computer.

Step 3: Update older signatures

For signatures created in older versions of Outlook, you should update the signature files so that they use revised HTML code that uses cascading style sheets (CSS). This helps prevent potential problems when switching or deleting signatures in a message.

Click the File tab.
Click Options.
Click Mail.
Under Compose messages, click Signatures.
In the Signatures and Stationery dialog box, in the Select signature to edit list, click a signature.
Click in the Edit signature box, and then click Save.

Hope this helps!!!

Character Name	Entity Reference	Character Reference	Numeric Reference
Ampersand	&	&	&#38;
Left angle bracket	<	<	&#60;
Right angle bracket	>	>	>
Straight quotation mark	"	"	'
Apostrophe	'	'	"

Character Name	Entity Reference	Character Reference	Numeric Reference
Ampersand	&	&	&#38;
Left angle bracket	<	<	&#60;
Right angle bracket	>	>	>
Straight quotation mark	"	"	'
Apostrophe	'	'	"

Character Name	Entity Reference	Character Reference	Numeric Reference
Ampersand	&	&	&#38;
Left angle bracket	<	<	&#60;
Right angle bracket	>	>	>
Straight quotation mark	"	"	'
Apostrophe	'	'	"