Information

When we try to open a large Sql file in SQl Server, it's gives the error "The operation could not be completed. not enough storage is available to complete this operation The template specified cannot be found. Please check that the full path is correct" Solution: use the  sqlcmd  tool to execute the file. To execute a SQL script: Start the Command Prompt Run the below command   sqlcmd -S DatabaseServer -d Database -i C:\Users\Administrator\Downloads\script.sql -x

Error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe" Solution: This is login authenticate problem. Please check the following things: In the SQL Server Properties, please check that "SQL Server and Windows Authentication Mode" is enabled, if not enable, please enable and restart the SQL Server.   Is Login have the permission to particular Database?   This might be a firewall issue as you mentioned it only happens when security agent installed. Please make sure you have properly configured security agent to allow Database Engine Services accesses.  

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

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.

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.

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 (MSSQLSERVE...

Steps to attach a database with different name: Detach database from local PC Copy the mdf and ldf files and rename them. Copy\Move the files to the server directory where the original Database is stored. 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

Transfer logins and passwords to destination server (Server A) using scripts generated on source server (Server B) To create a script on the source Server, follow these steps: On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database. Open a new Query Editor window, and then run the following script. USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLO...

Steps to recover MS SQL-server Database from "suspect "mode: EXEC sp_resetstatus ' DatabaseName '; ALTER DATABASE DatabaseName SET EMERGENCY DBCC checkdb(' DatabaseName ') DBCC CHECKCATALOG(' DatabaseName ') ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB (' DatabaseName ', REPAIR_ALLOW_DATA_LOSS ) ALTER DATABASE DatabaseName SET MULTI_USER ALTER DATABASE DatabaseName SET ONLINE;