Back devices: 2012

Friday, March 30, 2012

How can I retrieve domain username for group login?

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

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

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 ?

(1) your sql in your stored proc is wrong. You are using the parameter@.softId in your sql which is not in the parameter list in your procdefinition. either you would need to change the parameter name from@.parameter1 to @.softid or the otherway.
(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

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 ?) :-))

How Can I retrieve data from a self joined table

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.

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?

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

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

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.

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...

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?
>

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

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
>

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...

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

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

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

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

How Can I Restore a Database to Different Files and ...............

Hi,

I have a database that over time has become spread over different files, file groups all of various sizes.

I want to restore this database to a different set of files/filegroups and evenly spread.

It appears that I can only resotore a database to number/of and size of files from which it was backed up..

I want to redistribute a 40GB file, using EMPTY is taking for ever and then eventually fails.

What can I do?

Thanks for your help

Try adding several new data files, then doing a shrink-empty on the big file to get it to be spread out to the new files. If the big data file is your primary data file, that will not work.

Suggestion two is to Rebuild the clustered index for several of your larger tables into the new files. This will move the data.

How can I restore a database from a UNC?

It appears that Management Studio will not allow you to perform a restore from a UNC path. Is this true or am I missing the option to open the magic UNC door? I am very new to SQL2005 and this was buried in SQL2000, but it was possible to do in Enterprise Manager.

Have you tried using the raw TSQL query to start the restore, and not use the GUI Interface.

|||

Not to be snotty, but you didn't answer the question. Your point is taken, there are other ways to do this. I can use a script to restore from a UNC path. I am still interested in getting an answer to the question I posted, not to the underlying assumption that I am looking for any way to accomplish a restore from UNC.

As a DBA, I do many operations by script; however, there are times that, for a number of reasons, the UI is a better option. Since this was something that could be done in SQL2000 through the UI, I thought that perhaps Microsoft would also provide a means to do it through the UI in 2005. We have non-technical folks who need to do restores from different sources for demos. It is more practical to teach them how to use the UI to grab the backup they need for their laptop rather than scripting the numerous possibilities or having them alter a script.

So, if anyone knows if Management Studio can be used to do a UNC restore, please share with me how it can be done.

|||Hi,

this is sure possible: Select Restore Files and Filegroup --> Fome Device --> Add --> Enter a Full qualified name in the filename textbox like \\m2-jenss\SomeShare\Somebakfile.bak

and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You nailed it. I initially tried that, but permissions foiled me, but I misread it as a limitation of the UI because the Selected Path pointed to a local drive and the field was disabled. Apparently, anything you put in File name will override the Selected Path entry if using the UNC path. Thanks a ton!

How can I resolve this database error, in VS 2005?

Every time I try to add a new row to my table, i get this error which i don't now what it means and how i can correct it, could you please advice. i am using VS 2005 and VB Language

**************The error message

"No row was updated.

The data in row 2 was not committed.

Error source:mscorlib.

Error Message: Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index"

What code are you using to add the row?

This is the basic VB syntax

Dim myNewRow as DataRow

myNewRow=MyDataTable.NewRow()

|||

Yep. What kind of table? A table in a sql server database, or a datatable, which is a .net class?

|||

i am using sql server database

How can i reset the Primary key field numbers to zero before uploading my site to the prod

I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.DBCC CHECKIDENT (tableName, RESEED, 0)|||

usingtruncate tablewill help you.

thanks,

satish.

sql

How can I reset a database surrogate PK using SSIS?

I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.

How can I do that using SSIS package?

thanks,

Aref

A quick Google revelas that you can use DBCC CHECKIDENT to do this

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

You can issue the command using the Execute SQL Task.

-Jamie

how can i reserve a record to prevent anyone else from editing for X minutes?

Hey All,

I think this is something like locking, but not quite. I need to select a record in a database and display the information to the end user. The end user has a few minutes to review it before giving the ok at which point I will update the record.

However, while the user is reviewing the record, i dont want anyone else to be able to grab that record. I also want to make sure that if the user doesnt submit the page, that the record will be freed up and not indefinitely marked as taken.

What would be the best way to do this? This is with .net 2.0 and sql2000

I am afraid you want to prevent the following problem:

User A retrieves a record and it takes a long time before he submit and during that time User B updated that record so when user submit he will overwrite the record.

You can solve this problem by using the following way:

When a user submits a record you check the database to see if it has been updated by other users if so stop updating, display the updated record and ask him to try again.

Hope it helps.

|||

Thats not really an ideal solution since it means the user may fill out all their info, hit submit and then find out everything they submitted is now invalid.

However, i got some advice from the aspadvice.com ,mailing lists which seem like a good idea. I can add a date field to the table called LastLocked - and when i do my select, i only grab records where the lock is older then X minutes. I can also add a field LockedBy so that the user can access their own locked records. I think this will work for me.

thanks

How can I request row #3 in a dataset?

I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, ElevationFROM [PointTable]WHERE (RouteID = 5)

But I cannot (or do not know how to) add a clause that says

AND (Num = 3)

So can someone show me how to request a specific row?

It's really easy, and the trick to it will serve you in good stead in many other situations.

When you issue a select statement, you are selecting "from" something. That something might be a table, or it might be a view.

But what IS a view? It's a query that's given a name, an alias if you will. When a view is used, the actual query statement it represents is substituted for the view's name in the sql statement as part of the parsing process.

How does that knowledge help?

Because you don't have to bother to give a view a name, you can just fill in a query for yourself. Just pop in inside a set of parentheses and supply an alias

So:

SELECT *

FROM

(

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, Elevation
FROM [PointTable]
WHERE (RouteID = 5)

) AS temporary_result_set

WHERE temporary_result_set.Num = 3

|||WOW! Thank you David, that did the trick. You're explaination just doubled my understanding on how T-SQL works and got my mind thinking of other things to try.

How can i reprogram this? It's taking forever to run.

I need to rewrite a sp. I'm pasting the major part of the sp down here. Also
,
if possible, can you tell me which columns to index? Thank you.
DECLARE @.procAsset int,
@.procStartDate datetime,
@.procEndDate datetime
DECLARE assetdate_cur CURSOR FOR
SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)
)
+ '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
StartDate
FROM mapITAsset2ExecutablesUsage
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
OPEN assetdate_cur
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
--Delete old data for the day from the table
DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
@.procAsset AND RunDate = @.procStartDate
INSERT INTO mapITAsset2ExecutablesUsageSummary
(ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
RunDate)
SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
WHEN SUM(Secs) IS NULL THEN 0
ELSE SUM(Secs)
END, @.procStartDate
FROM
(
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND UsageEndDateTime >= @.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
@.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
@.procEndDate AND UsageEndDateTime >= @.procStartDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
) AS Totals
GROUP BY ExecutableRepositoryID, UserID
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
END
CLOSE assetdate_cur
DEALLOCATE assetdate_cur
This sp actually summarizes thes data but it just takes forever to
run...(more than 10 hours)
Can someone give me a hint on how can i reprogram this? Thank youYou've got to get rid of the cursor, bottom line. Take out the subquery in
your INSERT INTO...SELECT statement. Put that data into a temp table and
link to that if you need to instead. If you post the DDL statements that
define your tables, I can help you better.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:E02337CF-71FF-4871-8F3C-60CDFC79C4B6@.microsoft.com...
>I need to rewrite a sp. I'm pasting the major part of the sp down here.
>Also,
> if possible, can you tell me which columns to index? Thank you.
>
> DECLARE @.procAsset int,
> @.procStartDate datetime,
> @.procEndDate datetime
> DECLARE assetdate_cur CURSOR FOR
> SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
> AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0'
> AS
> StartDate
> FROM mapITAsset2ExecutablesUsage
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
>
> OPEN assetdate_cur
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
> --Delete old data for the day from the table
> DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
> @.procAsset AND RunDate = @.procStartDate
> INSERT INTO mapITAsset2ExecutablesUsageSummary
> (ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
> RunDate)
> SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
> WHEN SUM(Secs) IS NULL THEN 0
> ELSE SUM(Secs)
> END, @.procStartDate
> FROM
> (
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
> Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND UsageEndDateTime >= @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
> @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
> @.procEndDate AND UsageEndDateTime >= @.procStartDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> ) AS Totals
> GROUP BY ExecutableRepositoryID, UserID
>
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> END
> CLOSE assetdate_cur
> DEALLOCATE assetdate_cur
>
>
> This sp actually summarizes thes data but it just takes forever to
> run...(more than 10 hours)
> Can someone give me a hint on how can i reprogram this? Thank you|||Couple more things:
1. You may also want to consider having a clustered index on
UsageStartDateTime since you are doing a lot of range selection using
operators such as >, >=, <, and <=.
2. You may want to consider using T-SQL extended arguments like
READ_ONLY, OPTIMISTIC locking, etc for your cursor.|||I've acdtually used a temp table as a replacement for the cursor but it has
not given me the expected results. I've taken the initial query and stored i
t
in a temp table. I have then joined that temp table to the map... table and
done all my querying. But the group by stuff throws me off because when i do
selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate which
I
dont want to do.
SELECT DISTINCT ITAssetObjectID,
CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS StartDat
e
,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
'/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2))
+ '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS VARCHAR(4))
+ ' 0:0:0' AS EndDate
into ##SummarizeITAsset3
FROM mapITAsset2ExecutablesUsage (nolock)
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
##SummarizeITAsset3 SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
where mia2eus.RunDate = SITA.StartDate
SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*) AS
Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
into ##Totals
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
mia2eus.UsageStartDateTime < SITA.EndDate
AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime IS
NULL)
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
--2004
--union all
insert into ##Totals
SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second, UsageStartDateTime,
UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
mia2eus.UsageStartDateTime < SITA.EndDate
AND mia2eus.UsageEndDateTime >= SITA.EndDate
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
.
.
.
.
I'm having problems with the above select into's and the insert into's. How
would you do those and the groupby's? Please help with that. Thank you.
The DDL's are as follows.
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[ProcessID] [int] NOT NULL ,
[UsageStartDateTime] [datetime] NOT NULL ,
[UsageEndDateTime] [datetime] NULL ,
[ServerUpdateTime] [datetime] NULL ,
[IsSummarized] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[RunCount] [int] NOT NULL ,
[RunTime] [int] NOT NULL ,
[RunDate] [datetime] NOT NULL
)
GO|||Sorry I don't have time to test this myself right now, but maybe what I have
will get you going in a better direction. I've tried to write things based
on your first procedure you posted:
--First, create a View that joins the mapITAsset2ExecutablesUsage table to
itself based on the ITAssetObjectID.
--This will filter out the IsSummarized records and give you a time
computation for each join result.
--I left out the parsing of the StartDate field since I couldn't tell why
you had that in there in the first place.
--I based the CASE statement on the way you calculated the EndDate field in
your cursor and your UNION statements. I'm sure you could clean it up
better.
CREATE VIEW vw_mapITAsset2ExecutablesUsage
AS
SELECT
M1.ITAssetObjectID,
M1.ExecutableRepositoryID,
M1.UserID,
M1.UsageStartDateTime,
CASE
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
(M2.UsageEndDateTime < DATEADD(day, 1, M1.UsageStartDateTime) OR
M2.UsageEndDateTime IS NULL) THEN DATEDIFF(second, M2.UsageStartDateTime,
M2.UsageEndDateTime)
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
M2.UsageEndDateTime >= DATEADD(day, 1, M1.UsageStartDateTime) THEN
DATEDIFF(second, M2.UsageStartDateTime, DATEADD(day, 1,
M1.UsageStartDateTime))
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
>= DATEADD(day, 1, M1.UsageStartDateTime) THEN 86400
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
< DATEADD(day, 1, M1.UsageStartDateTime) AND M2.UsageEndDateTime >=
M1.UsageStartDateTime THEN DATEDIFF(second, M1.UsageStartDateTime,
M2.UsageEndDateTime)
END AS Secs
FROM mapITAsset2ExecutablesUsage M1
INNER JOIN mapITAsset2ExecutablesUsage M2 ON M1.ITAssetObjectID =
M2.ITAssetObjectID
WHERE M1.IsSummarized = 0 AND M2.IsSummarized = 0
---
--Next, your procedure to populate the mapITAsset2ExecutablesUsageSummary
table should be as simple as...
TRUNCATE TABLE mapITAsset2ExecutablesUsageSummary
INSERT INTO mapITAsset2ExecutablesUsageSummary (
ITAssetObjectID,
ExecutableRepositoryID,
UserID,
RunCount,
RunTime,
RunDate)
SELECT M.ITAssetObjectID,
M.ExecutableRepositoryID,
M.UserID,
COUNT(M.ITAssetObjectID),
SUM(Secs),
M.UsageStartDateTime
FROM vw_mapITAsset2ExecutablesUsage M
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID,
M.UsageStartDateTime
--Here you get both the count on rows and the summary of the time in seconds
in the same statement.
--
Again, I may not have understood in the time I had your query needs, but I
hope this helps you out better. Keep in mind you can also add indexes to
the View to assist in your performance to the final query.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1052AF0E-B7D3-47B8-9DA9-69C6E04A4651@.microsoft.com...
> I've acdtually used a temp table as a replacement for the cursor but it
> has
> not given me the expected results. I've taken the initial query and stored
> it
> in a temp table. I have then joined that temp table to the map... table
> and
> done all my querying. But the group by stuff throws me off because when i
> do
> selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate
> which I
> dont want to do.
>
> SELECT DISTINCT ITAssetObjectID,
> CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
> StartDate
> ,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
> '/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS
> VARCHAR(4))
> + ' 0:0:0' AS EndDate
> into ##SummarizeITAsset3
> FROM mapITAsset2ExecutablesUsage (nolock)
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
> delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
> ##SummarizeITAsset3 SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> where mia2eus.RunDate = SITA.StartDate
>
>
> SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*)
> AS
> Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> into ##Totals
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime
> IS
> NULL)
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> --2004
> --union all
> insert into ##Totals
> SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
> mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second,
> UsageStartDateTime,
> UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND mia2eus.UsageEndDateTime >= SITA.EndDate
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> .
> .
> .
> .
> I'm having problems with the above select into's and the insert into's.
> How
> would you do those and the groupby's? Please help with that. Thank you.
> The DDL's are as follows.
>
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [ProcessID] [int] NOT NULL ,
> [UsageStartDateTime] [datetime] NOT NULL ,
> [UsageEndDateTime] [datetime] NULL ,
> [ServerUpdateTime] [datetime] NULL ,
> [IsSummarized] [int] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [RunCount] [int] NOT NULL ,
> [RunTime] [int] NOT NULL ,
> [RunDate] [datetime] NOT NULL
> )
> GO
>|||Hey Random. I have put this in QA. There are a few problems I have with this
.
I'm thinking the view is correct but the group by clause is messing a lot of
things up. I'm receiving the correct format for almost everything.
-I need to remove the time field from the RunDate or make it 00:00:00
because everything for a day is to be summarized.
-The Runcount is somehow screwed. All the runcounts for a particular
ITAssetObjectID is the same regardless of its ExecutableRepositoryID, UserID
(I think I have found a fix as it's another column that it needs to count
on, it's not ITAssetObjectID, it's ProcessID.
This are the problems for now.|||Hey Random, The second point in my previous post is also a problem. Even
when I do a count(ProcessID) for the RunCount column it gives me the same
counts for every ITassetObjectId and ExecutableRepositoryID.
It's quite urgent for me to get this to work. Thank you for all your help...|||Tejas;
Is there any way you can email me some sample data? Maybe a lot of INSERT
statements for your mapITAsset2ExecutablesUsage table and an idea of what
the result data should look like in mapITAsset2ExecutablesUsageSummary?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DFA772AC-9CCA-46AB-A434-C1C06F8BB7E5@.microsoft.com...
> Hey Random. I have put this in QA. There are a few problems I have with
> this.
> I'm thinking the view is correct but the group by clause is messing a lot
> of
> things up. I'm receiving the correct format for almost everything.
> -I need to remove the time field from the RunDate or make it 00:00:00
> because everything for a day is to be summarized.
> -The Runcount is somehow screwed. All the runcounts for a particular
> ITAssetObjectID is the same regardless of its ExecutableRepositoryID,
> UserID
> (I think I have found a fix as it's another column that it needs to count
> on, it's not ITAssetObjectID, it's ProcessID.
> This are the problems for now.|||Yes, I can. Can you give me youe email address or email me at
parikht@.gmail.com? Thank you.

How can I represent checkboxes in my report?

in my input form i have checkboxes and textboxes, if i click on a checkbox the textbox beside it enables, the textbox field is optional so the checkbox alone has a value that needs to be saved to the database. i have a report.rdlc, i want to display the checkboxes and the textboxes, what should i do?

I moved this thread to the SQL Server Reporting Services section, since it appears that you're asking how to put checkboxes and text boxes onto a SRS report (the rdlc file).

|||

icemart525 wrote:
in my input form i have checkboxes and textboxes, if i click on a checkbox the textbox beside it enables, the textbox field is optional so the checkbox alone has a value that needs to be saved to the database. i have a report.rdlc, i want to display the checkboxes and the textboxes, what should i do?

..or maybe I'll use an image, right?

sql

How can I report on entered and updated records

My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.

Maybe a tool someone knows of?

Jackxxx:
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.
Maybe a tool someone knows of?

You mean without doing a SELECT on the tables? Check out some log reader tools - Red Gate has one.. perhaps there are others..but then they only have so many transactions and not your entire database..

How can i Replace the Minus-Statement

Hi,

I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic

what can I do to run it on SQL-Server.

Thanks in advance

Raimund

use whidbey's intersect function|||

Hi you didn′t specify wheter you are using 2k5 or 2k. In SQL Server you would use EXCEPT

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
EXCEPT
select tab_name from data_dic
)
EXCEPT
select tab_name, col_name from data_dic

in 2k, you would use NOT IN, like you did in the above query:

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
WHERE table_name NOT IN
(
select tab_name from data_dic
)
)
WHERE table_name NOT IN
(
select tab_name, col_name from data_dic
)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks it works fine.

I already had it before but it didn't work. Probably there was something wrong in the Syntax.

Best Regards

Raimund

How can i replace some set of white space to one white space in between string

Dear Frnd

I have to remove some set of white space in between the string and i have to replace with one white space

eg:- my name is divakar

I have to change the string to like this

my name is divakar.

so how can write sql query for that

Code Snippet

declare @.var varchar(100)

set @.var = 'my name is divakar'

while charindex(' ', @.var) > 0 --two spaces

begin

set @.var = replace(@.var, ' ', ' ') -- first is two spaces, second is one space

end

select @.var

|||

Having a function (recursive) to use it on your query...

Code Snippet

Create Function dbo.ProperWhiteSpace

(

@.String varchar(1000)

)

Returns Varchar(1000)

Begin

If CharIndex('',@.String) <> 0

Return dbo.ProperWhiteSpace(Replace(@.String,'', ' '));

Return @.String

End

Select dbo.ProperWhiteSpace('mynameis divakar')

|||

Thx Dale

|||thx Manivannan.D.Sekaran

How can I replace a value in xml with .modify from the variable?

Thanks for your help.
I am trying to modify a value within an xml. I found that this can be done w
ith .modify but I must use literal for modify command. I need it variablized
. Is there any way to do it without sp_executesql?
thanks
declare
@.xml varchar(max)
,@.xml1 xml
, @.ConversationHandle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.xml1 = @.xml
SET @.ConversationHandle = newid()
This is what I want but using the xml .modify function
select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Conv
ersationHandle="' + @.ConversationHandle + '"') as xml)
SET @.xml1.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "boo"
')
SELECT @.xml1
DECLARE @.m varchar(1000)
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "' + cast(@.ConversationHandle as char(36)) + '"'
SET @.xml1.modify(@.m) -- this errors
SELECT @.xml1Hello Farmer,
Off the top of my head, sp_sqlexcutesql is the only was to do this as the
constructor isn't availble in XQuery DML for SQL Server 2005.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent,
I also could not find any better way.
the answer using sp_executesql is:
DECLARE @.SQL nvarchar(max)
SET @.SQL =
'SET @.xml.modify(''replace value of (/Tasks/row/@.ConversationHandle)[1] with
"' + cast(@.ConversationHandle as char(36)) + '"'');'
SELECT @.sql
EXEC sp_executesql
@.stmt = @.sql
,@.params = N'@.xml xml OUTPUT'
,@.xml = @.xml1 OUTPUT
select @.xml1
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741397a8c8d18706916da0@.news.microsoft.com...
> Hello Farmer,
> Off the top of my head, sp_sqlexcutesql is the only was to do this as the
> constructor isn't availble in XQuery DML for SQL Server 2005.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>|||Well you must always use a string literal in the modify method but you can h
ave access to the values in sql columns or sql variables through the use of
sql:column() and sql:variable() in your XQuery.
I would suggest that you read about it in Books Online, but here's a quick e
xample. You can replace the value of your ConversationHandle attribute with
the value from a sql variable named @.handle like this
declare @.xml xml, @.handle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.handle = newid()
SET @.xml.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with sql:variable("@.handle")
')
I hope this helps
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.mi
crosoft.com/info/cpyright.htm
"Farmer" <someone@.somewhere.com> wrote in message news:%23RAddk3AHHA.4428@.TK
2MSFTNGP04.phx.gbl...
Thanks for your help.
I am trying to modify a value within an xml. I found that this can be done w
ith .modify but I must use literal for modify command. I need it variablized
. Is there any way to do it without sp_executesql?
thanks
declare
@.xml varchar(max)
,@.xml1 xml
, @.ConversationHandle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.xml1 = @.xml
SET @.ConversationHandle = newid()
This is what I want but using the xml .modify function
select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Conv
ersationHandle="' + @.ConversationHandle + '"') as xml)
SET @.xml1.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "boo"
')
SELECT @.xml1
DECLARE @.m varchar(1000)
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "' + cast(@.ConversationHandle as char(36)) + '"'
SET @.xml1.modify(@.m) -- this errors
SELECT @.xml1|||
> Thanks for your help.
> I am trying to modify a value within an xml. I found that this can be done
with .modify but I must use literal for modify command. I need it variabliz
ed. Is there any way to do it without sp_executesql?
> thanks
> declare
> @.xml varchar(max)
> ,@.xml1 xml
> , @.ConversationHandle char(36)
> set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
> SET @.xml1 = @.xml
> SET @.ConversationHandle = newid()
> This is what I want but using the xml .modify function
> select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Co
nversationHandle="' + @.ConversationHandle + '"') as xml)
>
> SET @.xml1.modify('
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "boo"
> ')
> SELECT @.xml1
> DECLARE @.m varchar(1000)
> SET @.m ='
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "' + cast(@.ConversationHandle as char(36)) + '"'
> SET @.xml1.modify(@.m) -- this errors
> SELECT @.xml1
>
>
>
>
> Thanks for your help.
> I am trying to modify a value within an xml. I =
> found that=20
> this can be done with .modify but I must use literal for modify command. =
> I need=20
> it variablized. Is there any way to do it without =
> sp_executesql?
> thanks
> declare
> @.xml varchar(max)
> ,@.xml1 xml
> , @.ConversationHandle char(36)
> set @.xml=3D ''
> SET @.xml1 =3D @.xml
> SET @.ConversationHandle =3D newid()
> This is what I want but using the xml .modify=20
> function
> select cast(@.xml as xml), cast(REPLACE(=20
> @.xml, =
> 'ConversationHandle=3D""', 'ConversationHandle=3D"' + @.ConversationHandle
+ '"') as xml)
> SET @.xml1.modify('
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "boo"
> ')
> SELECT @.xml1
> DECLARE @.m varchar(1000)
> SET @.m =3D'
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "' + cast(@.ConversationHandle as char(36)) + '"'
> SET @.xml1.modify(@.m) -- this =
> errors
> SELECT =
> @.xml1
You can easily do this using SQL variables in the XQuery literal
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with sql:variable("@.ConversationHandle")'
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

How Can I Rename a Reportin Service Folder using Code

Using Reporting Service Web service

It is possible to create folder and delete folder. But I couldn't rename the folder using code it tried with setProperties method but name is ready only property. Please help me to resolve the issue.

Thanks in Advance.

Krishna kumar.M

hi all,

Later I found if use the rs.MoveItem we can achive the same

sql

how can i rename a login (change the domain)?

Hi All,
I would like to rename a login SAMPLE-IT\bean to NEWDOMAIN\bean, but i get this message:
"The name change cannot be performed because the SID of the new name does not match the old SID of the principal."

the command is : alter login [SAMPLE-IT\bean] with name=[NEWDOMAIN\bean]
server is sql2005 std (initial base)

what can i do ( there are lot of db on this instance and there are lot of instance where I have to change the domain of the user...) ... and there are lot of user whom I have to change it...:-(

thnx
Csaba

A similar thread appears here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2052035&SiteID=17

I suggest to install SQL Server SP2 and read the articles linked the thread above.

How can I remove the workitem type from the existing project?

When creating a new project using 'Agile' process template, there are 5 workitem types in the list of workitem type by default. After the project being created, how can I delete some workitem type that we don' t need?

For example, my team will never use 'Senario' workitem type for the project and I would like to remove this workitem type from the list. So no team member will see 'Senario' workitem type in the list when creating workitem, and therefore no one will create a workitem using 'Senario' workitem type by accident.

I understand that workitem type can't be deleted if it is used to created any workitem. So what I mean is the workitem type that has never used to create any workitem.

Thanks.

Unfortunately you cannot remove them from the list even if you have never created one. You can remove them from the template prior to creating a project and they will never be in the project. You could also try to put security on the transition from nothing to new for TFSAdmins only. That way the typical users would not be able to create a new workitem of the type Scenario. There may be other creative ways, but in the end they will still show in the list.

-paul

|||Thanks.

How can I Remove the Screwed-up AdventureWorksDB in SQL Server Management Studio Express?

Hi all,

I downloaded AdventureWorksDB.msi Microsoft and struggled to install it in my SQL Server Management Studio Express 8 months ago. I forgot the AdventureWorks examples and other stuff were in my C:\ drive. Recently, I downloaded a new AdventureWork.msi and executed it to install without removing the old stuff from the "Add and Remove" of Control Panel. Now my AdventureWorks database is screwed-up in my termical PC that is linked with Microsoft NT 4 LAN System!!! Please kindly help and tell me how I can revome the scewed-up AdventureWorksDB in my terminal PC.

Thanks in advance,

Scott Chang

Hello Scott -

I'm not sure what is going on here - can you tell me where the software is installed and what you mean by "screwed up"? to help you, the readers need to know what you're seeing, and where. Be as detailed as you can.

Thanks!

Buck Woody

|||

Hi Buck,

I can tell you the following 2 things:

1) When I double-clicked on AdventureWorksDB.msi recently, it tried to install the stuff of Sample Databases for Express Editions in my C:\Program Files\Microsoft SQL Server\MSSQL1\MSSQL\. But it stated an error occurred in the last step of installation process. After it happened, I tried to remove "AdventureWorksDB" in the "Add and Remove" of Control Panel, I got the following error:

AdventureWorksDB Error. 1326 Error: getting file security: C:\Program Files\Microsoft SQL.Server\MSSQL 1

\MSSQL\GetLastError 5.

I think it was because I forgot to remove the old AdventureWorksDB, before I clicked the new AdventureWorks.msi!!

2) This morning, I entered my SQL Server Management Studio Express and tried to removed the "AdventureWorksDB" in the Databases by using 'Delete' after I right-clicked "AdventureWorksDB" and "Delete Object" appeared - I kept clicking "Delete Object" and it just kept going and doing the same thing again and again..... I gave up at this poit!!!

That is what I called "screwed-up" AdventureWorksDB. Please help and tell me how I can remove this "screwed-up" stuff.

Thanks again,

Scott Chang

|||I see. Try this: Right-click the database and select "All Tasks", then "Detach Database". Re-start the server, and then see if the database was released. If it is, then you can delete the Adventureworks database files from the hard drive.|||

Hi Buck, Thanks for your response.

I following your instructions and I got the following:

Database to detach:

Database Name Drop Update Keep Status Message

AdventureWorks V Failure Detach database failed to Server

|OK|

After I clicked on the |OK| button, the following appeared:

Microsoft SQL Server Management Studio Express

Detach database failed for Server 'myComputerID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional Information:

-> An exception occured while executing a Transct-SQL statement or batch. (Microsoft.SqlServer.Express.ConnecttionInfo)

-> EXECUTE permission denied on object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL

Server, Error: 229)

|OK|

I am completely lost now. Please help me again and give me more instructions to resolve/solve the proble.

Many Thanks and Regards,

Scott Chang

|||

Hi Scott,

The installer should have put something in add/remove programs for you to uninstall it. The new one does that.

|||

Hi,

Refer http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm for instruction on how to remove Adventure Works DB

HTH

Hemantgiri S. Goswami

How can I Remove the Screwed-up AdventureWorksDB in SQL Server Management Studio Express?

Hi all,

Thanks in advance,

Scott Chang

Hello Scott -

Thanks!

Buck Woody

|||

Hi Buck,

I can tell you the following 2 things:

AdventureWorksDB Error. 1326 Error: getting file security: C:\Program Files\Microsoft SQL.Server\MSSQL 1

\MSSQL\GetLastError 5.

I think it was because I forgot to remove the old AdventureWorksDB, before I clicked the new AdventureWorks.msi!!

That is what I called "screwed-up" AdventureWorksDB. Please help and tell me how I can remove this "screwed-up" stuff.

Thanks again,

Scott Chang

Hi Buck, Thanks for your response.

I following your instructions and I got the following:

Database to detach:

Database Name Drop Update Keep Status Message

AdventureWorks V Failure Detach database failed to Server

|OK|

After I clicked on the |OK| button, the following appeared:

Microsoft SQL Server Management Studio Express

Detach database failed for Server 'myComputerID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional Information:

-> An exception occured while executing a Transct-SQL statement or batch. (Microsoft.SqlServer.Express.ConnecttionInfo)

-> EXECUTE permission denied on object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL

Server, Error: 229)

|OK|

I am completely lost now. Please help me again and give me more instructions to resolve/solve the proble.

Many Thanks and Regards,

Scott Chang

|||

Hi Scott,

The installer should have put something in add/remove programs for you to uninstall it. The new one does that.

|||

Hi,

Refer http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm for instruction on how to remove Adventure Works DB

HTH

Hemantgiri S. Goswami

How Can I remove Table Scan?

I have the following query and just cannot seem to remove the table scan on
Table2. I have posted the indices from all tables in the join. I think Ive
covered everything according to rule. but is it overkill?
select count(a.Vh_SummVeh_id)
from Table1 a (nolock),
Table2 b (nolock),
Table3 c (nolock),
Table4 d (nolock)
where a.VH_Group_ID = b.Vh_group_id
and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
and c.vh_value_id = b.vh_value_id
and c.Vh_band_low_id = a.Vh_band_low_id
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
sp_help Table1
nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
Vh_Rating_Class_ID, Vh_band_low_id
sp_help Table2
nonclustered, unique located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Vh_reg_area_id
nonclustered located on PRIMARY Vh_VhAll_group_id
nonclustered located on PRIMARY Vh_Motor_group_id
nonclustered located on PRIMARY Vh_mfg_yr_id
nonclustered located on PRIMARY Vh_value_cv_id
nonclustered located on PRIMARY Vh_category_id
nonclustered located on PRIMARY Vh_mfg_age_id
nonclustered located on PRIMARY Vh_group_id
nonclustered located on PRIMARY Vh_rating_class_id
nonclustered located on PRIMARY Vh_model_id
nonclustered located on PRIMARY Vh_make_id
nonclustered located on PRIMARY Vh_value_id
nonclustered located on PRIMARY Vh_reg_year_id
sp_help Table3
nonclustered, unique located on PRIMARY Vh_value_id
clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper,
Vh_value_id
nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_id
sp_help Table4
nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
Tr_seq_id
nonclustered located on PRIMARY Product_id
nonclustered located on PRIMARY Inception_date_id
nonclustered located on PRIMARY Client_ver_id
nonclustered located on PRIMARY Tr_sub_type_id
nonclustered located on PRIMARY Rated_driver_id
nonclustered located on PRIMARY f_Ren_Flag
nonclustered located on PRIMARY Cp_cover_class_ind_id
nonclustered located on PRIMARY cp_attribute_id
nonclustered located on PRIMARY Cp_gen_ind_id
nonclustered located on PRIMARY Policy_id
nonclustered located on PRIMARY dim_keys_id
nonclustered located on PRIMARY Po_tr_pay_method_id
nonclustered located on PRIMARY Vh_SummVeh_id
nonclustered located on PRIMARY Po_tr_status_id
nonclustered located on PRIMARY Po_tr_bus_type_id
nonclustered located on PRIMARY Po_rd_postcode_id
nonclustered located on PRIMARY Cover_id
nonclustered located on PRIMARY Cur_trn_dt
nonclustered located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Cover_idWhat exactly is this test supposed to achieve:
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
?
This one also makes no sense to me:
and isnull(d.Vh_SummVeh_id,0) <> 0
Try replacing these two with valid tests first.
E.g.:
and (b.vehicle_id = d.vehicle_id or b.vehicle_id is null) -- just guessing
here
and (d.Vh_SummVeh_id is null)
ML|||try
Updating statistics
--
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> I have the following query and just cannot seem to remove the table scan o
n
> Table2. I have posted the indices from all tables in the join. I think Ive
> covered everything according to rule. but is it overkill?
> select count(a.Vh_SummVeh_id)
> from Table1 a (nolock),
> Table2 b (nolock),
> Table3 c (nolock),
> Table4 d (nolock)
> where a.VH_Group_ID = b.Vh_group_id
> and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
> and c.vh_value_id = b.vh_value_id
> and c.Vh_band_low_id = a.Vh_band_low_id
> and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
> and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
> sp_help Table1
> nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
> Vh_Rating_Class_ID, Vh_band_low_id
> sp_help Table2
> nonclustered, unique located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Vh_reg_area_id
> nonclustered located on PRIMARY Vh_VhAll_group_id
> nonclustered located on PRIMARY Vh_Motor_group_id
> nonclustered located on PRIMARY Vh_mfg_yr_id
> nonclustered located on PRIMARY Vh_value_cv_id
> nonclustered located on PRIMARY Vh_category_id
> nonclustered located on PRIMARY Vh_mfg_age_id
> nonclustered located on PRIMARY Vh_group_id
> nonclustered located on PRIMARY Vh_rating_class_id
> nonclustered located on PRIMARY Vh_model_id
> nonclustered located on PRIMARY Vh_make_id
> nonclustered located on PRIMARY Vh_value_id
> nonclustered located on PRIMARY Vh_reg_year_id
> sp_help Table3
> nonclustered, unique located on PRIMARY Vh_value_id
> clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper
,
> Vh_value_id
> nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_
id
> sp_help Table4
> nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
> Tr_seq_id
> nonclustered located on PRIMARY Product_id
> nonclustered located on PRIMARY Inception_date_id
> nonclustered located on PRIMARY Client_ver_id
> nonclustered located on PRIMARY Tr_sub_type_id
> nonclustered located on PRIMARY Rated_driver_id
> nonclustered located on PRIMARY f_Ren_Flag
> nonclustered located on PRIMARY Cp_cover_class_ind_id
> nonclustered located on PRIMARY cp_attribute_id
> nonclustered located on PRIMARY Cp_gen_ind_id
> nonclustered located on PRIMARY Policy_id
> nonclustered located on PRIMARY dim_keys_id
> nonclustered located on PRIMARY Po_tr_pay_method_id
> nonclustered located on PRIMARY Vh_SummVeh_id
> nonclustered located on PRIMARY Po_tr_status_id
> nonclustered located on PRIMARY Po_tr_bus_type_id
> nonclustered located on PRIMARY Po_rd_postcode_id
> nonclustered located on PRIMARY Cover_id
> nonclustered located on PRIMARY Cur_trn_dt
> nonclustered located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Cover_id
>|||ps: i would prefer not to use index hint
also: http://techrepublic.com.com/5100-22_11-1050429.html
which explains the problem with counting nulls|||Have you tried any of my suggestions? How did they affect the execution plan
?
ML|||Thanks but statistics are updated,
the coalesce reduces the amount of disk IO by 5%.
The isNull gets away from a SQL "feature" [IS NOT NULL] that ensures the
number of IS NOT NULLs counted is correct(see thread 'Are Null counts
reliable' from yesterday).
So any other ideas on why when the selected column to update from is indexed
as well as the columns in the join, a table scan still exists? And how can I
overcome this?|||Hey ML, I have.
"and (d.Vh_SummVeh_id is null)" I cannot used because it does not give me
the correct number of ISNULL records.
The other uses a table scan also.|||there are 4million records in Table2, is it possible the optimizer thinks th
e
tablescan is the best way to run the query?
"ML" wrote:

> Have you tried any of my suggestions? How did they affect the execution pl
an?
>
> ML|||I have evaluated the performance on using a hint which drops the table scan
and creates a 95% bookmark. performance did not increase.
I think the optimizer may be right to do its table scan.
Thanks all round|||There's also no clustered index on Table4. Try making the one on the
vehicle_id column in Table4 a clustered index.
MLsql

Friday, March 30, 2012

Blog Archive

About Me