::::::::::::::::::::::::::::COMPUTERLEADERS::::::::::::::::::::::::::Who Leads The Compute

Triggers and Auto serial number in sqlserver

2008-08-12T03:17:00.000-07:00

-------------------TRIGGER CONCEPTS-----------------------

create trigger Prevent1(Trigger name)
on database
for Create_Table
as
Print 'U cant create the table'
rollback

-----For Enabling Creating Table
disable trigger Prevent1 On database

---------For Disabling Creating Table

enable trigger Prevent1 On database

----------------------------Using Trigger U can stop creating Prodedures also

------------------------Automatic Generaion Number Using query in a table
select row_number() over(order by column_name1) as SR ,* from table_name

Student Laptop Scheme 2008 by ELCOT ~ Digital Dreams

2008-07-10T22:48:00.000-07:00

Student Laptop Scheme 2008 by ELCOT ~ Digital Dreams

Useful Tips From Me

2008-05-15T02:31:00.001-07:00

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

2008-05-15T02:31:00.000-07:00

Export And Import .Xls File

2008-05-13T00:35:00.000-07:00

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:

2008-05-13T00:19:00.001-07:00

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:

2008-05-13T00:19:00.000-07:00

create database databasename