LL – Laxmi Lal Menaria Blog!

Category: MSSQL

  • SQL Managemnet Studio (SSMS)- Retrieve Unsaved Scripts

    While working with SQL Server Management Studio (SSMS), we can have crash issues. This causes loss of our scripts / queries / query windows data. Sometimes we get “Not responding” error message and restarted again after automatically closed. We can’t stop this. This will be frustrating to re-write all statements again. So, there are some…

  • How to list SQL Jobs using Query?

  • 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…

  • 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…

  • Replace Blank CELLs with NULL Value

    SELECT ‘UPDATE table SET [‘ + name + ‘] = NULL WHERE [‘ + name + ‘] = ””;’ FROM syscolumns WHERE id = object_id(‘table’) AND isnullable = 1;

  • How to rename column at MSSQL

    Use sp_RENAME EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’

  • How to ALTER all tables columns in SQL Database

  • Exception- The definition for user-defined data type ‘MyTypeList’ has changed.

    The definition for user-defined data type ‘MyTypeList’ has changed. Exception when we rename or changed any User defined TYPE and it used in stored procedures. Stored Procedures which is using this type variable will not run and throwing the exception. If we have large numbers of SP then it will to time consuming or irritating…

  • Search in MSSQL tables

    Sometime we need to reverse engineering in the database tables to find a specific values, we can find the column name easily but in large database, to search a specific key value is too hard. From below query we can easily search value in all tables, it will list column with table in the result.…

  • How to read DBF to Datatable

    [csharp] OleDbConnection oConn = new OleDbConnection(@”Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp;Extended Properties=dBase III”); OleDbCommand command = new OleDbCommand(“SELECT * FROM Test.DBF”, oConn); oConn.Open(); DataTable dt = new DataTable(); dt.Load(command.ExecuteReader()); oConn.Close(); [/csharp]