QUESTION: Status Column Of FK Constraints In Sysobjects - ????
Jul 23, 2005
Greetings:
I have to do a one-off forceful change of some data in a database. I
need to disable some FK constraints, make the data change, and then
re-enable the constraints.
My process will be:
ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'
ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2
I wanted to get some evidence that the constraints were back up after I
did the 'check constraint' statements, so I looked in sysobjects.
There's a status column for the constraints, but I can't make much
sense of the numbers. Most of the FK constraints that I have not
changed are of status 0, but when I NOCHECK a constraint, it goes to
2304. When I CHECK it again, it's 2408.
Can someone explain this, and suggest a way to double-check that my
constraints are in fact enabled once I run the CHECK statement?
Thanks!!
View 2 Replies
ADVERTISEMENT
Sep 6, 2001
Hi all,
I have a question about accessing a specific column from the database.
I know when we use: select name from sysobjects would select all the table names and the constraints from the database. I want to select all the column names in each of the table. How can I go about doing this.
Thank you
View 1 Replies
View Related
Jul 25, 2007
Everyone,
Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?
Thanks
Tim
View 3 Replies
View Related
Jul 17, 2001
Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
View 4 Replies
View Related
Nov 22, 2007
I have about 100 databases which yeterday had the wrong ddl for a column added, we have a simple cursor that loops through all databases on the server and runs the sql statement.
The following was run
ALTER TABLE salesrep ADD newCol bit DEFAULT 1
Problem is that it should have been this (and preferably have had a named constraint):
ALTER TABLE salesrep ADD newCol bit NOT NULL DEFAULT 0
Problem is now i dont know the names of the contraints they have random hex on the end "The object 'DF__SALESREP__newCol__5BCD9859' is dependent on column..."
How do i just drop this column with ANY constraints on it?,
I really dont want to go through the databases 1 by 1 and look up the constraint name then drop the constraint, then drop the column so i can add it in correctly.
I wish the following would just work.
ALTER TABLE salesrep ALTER COLUMN newCol bit NOT NULL Default 0
View 1 Replies
View Related
Jan 7, 2008
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
Jun 28, 2006
Hi everyone,
while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column.
I understood nothing about its usage. So would you please inform me ?
Thanks
View 1 Replies
View Related
Oct 8, 2007
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition:
Column Name Data Type Allow Nulls
--------------------------------------------------------------------------------------------
Column1(PrimaryKey) int No
Column2 nvarchar(MAX) No
View 9 Replies
View Related
May 12, 2004
How to interprete the colum status in sysconstraints table ?
(I have some thing like 133141).
View 5 Replies
View Related
Oct 20, 2015
can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?
View 3 Replies
View Related
Aug 6, 2007
I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.
I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?
I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2
Thanks,
Chris
View 15 Replies
View Related
Jun 4, 2008
Hi,
On my page I am using a wizard control with textboxes to collect data from the visitor.
When they click the finish button - this inserts their data into a Sql database.
On the form is a checkbox.
I dont know what type to use in the database column to accept this and also
how to configure the checkbox on the page so I know if its checked.
Also what would be inserted into the database column "yes" "no" or a numerical value??
Thanks for any advice?
amereto
View 1 Replies
View Related
Apr 7, 2008
I am looking for assistance coming up with a function (or maybe not a function if there is a better way) to make the Status column in my order table dynamic. The default value for new records will always be "1" to designate a quote. At this point the field is not dynamic. Once the customer confirms the order, the user needs to execute a command to change the status to "3" to designate a Confirmed order. At this point the field needs to be dynamic based on the shipping records. There are two order details tables. One for sales items and one for rental items. Each of these details tables has their own shipping record. the CheckInOut Tables are for rental while the Ship tables are for sales. So, if some (but not all) of the items in either of these order details tables has a shipping record associated with it, then the status should be changed to "5". If everything has been shipping, the status is changed to "4". If everything has been shipping but some items have been returned, the status is "6" if everything has been shipping and all of the RentalDetail items have been returned then the status is "7" and if there is any other combination of a variety of ships and returns, the status is "8". Also, at any time, the user needs to be able to execute a command to change the value to "2". once the value is changed to "2" the field stops being dynamic again.
Below are my tables creation commands.
CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderRentalDetail
(
OrderRentalDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE CheckInOutHeader
(
CheckInOutID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE CheckInOutDetail
(
CheckInOutDetailID int identity primary key,
CheckInOutID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderRentalDetailID int,
Qty int
)
CREATE TABLE OrderSalesDetail
(
OrderSalesDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderSalesDetailID int,
Qty int
)
View 3 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
Jul 17, 2001
Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
View 1 Replies
View Related
Jun 26, 2001
How can you modify the crdate in sysobjects?
View 1 Replies
View Related
Mar 20, 2008
I am not aboe to view the userdefined tables in sql 2005. not sure whether my user dont have permission to view. how to view my user permission or not ..
another one , is there any table to find system table instead sysobjects,information_schema in 2000.
View 2 Replies
View Related
Oct 19, 2001
Any idea what are different types in sysobjects table? How can I seperate views only?
select distinct type from sysobjects
type
----
P
U
S
V
K
D
R
View 2 Replies
View Related
Jul 8, 2000
I would very much like to create a trigger on the sysobjects table in my database. When I attempt to do so, I get a permission error. Is there some way I can change the permission setting to allow me to do this?
Thanks.
View 1 Replies
View Related
Oct 3, 2006
HI there , i have recently upgraded my publishers to 2005.
Transactional replciation seems to be running fine.
However in SS2000 i always used to query sysobjects on replinfo to see if a table was replicaed and how it was replicated, 1 = snapshot , 3 = transactional etc.
However after upgrading replinfo on sysobjects is 0 for all my articles, whats up with this ?
Must i use sp_helparticle only now? Also how does the system know if an object is replicated now , i used to think it used the replinfo column on sysobjects, how is this different in 2005?
Thanx
View 3 Replies
View Related
Aug 16, 2000
Hi there,
Once in a while, I find down there are some missing objects in production database. Because we share the sa password with couple of developers, therefore, it's hard find down who did it. So, I try to create a trigger in sysobjects table to prevent this problem, however, I keep getting the error message "error 229: create trigger permission denied on object 'sysobjects'"
although I log in using sa. Can someone give me some suggestions how to prevent this from happening beside using trace profiler and also why do I get the denied message when create trigger on sysobject even with sa login.
Thanks in advance
View 1 Replies
View Related
May 23, 2006
hi,
can any one tell what is the difference between data in xtype and type columns in sysobjects?
View 1 Replies
View Related
Nov 13, 2007
Hello everyone.
I'm facing to a serious problem.
I wanted to migrate SQL 2000 -> SQL 2005.
First of all, I tried to attach my *.mdf file to SQL 2000. but It was not only failed but also SQL 2005 failed to recover my *mdf file.
Now, I think my *.MDF file has system tables for SQL 2005 because when trying to attach *.mdf to SQL 2000 engine send the following message :
--------------------------------------------------------------------------
Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'rudol.net'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.
--------------------------------------------------------------------------
Also, I tried again to attach the *.mdf to SQL 2005 but SQL 2005 doesn't really works.
Stupidly, I didn't back up the *.mdf file.
How do I attach it to SQL 2005 or recovery "sysobjects" table for SQL 2000?
Please answer.
P.S.
Forgive my terrible English. English is not my mother tongue.
View 2 Replies
View Related
Jan 21, 2008
HI friends,
i have deleted the 'sysobjects' table by accident. then follows the problem.
1. default value constraint not working
2. auto increment constraint not working
is that because of this sysobjects problem.
help me out to fix this problem...
hav fun, njoy
sundaram
View 16 Replies
View Related
Jul 20, 2005
Hi,I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,N'IsUserTable') = 1" to get all user-defined tables(about 17000tables)and found out there are tables missing in sysobjects.How can I refresh/rebuild sysobjects table?is there a better way to get a list of user-defined tables?thanks,
View 2 Replies
View Related
Jun 29, 2006
I have a question about the xtype field in the sysobjects view. What do these characters mean? I think I've found lists, but are they complete?
What is the difference between FN and FS?
View 4 Replies
View Related
Jun 13, 2000
I would like to know how can i check from SQL statement that
whether LinkedSERVER exists or not like we search a table from "sysobjects"
Is there a way where i can search my LinkedServer exists ?
if exists (select * from sysobjects where name = ['mylinkedserver'])
View 3 Replies
View Related
Dec 26, 2007
Hey all,
Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.
Thanks for any advice.
View 6 Replies
View Related
Jan 18, 2008
Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.
I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:
quote:select * from MyTable
Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects.
dbcc checkdb ('MyDatabase')
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc checktable ('MyTable') with ALL_ERRORMSGS
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
dbcc checktable ('sysobjects') with ALL_ERRORMSGS
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I've fully verified the correct existence of the table and how it links to the object ID by running statements like:
quote:select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)
Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.
Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.
All tables are owned by dbo.
Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.
Any thoughts?
View 7 Replies
View Related
Jul 23, 2005
Hi,I'm trying to determine with my program whether or not a given databasesupports a given feature set. To do this I'm querying for certain storedprocedures in the sysobjects table and if they are present, making theassumption the database will support the given feature. The problem is Ican't find a certain stored procedure in the sysobjects table, even though Iknow it exists and can see other similar procedures using:select * from dbo.sysobjects order by nameIts as if all of my other stored procedures are in the sysobjects tableexcept this one, the one I'm specifically querying for. Are there certainreasons why a proc won't appear in the sysobjects table? Is this somethingI need to fix?Thanks,Robin
View 4 Replies
View Related
Jul 23, 2005
Could anyone, please, explain to me why I have negative values inRowModCtr column in sysobjects table? I have tested that after I updatestatistics the RowModCtr column is reset to 0. But why do I havenegative values in the first place? Thx.
View 2 Replies
View Related
Mar 12, 2006
Hi All,
I've written a scalar function in C# for the MS SQL Server 2005 that has to have access to two databases in one instance of a server ("database1" and "database2"). I got information from database1, calculate the name of the database2 and got information from user's tables in the database2. But when I'm trying to get informatin from "sys.tables", "sys.columns", "sysobjects" I always get an error "System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly."
All databases are inside one server, I can run this quieries in SQL Server management Studio - could anybody help me what should I set/change to get metainformation from another database in the same server (in fact I need a list of fields of a curtain table).
Thanks,
Alex Gerasimov
P.S. Atrribute [SqlFunction(DataAccess = DataAccessKind.Read)] is in the applicaiton.
View 1 Replies
View Related