How to edit more than 200 rows in SQL Server Management Studio 2008
There are two options to edit more than 200 rows in SQL Management Studio 2008 Option 1-changing the setting of 200 rows permanently: Tools–>options–>SQL Server object explorer –>CommandsEdit “Change Value for Edit Top <n> Rows Command” Option 2-changing the setting of 200 rows temporarily: Right-click Table–>click on Edit Top 200 Rows –>New Query window will be opened. You can change the SELECT TOP (n) statement. After changing it, click on the red exclamation mark (!) to update the selection. Disable “Prevent saving changes that require the table to be re-created” By default, Microsoft SQL Server Management Studio has the option “Prevent saving changes that require the table to be re-created” enabled. This causes an error when you want to save changes to a table that require the table to be dropped and re-created. To turn off the setting, go to (on the menu bar) Tools/Options/Designers/Table and Database Designers, then under the Table Options section, uncheck Prevent saving changes that require the table to be re-created. Have questions? Contact the technology experts at InApp to learn more.
Insert Generator Script
By executing the following stored procedure we can create insert statement for all records in a table EXECUTE [InsertGenerator] ‘tableName’. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[InsertGenerator] (@tableName varchar(max)) as –Declare a cursor to retrieve column specific information for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(max) –for storing the first half of INSERT statement DECLARE @stringData nvarchar(max) –for storing the data (VALUES) related statement DECLARE @dataType nvarchar(max) –data types returned for respective columns SET @string=’INSERT ‘+@tableName+'(‘ SET @stringData=” DECLARE @colName nvarchar(max) FETCH NEXT FROM cursCol INTO @colName,@dataType IF @@fetch_status<>0 begin print ‘Table ‘+@tableName+’ not found, processing skipped.’ close curscol deallocate curscol return END WHILE @@FETCH_STATUS=0 BEGIN IF @dataType in (‘varchar’,’char’,’nchar’,’nvarchar’) BEGIN –SET @stringData=@stringData+””””’+isnull(‘+@colName+’,””)+”””,”+’ SET @stringData=@stringData+””+”’+isnull(””’+””’+[‘+@colName+’]+””’+””’,”NULL”)+”,”+’ END ELSE if @dataType in (‘text’,’ntext’) –if the datatype is text or something else BEGIN SET @stringData=@stringData+””””’+isnull(cast([‘+@colName+’] as varchar(2000)),””)+”””,”+’ END ELSE IF @dataType = ‘money’ –because money doesn’t get converted from varchar implicitly BEGIN SET @stringData=@stringData+”’convert(money,”””+isnull(cast([‘+@colName+’] as varchar(200)),”0.0000”)+”””),”+’ END ELSE IF @dataType=’datetime’ BEGIN SET @stringData=@stringData+”’convert(datetime,’+”’+isnull(””’+””’+convert(varchar(200),[‘+@colName+’],121)+””’+””’,”NULL”)+”,121),”+’ END ELSE IF @dataType=’image’ BEGIN SET @stringData=@stringData+””””’+isnull(cast(convert(varbinary,[‘+@colName+’]) as varchar(6)),”0”)+”””,”+’ END ELSE –presuming the data type is int,bit,numeric,decimal BEGIN SET @stringData=@stringData+””+”’+isnull(””’+””’+convert(varchar(200),[‘+@colName+’])+””’+””’,”NULL”)+”,”+’ END SET @string=@string+'[‘+@colName+’],’ FETCH NEXT FROM cursCol INTO @colName,@dataType END DECLARE @query nvarchar(MAX) SET @query =’SELECT ”’+substring(@string,0,len(@string)) + ‘) VALUES(”+ ‘ + substring(@stringData,0,len(@stringData)-2)+”’+”)” FROM ‘+@tableName exec sp_executesql @query CLOSE cursCol DEALLOCATE cursCol Have questions? Contact the technology experts at InApp to learn more.
How to Clear SQL Server Transaction Log
In some cases, the Microsoft SQL Server Transaction Log (.LDF) file becomes very huge. It’s wasting a lot of disk space and causing some problems if you want to back up and restore the database. We can delete the log file and create a new log file with the minimum size. To delete SQL server transaction log files, follow the steps given below: Backup the database Detach the database Right-click on the database => Tasks => Detach 3. Delete or rename the big log file (path:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA) 4. Attach the database againRight-click Databases => Attach On Attach Database box, click Add… Browser to the database (.mdf) file then click OK Select the log (.ldf) file then click Remove Finally, click OK. You can see the new log file with the minimum size. Have questions? Contact the technology experts at InApp to learn more.