Thursday, May 15, 2008

Useful Tips From Me

Group Policy Object Editor:

Click->start->run->gpedit.msc





To increase BSNL BroadBand Connection Speed :


Click Start-->Run-->type "gpedit.msc" without the "
This opens the group policy editor. Then go to:
Local Computer Policy-->Computer Configuration-->Administrative Templates-->Network-->QOS Packet Scheduler-->
Limit Reservable Bandwidth
Double click on Limit Reservable bandwidth. It will say it is not configured, but the truth is under the 'Explain' tab :

"By default, the Packet Scheduler limits the system to 20 percent of the bandwidth of a connection, but you can use this
setting to override the default."
So the trick is to ENABLE reservable bandwidth, then set it to ZERO.
This will allow the system to reserve nothing, rather than the default 20%.


To display Image In Grid View:

Open Handler.ashx and type the code


To Open Handler Vs2008->File->new Project->Generic Handler

And Then Type The Code

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.IO;
using System.Configuration;
using process;

public class Handler : IHttpHandler
{
public conn con = new conn();

public void ProcessRequest (HttpContext context)
{
string imageid = context.Request.QueryString["img"];
con.opencon();
SqlCommand command = new SqlCommand("select pro_img from source where sl="+imageid, con.con);
command.CommandType = CommandType.Text;
SqlDataReader dr = command.ExecuteReader();
dr.Read();
context.Response.BinaryWrite((Byte[])dr[0]);
con.closecon();
context.Response.End();
//context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");

}


public bool IsReusable
{
get
{
return false;
}
}

}


And Add The Query String Like This




ImageUrl='<%# "Handler.ashx?img="+ Eval("sl") %>' />







To Block a site in Computer:

Start->run->c:/windows/system32/drivers/etc/hosts

Open Notepad:

And Then Type after

127.0.0.1 Localhost
127.0.0.1 www.website.com

TO Find Foriegnkey and PrimaryKey in database:


use databasename
go
SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

To Find Primary key in a database:


use databasename
GO
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Useful Tips From Mer

Tuesday, May 13, 2008

Export And Import .Xls File

To Export .xls file to sql :
sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
insert into openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=d:\new.xls','select * from [sheet1$]')select * from test.dbo.tablename

To import sql to .xls file:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=d:\new.xls

Sqlserver Queries From Me:

Create a database:
create database databasename

Drop a database:
Drop database databasename

Create a table in database:
create table tablename(i int,name varchar(20))

Drop a table in database:

drop table tablename

Update a column in a table:
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value


Delete a column in a table:

DELETE FROM table_name
WHERE column_name = some_value


Insert values into table:
insert into tablename values('20','suba','shshs')
Another Method:
insert tablename select('20','sjs','sss')

Backup database:

backup database databasename to disk ='e:data.bak'

Restore Database:
restore database databasename from disk ='e:data.bak'

Wait For Delay to Do the Transaction

begin tran
select * from tablename
waitfor delay '00:00:05'
select * from tablename1
commit

To get the current server time:
select Right (convert(varchar(20),getdate(),100),7)

Find Monday of the Current Week:
select dateadd(wk,datediff(wk,0,getdate()),0)Mondayofcurrentweek



Insert a .txt file in a table:
bulk
insert tablename
from 'e:\test.txt'
with
{
field terminator=','
row terminator='\n'
}

Rename a column name in table:
sp_rename'tablename.[oldcolumnname]',newcolumnname,'column'

Rename a tablename:
sp_rename 'oldtablename', 'newtablename'





Find A particular table in database :

SELECT *
FROM sys.Tables
WHERE name LIKE '%tablename%'

Create a database:

create database databasename