How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Wednesday, March 28, 2012
Inventory system.. Reads and writes
How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>> How does one go about building a highly transactional inventory system
>> where one goes about searching , adding and decrementing inventory..?
>> Can one share their high level architectural design ?
>> I am afraid of intensive blocking . When one goes about buying an item
>> from an inventory, how do you prevent others from not seeing it or even
>> buying it ?
>> Would really love to hear how this is implemented ?
>>
>>
>
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>> How does one go about building a highly transactional inventory system
>> where one goes about searching , adding and decrementing inventory..?
>> Can one share their high level architectural design ?
>> I am afraid of intensive blocking . When one goes about buying an item
>> from an inventory, how do you prevent others from not seeing it or even
>> buying it ?
>> Would really love to hear how this is implemented ?
>>
>>
>
Inventory system.. Reads and writes
How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?
Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>
|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>
sql
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?
Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>
|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>
sql
Monday, March 19, 2012
Invalid Descriptor Index error
I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.
can you cut/paste the entire agent error output?|||The distribution Job fails with this message " Invalid Descriptor Index. The step failed."
There are no other error messages within any of the logs. Have re-applied replication over 12 times now.
|||I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks
|||Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.
-Raymond
|||Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"
Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'
Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3
Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME
Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'
This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.
-Raymond
|||You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.
I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!
Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.
Thanks again!
|||Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.
This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.
Thanks to both of you for your help!!!
|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.
Still sort of worrying about the SP though.
Invalid Descriptor Index error
I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.
can you cut/paste the entire agent error output?|||The distribution Job fails with this message " Invalid Descriptor Index. The step failed."
There are no other error messages within any of the logs. Have re-applied replication over 12 times now.
|||I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks
|||Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.
-Raymond
|||Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"
Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'
Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3
Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME
Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'
This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.
-Raymond
|||You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.
I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!
Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.
Thanks again!
|||Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.
This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.
Thanks to both of you for your help!!!
|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.
Still sort of worrying about the SP though.
Invalid descriptor index
Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:
> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:
> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks
Invalid Descriptor Index
I'm testing db to db transactional replication on a box ( all on the same box
) and the distribution agent fails with the above error. I know it's
something to do with the physical server as this test works on other servers
fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named
and anonymous. Replication also fails if I use the default snapshot location.
I suspect policy restrictions ( maybe on the sql service accounts ) Any
pointers would be helpful - there are no errors other than above, sadly.
The distribution Job fails with this message " Invalid Descriptor Index.
The step failed."
The snapshot works fine, I can see snapshots created ( as I add articles
through tsql ) the data is produced in the designated folder ( not the
default )
When I used the default snapshot folder the snapshot failed with a
permission error - couldn't write the files ( or similar ) which with the
services in the local admins makes me think this is a policy thing.
The servers are not really on the domain and it's actually quite tricky (
like a collection of workgroups ) but that shouldn't stop local replication
working.
This is a hoary problem with no good solution I know of. Some people have
reported success by
1) remove and re-enabling replication (not an option on a clustered server)
2) applying the sp again
3) rearranging the order of columns so the text column is not the last
column in the table. This would require a recreating of the table.
Can you enable logging to determine which table it is breaking on?
http://support.microsoft.com/default...312292&sd=tech
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
> I'm testing db to db transactional replication on a box ( all on the same
> box
> ) and the distribution agent fails with the above error. I know it's
> something to do with the physical server as this test works on other
> servers
> fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
> Server and Agent accounts are in local admins, tried push and pull, named
> and anonymous. Replication also fails if I use the default snapshot
> location.
> I suspect policy restrictions ( maybe on the sql service accounts ) Any
> pointers would be helpful - there are no errors other than above, sadly.
> The distribution Job fails with this message " Invalid Descriptor Index.
> The step failed."
> The snapshot works fine, I can see snapshots created ( as I add articles
> through tsql ) the data is produced in the designated folder ( not the
> default )
> When I used the default snapshot folder the snapshot failed with a
> permission error - couldn't write the files ( or similar ) which with the
> services in the local admins makes me think this is a policy thing.
> The servers are not really on the domain and it's actually quite tricky (
> like a collection of workgroups ) but that shouldn't stop local
> replication
> working.
>
|||Hah - well there's a point, I'm actually replicating a function, although I
did try a table and a procedure all produced the same result.
have removed and replaced replication about twelve times with no change to
result.
will ask about having the sp re-installed but as it's hosted, not sure. I
could try for 2187 rollup I guess.
"Hilary Cotter" wrote:
> This is a hoary problem with no good solution I know of. Some people have
> reported success by
> 1) remove and re-enabling replication (not an option on a clustered server)
> 2) applying the sp again
> 3) rearranging the order of columns so the text column is not the last
> column in the table. This would require a recreating of the table.
> Can you enable logging to determine which table it is breaking on?
> http://support.microsoft.com/default...312292&sd=tech
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
>
>
|||Two considerations. 1) use snapshot replication for replicating schema only
objects - like functions, views, stored procedures. Snapshot replication is
the only replication type which picks up schema changes.
2) try to use sp_addscriptexec to deploy your function if you deployed your
snapshot through a unc. It does not work using ftp.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...[vbcol=seagreen]
> Hah - well there's a point, I'm actually replicating a function, although
> I
> did try a table and a procedure all produced the same result.
> have removed and replaced replication about twelve times with no change to
> result.
> will ask about having the sp re-installed but as it's hosted, not sure. I
> could try for 2187 rollup I guess.
>
> "Hilary Cotter" wrote:
|||I have to provide a scripted solution for replication, using the GUI is not
an option for a controlled environment. It all works fine on the test boxes,
and yes I'm using the snapshot to move the non table objects. It provides a
simplified solution for the client if everything is within one publication,
less chance of mistakes, and they ( or another DBA ) will have to support my
work after I've gone. There are in truth a number of routes I could take but
a consistant method of implementing changes is important.
Anyway I digress - it works except on the production cluster, if I could
extract a more useful error message or figure out how to run the distributor
command out of the agent job ?
Unless I can get a handle on the problem there is no way to go to the data
centre providers so currently we have an impasse as I figure it's the server
config but without some measure of documented proof I can't approach the data
centre.
"Hilary Cotter" wrote:
> Two considerations. 1) use snapshot replication for replicating schema only
> objects - like functions, views, stored procedures. Snapshot replication is
> the only replication type which picks up schema changes.
> 2) try to use sp_addscriptexec to deploy your function if you deployed your
> snapshot through a unc. It does not work using ftp.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...
>
>
|||Use a pre or post snapshot script to deploy the schema only objects then.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...[vbcol=seagreen]
>I have to provide a scripted solution for replication, using the GUI is not
> an option for a controlled environment. It all works fine on the test
> boxes,
> and yes I'm using the snapshot to move the non table objects. It provides
> a
> simplified solution for the client if everything is within one
> publication,
> less chance of mistakes, and they ( or another DBA ) will have to support
> my
> work after I've gone. There are in truth a number of routes I could take
> but
> a consistant method of implementing changes is important.
> Anyway I digress - it works except on the production cluster, if I could
> extract a more useful error message or figure out how to run the
> distributor
> command out of the agent job ?
> Unless I can get a handle on the problem there is no way to go to the data
> centre providers so currently we have an impasse as I figure it's the
> server
> config but without some measure of documented proof I can't approach the
> data
> centre.
> "Hilary Cotter" wrote:
|||It's interesting that there doesn't seem to be any logical solutions or
pointers to this error message. I searched extensively prior to posting ( on
several forums ) and I haven't seen one solution other than re-installing sp3
- which doesn't apply here. I have asked that the data centre re-patch but I
don't know when that will be.
I have to admit I rarely post problems I encounter, as, like now, I never
seem to find a resolution - it's very frustrating !!!
Such is life I guess.
"Hilary Cotter" wrote:
> Use a pre or post snapshot script to deploy the schema only objects then.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...
>
>
|||You can always open a support incident with PSS.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:44A1572A-A1A8-4DC7-A449-D0418991B110@.microsoft.com...[vbcol=seagreen]
> It's interesting that there doesn't seem to be any logical solutions or
> pointers to this error message. I searched extensively prior to posting
> ( on
> several forums ) and I haven't seen one solution other than re-installing
> sp3
> - which doesn't apply here. I have asked that the data centre re-patch but
> I
> don't know when that will be.
> I have to admit I rarely post problems I encounter, as, like now, I never
> seem to find a resolution - it's very frustrating !!!
> Such is life I guess.
> "Hilary Cotter" wrote:
) and the distribution agent fails with the above error. I know it's
something to do with the physical server as this test works on other servers
fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named
and anonymous. Replication also fails if I use the default snapshot location.
I suspect policy restrictions ( maybe on the sql service accounts ) Any
pointers would be helpful - there are no errors other than above, sadly.
The distribution Job fails with this message " Invalid Descriptor Index.
The step failed."
The snapshot works fine, I can see snapshots created ( as I add articles
through tsql ) the data is produced in the designated folder ( not the
default )
When I used the default snapshot folder the snapshot failed with a
permission error - couldn't write the files ( or similar ) which with the
services in the local admins makes me think this is a policy thing.
The servers are not really on the domain and it's actually quite tricky (
like a collection of workgroups ) but that shouldn't stop local replication
working.
This is a hoary problem with no good solution I know of. Some people have
reported success by
1) remove and re-enabling replication (not an option on a clustered server)
2) applying the sp again
3) rearranging the order of columns so the text column is not the last
column in the table. This would require a recreating of the table.
Can you enable logging to determine which table it is breaking on?
http://support.microsoft.com/default...312292&sd=tech
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
> I'm testing db to db transactional replication on a box ( all on the same
> box
> ) and the distribution agent fails with the above error. I know it's
> something to do with the physical server as this test works on other
> servers
> fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
> Server and Agent accounts are in local admins, tried push and pull, named
> and anonymous. Replication also fails if I use the default snapshot
> location.
> I suspect policy restrictions ( maybe on the sql service accounts ) Any
> pointers would be helpful - there are no errors other than above, sadly.
> The distribution Job fails with this message " Invalid Descriptor Index.
> The step failed."
> The snapshot works fine, I can see snapshots created ( as I add articles
> through tsql ) the data is produced in the designated folder ( not the
> default )
> When I used the default snapshot folder the snapshot failed with a
> permission error - couldn't write the files ( or similar ) which with the
> services in the local admins makes me think this is a policy thing.
> The servers are not really on the domain and it's actually quite tricky (
> like a collection of workgroups ) but that shouldn't stop local
> replication
> working.
>
|||Hah - well there's a point, I'm actually replicating a function, although I
did try a table and a procedure all produced the same result.
have removed and replaced replication about twelve times with no change to
result.
will ask about having the sp re-installed but as it's hosted, not sure. I
could try for 2187 rollup I guess.
"Hilary Cotter" wrote:
> This is a hoary problem with no good solution I know of. Some people have
> reported success by
> 1) remove and re-enabling replication (not an option on a clustered server)
> 2) applying the sp again
> 3) rearranging the order of columns so the text column is not the last
> column in the table. This would require a recreating of the table.
> Can you enable logging to determine which table it is breaking on?
> http://support.microsoft.com/default...312292&sd=tech
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
>
>
|||Two considerations. 1) use snapshot replication for replicating schema only
objects - like functions, views, stored procedures. Snapshot replication is
the only replication type which picks up schema changes.
2) try to use sp_addscriptexec to deploy your function if you deployed your
snapshot through a unc. It does not work using ftp.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...[vbcol=seagreen]
> Hah - well there's a point, I'm actually replicating a function, although
> I
> did try a table and a procedure all produced the same result.
> have removed and replaced replication about twelve times with no change to
> result.
> will ask about having the sp re-installed but as it's hosted, not sure. I
> could try for 2187 rollup I guess.
>
> "Hilary Cotter" wrote:
|||I have to provide a scripted solution for replication, using the GUI is not
an option for a controlled environment. It all works fine on the test boxes,
and yes I'm using the snapshot to move the non table objects. It provides a
simplified solution for the client if everything is within one publication,
less chance of mistakes, and they ( or another DBA ) will have to support my
work after I've gone. There are in truth a number of routes I could take but
a consistant method of implementing changes is important.
Anyway I digress - it works except on the production cluster, if I could
extract a more useful error message or figure out how to run the distributor
command out of the agent job ?
Unless I can get a handle on the problem there is no way to go to the data
centre providers so currently we have an impasse as I figure it's the server
config but without some measure of documented proof I can't approach the data
centre.
"Hilary Cotter" wrote:
> Two considerations. 1) use snapshot replication for replicating schema only
> objects - like functions, views, stored procedures. Snapshot replication is
> the only replication type which picks up schema changes.
> 2) try to use sp_addscriptexec to deploy your function if you deployed your
> snapshot through a unc. It does not work using ftp.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...
>
>
|||Use a pre or post snapshot script to deploy the schema only objects then.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...[vbcol=seagreen]
>I have to provide a scripted solution for replication, using the GUI is not
> an option for a controlled environment. It all works fine on the test
> boxes,
> and yes I'm using the snapshot to move the non table objects. It provides
> a
> simplified solution for the client if everything is within one
> publication,
> less chance of mistakes, and they ( or another DBA ) will have to support
> my
> work after I've gone. There are in truth a number of routes I could take
> but
> a consistant method of implementing changes is important.
> Anyway I digress - it works except on the production cluster, if I could
> extract a more useful error message or figure out how to run the
> distributor
> command out of the agent job ?
> Unless I can get a handle on the problem there is no way to go to the data
> centre providers so currently we have an impasse as I figure it's the
> server
> config but without some measure of documented proof I can't approach the
> data
> centre.
> "Hilary Cotter" wrote:
|||It's interesting that there doesn't seem to be any logical solutions or
pointers to this error message. I searched extensively prior to posting ( on
several forums ) and I haven't seen one solution other than re-installing sp3
- which doesn't apply here. I have asked that the data centre re-patch but I
don't know when that will be.
I have to admit I rarely post problems I encounter, as, like now, I never
seem to find a resolution - it's very frustrating !!!
Such is life I guess.
"Hilary Cotter" wrote:
> Use a pre or post snapshot script to deploy the schema only objects then.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...
>
>
|||You can always open a support incident with PSS.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:44A1572A-A1A8-4DC7-A449-D0418991B110@.microsoft.com...[vbcol=seagreen]
> It's interesting that there doesn't seem to be any logical solutions or
> pointers to this error message. I searched extensively prior to posting
> ( on
> several forums ) and I haven't seen one solution other than re-installing
> sp3
> - which doesn't apply here. I have asked that the data centre re-patch but
> I
> don't know when that will be.
> I have to admit I rarely post problems I encounter, as, like now, I never
> seem to find a resolution - it's very frustrating !!!
> Such is life I guess.
> "Hilary Cotter" wrote:
Invalid cursor state @ Distribution Agent
Hi! When i create a Transactional Replication between two SQL Server 2000 (with no additional Service Pack or Update) with the Enterprise Manager, i get this error (also when i restart the agent):
Invalid cursor state
(Source: ODBC Driver Manager [ODBC]; Error number: 24000)
I think it the MDAC is not the problem, because i never updated the SQL Server 2000 and there the error is from the ODBC SQL Driver. But here it′s from the Driver Manager.
Please help me!! I really despair!
Thanks
Does this apply to your case:
http://support.microsoft.com/default.aspx?kbid=831997?
HTH,
Paul Ibison
Invalid cursor state
(Source: ODBC Driver Manager [ODBC]; Error number: 24000)
I think it the MDAC is not the problem, because i never updated the SQL Server 2000 and there the error is from the ODBC SQL Driver. But here it′s from the Driver Manager.
Please help me!! I really despair!
Thanks
Does this apply to your case:
http://support.microsoft.com/default.aspx?kbid=831997?
HTH,
Paul Ibison
Invalid cursor state
Hi! I want to build up a transactional replication between 2 SQL Server 2000. The Publisher is also the distributor.
I used all the wizards and after i made the subscriber, I get an error at the distribution agent:
Invalid cursor state
(Source: ODBC Driver Manager (ODBC); Error number: 24000)
What should i do now?
Please help, thank you!!
Is this error reproducible? For instance if you restart your Distribution
Agent do you get it again?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"oselinge" <oselinge@.discussions.microsoft.com> wrote in message
news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> Hi! I want to build up a transactional replication between 2 SQL Server
2000. The Publisher is also the distributor.
> I used all the wizards and after i made the subscriber, I get an error at
the distribution agent:
> Invalid cursor state
> (Source: ODBC Driver Manager (ODBC); Error number: 24000)
> What should i do now?
> Please help, thank you!!
|||Yes, I get it again! I often tried to restart, but everytime i got this error.
"Hilary Cotter" wrote:
> Is this error reproducible? For instance if you restart your Distribution
> Agent do you get it again?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "oselinge" <oselinge@.discussions.microsoft.com> wrote in message
> news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> 2000. The Publisher is also the distributor.
> the distribution agent:
>
>
|||Oselinge,
usually this is an MDAC/ODBC error - hopefully this
article applies to your case:
http://support.microsoft.com/default.aspx?kbid=831997
HTH,
Paul Ibison
|||I think this is not the reason for this error.
Any other ideas?
"Paul Ibison" wrote:
> Oselinge,
> usually this is an MDAC/ODBC error - hopefully this
> article applies to your case:
> http://support.microsoft.com/default.aspx?kbid=831997
> HTH,
> Paul Ibison
>
I used all the wizards and after i made the subscriber, I get an error at the distribution agent:
Invalid cursor state
(Source: ODBC Driver Manager (ODBC); Error number: 24000)
What should i do now?
Please help, thank you!!
Is this error reproducible? For instance if you restart your Distribution
Agent do you get it again?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"oselinge" <oselinge@.discussions.microsoft.com> wrote in message
news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> Hi! I want to build up a transactional replication between 2 SQL Server
2000. The Publisher is also the distributor.
> I used all the wizards and after i made the subscriber, I get an error at
the distribution agent:
> Invalid cursor state
> (Source: ODBC Driver Manager (ODBC); Error number: 24000)
> What should i do now?
> Please help, thank you!!
|||Yes, I get it again! I often tried to restart, but everytime i got this error.
"Hilary Cotter" wrote:
> Is this error reproducible? For instance if you restart your Distribution
> Agent do you get it again?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "oselinge" <oselinge@.discussions.microsoft.com> wrote in message
> news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> 2000. The Publisher is also the distributor.
> the distribution agent:
>
>
|||Oselinge,
usually this is an MDAC/ODBC error - hopefully this
article applies to your case:
http://support.microsoft.com/default.aspx?kbid=831997
HTH,
Paul Ibison
|||I think this is not the reason for this error.
Any other ideas?
"Paul Ibison" wrote:
> Oselinge,
> usually this is an MDAC/ODBC error - hopefully this
> article applies to your case:
> http://support.microsoft.com/default.aspx?kbid=831997
> HTH,
> Paul Ibison
>
Wednesday, March 7, 2012
Introducing (NOLOCK) into production code for Selects
I have a large application that is multi-user and quite transactional,
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.
We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).
I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:
SELECT <field list> FROM <table> (NOLOCK) WHERE .......
This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.
My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.
Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)
If @.Account_Type = 'P'
Select <some fields> from <view>
Else
Select <other fields> from <view>
Which begs another question, can NOLOCK be used when selecting from a view?
As in:
If @.Account_Type = 'P'
Select <some fields> from <view> (NOLOCK)
Else
Select <other fields> from <view> (NOLOCK)
Thanks for your input.On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkotuby@.snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?
Yes.
What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!
I've seen tons of production code done the way you want.
Not sure I approve of it, but it does what it does.
J.|||As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>|||Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:
SELECT * FROM Table WITH (NOLOCK)
Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.
Andrew J. Kelly SQL MVP
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.
We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).
I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:
SELECT <field list> FROM <table> (NOLOCK) WHERE .......
This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.
My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.
Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)
If @.Account_Type = 'P'
Select <some fields> from <view>
Else
Select <other fields> from <view>
Which begs another question, can NOLOCK be used when selecting from a view?
As in:
If @.Account_Type = 'P'
Select <some fields> from <view> (NOLOCK)
Else
Select <other fields> from <view> (NOLOCK)
Thanks for your input.On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkotuby@.snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?
Yes.
What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!
I've seen tons of production code done the way you want.
Not sure I approve of it, but it does what it does.
J.|||As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>|||Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:
SELECT * FROM Table WITH (NOLOCK)
Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.
Andrew J. Kelly SQL MVP
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>
Friday, February 24, 2012
Intra-query Paralleism caused your query to deadlock . . .
We often get the above message from our Sql Server 2000 Transactional Replication Distribution service. How do we implement the OPTION (MAXDOP 1) hint as directed? It seems I would have to modify the MS procedures. Can't do that. Any suggestions?
Michael
I originally posted this in the Replication forum but was ignored so I thought that perhaps a more "general" forum would attract a different kind of contributor . . .
If you cant give the OPTION (MAXDOP 1) query hint, you can change the Max Degree of Parallelism setting to 1, which in effect gives the same result.|||Are you suggesting that the max parallism for the entire server be changed to 1? Is that the solution? Will setting this option affect other queries in the system? What are other DBA's doing to solve this problem? I am concerned about the performance impact on the other queries in the server.
Thanks a lot Roji.
Subscribe to:
Posts (Atom)