SQL 2012 :: Check Constraints Have A Definition That Is Longer Than 4000 Characters
Oct 14, 2015
I'm putting a process together to run a DBCC CHECKCONSTRAINTS process against copies of client databases.The author application doesn't set the constraints as trusted, and therefore we need to check the integrity of the data.
The problem is that some of the Check constraints have a definition that is longer than 4,000 characters.When this is the case, DBCC CHECK CONSTRAINTS fails.One option is that I write a cursor to select the constraints that have a definition less than 4,000 characters and then call the DBCC command for those particular constraints. However, I'd prefer a more elegant approach - ideally a way to run DBCC CHECKCONSTRAINTS against all constraints regardless of the length of the definition
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit some size limit on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255 String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) String or binary data would be truncated. The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
Is it not possible for expressions to work with variables that contain more than 4000 characters? I've heard of a limitation of expressions and 4000 characters, but I thought this meant that the expression itself cannot be more than 4000 characters -- not that the expression cannot work with values of more than 4000 characters.
For example, I have a two variables:
Test1 (type = String): Value = [Some really long string whose length is > 4000 characters]
A trigger existed on the job steps table which captured any changes before and after. The insert table was limited changes to 4000 characters.
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit a size limit of ~4000 characters on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255 String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) String or binary data would be truncated. The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
Hi, I have a clr stored procedure that takes in 2 parameters, input xml and a query name. The stored procedure transforms the xml with a the xslt for the given query name (stored in a database). I am currently using and output parameter that is of type NVarChar(4000) to retrieve the xml in .net. This all works fine unless the xml that is being transformed is greater than 4000 characters which will happen. Are there any ways of returning a string/xml greater than 4000 characters (in the region of 60-70k characters).
I want the reason for the above statement where I user nvarchar(4000) to insert the japanese text it give the same error , why we cannot have maximum size ? if we can have maximum size than 8060 what is the setting
When using SQL CE 3.5 and I try to insert data to DataSet with NTEXT type column that has more than 4000 characters I get this error
"InvalidOperationException was unhandled @p4 : String truncation: max=4000, len=8414
I had not problem with SQL Compact 3.1 I didn't even changed DataSet I just upgraded my database 3.1 -> 3.5 and I get this error. This is serious bug....
There are other people who have the same problem http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812683&SiteID=17
I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work. Â
cmd = New SqlCeCommand(strSQL, cn) Try cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try If cn.State = ConnectionState.Closed Then cn.Open() End If
Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") cmd.Parameters.AddWithValue("@docData", info) cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try End If
Changing the cmd.Parameters as follows works:
Dim paramdocData As SqlCeParameter Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText) paramdocData.Value = info cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try
Thanks to the following Microsoft ReadMe for the above suggestion. See: http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
I have a Live Database as well as the Test database with the same Definition
now i need to write a SQL to check identity of these two database tables,
i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ? In the same way how do i check for the Stored peocedures ?
Hi, I am developing a database for my company in SQL server 2000 and I have some few problems. Firstly.
I have a customers table and orders table in my DB:
Customers Orders --------- ------ CustID (primary key) ----------------< CustID . ^ ProductID . | Quantity . | . . | . etc. | etc. | relationship (one to many)
What I want to do is: 1) to be able to delete a Customer and automaticaly SQL server delete all the orders that this customer done from the Orders table. 2) If for some reason the CustomerID changes, SQL should be able to automaticaly update the necessary fields with the new values in the Orders table. Finally, 3) I want to be able to insert a new customer that has an order and update both the Customers table and Orders table automaticaly. e.g
CustID Name Address ProductID Quantity etc. ------ ---- ------- --------- -------- ---- 10-003 John London 33-25 2 ...
Such a kind of insert should add automaticaly the following entries in the two tables:
Customers Orders --------- ------ CustID (10-003) CustID (10-003) Name (John) ProductID (33-25) Address(London) Quantity (2) . . . . etc. etc.
A friend of mine told me that this can be done using Foreign Check constraints in SQL server. But I do not know what to do.
I am Trying to add a check constraint that if the paymenttotal is 0 the column is allowed to have null and if its greater then 0 it is not allow to have null. Here is what I have so far but i get some syntax errors, See if you can see what im doing wrong and how to get this to be valid. Thanks heres what I got so far
Code:
ALTER TABLE Invoices WITH CHECK PaymentDate SMALLDATETIME NULL, CHECK (PaymentTotal = 0) PaymentDate SMALLDATETIME NOT NULL, CHECK (PaymentTotal > 0)
I also had this before i changed it to that and I got syntax erros as well but i dunno which is closer.
I would like to establish a constraint that extablishes one to one relation between ID1 and ID2. Meaning in the above example ID2 =122 should not be assingned to any ID1 other than 1. (For example, I should not be able to insert another row like ID1EffDateExpDateID2ID2_Location 41/1/200712/31/9999122ABC)
Beacause this table is maintained in a manual way, sometimes the ID2 which has already been assigned an ID1 is being assigned to another ID1.
What kind of constraint or rule will avoid this scenerio.
I have an Account table as well as a Bank table. The bank table has a total assets field. There is a foreign key in Account referencing Bank.
What I need is a check constraint that verifies that the total sum of the account balances for a particular bank is less than that banks total assets.
I've been thinking about this one for a while but it's just confusing me. How do I create the expression in the constraint for this? Currently I am using SQL Server Management Studio Express so I'm creating this constraint with the gui.
Hello, I want to write a unique constraint that applies to more than one column. What I mean is that the uniqueness should be that if column A is 5 and column B is 3 no other row where A and B has those values can exist.
Do I write this as a check constraint ? Or how do I do it ?
Also, is there anyone who knows some good reading on how to use Link Tables (many to many relations) in MS SQL Server ?
I have a question concerning setting up data integrity checks in SQL Server.
I have a table that lists "Groups" to which an Entity belongs. The Entity can belong to multiple Groups. Every entity has 1 and only 1 of its Groups designated as the "Primary Group". Based on this, my table contains multiple records for each Entity. Each record describes 1 Group of which the Entity is a member. In this record, there is a bit field indicating whether the Group is the "Primary Group".
In other DBMS's I have implemented a check constraint on the "Primary Group" column to enforce the business rule that "a Entity may have one and only one Primary Group". I am aware now, that in SQL Server 7, I must implement this rule as a trigger, or in the client or data services layers.
Does anyone know if SQL Server 2000 will allow me to write such a check constraint?
Hi, I´m facing teh following situation:This are just sample table names, but should do for discussingpurpouses.Create table Invoice(InvoiceID Integer Not Null,CustomerType Integer Not Null,CustomerCode Integer Not Null,Amount DECIMAL(10,2) Not Null,.................)Create Table Type1Customer(CustomerCode Integer Not Null,...............................)Create Table Type2Customer(CustomerCode Integer Not Null,...............................)I need to add a way to restrict the CustomerType and CustomerCode,in the Invoice table to the correct values.This means that if customerType equals 1 the customerCode should bechecked against Type1Customer and if customerType equals 2 thecustomerCode should be checked against Type2Customer.I succesfully created a check constraint. That ensures that the validvalues exists when the rows in the Invoice table are inserted orupdated, but doesn´t prevent from deleting records from tablesType1Customer and Type2Customer that are referenced from the Invoicetable.Are triggers the only way to go?Thanks in advanceSebastián streiger
I am working with an evaluation copy of SQL Server 2000 for the firsttime; my DB experience lies with MS Access.I have a simple table in SQL Server (tblCompany) that has a fieldcalled "Ticker." When new company stock tickers (i.e., MSFT forMicrosoft) are entered into the field, I'd like them in allcaps--whether the user types msft, Msft, MsFt, etc. In Access, thiswas easy--simply set the Format to ">" in table design view.In SQL Server Design Table view, I've clicked on "Manage Constraints"and put the following code in that I found elsewhere:([Ticker] = upper([Ticker]))I then checked all three boxes below: "Check existing data oncreation," "Enforce constraint for replication," and "Enforceconstraint for INSERTs and UPDATEs." The first one, "Check existingdata..." is checked as I've already entered in some data in the fieldin lowercase to see if the check constraint would go back and changeit to Upper Case--this because I'm wanting to ultimately migrate atable from Access to SQL Server and ensure that all Tickers are inUpper Case.I'm able to do this and then save the table design with changes; butevery time, I then go and look at the table data to see if the checkconstraint was applied, and each time it is not; then, I go back to"Manage Constraints" and find that the "Check existing data..." box isunchecked. I've gone through this SEVERAL times.Hoping this is something simple. Apologize for my "newbieness." I'vegot a "For Dummies" book in front of me as well as numerous Internetwindows open, trying to figure this out. Have checked books online onthe MSFT site as well to no avail.Thanks in advance--RAD
I have tables that are replicated using transactional and merge replication. As a result I am unable to use automatic identity management as transactional replication doesn€™t seem to understand it.
Therefore I have implemented a version of the automatic mechanisms that seems to work in a hybrid environment. It is based on a central table that holds the maximum identity for each table that has been issued to date. Valid identity ranges are issued to each publisher and subscriber as needed in a similar way to the automatic mechanisms and tables are reseeded as needed.
I want to enforce the ranges in a similar way to the automatic mechanism using a check constraint similar to this:
alter table [dbo].[test1] with NOCHECK add CONSTRAINT repl_identity_range_48DF13ED_D503_4F5C_AED9_4E504D03E752 check NOT FOR REPLICATION (([id] > 10001 and [id] <= 20001) or ([id] > 50001 and [id] <= 70001))
This works OK on a client subscriber, but if the change is made on the publisher, then the alter statement itself is replicated out to all clients €“ which is not what is wanted. I have traced the automatic mechanisms using profiler and they issue an alter statement as above €“ following dropping of the constraint €“ but the check constraint isn€™t replicated. I can't see how this is achieved.
How do I stop the check constraint being replicated?
The article property schema option can be set to stop replicating check constraints, but this seems to have no effect. If the publication property replicate_ddl is set to 0 then I do see the behaviour that I want. However, I do need to be able to replicate most schema changes due to upgrades etc €“ so this doesn€™t look like a viable option €“ except possibly for the transactional publication.
When our DB was converted from 6.5 to 7, the some column check constraints changed to table constraints.
Is there a way to change them back, short of rebuilding the table? I can't find a syntax to add a column constraint without adding a column. Some of the affected tables contain millions of rows, so I'd rather not rebuild them.
When I create a test table with a column and a table check, I see that in sysconstraints "colid" and "status" are different, and in sysobjects "info" and "status" are different. I am leary of tweaking the database catalog though. Heck, this is SQL 7; I don't even know if these are real tables or mirages.
create table zzzfoo ( myname char(30) check (myname in ('foo', 'bar')), myfuzz char(30), check (myfuzz in ('cotton', 'wool', 'linen')) )
select sc.* from sysconstraints sc, sysobjects so where sc.id = (select id from sysobjects where name = 'zzzfoo') and sc.constid = so.id and so.type = 'C'
Let's say I create a multi-statement function like this:CREATE FUNCTION dbo.Test ()RETURNS @res TABLE (N int NOT NULL CHECK (N >= 0))ASBEGININSERT INTO @resSELECT 1RETURNENDThat works fine. Then I make a change in the function's body, replace theCREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9Cannot ALTER 'dbo.Test' because it is being referenced by object'CK__Test__N__5D2E32EB'.Indeed, if I look at the list of dependencies for the function in QA'sobject tree, I can see the check constraint referenced in the errormessage.ALTER FUNCTION works fine if I don't specify the CHECK constraint in thedefinition of the @res table.So it seems that the only way to modify such a function is to drop andrecreate. Is that a known behavior? Is there any particular reason for it?Thanks.--(remove a 9 to reply by email)
I have one table like below Test table. My requirement is to create constraints to confirm <g class="gr_ gr_331 gr-alert gr_gramm Grammar only-ins replaceWithoutSep" data-gr-id="331" id="331">uniqueness</g> of STID value 101 with LN.
likeÂ
ID - LN - STID 1 - 'ABC' - 101 Â ---- Valid Row 2 - 'ABC' - 202 --- Valid Row 3 - 'ABC' - 202 --Valid Row (as I want only unique when LN = 'ABC' with STID = 101) 4 - 'ABC' - 101 -- Invalid Row (As I want uniqueness base on LN and STID = 1011)
create table dbo.Test ( ID int identity, LN varchar(50), STID bigint )
Is this possible with constraints as I don't want to use <g class="gr_ gr_1041 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1041" id="1041">trigger</g>.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements Dim rowscopied As Integer = 0 ' first, create the insert command that we will call over and over: destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar) ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar) ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar) ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar) ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar) ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar) ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar) ' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
