@@ERROR

Hi Folks,
I try to save the error number when a restore fails. In case a device error
I'd like to save the error number into a table. Let's say I have a RESTORE
as below
RESTORE LOG xxxxx
FROM DISK='xxxxxxxxxxx.bak'
PRINT @.@.ERROR
Server: Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'xxxxxxxxx'. Device error or device off-line. See
the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
If a device errors I want to get the error number 3201, which is more
specific about the cause of failure. However @.@.ERROR is always 3013 which
doesn't help much. How do I get it right as expected?
Thank you.Hmm, this may be tricky.
@.@.ERROR, as I understand it, always has the last immediate error that
occured. So, if you're printing @.@.error after the backup fails, it's always
going to be the last error, which is 3013 in this case.
I don't really see a way to just grab the error directly, other than logging
it to an output file in the job or writing to the event log. It seems to me
the event log is a more approprate place to put the entire message. You
could then import the event log into a table and do a search on that
specific error number.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:ePQlImsUDHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I try to save the error number when a restore fails. In case a device
error
> I'd like to save the error number into a table. Let's say I have a
RESTORE
> as below
> RESTORE LOG xxxxx
> FROM DISK='xxxxxxxxxxx.bak'
> PRINT @.@.ERROR
>
> Server: Msg 3201, Level 16, State 2, Line 3
> Cannot open backup device 'xxxxxxxxx'. Device error or device off-line.
See
> the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> If a device errors I want to get the error number 3201, which is more
> specific about the cause of failure. However @.@.ERROR is always 3013 which
> doesn't help much. How do I get it right as expected?
> Thank you.
>

.BAK is growing up

Hello ,Folks
SQL Server 2000 (sp3)
I have posted this question yesterday and still have not got an idea why it
is happening.
I have a database 11GB and every night I do a full backup (WITH INIT) of
the database that .BAK file was always around 11 GB as well
Two days ago it finnished (backup operation) with 16GB .bak file. The
.MDF,.LDF files have not grown since.
The question is WHY is my .BAK file grew so much? What could be a
reason?
ThanksHi Alex
From the sp_spaceused information you figures don't correspond
database_name database_size unallocated space
-- -- --
dbname 28511.69 MB 519.01 MB
reserved data index_size unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
This says your database is 27GB and there is 10GB unused space, so 16GB for
a backup seems reasonable.
John
"Alex" wrote:
> Hello ,Folks
> SQL Server 2000 (sp3)
> I have posted this question yesterday and still have not got an idea why it
> is happening.
> I have a database 11GB and every night I do a full backup (WITH INIT) of
> the database that .BAK file was always around 11 GB as well
> Two days ago it finnished (backup operation) with 16GB .bak file. The
> ..MDF,.LDF files have not grown since.
> The question is WHY is my .BAK file grew so much? What could be a
> reason?
> Thanks
>
>|||John
Yes , you are right, the database contains three files , however , it has
been all time that the .BAK file was 11GB , so what happened at night to
grow to 16GB?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
> Hi Alex
> From the sp_spaceused information you figures don't correspond
> database_name database_size unallocated space
> -- -- --
> dbname 28511.69 MB 519.01 MB
> reserved data index_size unused
> -- -- -- --
> 23568632 KB 6408816 KB 6066240 KB 11093576 KB
>
> This says your database is 27GB and there is 10GB unused space, so 16GB
> for
> a backup seems reasonable.
> John
> "Alex" wrote:
>> Hello ,Folks
>> SQL Server 2000 (sp3)
>> I have posted this question yesterday and still have not got an idea why
>> it
>> is happening.
>> I have a database 11GB and every night I do a full backup (WITH INIT)
>> of
>> the database that .BAK file was always around 11 GB as well
>> Two days ago it finnished (backup operation) with 16GB .bak file. The
>> ..MDF,.LDF files have not grown since.
>> The question is WHY is my .BAK file grew so much? What could be a
>> reason?
>> Thanks
>>|||Hi Alex
The simple answer would be that there is more information in the file!
Unless you are archiving data from your database then you would expect a
steady growth. A 4GB increment would indicate something abnormal has
happened, such as someone copyied a large table. To find out space used by
each table you could run something like:
CREATE TABLE #spaceused ( name nvarchar(128),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18),
reservedK AS CAST(LEFT(reserved,charindex(' ', reserved)-1) AS BIGINT) )
EXEC sp_msforeachtable 'INSERT INTO #spaceused ( name, rows, reserved, data,
index_size, unused ) EXEC sp_spaceused [?]'
SELECT * FROM #spaceused
ORDER BY reservedK DESC
DROP TABLE #spaceused
John
"Alex" wrote:
> John
> Yes , you are right, the database contains three files , however , it has
> been all time that the .BAK file was 11GB , so what happened at night to
> grow to 16GB?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
> > Hi Alex
> >
> > From the sp_spaceused information you figures don't correspond
> >
> > database_name database_size unallocated space
> > -- -- --
> > dbname 28511.69 MB 519.01 MB
> >
> > reserved data index_size unused
> > -- -- -- --
> > 23568632 KB 6408816 KB 6066240 KB 11093576 KB
> >
> >
> > This says your database is 27GB and there is 10GB unused space, so 16GB
> > for
> > a backup seems reasonable.
> >
> > John
> >
> > "Alex" wrote:
> >
> >> Hello ,Folks
> >>
> >> SQL Server 2000 (sp3)
> >>
> >> I have posted this question yesterday and still have not got an idea why
> >> it
> >> is happening.
> >>
> >> I have a database 11GB and every night I do a full backup (WITH INIT)
> >> of
> >> the database that .BAK file was always around 11 GB as well
> >> Two days ago it finnished (backup operation) with 16GB .bak file. The
> >> ..MDF,.LDF files have not grown since.
> >>
> >> The question is WHY is my .BAK file grew so much? What could be a
> >> reason?
> >>
> >> Thanks
> >>
> >>
> >>
>
>

(re)using a temporary table in a stored proc (was "Confusion")

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?

Howdy!Yes..the local temp table is only around for the length of process...

A global temp table sticks around as long as it's referenced by any process...

So you can do something like this...basically wrapping your sprocs with a driver sproc

USE Northwind
GO

CREATE PROC mySproc00
AS
BEGIN
UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
END
GO

CREATE PROC mySproc99
AS
BEGIN
SELECT * INTO ##myGlobalTemp99 FROM Orders

SELECT TOP 10 * FROM ##myGlobalTemp99

EXEC mySproc00

SELECT TOP 10 * FROM ##myGlobalTemp99

DROP TABLE ##myGlobalTemp99

END
GO

EXEC mySproc99
GO

DROP PROC mySproc00
DROP PROC mySproc99
GO|||Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.

Any other guidlines!

Howdy!|||post what the table looks like...and what the sproc is suppose to be doing...|||Instead of Temp table, declare table type variable and work with that.

Madhivanan|||I believe he needs to pass and store data between processing threads...table variables won't let you do that.

Why I asked for the Table Layout is this.

Each Process gets it's own unique spid.

I would create a permanent table, and and a column for the spid.

Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

Just make sure you clear out the rows for your spid before you start the process.|||Yes, Brett; that idea helped. thanx. :)

Howdy!|||Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

I'll take a look into this...|||I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

create proc sp_1 as selct * from #t
go
create proc sp_2 as
ceate table #t (f1 int null)
insert #t select 1 union select 2
exec sp_1
go
exec sp_2
go
drop proc sp_1, sp_2
go

Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.|||Doooh...it's the same spid

As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

Even with that...what I suggested wouldn't buy you anything anyway

Thanks for pointing that out...|||Doooh...Even with that...what I suggested wouldn't buy you anything anyway...So why did you suggest it? :D|||Cause I'm a moron...

It's like flying down the freeway...I missed the exit

You could use that method to pass data to a trigger though...|||So if that is not causing the problem he is having, then what is?

If i call #mytable after executing the procedure; won't work.

This seems to indicate he wants to access the table through a different connection...|||If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?Actually it looks like the same connection, unless his definition of a "session" is different...|||It's like flying down the freeway...I missed the exit...Don't feel bad, I use turn-arounds very often :D|||Then maybe he should post his code, 'cause something must be going wrong...|||He's probably creating the temp in the nested proc

The temp needs to be created in the driver

I imagine this is what's going on

create proc sp_2 as
create table #t (f1 int null)
insert #t select 1 union select 2
go

create proc sp_1 as
EXEC sp_2
select * from #t
go

exec sp_1
go

drop proc sp_1, sp_2
go|||Hi folks; u've been so kind! Sorry for getting back late;

I've a proc proc1 that performs some routine and outputs a temporary table #tab1. I want this table be accessed through other procs or within a batch that calls this procedure; WITHIN THE SAME CONNECTION(SESSION,SPID :))

The only way around i could find was the SPID idea into a permanent table.

The proc1 is a standard routine and many other procs or scripts need the output from it; i wonder if i could use function that returns a table... that could be used further.

Howdy|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua

First read Brett's sticky on top of the forum.|||...and start a new thread rather than resurrecting this old one...