Thursday, March 22, 2012
error 8152
ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
length of the column will not result any error. But I still get error 8152:
String or binary data would be truncated, within a trigger, even I set
ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Contex
t?hi
Just see this:
ANSI_WARNINGS
When set to ON, errors or warnings are issued when conditions such as
"divide by zero" occur or null values appear in aggregate functions. When se
t
to OFF, no warnings are raised when null values appear in aggregate
functions, and null values are returned when conditions such as "divide by
zero" occur. By default, ANSI_WARNINGS is OFF.
SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on
computed columns or indexed views.
Connection-level settings (set using the SET statement) override the default
database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issu
e
a connection-level SET statement setting ANSI_WARNINGS to ON for the session
when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.
The status of this option can be determined by examining the
IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"nonno" wrote:
> I read the Error Handling article by Erland Sommarskog, he said when
> ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
> length of the column will not result any error. But I still get error 8152
:
> String or binary data would be truncated, within a trigger, even I set
> ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Context?[/co
lor]|||Can you post a reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:A6D01321-73CE-4EE8-BACC-B8C2B9A08F06@.microsoft.com...
>I read the Error Handling article by Erland Sommarskog, he said when
> ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
> length of the column will not result any error. But I still get error 8152
:
> String or binary data would be truncated, within a trigger, even I set
> ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Context?[/co
lor]|||thx 4 ur reply, Chandra
I know that Query Analyzer is connected thro' ODBC, but I get 0 from
IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function, and I'm
sure that the connection property 'Set ansi_warnings' is ON, any idea?
"Chandra" wrote:
> hi
> Just see this:
> ANSI_WARNINGS
> When set to ON, errors or warnings are issued when conditions such as
> "divide by zero" occur or null values appear in aggregate functions. When
set
> to OFF, no warnings are raised when null values appear in aggregate
> functions, and null values are returned when conditions such as "divide by
> zero" occur. By default, ANSI_WARNINGS is OFF.
> SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes
on
> computed columns or indexed views.
> Connection-level settings (set using the SET statement) override the defau
lt
> database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients is
sue
> a connection-level SET statement setting ANSI_WARNINGS to ON for the sessi
on
> when connecting to SQL Server. For more information, see SET ANSI_WARNINGS
.
> The status of this option can be determined by examining the
> IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "nonno" wrote:
>
Error 8152
I have the following problem. I create a small request to truncate a column
(see below). When I run it, I get an error 8152. I don't understand why.
I have no null value into the table.
I would appreciate any help
tia
Samuel
ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
COLLATE Latin1_General_CI_AS NOT NULLError 8152 ("String or binary data would be truncated.") indicates that you
have data in the column that is longer than the length you are trying to set
it to, i.e. longer than 35 characters.
Do
SELECT MAX(LEN(Number)) FROM ArticleNumber
to find out how long the values in that column are.
Jacco Schalkwijk
SQL Server MVP
"Samuel Bernard" <SamuelBernard@.discussions.microsoft.com> wrote in message
news:0F9B9A47-B83C-43B0-A6C1-F6D4EE61C56E@.microsoft.com...
> Hi,
> I have the following problem. I create a small request to truncate a
> column
> (see below). When I run it, I get an error 8152. I don't understand why.
> I have no null value into the table.
> I would appreciate any help
> tia
> Samuel
> ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
> COLLATE Latin1_General_CI_AS NOT NULL|||Hi
What data type is the column now?
The error message indicates that there is data, with lergth exceeding 35, in
the column and SQL Server will not just throw the extra data away.
Run something like this frist to remove the extra characters (NOTE: You
loose data by doing this, so only run it after careful consideration and
testing)
Update ArticleNumber
SET Number = SUBSTRING(Number, 1, 35)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Samuel Bernard" wrote:
> Hi,
> I have the following problem. I create a small request to truncate a colum
n
> (see below). When I run it, I get an error 8152. I don't understand why.
> I have no null value into the table.
> I would appreciate any help
> tia
> Samuel
> ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
> COLLATE Latin1_General_CI_AS NOT NULL|||Hi Jacco,
Thanks you for your quick and GOOD answer.
"Jacco Schalkwijk" wrote:
> Error 8152 ("String or binary data would be truncated.") indicates that yo
u
> have data in the column that is longer than the length you are trying to s
et
> it to, i.e. longer than 35 characters.
> Do
> SELECT MAX(LEN(Number)) FROM ArticleNumber
> to find out how long the values in that column are.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Samuel Bernard" <SamuelBernard@.discussions.microsoft.com> wrote in messag
e
> news:0F9B9A47-B83C-43B0-A6C1-F6D4EE61C56E@.microsoft.com...
>
>
Error 8102:cannot update identity column
Thanks.Check out this MSDN Article:Replication: Managing Identity Values.
Terri|||Thanks for the reply, I already read this article and it solves the problem but creates another. The article does describe how to increment the Identity column on the subscriber, but it will not match the value on the publisher because you must set the ID column ranges in seperate blocks. Server P 1-1000 Server S 1001-2000 When 1 is used on P, it insert 1001 on S.
Here's what I'm doing and maybe there is another way to do it.
I have two servers running my application, 1 exposed to the Internet for client access and one for my Intranet. I need an exact replica of my database on the Internet server that is being updated periodically from my Intranet server. I am installing clustered servers soon but I need to have an interim solution in place until then. The publically exposed server is also to act as a backup in the event that my Intranet server goes down.
The database is currently being replicated between the servers, but the Identity columns on the subscriber are not set, so if we had to switch to it I would have to define the ID columns in my tables for my application to work properly.
Would it be okay to point my Internet servers WEB.CONFIG data connection string to the Intranet server and just allow the replication to continue as is, and if I do have an outage direct my Intranet traffic to the Internet server, change the conn string in the WEB.CONFIG and define the ID columns?
Thanks in advance.|||Is your Internet server then serving as a warm standby? Is it then basically a read-only version of the database on your Intranet server??
If so, I can telll you that we usetransaction log shipping to keep a warm standby of our database on a different server.
Otherwise, I'm sorry, replication is out of my league. We had used replication for a short while until we found out that it did not meet our needs (due in part to the IDENTITY column situation). With some research and effort we might have gotten replication to work, but log shipping was exactly right for us.
Terri
PS, if you haven't seen it, you might consider this article:Architecting Replication with Identity Columns
|||Transaction logs would work if I had the Enterprise version of SQL, but I won't have it for a few more months, at which point I am installing clustered servers. I think for the time being I am going to go with allowing it to replicate with no ID columns set on the subscriber. I only have 5, so if there was a problem I could just set the ID columns on the subscriber and then change my DB Connection in my WEB.CONFIG to point to it. This is however a temporary solution.
Thanks, I found your info most helpful.
error 8102:cannot update identity column
I have 2 SQL Servers 2000 enterprise version. I setup a transactional replication between them. There is a table using identity feature and set to not for replication. On the server A as publication, B as Subscriber. I created table in B manually and set the name conflict in article default to keep existing data. When it replicates at the first time, everything is fine, but if there is some data added to the table in Server A, the replication fails, saying cannot update indentity column.
I read about something on add Set Identity on/off thing. But I dont know which SP I should add this to. Please help. As detail as possible. Thanks.There is a bug in the Managed Range Identity system. In Merge Replication I have found that while creating a publication using range identities it will work only the first time. Changing settings such as Publisher range or subscriber range does not have an effect. Also, deleting the subscriptions and publications DOES NOT remove the constraints from the affected subscriber tables. Also, the entry for the database in the replication distribution DB table MSrepl_identity_range does not get deleted and therefore the next time the publication is run it will not take the correct settings. In order to successfully recreate the subscription you must delete the row(s) from the afore mentioned table and remove all constraints manually. Only then will it work. If the constraints are not removed the subscriber will show the identity range as used up and will demand a re-sync, and the re-sync will have no effect.
Applies to SQL Server 2000 All SPs
You MUST delete all publications on the affected database and subscriptions first, then run SP_RemoveDBReplication DBName on the publisher and subscribers first then follow the steps above. You can sp_help to see what constraints are on the tables.|||I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?|||Originally posted by cobraeyez
I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?
no, you can set it up. Just keep in mind that there is a bug in SQL Server relating to the identitiy managing subsystem. What you want to do is delete the pub, subscriptions, drop the Check Constraints on all the subscription tables, find the Identity_Range table in the Replication Distribution database and delete all entries for the database that you are working on. Then recreate the publication and subcription and you will be fine.|||hey. thanks that SP_RemoveDBReplication thing works. :)|||ok, maybe I speak too quick, I think the insert works, but the update doesn't . If I modify a record in the publisher the replication failed again.
And what I did for fix the insert record is remove the publications and subscriptions and ran that RemoveReplication command on the DBs, there is no records in MSrepl_indentity_range table for me to delete.|||ok. The article containing the identitiy field is not set to range identitiy. When you set up the publication you need to click the "..." button to the right of the article name, then click the "Identity Range" tab. Enter your values and click OK to start the Identitiy Ranging.|||thanks, you mean the table article property button (...) right? In there, there is no Identity Range Tab. There are 3 tabs in it. General, Command, Snapshot.|||You will only have that tab on articles that have an identitiy column. Are you sure the article has an Identitiy?|||yes, and I am using sql 2000, identity is enabled and set not for replication|||You've got much of suggestions to the question, but I hope this MSDN Link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp) also gives you much insight to resolve.
HTH|||I had a similar problem and it was because I had triggers and DRI on the same table
Error 8102: Cannot Update Identity Column
I'd set up Transactional Replication btw 2 servers running on MSSQL 2000.
However, from time to time, I encounter Error 8102. I try to reinitialise the subscription but failed. After I delete the current subscription and PUSH a new one, it will works fine again.
I'm curious why this is happening as I'd already set all the tables (articles) in the subscriber to "YES, not_for_replication".
Any idea?
Thanks.Originally posted by da_i256
Hi,
I'd set up Transactional Replication btw 2 servers running on MSSQL 2000.
However, from time to time, I encounter Error 8102. I try to reinitialise the subscription but failed. After I delete the current subscription and PUSH a new one, it will works fine again.
I'm curious why this is happening as I'd already set all the tables (articles) in the subscriber to "YES, not_for_replication".
Any idea?
Thanks.
check this thread:
http://dbforums.com/arch/7/2003/5/779346|||thanks, harshal_in...
then link did provide some insights into the matter.
anyone else got any idea?
thanks.|||Have you gone thru the MSDN link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp) referred in the above post, it must have resolved with the workaround mentioned.|||Thanks guys...
the problem solved... :>sql
Monday, March 19, 2012
Error 7356 - do we have to live with that?
(OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column
was supplied during execution that was not found at compile time.
OLE DB error trace [Non-interface error: OLE DB provider returned an extra
column at execution time: ProviderName=.....)
I change the table name to a view returning all columns of the same table.
I have been doing this since SQL 7.0.
1. Is it an unfixable bug we have to live with?
2. Is there a better way of fixing the problem other than creating a view?
Thanks,
-StanHi Stan,
What is your SQL Server version? Is it SQL Server 7.0 SP4 or SQL Server
2000 SP3?
From your post, I am not sure about the steps to reproduce the problem.
Could you post it so that I can check further?
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| Subject: Error 7356 - do we have to live with that?
| Date: Fri, 12 Sep 2003 14:15:35 -0400
| Lines: 20
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306189
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| To resolve a well know issue with quering a linked server:
|
| (OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra
column
| was supplied during execution that was not found at compile time.
| OLE DB error trace [Non-interface error: OLE DB provider returned an
extra
| column at execution time: ProviderName=.....)
|
| I change the table name to a view returning all columns of the same table.
|
| I have been doing this since SQL 7.0.
|
| 1. Is it an unfixable bug we have to live with?
|
| 2. Is there a better way of fixing the problem other than creating a view?
|
| Thanks,
|
| -Stan
|
|
||||Hi Stan,
I have found several articles regarding error "OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time." However, for SQL Server 7.0,
there is an article.
266337 PRB: Some SQL Server 7.0 Service Pack 2 Updates Must be Added
Manually
http://support.microsoft.com/?id=266337
However, I did not find any known issue regarding the same error on SQL
Server 2000. According to your description, it seems that the problem also
occurs on SQL Server 2000. Am I right? Since the problem randomly occurs,
it is very difficult to troubleshoot. However, please try the following
steps first:
1. The output from running "sp_helpserver" from the SQL Server 2000 machine
and the name of the linked server which encounters the problem.
2. Does dropping the linked server and adding it back work?
3. The output from the Component Checker from SQL Server 2000.
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
<zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
| Subject: Re: Error 7356 - do we have to live with that?
| Date: Mon, 15 Sep 2003 08:02:11 -0400
| Lines: 71
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08|||Bill,
> However, I did not find any known issue regarding the same error on SQL
> Server 2000. According to your description, it seems that the problem also
> occurs on SQL Server 2000. Am I right?
It ocurrs in the query on SQL Server 2000 against SQL Server 7.0 linked
server.
So, the culprilt is probably SQL Server 7.0
> 2. Does dropping the linked server and adding it back work?
No, it does not
Thanks!
-Stan|||Bill,
> However, I did not find any known issue regarding the same error on SQL
> Server 2000. According to your description, it seems that the problem also
> occurs on SQL Server 2000. Am I right?
It ocurrs in the query on SQL Server 2000 against SQL Server 7.0 linked
server.
So, the culprilt is probably SQL Server 7.0
> 2. Does dropping the linked server and adding it back work?
No, it does not
Thanks!
-Stan|||Hi Stan,
According to my research, we need to install Service pack 4 and also apply
the Instcat.sql manually.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
<zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
<ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
<noEhU9LfDHA.1388@.cpmsftngxa06.phx.gbl>
<#OO9DMRfDHA.2320@.TK2MSFTNGP12.phx.gbl>
<0fWgE0dfDHA.2588@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error 7356 - do we have to live with that?
| Date: Thu, 18 Sep 2003 15:34:12 -0400
| Lines: 69
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eNgonvhfDHA.1732@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:307320
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Bill,
|
| No, we don't have the hotfix on 7.0. Woudn't be better to put the latest
| service pack for SQL 7.0 (I guess it SP4)?
|
| ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
| news:0fWgE0dfDHA.2588@.cpmsftngxa06.phx.gbl...
| > Hi Stan,
| >
| > Have you tried the methods in 266337? I have not found any known issue
| > regarding this in SQL Server 2000.
| >
| > Has the error occurred after you apply 266337 on SQL Server 7.0? If so,
| > could you let me know what operations you have performed before the
error
| > occurs?
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --
| > | From: "Stan" <nospam@.yahoo.com>
| > | References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
| > <zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
| > <ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
| > <noEhU9LfDHA.1388@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error 7356 - do we have to live with that?
| > | Date: Wed, 17 Sep 2003 07:57:59 -0400
| > | Lines: 21
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <#OO9DMRfDHA.2320@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: 12.148.36.131
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306904
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Bill,
| > |
| > | > However, I did not find any known issue regarding the same error on
| SQL
| > | > Server 2000. According to your description, it seems that the
problem
| > also
| > | > occurs on SQL Server 2000. Am I right?
| > |
| > | It ocurrs in the query on SQL Server 2000 against SQL Server 7.0
linked
| > | server.
| > | So, the culprilt is probably SQL Server 7.0
| > |
| > | > 2. Does dropping the linked server and adding it back work?
| > | No, it does not
| > |
| > |
| > |
| > | Thanks!
| > |
| > | -Stan
| > |
| > |
| > |
| > |
| >
|
|
|
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
>
Friday, March 9, 2012
Error 515
" Cannot insert the value NULL into column 'nicknames', table
'REP_Sub.dbo.MSmerge_genhistory'; column does not allow nulls. INSERT fails. "
Can anyone help
Did you register your publisher or subscriber using an IP address? There
have been reports in the past that this can cause this problem.
Hilary Cotter
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
"Sesha" <Sesha@.discussions.microsoft.com> wrote in message
news:87EB3D3B-913F-4B6B-8FC7-471517A0F444@.microsoft.com...
> I get this error during Merge replication
> " Cannot insert the value NULL into column 'nicknames', table
> 'REP_Sub.dbo.MSmerge_genhistory'; column does not allow nulls. INSERT
fails. "
> Can anyone help
|||Sesha,
You must check @.@.servername on the SUBSCRIBER. This will probably return a
NULL.
Select * from sysservers
This will give you a list of local/linked/remote servers on that box. If you
returned NULL above, it's because the server has gotten confused about not
having a local named server (SRVID=0) to put in the nickname table.
If you don't have a SRVID=0 and your servername does exist in the list then
do the following:
SP_dropserver 'Servername'
go
Sp_Addserver 'Servername','local'
If it doesn't exist and you don't have SRVID=0 then just do the Add.
That should sort you out.
Good luck.
"Hilary Cotter" wrote:
> Did you register your publisher or subscriber using an IP address? There
> have been reports in the past that this can cause this problem.
> --
> Hilary Cotter
> 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
> "Sesha" <Sesha@.discussions.microsoft.com> wrote in message
> news:87EB3D3B-913F-4B6B-8FC7-471517A0F444@.microsoft.com...
> fails. "
>
>
Friday, February 17, 2012
Error -2147217900 (or 80040E14 in Hex)
I'm trying to execute the following sentence:
ALTER TABLE LIBRE2 ALTER COLUMN DAT001 varchar(5)
getting the following error:
Error in ALTER TABLE ALTER COLUMN DAT001. PRIMARY KEY CONSTRAINT aaaaaLIBRE2_PK has access to this column.
I'm using Visual Basic 5.0 over an SQLServer 7.0 database.
It seems to be an error with the index of the table, but I need to make this change inorder to solver other problems I have, and I need to do this operation by code, not with de tools of the SQL Server. Does anyone knows any workaround to solve this problem?
Thanks in advance!!You will have to drop the constraint first, alter the column, then reenable the constraint. Is this a 24/7 database ? Also, is this being used as a foreign key constraint in other tables ?|||What other problems are you having ?|||After analyzing the problem I reached the same solution, so I make the process just to make a few changes before altering the table. Not only remove the constraint, but also check if the field was a primary key or foreign key, but it just give me a new implementation problem, but I'm solving it. After the holidays I think I'll solve it all.
Thanks a lot!|||Your welcome. Good luck.
Error 213: Insert Error: Column name or values don't match table definition
I have had the below procedure working and in use for some time now,
and suddently i started getting the below error:
"Eorror 213: Insert Error: Column name or number of supplied values
does not match table definition"
Thank you
CREATE PROCEDURE bat_LogData (
@.ComputerName varchar(50),
@.UserDomain varchar(50),
@.UserName varchar(50),
@.Date_Time datetime,
@.Message varchar(128),
@.SubMessage varchar(128),
@.ErrorText varchar(128),
@.ErrorCode nvarchar(15),
@.smsguid nvarchar(41),
@.locale int,
@.scriptver nvarchar(15),
@.wshver nvarchar(15)
)
AS
INSERT INTO LogonScript_data VALUES (
@.ComputerName,
@.UserName,
@.UserDomain,
GetDate(),
@.Date_Time,
@.Message,
@.SubMessage,
@.ErrorText,
@.ErrorCode,
@.smsguid,
@.locale,
@.scriptver,
@.wshver
)
SELECT @.@.IDENTITY AS 'MessageID'
GOrdixon5652@.hotmail.com wrote:
> Hello,
> I have had the below procedure working and in use for some time now,
> and suddently i started getting the below error:
> "Eorror 213: Insert Error: Column name or number of supplied values
> does not match table definition"
> Thank you
> CREATE PROCEDURE bat_LogData (
> @.ComputerName varchar(50),
> @.UserDomain varchar(50),
> @.UserName varchar(50),
> @.Date_Time datetime,
> @.Message varchar(128),
> @.SubMessage varchar(128),
> @.ErrorText varchar(128),
> @.ErrorCode nvarchar(15),
> @.smsguid nvarchar(41),
> @.locale int,
> @.scriptver nvarchar(15),
> @.wshver nvarchar(15)
> )
> AS
> INSERT INTO LogonScript_data VALUES (
> @.ComputerName,
> @.UserName,
> @.UserDomain,
> GetDate(),
> @.Date_Time,
> @.Message,
> @.SubMessage,
> @.ErrorText,
> @.ErrorCode,
> @.smsguid,
> @.locale,
> @.scriptver,
> @.wshver
> )
> SELECT @.@.IDENTITY AS 'MessageID'
> GO
I expect the table changed. Did you add or drop a column?
Always list the column names in an INSERT statement. Leaving them out
as you have done will inevitably make your code less reliable and
harder to debug. For example:
INSERT INTO LogonScript_data (
computername, username, userdomain, dt, date_time, message,
submessage,
errortext, errorcode, smsguid, locale, scriptver, wshver)
VALUES
(@.computername, @.username, @.userdomain, GETDATE(), @.date_time,
@.message,
@.submessage, @.errortext, @.errorcode, @.smsguid, @.locale, @.scriptver,
@.wshver
)
David Portas
SQL Server MVP
--
error 21268
[Microsoft SQL-DMO] Error 21268: [SQL-DMO]Row or column specified is outside
the
range of the specified query result set.
this refers to sql 200 sp2 .. when I try upgrade to service pack 3 it tells
I do not have sql 2000 on my system
Please help
Frank
hi Frank,
frank wrote:
> I have msde version 8.00.760 and when I try a sqlmaint it tells me
8.00.760 is service pack 3
> [Microsoft SQL-DMO] Error 21268: [SQL-DMO]Row or column specified is
> outside the
> range of the specified query result set.
> this refers to sql 200 sp2 .. when I try upgrade to service pack 3 it
> tells I do not have sql 2000 on my system
>
how are you trying to upgrade to sp3 (BTW, you already have it installed...
you eventually should go for sp4)?
pleas post the command prompt line you execute including all parameters as
long as your original instance name..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
Wednesday, February 15, 2012
Error 209 Ambiguous column name ...Transaction count after EXECUTE indicates that a COMMIT
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that....."
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
here is my stored proc
CREATE PROCEDURE dbo.ExportFile
@.intMonth INT,
@.intYear INT,
@.dtFirstDayOfMonth DATETIME
AS
BEGIN
BEGIN TRANSACTION
/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @.dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @.dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @.@.ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT
IF @.@.ERROR<>0
GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
END
ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO
Yes Motley is right, not all error can be processed using IF @.@.ERROR<>0. Some errors will cause break the whole batch so that the error handling is skipped. This shows the flaw in SQL2000 error handling. SQL2005 has much enhancement in error handling, which allows you to use TRY..CATCH block. You can take a look at this article:
http://msdn.microsoft.com/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp?
Error 209 Ambiguous column ..Transaction count after EXECUTE indicates .....
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
here is my stored proc
CREATE PROCEDURE dbo.ExportFile
@.intMonth INT,
@.intYear INT,
@.dtFirstDayOfMonth DATETIME
AS
BEGIN
BEGIN TRANSACTION
/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @.dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @.dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @.@.ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT
IF @.@.ERROR<>0
GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
END
ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO
The procedure stops immediately after an error like this. In fact, 2005's try..catch will not even stop it. It is pretty rare, and I am guessing that you modified one of the objects after creating the procedure:
create table testCrash
(
testCrashId int,
value varchar(10)
)
create table testCrash2
(
testCrash2Id int
)
go
create procedure testCrash$test
as
begin tran
select value
from testCrash
cross join testCrash2
rollback tran
go
exec testCrash$test
go
alter table testCrash2
add value varchar(10)
go
exec testCrash$test
go
Msg 209, Level 16, State 1, Procedure testCrash$test, Line 4
Ambiguous column name 'value'.
Msg 266, Level 16, State 2, Procedure testCrash$test, Line 4
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
go
some errors are transaction aborting and can't be
handled by any sql server error handling mechanism
|||This doesn't abort the transaction. That is what the error message is stating :) Run the batch and check the @.@.trancount. It will be 1, not 0.
It aborts the procedure only (even with try...catch in 2005, it busts out of the try...catch too!)
|||i'm using SQL SERVER 2000. and if you see message it says Transaction count 1, in .net quick watch error number 209. but immediatly after that if you goto query analyzer and try to rollback transaction it says NO transaction found.|||How do you verify if there is an open transaction when you come into QA?
Do you:
select @.@.trancount
or
dbcc opentran(myDbName)
/Kenneth
You cannot rollback a transaction in a different session. You will have to kill the connection or do the rollback on the connection where the error occurred. If you run the code I gave you in QA it will be in a transaction.
The best cure for this issue is to be careful with your source control. This really should never happen in a production system, and should be corrected immediately if you happen to find the issue in development.
|||if there is any error (which can stop the execution of T-SQL) within in a transaction then the transaction will be rolled back automaticall before the execution is going to abort.Error 209 Ambiguous column ..Transaction count after EXECUTE indicates .....
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
here is my stored proc
CREATE PROCEDURE dbo.ExportFile
@.intMonth INT,
@.intYear INT,
@.dtFirstDayOfMonth DATETIME
AS
BEGIN
BEGIN TRANSACTION
/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @.dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @.dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @.@.ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT
IF @.@.ERROR<>0
GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
END
ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO
The procedure stops immediately after an error like this. In fact, 2005's try..catch will not even stop it. It is pretty rare, and I am guessing that you modified one of the objects after creating the procedure:
create table testCrash
(
testCrashId int,
value varchar(10)
)
create table testCrash2
(
testCrash2Id int
)
go
create procedure testCrash$test
as
begin tran
select value
from testCrash
cross join testCrash2
rollback tran
go
exec testCrash$test
go
alter table testCrash2
add value varchar(10)
go
exec testCrash$test
go
Msg 209, Level 16, State 1, Procedure testCrash$test, Line 4
Ambiguous column name 'value'.
Msg 266, Level 16, State 2, Procedure testCrash$test, Line 4
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
go
some errors are transaction aborting and can't be
handled by any sql server error handling mechanism
|||This doesn't abort the transaction. That is what the error message is stating :) Run the batch and check the @.@.trancount. It will be 1, not 0.
It aborts the procedure only (even with try...catch in 2005, it busts out of the try...catch too!)
|||i'm using SQL SERVER 2000. and if you see message it says Transaction count 1, in .net quick watch error number 209. but immediatly after that if you goto query analyzer and try to rollback transaction it says NO transaction found.|||How do you verify if there is an open transaction when you come into QA?
Do you:
select @.@.trancount
or
dbcc opentran(myDbName)
/Kenneth
You cannot rollback a transaction in a different session. You will have to kill the connection or do the rollback on the connection where the error occurred. If you run the code I gave you in QA it will be in a transaction.
The best cure for this issue is to be careful with your source control. This really should never happen in a production system, and should be corrected immediately if you happen to find the issue in development.
|||if there is any error (which can stop the execution of T-SQL) within in a transaction then the transaction will be rolled back automaticall before the execution is going to abort.error 207 when trying to add a new job
"error sqlstate: 42s33"
error 207: invalid column name 'category_id'
invalid column name 'netsend_address'
invalid column name 'last_netsend_date'
invalid column name 'last_netsend_time.
Hi,
Did you upgraded your SQL server from older version. If yes then the MSDB
upgrade was not done fully. See the below old post for the step by step
solution.
http://groups.google.com/groups?hl=e...lm=0TDE0%23ZdB
HA.187%40cppssbbsa01.microsoft.com
Thanks
Hari
MCDBA
"ed" <ed@.discussions.microsoft.com> wrote in message
news:99EECA57-E7CC-4F36-A248-F97616029E50@.microsoft.com...
> when trying to add a new job via sql agent server, receiving a message:
> "error sqlstate: 42s33"
> error 207: invalid column name 'category_id'
> invalid column name 'netsend_address'
> invalid column name 'last_netsend_date'
> invalid column name 'last_netsend_time.
|||Hi
-As per SQL Server Books Online "This error (error 207) occurs when a column
referenced in a Transact-SQL statement was not found in any table specified
in
the FROM clause of the query.
-Table msdb..sysoperators didn't have the following columns 'category_id', '
netsend_address',' last_netsend_date',' last_netsend_time'
Looks like some problem with MSDB, try and rebuild as suggested in previous
post.
HTH
Regards
Sadeesh
This posting is provided AS IS with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OBuR4L6cEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Did you upgraded your SQL server from older version. If yes then the MSDB
> upgrade was not done fully. See the below old post for the step by step
> solution.
>
> http://groups.google.com/groups?hl=e...lm=0TDE0%23ZdB
> HA.187%40cppssbbsa01.microsoft.com
> Thanks
> Hari
> MCDBA
> "ed" <ed@.discussions.microsoft.com> wrote in message
> news:99EECA57-E7CC-4F36-A248-F97616029E50@.microsoft.com...
>
error 207 when trying to add a new job
"error sqlstate: 42s33"
error 207: invalid column name 'category_id'
invalid column name 'netsend_address'
invalid column name 'last_netsend_date'
invalid column name 'last_netsend_time.Hi,
Did you upgraded your SQL server from older version. If yes then the MSDB
upgrade was not done fully. See the below old post for the step by step
solution.
http://groups.google.com/groups?hl=...elm=0TDE0%23ZdB
HA.187%40cppssbbsa01.microsoft.com
Thanks
Hari
MCDBA
"ed" <ed@.discussions.microsoft.com> wrote in message
news:99EECA57-E7CC-4F36-A248-F97616029E50@.microsoft.com...
> when trying to add a new job via sql agent server, receiving a message:
> "error sqlstate: 42s33"
> error 207: invalid column name 'category_id'
> invalid column name 'netsend_address'
> invalid column name 'last_netsend_date'
> invalid column name 'last_netsend_time.|||Hi
-As per SQL Server Books Online "This error (error 207) occurs when a column
referenced in a Transact-SQL statement was not found in any table specified
in
the FROM clause of the query.
-Table msdb..sysoperators didn't have the following columns 'category_id', '
netsend_address',' last_netsend_date',' last_netsend_time'
Looks like some problem with MSDB, try and rebuild as suggested in previous
post.
HTH
Regards
Sadeesh
--
This posting is provided AS IS with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OBuR4L6cEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Did you upgraded your SQL server from older version. If yes then the MSDB
> upgrade was not done fully. See the below old post for the step by step
> solution.
>
> [url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=0TDE0%23ZdB[/ur
l]
> HA.187%40cppssbbsa01.microsoft.com
> Thanks
> Hari
> MCDBA
> "ed" <ed@.discussions.microsoft.com> wrote in message
> news:99EECA57-E7CC-4F36-A248-F97616029E50@.microsoft.com...
>|||hi,
i didn't upgrage but i restored msdb from backup and it was ok.
thanks.
"Hari Prasad" wrote:
> Hi,
> Did you upgraded your SQL server from older version. If yes then the MSDB
> upgrade was not done fully. See the below old post for the step by step
> solution.
>
> [url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=0TDE0%23ZdB[/ur
l]
> HA.187%40cppssbbsa01.microsoft.com
> Thanks
> Hari
> MCDBA
> "ed" <ed@.discussions.microsoft.com> wrote in message
> news:99EECA57-E7CC-4F36-A248-F97616029E50@.microsoft.com...
>
>|||yep some of the columns were missing from the system tables in msdb? resto
red msdb from an old backup.
thanks for your help.
"Sadeesh[MSFT]" wrote:
> Hi
> -As per SQL Server Books Online "This error (error 207) occurs when a colu
mn
> referenced in a Transact-SQL statement was not found in any table specifie
d
> in
> the FROM clause of the query.
> -Table msdb..sysoperators didn't have the following columns 'category_id',
'
> netsend_address',' last_netsend_date',' last_netsend_time'
> Looks like some problem with MSDB, try and rebuild as suggested in previou
s
> post.
> HTH
> Regards
> Sadeesh
> --
> This posting is provided AS IS with no warranties, and confers no rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OBuR4L6cEHA.3512@.TK2MSFTNGP12.phx.gbl...
>
>
Error 20068, Article Column Max 256
chokes when creating replication with error 20068, stating that the maximum
number of columns allowed for an article is 256.
The workarounds I am aware of involve creating two tables and using views to
abstract this change from the front end application. There is no way my
client will pay for a change as drastic as this with the only benifit is that
replication will now be allowed to work.
I have two questions:
a. Is there a better workaround?
b. Does SQL2005 solve this issue (I might be better able to sell my client
on an upgrade)?
partitioning the table as you describe is the standard workaround in SQL
Serever 2000.
In SQL Server 2005, for merge the max no of columns is 246. For
transactional and snapshot it is 1000 (995 with Oracle).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the application using this table uses a stored procedure to make the
updates you may be able to replicate the execution of the stored procedure -
however there is the same procedure limit. Another option is to use a
trigger to write to two "audit" tables and replicate these audit tables to
the subscriber where they in turn assemble the final row.
Hilary Cotter
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
"dtaylo75" <dtaylo75@.discussions.microsoft.com> wrote in message
news:EE67681F-C2FE-489B-88B1-1A69F719DE6D@.microsoft.com...
> I'm trying to replicate a table that now has more than 256 columns.
> SQL2000
> chokes when creating replication with error 20068, stating that the
> maximum
> number of columns allowed for an article is 256.
> The workarounds I am aware of involve creating two tables and using views
> to
> abstract this change from the front end application. There is no way my
> client will pay for a change as drastic as this with the only benifit is
> that
> replication will now be allowed to work.
> I have two questions:
> a. Is there a better workaround?
> b. Does SQL2005 solve this issue (I might be better able to sell my client
> on an upgrade)?