Can't check "Check existing data on creation" in relationship

Hi
When I added relationship to table, checked "Check existing data on
creation" and clicked "Save" in Enterprise Management tool, it has no error.
However, when I check tables again, the "Check existing data on creation"
option was unchecked.
Help me!It's the expected behaviour: the first time you create the
relationship, the existing data is verified (if "Check existing data on
creation" is checked) and subsequently all the new data is verified (if
"Enforce relationship for INSERTs and UPDATEs" is checked).
If you modify something in the relationship (for example: the
relationship's name or the cascade updates/deletes checkboxes), the
existing data does not need be verified again (that's why this checkbox
is unchecked when you look at it later).
Only if you modify the relationship in more semnificative ways (for
example: different columns), you should make sure that you click on
"Check existing data on creation" so the existing data will be verified
according to the new relationship.
Razvan

Cant Cascade Delete

I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.

I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative.

Thanks

Hello my friend,

I see that you have a tree-like table. You have records that have a parent record, that can have a parent record that can have a parent record in the same table, and so on. The function at the bottom will help. You will need to change the table name from tblTree and the field names ParentID and PageID to whatever you have called them.

Anyway, the function will return a list of all child records. For example, if PageID 2 had childs 5 and 8, and 5 had 3 childs 67, 68, and 70, the resultset would look like the following: -

2
5
67
68
70
8

All you need to do is run a delete against this returned set as follows, which deletes number 2 and all of its children: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(2))

The function is as follows: -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

If you have any questions on this, please let me know.

Kind regards

Scotty

|||Excellent stuff. Thank you!

can't block delete permissions

I’m trying to lock down an audit table in our database. As a test, I opene
d the table’s ‘manage permissions’ dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database
role membership and saw that she was a member of the db_owner role, so I rev
oked that. I then ran a DENY statement: "deny delete on Histories to edenr".
I removed her memberships in the db_accessadmin and db_securityadmin roles,
and had her close and reop
en Enterprise Manager. After all that, she was still able to delete records.
The manage permissions dialog for this table shows that she is denied delete
permissions. She is still a member of the public, db_datareader, and db_dat
awriter groups, but that shouldn’t override explicitly denied permissions.
I’m the dbo of the datab
ase, so I certainly should have sufficient rights to issue a denial.
What does it TAKE to block a programmer from having permission to delete rec
ords?Yes but what Login is Enterprise Manager using? It is probably not hers.
Andrew J. Kelly SQL MVP
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Hi,
Check the role associated for the user first by executing below command:-
sp_helplogins <Login_name_for that _user'
If you have any roles apart from db_datareader and db_datawriter revoke
that.
After this Execute the below command
use <dbname>
go
deny delete on <table_name> to <user_name>
After that login to query analyzer using that user and run the command:-
select suser_sname()
Now execute the delete statatement on that table.
Thanks
Hari
MCDBA
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Thanks for the suggestions. I tried this, and got the same result. It did ha
ve the effect of re-confirming that the deletions were being run under the p
ermissions of the user in question, which was useful.
The goal here is to be able to block anybody, including programming team mem
bers, from being able to delete records in the production database's audit t
able.
Got any other suggestions where she might be getting delete permissions that
override the explicit denial?
"Hari" wrote:

> Hi,
> Check the role associated for the user first by executing below command:-
> sp_helplogins <Login_name_for that _user'
> If you have any roles apart from db_datareader and db_datawriter revoke
> that.
> After this Execute the below command
> use <dbname>
> go
> deny delete on <table_name> to <user_name>
> After that login to query analyzer using that user and run the command:-
> select suser_sname()
> Now execute the delete statatement on that table.
>|||Check server roles as well. Maybe she is a member of
sysadmins either directly or through windows group
membership
-Sue
On Fri, 2 Jul 2004 09:07:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the suggestions. I tried this, and got the same result. It did h
ave the effect of re-confirming that the deletions were being run under the
permissions of the user in question, which was useful.
>The goal here is to be able to block anybody, including programming team me
mbers, from being able to delete records in the production database's audit
table.
>Got any other suggestions where she might be getting delete permissions tha
t override the explicit denial?
>"Hari" wrote:
>|||Thanks--it looks like that was it. Most of our programmers, including the on
e I'm using as a test case, are members of the System Adminstrators role, an
d the System Adminstrators role has delete permissions on any object in any
database.
All domain admins are automatically members of the sysadmins role, so anyone
who is a domain admin can't be removed from the group even if I decided tha
t was the best solution.
It looks like permissions granted due to membership in the sysadmins role ca
n't be overridden by a denial? Is there any way to override these permission
s in a particular database?
"Sue Hoegemeier" wrote:

> Check server roles as well. Maybe she is a member of
> sysadmins either directly or through windows group
> membership|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: 0-1pool76-99.nas29.thornton1.co.us.da.qwest.net 67.4.76.9
9
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.security:21673
Good - glad to hear that helped you track it down.
On the sysadmins, someone who is a member of the role can do
everything. Members of this role bypass any denies you set
up for them. You can't override this on any level, not by
database or anything else. They can do whatever.
Regarding domain admins, they get their access through the
BUILTIN\Administrators group in SQL Server that is by
default a member of sysadmins. You can remove the
BUILTIN\Administrators but doing this can cause some
problems. Whether you get problems or not depends. The
following article has an more information section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Tue, 6 Jul 2004 11:38:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks--it looks like that was it. Most of our programmers, including the o
ne I'm using as a test case, are members of the System Adminstrators role, a
nd the System Adminstrators role has delete permissions on any object in any
database.
>All domain admins are automatically members of the sysadmins role, so anyon
e who is a domain admin can't be removed from the group even if I decided th
at was the best solution.
>It looks like permissions granted due to membership in the sysadmins role c
an't be overridden by a denial? Is there any way to override these permissio
ns in a particular database?
>"Sue Hoegemeier" wrote:
>

can't alter column to NOT NULL

If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:

> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql

Can't Add Tables To A Diagram

SQL Server 2000 - Latest SP, etc...
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
JerryHi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Cant add fields to table in EM - Odd SP3a prob?

Ok, I think this is a SP3a issue but I wanted to know if anyone else had this same problem.

I am trying to add fields to an existing table but everytime I do, I get a message saying:

'Races' table
- Unable to create index 'IX_Races2'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

I have narrowed it down to this. I can add the field if I add it to the bottom of the field list AND make it allow NULL. BUT...if I try to move the column up to another position (no, I don't need it in that position I know), it throws the error.

Also, if I add the field to the bottom of the field list, then try take off the NULL and fill in a default value, it still throws me this error. Someone tell me that this is just a SP3a issue. I have done this kind of thing before and never ran across this problem before.

Thanks,
GregHmm...ok. I have really narrowed it down now. The version that I am running on my server is 8.00.859. The version I have on my development machine is 8.00.760.

I detached the db from my server, attached it on my box, tried the same thing as above in my first post, and I was able to do it without a problem.

Anyone else experience this?

Greg

Cant add bit column to unique index

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.

Can anyone shed any light on the problem?

Thanks.

You can not create index on BIT data type and that's the reason you can not see column.|||

Where can I find documentation on that? Why is that restriction in place?

|||

JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:

DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO

The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.

I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.

|||

I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.

|||Oh Thanks ZLA. Sorry about that.

Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

sql

Cant access table due to no owner (I think)

I can't access a number of tables on my SQL Server 7.0 database. Have checked using Enterprise Manager and the table does not appear to have any owner. When trying to do anything with the table I always get the following message

{SQL-DMO} The name 'Table Name' was not found in the tables collection. If the name is a qualified name, use [] to seperate various parts of the name, and the try again.

Have tried to change the owner by using sp_changeobjectowner but I am either getting the syntax wrong or it ain't working.

Any ideas??

c8lWhen you look in Enterprise Manager, what do you see?|||Hi,

The only thing I see is the table name (TABLE1), the type (USER) and the creation date. The owner column is blank. Normally you would expect it to say dbo.|||Blank?

What happends when you run this in QA?

SELECT * FROM INFORMATION_SCHEMA.Tables

Maybe you need to close EM and relaunch it. ...|||Thanks for the help but the problem has been resolved, I had to manually update the sysobject table so that the UID for each of the objects that could not be accessed was set to 1. For some reason this was 6.

Cheers
c8l

Can't access OLEDB datasource

The following problem is occuring when I try to access a
table either via an oledb connection or via a linked
server
I can enter the query in the rs/text box and run it
sucessfully. If I either goto edit the query via the
query tool or select the data option. Visual Studio
crashes.
This scenario reproduces the error
select * from link...table, however
this scenario works
select * from openquery(link,'select * from table')
I have downloaded sp1 for reporting services
thanks
DanAre you using the generic query designer, or are you using the graphical
query desinger?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:b92401c479a8$2a760220$a601280a@.phx.gbl...
> The following problem is occuring when I try to access a
> table either via an oledb connection or via a linked
> server
> I can enter the query in the rs/text box and run it
> sucessfully. If I either goto edit the query via the
> query tool or select the data option. Visual Studio
> crashes.
> This scenario reproduces the error
> select * from link...table, however
> this scenario works
> select * from openquery(link,'select * from table')
> I have downloaded sp1 for reporting services
> thanks
> Dan|||Thank you for the response, I can type in a query in the
generic designed, however if I select edit which is
supposed to launch the graphic tool, it crashes. This is
the same behavior whether I use the oledb interface or
try to access the same table via a linked-server
Regards
>--Original Message--
>Are you using the generic query designer, or are you
using the graphical
>query desinger?
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>
>.
>|||Unfortunately, the graphical query editor is a component of Visual Studio
which we reuse. If you send me your query, I can file a bug on their side.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"dan" <anonymous@.discussions.microsoft.com> wrote in message
news:befe01c479d1$f409b860$a501280a@.phx.gbl...
> Thank you for the response, I can type in a query in the
> generic designed, however if I select edit which is
> supposed to launch the graphic tool, it crashes. This is
> the same behavior whether I use the oledb interface or
> try to access the same table via a linked-server
> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.|||Thanks
Here is the example: SELECT * FROM NSXLINK...KNA1
NSXLINK is an OLEDB compliant linked server kna1 is the
table. If you select edit you get the error.
If however you submit the following query
SELECT * FROM OPENQUERY(NSXLINK,'SELECT * FROM NSXLINK')
it works. The same problem will appear if you use the
OLEDB provider directly, without the linked server
If you want I can get the error from the program that is
generated
thanks again
dan
>--Original Message--
>Unfortunately, the graphical query editor is a component
of Visual Studio
>which we reuse. If you send me your query, I can file
a bug on their side.
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:befe01c479d1$f409b860$a501280a@.phx.gbl...
>> Thank you for the response, I can type in a query in
the
>> generic designed, however if I select edit which is
>> supposed to launch the graphic tool, it crashes. This
is
>> the same behavior whether I use the oledb interface or
>> try to access the same table via a linked-server
>> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
>> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to
access
>> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.
>
>.
>

CanShrink=True doesnt work

I have added to detail rows to my table and set the canshrink property to
them all to be =True.
I only use one field in each row, and if there is no value in that field you
would think it would shrink the whole row to nothing. It keeps the row
there and takes up the space of the whole row.
Has anyone else seen this or have any idea why CanShrink does not work.?Did you ever find a solution to this? I have the same problem. I have a
matrix control that has the potential to have four rows per group. However,
depending on the group I only want to show some rows and not others. I'm
able to hide the values in the rows, but I can't hide the row.
Thanks!
Ian

Cannot use the reserved user or role name db_datareader.

Hi, I have got a problem. When I try to access my database table Users, I get the following error:
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
SoI tried to grand this select command in MS Web Data Administration, butit doesnt work. When I try to grand db_datareader role to dbo, I getthe following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'.

Does someone have an idea where could be a problem?

You don't need to be granting db_datareader to dbo, you need to beputting the user that you're accessing the database with in that role,or at least giving it SELECT permissions on that table, or better yet,giving it EXECUTE permissions on a stored proc that selects from thattable.

Cannot use TEXTIMAGE_ON when a table...

I am wondering if someone can help solve this question I have a table
in sql server 2000, I setup it using Enterprise manager.

When I generate an SQL Script for this table it scripts as:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Which is fine, however when I try to insert that into another database
using query anaylser I get the following error:

Server: Msg 1709, Level 16, State 1, Line 2
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image
columns.

OK I know I can remove the TEXTIMAGE_ON [PRIMARY] and that solves the
problem, however I have written some scripts to automate script
generation process, and this TEXTIMAGE thing, throws a spanner in the
automation process.

Any to suggestions as to why this is happening?

If I try building a new table manually using enterprise manager
creating the same table definition above, then script it, I get:

CREATE TABLE [dbo].[CubicleConfiguration2] (
[CubicleConfigurationID2] [int] IDENTITY (1, 1) NOT NULL ,
[Description2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Which is correct and should be generated in the first place

Any ideas as to why enterprise manager decides to add a TEXTIMAGE_ON
[PRIMARY] and break it?

The question is has something in the schema been corrupt?, how do I
return it back to normal?Hi

It seems that at some point the table may have contained a text or image
column and there is an entry left in sysindexes with an indid of 255 for
that table.

Without modifying sysindexes directly you may have to resort to re-creating
that table under another name, transfering the data, dropping the original
table and renaming the new one. Possibly setting the SQL-DMO TextFileGroup
Property will be possible, but I have not tried it.

John

"MrDom" <mr_dom_is@.hotmail.com> wrote in message
news:1117665527.868445.165330@.f14g2000cwb.googlegr oups.com...
>I am wondering if someone can help solve this question I have a table
> in sql server 2000, I setup it using Enterprise manager.
> When I generate an SQL Script for this table it scripts as:
>
> CREATE TABLE [dbo].[CubicleConfiguration] (
> [CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Which is fine, however when I try to insert that into another database
> using query anaylser I get the following error:
> Server: Msg 1709, Level 16, State 1, Line 2
> Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image
> columns.
>
> OK I know I can remove the TEXTIMAGE_ON [PRIMARY] and that solves the
> problem, however I have written some scripts to automate script
> generation process, and this TEXTIMAGE thing, throws a spanner in the
> automation process.
> Any to suggestions as to why this is happening?
> If I try building a new table manually using enterprise manager
> creating the same table definition above, then script it, I get:
>
> CREATE TABLE [dbo].[CubicleConfiguration2] (
> [CubicleConfigurationID2] [int] IDENTITY (1, 1) NOT NULL ,
> [Description2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Which is correct and should be generated in the first place
> Any ideas as to why enterprise manager decides to add a TEXTIMAGE_ON
> [PRIMARY] and break it?
> The question is has something in the schema been corrupt?, how do I
> return it back to normal?|||I don't really know, but what does this return:

select objectproperty(object_id('CubicleConfiguration',
'TableHasTextImage'))

If you get 1, and CubicleConfiguration doesn't have a text column, then
it's likely that there's some sort of metadata corruption - you could
try dropping and recreating the table to see if it fixes the problem.

Alternatively, if that isn't an option for some reason, and if you only
have one filegroup, then you could use the SQLDMOScript2_NoFG constant
to prevent the filegroup clause from being included in your script. (I
assume you're using SQLDMO to generate your scripts - if you're using a
third-party tool, then you'd have to check the documentation for the
tool).

Simon|||yes when i ran the above script it did infact return 1.|||I checked the the sysindexes for that database and I did infact find
another index, with an indid of 255.

It's strange that the index doesn't show up in enterprise manager, or
DBCC doesn't update it and remove it from the sysindex table.

Thanks for your help guys!|||Hi

That is because it is not really an index.

In BOL the documentation for sysindexes/Indid
255 = Entry for tables that have text or image data

I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
mopped it up, but it doesn't seem to.

John

"MrDom" <mr_dom_is@.hotmail.com> wrote in message
news:1117728125.050213.13480@.g14g2000cwa.googlegro ups.com...
>I checked the the sysindexes for that database and I did infact find
> another index, with an indid of 255.
> It's strange that the index doesn't show up in enterprise manager, or
> DBCC doesn't update it and remove it from the sysindex table.
> Thanks for your help guys!|||John Bell (jbellnewsposts@.hotmail.com) writes:
> That is because it is not really an index.
> In BOL the documentation for sysindexes/Indid
> 255 = Entry for tables that have text or image data
> I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
> mopped it up, but it doesn't seem to.

This script repros the problem:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
some_text text,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
Go
ALTER TABLE CubicleConfiguration DROP COLUMN some_text
go
select objectproperty(object_id('CubicleConfiguration'),
'TableHasTextImage')
go
DROP TABLE CubicleConfiguration

The good news is that SQL 2005 gets it right.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cannot use global variables in Table Footer

Hi,
I tried to use the global variables "totalpages" in my table footer to decide whether to hide or show if there are no records from the dataset.
But then I found out that global variables can only be used in page header/footer.
Is there any way i could check whether there's records returned, and hence control the visibility.
Thanks.Good Evening!
Right or wrong - but never mind me using stored procs for everything!

For ALL MS RS reports we use stored procedures - for everything...
So in every stored proc we count records in a lot of various ways - but primarily we use in the first line
Select
Count(1) as Expr1 - Expr1 is our indicator that data has been populated within the SQL Select for all records processed (may have some records or not)
You should never use Count(*) anyway because it does another "scan" of the DB to get the result...
So within MS RS the first thing we do is inspect EXPR1 for a value
No Value
Display "Selection Parameters Found No Information For Your Request" in a text box that was added to the Header of the Report
Else
Display the data within MS RS.
The header text box has an IIF condition and we BOLD in RED so it is visible for the users who do not like to read what they are getting
I don't know how to do if the SQL Select was a "TEXT" string versus a stored procedure! Probaly just as easy - I guess....
We have also in a lot of instances where we use a SQL Stored Proc driver which means and it works really nice if you have SELECTS <= 8000 characters...
We already know the fields to populate the MS RS REport so we just take the parameters passed - look up in a table the SQL for the particulare MS RS report and insert the parameters passed by MS RS so we always get a return value(s) from the SQL Stored Proc driver that performs and EXEC for the SQL select we pull from the table and then declare and insert in the SQL script the values the user passed from MS RS.
So the return value from the EXEC is passed back to use and we cast as EXPR1.
I got to "woordy" here - but nevertheless - I would hope that not only for no data from a Select is captured but also other error situations that might occur as well - so you can pass this information back as well especially if your environment is OLTP versus a warehouse...
Best regards



|||

eeyore21,
CountRows(Scope) where Scope is your dataset. ex: Place=IIF(CountRows("MyDataset") = 0, True, False) as Visibility->Hidden->Expession for your object that you want to hide/show based on record count.

Hope this helps,
Mike

|||

Thanks,
It sure helps alot.

Cannot update the table in SQL Analyzer

I want to update the table
se.g update myTable set t1=0,t2= 0
However, i got the following error
Could not allocate space for object '(SYSTEM table id: -631615181)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.Looks like you're either out of hard drive space on the SQL Server, or the
filegroup where your tempdb (and probably master, etc.) are. Check and make
sure you have enough drive space, and maybe run some dbcc on your database
once you're sure you have at least 10% hard drive space free.
- Nevyn
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23w1JmfNCFHA.3976@.tk2msftngp13.phx.gbl...
>I want to update the table
> se.g update myTable set t1=0,t2= 0
> However, i got the following error
> Could not allocate space for object '(SYSTEM table id: -631615181)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>|||Agnes
1) Restart SQL Server (It will create a new TEMPDB)
2) DBCC SHRINKFILE
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23w1JmfNCFHA.3976@.tk2msftngp13.phx.gbl...
> I want to update the table
> se.g update myTable set t1=0,t2= 0
> However, i got the following error
> Could not allocate space for object '(SYSTEM table id: -631615181)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>

cannot update table

I have an msde database connected to a microsoft project front end.
I am trying to update a table with values. When I use a stored procedure
everything works fine but when I try and edit the table directly I am warned
that the 'the recordset is not updatable'
Is this 'by design'? or are there permission issues?
I am logged into the database as a user almx who has database creation
rights.
I tried logging into the database as sa but I it makes no difference.
Are there any specific permissions I need to set to allow users to update
the table values directly or is this not possible in a MS Access Project?
June
Access won't allow you to update records unless there's a primary key
or unique index defined on the tables.
--Mary
On Wed, 16 Jun 2004 12:24:34 +0100, "June Macleod"
<junework@.hotmail.com> wrote:

>I have an msde database connected to a microsoft project front end.
>I am trying to update a table with values. When I use a stored procedure
>everything works fine but when I try and edit the table directly I am warned
>that the 'the recordset is not updatable'
>Is this 'by design'? or are there permission issues?
>I am logged into the database as a user almx who has database creation
>rights.
>I tried logging into the database as sa but I it makes no difference.
>Are there any specific permissions I need to set to allow users to update
>the table values directly or is this not possible in a MS Access Project?
>June
>

Cannot update some records on Sql server 2005

Hi,
I have a problem on updating some records on sql server 2005.
I can update the records in the table apart from some records.
Records are added to table by a .net application.
When I try to update some records on the table (1-2 records ~1500 records) sql server shows "executing query" message but it cannot execute the update query for these records.
on the other records updating query works fine.
Do you have any idea?Question moved to SQL Server forum .

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx
.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.
What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.