Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?
Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>
|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:
>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribió en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>>Hi all:
>>I have following problem.
>>When I try to run an INSERT from Query Analyser to a table of a linked
>>server I get
>>a bug about a TEXT field of table:
>>INSERT is like this one:
>>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>>Bug is like this one:
>>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>>OLE DB [OLE/DB Provider 'SQLOLEDB'
>>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>>STATUS=DBSTATUS_S_DEFAULT],
>>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>>value.
>>Running same instruction locally there isn't any problem.
>>How can I solve this problem ?
>>
>>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribió en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
>> When I try to run an INSERT from Query Analyser to a table of a linked
>> server I get
>> a bug about a TEXT field of table:
>> INSERT is like this one:
>> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>> Bug is like this one:
>> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>> OLE DB [OLE/DB Provider 'SQLOLEDB'
>> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK],
>> [COLUMN_NAME=familia
>> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>> STATUS=DBSTATUS_S_DEFAULT],
>> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>> value.
>> Running same instruction locally there isn't any problem.
>> How can I solve this problem ?
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion
)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observa
ciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f
amilia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAUL
T],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observacio
nes'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcio
n)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observ
aciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=
familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAU
LT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaci
ones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripci
on)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'obser
vaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME
=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observac
iones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

problem with index and duplicates

Dariusz Tomon wrote:
> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields
-
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
It looks like you're attempting to define a primary key on your table.
Primary keys must be unique, and the field(s) that you're keying on have
some duplicate values.Dariusz Tomon,
Are you using Enterprise Manager to apply this change (add new columns)?
AMB
"Dariusz Tomon" wrote:

> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields
-
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
>|||Hi
I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields -
but during that I get the following error message:
'TB_KONTAKT' table
- Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is '1190914'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
That index is already build and I'm rather scared of destroying it.
How can I fix the problem?
Best Regards
Darek T.|||Dariusz Tomon wrote:
> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields
-
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
It looks like you're attempting to define a primary key on your table.
Primary keys must be unique, and the field(s) that you're keying on have
some duplicate values.|||Dariusz Tomon,
Are you using Enterprise Manager to apply this change (add new columns)?
AMB
"Dariusz Tomon" wrote:

> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields
-
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
>

problem with index and duplicates

Hi
I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields -
but during that I get the following error message:
'TB_KONTAKT' table
- Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is '1190914'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
That index is already build and I'm rather scared of destroying it.
How can I fix the problem?
Best Regards
Darek T.Dariusz Tomon wrote:
> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields -
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
It looks like you're attempting to define a primary key on your table.
Primary keys must be unique, and the field(s) that you're keying on have
some duplicate values.|||Dariusz Tomon,
Are you using Enterprise Manager to apply this change (add new columns)?
AMB
"Dariusz Tomon" wrote:
> Hi
> I'm trying to change structure of table 'TB_KONTAKT' to add a fiew fields -
> but during that I get the following error message:
> 'TB_KONTAKT' table
> - Unable to create index 'PK__TB_KONTAKT__04AFB25B'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '1190914'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> That index is already build and I'm rather scared of destroying it.
> How can I fix the problem?
> Best Regards
> Darek T.
>
>

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type = ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type => ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Problem with Hebrew characters

Hi,

I tried to transfer data from sqlserver2005 to oracle db.

For this, I have to convert some fields before.

I convert unicode data to nonunicode -

from DT_WSTR to DT_STR.

I began to do it with the wizard and the code page is 1252.

My problem is with the hebrew characters.

when there are hebrew characters in the field, it fails.

This is the error output:

"Text was truncated or one or more characters had no match in the target code page."

I tried to change the code page to 1255,

I changed "AlwaysUseDefaultCodePade" to True.

but I can't solve it.

Do you have any experience with it?

Thanks in advance.

Where did you change the code page to 1255? If it's in the Data Conversion transform and you still get the error, then the problem is likely that you have characters in your unicode string that can't be converted. In that case, you'll need to examine that string (try using the error output of the data conversion to get the string) and determine what characters are the cause and how you want to handle it (e.g. fix up or replace the string using another transform before inserting into Oracle). If you're not already using the Data Conversion transform, you could try reading the data in as DT_WSTR and then use the Data Conversion Transform to convert to 1255 to help narrow this problem down.

Another option to try is to do the string conversion outside of SSIS. For example, using a select statement with a cast in the source adapter that reads the data into IS already as DT_STR (the cast outside of IS may fail for the same reason which would also help you narrow this down).

|||

Thank you very much!

I used Data Conversion transform, I changed all of the columns to code page 1255.

In the destination I also set the default code page to 1255, and it works.

Problem with Group By

Hi there,

I am a novice SQL Server Programmer. I have got a task to select multiple fields from multiple tables...I have successfully used joins to get the result. but I have got 2 questions

1. Is there any way of combining (concatenating) three strings as one in the query using "AS"

2. The final result had to be GROUPED by one particular field I am using in select statement...But, for every field in select statement, it lets me run the query without error, only if the field is included Group By clause.

Is there any way to avoid it?

yes, you can concatenate multiple strings into a single field with as...

select (f1+ f2 + f3) as singleString

and you to include any fields in the query in the group by clause...just put the field you are concerned with first. -- jp

|||

hi jp

Thanks for your reply...

your first answer fetched me correct results but regarding the Group By, I still have the problem :(

|||when you use group by you have to include all the fields from the query statement in the group by clause...|||

thank you jp..............

Problem with Dates and OpenXML command

My data is being updated correctly for all fields except a date field.
The program is crashing when a date is encountered with OpenXML. Is
there a way to convert the date within SQL to handle this...I have tried
the CONVERT statement below, but it does little
UPDATE EducationSheetSchool
SET
EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
tart, 126),
*** Sent via Developersdex http://www.codecomments.com ***
Can you show a sample of the XML and the OPENXML syntax you're using?
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Paul Barnett" <barnettpaul@.gmail.com> wrote in message
news:eCtAwfM4FHA.1188@.TK2MSFTNGP12.phx.gbl...
>
> My data is being updated correctly for all fields except a date field.
> The program is crashing when a date is encountered with OpenXML. Is
> there a way to convert the date within SQL to handle this...I have tried
> the CONVERT statement below, but it does little
>
> UPDATE EducationSheetSchool
> SET
> EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
> EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
> EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
> EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
> EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
> EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
> tart, 126),
> *** Sent via Developersdex http://www.codecomments.com ***