The title says it all. Given that I have created a login for a domain group, and a database user for that login. What I want to do is retrieving the domain username for the active user. USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.
The goal is to use domain group logins, while still allowing for logging what user performed which action.USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.
Really?
When I query suser_sname() while logged in under a windows domain account (using a windows domain group login) I get my DOMAIN\UserName.
Regards,
hmscott
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Friday, March 30, 2012
How can I retrieve Description and other column information?
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...type
sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...
You need to look up
fn_listextendedproperty
Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.
Check this table:
select * from sysproperties|||Yup... Works fine.
Thanks Brett..|||select * from sysproperties
Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.
Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.
declare @.tbid integer
declare @.colid integer
Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'
Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering
Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'
-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description
You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...type
sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...
You need to look up
fn_listextendedproperty
Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.
Check this table:
select * from sysproperties|||Yup... Works fine.
Thanks Brett..|||select * from sysproperties
Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.
Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.
declare @.tbid integer
declare @.colid integer
Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'
Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering
Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'
-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description
You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object
How can I retrieve data?
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.SoftID uniqueidentifier
AS
SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @.SoftID)
RETURN
where I retrieve data using sqldatasource, an error appear. how can do ?
(2) you have declared @.parameter2 as OUTPUT type but you dont seem tobe returning anything through it. So you can get rid of it.
How Can I retrieve data from a self joined table
hi all
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada V
It may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegr oups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>
|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***
|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***
|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) ?
|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)
|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))
sql
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada V
It may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegr oups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>
|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***
|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***
|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) ?
|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)
|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))
sql
How Can I retrieve data from a self joined table
hi all
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada VIt may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegroups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) '|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada VIt may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegroups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>|||hi Amish
thanks for ur reply
but,for ur first query
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID
its just listing all the records
and for ur second query
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID
but whats it meant by max(employeename) its giving the salary field
correctly
but it only selects the first employeename not the required employeename
the required empname is the employeename corresponding to max salary in
that particular dept
Thanks and regards
Sarada v
*** Sent via Developersdex http://www.codecomments.com ***|||hi
this is the table structure
PKEmployeeID bigint
EmployeeName varchar
FKDeptID bigint
Salary decimal
PhoneNumber varchar
FKEmployeeID bigint
and i dont know anything about creating functions and all those stuff
i am loooking for a simple query
Can u help me plz
Thanks and Regards
Sarada V
*** Sent via Developersdex http://www.codecomments.com ***|||Try this: ("nested table" techniqe)
select emp.employeename, maxsal.salary1, maxsal.fkdeptid from
employee_sarda,
( Select max(salary) as salary1, FKDeptID from employee_sarda
group by FKDeptID ) as maxsal
where
emp.FKDeptid = maxsal.FKDeptid
emp.salary = maxsal.salary
Note: If 2 or more guys of a dept. earn the same maximum, you'll get
back all of them.
Is this from some exam :-) '|||Or this ("correlated subquery")
select emp.employeename, emp.salary, emp.fkdeptid from
employee_sarda emp
where salary =
(select max(salary) from employee_sarda maxsal
where emp.fkdeptid = maxsal.fkdeptid)|||sorry other option may be like this
select salary , employeename, fkdeptid from employee_sarda e where
salary in (select max(salary) from employee_sarda t where e.fkdeptid =
t.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sarda e where
exists (select max(salary),fkdeptid from employee_sarda t group by
fkdeptid having max(t.salary)= e.salary and e.fkdeptid = t.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Sorry other option may be like this
select salary , employeename, fkdeptid from employee_sharda e where
salary in (select max(salary) from employee_sharda t1 where t1.fkdeptid
= e.fkdeptid )
or
select salary , employeename, fkdeptid from employee_sharda e where
exists (select max(salary),fkdeptid from employee_sharda t1 group by
fkdeptid having max(t1.salary)= e.salary and t1.fkdeptid = e.fkdeptid )
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||Hello Amish,
Great ones, too.
Shalll we now elaborate on the "query plans" of the optimizer for all
these
to determine which is the "quickest" (best ?) :-))
How Can I retrieve data from a self joined table
hi all
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada VIt may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegroups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>
i have one table with the format
PKEmployeeID PrimaryKey
FKDeptID
Salary
EmployeeName
FKEmployeeID
here I have to retrieve the max salary by dept wise
thats ok by writing
Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
How can i retrieve the employeename for the max salary
Thanks and Regards
Sarada VIt may be possible in same FKDeptID two or more employee have same
salary which is max in that FKDeptID.
Select max(salary), employeename, FKDeptID from employee_sarda group by
employeename,FKDeptID.
or
if you want only one name then
Select max(salary), max(employeename), FKDeptID from employee_sarda
group by FKDeptID.
Please post ddl with data to get better idea.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||Hi
Can you provide us with your table's stucture?
--Written by Itzik Ben-Gan
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
If it doesnot help , pls show us an expected result
"pandu" <saradabhanuv@.gmail.com> wrote in message
news:1135835404.968676.316380@.g14g2000cwa.googlegroups.com...
> hi all
> i have one table with the format
> PKEmployeeID PrimaryKey
> FKDeptID
> Salary
> EmployeeName
> FKEmployeeID
> here I have to retrieve the max salary by dept wise
> thats ok by writing
> Select max(salary),FKDeptID from Employee_Sarada group by FKDeptID
> How can i retrieve the employeename for the max salary
> Thanks and Regards
> Sarada V
>
How can I retrieve a recordset from a matrix-like table?
I have a table defined as (int Row, int Column, money Data); as it were a matrix.
Which is the more efficient way of retrieving a result set with the following form?
Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...
Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.
Which is the more efficient way of retrieving a result set with the following form?
Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...
Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.
how can i retrieve a pdf file from the database?
i'm working with a project right now that needs pdf file retrieval from the database with the use of a search engine... any codes that could make that search engine to function properly? and make it work?
Hi
There are some threads about search pdf file .I am not sure if they are helpful ,anyway you can take a look:
http://forums.asp.net/thread/1107159.aspx
http://forums.asp.net/thread/745532.aspx
http://forums.asp.net/thread/850295.aspx
How can I retrieve a function return value from an executed string?
Hello!
Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?
I tried the following, but it did not work:
declare @.Receptor as sql_variant
set @.Receptor=execute('select ' + @.Function + '(10)')
Thanks a lot in advance.The function is not completely qualified (DB name, owner)
Try:
declare @.Receptor as sql_variant
set @.Receptor = database_name.dbo.Function(@.in_param_value)
A user defined function can be used like any other pre-defined function
Originally posted by EMoscosoCam
Hello!
Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?
I tried the following, but it did not work:
declare @.Receptor as sql_variant
set @.Receptor=execute('select ' + @.Function + '(10)')
Thanks a lot in advance.sql
Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?
I tried the following, but it did not work:
declare @.Receptor as sql_variant
set @.Receptor=execute('select ' + @.Function + '(10)')
Thanks a lot in advance.The function is not completely qualified (DB name, owner)
Try:
declare @.Receptor as sql_variant
set @.Receptor = database_name.dbo.Function(@.in_param_value)
A user defined function can be used like any other pre-defined function
Originally posted by EMoscosoCam
Hello!
Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?
I tried the following, but it did not work:
declare @.Receptor as sql_variant
set @.Receptor=execute('select ' + @.Function + '(10)')
Thanks a lot in advance.sql
How can i restrict the SA user
hi,
Is there any way to restrict the SA access. The problem is that all my
client machine users knows the sa password, and also right now i don't want
to change the password. So i just want to know is there any way to restrict
their access ? thanking in advance
ARHi
SA is not restrictable as it is the highest built in security account.
If security is so important, you have to change the password. Remember, if
you are using integrated security, domain and local admins are by default,
mapped to SA.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
>|||"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
Make a new user and give him an appropriate permissions.
regards,
Dra
Is there any way to restrict the SA access. The problem is that all my
client machine users knows the sa password, and also right now i don't want
to change the password. So i just want to know is there any way to restrict
their access ? thanking in advance
ARHi
SA is not restrictable as it is the highest built in security account.
If security is so important, you have to change the password. Remember, if
you are using integrated security, domain and local admins are by default,
mapped to SA.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
>|||"Aneesh" <aneesh.r@.eostek.com> wrote in message
news:u4iPss30EHA.2608@.TK2MSFTNGP10.phx.gbl...
> hi,
> Is there any way to restrict the SA access. The problem is that all my
> client machine users knows the sa password, and also right now i don't
want
> to change the password. So i just want to know is there any way to
restrict
> their access ? thanking in advance
> AR
Make a new user and give him an appropriate permissions.
regards,
Dra
How can I restrict bandwidth between to SQL servers
Hi
I have two SQL2000 servers in different sites, once a day approximately
1M of data in the form of a large update is required to be transfered
between the 2. We have use of a 2M pipe between the servers but there
is no quality of service, the other users on the pipe are traders so
there must be no interruption in the quality of their bandwidth at any
time.
I have two SQL2000 servers in different sites, once a day approximately
1M of data in the form of a large update is required to be transfered
between the 2. We have use of a 2M pipe between the servers but there
is no quality of service, the other users on the pipe are traders so
there must be no interruption in the quality of their bandwidth at any
time.
Is there any way of throttling back the data transfer between the two
servers to restrict its bandwidth use. Obviously we want to retain the
max bandwidth on our local network.
The pipe is administered by a seperate company so we do not have admin
access to their gateways, routers etc.. so a solution which we can
implement on our database servers would be the easiest.
I am not sure if this is the right newsgroup for this but any
information would be great
Thanks
MarkMSSQL itself doesn't have any way to manage this - you would have to
work it out at the OS or network level. From the database side you
could update only a few rows at a time, with pauses in between, but
it's not a great solution and wouldn't be suitable if you need
everything in a transaction. Or perhaps export the data to flat files,
send them in chunks, then reassemble them and do the update on the
server, but again that seems very clumsy.
You might also want to post in a networking group - there may be an
OS-level solution which would do what you need,
Simon
How can I restore the db successfully
I'm a new user to SQL Server.
I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea about why it prompt error when I click OK and say :
Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
1st Step: New a Database (no change to the setting)
2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give me.?
Best regards,
Grace
Hi,
How can I choose the backup database file that someone give me.?
In the Restore databaase window -- Select the option button "From Device" ,
Click the "Select Device" -- Click "Add"
and in the file name get the .BAK file from the directory and click "OK".
There you select Backup Number "View contents" . This
will show you all the files associated with the backup file.
If you have mutiple files select the one you require and click "OK" to
restore.
Cannot find file ID 2 on device c: ?
I feel that this Backup is taken in mutiple files (Devices) and you have
only one file. In that case you cant restore till u get the other file.
Thanks
Hari
MCDBA
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a
database. But I have no idea about why it prompt error when I click OK and
say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL
Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can
I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the
backup database file that someone give me.?
> Best regards,
> Grace
|||Grace,
The restore dialog in EM can use the backup history produced when you took the backup. But that doesn't
necessarily reflect what you actually have on the backup files. In this case, it seems like the backup history
say that there should be at least two backups on the backup file, and there isn't. Perhaps the backup file
doesn't exist at all...
If you want to restore a backup for a database which doesn't exists, *do not* create the database first. If
you do, you will most probably not create it with the same file structure as you had when you took the backup.
Let the restore process create the database for you. Just enter the restore dialog, type in the database name,
and select "from device". Here you can specify the backup file from which you want to do the restore.
I strongly suggest you read about how backup and restore work in Books Online, without that knowledge, you
might find above a bit difficult to understand. Also, I find it easier to work with the BACKUP and RESTORE
commands from Query Analyzer instead of using a GUI like EM. At least until you have good understanding about
the architecture of backup and restore and then understand more of how it work and what EM does when you
select options in the GUI. Just my opinion, that is...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea
about why it prompt error when I click OK and say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE
is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give
me.?
> Best regards,
> Grace
|||You are trying to restore from database, which uses the history of backups. The history path is different from the path of the file on the disk you are trying to restore from.
To restore from the file you have, you should click the bullet marked device (change from database) and browse to the actual file system file you are trying to restore the database to(via select devices... then Add...button).
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea about why it prompt error when I click OK and say :
Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
1st Step: New a Database (no change to the setting)
2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give me.?
Best regards,
Grace
Hi,
How can I choose the backup database file that someone give me.?
In the Restore databaase window -- Select the option button "From Device" ,
Click the "Select Device" -- Click "Add"
and in the file name get the .BAK file from the directory and click "OK".
There you select Backup Number "View contents" . This
will show you all the files associated with the backup file.
If you have mutiple files select the one you require and click "OK" to
restore.
Cannot find file ID 2 on device c: ?
I feel that this Backup is taken in mutiple files (Devices) and you have
only one file. In that case you cant restore till u get the other file.
Thanks
Hari
MCDBA
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a
database. But I have no idea about why it prompt error when I click OK and
say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL
Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can
I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the
backup database file that someone give me.?
> Best regards,
> Grace
|||Grace,
The restore dialog in EM can use the backup history produced when you took the backup. But that doesn't
necessarily reflect what you actually have on the backup files. In this case, it seems like the backup history
say that there should be at least two backups on the backup file, and there isn't. Perhaps the backup file
doesn't exist at all...
If you want to restore a backup for a database which doesn't exists, *do not* create the database first. If
you do, you will most probably not create it with the same file structure as you had when you took the backup.
Let the restore process create the database for you. Just enter the restore dialog, type in the database name,
and select "from device". Here you can specify the backup file from which you want to do the restore.
I strongly suggest you read about how backup and restore work in Books Online, without that knowledge, you
might find above a bit difficult to understand. Also, I find it easier to work with the BACKUP and RESTORE
commands from Query Analyzer instead of using a GUI like EM. At least until you have good understanding about
the architecture of backup and restore and then understand more of how it work and what EM does when you
select options in the GUI. Just my opinion, that is...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea
about why it prompt error when I click OK and say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE
is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give
me.?
> Best regards,
> Grace
|||You are trying to restore from database, which uses the history of backups. The history path is different from the path of the file on the disk you are trying to restore from.
To restore from the file you have, you should click the bullet marked device (change from database) and browse to the actual file system file you are trying to restore the database to(via select devices... then Add...button).
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
how can i restore if my dropped a wrong table and recreated it?
Is it possible to restore it if i dropped a wrong table and recreated it?
can i restore the whole table without backups?
thanks a lot..If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup t
he transaction log and then use
your latest db backup and then all subsequent backups to restore up until ju
st before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool
, but all of them requires that
the transaction log records are still inside the transaction log. See my web
-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...[colo
r=darkred]
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>[/color]|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>
can i restore the whole table without backups?
thanks a lot..If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup t
he transaction log and then use
your latest db backup and then all subsequent backups to restore up until ju
st before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool
, but all of them requires that
the transaction log records are still inside the transaction log. See my web
-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...[colo
r=darkred]
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>[/color]|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>
how can i restore if my dropped a wrong table and recreated it?
Is it possible to restore it if i dropped a wrong table and recreated it?
can i restore the whole table without backups?
thanks a lot..
If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup the transaction log and then use
your latest db backup and then all subsequent backups to restore up until just before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool, but all of them requires that
the transaction log records are still inside the transaction log. See my web-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>
|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>
sql
can i restore the whole table without backups?
thanks a lot..
If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup the transaction log and then use
your latest db backup and then all subsequent backups to restore up until just before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool, but all of them requires that
the transaction log records are still inside the transaction log. See my web-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>
|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>
sql
How can I restore a SQL 6.5 database backup to a SQL 7.0/2000 server?
Thanks,
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> Pedro Rodrigues
> pedro@.markdata.pt
>
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> Pedro Rodrigues
> pedro@.markdata.pt
>
How can I restore a SQL 6.5 database backup to a SQL 7.0/2000 server?
Thanks,
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...
quote:
> Thanks,
> Pedro Rodrigues
> pedro@.markdata.pt
>
How can I restore a SQL 2005 database on my SQL 2000?
My customer has SQL Server 2005. I have SQL Server 2000 on my laptop (on XP Pro). I believe I have an empty database the same as theirs, but I need to restore their data. They sent me a normal SQL Backup, but my laptop threw a fit and cannot work with it.
They are now trying to export the database to Microsoft Access and send me that, but I won't get that until next week now. However is there a better and easier solution for those of us with insufficient experience of this, please?
Many thanks,
CasparThere is no way to restore SQL 2005 backup on SQL 2000.
Easiest way in this case is - install Express Edition of SQL 2005, restore your backup there and then import it to SQL 2000.|||Or you might consider skipping the import to SQL 2000, and just work on it using SQL 2005 Express on your laptop.
-PatP|||Thank you so much for this advice. I am in the process of trying it, having found downloads at: http://msdn.microsoft.com/vstudio/express/sql/download/ where I am offered 2 choices - I have chosen the left-hand pair of files for a simpler setup.
If I am successful in restoring the database to this then if I do decided I want to import it into SQL 2000 then how to I go about that, please?
Many thanks,
Caspar
They are now trying to export the database to Microsoft Access and send me that, but I won't get that until next week now. However is there a better and easier solution for those of us with insufficient experience of this, please?
Many thanks,
CasparThere is no way to restore SQL 2005 backup on SQL 2000.
Easiest way in this case is - install Express Edition of SQL 2005, restore your backup there and then import it to SQL 2000.|||Or you might consider skipping the import to SQL 2000, and just work on it using SQL 2005 Express on your laptop.
-PatP|||Thank you so much for this advice. I am in the process of trying it, having found downloads at: http://msdn.microsoft.com/vstudio/express/sql/download/ where I am offered 2 choices - I have chosen the left-hand pair of files for a simpler setup.
If I am successful in restoring the database to this then if I do decided I want to import it into SQL 2000 then how to I go about that, please?
Many thanks,
Caspar
How can I restore a field back to Null?
Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan
How can I restore a field back to Null?
Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql
How can I restore a field back to Null?
Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan
Subscribe to:
Posts (Atom)