INFORMATION_SCHEMA.PARAMETERS

Is anybody able to access system tables used by INFORMATION_SCHEMA.PARAMETERS?

Namely: sys.sysscalartypes, sys.sysschobjs and sys.syscolpars.

A select on any of these failes with 'Invalid object name'

These are system base tables that are used by the database engine only.

Please see http://msdn2.microsoft.com/en-us/library/ms179503.aspx

Is there a reason why you would want to access these base tables ?

|||Curiosity really...

INFORMATION_SCHEMA.PARAMETERS

Is anybody able to access system tables used by INFORMATION_SCHEMA.PARAMETERS?

Namely: sys.sysscalartypes, sys.sysschobjs and sys.syscolpars.

A select on any of these failes with 'Invalid object name'

These are system base tables that are used by the database engine only.

Please see http://msdn2.microsoft.com/en-us/library/ms179503.aspx

Is there a reason why you would want to access these base tables ?

|||Curiosity really...

Infameous 0x80070005 ERROR: Access denied

Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:

1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.

I keep not being able to connect, and receving 0x80070005, what I'm
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||

Did you ever get a response or resolution to this?

Hopefully, your system isn't still DOA 7-8 months later.

We have exactly the same issue with a brand-new server running WinServer 2003x64 R2

Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?

|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/

Infameous 0x80070005 ERROR: Access denied

Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:

1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.

I keep not being able to connect, and receving 0x80070005, what I'm
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||

Did you ever get a response or resolution to this?

Hopefully, your system isn't still DOA 7-8 months later.

We have exactly the same issue with a brand-new server running WinServer 2003x64 R2

Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?

|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/

Inexplicable Invalid Syntax error

I created a DB in Access.. then I converted it to work with SQL Server. I have several tables and queries that are working just fine with it.

However, the other day I made a few changes to one of the tables ( Order table), and it seems no matter what kind of query I do with it, it always returns this error:

Invalid syntax near the keyword 'Order'

The query is IDENTICAL to about 10 others like it.. but no matter what kind of query I do to this Order table.. it always returns the same error. I've stepped through it, looked at the query builder, and there's nothing wrong with the format. Here is what the code looks like..

strSQL = "SELECT OrderId FROM Order WHERE SessionId = '"
+ "testfudgeddata" + "' ";
cm.CommandText = strSQL;
dr = cm.ExecuteReader();

while( dr.Read() )
{
orderId = dr.GetInt32(0);
}

It always fails when ExecuteReader() gets called.. with the same error. The syntax is exactly the same as many other queries.. and I even typed in 'testfudgeddata' into the db directly. I am thinking this problem might be related to some proxy-type information that I need to clean out due to the change of the name of this table since it was created in Access.. anyone ever see anything like this?

BrentORDER is a reserved word in SQL Server. change the table name (!)|||Try placing the table name in square brackets.|||AAAAK!

It seems every time I ask for help, I figure out the problem right after. I ran Query Analyzer and was a little confused why my Order table text was blue.. and the other tables were black.

Order is KEYWORD!! DUH!!

Anyways, it's a little funny I guess.. hope you can all get a good laugh from my misery.. heh|||Thanks for the reply guys.. looks like all 3 of us posted at exactly the same time. ;)sql

Indexing with null or zero

I am adding an int field to a table that will be used to link it to another
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:

>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:

>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK

>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:

> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:

> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:

> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>

Indexing

Hi,
Could someone please tell me the "best" way to index a table? Is the best
way (defined as quickest access to data) to create an index on every field
individually that might be queried, or is it best to create an index that
contains multiple fields if that's how they appear after the WHERE clause.
Thanks,
SteveHi Steve
Check out
http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.c...ance_audit7.asp
http://www.sql-server-performance.c..._structures.asp
What the article doesn't specify is that it is always worth running the
index tuning wizard just to see what it comes up with, in case you may have
missed something or alternatively as a starting point. I would use this for
analysis and implement the suggestions you believe to be suitable rather tha
n
implement them verbatim.
John
"Steve Caliendo" wrote:

> Hi,
> Could someone please tell me the "best" way to index a table? Is the best
> way (defined as quickest access to data) to create an index on every field
> individually that might be queried, or is it best to create an index that
> contains multiple fields if that's how they appear after the WHERE clause.
> Thanks,
> Steve
>
>

Indexes and Filegroups

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried

The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

add new field int type with identity and set it as primary key

if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

Hi Rama,

You can use the option EXPAND VIEWS in SELECT clause for your purpose.

Microsoft recommend's, however, to let the query optimizer dynamically determine the best access methods to use for each individual query

Jag

|||

Can u explain me in detail.if possible with example becz i'm new to DB stuff.

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

First of all you can't create a index on your view.

Reason: suser_sname is not a deterministic function. So it wont to allow you to create a index on this..

|||

Can u explain me in detail .I’m new to indexed views.

Can we create indexed view for all data in tables and can be filtered this data by referencing this indexed view with in another view? Can u consider this point also.

If this is possible then, is there any way restrict not to use indexed views by query optimizer .