Determine sequence of transactions in replication

I am running transactional replication with a push transformable
subscription. I am running into a PK error. After researching the error I
have found two transactions tied to record in the subscription database. So I
use the following to determine which transaction is getting fired first.
select * from distribution.dbo.MSrepl_commands
where publisher_database_id = 29
and article_id = 30
and xact_seqno = 0x0000235000000EFA000100000000
This returns a list of 58 transactions with the command_id and the command.
But I am unable to determine what the command is because it is not readable.
If I use
distribution.dbo.Sp_browsereplcmds
it gives me the command but not the command_id (sequence number).
How can I determine what the sequence of transactions is and read the
command?
Thanks for your time. Mike
Mike,
the code in sp_browsereplcmds is not too difficult. You could create your
own custom script using this as a template, but which includes the
command_id.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

detailsview control - how to edit record in Sql Server Express

I have null values in some of the fields & when I use EDIT to make changes these changes are not recorded + I receive no error.

Check the condition in the update statement. If it is correct..pls provide the complete code...so that we can give you some suggestion accordingly

Detailled error description in a script component (data flow)

Hi,

I'm pretty new in SSIS and i have some problems with error log. I want to get detailled error description in a script component of a dataflow. for the moment I use thooses lines

Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

and for unique constraints on a sql table I have this error : The data value violates integrity constraints.

For the same error, if i use an event handler on error, i have more row and the first of them is more explicit (Variable System::ErrorDescription)

An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert duplicate key row in object 'dbo.dimDepot' with unique index 'IX_dimDepot'.".

Is that possible to have a so detailled error text in a script componnent of a data flow? If yes, How?
Or if i use error event how can authorize the dataflow go ahead even if there is error.

thanks for you help

krest

Having written on the inadequate specificity of OLEDB error messaging available to the data flow as compared to that available to the OnError handler from the underlying SQL Native client, the following route was chosen to circumvent the problem.

Use the ADO.NET destination available in the SSIS Integration Services book written by Kirk Haselden.

Two modifications to the provided ADO.NET destination component in the book were made, to do exactly what you're looking for: inject detail error descriptions/codes as columns in the error output, to allow for their use when error row redirection is desired. They are as follows:

1. With the ADO.NET destination component, add two columns to the error output, NativeErrorMessage (type DT_WSTR ) and NativeErrorCode (type DT_I4).

2. Modify the error handler to populate the NativeErrorMessage and the NativeErrorCode from the the SqlException object.

I would post the code, but I'm not certain if that's allowed by the author or not.

Detailed error reporting. How to?

The best I can get out of my detail log is this which is no help. How do I find out what really happened. My windows app log is no help either

Date 5/24/2006 9:51:41 PM
Log Job History (DTS_xx)

Step ID 1
Server xx
Job Name DTS_xx
Step Name DTS_xx
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xx\administrator. The package execution failed. The step failed.

This KB article discusses various options:
http://support.microsoft.com/kb/918760|||

Michael - I'm glad your online right now. All that I'm trying to do is run stored procs on my database. My ssis package uses the correct login but I keep getting the error I posted. This was running fine on my 2000 box.

So I tried a few other things. I recreated the package on my production server and it works. Seems to be the problem is when I move it from my dev box to production its loosing something.

detailed error information in an e-mail

Hello,

Is there a way to get detailed error information in an e-mail?

Thanks,

Michael

All error information is available in the OnError eventhandler, and you can send emails from there!

-Jamie

|||

Hello,

On sorry but I guess I need some more information.

I created a SendMailTask but I cannot find where to get this error information?

Thanks,

Michael

desperately needing help with a Server Error

hey... i know NOTHING about sql server, .net framework or probably anything
else on sql monster. i am a novice access user, self-taught for about 9
months now and have only a basic understanding of vba. that being said, i
need some help...8)
i have a web site that i used to routinely visit for my job that performs
calculations for monthly car payments based on about 20 user-entered fields.
the problem is that about 3 months ago, it stopped working -- when you hit
the calculate button, the screen will seem to refresh as normal but either
nothing comes up in the monthly payment box, or if it's there, when you clic
k
the print button (and you HAVE to click this button as opposed to printing
right from internet explorer), the following error occurrs:
Server Error in '/' Application
.Net SqlClient Data Provider::Procedure'sp_MW_tblDeals_Updat
e_Main_Finance'
expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Stack Trace: (i will repost this if you need to see it -- it's a little
lengthy)
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NE
T
Version:1.1.4322.2300
Now, here's the weird bit. this is NOT a problem with the web site. it
isn't a problem with internet explorer (99%) or adobe. i know this because
if i log off of windows under my normal user id, and log in as admin, it wil
l
work. the admin id has minimal files, software, etc.. just like most pc's
i'm sure. i uninstalled the .net framework and reinstalled it using version
2.0 and i still get the same error identifying version 1.1.4322.2300. I hav
e
called tech support for this web site but they are useless, trying to have m
e
delete temp internet files and cookies and not much else. i have created a
new user id on this pc just to be able to use this web site (which despite m
y
slow pace at adressing this issue due to lack of company pressure has become
a hot-button for my bosses all of a sudden).
i apologize for the way in which this question was worded, if it even makes
sense at all. i tripped on to this board from access monster and thought it
a stroke of dumb luck, considering i had no idea of where to turn for help
with this. thanks for looking.
Greg
Message posted via http://www.droptable.com"kaosyeti@.comcast.net" <u15580@.uwe> wrote in message
news:6057106c0556f@.uwe...
> hey... i know NOTHING about sql server, .net framework or probably
> anything
> else on sql monster. i am a novice access user, self-taught for about 9
> months now and have only a basic understanding of vba. that being said, i
> need some help...8)
> i have a web site that i used to routinely visit for my job that performs
> calculations for monthly car payments based on about 20 user-entered
> fields.
> the problem is that about 3 months ago, it stopped working -- when you hit
> the calculate button, the screen will seem to refresh as normal but either
> nothing comes up in the monthly payment box, or if it's there, when you
> click
> the print button (and you HAVE to click this button as opposed to printing
> right from internet explorer), the following error occurrs:
>
> Server Error in '/' Application
> .Net SqlClient Data
> Provider::Procedure'sp_MW_tblDeals_Updat
e_Main_Finance'
> expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Stack Trace: (i will repost this if you need to see it -- it's a little
> lengthy)
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
> ASP.NET
> Version:1.1.4322.2300
>
<snip>
The error means that the stored procedure named
sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
the web sites calling code. I suspect someone has changed either the stored
procedure code by adding a new parameter, or changed the web site calling
code by removing a parameter. All I can suggest is that you send the error
message to the web site administrator.
Good Luck
Ron.|||you believe this despite the fact that it works under a different user id on
the same pc?
Ron wrote:
>[quoted text clipped - 28 lines]
><snip>
>The error means that the stored procedure named
>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set i
n
>the web sites calling code. I suspect someone has changed either the stored
>procedure code by adding a new parameter, or changed the web site calling
>code by removing a parameter. All I can suggest is that you send the error
>message to the web site administrator.
>Good Luck
>Ron.
>
Greg
Message posted via http://www.droptable.com|||"kaosyeti@.comcast.net via droptable.com" <u15580@.uwe> wrote in message
news:6067051e82365@.uwe...
> you believe this despite the fact that it works under a different user id
> on
> the same pc?
>
<snip>[vbcol=seagreen]
<snip>
Well, that is what the error from .net sql client means. The error message
suggests a problem with the web site, but from what you say, it sounds as if
you may be having a windows permissions problem. Have you tried deleting the
user you have trouble with, and re-adding it? Can you try using a different
machine? Failing that, perhaps you could post the web site address, so we
could try it from here.
I am unsure what else to suggest that would help you.
Regards,
Ron.

desperately needing help with a Server Error

hey... i know NOTHING about sql server, .net framework or probably anything
else on sql monster. i am a novice access user, self-taught for about 9
months now and have only a basic understanding of vba. that being said, i
need some help...8)
i have a web site that i used to routinely visit for my job that performs
calculations for monthly car payments based on about 20 user-entered fields.
the problem is that about 3 months ago, it stopped working -- when you hit
the calculate button, the screen will seem to refresh as normal but either
nothing comes up in the monthly payment box, or if it's there, when you click
the print button (and you HAVE to click this button as opposed to printing
right from internet explorer), the following error occurrs:
Server Error in '/' Application
.Net SqlClient Data Provider::Procedure'sp_MW_tblDeals_Update_Main_Finance'
expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Stack Trace: (i will repost this if you need to see it -- it's a little
lengthy)
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET
Version:1.1.4322.2300
Now, here's the weird bit. this is NOT a problem with the web site. it
isn't a problem with internet explorer (99%) or adobe. i know this because
if i log off of windows under my normal user id, and log in as admin, it will
work. the admin id has minimal files, software, etc.. just like most pc's
i'm sure. i uninstalled the .net framework and reinstalled it using version
2.0 and i still get the same error identifying version 1.1.4322.2300. I have
called tech support for this web site but they are useless, trying to have me
delete temp internet files and cookies and not much else. i have created a
new user id on this pc just to be able to use this web site (which despite my
slow pace at adressing this issue due to lack of company pressure has become
a hot-button for my bosses all of a sudden).
i apologize for the way in which this question was worded, if it even makes
sense at all. i tripped on to this board from access monster and thought it
a stroke of dumb luck, considering i had no idea of where to turn for help
with this. thanks for looking.
--
Greg
Message posted via http://www.sqlmonster.com"kaosyeti@.comcast.net" <u15580@.uwe> wrote in message
news:6057106c0556f@.uwe...
> hey... i know NOTHING about sql server, .net framework or probably
> anything
> else on sql monster. i am a novice access user, self-taught for about 9
> months now and have only a basic understanding of vba. that being said, i
> need some help...8)
> i have a web site that i used to routinely visit for my job that performs
> calculations for monthly car payments based on about 20 user-entered
> fields.
> the problem is that about 3 months ago, it stopped working -- when you hit
> the calculate button, the screen will seem to refresh as normal but either
> nothing comes up in the monthly payment box, or if it's there, when you
> click
> the print button (and you HAVE to click this button as opposed to printing
> right from internet explorer), the following error occurrs:
>
> Server Error in '/' Application
> .Net SqlClient Data
> Provider::Procedure'sp_MW_tblDeals_Update_Main_Finance'
> expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Stack Trace: (i will repost this if you need to see it -- it's a little
> lengthy)
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
> ASP.NET
> Version:1.1.4322.2300
>
<snip>
The error means that the stored procedure named
sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
the web sites calling code. I suspect someone has changed either the stored
procedure code by adding a new parameter, or changed the web site calling
code by removing a parameter. All I can suggest is that you send the error
message to the web site administrator.
Good Luck
Ron.|||you believe this despite the fact that it works under a different user id on
the same pc?
Ron wrote:
>> hey... i know NOTHING about sql server, .net framework or probably
>> anything
>[quoted text clipped - 28 lines]
>> ASP.NET
>> Version:1.1.4322.2300
><snip>
>The error means that the stored procedure named
>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
>the web sites calling code. I suspect someone has changed either the stored
>procedure code by adding a new parameter, or changed the web site calling
>code by removing a parameter. All I can suggest is that you send the error
>message to the web site administrator.
>Good Luck
>Ron.
>
--
Greg
Message posted via http://www.sqlmonster.com|||"kaosyeti@.comcast.net via SQLMonster.com" <u15580@.uwe> wrote in message
news:6067051e82365@.uwe...
> you believe this despite the fact that it works under a different user id
> on
> the same pc?
>
<snip>
>>The error means that the stored procedure named
>>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set
>>in
>>the web sites calling code. I suspect someone has changed either the
>>stored
>>procedure code by adding a new parameter, or changed the web site calling
>>code by removing a parameter. All I can suggest is that you send the error
>>message to the web site administrator.
<snip>
Well, that is what the error from .net sql client means. The error message
suggests a problem with the web site, but from what you say, it sounds as if
you may be having a windows permissions problem. Have you tried deleting the
user you have trouble with, and re-adding it? Can you try using a different
machine? Failing that, perhaps you could post the web site address, so we
could try it from here.
I am unsure what else to suggest that would help you.
Regards,
Ron.

Desperate with SQL2000 Log Shipping , Out Of Sync

After try and error for several months, finally my SQL2000 LS works.

i gave my self a little celebration for that.

but after running well for a day, just one day !

this morning it give me Out of Sync.

error messages:

failure /4305

activity : Load

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

activity : Copy

failure / 32

The process cannot access the file because it is being used by another process.

i just dont understand, how come the load & copy process failed, after it works for a day.

Hi toni,

Are you using SQL 2000 Standard or Enterprise? In short are you using the Enterprise log shipping feature or carrying out log shipping through scripts?

sk

|||

Hi SK,

I'm using SQL2000 Ent Edition feature , using the Log Shipping Wizard.

The database for this LS is about 40GB, so in this case we restore the database to target server.

we did the restore process using OVDP(open view data protector) and it worked fine for a day until it gave us Out of sync.

any troubleshooting steps will be appreciated.

thx

|||

Not sure i could help you out in this as i am using the Standard edition of SQL Server 2k. However i was interested in log shipping, by script that is, but failed to make it work..So i was curious to know about your setup...

I take it that your transaction logs are created on a shared drive which then is restored by the receiving Sql Server(target).. Could it be that the network connection was lost and it missed out on one of the logs in between?

This guess is based on the fact that it worked for a day and so there shouldnt be any reason why it fails unless there is a communication problem...

Hope someone else could point out the technicalities behind it...

sk

|||

//Could it be that the network connection was lost and it missed out on one of the logs in between?//

i'll observe that, but i wonder if the system itself already has the mechanism to react on that problems ?

try to reconfigure the LS , set the interval to 15 mins, lets see...

thx

|||

failed again, but this time i think i found it.

there is another job running trx backup to tape device, so the missing trx goes to tape.

rgds

Desperate with SQL2000 Log Shipping , Out Of Sync

After try and error for several months, finally my SQL2000 LS works.

i gave my self a little celebration for that.

but after running well for a day, just one day !

this morning it give me Out of Sync.

error messages:

failure /4305

activity : Load

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

activity : Copy

failure / 32

The process cannot access the file because it is being used by another process.

i just dont understand, how come the load & copy process failed, after it works for a day.

Hi toni,

Are you using SQL 2000 Standard or Enterprise? In short are you using the Enterprise log shipping feature or carrying out log shipping through scripts?

sk

|||

Hi SK,

I'm using SQL2000 Ent Edition feature , using the Log Shipping Wizard.

The database for this LS is about 40GB, so in this case we restore the database to target server.

we did the restore process using OVDP(open view data protector) and it worked fine for a day until it gave us Out of sync.

any troubleshooting steps will be appreciated.

thx

|||

Not sure i could help you out in this as i am using the Standard edition of SQL Server 2k. However i was interested in log shipping, by script that is, but failed to make it work..So i was curious to know about your setup...

I take it that your transaction logs are created on a shared drive which then is restored by the receiving Sql Server(target).. Could it be that the network connection was lost and it missed out on one of the logs in between?

This guess is based on the fact that it worked for a day and so there shouldnt be any reason why it fails unless there is a communication problem...

Hope someone else could point out the technicalities behind it...

sk

|||

//Could it be that the network connection was lost and it missed out on one of the logs in between?//

i'll observe that, but i wonder if the system itself already has the mechanism to react on that problems ?

try to reconfigure the LS , set the interval to 15 mins, lets see...

thx

|||

failed again, but this time i think i found it.

there is another job running trx backup to tape device, so the missing trx goes to tape.

rgds

Desperate on installing SP 1

after we download sp1 and hit runj, we got "Unspecific Error Occur" popup window, with message:The following unexpected error occur:" then without any detail. here is the hotfix log:

Please help

08/01/2006 14:33:51.967 ================================================================================
08/01/2006 14:33:51.967 Hotfix package launched
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: CommonFilesDir, string value = C:\Program Files (x86)\Common Files
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files (x86)
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: CommonFilesDir, string value = C:\Program Files (x86)\Common Files
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files (x86)
08/01/2006 14:33:52.014 Local Computer:
08/01/2006 14:33:52.014 Target Details: ISSQL05
08/01/2006 14:33:52.014 commonfilesdir = C:\Program Files (x86)\Common Files
08/01/2006 14:33:52.014 lcidsupportdir = e:\1af2e497cde06964771333\1033
08/01/2006 14:33:52.014 programfilesdir = C:\Program Files (x86)
08/01/2006 14:33:52.014 supportdir = \\ISSQL05\e$\1af2e497cde06964771333
08/01/2006 14:33:52.014 supportdirlocal = e:\1af2e497cde06964771333
08/01/2006 14:33:52.014 windir = C:\WINDOWS
08/01/2006 14:33:52.014 winsysdir = C:\WINDOWS\system32
08/01/2006 14:33:52.014
08/01/2006 14:33:52.014 Enumerating applicable products for this patch
08/01/2006 14:33:53.530 The patch installation could not proceed due to unexpected errors
08/01/2006 14:33:53.530
08/01/2006 14:33:53.530 Product Status Summary:
08/01/2006 14:33:53.780 Hotfix package closed

I also faced this error after process "Enumerating applicable products for this patch" once installing SQL SP1 but I m not sure why this came up. That was a development machine so I didnt fiddle much with it.

Regards

|||hmm.. I have the same problem.|||

I found sollution in this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=614124&SiteID=1&mode=1

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.
|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL.

Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.
|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.