Problem With LookUp

Iam trying to make a look up of 'ColumnA' with 'columnB' of another table .But 'columnB' has some duplicate vlaues..so lookup transmition is giving an error.

Is there any solution for this

Thanks
Niru
Findout out the solution...using Sqlquery as OLEDB Source instead of Table.This solved the thing.

Thanks
Niru
sql

Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

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 Linked Server

Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
Lalit
I posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit

Problem with Linked Server

Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
LalitI posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit

problem with like [^]

SET NOCOUNT ON
declare @.tab table(vc varchar(2000))
insert into @.tab select '10.1'
insert into @.tab select '10.1.1'
insert into @.tab select '10.1.2'
insert into @.tab select '10.1.1.1'
insert into @.tab select '10.1.1.1.1'
The problem is that i need to select only 10.1.1 and 10.1.2 when i put input
as 10.1
If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
I tried this but not working
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'try this
SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"aneeshattingal" wrote:

> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>
>|||aneeshattingal wrote:
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
Drop the '%' from your query:
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]'|||Sorry.. that won't work for 10.1.12.. Wrong solution.
You will have to do it the long way :)
SELECT * FROM @.Tab WHERE
vc not like '10.1.%[.]%'
and vc like '10.1.%'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> try this
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "aneeshattingal" wrote:
>|||Thank guys , both worked ..
"aneeshattingal" <aneeshattingal@.hotmail.com> wrote in message
news:u0zMeYkjGHA.3408@.TK2MSFTNGP05.phx.gbl...
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put
> input as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>

Problem with LEFT JOIN... please help!

Please help,

below is my problem. Let's say I have 2 tables, a Products table and a
Colors table that go as follow:

Table Products

prodID Name
1 shirt
2 tshirt
3 pants
4 jeans

and

Table Colors

prodID Colors
1 Blue
1 Red
2 Blue
3 Black
3 White
4 Blue

I want to find out all the products that come in Blue, and if not I
want to have the color empty. The result I want from my Query / SQL
Statement is:

prodID Colors Name...

1 Blue
2 Blue
3
4 Blue

What should my SQL statement/Query be like?

I tried:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue

Notice that prodID 2 doesn't show up but I want to return all prodIDs
whether or not they have a color Blue.

Please help...

Thanks[posted and mailed, please reply in news]

Allan (proflicker@.hotmail.com) writes:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> where Colors.Colors = "blue"

When you say:

FROM a LEFT JOIN b on ...

You are, concpetually, constructing a table. Then you apply a WHERE
clause to filter out rows from that table.

Thus for

Products Left Join Colors on Product.ProdID = Colors.ProdID

You get a table with data in all columns for Products, but where
there is no matching row in Colors, you get NULL.

Then you apply a WHERE clause to this, but then you filter all those
NULL rows, because NULL is not equal to "blue".

The remedy is to move the condition to the ON clause:

Products Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Color = "blue"

Now the condition on Colors becomes part of that conceptual table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 7 Jun 2004 15:26:39 -0700, Allan wrote:

>Please help,
>below is my problem. Let's say I have 2 tables, a Products table and a
>Colors table that go as follow:
>Table Products
>prodID Name
>1 shirt
>2 tshirt
>3 pants
>4 jeans
>and
>Table Colors
>prodID Colors
>1 Blue
>1 Red
>2 Blue
>3 Black
>3 White
>4 Blue
>
>I want to find out all the products that come in Blue, and if not I
>want to have the color empty. The result I want from my Query / SQL
>Statement is:
>prodID Colors Name...
>1 Blue
>2 Blue
>3
>4 Blue
>What should my SQL statement/Query be like?
>I tried:
>Select Product.ProdID, Colors.Colors
>From
>Products
>Left Join Colors
>on Product.ProdID = Colors.ProdID
>where Colors.Colors = "blue"
>and this is what I get:
>prodID Colors
>1 Blue
>2 Blue
>4 Blue
>
>Notice that prodID 2 doesn't show up but I want to return all prodIDs
>whether or not they have a color Blue.
>Please help...
>Thanks

Hi Allan,

Try:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Colors = 'blue'

(untested)

Note: Changed "where" to "and" and also changed double-quotes to
single-quotes (single quotes are the standard string delimiter for SQL, as
defined by the ANSI standard. Double quotes are, depending on the setting
of some option, still supported in SQL Server 2000 for backward
compatibility)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the quick reply...

I had already tried that and I get an error message saying:

JOIN expression not supported

Any other suggestions?

Gad

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> >Please help,
> >below is my problem. Let's say I have 2 tables, a Products table and a
> >Colors table that go as follow:
> >Table Products
> >prodID Name
> >1 shirt
> >2 tshirt
> >3 pants
> >4 jeans
> >and
> >Table Colors
> >prodID Colors
> >1 Blue
> >1 Red
> >2 Blue
> >3 Black
> >3 White
> >4 Blue
> >I want to find out all the products that come in Blue, and if not I
> >want to have the color empty. The result I want from my Query / SQL
> >Statement is:
> >prodID Colors Name...
> >1 Blue
> >2 Blue
> >3
> >4 Blue
> >What should my SQL statement/Query be like?
> >I tried:
> >Select Product.ProdID, Colors.Colors
> >From
> >Products
> >Left Join Colors
> >on Product.ProdID = Colors.ProdID
> >where Colors.Colors = "blue"
> >and this is what I get:
> >prodID Colors
> >1 Blue
> >2 Blue
> >4 Blue
> >Notice that prodID 2 doesn't show up but I want to return all prodIDs
> >whether or not they have a color Blue.
> >Please help...
> >Thanks
> Hi Allan,
> Try:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> and Colors.Colors = 'blue'
> (untested)
> Note: Changed "where" to "and" and also changed double-quotes to
> single-quotes (single quotes are the standard string delimiter for SQL, as
> defined by the ANSI standard. Double quotes are, depending on the setting
> of some option, still supported in SQL Server 2000 for backward
> compatibility)
> Best, Hugo|||"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0406071919.319fa873@.posting.google.c om...
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported
> Any other suggestions?

The name of your products table is "Products", right?
You have "Product" in the join condition of your query.

SELECT P.prodID, C.colors
FROM Products AS P
LEFT OUTER JOIN
Colors AS C
P.prodID = C.prodID AND
C.colors = 'blue'

--
JAG

> Gad
>
> Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> > On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> > >Please help,
> > > >below is my problem. Let's say I have 2 tables, a Products table and a
> > >Colors table that go as follow:
> > > >Table Products
> > > >prodID Name
> > >1 shirt
> > >2 tshirt
> > >3 pants
> > >4 jeans
> > > >and
> > > >Table Colors
> > > >prodID Colors
> > >1 Blue
> > >1 Red
> > >2 Blue
> > >3 Black
> > >3 White
> > >4 Blue
> > > > >I want to find out all the products that come in Blue, and if not I
> > >want to have the color empty. The result I want from my Query / SQL
> > >Statement is:
> > > >prodID Colors Name...
> > > >1 Blue
> > >2 Blue
> > >3
> > >4 Blue
> > > >What should my SQL statement/Query be like?
> > > >I tried:
> > > >Select Product.ProdID, Colors.Colors
> > >From
> > >Products
> > >Left Join Colors
> > >on Product.ProdID = Colors.ProdID
> > >where Colors.Colors = "blue"
> > > >and this is what I get:
> > > >prodID Colors
> > > >1 Blue
> > >2 Blue
> > >4 Blue
> > > > >Notice that prodID 2 doesn't show up but I want to return all prodIDs
> > >whether or not they have a color Blue.
> > > >Please help...
> > > >Thanks
> > Hi Allan,
> > Try:
> > Select Product.ProdID, Colors.Colors
> > From
> > Products
> > Left Join Colors
> > on Product.ProdID = Colors.ProdID
> > and Colors.Colors = 'blue'
> > (untested)
> > Note: Changed "where" to "and" and also changed double-quotes to
> > single-quotes (single quotes are the standard string delimiter for SQL, as
> > defined by the ANSI standard. Double quotes are, depending on the setting
> > of some option, still supported in SQL Server 2000 for backward
> > compatibility)
> > Best, Hugo|||Allan (proflicker@.hotmail.com) writes:
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported

It is at this time I find it appropriate to ask which version of SQL
Server you are using. Or rather which DBMS you are using. That message
does not sound familliar at all. But it could also be that you are issueing
the query through some unknown tool which has its own quirks. Did you
try running in Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>Let's say I have 2 tables, a Products table and a Colors table that
go as follow: <<

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

2) Next, can you explain why color is NOT AN ATTRIBUTE of a product??
In your reality and therefore, your data model, it floats around with
weight, height, or whatever physcial attributes that go with being a
color?

If you had done this right, wouldn't it look more like this?

CREATE Table Products
(sku INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(25) NOT NULL,
color CHAR(5) NOT NULL
CHECK(color IN (..)),
..);

>> I want to find out all the products that come in Blue, and if not I
want to have the color empty. <<

Weird, if your inventory is of any size at all; How many non-blue
things do you think that J.C. Penney's has, as compared to blue
things? But you can try this:

SELECT I1.sku, 'Blue'
FROM Inventory AS I1
WHERE color = 'Blue'
UNION ALL
SELECT I1.sku, 'Not Blue'
FROM Inventory AS I1
WHERE color <> 'Blue';

If the Colors table were actually not an attribute in a properly
designed schema, then you'd use an outer join.

Problem with keeptogether

In a report I have a table with 3 detail lines. The property 'keeptogether'
is set to true. However, if I call the report in IE and then export it to
pdf, the detail region can be splitted: The first detail line is at the end
of a page and the next 2 lines (of the same record) are on the next page. I
want that the 3 lines come together on the same page (of course with the
exception that the 3 lines together are longer than one page). How must this
be solved?On Sep 27, 3:20 am, "Lorenz Ingold" <l.ing...@.winvs.ch> wrote:
> In a report I have a table with 3 detail lines. The property 'keeptogether'
> is set to true. However, if I call the report in IE and then export it to
> pdf, the detail region can be splitted: The first detail line is at the end
> of a page and the next 2 lines (of the same record) are on the next page. I
> want that the 3 lines come together on the same page (of course with the
> exception that the 3 lines together are longer than one page). How must this
> be solved?
You might need to select 'Fit table on one page if possible' as part
of the table's Properties (if you haven't already). Also, you might
try putting the table control into a rectangle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

problem with japanese character 俱

Hi All,

Create a table , and put in some row 子ど and 俱

and fire a query something like SELECT * FROM mytable WHERE (myColumn = '子ど')

it gives me right result.

But if i fire

SELECT * FROM mytable WHERE (myColumn = '俱')

It does not return any result for the same even if myColumn has '俱'.

Surprisnly if i fire query like "SELECT * FROM mytable" it

correctly dispalys 俱.

What's the reason for the same ? Why does it is not able to search me on this japanese character(俱).Collation is Japanese_Unicode_CI_AS

Regards,

Sunil

Hi Sunil,

you wil have to indicate that the string is unicode in your query:

SELECT * FROM mytable WHERE (myColumn = N'俱')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens for your quick response.

I have one query why it was working without N in 子ど but not in 俱

Regards,
Sunil

problem with japanese character 俱

Hi All,
Create a table , and put in some row Xど and 俱
and fire a query something like SELECT * FROM mytable WHERE
(myColumn = 'Xど')
it gives me right result.
But if i fire
SELECT * FROM mytable WHERE (myColumn = '俱')
It does not return any result for the same even if myColumn has '俱'.
Surprisnly if i fire query like "SELECT * FROM mytable" it
correctly dispalys 俱.
What's the reason for the same ? Why does it is not able to search me on
this japanese character(俱).Collation is Japanese_Unicode_CI_AS
Regards,
Sunil
Please don't multi-post. If you really need to post in multiple
newsgroups, you should cross-post (post only one time, but with both
newsgroups in the To: field).
I've responded to this question in the
microsoft.public.sqlserver.programming newsgroup.
Razvan

Problem with isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

Problem With Insert Using SQLDataSource

I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error:

========== Error ===========

Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query.

============================

The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows:

======== Code ============

Dim guidMemberidAs Guid =CType(user.ProviderUserKey, Guid)

Session("currmember") = guidMemberid

....

Session("currinterest") =CType(item.FindControl("CheckBox1"), CheckBox).Text

==========================

Any ideas of what I am doing wrong here?

========= SQLDataSource ==========

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:GoodSamSiteDB %>"

DeleteCommand="DELETE FROM [gs_MemberInterests] WHERE [memberid] = @.memberid"

InsertCommand="INSERT INTO [gs_MemberInterests] ([memberid], [Interest]) VALUES (@.memberid, @.Interest)"

SelectCommand="SELECT memberid, Interest FROM gs_MemberInterests WHERE (memberid = @.memberid) AND (Interest = @.interest)">

<DeleteParameters>

<asp:ParameterName="memberid"Type="Object"/>

</DeleteParameters>

<SelectParameters>

<asp:SessionParameterName="memberid"SessionField="currmember"Type="Object"/>

<asp:SessionParameterName="interest"SessionField="currinterest"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="memberid"Type="Object"/>

<asp:ParameterName="Interest"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

=============================

Type="Object"|||

Motley wrote:

Type="Object"

Thanks. I assume you are referring to the fact that the "Type='Object'" has to be removed. I just found a reference to that bug. That cleared the problem.

sql

Problem with INSERT INTO Temp Table on SQL 2000 using Linked Server to SQL 2005

I have a SQL Server 2000 linked to a SQL Server 2005 and I am attempting populate a temporary table on the 2000 server using INSERT INTO by executing a stored procedure on the 2005 server. If I just execute the stored procedure, the data is returened with no issues. If I try to Insert the data into the temporary table the process hangs and times out.

This returns the data with no issues:

EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1

This hangs and has to be killed or times out:

CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable

EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1
SELECT * FROM #TempTable

I tried adding SET REMOTE_PROC_TRANSACTIONS OFF as suggested in an earlier post, but this had no effect.

You need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

|||

Thanks for your reply.

MSDTC service is already running on both servers.

problem with insert into in scheduled job pleas help ASAP

Hello MSDN

I am using SQL 2005 and trying to INSTERT data in to a table

When I am using my command from SQL query windows it works fine,

INSERT INTO "tbl.FTPuploads" ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

But when I am trying to put that command in to a scheduled job I get this error

Executed as user: GW\fmnlasa. Incorrect syntax near 'tbl.FTPuploads'. [SQLSTATE 42000] (Error 102).The step failed.

I have changed the command to this, I have removed the quotes from the table name.

INSERT INTO tbl.FTPuploads ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And now I get this error

Executed as user: GW\fmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).The step failed.

When you execute from the query, it will use your credential to connect the remote server. But the scheduled query might use the ServiceAccount. Check which service account used in the current context & give the required permission on the remote server. Or change the service account context to NT Credential.|||

Bascially the issues is of Security Context under which the Job run. Check these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012759&SiteID=1

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/44111/Access-to-the-remote-server-is-denied-because-the-current-security

Madhu

|||

Thank you, for such a quick reply

I was using the same account in Job to as I was using in Query window

I am not sure but I think that the problem was in my table and column names

I have created new table with the name tblFTPuploads the old one was tbl.FTPuploads

After that I got erros for may column names so I have changed the first column name to FTPFileNames the old one was FTPfile_name

And the last step I did was to remove the quotes from the table and column names.

So the end script is like this

INSERT INTO tblFTPuploads (FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME)

SELECT FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And it is working now no problems.

Thanks again for a quick reply

Best regards,

Artavazd ASLANYAN

Network Administrator

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
......
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
Dan
Dan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com
|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen
|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com
|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheen

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
.....
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...
)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
DanDan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,..
.)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheensql

Problem with Index Tuning Wizard

When running the Index Tuning Wizard (ITW) on our SQL Server we got a
error message : 'Could not complete cursor operation because the table
schema changed after the cursor was declared'
The ITW was used only to advice new indexes. I know that it creates
hypothetical indexes in the sysindexes table, but what are the changes
in the user-tables ?
How do you use the ITW without interfering with the real
schema-definitions ?
thanks,
RobbertHi,
Before using the ITW, you should get a load of the queries
you are running. You can capture the events in profiler
and then use that load file for the ITW to use.
hth
DeeJay
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>|||My theory on this is while you were running the ITW
someone in your tream made a change to the structure a
table, which caused it to get an error.
I have run ITW a number of times using our Production
Database, with no problems.
Peter
"We are all worms. But I believe that I am a glow-worm."
Winston Churchill
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>

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.
>
>