Tag: MSSQL

  • SQL Managemnet Studio (SSMS)- Retrieve Unsaved Scripts

    Sometimes when using Sql Management Studio, it get crashed or accidentally close the query tab by using close button. If SSMS is crashed then on re-start it some time shows a window for recover unsaved script. Or do using SQL from its history using: SELECT txt.TEXT AS [SQL Statement], qs.LAST_EXECUTION_TIME AS [Last Time Executed]FROM SYS.DM_EXEC_QUERY_STATS…

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

  • How to rename column at MSSQL

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

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

    [SQL] 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…

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

  • Get rows count of the tables – Microsoft SQL Server

    [sql] CREATE TABLE #counts ( table_name varchar(255), row_count int ) EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT ”?”, COUNT(*) FROM ?’ SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC DROP TABLE #counts [/sql]