Software Applications Programming Errors and solutions.

How to create sitemap runtime in asp.net // begin program abc // declare namespace using System.Xml; using System.Text; using System.Data.SqlClient; //declare class public partial class sitemap : System.Web.UI.Page { SqlConnection cn; protected void Page_Load(object sender, EventArgs e) { try { cn = new SqlConnection("Your connection string"); string mydomain = "your domain name"; string strSql = "select abc from test"; SqlDataAdapter dacontent = new SqlDataAdapter(strSql, cn); DataSet dscontent = new DataSet(); dacontent.Fill(dsd, "SiteMap"); //Now we are going to create XML file using XMLTextWriter XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.UTF8); writer.WriteStartDocument(); writer.WriteStartElement("urlset", "url"); writer.Writ...

Restore database through query in sql Just try to execute following query to restore database using query analyzer or from network location. RESTORE DATABASE testdb FROM DISK = 'C:\folder1\testdb.bak' WITH REPLACE, MOVE 'testdb_Data' TO 'C:\folder1\testdb.mdf', MOVE 'testdb_Log' TO 'C:\folder1\testdb.LDF' If you want to restore database by code then you can try following SQL Code to restore database . Use master go RESTORE DATABASE [SID] FROM disk = N'\\SERVERNAME\SFILE.bak' WITH FILE = 1, NORECOVERY, REPLACE, NOUNLOAD, STATS = 10 go RESTORE LOG [SID] FROM DISK = N'\\SERVERNAME\SFILE.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO sp_change_users_login @Action='Report' Attach mdf data base to new database in sql To Attach mdf data base to new database in sql You can follow following steps. 1 Go to enterprise manager, expand tree till Database 2 Right click on...

Query to delete duplicate records from table using sql select * from test where exists (select * from test as t where test.id=t.id having count(*)>1 and min(t.id) !=test.id.); How to Remove Duplicates from a Single Column SQL Table To Remove Duplicates from a Single Column Table in sql you can use temparary table concept and can try this way lock tables Table1 write; drop temporary table if exists TestTmp; begin work; create temporary table TestTmp select distinct * from Table1; delete from Table1; insert into Table1 select * from TestTmp; unlock tables; commit; delete all the duplicate rows in the table of SQL database If the table is not having an unique column you can use the below: DELETE FROM _Table1 WHERE _Table1.ID IN -- List 1 - all rows that have duplicates (SELECT F.ID FROM _Table1 AS F WHERE Exists (SELECT Field1, Field2, Count(ID) FROM _Table1 WHERE _Table1.Field1 = F.Field1 AND _Table1.Field2 = F.Fi...

This is a Sql stored procedure which can be used to change column name from lower case to upper in tables. Its simple to use and understand. DECLARE @count INT, @script NVARCHAR(1000), @column_name_old VARCHAR(100), @column_name_new VARCHAR(100) DECLARE @table TABLE (nid INT IDENTITY(1,1), column_name VARCHAR(1000)) INSERT INTO @table(column_name) SELECT name FROM syscolumns WHERE id = OBJECT_ID('test_column_change') SELECT @count= count(1) FROM @table WHILE(@count >=1) BEGIN SELECT @column_name_old = column_name FROM @table WHERE nid = @count SELECT @column_name_new = UPPER(@column_name_old) SELECT @Script ='sp_rename ''test_column_change.'+@column_name_old+''' , '''+@column_name_new+''' , ''COLUMN''' --select @script EXEC (@Script) SELECT @Count = @count-1 END

Set Auto Increment of column which is varchar type in sql If you want to Set Auto Increment of column which is varchar type in sql table then you can do that by using following way CREATE TABLE StudentTable ( Regid int IDENTITY(1,1) NOT NULL , StudentCol nvarchar(3) DEFAULT 'STD' , StudentId AS (StudentCol+ REPLICATE('0', 10-LEN(CONVERT(nvarchar(100),Regid )) ,OtherColumnsYouNeed nvarchar(MAX) )