| Author |
Topic |
|
wildebube
Starting Member
4 Posts |
 Posted - 2006-03-27 : 11:17:26
|
| I’m trying to publish some information stored in a SQL Server 2000 database onto the web. The problem is that due to some licensing issues here, I can’t publish any data from that server. I can’t use replication either, but that’s another issue. In an attempt to resolve this, I’ve created a DTS package that runs nightly on another server.For clarity purposes, let’s say the data is on Server A and I need to publish it from Server B. I only need a very small subset of the data and I’m putting that into a single table. The package runs on Server B. It starts by dropping and recreating the table. The second step executes a query that selects data from three tables on Server A and places the result set into the single table on Server B.Now the problem: sometimes, most of the time in fact, the query seems to be running twice. The table on Server B has every single row duplicated. There is an ORDER BY clause in the select, so I can see that it’s running twice. I have all of the expected rows in the correct sequence followed by a second iteration in the same sequence.My analysis is that the query portion must be running twice. The fact that I never see more than two iterations would seem to indicate that the table is getting dropped and recreated. The fact that both iterations are identical further verifies that it’s being run twice. If it was appending to a table created the previous night, the data would be different. It’s also interesting to note that this doesn’t always happen. It runs nightly, and the duplication shows up about five times a week. No pattern to when those five times might be though.Any ideas what’s happening? Any suggestions on how to debug this? I’m an old-timer mainframe DBA and kind of new to this SQL Server stuff. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-27 : 11:45:18
|
| Check for a second schedule first.. Then check the workflow properties on each step in the DTS, you may have set the source or destination steps incorrectly.. Also check there are no extra connections in the DTS, if you made a connection and then deleted it, it sometimes stays int he package, do this by opening the connection properties and using the existing connection dropdown.. If there are extra conenctions, you can delete them by creating a connection object, setting it to the extra connection and then deleteing it.. |
 |
|
|
wildebube
Starting Member
4 Posts |
Posted - 2006-03-27 : 16:34:22
|
| Obviously, I don’t know what I’m doing here, but I’ve gone back and checked everything I can think of to check.The package contains three objects(?). First is an “Execute SQL Task” which is where the target table is being dropped and recreated. There’s an arrow indicating precedence going from there to an OLE DB provider for SQL Server called “Connection 1”. All it says is “create table results” and has completion listed under precedence.When I look at the object at the other end of the arrow, the Properties box shows two connections in the dropdown, Connection 1 and Connection 2. It’s showing “Connection 1”. It shows Server A in server and shows the source database in Database.There’s an arrow from that OLE DB provider to another one called “Connection 2”. The properties on that arrow show a description, show the connection as “Connection 1”, and have the query appearing in the box. The destination tab shows Connection 2 and lists the columns from the target table. Transformations shows how the columns from the source tables map to the columns in the target table.The icon for the second OLE DB provider for SQL Server is named “Connection 2”. Like the other one, it has both Connection 1 and Connection 2 in the dropdown, but it is showing Connection2. The server is Server B and the database is the target database.I know I’m pretty green in SQL Server, but that all looks right to me. Is that telling me that there ate two connections? |
|
|
|
wildebube
Starting Member
4 Posts |
Posted - 2006-03-27 : 16:36:45
|
| It also seems strange that I don’t always get duplicate data. I do most of the time, but not always.Another thing I left out: I never get duplicate data if I execute the package manually. It only happens when it runs on the schedule. I’ve been coming into work in the morning and looking at the data. If it’s duplicated, I just execute the package manually and that fixes it. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-28 : 04:37:52
|
| Ok, it sounds like there is a second schedule, the package sounds fine, so check through your jobs and check that that package isn't being run twice, you may have another schedule there, especially if you let SQL schedule your package.. This may be a pain to find as the name will be encrypted in the job step.. The best thing to do is change the DTSRUN /5u7t8hfh94y (or whatever it is) to DTSRUN /S <servername> /E /N <DTSPackageName> |
|
|
|
wildebube
Starting Member
4 Posts |
Posted - 2006-03-28 : 09:47:52
|
| Sure enough, I found that it was scheduled twice at exactly the same time. Don't know how that happened, but I must have done it somehow. In any case, it's fixed now. Thanks! |
|
|
|
webber0109
Starting Member
2 Posts |
Posted - 2007-06-07 : 19:44:01
|
| hi there, i'm getting this problem as well, the DTS package seems to run twice... this package is running via DTS run with hash codes and stuff - DTSRun /~Z0x7C6C6240DD4D309813F3C... i've checked the sql job but there's nothing there. is there another way i can trace this duplicate process to get rid of the failed process due to duplicate record entry? TIA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 01:25:08
|
| Set tarce in profiler. |
|
|
|
webber0109
Starting Member
2 Posts |
Posted - 2007-06-17 : 23:55:29
|
| help! i am still having the problem above... i've even created a new DTS package from scratch but it is still running twice (with an interval of 10minutes) when it runs overnight. i thought by creating a new package will solve the problem but not this time. btw, this uses package runs inside the batch file with DTSRUN and via window scheduled task. i can't find schedule jobs and sql jobs that runs the same dts package. pls help. |
|
|
|
|
Parts of package running twice