HI There
My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?
Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.
It only stops when i disable activation on the queue.
WHy is the queue not disabling ?
Thanx
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
I need to disable a trigger for a week w/o droping it. Is there a way in sql ?
Thanks and nice week-end.
Ivan
Please help if you can! I have setup a job that runs every day on an hourly basis. Every morning I find that it's been disabled. The funny thing is is that the schedule is disabled, but the job is not (you see 'Enabled' in the jobs list in EM, but when you view the schedules tab, it's disabled.) Also, it runs a several times before becoming disabled.
TIA,
Colleen
I have a server that was being used for logshipping and had
replication set up at some point as well. One of the databases got out
of sync in the logshipping process so I removed logshipping and was
going to reinitialize the database and set up the logshipping again.
The database is in read only mode and when I try to take it out of
read only I get the following message:
Error 5063: Database 'XXXXXXXX' is in warm standby. A warm stanby
database is read-only. ALTER DATABASE statment failed. sp_dboption
command failed.
I have tried to disable replication on the server but get the
following error message:
SQL Server Enterprise Manager could not disable 'SRVXXXX' as a
publisher.
Error 3906: Could not run BEGIN TRANSACTION in database 'XXXXXXX'
because the database is read only.
So my problem is that I can't take the database out of read-only mode
because of replication and I can't disable replication because the
database is in read-only mode.
Has anyone come across this before and how should I resolve it? I
tried dropping the database as well and that didn't work either.
Any ideas or help would be greatly appreciated.
Thanks in advance,
Brad
Hi,
I have a update trigger on a table. Can I disable this trigger for duration of a job which updates this table.
Thanks in advance ...j
Is there a way to disable a trigger when performing a transaction besides dropping and recreating the trigger? I am trying to perform an insert on a table, and this keeps firing a trigger that I want to disable.
Any help is appreciated.
I have accidentally registered an existing database as a distribution database, which made it a system database.
the data itself is safe and sound, but I want to undo the whole thing.
How can I do this?
I have an SQL job that runs a .vbs script. It has the following code:
Cmd2.ActiveConnection = Conn2
Cmd2.CommandText = "USE msdb EXEC sp_update_job @job_name = 'Email Download',@enabled = 0"
Cmd2.CommandType = 1
Cmd2.Execute()
It's not disabling the job. If I execute in Query Analyzer, it works. I'm thinking it might be a permissions issue. Any suggestions?
TIA
Hii have a table with primary key defined on col1 and col2. now i want tohave col3 also included in primary key. when i alter the table it givesme error for duplicate rows. there is an option for 'with nocheck' butit only works with check or foreign key constraint. is there any optionin sql server like in oracle 'no validate' which doesnt validate theexisting data and force the data validation from new records.thanxFarid*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedHi all,
1) Can anybody provide me the script for DISABLING all the jobs in the server and vice versa (i.e.) for enabling also.
2) Script for DISABLING LOGINS and script for ENABLING all Logins.
Its Sql Server 2005
Thanks... Any Help Greatly appreciated.
I have an SSIS task that transforms 70 million rows of data nightly.
The data is dumped and imported fresh every day.
Obviously I don't want my production Transaction Log getting cluttered up with 70 million rows of transaction logs daily.
Is there any way to avoid logging to the transaction log with an SSIS task?
Hi,
Is there any way to disable/enable constraints on the table?
Command "ALTER TABLE table_name {CHECK|NOCHECK} CONSTRAINT ALL" produces error.
I want to disable foreign key constraints en mass. Is there a way to do this?
I know that I can go into each table and navigate to the Relationship tab of Properties and uncheck the "Enforce relationship for INSERTs and UPDATEs" box, but I'd much prefer to automate this process with a query since there are 160+ tables and probably 200+ relationships to disable.
I figure that sysconstraints my be the ticket, but I will keep experimenting until I get the right solution. In the meantime, any insight to steer me in the right direction is appreciated.
Hi,
I'm using DMO (SQLOLE65.dll) to programmatically replicate selected publications. The Publication object supports a property called Enabled, which can be set to FALSE. I'm setting the enabled property to TRUE for those publications that need be replicated, and make the others FALSE. Still, all publications get replicated. [I give DoAlter to commit the changes i made.]
Any solution,Please mail me ASAP to venkateswaranb@synectics.soft.net
Thanks,
Venkat
sql 2000, northwind databse
code in sql analyzer:
Code:
ALTER TABLE categories NOCHECK CONSTRAINT ALL
TRUNCATE TABLE categories
ALTER TABLE categories CHECK CONSTRAINT ALL
but still i am getting the error:
Quote: Server: Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'categories' because it is being referenced by a FOREIGN KEY constraint.
what i need to do is write a query that just cleans all the tables of all data.
In the process of doing some routine monitoring/clean-up we'vediscovered that several (many?) users are apparently set to access ourSQL Server 2000 database instances via the Named Pipes protocol. Inreadings and recommendations we've decided that our WAN would be bestserved if we use the less "chatty" TCP/IP.As such we've also decided to try to enforce this decision to useTCP/IP exclusively using the domain login script used by all of ourend-users.Question: does anyone know what registry entries are created/used toindicate that TCP/IP is enabled and is the default protocol for SQLServer 2000? Our environment is: XP Pro SP2 and SQL Server 2000(typically SP3).TIAGlenn - newbie DBA
View 5 Replies View RelatedHi all,
Here is my situation. I need to disable a task at runtime. I have a script task that configures a boolean variable at runtime and sets its value to either true or false based on a condition. And also i have already set "disable" property of the component to get value from the boolean variable. The problem here is that the component gets the default value which we give during variable creation instead of getting the configured value.
Am i missing out anything. thanks in advance.
Regards,
Praveen
I'm currently using SQL Server 2000 and SSRS 2000 with the latest Service Packs.
I need to disable Excel Exports for a single report.
I've found a way to disable Exporting Formats but it disables for ALL REPORTS on the server.
This involves changing the rsreportserver.config file.
http://blogs.digineer.com/blogs/jasons/archive/2006/05/10/93.aspx
Another site mentions a way to disable Exporting Formats for a single Report.
http://mikemason.ca/2007/04/30/
Code Snippetusing System.Reflection;
using Microsoft.Reporting.WebForms;
using Microsoft.SqlServer.ReportingServices2005.Execution;
namespace MyProject
{
public class ServerReportDecorator
{
private readonly ServerReport serverReport;
public ServerReportDecorator(ReportViewer reportViewer)
{
this.serverReport = reportViewer.ServerReport;
}
public ServerReportDecorator(ServerReport serverReport)
{
this.serverReport = serverReport;
}
public void DisableUnwantedExportFormats()
{
foreach(RenderingExtension extension in serverReport.ListRenderingExtensions())
{
if(extension.Name == "XML" || extension.Name == "IMAGE"
|| extension.Name == "MHTML")
ReflectivelySetVisibilityFalse(extension);
}
}
private void ReflectivelySetVisibilityFalse(RenderingExtension extension)
{
FieldInfo info = extension.GetType().GetField("m_serverExtension",
BindingFlags.NonPublic
| BindingFlags.Instance);
if (info != null)
{
Extension rsExtension = info.GetValue(extension) as Extension;
if(rsExtension != null)
{
rsExtension.Visible = false;
}
}
}
}
}
I'm looking for further clarification of this process.
1. Where I can download the Microsoft.SqlServer.ReportingServices2000 dll?
2. Does anyone have a project example of the process? How/When to actually use the code quoted?
Any script out there for disabling all the constraints on all the tables in a database?
Since there are more than 100 tables I need to import the data into, is there any generic script that I can use to disable all the constraints and triggers etc on all the tables?
Thanks,
Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
Federico
Greetings,I want to bulk load data into user defined SQL Servertables. For this i want to disable all the constraints on all the userdefined tables.I got solution in one of the thread and did the following:declare @tablename varchar(30)declare c1 cursor for select name from sysobjects where type = 'U'open c1fetch next from c1 into @tablenamewhile ( @@fetch_status <> -1 )beginexec ( 'alter table ' + @tablename + ' check constraint all ')fetch next from c1 into @tablenameenddeallocate c1goNow when i try to truncate one of the tables (say titles) it gives methe following error:Cannot truncate table 'titles' because it is being referenced by aFOREIGN KEY constraint.Can anyone show me the right path? I am working on ASE 12.5TIA
View 4 Replies View RelatedHello,Is there anyway.. we can disable AUTOCOMMIT option in MSSQL serverwhile executing the SQL queries via SQL ANALYZER
View 2 Replies View Related
I have a report that can accept two parameters name or address. So if the user selects the name the dropdown for address should be disabled. if the user select an address the name dropdown should be disables?
is it possible to do something like this in SQL Report
Thanks
I recently updated a default instance of SQL Express to Service Pack 2. When I tried to attach my database I got an error "Login failed for user SA. The user is not associated with a trusted SQL server connection".
I found that my SA account for the instance was locked out. I unlocked it and everything worked again.
This happened to my on two different PC's using a default instance of SQL Express. Does anybody know why SA is being locked out? Thanks in advance.
I have a report where a particular row may or may not need to drill down, depending on whether or not there is more than one row grouped within it. I was wondering if there is some sort of property I can access that will enable or disable a drill down. I know where to go to do it in the IDE, but this is something that needs to happen at runtime.
Thanks!
HI all,
I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.
Here's where I'm at:
----------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[_InitializeDB]
AS
SET NOCOUNT ON
DECLARE @name varchar(255)
DECLARE @sql nvarchar(255)
DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'
OPEN tables
FETCH NEXT FROM tables INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = 'ALTER TABLE ['+ @name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
SET @sql = 'DISABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'TRUNCATE TABLE [' + @name + ']'
EXEC sp_executesql @sql
BEGIN TRY
SET @sql = 'INSERT INTO [' + @name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @sql
END TRY
BEGIN CATCH
PRINT @sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @sql = 'ENABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'ALTER TABLE ['+ @name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
FETCH NEXT FROM tables INTO @name
END
CLOSE tables
DEALLOCATE tables
----------------------------------------------------------------------------------
Running this Sproc produces (for the first ref'd table):
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.
I have a bit of an issue with an app I'm working on. The app integrates two different SQL Server applications - both of which employ recursive triggers to some extent. My integration basically serves to establish a link between different tables in the applications, and maintains consistency between the two applications (where common fields exist) by employing INSERT, UPDATE, and DELETE triggers.
In order to prevent infinitely recursive triggers (AppA.TableA's update trigger updates AppB.TableA. AppB.TableA's update trigger updates AppA.TableA...and so on, and so on) I need to be able to somehow selectively prevent these triggers only from executing recursively. For example, if the trigger in AppA is what calls the trigger in AppB, I do not want AppB's trigger to fire (and vice versa).
Further Information:
The apps may be on the same, or different, SQL servers.
I'm fully aware of the database-wide options to disable trigger recursion (ALTER DATABASE), but I can't disable recursion for the balance of the triggers in the databases.
The integration will run on either SQL 2000 or SQL 2005 - and perhaps one server on 2000, and one on 2005 (depending upon the deployment).
I'm perfectly amenable to handling it in the trigger code, if possible, but I'm at a bit of a loss as to how to properly and efficiently manage that.
I know that SQL Server will kill infinitely recursive triggers once it detects them, but that doesn't exactly solve my original problem.
Thanks very much for any input you can offer.
Posted - 02/01/2007 : 22:35:02
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch
only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs
could not update the distribution database subscription table, the subscription status could not be changed.
changed database context to 'master',(MSSQL SERVER error 22538)
Please help
publisher: 2005 Standard SP2 CTP
Hi,
I am creating an adhoc report using report builde in RS2005, now the created report has few columns and some if them have functonality of drillng diwn but is it possible to have a column where a particular column can not be drilled into >
prashant
Hi,I have over 200 stored procedures and they have dependencies on each other (not necessarily a cyclic dependency).Is there a way in SQL Server 2005 that I can disable a dependency check while I am executing a script that creates those stored procedures, and then re-enable it after?Thanks.
View 2 Replies View RelatedI want to disable a trigger on a table in a database from inside a stored procedure in another database.
Can I disable then enable? Do I have to drop then recreate the trigger?
How do I code it? I've tried several ways but I can't get it right...
I'm trying to improve the loading of some tables with large amounts of data that forms part of an ETL. I was going to try removing any indexes before the inserting to speed up the process, but I had some questions on whether or not I should include the clustered index (assuming one exists).
I was originally planning on including a step to disable all indexes on the destination table using the following:
ALTER INDEX ALL ON MyTable DISABLE
Once the load had finished I'd simply rebuild all the indexes.
should I simply disable the non-clustered indexes?