More Info:

We have setup a replication in SQL2000:

We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command

Alter table 'tbl' disable trigger 'abc' errored out. The error message said:

''Cannot alter the table 'tbl' because it is being published for replication."

I've digged more into this and found although it's not allowed to disable a triggers,

the SQLServer do allow delete the trigger and recreate them.

Is there any way to disable the trigger directly?

Thanks in advance,

Don

BTW:

I've used the following sql directly, however the trigger still fires.

UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@.table_name)

The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.

More Info: We are using transactional publication.

And trying the sql command in query analyzer quickly give us an error on published article table.

alter table tbl disable trigger abc

|||have you tried adding NOT FOR REPLICATION in your trigger?|||

We did try added not for replication in my trigger. and it does not work.

This issue is for loading data for the master table.

Besides, we did not publish triggers.

|||Disabling the trigger is not the issue. An ALTER TABLE command of ANY kind is not supported in 2000 against a replicated table. The only option in this case is to drop the trigger and recreate it, which is allowed in 2000 against a replicated table.|||

We kown this and we've found a better way to create the triggers. Nobody like this approach through.

If dropping trigger is allowed, there's no reason to not allow disable trigger.

It looks to me it's kind of lack of insight of microsoft. they have sp_repladdcolumn...

they should have a sp_replenabletrigger...

If you look at me post, I tried to modify sysobject tables trigger flag to change it directly.

However, it does not work so my guesses microsoft have more tables involved in this.

I was expecting somebody from microsoft could give me a hint or give me a sp_replenabletrigger.

|||

sp_repladdcolumn and sp_repldropcolumn were added as very specific patches to very specific problems. The issue was quite simply not having enough time to redesign a major portion of the replication engine to allow an ALTER TABLE statement. There are no hints, no work arounds, and no bypasses. There isn't any code like you're looking for either. The reason for that is quite simple. For the last 6 or so years, all development efforts were focused on SQL Server 2005, so if SQL Server 2005 already had a feature that would address and eliminate this entire issue, why waste the time and resources to graft this into SQL Server 2000?

I saw the message in the post. You are more than welcome to hack the system tables if you choose to. I'm certainly not going to hand anyone code to do so. Hacking the system tables is completely unsupported. If you blow up the system, you will not get any support from Microsoft in fixing it.

You have exactly two options:

1. Stay with SQL Server 2000 and write the code as a drop/create trigger

2. Upgrade to SQL Server 2005 and use an ALTER TABLE to disable the trigger

|||

Thanks, Michael.

That's a clear message.

We do encounter other problems with recreating triggers. if we using cmdshell to create the trigger though.

For example, sp-a is the one to create the trigger.

if sp-b calls sp-a,

sp-b do this:

Begin transaction

exec sp-a

Commit Transaction

calling sp-b will stuck inthe command shell out.That's another unpleasant finding yesterday.

Every developer want to upgrade to sql2005. Unfortunately upgrade to sql2005 require massive re-testing of all existing applciations,

This could be a valid reason but it's not enough for the move to sql2005.

I have to considering other solution other than replication now since we are also tight on schedule.

Thanks again for the response.

Don

sql

Monitoring / Logging Suggestion

Hi Everyone,

I'm looking for some suggestions on how I can go about the following task. I have an application which connects to our SQL2000 servers. Ocasionally some of the db connections take a long time to run. What I'd like to do is enable some sort of logging which would provide me with the following pieces of information... where the request originated from, length of time servicing the request, some sort of info on the nature of the request (search, insert, update, sproc, etc...), time of the request.

I'd then like to cross reference these logs with my application logs to isolate which requests are taking to long to service. Hopefully this will help us as we attempt to eliminate the issue.

Thanks for any suggetions you might have!!!! much appreciated!You can use the Profiler which is a standard tool that comes with SQL Server. Several templates are already defined to create a trace (such as for performance measuring) or you can create your own. See Books Online for more info about the parameters and settings.|||Thanks for the suggestion Johan. Taking a look now!|||I attached an MSAccess database which may or may not be helpful for you. It monitors SQL Server sessions. It's written in MSAccess XP.

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.
Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>
|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>>I want an SQL script that will monitor free space on certain drives of
>> my SQL2000 server.
>> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
>> a
>> SELECT freespace FROM xxxx WHERE (mydrive='C')
>> method that I can use?
>>
>> TIA
>> Dave.
>

MOM 2005 Reporting

I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
server also hosts the sql database (sql2000 w/sp3). While running the
install for the reporting services I get this error.
Failed to create data source for data warehouse, check to make sure you can
access the SQL services reporting server. (This is also hosted locally on
the mom server). Error code 2147467259.
Any one have any clues? I am NOT installing the report services to the same
folder as the mom2005 folder per microsoft. Any suggestions or guidance is
greatly appreciated.
Vid
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Check your system requirements! 2003 SP1 is not mentioned, only 2003 RTM.
I had to:
1. uninstall SP1.
2. uninstall MOM web console
3. uninstall SRS
4. uninstall Application Services (IIS, ASP, etc.)
5. reinstall Application Services
6. reinstall MOM web console
7. reinstall SRS
8. reinstall MOM reporting services
Early indications are that this seems to have corrected the problem. Your
mileage may vary.
"dave@.xrxdc.com" wrote:
> I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
> server also hosts the sql database (sql2000 w/sp3). While running the
> install for the reporting services I get this error.
> Failed to create data source for data warehouse, check to make sure you can
> access the SQL services reporting server. (This is also hosted locally on
> the mom server). Error code 2147467259.
> Any one have any clues? I am NOT installing the report services to the same
> folder as the mom2005 folder per microsoft. Any suggestions or guidance is
> greatly appreciated.
> Vid
> --== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
>|||Were you able to successfully install the MOM database itself? MOM is rather
picky when it comes to the order in which things are installed...
(1) Install MOM database after installing SQL 2000 w/SP3a
(2) Install MOM application
(3) Install SQL Reporting
As with previous reply - your mileage will vary.
"JerryW" wrote:
> Check your system requirements! 2003 SP1 is not mentioned, only 2003 RTM.
> I had to:
> 1. uninstall SP1.
> 2. uninstall MOM web console
> 3. uninstall SRS
> 4. uninstall Application Services (IIS, ASP, etc.)
> 5. reinstall Application Services
> 6. reinstall MOM web console
> 7. reinstall SRS
> 8. reinstall MOM reporting services
> Early indications are that this seems to have corrected the problem. Your
> mileage may vary.
>
> --
> "dave@.xrxdc.com" wrote:
> > I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
> > server also hosts the sql database (sql2000 w/sp3). While running the
> > install for the reporting services I get this error.
> > Failed to create data source for data warehouse, check to make sure you can
> > access the SQL services reporting server. (This is also hosted locally on
> > the mom server). Error code 2147467259.
> >
> > Any one have any clues? I am NOT installing the report services to the same
> > folder as the mom2005 folder per microsoft. Any suggestions or guidance is
> > greatly appreciated.
> >
> > Vid
> >
> > --== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
> > http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
> > --= East and West-Coast Server Farms - Total Privacy via Encryption =--
> >