Friday, March 9, 2012
missing rows problem
Recently I have noticed some strange behaviour from SQL Server.
It seems to me that rows are being deleted some time after they are
inserted (or maybe they are unable to be read again).
I use one Trial SQL server 2000 as central server with Merge replication
and MSDE as anonymous subscription.
When I used only Trial server with no replication I didn't notice this
problem (I mean, I didn't get reports of strange behaviour of data
from users using the program).
Couple a days ago I got first report of strange behaviour of data
not being on the server. I said that he is probably mistaken and
that he didn't do something right. So I went to check log of the
data where I found out that he was right, and that SQL server
deleted three rows.
I know this because there is a log with primary key of the data
that were inserted. This log is created inside transaction after
the data is inserted. And there is no stored procedure for deleting
this data.
So he inserted that same rows again, and again for some reasons they
were also deleted after some time. Now I had two logs of the same data
being inserted and not present.
After the third time the data remained there.
Today I got a report of another problem. One row is also missing, but
this time from another table.
I have a couple of data which proves that this row was inserted and
unless using sql commands it can't be removed from database.
Users can only make changes to the data on the Trial server and then
the data are propagated to MSDE server.
I found http://support.microsoft.com/defaul...kb;en-us;826433
which could be related but I'm not sure what to do.
Any suggestions on how to approach this problem? How to detect when and
why are data being removed?From which server are they missing the publisher, subscriber or both?
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrfef$1b5$1@.ss405.t-com.hr...
> Hi!
> Recently I have noticed some strange behaviour from SQL Server.
> It seems to me that rows are being deleted some time after they are
> inserted (or maybe they are unable to be read again).
> I use one Trial SQL server 2000 as central server with Merge replication
> and MSDE as anonymous subscription.
> When I used only Trial server with no replication I didn't notice this
> problem (I mean, I didn't get reports of strange behaviour of data
> from users using the program).
> Couple a days ago I got first report of strange behaviour of data
> not being on the server. I said that he is probably mistaken and
> that he didn't do something right. So I went to check log of the
> data where I found out that he was right, and that SQL server
> deleted three rows.
> I know this because there is a log with primary key of the data
> that were inserted. This log is created inside transaction after
> the data is inserted. And there is no stored procedure for deleting
> this data.
> So he inserted that same rows again, and again for some reasons they
> were also deleted after some time. Now I had two logs of the same data
> being inserted and not present.
> After the third time the data remained there.
> Today I got a report of another problem. One row is also missing, but
> this time from another table.
> I have a couple of data which proves that this row was inserted and unless
> using sql commands it can't be removed from database.
>
> Users can only make changes to the data on the Trial server and then
> the data are propagated to MSDE server.
> I found http://support.microsoft.com/defaul...kb;en-us;826433
> which could be related but I'm not sure what to do.
> Any suggestions on how to approach this problem? How to detect when and
> why are data being removed?|||Nik Marshall-Blank wrote:
> From which server are they missing the publisher, subscriber or both?
>
both|||After thinking about this I have one more question:
Could it be that because of referential integrity at the MSDE
server the data could be pushed back as a delete to Trial SQL
server?
Right now I have Enforce relationship for replication checked.
Should I unckeck it?|||You could try it.
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?|||Just read up on Merge Replications and updates at Subscribers are propogated
back to Publishers si I think this it=s your problem. Maybe you should go
for a different type of replication where subscriber changes are not
progpogated back.
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?
missing rows problem
Recently I have noticed some strange behaviour from SQL Server.
It seems to me that rows are being deleted some time after they are
inserted (or maybe they are unable to be read again).
I use one Trial SQL server 2000 as central server with Merge replication
and MSDE as anonymous subscription.
When I used only Trial server with no replication I didn't notice this
problem (I mean, I didn't get reports of strange behaviour of data
from users using the program).
Couple a days ago I got first report of strange behaviour of data
not being on the server. I said that he is probably mistaken and
that he didn't do something right. So I went to check log of the
data where I found out that he was right, and that SQL server
deleted three rows.
I know this because there is a log with primary key of the data
that were inserted. This log is created inside transaction after
the data is inserted. And there is no stored procedure for deleting
this data.
So he inserted that same rows again, and again for some reasons they
were also deleted after some time. Now I had two logs of the same data
being inserted and not present.
After the third time the data remained there.
Today I got a report of another problem. One row is also missing, but
this time from another table.
I have a couple of data which proves that this row was inserted and
unless using sql commands it can't be removed from database.
Users can only make changes to the data on the Trial server and then
the data are propagated to MSDE server.
I found http://support.microsoft.com/default...b;en-us;826433
which could be related but I'm not sure what to do.
Any suggestions on how to approach this problem? How to detect when and
why are data being removed?
From which server are they missing the publisher, subscriber or both?
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrfef$1b5$1@.ss405.t-com.hr...
> Hi!
> Recently I have noticed some strange behaviour from SQL Server.
> It seems to me that rows are being deleted some time after they are
> inserted (or maybe they are unable to be read again).
> I use one Trial SQL server 2000 as central server with Merge replication
> and MSDE as anonymous subscription.
> When I used only Trial server with no replication I didn't notice this
> problem (I mean, I didn't get reports of strange behaviour of data
> from users using the program).
> Couple a days ago I got first report of strange behaviour of data
> not being on the server. I said that he is probably mistaken and
> that he didn't do something right. So I went to check log of the
> data where I found out that he was right, and that SQL server
> deleted three rows.
> I know this because there is a log with primary key of the data
> that were inserted. This log is created inside transaction after
> the data is inserted. And there is no stored procedure for deleting
> this data.
> So he inserted that same rows again, and again for some reasons they
> were also deleted after some time. Now I had two logs of the same data
> being inserted and not present.
> After the third time the data remained there.
> Today I got a report of another problem. One row is also missing, but
> this time from another table.
> I have a couple of data which proves that this row was inserted and unless
> using sql commands it can't be removed from database.
>
> Users can only make changes to the data on the Trial server and then
> the data are propagated to MSDE server.
> I found http://support.microsoft.com/default...b;en-us;826433
> which could be related but I'm not sure what to do.
> Any suggestions on how to approach this problem? How to detect when and
> why are data being removed?
|||Nik Marshall-Blank wrote:
> From which server are they missing the publisher, subscriber or both?
>
both
|||After thinking about this I have one more question:
Could it be that because of referential integrity at the MSDE
server the data could be pushed back as a delete to Trial SQL
server?
Right now I have Enforce relationship for replication checked.
Should I unckeck it?
|||You could try it.
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?
|||Just read up on Merge Replications and updates at Subscribers are propogated
back to Publishers si I think this it=s your problem. Maybe you should go
for a different type of replication where subscriber changes are not
progpogated back.
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?
missing rows problem
Recently I have noticed some strange behaviour from SQL Server.
It seems to me that rows are being deleted some time after they are
inserted (or maybe they are unable to be read again).
I use one Trial SQL server 2000 as central server with Merge replication
and MSDE as anonymous subscription.
When I used only Trial server with no replication I didn't notice this
problem (I mean, I didn't get reports of strange behaviour of data
from users using the program).
Couple a days ago I got first report of strange behaviour of data
not being on the server. I said that he is probably mistaken and
that he didn't do something right. So I went to check log of the
data where I found out that he was right, and that SQL server
deleted three rows.
I know this because there is a log with primary key of the data
that were inserted. This log is created inside transaction after
the data is inserted. And there is no stored procedure for deleting
this data.
So he inserted that same rows again, and again for some reasons they
were also deleted after some time. Now I had two logs of the same data
being inserted and not present.
After the third time the data remained there.
Today I got a report of another problem. One row is also missing, but
this time from another table.
I have a couple of data which proves that this row was inserted and
unless using sql commands it can't be removed from database.
Users can only make changes to the data on the Trial server and then
the data are propagated to MSDE server.
I found http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
which could be related but I'm not sure what to do.
Any suggestions on how to approach this problem? How to detect when and
why are data being removed?From which server are they missing the publisher, subscriber or both?
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrfef$1b5$1@.ss405.t-com.hr...
> Hi!
> Recently I have noticed some strange behaviour from SQL Server.
> It seems to me that rows are being deleted some time after they are
> inserted (or maybe they are unable to be read again).
> I use one Trial SQL server 2000 as central server with Merge replication
> and MSDE as anonymous subscription.
> When I used only Trial server with no replication I didn't notice this
> problem (I mean, I didn't get reports of strange behaviour of data
> from users using the program).
> Couple a days ago I got first report of strange behaviour of data
> not being on the server. I said that he is probably mistaken and
> that he didn't do something right. So I went to check log of the
> data where I found out that he was right, and that SQL server
> deleted three rows.
> I know this because there is a log with primary key of the data
> that were inserted. This log is created inside transaction after
> the data is inserted. And there is no stored procedure for deleting
> this data.
> So he inserted that same rows again, and again for some reasons they
> were also deleted after some time. Now I had two logs of the same data
> being inserted and not present.
> After the third time the data remained there.
> Today I got a report of another problem. One row is also missing, but
> this time from another table.
> I have a couple of data which proves that this row was inserted and unless
> using sql commands it can't be removed from database.
>
> Users can only make changes to the data on the Trial server and then
> the data are propagated to MSDE server.
> I found http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> which could be related but I'm not sure what to do.
> Any suggestions on how to approach this problem? How to detect when and
> why are data being removed?|||Nik Marshall-Blank wrote:
> From which server are they missing the publisher, subscriber or both?
>
both|||After thinking about this I have one more question:
Could it be that because of referential integrity at the MSDE
server the data could be pushed back as a delete to Trial SQL
server?
Right now I have Enforce relationship for replication checked.
Should I unckeck it?|||You could try it.
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?|||Just read up on Merge Replications and updates at Subscribers are propogated
back to Publishers si I think this it=s your problem. Maybe you should go
for a different type of replication where subscriber changes are not
progpogated back.
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?
Monday, February 20, 2012
Missing files after using File System Task
Hi All,
I don't know if anyone faced this issue. We are having a strange problem. Our process was working well when it was implemented on 32 bit processor.IT ran perfectly for 6 months with out a problem. But when we moved the packages to a 64 bit machine, this issue along with some other issues started to show up.
The issue is we are missing files in the source folder.
Our process is designed such that a source process, brings in a file and updates a status for the file in a audit table. The ETL process picks up the file, then assigns the status as ‘running’ when SRC process is complete and loads into Target DB, and updates ETL status to complete. But current problem is the ETL is losing files after it assigns the status as running. When we looked into the DB weather the data is loaded, we could not find any data related to these files.
we are have mapping level parameters for source path and target path.
We are using a For Each Loop task, and processing files(which are simple flat files) in the source path. The file name is stored in the mapping level parameter. Once the file is process we are moving them into a target path.
Our src and target file paths are on the same drive, just have src folder, inside src folder we have processed folder and failed folder. So files are picked from the source folder and moved into processed folder after processing. The files are not even moved to a failed folder.
There are lot other processing going on this box, and the trend observed is that when more processors are running at peak hour, the missing files’ count is more.
Right now we are refetching those files, as a work around, but does any one has any suggestion why this is happening or any better implementation suggestions?
Thanks
We solved our problem. This was due to the combination of problems.
1. Environment setup for our new 64 bit server
2. Use of event handler for 'On Error' instead of 'On task failure'.
3. Overwrite destination in move file task properties in Event handler.
4. Consistent connection (I don't exactly remember the word, but its in the properties of the connections in connection manager)
1st and 4th issue: The sympotom was the missing files were more when the system was too busy, also processing other packages. When we set up our initial 32 bit machine, we had our named pipes enabled on both client and server. so no issues. But in our new 64 bit environment, the server named pipes was disabled (Shared Memory, TCP/IP were enabled) and client's was enabled, so when client wants a connection, it looks in the same order, 1st Shared Memory, 2nd TCP/IP and last named pipes. But the server, only gives connection on first 2. So the connection were timing out in peak processing.
Added to that the connection properties in the package(4th issue), was not stable state, so every time, its encounters a task in the package which needs DB connection, its requests new connection. So this issue multiplied because the named pipes were disabled. So the solution to this both issues is, just enable named pipes on the server (or disable on client, enable is better since the process has extra options for connections). In the package conection properties, select connection as stable state. This option helps to have stable connection from the start of package execution to the end, instead of connection and disconnecting for each task.
2nd & 3rd issue: This issue was already present in our 32 bit also, but did not show up since we did not have connection failure error due to named pipes. Since connection was timing out on our new environment, each failure consitues to an error, so when the first error raised, the src file was moved to the destination folder(with overwrite destination option true). But due to connection failures, we had often multiple error events, so for subsequest error events, its tries to moved again the src file which is not present since it was moved the first time. So for subsequest errors, since we had overwrite destination option to true, the package first deletes the target files, and tries to move the file and fails, since the src file is not present. This leads to the missing file problem.
The solution to this problem is, just set overwrite destination option to false in file task. Based on your requirement, you can also have the event handler on the 'On Task Failure' event instead of 'on Error event'.
Hope this helps a lot of pains to lot of people, and saves lot of bucks to many companies.
Excuse my spelling mistakes if any.
Thanks,
Venkat
Missing files after using File System Task
Hi All,
I don't know if anyone faced this issue. We are having a strange problem. Our process was working well when it was implemented on 32 bit processor.IT ran perfectly for 6 months with out a problem. But when we moved the packages to a 64 bit machine, this issue along with some other issues started to show up.
The issue is we are missing files in the source folder.
Our process is designed such that a source process, brings in a file and updates a status for the file in a audit table. The ETL process picks up the file, then assigns the status as ‘running’ when SRC process is complete and loads into Target DB, and updates ETL status to complete. But current problem is the ETL is losing files after it assigns the status as running. When we looked into the DB weather the data is loaded, we could not find any data related to these files.
we are have mapping level parameters for source path and target path.
We are using a For Each Loop task, and processing files(which are simple flat files) in the source path. The file name is stored in the mapping level parameter. Once the file is process we are moving them into a target path.
Our src and target file paths are on the same drive, just have src folder, inside src folder we have processed folder and failed folder. So files are picked from the source folder and moved into processed folder after processing. The files are not even moved to a failed folder.
There are lot other processing going on this box, and the trend observed is that when more processors are running at peak hour, the missing files’ count is more.
Right now we are refetching those files, as a work around, but does any one has any suggestion why this is happening or any better implementation suggestions?
Thanks
We solved our problem. This was due to the combination of problems.
1. Environment setup for our new 64 bit server
2. Use of event handler for 'On Error' instead of 'On task failure'.
3. Overwrite destination in move file task properties in Event handler.
4. Consistent connection (I don't exactly remember the word, but its in the properties of the connections in connection manager)
1st and 4th issue: The sympotom was the missing files were more when the system was too busy, also processing other packages. When we set up our initial 32 bit machine, we had our named pipes enabled on both client and server. so no issues. But in our new 64 bit environment, the server named pipes was disabled (Shared Memory, TCP/IP were enabled) and client's was enabled, so when client wants a connection, it looks in the same order, 1st Shared Memory, 2nd TCP/IP and last named pipes. But the server, only gives connection on first 2. So the connection were timing out in peak processing.
Added to that the connection properties in the package(4th issue), was not stable state, so every time, its encounters a task in the package which needs DB connection, its requests new connection. So this issue multiplied because the named pipes were disabled. So the solution to this both issues is, just enable named pipes on the server (or disable on client, enable is better since the process has extra options for connections). In the package conection properties, select connection as stable state. This option helps to have stable connection from the start of package execution to the end, instead of connection and disconnecting for each task.
2nd & 3rd issue: This issue was already present in our 32 bit also, but did not show up since we did not have connection failure error due to named pipes. Since connection was timing out on our new environment, each failure consitues to an error, so when the first error raised, the src file was moved to the destination folder(with overwrite destination option true). But due to connection failures, we had often multiple error events, so for subsequest error events, its tries to moved again the src file which is not present since it was moved the first time. So for subsequest errors, since we had overwrite destination option to true, the package first deletes the target files, and tries to move the file and fails, since the src file is not present. This leads to the missing file problem.
The solution to this problem is, just set overwrite destination option to false in file task. Based on your requirement, you can also have the event handler on the 'On Task Failure' event instead of 'on Error event'.
Hope this helps a lot of pains to lot of people, and saves lot of bucks to many companies.
Excuse my spelling mistakes if any.
Thanks,
Venkat
Missing embedded images in Reporting Services
Any help will be much appreciated.
Hi Eduard,
Any luck resolving this issue? We are having the same problem and have not been able to find a solution. Any help would be appreciated.
Thanks,
Joel
|||Do the images show up when exported to PDF?
You might want to try setting UseSessionCookies to false in the ConfigurationInfo table of the ReportServer db.