Tuesday, March 27, 2012
Error 8623
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on technet and find the following article
(http://support.microsoft.com/defaul...kb;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e no encontrei informaes sobre este FLAG.
Does someone know any information about this error or about this flag?I found several KB's associated with that error such as this one:
http://support.microsoft.com/defaul...4&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on technet and find the following article
> (http://support.microsoft.com/defaul...kb;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e no encontrei informaes sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>
Error 8623
After some changes in my database when I try to execute following statement:
delete from dbo.PrzychodH
i received this error:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error:
The query processor could not produce a query plan.
Contact your primary support provider for more information.
When I dropped all changes this error does NOT disappeared.
Sql2000 SP4 on WinServ2003 SP1.
Is this SQL2k SP4 new bug?
MarekMarek,
Not sure what you're doing but might take a look at:
http://support.microsoft.com/search...>
ast=3&mode=a
There are quite a few references to the error code in here.
Also, might perform a DBCC CHECKTABLE on przychodH just to rule that out as
a possible cause.
HTH
Jerry
"Marek" <marlie@.wp.pl> wrote in message
news:OcbJYr2wFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> After some changes in my database when I try to execute following
> statement:
>
> delete from dbo.PrzychodH
>
> i received this error:
>
> Server: Msg 8623, Level 16, State 1, Line 1
> Internal Query Processor Error:
> The query processor could not produce a query plan.
> Contact your primary support provider for more information.
>
> When I dropped all changes this error does NOT disappeared.
>
> Sql2000 SP4 on WinServ2003 SP1.
>
> Is this SQL2k SP4 new bug?
>
> Marek
>
>sql
Error 8623
After some changes in my database when I try to execute following statement:
delete from dbo.PrzychodH
i received this error:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error:
The query processor could not produce a query plan.
Contact your primary support provider for more information.
When I dropped all changes this error does NOT disappeared.
Sql2000 SP4 on WinServ2003 SP1.
Is this SQL2k SP4 new bug?
Marek
Marek,
Not sure what you're doing but might take a look at:
http://support.microsoft.com/search/...2&ast=3&mode=a
There are quite a few references to the error code in here.
Also, might perform a DBCC CHECKTABLE on przychodH just to rule that out as
a possible cause.
HTH
Jerry
"Marek" <marlie@.wp.pl> wrote in message
news:OcbJYr2wFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> After some changes in my database when I try to execute following
> statement:
>
> delete from dbo.PrzychodH
>
> i received this error:
>
> Server: Msg 8623, Level 16, State 1, Line 1
> Internal Query Processor Error:
> The query processor could not produce a query plan.
> Contact your primary support provider for more information.
>
> When I dropped all changes this error does NOT disappeared.
>
> Sql2000 SP4 on WinServ2003 SP1.
>
> Is this SQL2k SP4 new bug?
>
> Marek
>
>
Error 8623
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on TechNet and find the following article
(http://support.microsoft.com/default...b;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e no encontrei informaes sobre este FLAG.
Does someone know any information about this error or about this flag?
I found several KB's associated with that error such as this one:
http://support.microsoft.com/default...&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on TechNet and find the following article
> (http://support.microsoft.com/default...b;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e no encontrei informaes sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>
Error 8623
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on TechNet and find the following article
(http://support.microsoft.com/default.aspx?scid=kb;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e não encontrei informações sobre este FLAG.
Does someone know any information about this error or about this flag?I found several KB's associated with that error such as this one:
http://support.microsoft.com/default.aspx?scid=kb;en-us;841404&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on TechNet and find the following article
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e não encontrei informações sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>
Error 8623
After some changes in my database when I try to execute following statement:
delete from dbo.PrzychodH
i received this error:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error:
The query processor could not produce a query plan.
Contact your primary support provider for more information.
When I dropped all changes this error does NOT disappeared.
Sql2000 SP4 on WinServ2003 SP1.
Is this SQL2k SP4 new bug?
MarekMarek,
Not sure what you're doing but might take a look at:
http://support.microsoft.com/search/default.aspx?spid=2852&query=not+produce+a+query+plan&catalog=LCID%3D1033&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a
There are quite a few references to the error code in here.
Also, might perform a DBCC CHECKTABLE on przychodH just to rule that out as
a possible cause.
HTH
Jerry
"Marek" <marlie@.wp.pl> wrote in message
news:OcbJYr2wFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> After some changes in my database when I try to execute following
> statement:
>
> delete from dbo.PrzychodH
>
> i received this error:
>
> Server: Msg 8623, Level 16, State 1, Line 1
> Internal Query Processor Error:
> The query processor could not produce a query plan.
> Contact your primary support provider for more information.
>
> When I dropped all changes this error does NOT disappeared.
>
> Sql2000 SP4 on WinServ2003 SP1.
>
> Is this SQL2k SP4 new bug?
>
> Marek
>
>
Thursday, March 22, 2012
error 8152 in sql update statement
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.
Please post your DDL plus the statement that is giving you trouble.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.
error 8152 in sql update statement
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.sql
error 8152 in sql update statement
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.
Error 8114 Date data
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.sql
Error 8114 Date data
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts?
Thanks in advance.
This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts?
> Thanks in advance.
Error 8114 Date data
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.
Wednesday, March 21, 2012
Error 7399 in Openquery. GetNextRow in Informix
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Informix][Informix ODBC Driver]Error in row.]
[OLE/DB provider returned message: sqlerrm(table_name)]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: ].
I noticed that the error is occuring when an user is Updating/Deleting or Inserting data in the table at the same time.
Environment:
Windows 2K
SQL Server 2000 with SP 3a
SDK Client 2.81 for Informix
ODBC 3.81 for Informix
Informix 7.3 running on a Unix machineHi,
Do you found any solution about this case? I have the same problem as you. Please send me information if you have some, this will be a big help for me.
Thank you and best regards,
ThinkMan|||Hi Thinkman,
Sorry, but I didnt find any solution for OUR problem.
Norivalsql
Error 7391 running distributed transaction
error:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have two servers.
ServerA
Windows 2003
SQL Server 2000 SP3a
MDAC 2.8 RTM
ServerB
Windows 2000
SQL Server 2000 SP3a
MDAC 2.8 RTM
When I open Query Analyzer on ServerA and execute the following
statement, I get the error.
begin distributed transaction
exec ServerB.master.dbo.sp_helpdb
I've already enabled network access for Component Services:
http://support.microsoft.com/default.aspx?kbid=329332
Didn't fix the issue.
Any ideas?
Thanks,
MarkMake sure that the DTC Service is running on both machines as well..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Newton" <mark.newton@.bdsmarketing.com> wrote in message
news:0ia5sv0ifue6po44fbc1j3aipvpas38put@.4ax.com...
> When attempting to run a distributed transaction I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I have two servers.
> ServerA
> Windows 2003
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> ServerB
> Windows 2000
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> When I open Query Analyzer on ServerA and execute the following
> statement, I get the error.
> begin distributed transaction
> exec ServerB.master.dbo.sp_helpdb
>
> I've already enabled network access for Component Services:
> http://support.microsoft.com/default.aspx?kbid=329332
> Didn't fix the issue.
> Any ideas?
> Thanks,
> Mark|||MSDTC is running on both machines.
On Tue, 25 Nov 2003 05:45:42 -0500, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Make sure that the DTC Service is running on both machines as well..
Monday, March 19, 2012
Error 7317 when Accessing Linked Oracle Server
e
Oracle tables I get the following error:
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid schema definition.].
The problem is occurring at my customer's site. I have tried duplicating
their environment in my office, but I can not reporduce the problem.
I would appreciate any help you could offer.
Thanks,
Bill Manring
Chief Software Architect
Wavefront Software, Inc.I found an answer to this problem so I thought I should post it in case
anyone else has a similar problem.
If I execute the query using the OPENQUERY function, the query executes
properly. Since OPENQUERY is a peferred way of querying linked srevers
anyway, there really is no problem.
"Bill Manring" wrote:
> I have a linked server to an Oracle 8i database. When I attempt to query
the
> Oracle tables I get the following error:
> Server: Msg 7317, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' returned an invalid schema definition.
> OLE DB error trace [Non-interface error: OLE/DB provider returned an
> invalid schema definition.].
> The problem is occurring at my customer's site. I have tried duplicating
> their environment in my office, but I can not reporduce the problem.
> I would appreciate any help you could offer.
>
> --
> Thanks,
> Bill Manring
> Chief Software Architect
> Wavefront Software, Inc.
Error 7317 when Accessing Linked Oracle Server
Oracle tables I get the following error:
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid schema definition.].
The problem is occurring at my customer's site. I have tried duplicating
their environment in my office, but I can not reporduce the problem.
I would appreciate any help you could offer.
Thanks,
Bill Manring
Chief Software Architect
Wavefront Software, Inc.
I found an answer to this problem so I thought I should post it in case
anyone else has a similar problem.
If I execute the query using the OPENQUERY function, the query executes
properly. Since OPENQUERY is a peferred way of querying linked srevers
anyway, there really is no problem.
"Bill Manring" wrote:
> I have a linked server to an Oracle 8i database. When I attempt to query the
> Oracle tables I get the following error:
> Server: Msg 7317, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' returned an invalid schema definition.
> OLE DB error trace [Non-interface error: OLE/DB provider returned an
> invalid schema definition.].
> The problem is occurring at my customer's site. I have tried duplicating
> their environment in my office, but I can not reporduce the problem.
> I would appreciate any help you could offer.
>
> --
> Thanks,
> Bill Manring
> Chief Software Architect
> Wavefront Software, Inc.
Error 7139,image column
> The SQL Server gives the following error msg When my app executes an update
> statement to set an image column:
> native error=7139, legth of text,ntext,or image data(70986) to be replicated
> exceeds configured maximum 65536.
I have this same error. Any suggestions?
OLE exception from "cqole":
SQLParamData: RETCODE=-1, State=37000, Native Error=7139
SQL statement="update devtask [...] where dbid = 34163920 and version =
11"
[Microsoft][ODBC SQL Server Driver][SQL Server]Length of text, ntext,
or
image data (73254) to be replicated exceeds configured maximum 65536.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Win32::OLE(0.1702) error 0x80020009: "Exception occurred"
in METHOD/PROPERTYGET "Commit"
client: Windows 2003, OLE
server: Windows 2003, Sql Server 2000
issue the following sp_configure 'max text repl size',value where value is
the largest expected value of your image data. Issue a reconfigure with
override after issuing this command.
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
"topher67" <t0ph3r1967@.netscape.net> wrote in message
news:1162862580.901319.171170@.m7g2000cwm.googlegro ups.com...
> On Thurs, Apr 26 2001 8:52 pm, Stoney said:
>
> I have this same error. Any suggestions?
> OLE exception from "cqole":
> SQLParamData: RETCODE=-1, State=37000, Native Error=7139
> SQL statement="update devtask [...] where dbid = 34163920 and version =
> 11"
> [Microsoft][ODBC SQL Server Driver][SQL Server]Length of text, ntext,
> or
> image data (73254) to be replicated exceeds configured maximum 65536.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> Win32::OLE(0.1702) error 0x80020009: "Exception occurred"
> in METHOD/PROPERTYGET "Commit"
> client: Windows 2003, OLE
> server: Windows 2003, Sql Server 2000
>
Wednesday, March 7, 2012
Error 3930, The current transaction cannot be committed and cannot support
I am trying to exectue this store procedure and I am getting this error
Msg 3930, Level 16, State 1, Line 190
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I am using a linked server "SODIM" and I am trying to execute a distributed transaction between my SQL Server 2005 Express Edition and this linked server SQL Server 2005 standar Edition.
I need to set the isolation level to serializable because I need to block tbl_GuiasDistribuidores table.
I`m executing this SP under sa user and I dont know if a need certanly permissions in the database or in the MS DTC.
I dont know what I`m missing, Could give me some help?
This is the sp code:
ALTERPROCEDURE [dbo].[PCN_ASIGNARGUIAS]
@.Planta varchar(15),
@.DB varchar(50)
AS
BEGIN
DECLARE @.lvaSentencia VARCHAR(8000)
set @.lvaSentencia ='
SET XACT_ABORT on
begin try
begin transaction
declare @.DisCodigo as varchar(15)
declare @.idProceso as int
declare @.guia as numeric
declare @.idDato as int
declare @.Estado as bit
declare @.GuiaFinal as numeric
declare @.AsignarGuia as varchar(2)
delete tbl_ErrorRangosDistribuidores
IF OBJECT_ID('+'''ErrorGuias'''+') IS NOT NULL
BEGIN
drop table ErrorGuias
END
insert into sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
(Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado)
select Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado
from tbl_archivossubidos
SELECT @.idProceso=a.Ars_Codigo FROM sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos a, Tbl_ArchivosSubidos b
where a.Ars_FechaUltimoCambioEfectuado = b.Ars_FechaUltimoCambioEfectuado
and a.Ars_Fecha = b.Ars_Fecha
and a.Ars_NombreArchivo = b.Ars_NombreArchivo
and a.Ars_OrdenProduccion = b.Ars_OrdenProduccion
and a.Doc_Codigo = b.Doc_Codigo
declare CursorCodDistribuidor cursor for
select Dis_Codigo,IndiceOLlaveunica from VisOrdenamientoDatosFinalCiudades
order by Dis_Codigo
open CursorCodDistribuidor
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
set @.Estado = 1
while @.@.fetch_status = 0
begin
SET TRANSACTION ISOLATION LEVEL Read COMMITTED
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
while @.Estado = 1
begin
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
end
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 1
where
Dis_codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
select @.AsignarGuia = Dis_ImprimirGuia from sodim.'+ @.DB +'.dbo.tbl_Distribuidores --with(TABLOCKX SERIALIZABLE)
where Dis_Codigo = @.DisCodigo
if @.AsignarGuia = '+'''SI'''+'
begin
set @.guia = -1
--consulta si hay guias disponibles en la tabla Tbl_GuiasActivas
select @.guia = GAct_Guia from sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where dis_Codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
order by GAct_Guia desc
--si no hay guias disponibles en la tabla Tbl_GuiasActivas, la variable @.guia = -1
if @.guia = -1
begin
-- consulta el valor de GDis_RangoFinal y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinal from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- consulta el valor de la siguiente guia disponible y lo almacena en la variable @.guia
select @.guia = convert(numeric,GDis_ultimaguia) + 1 from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- si @.guia > @.GuiaFinal no hay guias disponibles
if @.guia > @.GuiaFinal
BEGIN
--PRINT '+'''@.guia > @.GuiaFinal'''+'
--PRINT @.guia
--PRINT @.GuiaFinal
--consulta el valor de GDis_RangoFinalReserva y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinalReserva from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--si @.GuiaFinal = 0 no existen guias en el rango de reserva (no hay guias para asignar)
if @.GuiaFinal = 0
begin
--PRINT '+'''entro error guias'''+'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''Se agot el rango principal y de reserva para el Distribuidor'''+')
--PRINT '+'''despues del insert'''+'
RAISERROR (@.DisCodigo, 16, 1)
end
--no hay guias para asignar pero si hay guias en reserva
else
BEGIN
insert into sodim.'+ @.DB +'.dbo.tbl_HistoricoRangoGuias
(Dis_Codigo,HR_RangoInicial,HR_RangoFinal)
select Dis_Codigo,GDis_RangoInicial,GDis_RangoFinal from
sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--asigna las guias de reserva al rango principal
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_RangoInicial = GDis_RangoInicialReserva
,GDis_RangoFinal = GDis_RangoFinalReserva
,GDis_UltimaGuia = GDis_RangoInicialReserva
,GDis_RangoInicialReserva = 0
,GDis_RangoFinalReserva = 0
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--selecciona la ultima guia disponible y la almacena en la variable @.guia
select @.guia = GDis_UltimaGuia from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''se agoto el rango principal para el Distribuidor'''+')
end
end
--no hay guias disponibles en la tabla tbl_guiasActivas y si hay guias disponibles del distribuidor
else
BEGIN
--PRINT '+'''graba ultima guia'''+'
--PRINT @.guia
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
end
end
else
BEGIN
--PRINT '+'''elimina Guia activa'''+'
--PRINT @.guia
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where GAct_Guia = @.guia
and dis_Codigo = @.DisCodigo
END
--PRINT '+'''guarda la Guia en Guia asignada'''+'
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
(Ars_Codigo,Dis_Codigo,GA_Guia,Plan_Codigo)
values
(@.idProceso,@.DisCodigo,@.guia,'+ @.Planta +')
--PRINT '+'''guarda la Guia en datos subidos'''+'
--PRINT @.guia
update dbo.Tbl_datosSubidos
set Guia = @.guia
where IndiceOLlaveunica = @.idDato
END
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
--PRINT '+'''lee otro registro del cursor'''+'
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
--PRINT '+'''despues del commit'''+'
end
commit transaction
end try
--manejo de errores
begin catch
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
(Dis_Codigo,GAct_Guia,Plan_Codigo)
select Dis_Codigo,GA_Guia,plan_Codigo from sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
update Tbl_DatosSubidos
set
guia = '+'''SIN DATOS'''+'
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
--PRINT '+'''mostrar error'''+'
select
ERROR_NUMBER() AS Numero,
ERROR_LINE() AS Linea,
ERROR_MESSAGE() AS Mensaje
into ErrorGuias
rollback transaction
end catch
close CursorCodDistribuidor
deallocate CursorCodDistribuidor
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
delete sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
where Ars_Codigo = @.idProceso
SET TRANSACTION ISOLATION LEVEL Read COMMITTED'
--PRINT @.lvaSentencia
EXEC(@.lvaSentencia)
end
try rolling back the failed transaction before you update data in your catch() block.
e.g.
begin catch
if xact_state()=-1 rollback
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
Sunday, February 26, 2012
Error 3621
server. THe message is being written into my logs on the server.
MSSQL Error Msg 3621 The statement has been terminated
THis is then followed by the information message
Connected to DB
THis continues in a over and over again. My DB was working
fine and I could use the query analyzer, now however I cannot
query anything in the DB.
Any help would be great. I really dont want to reinstall this
as a solution.
TIA
dogdog
This message is usually preceded by another message indicating the cause the
error. Are these messages in the SQL Server error log or in a log generated
by your application?
Hope this helps.
Dan Guzman
SQL Server MVP
<dogdog@.noemail.com> wrote in message
news:pan.2006.03.25.15.15.20.194339@.noemail.com...
> For some reason I'm getting the following error on my MS2000 MSSQl
> server. THe message is being written into my logs on the server.
> MSSQL Error Msg 3621 The statement has been terminated
> THis is then followed by the information message
> Connected to DB
> THis continues in a over and over again. My DB was working
> fine and I could use the query analyzer, now however I cannot
> query anything in the DB.
> Any help would be great. I really dont want to reinstall this
> as a solution.
> TIA
> dogdog
|||On Sat, 25 Mar 2006 09:32:12 +0000, Dan Guzman wrote:
> This message is usually preceded by another message indicating the cause the
> error. Are these messages in the SQL Server error log or in a log generated
> by your application?
The next message after the error 3621 is Statement has been terminated
and is then followed by an information message
stating "connected to db" This sequence just keeps going and going
over and over.
Hope that helps. Because it means absolutely nothing to me.
tks
dogdog
|||So these messages are in a log generated by your application? In that case,
it appears your application is reporting only the 3621 warning message and
not the preceding error that caused the statement to be terminated.
For example, below are the messages that are displayed by Query Analyzer
when I get a primary key violation:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key
in object 'Table1'.
The statement has been terminated.
If you have no control over the application logging behavior, I suggest you
run a Profiler trace to capture error events. This should help you identify
the problem SQL statements.
Hope this helps.
Dan Guzman
SQL Server MVP
<dogdog@.noemail.com> wrote in message
news:pan.2006.03.25.20.57.13.800017@.noemail.com...
> On Sat, 25 Mar 2006 09:32:12 +0000, Dan Guzman wrote:
>
> The next message after the error 3621 is Statement has been terminated
> and is then followed by an information message
> stating "connected to db" This sequence just keeps going and going
> over and over.
> Hope that helps. Because it means absolutely nothing to me.
> tks
> dogdog
|||On Sun, 26 Mar 2006 06:27:38 +0000, Dan Guzman wrote:
First off thanks for taking your time on this and explaining things to
me a novice with mssql.
> So these messages are in a log generated by your application? In that case,
> it appears your application is reporting only the 3621 warning message and
> not the preceding error that caused the statement to be terminated.
>
Yes they are being written to the system log by the mssql server. I
guess that was the best idea for this setup rather than having numerous
logs.
> For example, below are the messages that are displayed by Query Analyzer
> when I get a primary key violation:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key
> in object 'Table1'.
> The statement has been terminated.
> If you have no control over the application logging behavior, I suggest you
> run a Profiler trace to capture error events. This should help you identify
> the problem SQL statements.
It seems as if the last query ran is "stuck" for lack of a better
understanding and just keeps querying. So it runs (without user
interaction for some reason) then reports the Error 3621 Statement
has been terminated. Then waits and reconnects to the server and
does it again. Seems crazy but I didnt make this stuff and it
makes it really hard to figure out when you inherit something like
this. Unfortunately there are no other logs to help in troubleshooting
and I want to avoid reinstalling the entire database. Luckily it
just came online and we just started testing it (i.e. sending
queries on test data) so if it does come down to reloading there
will be nothing lost. I just want to avoid that.
Thanks again for your help. If you have any other ideas I'll be glad
to try them out. I'm going to give the Profiler trace idea a shot.
Hopefully it will lead to something.
error 3205
Server: Msg 3205, Level 16, State 2, Line 3
Too many backup devices specified for backup or restore; only 64 are allowed.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
* my command:RESTORE DATABASE tkb
FROM DISK=’C:\ Program Files\Microsoft SQL Server\MSSQL\BACKUP\tkb.bak’
WITH FILE = 1,
NORECOVERY,NOUNLOAD, REPLACE, STATS = 10*** Help me, please!
My guess is that there was a typo in your command or script that confused the parser into thinking that you had specified more devices for the restore than you had.
Is this the EXACT command you used? Can you cut and paste from a script?
If it wasn't a script, have you tried re-typing and re-running the command?
|||Hi,I think it is not possible to restore a Sql server 2005 backup to Sql server 2000. So restore the backup to Sql server 2005 environment, it will work.