Search a column in a table

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%_ColumnName%'
ORDER BY schema_name, table_name

Cannot open database requested by the login. The login failed

This error occurs when you have configured your application with IIS, and IIS goes to SQL Server and tries to login with credentials that do not have proper permissions

1. Go to SQL Server > Security > Logins > right click on 'Your user error name' > Properties

In opened screen of Login Properties, go to the “User Mapping” tab.

2. Select map checkbox appropriate to your database, And change user dropdown to sa (if necessary)

3. Then change user membership to dbo.owner

Then click on OK

In almost all such cases, this should fix your problem.

 

 

PrimaryKey vs UniqueKey

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined.
Primary Key creates a clustered index on the column.
Primary Key does not allow Nulls.

CREATE TABLE EMP (eID INT NOT NULL PRIMARY KEY,
eName VARCHAR(100) NOT NULL)

Alter table with Primary Key:
ALTER TABLE EMP
ADD CONSTRAINT pk_eID PRIMARY KEY (eID)

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined.
Unique Key creates a non-clustered index on the column.
Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE EMP ADD CONSTRAINT UK_empName UNIQUE(empName)

Refer: https://www.simple-talk.com/sql/learn-sql-server/primary-key-primer-for-sql-server/

Update alternate rows in sqlserver

update s set recstatus=’I’ from
(select row_number() over (order by id) sno,* from mastertbl)s
where sno%2=0

or

with c as (
select row_number() over (order by id) sno,* from mastertbl
) update c set recstatus=’I’ where sno%2=0

Image binary format c#

source:

    <div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

        OnRowCommand="gv_command">

        <Columns>

            <asp:TemplateField HeaderText="files">

                <ItemTemplate>

                    <asp:LinkButton ID="l1" runat="server" Text='<%#Eval("filenam") %>' CommandArgument='<%#Eval("id") %>' CommandName="link"></asp:LinkButton>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="image">

                <ItemTemplate>

                    <asp:ImageButton ID="i1" runat="server" ImageUrl='<%#"Handler.ashx?id=" + Eval("id")  %>' CommandName="image" CommandArgument='<%#Eval("id") %>'

                    Visible='<%#Convert.ToString(Eval("isimage")).Trim()=="yes"?true:false %>' Height="50px" Width="50px" />

                </ItemTemplate>

            </asp:TemplateField>

        </Columns>

        </asp:GridView>

        <asp:FileUpload ID="FileUpload1" runat="server" />

        <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="b1_click"/><br />

        <asp:Image ID="Image1" runat="server" Height="300px" Width="300px"/>

    </div>

aspx.cs:

using System.Data.SqlClient;

using System.Data;

using System.IO;

    SqlConnection con = new SqlConnection("Data Source=.;database=db;UID=sa;Pwd=sa123");

    SqlDataAdapter da = new SqlDataAdapter();

    DataSet ds = new DataSet();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack) {

            GetData();

        }

    }

    private void GetData()

    {

        da = new SqlDataAdapter("select * from upld order by id", con);

        ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds;

        GridView1.DataBind();

    }

    protected void gv_command(object sender, GridViewCommandEventArgs e)

    {

        if (e.CommandName == "link")

        {

            string path = "";

            byte[] a = getbyte(e.CommandArgument.ToString(), out path);

 

            if (!File.Exists(Server.MapPath(path)))

                //File.Delete(Server.MapPath(path));

                File.WriteAllBytes(Server.MapPath(path), a);

            System.Diagnostics.Process.Start(Server.MapPath(path));

        }

        else if (e.CommandName == "image")

        {

            string path = "";

            byte[] a = getbyte(e.CommandArgument.ToString(), out path);

            string fil = path.Substring(path.LastIndexOf('.') + 1).ToUpper();

            string[] files = new string[] { "GIF", "PNG", "JPG", "BMP", "ICO" };

            if (files.Contains(fil))

            {

                MemoryStream ms = new MemoryStream(a);

                System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(ms);

                if (!File.Exists(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString())))

                    //File.Delete(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString()));

                    bmp.Save(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString()), System.Drawing.Imaging.ImageFormat.Jpeg);

                Image1.ImageUrl = "d/" + ds.Tables[0].Rows[0][1].ToString();

            }

        }

    }

 

    byte[] getbyte(string s, out string p)

    {

        da = new SqlDataAdapter("select top 1 filebin,filenam from upld where id=" + s, con);

        ds = new DataSet();

        da.Fill(ds);

        string fil = ds.Tables[0].Rows[0][1].ToString().Substring(ds.Tables[0].Rows[0][1].ToString().LastIndexOf('.') + 1);

        p = "d/" + ds.Tables[0].Rows[0][1].ToString();

        return (byte[])ds.Tables[0].Rows[0][0];

    }

    protected void b1_click(object sender, EventArgs e) 

    {

        FileUpload1.PostedFile.SaveAs(Server.MapPath(FileUpload1.FileName));

        string sFileName = Server.MapPath(FileUpload1.FileName);

        byte[] fileData = File.ReadAllBytes(Server.MapPath(FileUpload1.FileName));

        SqlCommand cmd = new SqlCommand();

        cmd.Parameters.Add(new SqlParameter("@filenam", sFileName));

        cmd.Parameters.Add(new SqlParameter("@filebin", fileData));

 

        string fil = FileUpload1.PostedFile.ContentType.ToString();

        fil = fil.Substring(fil.LastIndexOf('/') + 1).ToUpper();

        string[] files = new string[] { "GIF", "PNG", "JPG", "BMP","ICO" };

        fil = files.Contains(fil) ? "yes" : "no";

 

        cmd.CommandText = "insert into upld(path,filebin,filenam,isimage) values(@filenam, @filebin,'" + FileUpload1.FileName + "','" + fil + "')";

        cmd.Connection = con;

        con.Open();

        cmd.ExecuteNonQuery();

        File.Delete(Server.MapPath(FileUpload1.FileName));

        GetData();

    }

ashx.cs:

using System.Data;

using System.Data.SqlClient;

public void ProcessRequest (HttpContext context)

{

    SqlConnection con = new SqlConnection("Data Source=.;database=db;UID=sa;Pwd=sa123");

        SqlDataAdapter da = new SqlDataAdapter();

        DataSet ds = new DataSet();

 

        da = new SqlDataAdapter("select top 1 filebin from upld where id=" + context.Request.QueryString["ID"].ToString(), con);

        ds = new DataSet();

        da.Fill(ds);

        context.Response.BinaryWrite((byte[])ds.Tables[0].Rows[0][0]);

}

db: with auto increment id

Row to Column conversion separated delimiter in sql

declare @str1 varchar(max)=',,sql,,,asp,technique,yield,ado,,'
declare @str2 varchar(max)=''
create table #tbl1 (id varchar(max))

while(charindex(',',@str1)>0)
begin
if isnull(@str2,'')<>''
insert into #tbl1 select @str2
select @str2 = substring(@str1,1,charindex(',',@str1)-1)
set @str1 = substring(@str1,charindex(',',@str1)+1,len(@str1))
end

if isnull(@str2,'')<>''
insert into #tbl1 select @str2

select * from #tbl1
drop table #tbl1

Reset Identity for the table in sqlserver

CREATE TABLE IdentTest (Ident INT NOT NULL IDENTITY (1,1), varfield varchar(100))
INSERT INTO IdentTest VALUES ('abc')

DBCC CHECKIDENT ('IdentTest',RESEED,100)

-- here 100 starts identity from 101 if is there any data in table otherwise it starts from 100, for 1 you have to  use 0
INSERT INTO IdentTest VALUES ('def')

SELECT * FROM IdentTest
--Note: if the inserting with already existing identity, then it will raise the error.
--****first POST of the Year

Happy new Year with sqlserver query

SELECT CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15) + CHAR(15) + CHAR(15) + CHAR(15) + CHAR(13) + CHAR(72) + CHAR(65) + CHAR(80) + CHAR(80)+ CHAR(89) + ' ' + CHAR(78) + CHAR(69)+CHAR(87)+ ' ' + CHAR(89)+ CHAR(69)+ CHAR(65) + CHAR(82) +CHAR(13)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)

Get each char ASCII value from :

DECLARE @i int
SET @i = 1
WHILE @i < = 256 Begin PRINT CHAR(@i) + ' --> ' + CONVERT(VARCHAR,@i)
SET @i = @i + 1
END

***Last POST of the Year

Create a table named 'select' with column 'table'

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers

Reference: http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx

or

CREATE TABLE [SELECT] ([TABLE] INT)

Customize sql startup query window from:

C:\Users\My-PC\AppData\Local\VirtualStore\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql\SqlFile.sql

(Chclt)

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SqlFile.sql

Replace multiple spaces with one/none in sql server

select replace(replace(replace(LTrim(RTrim('      6      Spaces  6      Spaces.      ')),'  ',' |'),'| ',''),
--'|','')
' |','')

select replace(
replace(
replace(
LTrim(RTrim('      6      Spaces  6      Spaces.      '))
,' ',' |')
,'| ',''),
' |',' ')

--Trim the field
--Mark double spaces
--Delete double spaces offset by 1
--Tidy up

Convert column to row in SQL Server

select (select convert(varchar(max),id)+',' from #tbl1 for xml path('')) colTOrow

Query to attaching of database using only .mdf file without associated log file

CREATE DATABASE ... FOR ATTACH_REBUILD_LOG

with log file: CREATE DATABASE ... FOR ATTACH

Order of the sqlquery that logically processed

What is the order of execution in the following list?

• select


• from


• where


• group by


• order by


• having

A: from,where,group by,having,select,order by

ref:  http://msdn.microsoft.com/en-us/library/ms174149.aspx

Rename/Alter the column for a table in sqlserver

To add new column: Alter Table TableName add ColName dataType

To alter a column: Alter Table TableName Alter Column ColumnName DataType NULL/NOT NULL

To rename a column:

EXEC sp_rename
@objname = ' TableName. OldColumnName’,
@newname = 'New ColumnName',
@objtype = 'COLUMN'

note: computed columns(col3=col1+col2) doesnt hav a chance to rename, the only way is it has to be dropped and added back

Ex: Alter Table tblAddress Alter Column Address VARCHAR(512) NULL

------------------------------------------------------------------------------------------------

To rename the table name:

sp_RENAME 'TestTable', 'NewTable'

can u set innerjoin for update command?

Update Employee set State = s.StateID  
from Employee e  
inner join State s on e.State = s.StateName

Decide whether i/p parameter contains integers or not in sqlserver

IF PATINDEX('%[0-9]%',@val) > 0  
print 'i/p value contain integer'
else
print 'Not contain integer'

Insert records (multiple) into a table using single query in sqlserver

create table tblEmp (eid int, ename varchar(20), estatus bit)

sqlserver2005

basic: insert into tblEmp values (0,'emp', 'True')

insert into tblEmp select 1,'emp1','True'

union all select 2,'emp2','True'

union all select 3,'emp3','True'

sqlserver2008

insert into tblEmp values (1,'emp1','True'),(2,'emp2','True'),(3,'emp3','True')

If table not exist in database then:

select * into tblEmp from (select 1 eid,'emp1' ename,'True' estatus) tbltemp

SQL Command Types

DDL is Data Definition Language statements. Some examples:

• CREATE - to create objects in the database


• ALTER - alters the structure of the database


• DROP - delete objects from the database


• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed


• RENAME - raname column for a table


• COMMENT - add comments to the data dictionary

DML is Data Manipulation Language statements. Some examples:

• SELECT - retrieve data from the a database


• INSERT - insert data into a table


• UPDATE - updates existing data within a table


• DELETE - deletes all records from a table, the space for the records remain


• CALL - call a PL/SQL or Java subprogram


• EXPLAIN PLAN - explain access path to data


• LOCK TABLE - control concurrency

TCL is Transaction Control Language statements. Some examples:

• COMMIT - save work done


• SAVEPOINT - identify a point in a transaction to which you can later roll back


• ROLLBACK - restore database to original since the last COMMIT


• SET TRANSACTION - Change transaction options like what rollback segment to use

DQL/DRL is Data Query/Retrieve Language statements. Some examples:

• SELECT - retrieve data from the a database

DCL is Data Control Language statements. Some examples:

• GRANT - gives user's access privileges to database


• REVOKE - withdraw access privileges given with the GRANT command


• DENY - remove user's access privileges to database

select distinct of rows from a table of multiple duplicate columns

create table emp(empid int,empname varchar(50),sal money,status bit)

insert into emp select 1,'satya',20000,1 union all
select 1,'satya',20000,2 union all
select 1,'satya',20000,1000 union all
select 2,'sant',21000,-3 union all
select 3,'chclt',42000,0

alter table emp add cno int identity(1,1)

select empid, empname, sal from emp where cno in (select min(cno) from emp group by empid,empname,sal,comm,status)

output

empid empname sal
1           satya          20000
2           sant            21000
3           chclt           42000

alter table emp drop column cno

----------------------------------------------------

with x as(select *,row_number() over(partition by empid,empname,sal,comm,status order by empid) as count from emp)
delete from x where count>1