Retriving Deleted record from database

Hi friends

I have a bit problem here

Just I want to get back all deleted record of database

How do I perform this task?
If It is possible then plz help me out?

Thanks in Advance

Khan

if you have the backup which was taken before the datas were deleted you can make use of it and restore it any other server and retrieve your records................|||

There is no built-in command to undelete. However, you could use Log Explorer.

http://lumigent.com/products/le_sql.html

Retrieving ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data

Retrieving native progress events for a 2000 package from SSIS. Is it possible?

Dear all,

AFAIK I don't think so. Let me know if possible and how.

I'm talking about a SSIS package which owns a Execute DTS 2000 Package Task and from there it calls a DTS 2000. Programatically, from our VB .Net front-end app we're seeing only SSIS events (when validate and execute methods are called) and any kind of information is provided from RCW proxy (I suppose that internally it gonna be created in order to send messages between managed and unmanaged code).

When a task inside our DTS 2000 fails we're awaring by means a "generic message" such this:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Thanks a lot for your time and comments,

hi guys any idea?

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
JeradJerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Micros...5/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:

> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table.
I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed unde
r
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage
to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...[vbcol=seagreen]
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Micros...5/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote:
>

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
JeradJerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:
> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table. I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed under
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote:
>> I have a seemingly simple task, but the solution isn't quite as
>> obvious/simple as one would think.
>> I am trying to find out the disk space used by each of the indexes in my
>> database. I have been able to get the size used by indexes within each
>> table (sp_spaceused), but this sums all of the indexes within the table.
>> I
>> have also been able to get the broken down stats of each index
>> (dm_db_index_physical_stats), but it doesn't show how much total size the
>> index consumes.
>> I can see exactly what I'm looking for in Management Studio, through the
>> "Disk Usage" report on the Summary tab for my database. It is listed
>> under
>> the "Disk Space Used by Partitions" section, which shows each index, its
>> "Total Memory Used", and its "Total Memory Reserved". But I can't manage
>> to
>> find a query that gives me this information.
>> Thanks in advance for your help.
>> Jerad
>>
>>

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
Jerad
Jerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:

> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table. I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed under
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>
|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...[vbcol=seagreen]
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote: