Thursday, April 11, 2019

Microsoft SQL Server - How to check all empty tables in database?

Process to check all empty tables in a Database:

1. Select the database in the SSMS and open the "New Query" window.
2. Run the below query.

SELECT sch.name as SchemaName, t.NAME AS TableName,  p.rows AS RowCounts
FROM   sys.tables t
INNER JOIN  sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN sys.schemas sch on t.schema_id = sch.schema_id
WHERE  t.NAME NOT LIKE 'dt%'   AND t.is_ms_shipped = 0   AND p.rows <=0
GROUP BY  sch.name,t.Name, p.Rows
ORDER BY  sch.name,t.Name

Friday, April 5, 2019

System.InvalidOperationException: 'String[n]: the Size property has an invalid size of 0.'


This error occurs generally when we are not passing the Output parameter in the SqlDbType.VarChar value like the below example

SqlParameter job1 = cmd2.Parameters.Add("@job", SqlDbType.VarChar);
job1.Direction = ParameterDirection.Output;

You need to define a length when specifying SqlDbType.VarChar parameter, So

SqlParameter job1 = cmd2.Parameters.Add("@job", SqlDbType.VarChar, 50);
You should use the same length as defined in your SQL Server stored procedure.

How to Import and Export SSL Certificates in IIS 7

How to Import and Export SSL Certificates in IIS 7

This Article Explains how to export your certificate from working server, import the certificate to another server, and then enable the certificate for use on the new server.
Exporting/Backing Up to a .pfx File
  1. On the Start menu click Run and then type mmc.
  2. Click File > Add/Remove Snap-in.
  3. Click Certificates > Add.
  4. Select Computer Account and then click Next. Select Local Computer and then click Finish. Then close the add standalone snap-in window and the add/remove snap-in window.
  5. Click the + to expand the certificates (local computer) console tree and look for the personal directory
    Warning: Do not select the delete private key option.
  6. Leave the default settings and then enter your password if required.
  7. Choose to save the file and then click Finish. You should receive an "export successful" message. The .pfx file is now saved to the location you selected.
Importing from a .pfx File
  1. On the Start menu click Run and then type mmc.
  2. Click File > Add/Remove Snap-in.
  3. Click Certificates > Add.
  4. Select Computer Account and then click Next. Select Local Computer and then click Finish. Then close the add standalone snap-in window and the add/remove snap-in window.
  5. Click the + to expand the certificates (local computer) console tree and look for the personal directory/folder. Expand the certificates folder.
  6. Right-click on the certificate you want to backup and select ALL TASKS > Import.
  7. Follow the certificate import wizard to import your primary certificate from the .pfx file. When prompted, choose to automatically place the certificates in the certificate stores based on the type of the certificate.
Installing the Certificate with Website
  1. On the Start menu click Run and then type inetmgr.
  2. In the IIS Manager, click the server name.
  3. Expand the sites folder.
  4. Select the site that you want to secure (usually the default website).
  5. On the actions menu in the edit site section, click Bindings.
  6. In the site bindings window, click Add. If a binding for https already exists, select the https binding and click Edit.
  7. Fill out the information in the add site binding window. In the type drop-down choose https. Set the IP address to the IP address of the site or choose all unassigned. The port for SSL traffic is usually 443. Enter the recently imported certificate in the SSL Certificate field.
  8. Click OK. Your SSL Certificate is now installed and the website is configured to accept secure connections. You may have to restart IIS or the server for it to recognize the new certificate.

Saturday, March 30, 2019

HTTPS with Visual Studio's built-in ASP.NET Development Server


You need to enable the SSL in the project by using the below steps:
  • Enable the SSL with in IIS Express, you have to just set “SSL Enabled = true” in the project properties window.
  • Then you can start your project with IIS Express normally and it will start using SSL, the new Port will be 44301
Note : https and http both ports are generally different. By default SSL enabled is set to false with IIS Express; so if you run your web application first time, you will only be able to find the default URL and you can access the site over http.

Thursday, March 14, 2019

Ms Sql Server - The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib)

SSMS Error: "The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib)"

When you are trying to open Query or execute query in Microsoft SQL Server Management Studio, you may see the following message:

The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib).

Cause: Sometimes cache of the Sql Server corrupted due to Updates in the visual studio or Sql-Server components updates.

Solution : 

STEP 1: Delete the Temp Files. Shortcut to delete the temp files


STEP 2: Restart Microsoft SQL Server Management Studio and the error message will have disappeared.



A network-related or instance-specific error occurred while establishing a connection to SQL Server - microsoft sql server error 2


SSMS Error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server"

When you are trying to connect to a SQL Server using Microsoft SQL Server Management Studio, you may see the following message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that the SQL Server is configured to allow remote connections. (provided: Named Pipes Provider, error: 40- Could not open a connection to the SQL Server) (Microsoft SQL Server, Error: 2).

While the message tells you to check some of the items that can cause this issue, it fails to mention that if your SQL Server Service is not started, then you cannot connect. One of the first things that you should check is that the SQL Server (MSSQLSERVER) is started. You can go to the Services Console (services.msc) and look for SQL Server (MSSQLSERVER) to see that it is started. If not, then start the service.

You could also do this through an elevated command prompt by typing net start mssqlserver.


Thursday, February 28, 2019

WooCommerce Order Cancelled After Successful Payment: Unpaid Order Cancelled – Time Limit Reached

After upgrading to WooCommerce 2.0 and Above, you have experienced the issue  that all the purchased order changes to the “canceled” status. 
In the admin area, all the orders appear as “canceled”  with the message “Unpaid order canceled – time limit reached“. When this happens, you need manually change the statuses of all the canceled orders.
The problem arises due to the Hold Stock feature in WooCommerce Inventory management. In this feature, you can set a certain period of time to hold the stock for an unpaid order.
To Resolve the issue, Go to the admin side of your WordPress store, and navigate to
 WooCommerce > Settings > Products > Inventory.
From this page, you can see that the Hold Stock field is set to 60 minutes by default. If you are using WooCommerce stock management, you can disable this feature by leaving the field blank. Simply delete the value in the field and then save the changes.



Tuesday, February 26, 2019

Sql Server - Create a copy of database and Renaming it

Steps to attach a database with different name:

  1. Detach database from local PC
  2. Copy the mdf and ldf files and rename them.
  3. Copy\Move the files to the server directory where the original Database is stored.
  4. Use the below code
USE [master]
GO
CREATE DATABASE [example_db] ON ( FILENAME = N'D:\Data\example_db.mdf' ),
( FILENAME = N'E:\Logs\example_db_log.ldf' )FOR ATTACH
GO


you can also copy by using the Sql-server Management studio:

  • go to database and tasks->Copy Database
  • Select the source and destination server (same if you wants to make a copy on the same server)
  • change the destination database name to required.
  • Done

Friday, February 15, 2019

Wordpress - How to revert to classic editor / old editor?

How to revert to classic editor / old editor?

if you wants to move back to the old Classic Editor By WordPress Contributors, please install the Plugin
https://wordpress.org/plugins/classic-editor/

Classic Editor is an official plugin maintained by the WordPress team that restores the previous (“classic”) WordPress editor and the “Edit Post” screen. It makes it possible to use plugins that extend that screen, add old-style meta boxes, or otherwise depend on the previous editor.


This plugin also gives you an option to keep both Gutenberg and the Classic editor at the same time. You can configure this in the plugin’s settings.

Simply go to the Settings » Writing page in your WordPress admin area. You will see the option to do this under “Classic editor settings”.
Classic editor settings

Don’t forget to click on the save changes button to store your settings.

You will now be able to see a new link under the Posts menu to create new posts using the classic editor.
Switch to classic editor

You will also be able to edit older articles using the classic editor. Simply go to Posts » All Posts page and you’ll see a ‘Edit (Classic)’ link below posts.
Edit classic