Concat_null_yields_null

How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
in all databases and permenantly
?Originally posted by Karolyn
How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
in all databases and permenantly

?
check sp_dboption in bol|||USE master
EXEC sp_dboption 'M158005', 'concat null yields null', 'FALSE'|||USE master
EXEC sp_dboption 'M158005', 'concat null yields null', 'FALSE'

this command doesn't change anything

Select NULL + 'TOTO'
--> NULL|||i'm replying because Karolyn asked me to in another thread

"How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
in all databases and permenantly"

i have no idea

:D

whenever i run into null issues with concatenation, i always use COALESCE

e.g.

select coalesce(foo,'') & coalesce(bar,'') as foobar|||I agree with Rudy. I don't think code should rely on this setting being one value or another.|||The pb is that i'm migrating sql queries written for SYBASE
and I've got to check for concatenations in all queries
to put COALESCE() or ISNULL()
on each columns that can have a NULL value
...

I've finished migrating the queries but I still have to
check in the stored proc.

and there's lots of other programs to migrate

so setting this option should facilitate our migration

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
Claudia
Hello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
>> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
>> backing up databases onto compressed volumes is not recommended and not
>> supported. My question is: Is it ok/supported if we backup the databases
>> regularly and then zip the backup files?
>> Thanks in advance.
>> Claudia
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
>> Sure, but have you restored from the compressed volume? ;-)
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>> Seriously, I think there's a few reasons MS recommends against this, but
>> I haven't seen problems either.
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>> Also, another option is to look at some of the 3rd party backup tools out
>> there that will compress on the fly as they backup.
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsqlsql

Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.

Complex View creation question

I need to create a cross-database view (same server) in a master database.
The databases I'm joining in the view are listed in a table in that master.
I'd like for the view to automatically include new databases whose names are
added to that table in the master. How would I go about doing this? I'm no
t
familiar with dynamic sql... TIA.Views aren't going to have dynamic SQL in them, and I wouldn't really
recommend a multi-line table user-defined function even though you could
probably accomplish what you want by using dynamic SQL in it.
I would recommend a hook in your application/middle-tier (optimally) or a
trigger on that master table (less optimal) that would ALTER the view to
include all the existing databases in your master table whenever the list of
databases changes. Just create the ALTER VIEW statement based on the list
from the master table. If done from the app, you have many ways to do this;
if from a trigger, you'd create the SQL string via a cursor or a funky
SELECT statement and then execute it via EXEC (dynamic SQL).
The things to consider in this scenario would be: what impact does changing
the view have on the live system? how frequently does this change? does
the user adding/deleting records in the master table have permissions to
alter the view?
Mike
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:AD0E4C8D-1983-4003-B8FE-09D0222548F4@.microsoft.com...
>I need to create a cross-database view (same server) in a master database.
> The databases I'm joining in the view are listed in a table in that
> master.
> I'd like for the view to automatically include new databases whose names
> are
> added to that table in the master. How would I go about doing this? I'm
> not
> familiar with dynamic sql... TIA.

Complex Query with IF Statement

I have a complex SELECT statement in which it SELECTS about 12 different fields FROM 10 different tables (from 2 different databases) and WHERE there are a bunch of WHERE clauses depending on user entry.

There is one field, say EmpID, in which:

IF EmpID = xx123 THEN the SELECT statement shouldn't be so complex b/c it can't go that deep into the tree of relationships.

ELSE SELECT the COMPLEX query...

So let's say I enter a certain REGION to search for my Employees and I pick WEST, then my SELECT statement should EXECUTE both SELECT statements (if there are 2 of them?) and return something like this:

EmpID....Region....Market......F_ID......Loc
ab323.....WEST......Sales.......123456...Texas
xx123.....WEST......Sales.......unavail...unavail.
cc848......WEST......Marketing..393921...California

As you can see, if EmpID is anything but 'xx123' then all fields are approachable, but if it is 'xx123' then some fields aren't thus it should be a small SELECT statement with 1 or 2 JOIN statements.

SELECT t1.EmpID, t2.Region, t3.Market, t4.F_ID, t5.Loc ...
FROM t1 ... INNER JOIN ... t5
WHERE t2.Region like USER INPUT

Your assistance will be greatly appreciated!I dont think I get the actual problem in hand ... But from what I understand ...

1. You can write a Dynamic SQL to build your own Select query based on some user inputs

or

2. If the values are part of some thing then you can use the CASE expression to hide certain column values as

Select Case Col1 When 'test' then <<Show Col>> End [Hidden Col], Col2 ...
From <<Table>> ...|||Not knowing the table structure I can't say for sure but you might think about using a union statement for something like this. The Union is suitable if the records can be "stacked":

-- first the complex query
Select EmpID, Region, Market, F_ID, Loc FROM etc. Where whaterver.
UNION
-- then the simple query
Select EmpID, Region, Market, '' as F_ID, '' as Loc FROM etc. Where whaterver

Complex Duplicate Row Issue (Need To Keep Shorter Value)

a client of mine caused a bit of an issue with one of our SQL Server
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server.
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jéjé wrote:
>> select p.SKU, min(p.Name) as Name
>> from Products P
>> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
>> on P.sku = p1.sku and len(p.Name) = p2.L
>> group by p.SKU
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||This is a multi-part message in MIME format.
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I believe that this idea will work for the problem as you have =described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript =3D ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<carpeaqua@.gmail.com> wrote in message =news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> > What I need to do is delete the shorter value.
> > SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> > so I need to get rid of SKU 111 w/ the Name value of Glass.
> > What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> > Any help is appreciated.
> > Thanks!
>
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I believe that this idea will work for =the problem as you have described.
CREATE TABLE =#MyTable ( RowID =int IDENTITY , SKU int , Descript varchar(25) )
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( =111, 'Glass Vase' )INSERT INTO #MyTable VALUES ( 111, 'Glass' )INSERT INTO =#MyTable VALUES ( 333, 'Apple' ) DELETE #MyTableWHERE =Descript =3D ( SELECT min ( Descript ) &n=bsp; FROM #MyTable &=nbsp; GROUP BY sku = HAVING count( SKU ) > 1 &n=bsp; )
SELECT *FROM #MyTable
DROP TABLE =#MyTable
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
=wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...>a =client of mine caused a bit of an issue with one of our SQL Server> =databases. They modified the database to create duplicate SKU values.> Each =SKU value has a different Name column value.> > What I need to =do is delete the shorter value.> > SKU Name> 111 Glass Vase> =111 Glass> 333 Apple> > so I =need to get rid of SKU 111 w/ the Name value of Glass.> > What I =have done is created a view that give me a listing of all the> row values =with duplicate SKUs, but I am not sure how to do the Len> comparison =and return the shorter value.> > Any help is =appreciated.> > Thanks!>

---

--=_NextPart_000_0979_01C6B0A2.71C31A40--|||Arnie, if two SKUs have the same description that is going to erase
some good data.
Roy
On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>I believe that this idea will work for the problem as you have described.
>CREATE TABLE #MyTable
> ( RowID int IDENTITY
> , SKU int
> , Descript varchar(25)
> )
>SET NOCOUNT ON
>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>DELETE #MyTable
>WHERE Descript = ( SELECT min ( Descript )
> FROM #MyTable
> GROUP BY sku
> HAVING count( SKU ) > 1
> )
>SELECT *
>FROM #MyTable
>DROP TABLE #MyTable|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.4ax.com...
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>>I believe that this idea will work for the problem as you have described.
>>CREATE TABLE #MyTable
>> ( RowID int IDENTITY
>> , SKU int
>> , Descript varchar(25)
>> )
>>SET NOCOUNT ON
>>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>>DELETE #MyTable
>>WHERE Descript = ( SELECT min ( Descript )
>> FROM #MyTable
>> GROUP BY sku
>> HAVING count( SKU ) > 1
>> )
>>SELECT *
>>FROM #MyTable
>>DROP TABLE #MyTable|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name = (select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) = (select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name => (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) => (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.4ax.com...
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>>
>>This worked wonderfully. Thank you, my friend.|||On Wed, 26 Jul 2006 11:47:07 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What would happen if there were 2 rows with the same SKU and the same length
>[Name]?
>Would they BOTH be left -or deleted, and without indication of an incomplete
>update?
If they were both the longest they would both be left by the DELETE I
wrote, but only one of them would have appeared in the first query.
Roy

complete NOOB hope someone can point me in the right direction

I have been programming access databases for 8 years.
Well today I find out that I need to know SQL.
HUH LOL

well I don't have a clue as to how people even enter data into
a sql database or how to manipulate or retrieve data.

Can someone please recommend a book to get me started?

thank you very much for any and all help with this.sparks (jstalnak@.swbell.net) writes:

Quote:

Originally Posted by

I have been programming access databases for 8 years.
Well today I find out that I need to know SQL.
HUH LOL
>
well I don't have a clue as to how people even enter data into
a sql database or how to manipulate or retrieve data.
>
Can someone please recommend a book to get me started?
>
thank you very much for any and all help with this.

Isn't Access an SQL database? Or since you post in this newsgroup,
you specifically mean SQL Server databases?

SQL Server is a server application that accepts data from clients. Clients
can be implemented in a multitude of ways, both as Windows applications
and web applications. You can write applictions in .Net, in Perl, in VB6,
in Java, in about every language. You can even use Access - this appears
in fact to be a common solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Isn't Access an SQL database?

http://en.wikipedia.org/wiki/Microsoft_Access#Features
One of the benefits of Access from a programmer's perspective is its
relative compatibility with SQL (structured query language) queries may
be viewed and edited as SQL statements, and SQL statements can be used
directly in Macros and VBA Modules to manipulate Access tables. In this
case, "relatively compatible" means that SQL for Access contains many
quirks, and as a result, it has been dubbed "Bill's SQL" by industry
insiders. Users may mix and use both VBA and "Macros" for programming
forms and logic and offers object-oriented possibilities.

Complete Neophyte Question(s)

I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
familiar with databases from a data structure/manipulation standpoint, the
permissions/security model in SQL Server 2005 has baffled me thus far. I
digress...
As far as I can tell, our previous DBA create a role which is called
SP_Exec. I know that this role has permissions defined somehow, but I'll be
damned if I can figure it out. One of our users can modify a particular
view, and another can't. The only difference I can see is that one is a
member of this role and the other isn't. If I remove the role from the user
who CAN modify the view, he no longer can. Alas, I have no idea where this
is defined.
Schemas are also slightly confusing, but I imagine that's just a way of
logically grouping sets of objects. At first glance it seems everything is
utilizing the a schema called dbo. One of our users was having trouble
until I went into the schema and added "View Definition" as a permission.
I'm not even certain what this effectively did, but it solved the problem
while I try to figure out how this whole thing works.
Can anyone point me in the direction of a document that explains this in
English? Perhaps the majority of my hang-ups are with Management Studio and
not the actual structure of the overall permissions model. ANY help would
be appreciated.I'd recommend that you go for a training course ;-)
"James" <minorkeys@.gmail.com> wrote in message
news:eVtdKXmuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
>familiar with databases from a data structure/manipulation standpoint, the
>permissions/security model in SQL Server 2005 has baffled me thus far. I
>digress...
> As far as I can tell, our previous DBA create a role which is called
> SP_Exec. I know that this role has permissions defined somehow, but I'll
> be damned if I can figure it out. One of our users can modify a
> particular view, and another can't. The only difference I can see is that
> one is a member of this role and the other isn't. If I remove the role
> from the user who CAN modify the view, he no longer can. Alas, I have no
> idea where this is defined.
> Schemas are also slightly confusing, but I imagine that's just a way of
> logically grouping sets of objects. At first glance it seems everything
> is utilizing the a schema called dbo. One of our users was having trouble
> until I went into the schema and added "View Definition" as a permission.
> I'm not even certain what this effectively did, but it solved the problem
> while I try to figure out how this whole thing works.
> Can anyone point me in the direction of a document that explains this in
> English? Perhaps the majority of my hang-ups are with Management Studio
> and not the actual structure of the overall permissions model. ANY help
> would be appreciated.
>|||James (minorkeys@.gmail.com) writes:
> I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
> familiar with databases from a data structure/manipulation standpoint, the
> permissions/security model in SQL Server 2005 has baffled me thus far.
From having been very simple-minded in SQL 4.x, it is now quite sophisticate
d.

> As far as I can tell, our previous DBA create a role which is called
> SP_Exec. I know that this role has permissions defined somehow, but I'll > be dam
ned if I can figure it out.
In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
Find the role of interest, and click Properties in the context menu.
Go to the Seucrables tab.
Normally, you grant permissions to roles, and then add users to the
roles. If you were to grant rights to users directly, it would be more
difficult to managed.

> Schemas are also slightly confusing, but I imagine that's just a way of
> logically grouping sets of objects.
Right. There are some security features related to schemas. If you add
an object to a schema owned by someone else, the schema owner becomes
the object that you created. It is also possible to grant permissions
on a schmea, which implies that you get permissions to all existing
and future objects in the schema to which the permissions apply.

> At first glance it seems everything is utilizing the a schema called
> dbo. One of our users was having trouble until I went into the schema
> and added "View Definition" as a permission.
In SQL 2005, users are only permitted to see objects they have permission
to. This is a change from SQL 2000 where the metadata was visible to
all users.

> Can anyone point me in the direction of a document that explains this in
> English? Perhaps the majority of my hang-ups are with Management Studio
> and not the actual structure of the overall permissions model. ANY help
> would be appreciated.
The normal starting point would be
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808
659449c5.htm
in Books Online, but admittedly Books Online is surprisingly thin on
some of the permission topics.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you very much. I believe I have my head wrapped around this better.
When I go to the Securables tab for this role, there are no objects in the
listbox. Simply an Add button. Yet, if I view the properties of certain
stored procedures it will have that role listed with execute permissions.
This seems like a simple request, but all I want is to see the objects that
a certain role has permissions on, and what those permissions are?
Rhetorical: Why is this so difficult/counter-intuitive?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns995FC3C3DACYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> From having been very simple-minded in SQL 4.x, it is now quite
> sophisticated.
>
> In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
> Find the role of interest, and click Properties in the context menu.
> Go to the Seucrables tab.
> Normally, you grant permissions to roles, and then add users to the
> roles. If you were to grant rights to users directly, it would be more
> difficult to managed.
>
> Right. There are some security features related to schemas. If you add
> an object to a schema owned by someone else, the schema owner becomes
> the object that you created. It is also possible to grant permissions
> on a schmea, which implies that you get permissions to all existing
> and future objects in the schema to which the permissions apply.
>
> In SQL 2005, users are only permitted to see objects they have permission
> to. This is a change from SQL 2000 where the metadata was visible to
> all users.
>
> The normal starting point would be
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-78
08659449c5.htm
> in Books Online, but admittedly Books Online is surprisingly thin on
> some of the permission topics.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||James (minorkeys@.gmail.com) writes:
> Thank you very much. I believe I have my head wrapped around this better.
> When I go to the Securables tab for this role, there are no objects in the
> listbox. Simply an Add button. Yet, if I view the properties of certain
> stored procedures it will have that role listed with execute permissions.
> This seems like a simple request, but all I want is to see the objects
> that a certain role has permissions on, and what those permissions are?
> Rhetorical: Why is this so difficult/counter-intuitive?
I did some research, and I think I have the answer. If you do Help->About
what version do you get for Managment Studio? My guess is that you will
see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
me the list of objects.
You can find the latest service pack for SQL Server on
http://support.microsoft.com/kb/913089/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I don't use the word hero very often, but you sir, are the greatest hero of
all time. Thank you for confirming I'm not insane. SP2 has caused the
Securables tab to populate properly and now it actually makes sense.
The only other issue that I'm trying to wrap my head around and can't seem
to google well is the difference between "GRANT" and "WITH GRANT". Can you
enlighten me?
Thank you a million times over!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996577CE42B7BYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> I did some research, and I think I have the answer. If you do Help->About
> what version do you get for Managment Studio? My guess is that you will
> see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
> when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
> me the list of objects.
> You can find the latest service pack for SQL Server on
> http://support.microsoft.com/kb/913089/.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Disregard, seems my google-ing skills were lacking.
More generic question as I'm messing with the Northwind database to get a
thorough understanding of this. If a user isn't a member of any roles and
has no permissions explicitly defined, does it err on the side of deny or
grant? Or does that depend on the permission? Right now I have a user who
has Connect as the only database level permission, no roles, no secureables
but can still view definition, it seems. I'm able to connect and view all
of the tables, although everything else seems locked down. I have refreshed
and can still see them.
Either way, thanks again. I'm much much further than I was yesterday at
this time.
"James" <minorkeys@.gmail.com> wrote in message
news:uFdBmN%23vHHA.2304@.TK2MSFTNGP06.phx.gbl...
>I don't use the word hero very often, but you sir, are the greatest hero of
>all time. Thank you for confirming I'm not insane. SP2 has caused the
>Securables tab to populate properly and now it actually makes sense.
> The only other issue that I'm trying to wrap my head around and can't seem
> to google well is the difference between "GRANT" and "WITH GRANT". Can
> you enlighten me?
> Thank you a million times over!
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns996577CE42B7BYazorman@.127.0.0.1...
>|||James (minorkeys@.gmail.com) writes:
> Disregard, seems my google-ing skills were lacking.
You should not have to go Google to find out what WITH GRANT means. SQL
Server comes with online documentation on you hard disk.
WITH GRANT is one of the more esotheric features in SQL Server in my
opinion, but maybe that says more about the simplistic security of the
system I work with.

> More generic question as I'm messing with the Northwind database to get
> a thorough understanding of this. If a user isn't a member of any roles
> and has no permissions explicitly defined, does it err on the side of
> deny or grant? Or does that depend on the permission? Right now I have
> a user who has Connect as the only database level permission, no roles,
> no secureables but can still view definition, it seems. I'm able to
> connect and view all of the tables, although everything else seems
> locked down. I have refreshed and can still see them.
If no permissions have been granted, then you have no permissions. That is,
if run the below in a database, the SELECT should not return anything:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE USER erik
go
EXECUTE AS LOGIN = 'erik'
go
SELECT name FROM sys.objects
go
REVERT
go
DROP USER erik
go
DROP LOGIN erik
...unless rights have been granted to the public role.
In SQL 2005 a user only has permission to see the definition of objects
to which he been granted some access. More exactly he needs VIEW DEFINITION,
but this permission is implied if he already has SELECT permission.
Where DENY comes in is that it overrides GRANT. Say that a user is a member
of a role that has SELECT permission to a table X, but that himself he
has been denied access to the table. Then he cannot access that table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That makes complete sense to me, but somehow I'm overlooking something.
I have a user named james on a database. If I go into properties for that
user they have no owned schemas. They have no role membership (including
public, which isn't listed here for some reason). If I right-click the
database and go to properties -> Permissions, the only permission they have
is Connect, not view definition.
The only thing I can see is that there's a login of the same name at the
server level which is a member of the Server Role public, but my
understanding is that it's unrelated.
So the long and the short of it, is that this user can view definition on
this database, and I can't figure out why.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996668C8847CBYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> You should not have to go Google to find out what WITH GRANT means. SQL
> Server comes with online documentation on you hard disk.
> WITH GRANT is one of the more esotheric features in SQL Server in my
> opinion, but maybe that says more about the simplistic security of the
> system I work with.
>
> If no permissions have been granted, then you have no permissions. That
> is,
> if run the below in a database, the SELECT should not return anything:
> CREATE LOGIN erik WITH PASSWORD='rtsoppa'
> go
> CREATE USER erik
> go
> EXECUTE AS LOGIN = 'erik'
> go
> SELECT name FROM sys.objects
> go
> REVERT
> go
> DROP USER erik
> go
> DROP LOGIN erik
> ...unless rights have been granted to the public role.
> In SQL 2005 a user only has permission to see the definition of objects
> to which he been granted some access. More exactly he needs VIEW
> DEFINITION,
> but this permission is implied if he already has SELECT permission.
> Where DENY comes in is that it overrides GRANT. Say that a user is a
> member
> of a role that has SELECT permission to a table X, but that himself he
> has been denied access to the table. Then he cannot access that table.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||James (minorkeys@.gmail.com) writes:
> That makes complete sense to me, but somehow I'm overlooking something.
> I have a user named james on a database. If I go into properties for
> that user they have no owned schemas. They have no role membership
> (including public, which isn't listed here for some reason). If I
> right-click the database and go to properties -> Permissions, the only
> permission they have is Connect, not view definition.
> The only thing I can see is that there's a login of the same name at the
> server level which is a member of the Server Role public, but my
> understanding is that it's unrelated.
> So the long and the short of it, is that this user can view definition on
> this database, and I can't figure out why.
If you under database roles look at the public role, does it have any
permissions on anything?
If you run:
execute as login = 'james'
go
select * from sys.fn_my_permissions('dbo.Orders', 'object')
go
revert
What do you see?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

compiling objects to multiple databases

Anyone have any suggestions they could share on compiling objects to multipl
e
databases at the same time on the same server and also to multiple servers?
Example:
Stored procedure needs to be compiled on 60 databases on the same server.
Thanks for any help!Assuming the name of the SP on all 60 databases on the server are the same:
sp_MSforeachdb 'USE ? IF EXISTS (SELECT * FROM sysobjects WHERE name =
''sp_name'' AND xtype = ''U'') EXEC sp_recompile ''sp_name'''
"BL" wrote:

> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||Jack - how would I go about getting them compiled in the database when they
currently exist in a .txt file?
"BL" wrote:

> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||I would write an application using ADO.NET which reads the text files and cr
eate the procedures on
each server/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BL" <BL@.discussions.microsoft.com> wrote in message
news:87375008-C1A7-4009-835E-095F70427CE2@.microsoft.com...
> Jack - how would I go about getting them compiled in the database when the
y
> currently exist in a .txt file?
> "BL" wrote:
>

Compatibility Mode 70 in SQL Server 2008

Hi,
does anybody know if MS's going to deprecate "Compatibility Mode 70"
databases in SQL Server 2008?
With SQL Server 2005, MS already removed CM70 support from all their
Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
afraid they're gonna go one step further.
thx
AxelThere is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in SQL SErver 2008 November CTP.
Ekrem nsoy
"Axel Bender" <axel_bender@.t-online.de> wrote in message
news:fj0jhm$7l0$00$1@.news.t-online.com...
> Hi,
> does anybody know if MS's going to deprecate "Compatibility Mode 70"
> databases in SQL Server 2008?
> With SQL Server 2005, MS already removed CM70 support from all their
> Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
> afraid they're gonna go one step further.
> thx
> Axel|||... and Books Online (ALTER DATABASE and sp_dbcmptlevel) only lists levels
80, 90 and 100.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:6CB644E8-4284-4BA8-86B2-CD764F70EF27@.microsoft.com...
> There is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in
> SQL SErver 2008 November CTP.
> --
> Ekrem nsoy
>
> "Axel Bender" <axel_bender@.t-online.de> wrote in message news:fj0jhm$7l0$0
0$1@.news.t-online.com...
>

Compatibility Mode 70 in SQL Server 2008

Hi,
does anybody know if MS's going to deprecate "Compatibility Mode 70"
databases in SQL Server 2008?
With SQL Server 2005, MS already removed CM70 support from all their
Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
afraid they're gonna go one step further.
thx
AxelThere is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in SQL SErver 2008 November CTP.
--
Ekrem Önsoy
"Axel Bender" <axel_bender@.t-online.de> wrote in message
news:fj0jhm$7l0$00$1@.news.t-online.com...
> Hi,
> does anybody know if MS's going to deprecate "Compatibility Mode 70"
> databases in SQL Server 2008?
> With SQL Server 2005, MS already removed CM70 support from all their
> Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
> afraid they're gonna go one step further.
> thx
> Axel|||... and Books Online (ALTER DATABASE and sp_dbcmptlevel) only lists levels 80, 90 and 100.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:6CB644E8-4284-4BA8-86B2-CD764F70EF27@.microsoft.com...
> There is no "SQL Server 7.0 (70)" item in the Compatibility Level list in the database options in
> SQL SErver 2008 November CTP.
> --
> Ekrem Önsoy
>
> "Axel Bender" <axel_bender@.t-online.de> wrote in message news:fj0jhm$7l0$00$1@.news.t-online.com...
>> Hi,
>> does anybody know if MS's going to deprecate "Compatibility Mode 70" databases in SQL Server
>> 2008?
>> With SQL Server 2005, MS already removed CM70 support from all their Wizards (e.g. the Database
>> Backup Wizard - which is bad enough), so I'm afraid they're gonna go one step further.
>> thx
>> Axel
>

Compatibility Mode 70 databases in SP2

Hi,
Does anybody know if the creation of maintenance plans for Compatibility
Mode 70 databases will be possible with SQL Server 2005 SP2?
According to what I've heard so far, there is no chance to do that in
SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
the wizard). I consider this a severe shortcoming in the product, which
should be tackled by MS.
Any opinions/workarounds?Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:
> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>|||Thanks for the answer, John.
Although I generally agree with you in saying that a compatibility mode
should not be used on a long-term basis, I have to make some additional
points on that topic:
a) We have deployed a lot of CM 70 databases; changing the compatibility
mode for them would not break our application, but it would give the
users very slow response times in parts of the app (this is due to the
change MS made to the query optimizer when it comes to selecting
indexes). We know we have to make changes to the app, but frankly, we
simply cannot afford the time for this now.
b) Most of our customers are not able to write maintenance procedures
(nor are all of our supporters).
c) There is no common scheme that our users follow when it comes to
backing up and checking their databases.
d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
70 ones?
I think, that supporting CM 70 databases would not be a big deal for MS;
for us it would be.
Kind regards,
Axel|||Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:
> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility Mode 70 databases in SP2

Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>
Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility Mode 70 databases in SP2

Hi,
Does anybody know if the creation of maintenance plans for Compatibility
Mode 70 databases will be possible with SQL Server 2005 SP2?
According to what I've heard so far, there is no chance to do that in
SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
the wizard). I consider this a severe shortcoming in the product, which
should be tackled by MS.
Any opinions/workarounds?Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if yo
u
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>|||Thanks for the answer, John.
Although I generally agree with you in saying that a compatibility mode
should not be used on a long-term basis, I have to make some additional
points on that topic:
a) We have deployed a lot of CM 70 databases; changing the compatibility
mode for them would not break our application, but it would give the
users very slow response times in parts of the app (this is due to the
change MS made to the query optimizer when it comes to selecting
indexes). We know we have to make changes to the app, but frankly, we
simply cannot afford the time for this now.
b) Most of our customers are not able to write maintenance procedures
(nor are all of our supporters).
c) There is no common scheme that our users follow when it comes to
backing up and checking their databases.
d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
70 ones?
I think, that supporting CM 70 databases would not be a big deal for MS;
for us it would be.
Kind regards,
Axel|||Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
JohnCompatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility Level

Clicking "Details" from the drop-down menu that lists all the databases in
the Query Analyzer (in SQL Server 2000) pops out a dialog box where all the
databases are listed along with their Compatibility Level. What is this
Compatibility Level? Does this mean backward compatibility i.e. SQL Server
2000's compatibility with SQL Server 7.0? Also why is the Compatibility
Level for all databases 70 - what does it signify? Why not some number other
than 70?
Installing SQL Server 7.0 along with SQL Server 2000 on the same machine -
is that a sane approach or should such a situation be avoided? What problems
could arise if both versions of SQL Server are installed on the same
machine?
Thanks,
Arpan>> What is this Compatibility Level? <<
It is a DBMS specific internal value, that keep certain database behaviors
to be compatible with the specified DBMS version. The values are 80, 70, 65,
or 60 for SQL 2000, SQL 7.0, SQL 6.5 and SQL 6.0 respectively.
For example when you upgrade SQL Server from version 6.5 to version 7.0, the
compatibility will not get updated. You will have to use the procedure
sp_dbcmptlevel to get it to 70. Without this compatibility level, certain
features specific to SQL 7.0 like TOP clause will not work properly. See
details about sp_dbcmptlevel in SQL Server Books Online.
>> Also why is the Compatibility Level for all databases 70 - what does it
signify? Why not some number other than 70? <<
It simply means, this level is specific to SQL Server version 7.0.
>> Installing SQL Server 7.0 along with SQL Server 2000 on the same
machine - is that a sane approach or should such ... <<
You can keep an installation of SQL Server version 7.0 intact on your
computer and also install a named instance of SQL Server 2000 on the same
computer. Details on how to proceed can be found in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )|||Thanks, Anith, for your input. What do you mean by "named instance" of SQL
Server 2000?
Regards,
Arpan
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OuiU2ODlDHA.2244@.TK2MSFTNGP12.phx.gbl...
> >> What is this Compatibility Level? <<
> It is a DBMS specific internal value, that keep certain database behaviors
> to be compatible with the specified DBMS version. The values are 80, 70,
65,
> or 60 for SQL 2000, SQL 7.0, SQL 6.5 and SQL 6.0 respectively.
> For example when you upgrade SQL Server from version 6.5 to version 7.0,
the
> compatibility will not get updated. You will have to use the procedure
> sp_dbcmptlevel to get it to 70. Without this compatibility level, certain
> features specific to SQL 7.0 like TOP clause will not work properly. See
> details about sp_dbcmptlevel in SQL Server Books Online.
> >> Also why is the Compatibility Level for all databases 70 - what does it
> signify? Why not some number other than 70? <<
> It simply means, this level is specific to SQL Server version 7.0.
> >> Installing SQL Server 7.0 along with SQL Server 2000 on the same
> machine - is that a sane approach or should such ... <<
> You can keep an installation of SQL Server version 7.0 intact on your
> computer and also install a named instance of SQL Server 2000 on the same
> computer. Details on how to proceed can be found in SQL Server Books
Online.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Refer to :
http://msdn.microsoft.com/library/en-us/architec/8_ar_cs_9i5u.asp
--
- Anith
( Please reply to newsgroups only )

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
John
Compatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
JohnCompatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John