Wrong Default Constraint Shown From Object Browser In Query Analyser
Oct 14, 2005
I have several default constraints defined on a table. When I use the
Object Browser and expand the constraints for this table and
right-click and then select "Script Object to New Window As Create", a
create constraint statement for a different default constraint is
displayed than the one I just right-clicked on. For example, I click
on constraint "DF_C" and it shows me "DF_B".
The last time I encountered this, the solution was to dump contents of
the table into another, drop, recreate it, and restore the contents.
That's not a good option this time.
Is there another way to fised this or at least navigate the catalog to
find out what is "off" about this?
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
I just found an odd bug and was wondering if anyone else has seen this.Any templpate file in the directory 'C:Program FilesMicrosoft SQLServer80ToolsTemplatesSQL Query Analyzer' that has the NTFScompression turned on (that is, colored blue in Windows Explorer) doesnot display in the object browser's template tab of query analyzer.Control-Shift-Insert works fine to insert a template, however.What's up with that?
I thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO
Most of the times I cant see my database tables from the query analyser in the object browser window... There are some times that appear normally...but in mosto of cases they just dont appear at all...I can only see the master database and msdb... I am connecting giving the proper password...
bear in mind that I can write and execute queries to my database although I cant see the tables
Why could this be happened??? Is a matter that i have discussed with my hosting provider or I am missing something here???
Hi,I've run into a curious problem with MS SQL Server 8.0. Using sp_help andSQL Query Analyzer's object browser to view the columns returned by a view,I find that sp_help is reporting stale information.In a recent schema change, for example, someone lengthened a varchar columnfrom 15 to 50 characters. If we use sp_help to find out about a view thatdepends upon this column, it still shows up as VARCHAR(15), whereas theobject browser correctly reports it as VARCHAR(50).Dropping and recreating the view fixes the problem, but we have quite a fewviews, and dropping and re-creating all of them any time a schema change ismade is something we want to avoid. I tried using DBCC CHECKDB in hopes thatit would 'refresh' SQL Server's information, but no luck.(if you're curious as to why I don't just use the object browser instead,read boring technical details below)Has anyone seen this before? Is there some other way (other thanre-creating every view) to tell SQL Server to "refresh" it's information?Thanks!-Scott----------------------Boring Technical Information:The reason this is an issue for us (i.e., I can't just use the objectbrowser instead) is that our object model classes are built using standardmetadata query methods in Java that seem to be returning the same staleinformation that sp_help is returning. These methods are a part of thestandard JDK, so we can't easily fiddle with them. Anyway, as a result, ourobject model (at least with respect to views) may not match our currentschema!
Hi,I see the following in Books Online: CONSTRAINT--Is an optional keywordindicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGNKEY, or CHECK constraint definition...But I have a table column defined as follows:[MONTH] [decimal] (2, 0) NOT NULL CONSTRAINT[DF__TBLNAME__MONTH__216361A7] DEFAULT (0)My question: Is "DEFAULT" a constraint, or is it called something else?Thanks,Eric
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
A few weeks ago a client asked me to add a column to a table so Icreated this script:ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Now they want to change it from a BIT to an INT, to store an enum.Fair enough. However, no matter how much I wrangle with a script, Ican't find a reliable way to alter the column. I've mixed and matchedthe following and nothing seems to work:EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'DROP DEFAULT DF_tblIndividual_fldRenewalStatusALTER TABLE tblIndividualDROP CONSTRAINT fldRenewalStatus_DefaultALTER TABLE tblIndividualDROP COLUMN fldRenewalStatusGOALTER TABLE tblIndividualADD fldRenewalStatus int NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Thoughts?ThanksEdward
I have a table set up called tbl_DailySummary_new. In that table I have a column called €˜RevType€™ char(2) NOT NULL Default €˜00€™. I'm trying to populate this table from another table called tbl_DailySummary. The RevType column in tbl_DailySummary allows null values (and has them). When I attempt to insert all records from tbl_dailysummary into tbl_DailySummary_new, I get the following error message:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'RevType', table 'TxnRptg.dbo.tbl_DailySummary_new'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Why dosen€™t the default constraint on the RevType column in the new table pick this up?
I'm a software developer trying to hack my way thru a SQL script to increase the size of a column. The column has a constraint on it that won't allow the alter unless its dropped.
Alter command: ALTER TABLE dbo.QueueBack ALTER Column QBQueue varchar(4) NOT NULL
Message response: Msg 5074, Level 16, State 1, Line 10 The object 'DF__QueueBack__QBQue__76818E95' is dependent on column 'QBQueue'. Msg 4922, Level 16, State 9, Line 10 ALTER TABLE ALTER COLUMN QBQueue failed because one or more objects access this column.
So, my script drops the default constraint, alters the column size and then adds the constraint back. The problem is that the constraint does NOT appear to be the same as it was prior. I can run the Alter command on the column and it will allow me to change the size of the column... where it prohibited me from doing such before. How do I restore the default constraint so it is exactly the way it was before I dropped it?
Thx,
Stretch
---------------------------------
Script for drop, alter, add... modified slightly for simplicity...
declare @Error int
BEGIN TRAN SELECT @Error = 0
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
-- this is retrieved at run time from the sysconstraints table; hard-coded here... set @defname = 'DF__QueueBack__QBCategory'
IF @defname <> '' BEGIN SET @cmd = 'ALTER TABLE dbo.QueueBack DROP CONSTRAINT '+ @defname EXEC(@cmd)
if @@ERROR = -1 BEGIN SET @Error = -1 END END
if @Error = 0 BEGIN -- modify the column ALTER TABLE dbo.QueueBack ALTER Column QBCategory varchar(7) NOT NULL
if @@ERROR = -1 BEGIN SET @Error = -1 END END
if @Error = 0 BEGIN -- Add the default constraint back SET @cmd = 'ALTER TABLE dbo.QueueBack ADD CONSTRAINT '+ @defname + ' DEFAULT ('''') FOR QBCategory' EXEC(@cmd)
if @@ERROR = -1 BEGIN SET @Error = -1 END END
if @Error = 0 BEGIN -- Commit if no error... COMMIT TRAN END ELSE BEGIN -- Rollback if error... ROLLBACK TRAN END
I'm getting the "This database does not have a valid dbo user or you do not have permissions to impersonate the dbo user,...." error message when trying to add a Database Diagram from Visual Studio Server Explorer.
I can add the diagram without a problem from SQL Server Management Studio Express CTP.
When I view the properties of the database in VS, the owner shows as XYZMyOldUserName. This is wrong. I just added this database to a new project.
When I view the properties of the database in SSMSE, the owner shows as XYZMyNewUserName. This is correct. It shows the name I am logged onto the PC with.
I'm logged onto a domain using Active Directory. I renamed my user logon name from MyOldUserName to MyNewUserName a couple weeks ago. Now, VS is still picking up that old name somewhere and, despite it showing properly in SSMSE, it does not show properly in VS.
Where is that old user name stored? Why does it essentially show the database as being owned by two different users? (well, at least, the same user with two different names.)
Okay, maybe I'm getting ahead of myself. Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money. I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.
I can't seem to get the syntax correct for ALTERing an existing column with a default constraint. I've been to Help and BOL. There are examples that show how to use the ALTER command to add a column with a default constraint but not how to do it on an existing column.
I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.
ALTER TABLE TableTest ADD CONSTRAINT PRIMARY KEY (C1)
Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".
If I add a name to the constraint, it works fine. I'm using SQL Server 2000
I have a default constratint on DateColumn getdate()-1
I have used enterprise manager to update it to yesterday's date everyday.
I would like to have a SQL which can check for the date in the system or even a trigger which checks when the date changes the constraint is updated itself. If this is not possible I would like to have a stored procedure which I will schedule to run as a job everyday once.
So if today 6/12/2006, the default value in the Datecolumn should be 6/11/2006.
This gives me a error, i tried but could not fix the bug.
Alter Table TABLE_NAME Alter Constraint DF_DATECOLUMN Default getdate()-1
I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table. I dropped the existing constraint and need to add the new default constraint of 0 for that column.
What are the tables are having the Default Constraint those table's Script the User can't generate but remaining tables He/She can generate,If i want DENY he/she to do not generate the script those are not having the DF Constraints what should i do.....
 I have a table named [New Item] and created a default constraint on a column, and i wanted to change the data type of the column using the query
alter table [new item] alter column pcs_qty decimal(15,2) not null
but the name of the default constraint is 'DF__New Item__Pcs_Qt__2D12A970' and i am not able to delete the constraint because it contains a space in between.Is there any work around for this.
I tried to delete the constraint by using the query
alter table [new item] drop constraint 'DF__New Item__Pcs_Qt__2D12A970'
but I am getting the exception,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DF__New Item__Pcs_Qt__2D12A970'.
Are there any vices to using default constraints on all columns in your table. For example an Int that defaults to 0 or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad?
i have a problem with a report. if i try it with vs2005 and the preview it works. if i call the report from my web-application i get no error-message, but i only get my headlines and no query-results.
i use reportingservices 2005, vs2005 and .net 1.1 my datasource is an olap-cube.
This what i did , since i need to maintain five sql servers ,i thought i will build a repository so on my desk top (running sql server ) i created a table name master_dbscript with the following fields
server_name varchar(20), dbname varchar(20) db_create_scripts text
using enterprise manager-- all tasks --generate sql scripts , (cut & paste to the insert statement in query analyser, the following is the insert statement
insert into master_dbscript values ('isd11t','test','ALTER TABLE [dbo].[child] DROP CONSTRAINT FK_child_parent GO /****** Object: Trigger dbo.test_patcase Script Date: 25/08/2000 12:10:09 ******/ if exists (select * from sysobjects where id = object_id(N'[dbo].[test_patcase]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)drop trigger [dbo].[test_patcase] GO ')
oops it created all the objects in the database where i tried to run the insert statement. god saved me , i tried this with the test database.
when i tried the same with bcp it worked fine and i was able to see the record in my table (one record) ,note you cannot use dts because it will support maximum 8000 chars only .
I am executing the following query in the query analyser.
"select * from alien119700 order by alienid"
In the message pane it shows
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 4 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(43 row(s) affected)
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 454 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
We use SQL 2K(service pack 1).Our query analyser will freeze often.So we loose all query production work.Does anyone know if Version 2 has a fix?Please help.
I noticed that query analyser is much more quicker than EManager when I access my database from my hosting provider... is there any way to see the properties of the table X for example as one can do with EM...
I would be grateful if you could provide me with any query sample conserning this issue...
