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

More fun with DTS Packages

Hi guys,

Well, another day, another adventure in the land of editing DTS packages. Anyone out there run into a package that will run for one sysadmin but not for the other? I have a package that can't initialize it's connections (sql server doesn't exist or login denied) for me but runs fine for my boss from his workstation. Note, permissions have been thoroughly investigated both on the database and the server plus I can run other packages on that server.

Bad or missing files somewhere perhaps? I logged onto the server (where the packages and the database live) and ran some packages. While all my other packages run OK from the server, the package that is giving me fits from my box gets an error message about

'cannot find msdb..sp_log_dtspackage_begin'.

Has this file given anyone else issues? My other packages run from the server without this error.

Any other advice besides 'don't use those evil DTS packages'?

Thanks for your help!DTS is not evil silly girl Microsoft is evil!

who does your boss authenticate to when he/she/it logs in?
who do you athenticate to when you log in?

sp_log_dtspackage_begin should be owned by dbo, is that the case on your server?

What happens if you recompile the sp? Make sure you are logged in as SA!|||Hah! Just another example of absolute power corrupting absolutely?

Well, we all get authenticated (on the network, I'm assuming) through the same box--there are only 15 of us so we have one domain controller. Supposedly we are all processed similarly.

As for the sp_, I cannot find this puppy. I'm assuming that if it exists, lets not rule out a bogus error message, it's not something MS is displaying. Searching for it doesn't turn up anything. It certainly doesn't seem to be anything anyone here wrote.

Ahhhh, to have a local DBA. Currently I'm recreating the whole @.#^$#!$ thing (it's rather robust, to say the least--30 tasks, most with individual field scripting in data transformation objects). I'm on object 4 and so far it runs fine.

Have you ever had one of these become corrupted?

Should I get a bigger stick?|||Bigger stick? Only if it's long enough to reach the the prior developer!

When I was asking about authentication I meant on the SQL box. Are you athenticated to dbo where as your boss isauthenticated to sa?

this sp was shipped from Microsoft. it should be:

CREATE PROCEDURE sp_log_dtspackage_begin
@.name sysname,
@.description NVARCHAR(1000),
@.id UNIQUEIDENTIFIER,
@.versionid UNIQUEIDENTIFIER,
@.lineagefull UNIQUEIDENTIFIER,
@.lineageshort INT,
@.starttime DATETIME,
@.computer sysname,
@.operator sysname
AS
SET NOCOUNT ON

INSERT sysdtspackagelog (
name,
description,
id,
versionid,
lineagefull,
lineageshort,
starttime,
computer,
operator
) VALUES (
@.name,
@.description,
@.id,
@.versionid,
@.lineagefull,
@.lineageshort,
@.starttime,
@.computer,
@.operator
)
RETURN 0 -- SUCCESS

GO

if you can not find it in the list of stored procedures in the msdb database I would say you have a bigger problems. You should be able to copy this from another server.

SQL Server has been a stable product since 7.0. Stable in the sence that things don't mysteriously stop working. Data can and does get corrupt but this sounds like a stored procedure was deleted! Maybe time to check security and change the sa password?

as for corruption, I like to think of Microsoft as a drug dealer, they give you a free taste of their drugs till you get hooked and then WHOMP they have you!|||Thanks for the sp. Adding it to the database's sp's didn't make a difference. However, it is either not displaying or simply not there on any of the databases on this server. Hard to tell what's going on. The packages that run, run with or without. The one that doesn't, similarly, won't run either way.

Sorry if I'm being obtuse, after all I'm just a stray VB/VBA programmer who took a job without realizing that there was no DBA for the back end here. (A situation you do not find when writing to Oracle. Probably MS is making things appear to be way too easy. I bet it's a marketing tactic, 'Use our software and you can fire those expensive DBA's. Any schmoe can ride herd on SQL Server--even applications programmers.') However, under the Server's Security, Server Login Properties I am a system administrator. I'm assuming it doesn't really get any better than that.

Note, the box I'm currently using is considered to be seriously corrupted--at least by the previous user--and when I installed the sp_ my icons for Enterprise Manager blanked out on the desktop and icon bars. Is it a sign? Should I start looking for Holy Water and a disk with SQL Server on it?|||The sp must be add to the msdb database, if it isn't already there. Please verify that the sp exists in the msdb db.

sa = god in SQL Server speak.

<soapbox>I hate companies that think they don't need a dba and that a developer should be able to handle it. IMNSHO this is one of Microsoft's weaknesses. You will rarely see Oracle installed by someone that doesn't know what's going on. This is the #1 reason why Oracle is perceived to out perform SQL Server.</soapbox>

Before you lose your sanity I would verify you are working on a stable box or not. I am wondering if your DTS problems started suddenly or were never working right to begin with? If they just started to fail around the time your predecessor left you may be dealing with another issue.|||Oh dear, I am being a bonehead. Yes, it is there in the msdb.db. Forgot about that whole thing. For this level of hand-holding I may have to fly to Texas and deliver that ride.

About the box, it was my boss's until he managed to get an upgrade so I don't think he's been sabotaging (sp?) it, however, there have been many non-kosher installs. I'll be getting a recycled box whenever someone has time to rebuild a spare.

The only remnant of the previous developer here is the big crack in my monitor case--I hear the keyboard was destroyed. Apparently this occured immediately before his giving 2 minutes notice... Guess he was short on sense of humor.

In any case, I'm slowly hacking my way through rebuilding this thing (still on object 4 though). It's a good learning experience and I obviously have much to learn since I just met up with DTS two weeks ago. The VBScript part feels very comfortable and the two packages I have built so far seem to do a nice job. It's a great idea to have this nifty visual interface. Now if they could only add some decent debugging and flesh out the reference material to actually cover the entire product it would be grand.

I'm sure I'll beat this package into submission eventually.

Thanks for your help!|||Don't worrie about the ride, I needed a good laugh today!

DTS is a VERY cool tool, mainly because it was written to work with almost any provider not just SQL Server. When I worked at Exxon we used it heavely for Oracle and DB2.

Who is the owner of the DTS package? After re-reading your original post I have to believe this is an ownership/permissions issue.|||Well, I hope I made you laugh with me, not at me--but I guess either way is OK.

DTS is a cool tool and I think I'll be pretty good at it in a few months. Looks like I'm going to get lots of practice until they drag me away to do customizations on the front end.

Currently I am shown as the owner. However, it didn't start out that way. We have a wierd (read dysfunctional) setup here in that this package is used on a server in NY. However, development occurs in Boulder. We do not have access to the same databases or servers. They emailed me a copy of the package (which was working) for modifications/bug fixes. Then the fun began because, among other things, our databases have different names. 30 objects all pointing at a connections to nowhere...

I do believe that I have changed all connection strings to point where I can get a connection to a local copy of the NY database. I've also tried adding a new connection and pointing them there. Initially I suspected that the connections changes were the problem, but it is not intuitively obvious why it will run on my boss's box if that is the case.

None of this, of course, bodes well for the idea of sending the package back to NY and having it run in their environment. Right now I have tentative plans (assuming I can manage to test my changes here) of walking someone through just adding the changes on their system so that we change the one that is running rather than replacing it. Far from an ideal scenario either way.|||What happens if your boss logs on your box and runs the package?
Do you run the package from the GUI or DTSRUN.EXE?

Given your current suspect box and diffrent connections string, I think walking a warm body through the changes would be a safe thing to do, for now.

BTW The only person I ever laugh at is myself.|||Hey, I only started programming after they gave us a GUI. I was being an opera singer when that whole DOS promt thing was in vogue. Then almost ten years ago I discovered how easy it is to make money by working on computers... Well, it is easier than trying to get people to pay you decent money (or give you health benefits) to sing.

To answer your question, I click on that there big green arrow thingy or go to Package>Execute.

I'll see if I can get my boss to log onto my box. Might be a while before I can corral him into that.|||Well, I guess they don't pay the boss the big $$$ for nothing.

It wouldn't run for him either, but having received packages for editing from NY before, he knew of one more place to look. He figured out that the path for the package log was set to 'local'. I do have a local copy of SQL Server but it isn't running. Apparently his local copy was running this morning when he tried it on his machine.

Whew! It always seems so easy when it's fixed.

Thanks for all your help--not to mention the entertainment value! File this one in the 'strange but true' category.|||Glad you found a use for your boss. Some times they are useful to have around.

School of hard knocks! Always the best teacher.

Not sure I would have come up with that one but in time all magic is revieled.sql

More DTS Help

Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Function Main()
'esemail is the validation function
if EsEmail(DTSSource("email")) then
DTSDestination("email") = DTSSource("email")
else
'Here i need HELP!!!!!
end if

Main = DTSTransformStat_OK
End Function

So what should I put in the else statement to transfer the email to another destination table?

Thanks,
A.Hello,

I think you work with the DataPumpTask, aren't you?
If I'm right, then you have to change the destination table in this task when going through the ELSE-path.

For details how to change the properties of a DataPumpTask refer to http://www.sqldts.com/default.aspx?213.

Hope this helps otherwise give me a hint!
Carsten

Originally posted by ArgenSQL
Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Function Main()
'esemail is the validation function
if EsEmail(DTSSource("email")) then
DTSDestination("email") = DTSSource("email")
else
'Here i need HELP!!!!!
end if

Main = DTSTransformStat_OK
End Function

So what should I put in the else statement to transfer the email to another destination table?

Thanks,
A.|||Tambien te puede convenir hacer dos tareas
Una para correos validos y la otra para los no.

Suerte|||Thanks CarstenK, I read that sample, it help a lot but I'm still having a problem I'm getting an error.
This is the code I'm using:

Function Main()
Dim oPkg, oDataPump

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask

if EsEmail(DTSSource("email")) then

oDataPump.DestinationObjectName = "dbo.ValidEmail"
DTSDestination("email") = DTSSource("email")

else

oDataPump.DestinationObjectName = "dbo.NonValidEmail"
DTSDestination("email") = DTSSource("email")

end if

Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTransformStat_OK
End Function

but I'm getting this error "Task 'DTSStep_DTSDataPumpTask_1' not found"

In the link you gave me they explain how to get the name, I did it but still the same error, and I'm sure the name is correct (copy & paste).

Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column. I think there is where the script should go...
well... if anyone can help me with this would be great!

fhnth: hacer dos tareas no seria practico por que tendria que recorrer la tabla dos veces.

Bye,
A.|||Originally posted by ArgenSQL
Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Could you import to a staging table and use T-SQL to move the records into the correct destination table?|||Originally posted by RogerWilco
Could you import to a staging table and use T-SQL to move the records into the correct destination table?

I could but I don't want to do that, what I want to do is a dts package that do all the work in one (or few) steps. I hope someone with experience on that could help me...
DTS have all the functionality in the world, I want to learn how to use it so I can do things in the best way and using all the technology I have access to.

Bye,
A.|||Hello !

After reading your reply a third time, I think I do know your problem. You said
Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column.

I think you should create an ActiveXTask, that contains your code and runs before the actual data import and adjusts the properties.

I hope that brings you a step further!

Greetings,
Carsten|||I am not sure whether this can be achieved in a single DTS step (one source (a .csv file) and 2 destinations (2 diff. tables).

You can make a 2 step DTS package>
1). 1st Step:
source : .csv file
dest : 1st table
ActiveX :
If email1 = email2
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if

2). 2nd Step:
source : .csv file
dest : 2nd table
ActiveX :
If email1 = email2
Main = DTSTransformStat_SkipRow
else
Main = DTSTransformStat_OK
end if|||Hi!

My thoghts are going this way:

Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.
By taking this way, you can avoid processing an email twice!

Carsten|||Originally posted by CarstenK
Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.

I can't make it work, I tryed everyway and still getting different errors, if I put the ActiveX code before the data pump, I don't get an error by detecting the task name, but I get an error because I ask for the email in the DTSsource and that doesn't happen until I get in the data part, so I can assign a different destination table but not commanded by the email type. I can't find a solution for this I search the web a lot but couldn't find a solution without passing two times trought the table, so I'm doing that I don't like it but I have to finish this anyway, if anyone finds the way to do this, please tell me.

Bye, Thanks all for your help.sql

Monthly CD billing import

Greetings;

I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.

I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.

Is this a possibility? Or am I going about this all wrong?

Thanks

nhas

Then you want to check the structure of table and then execute different packages according to the actual table, right? But from your description it seems the chages of data structure come from source data, not detination tables in database--so will it help if we check the table structure in database? I don't thinks so. You may need to have serval tables with different schema in your database to host the bill data in different structure, and use different DTS tasks to import data in different sturctures.|||

lori_Jay;

Yes the table scheme changes on the import, rarly - like last month. I just dont want to have to verify the MS Access visually before I do the import.

I was thinking of one DTS but import one table at a time, in steps. But having multiple DTS packages maybe easier to do.

Is there a way of telling DTS to do a trial run to make sure that the Access will import before firing off and not importing all fields because of a billing change?

Thanks

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanks
I use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>
|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanksI use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanksI use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
Thanks and Regards,
Prasanth
You can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth
sql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth