ALTER PROCEDURE NEW
(
@tablename sysname = null,
@nvalue varchar(10) = null,
@ovalue varchar(10) = null
)
As
Declare @TABLE_NAME sysname
Declare @CONSTRAINT_NAME sysname
Declare @employeenosysname
declare @sql sysname
Declare c1 Cursor for
select TC.table_name, TC.constraint_Name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where T.TABLE_TYPE ='BASE TABLE'
open c1
Fetch next from c1 into @TABLE_NAME, @CONSTRAINT_NAME
while (@@Fetch_Status=0)
begin
--PRINT 'MED_FKEYS---' + @TABLE_NAME + @CONSTRAINT_NAME
if (@CONSTRAINT_NAME is null)
begin
/* If neither primary key nor foreign key table names given */
raiserror ('No Constraints' ,-1,-1)
end
else
select @sql='select' +@employeeno+' = EMPLOYEENO from'+ @TABLE_NAME
EXEC (@SQL)
if (@employeeno = NULL)
begin
/* If neither primary key nor foreign key table names given */
raiserror ('No EmployeeNo : ' ,-1,-1)
end
else
/*Disable the triggers and constraitns*/
SELECT @SQL='ALTER TABLE '+ @TABLE_NAME + ' DISABLE TRIGGER ALL'
EXEC (@SQL)
select @sql='ALTER TABLE '+ @TABLE_NAME + 'NOCHECK CONSTRAINT'+ @CONSTRAINT_NAME
EXEC (@SQL)
--select @sql='UPDATE '+ @TABLE_NAME+' SET EMPLOYEENO = '''+@nvalue+ ''' WHERE EMPLOYEENO = '''+ @ovalue +''''
--EXEC (@SQL)
select @sql='ALTER TABLE '+ @TABLE_NAME + 'NOCHECK CONSTRAINT'+ @CONSTRAINT_NAME
EXEC (@SQL)
SELECT @SQL='ALTER TABLE '+ @TABLE_NAME + ' ENABLE TRIGGER ALL'
EXEC (@SQL)
/*Enable the triggers and the constraints */
Fetch next from c1 into @TABLE_NAME, @CONSTRAINT_NAME
end
close c1
deallocate c1
return
GO
This procedure above gives me error
Line 1: Incorrect syntax near CONSTRAINT name.
Incorrect syntax near the keyword 'KEY'
Warning:
The table name has been created but its maximum row size (10760) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Is there any procedure or option to disable and enable constraints on database.the problem is we are having lot master and child tables. when i am trying to move the entire data from original db to another same db, there i am getting constraints problem. child tables are not accepting data with out loading the master tables.
is there any to disable and enable constraints? please give me reply asap
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.
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.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Is there a way to disable logging ? I'm about to split a 1000000 rows table into three other tables , and would prefere not to log this action ... Does anyone know how to do it in a SQL 7.0 statement ?
can I disable a trigger in Sqlserver 2000??? When i run a store procedure who works with one table i want that the trigger doesn´t work it. After that the trigger would be enabled again.
I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.
Is there any way to disable the primary key constraint?.I want to move the records from one table to another.say column 'x' in the source table doesnt contain a primay key.But my target table column 'x' contains primay key. How to ovecome this problem? Thanks in advance
In meen. primary keys, NOT NULL, IDENTETIES...et.cI have to do a maunally, one time, building of a database. Sometables has tostay an some are to be exchanged. The foreignkey inforcemnt ill do for myself so everything is correct. I just need to be allowed to de thede taskfor a while. Is it impossible?RegardsAnders
I am trying to disable certain users, but it is giving me this error. I am able to disable some accounts, but not all of them. It seems to happen with user that have been recently created.
TITLE: Microsoft SQL Server Management Studio ------------------------------
Disable Login. failed for Login 'domainuser'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Disable+Login.+Login&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain information about Windows NT group/user 'domainuser', error code 0x5. (Microsoft SQL Server, Error: 15404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15404&LinkId=20476
The links lead to a page that is no longer available.
I read somewhere that it had to do with the SQL Server service account and that if you change it to a Domain Administrator it would and it does, but I can not believe the solution. I would say that this even started happening after we applied SQL 2005 Service Pack 2.
Has anyone else seen this problem. Do I really need to make it a Domain Administrator?
Sorry to reopen a post, but I'm having the same problem. Worse, I can't change the trigger code right now.
My concerns with disabling triggers are:
1) DISABLE TRIGGER affects all sessions, not just the session doing the mass load 2) DISABLE TRIGGER is permanant until re-enabled, so if the mass load process fails (and if our TRY/CATCH blocks aren't perfect), then the triggers would remain disabled for normal OLTP use. Any ideas on ways around this? I can identify all the work the triggers would have done and do it on my own. The problem is getting them not to do it!
I would like to be able to disable grouping depending on the value of parameter. At the moment I have the following expression for grouping: =IIf(Parameters!Group2.Value.equals(""), false, Fields(Parameters!Group2.Value).Value)
What I am trying to do is if the value of the Group2 parameter = "" turn off grouping. Group2 is assigned a value from the following custom code:
Function Group2Value() As String Dim Group As String Group = "" Select Case Report.Parameters!ReportType.Value Case "1"
Group = "" Case "2"
Group = "CategoryID" Case "3"
Group = "StockFamilyID" Case "4"
Group = "GroupID" Case "5"
Group = "" Case "6"
Group = "SaleableGLCodeID" End Select Return Group End Function
However this is not working.
Can grouping be disabled and if so what am I doing wrong?
select * from table where cond1 and cond2 and col.port in <string> ... goes
end
i passing this string from other function, i may be not be needing this filter(underlined) for some functionality, but i will be using the same function for both the functionalities
i need a string that should tell the sql server to allow all the possible values for that column, so by disabling the filter which i am using...
Hello, I am making a web application and I will show reports with the reportviewer, however the user can manage some parameters of the applicacion, the administrator can decide if he wants to allow the user to export or not export to PDF or XLS.
Anyway, I want to enable or disable those options in the reportview dinamically!
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
Yesterday I set up a Transaction replication with 1 publication Server A (publisher)(push)(SQL 7) Server B (distributor and subscriber)(SQL 7)
The replication was succesful and the suscriber can get correct data.
This morning I found the Red sigh in replication mornitor/miscellaneous agents.The error message is: "The job failed. Unable to determine if the owner (domain nameuser ID) of job Agent history clean up: distribution has server access (reason: Could not obtain information about Windows NT group/user 'domain name'. [SQLSTATE 42000] (Error 8198))."
I tried to use the 'disable publishing and distribution wizard' to disable this replication, but it did not finish and the screen jusr freeze. I used the task manager to end the task.
then I check the replication monitor, the publication was gone but the RED sigh still there.
I tried to use sp_dropdistpublisher to drop a Publisher but it said "the server A is not a distributor"
I tried to use sp_dropdistributiondb to delete the distribution database. but it said "Could not drop the distribution database 'distribution'. This distributor database is associated with a Publisher."
I tried to use sp_dropdistributor to remove the Distributor . but it said "Could not drop the Distributor 'Server B'. This Distributor has associated distribution databases."
I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?
What is the best way to disable using the Windows policy requirements when creating a new login?
example: I'm trying to create a login and the password doesn't meet the windows policy requirements. The easy thing to do would be to create a strong password. This isn't an option at the moment. I need to disable this so I can create new users that dont have strong passwords.
There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.