db_owner can't bring DB online

Is there a way to allow a db_owner to bring his database online without maki
ng him a SA?The Fixed Server Role db_creator will do the trick.
A login with the db_creator role will be allowed is online and offline a DB,
so long as they have a user for the DB. Not sure about the "guest/public" d
b user, because we never keep it|||Please don't repost the same question at one hour intervals. Give the
newsgroup readers a little more time to respond. This was already answered
in your earlier thread.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ajanti" <anonymous@.discussions.microsoft.com> wrote in message
news:D13F60E9-317C-4AEA-9B43-1F7794A2DB9F@.microsoft.com...
> Is there a way to allow a db_owner to bring his database online without
making him a SA?

db trigger to avoid duplication of row ? how please

I'm Maintaining an existing website. Many times user clicked the button
twice, which results in duplication of row. now i cant do any modifications.
but i can write an trigger to check any row with same data,time stamp etc.
if so, then delete existing and insert new row with same data. but i do know
about trigger ? how can i achieve this validation using trigger.
Thnkz ,
Mahesh kumar.RCan't you use a UNIQUE constraint? For maintaining uniqueness that
would usually be the method preferred over a trigger.
David Portas
SQL Server MVP
--|||Why not define a primary key or unique constraint on the table?
Or, prevent the user from clicking the button twice, by disabling the
button, once it is pressed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
news:%23CDz0EjJFHA.2772@.TK2MSFTNGP14.phx.gbl...
I'm Maintaining an existing website. Many times user clicked the button
twice, which results in duplication of row. now i cant do any modifications.
but i can write an trigger to check any row with same data,time stamp etc.
if so, then delete existing and insert new row with same data. but i do know
about trigger ? how can i achieve this validation using trigger.
Thnkz ,
Mahesh kumar.R

Db Logic - Cant put it back together

Hi there!

Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.

Overview:

I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.

Layout for a magazine will look something like this:

Size Trim Type Bleed

Full Page 280x440 270x430 290x450

Half Page 140x220 130x210 150x230

etc...

Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.

Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.

Break it down!

Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).

I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!

The Problem.

The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.

I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.

Any ideas on how i could manage this?

If you made it tis far through my question then thanks anyways! I hope you can help me out!

Ta

You'll need to use SQL Servers new PIVOT function. Assuming your tables are set up as such:

MagSize: ID, Size
MagType: ID, TypeName
MagAttribute: SizeID, TypeID, Value

You generate your desired results with:

WITH TempTable
AS (
SELECT Size, TypeName, Value FROM
MagSize s
INNER JOIN MagAttribute v ON s.ID = v.SizeID
INNER JOIN MagType t ON t.ID = v.TypeID
)
SELECT * FROM TempTable
PIVOT (
MAX(VALUE)
FOR TypeName IN ([Trim],[Type],[Bleed])
) AS PVT

MSDN Article|||

Hi northside!

Thanks for your reply!

Ok seems like im gonna be stuck since i only have access to a SQL 2000 server.

I was just wondering from your query where you used the Trim, Type, Bleed values in your second last line: Because i dont know if every magazine will have three attributes (some might only have Trim and Bleed) Is there some way i can 1) replace Trim, Type, Bleed with variables and 2) use some array maybe to generate the quantity of attributes?

See I recon I can do this if I can create DDL based on the results created by the SELECT query. But my logic tells me ill need to store results in an array and i dunno if t-sql caters for that. So far i couldn't find anything.

Thanks once again!

|||

You can still do pivot queries in SQL Server 2000, it's just a bit more convoluted. The query below does the equivalent to the above:

SELECT s.Size,

MAX(CASE WHEN TypeName = 'Trim' THEN Value END) Trim,

MAX(CASE WHEN TypeName = 'Type' THEN Value END) Type,

MAX(CASE WHEN TypeName = 'Bleed' THEN Value END) Bleed

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size

Unfortunately, neither the above query and SQL Server 2005s pivot function are dynamic - you must know in advance what columns you want to pivot. If you want a more dynamic report, you have to write an ad-hoc query. Something like the one below should hopefully get you started:

CREATE PROCEDURE [dbo].[MagazinePivot]

@.ID int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.pivot VARCHAR(2000)

SELECT @.pivot = COALESCE(@.pivot + ', ', '') + 'MAX(CASE WHEN TypeName = '''+ TypeName + ''' THEN Value END) ' + TypeName

FROM MagType WHERE ID IN (

SELECT TypeID FROM MagAttribute WHERE SizeID = @.ID

)

DECLARE @.sql VARCHAR(2000)

SET @.sql = '

SELECT s.Size, ' + @.pivot + '

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size'

EXEC (@.sql)

END

|||

northside!

Dude quality post!

Gonna take me a while to work through this one though.

Thanks bud

DB Freezes When Replication Agent Cant Deliver

Hi

We are repeatedly having the trouble of our publisher database becoming unresponsive during network problems.
We have publisher in carribean, distribution agent in canada and subscribers all over. The problem comes when publisher cant deliver to distributor due to network outages.

We are planning on moving the distributor to the carribean ultimately but that will take a while, and our application repeatedly needs to be restarted due to these freezes.

Does anyone know of this problem & possible ways to fix it in the short term (short of changing our replication topology).

is this a bug in replication?

ps.database is merge & transactional pubs. (some tables merge/some trans)

help much apprec,
desAs you say those are network problems, make sure they are persistent.
If so take netadmin help to resolve them.

Are you performing replication over internet?
Refer thru event viewer,SQL error log on both the machines for any assessment.|||Yes, replication is over the internet, so cant really troubleshoot the outages - have been various ddos attacks affecting us.

have found no signs of trouble in error logs/eventvwr - only support saying apps are freezing & claiming is db...guess ill have to take a look at processes for deadlocks while frozen (unfortunately thats usually around 4am! :-(
)...

thanks for advice
des|||I think replication process Internet (on WAN) is slow as compared to over LAN.

What is the level of service pack on all invovled servers?

Review information from this http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/proxy.mspx (http://www.microsoft.com/info/smart404.asp?404;Technet page about configuring ISA and usage of proxy server involved in replication over WAN.
HTH|||all servers win2000 sp4 with sql2000 sp3.
not a WAN, entirely seperate networks.

our long term solution is to move the distributor to the same network as publishers so that publishing dbs dont depend on delivery over internet.

will have to investigate logging the process locks during freezes...
thanks for responses
des|||at last...it happens while I am here to see it - as usual turns out that it is the application feezing, not the database! back to the developers...
thanks satya
des|||Thats the reason, I wanted to refer the logs for any information on this process.