backing up SQL Server 2000 database to named pipe

would appreciate if you could comment on how to backup sql server 2000
database to named pipe
Thank you for your help beforehand,
Miron.I believe you must create a logical dump device of the type "Named Pipe" and
then issue the backup to that device. See sp_addumpdevice in BOL for more
details.
Andrew J. Kelly SQL MVP
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>|||I am short on time. It would be nice to have a working script for database
pubs and a named pipe \\\pipe\mypipe
Thanks,
Miron.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I believe you must create a logical dump device of the type "Named Pipe"
and
> then issue the backup to that device. See sp_addumpdevice in BOL for more
> details.
> --
> Andrew J. Kelly SQL MVP
>
> "miron" <nospam_berlin.miron@.verizon.net> wrote in message
> news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
>|||Try the following script to create the device and then backup the PUBS
database to the newly created device:
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
- Peter Ward
WARDY IT Solutions
"miron" wrote:

> I am short on time. It would be nice to have a working script for database
> pubs and a named pipe \\\pipe\mypipe
> Thanks,
> Miron.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> and
>
>|||Peter, thank you for the script. While using lines I run into issues, some
did resolve, others still need assistance. Here is step by step
1.) create the dump device using the first line of the script
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
2.) executed
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
and SQL Server immediately returned an error message:
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
3.) modified the dump device as following:
exec sp_dropdevice 'PUBS_PIPE_DUMP'
go
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
go
4.) tried to execute again
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
this time osql.exe cursor blinked for a few minutes. Then SQL Server
returned the same error message...
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
5.) just to see how much time it takes to backup pubs I run script
1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
2> go
Here is SQL Server message returned when completed backup:
Processed 144 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
MB/sec).
and it happened in a mere second. So, it does look like I am close to
succeeding at baking SQL Server 2000 database to a named pipe, yet something
is not letting backup to complete in reasonable time. I guess what really
needed is a working script, that would allow to store backup and then
restore from it using named pipe. I do greatly appreciate any further
assistance.
Miron.
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5ACAA03C-AC69-47AD-A638-F971557B2D5F@.microsoft.com...
> Try the following script to create the device and then backup the PUBS
> database to the newly created device:
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
>
> - Peter Ward
> WARDY IT Solutions
>
> "miron" wrote:
>
database
Pipe"
more
2000|||"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23NM7zvOOFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Peter, thank you for the script. While using lines I run into issues, some
> did resolve, others still need assistance. Here is step by step
> 1.) create the dump device using the first line of the script
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> 2.) executed
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> and SQL Server immediately returned an error message:
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 3.) modified the dump device as following:
> exec sp_dropdevice 'PUBS_PIPE_DUMP'
> go
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
> go
> 4.) tried to execute again
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> this time osql.exe cursor blinked for a few minutes. Then SQL Server
> returned the same error message...
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 5.) just to see how much time it takes to backup pubs I run script
> 1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
> 2> go
> Here is SQL Server message returned when completed backup:
> Processed 144 pages for database 'pubs', file 'pubs' on file 1.
> Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
> BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
> MB/sec).
> and it happened in a mere second. So, it does look like I am close to
> succeeding at baking SQL Server 2000 database to a named pipe, yet
> something
> is not letting backup to complete in reasonable time. I guess what really
> needed is a working script, that would allow to store backup and then
> restore from it using named pipe. I do greatly appreciate any further
> assistance.
You need to have something reading or writing data in or out of the named
pipe, within a reasonable amount of time, or the backup/restore will fail.
Simple example using lzop (google for it) to compress backups on-the-fly.
start cmd /C isql /E /Q"backup database <db_name> to pipe = '\\.\pipe\bck'"
sleep 3
lzop -v -o<db_name>.lzo < \\.\pipe\bck
The sleep command just allows a bit of time for the backup to get started
and the pipe to be setup.
To restore the data :-
start cmd /C isql /E /Q"restore database <db_name> from pipe
='\\.\pipe\bck'"
sleep 3
lzop -d -n -v -c <db_name>.lzo > \\.\pipe\bck
You can also use this to make quick copies of databases :-
start isql -E -Q"backup database <db_name> to pipe = '\\.\pipe\bck_o' with
stats = 10"
start isql -E -Q"restore database <db_name_new> from pipe = '\\.\pipe\bck_i'
stats = 10"
sleep 2
cat \\.\pipe\bck_o > \\.\pipe\bck_i
(The restore command will need 'with move' parameters, they are not shown
for clarity)
(copy didn't work when I first tried it, so I used cat, copy /b may work)
Ian.|||Actually you can do it directly
backup database prod to pipe='\\myserver\mypipe'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>

Background Color Return:

I am writing a report that I need to return a background color for a cell in
a table. The background color depends on what color is named in another
cell. For example, if the cell (Quality_status.value) displays the word
Green then the cell (Quanity_status.value) background color needs to be
Green. I have tried several IIF expressions in the BackgroundColor
properties but none of them seem to work. Please help!!
--
Sincerely,
Debo
--
Sincerely,
DeboThe only supported property of a field is Value. Therefore you can't do it.
HTH
Charles Kangai, MCT, MCDBA
"Debo" wrote:
> I am writing a report that I need to return a background color for a cell in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||I went to a textbox properties, background color, scrolled to the top to
add an expression. and did
=iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
have you tried
=(fields!Quality_status.Value)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Debo" <debo@.discussions.microsoft.com> wrote in message
news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> I am writing a report that I need to return a background color for a cell
in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||Hi Wayne,
I thought Debo was trying to read the BackColor property, and you cannot
read any property of a textbox except Value. Of course if you enter the name
of the color in words in a textbox, then you can do it, because you are
reading the Value property.
Cheers
Charles Kangai, MCT, MCDBA
ps - good to hear from you Wayne!
"Wayne Snyder" wrote:
> I went to a textbox properties, background color, scrolled to the top to
> add an expression. and did
> =iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
> have you tried
> =(fields!Quality_status.Value)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Debo" <debo@.discussions.microsoft.com> wrote in message
> news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> > I am writing a report that I need to return a background color for a cell
> in
> > a table. The background color depends on what color is named in another
> > cell. For example, if the cell (Quality_status.value) displays the word
> > Green then the cell (Quanity_status.value) background color needs to be
> > Green. I have tried several IIF expressions in the BackgroundColor
> > properties but none of them seem to work. Please help!!
> > --
> > Sincerely,
> >
> > Debo
> > --
> > Sincerely,
> >
> > Debo
>
>

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average

Hello,

I have two tables:
[Posts] > PostId, ...
[Ratings] > RatingId, PostId, Rating

I want to select all posts and add a new column named AverageRating.
This new column is the average of all ratings associated to that Post.
If a post was not rated then its AverageRating would be NULL.

How can I do this?
Thanks,
Miguel

select* , (SelectAvg(Rating)FROM Ratings RWhere R.PostId= P.PostId)as AverageRating

from Posts P

|||

SELECT p.*, t.avgRatingFROM posts pLEFTJOIN(Select postid,CAST(AVG(CAST(RatingasDecimal(5,2)))asDecimal(5,2))AS avgRatingFROM RatingsGroup By postid) tON p.postid=t.postid

Autonumbering the Records from XML

Hi,

I have a table "Del_Table", which must contain a column with autoincrement ability. I have added one column named as "Auto" and put it as Primary key, Identity and Integer. I am grabbing the data from an XML file. I have tried with the following Stored Procedure, but it gives an error as "An explicit value for the identity column in Del_Table can only be specified when a column list is used and IDENTITY_INSERT is ON". I have tried to to turn on IDENTITY_INSERT but still it does not work.

Code Snippet

CREATE PROCEDURE insertForecast
(@.OrderDoc ntext)
AS
DECLARE

@.hDoc int

EXEC sp_xml_preparedocument
@.hDoc output,
@.OrderDoc

INSERT INTO [Del_Table]
SELECT *
from OPENXML (@.hDoc,'Data/Delivery_Forecast/Forecast',3)
with
(
[ID] numeric '@.mp:id',
Document_Number char(35) '@.Document_Number',
Com_Date char(35) '@.Com_Date',
Code char (35) '@.code',
Port char (35) '@.port',
EPort char (35) '@.eport',
Number char (35) '@.number',
Instruction_Code char (35) '@.inst_Code',
Delivery_Date char (35) '@.del_date',
Quantity char (35) '@.quantity',
Status_Indicator char (35) '@.status_indicator'
)
ORDER BY
Document_Number

EXEC sp_xml_removedocument @.hDoc
GO

Any suggestions?

Thanks...

Specify the columns in the INSERT statement like the error message is stating (and still use the IDENTITY_INSERT of course).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com