PAE SQL server questions...

Hey all,

I'm wondering what command I could issue to see the amount of memory a named instance is using from within the instance itself. We've enabled address extensions (PAE), and task manager no longer shows the correct amount of mem being used by the process. Under perfmon, I've added the object MSSQL$INSTANCENAME:Memory and I'm looking at the Total Server Memory. I'm seeing 4 gigs, when max memory is capped at 2gigs. This must show the server memory and not the instance memory ? Is there some way to see the instance memory ?

Cheers,
-KilkaDBCC MEMORYSTATUS. It'll give you enough to start with. Coupled with MEMUSAGE, you can pi-point the exact object that hogs the memory.|||Thanks rdjabarov,

I'll have to check this out on our testing environments. I've read that MEMUSAGE can cause the instance to crash, I'll have to do some more testing when I get back to work to verify that it'll be safe to use on live.

Cheers,
-Kilka

owner data objects

I've a problem.

In my MSSql db I've some tables named username.mytable and only one
store procedure named dbo.sp;

CREATE TABLE [pippo].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [pluto].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from mytable
GO

but when I try to execute dbo.sp (from pippo or pluto connection) I've
this error (users pippo and pluto are owner):

Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
Invalid object name 'mytable'.

How can I access to pippo's (or pluto's) data from dbo.sp?

thanks!!zMatteo (origma@.edpsistem.it) writes:
> I've a problem.
> In my MSSql db I've some tables named username.mytable and only one
> store procedure named dbo.sp;
>
> CREATE TABLE [pippo].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [pluto].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE [dbo].[sp]
> AS
> select * from mytable
> GO
>
> but when I try to execute dbo.sp (from pippo or pluto connection) I've
> this error (users pippo and pluto are owner):
> Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> Invalid object name 'mytable'.
> How can I access to pippo's (or pluto's) data from dbo.sp?

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9629B0769BAFBYazorman@.127.0.0.1>...
> zMatteo (origma@.edpsistem.it) writes:
> > I've a problem.
> > In my MSSql db I've some tables named username.mytable and only one
> > store procedure named dbo.sp;
> > CREATE TABLE [pippo].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [pluto].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE [dbo].[sp]
> > AS
> > select * from mytable
> > GO
> > but when I try to execute dbo.sp (from pippo or pluto connection) I've
> > this error (users pippo and pluto are owner):
> > Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> > Invalid object name 'mytable'.
> > How can I access to pippo's (or pluto's) data from dbo.sp?
> SELECT * FROM pippo.mytable

but for user pluto i'd make a new store procedure...
I'd resolve the problem (attention: it's ok only for not sysadmin users)!!:

CREATE TABLE [dbo].[mytable] (
[user] [smallint] NOT NULL,
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[myview]
AS
select * from mytable
where user=user_id()
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from myview
GO