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 '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 installing SQL Server

Hello To all
I have uninstalled SQL SEVER 2005 EXSPERS to install Visaul basic .Net
After i have installed it i can not reinstall SQL Server
i having the messege:" An instance with thesame name is already
installed on this computer To proceed with SQL server Setup rovide a
unique instance name"
and i have unistatted all outer SQL Componets from my Computer
and Deleted SQL Folder from Program File
how Can i Fix This Error ?
TNX in advance to allHave you seen this article?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<drshay@.gmail.comwrote in message
news:1152605096.924997.150780@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Hello To all
I have uninstalled SQL SEVER 2005 EXSPERS to install Visaul basic .Net
After i have installed it i can not reinstall SQL Server
i having the messege:" An instance with thesame name is already
installed on this computer To proceed with SQL server Setup rovide a
unique instance name"
and i have unistatted all outer SQL Componets from my Computer
and Deleted SQL Folder from Program File
how Can i Fix This Error ?
TNX in advance to all
>

|||drshay@.gmail.com wrote:

Quote:

Originally Posted by

Hello To all
I have uninstalled SQL SEVER 2005 EXSPERS to install Visaul basic .Net
After i have installed it i can not reinstall SQL Server
i having the messege:" An instance with thesame name is already
installed on this computer To proceed with SQL server Setup rovide a
unique instance name"
and i have unistatted all outer SQL Componets from my Computer
and Deleted SQL Folder from Program File
how Can i Fix This Error ?
TNX in advance to all

Hi,

I too encountered this problem. I completely uninstalled VB.Net and SQL
Server 2005. I also uninstalled the .net framework 1 & 2.

I then cleared out the registry of all items relating to any of these
products. I then re-installed SQL Server 2005 first and then VB.Net
after that.

I had no further problems

Problem with import data from excel format file.

Hi All

I have a huge excel format file wants to export to sql server
database. One of the field has combination of numeric and
alphanumeric. When I import the excel format to sql server database,
the numeric is successfully imported but the alphanumeric does not
import at all.

Does anyone know how to solve my problem?

I am using SQL 2k and the spreadsheet is excel 2003

Thanks

Rich

You may have to use Data Conversion to convert data suitable to your SQL data type.sql

Problem with IDENTITY column

Hi all

I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:

CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

When I create two new records, the field ID is assigned the next available value.

So far everything works fine.

THE PROBLEM:
If I insert a record with the following SQL statements:

SET IDENTITY_INSERT W_Person ON

INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)

SET IDENTITY_INSERT W_Person OFF

and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.

Please, can anybody help me on this one!

//Peterhi peterand,

the identity-seed is set to 8000 after your insert. so the next value will be 8001...
if you want to re-seed, take a look at bol "DBCC CHECKIDENT"

markus

Problem with handling dates

hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @.var_NewNextRunDate=(CAST(DATEPART(YYYY,@.DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@.DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@.DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@.DT) + @.var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@.DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@.DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@.DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.you should do all data/time manipulations on a date/time datatypes not chars.
if you are extracting a portion of an existing date/time which results in a char datatype, you should cast it back to date/time before you start manipulating it.|||Although this is an old thread I just thought I'd add what I did. I just used the dateadd function to resolve the issue. It worked just perfect.

Problem with Extended Stored Procedure calling an WebService...

Hi all!
I have a problem with a Extended Stored Procedure that calls a
WebService...I'm using SOAP to call the WebService and I'm using an
TokenManager that I have written myself...I have WSE 2.0 installed...
It works fine when I'm calling it from my PC, but when I call it from the
SQL test server which is a Windows 2003 server with SQL Server 2000
installed.
The message we get when run the Extended Stored Procedure is.
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
126(The specified module could not be found.).
Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
version but that didn't help...
Regards,
Tommy Selggren
Mandator Sverige AB
http://www.mandator.comUse DEPENDS.EXE to figure out which file(s) is/are missing. Download
DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
which hosts the XP
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a problem with a Extended Stored Procedure that calls a
> WebService...I'm using SOAP to call the WebService and I'm using an
> TokenManager that I have written myself...I have WSE 2.0 installed...
> It works fine when I'm calling it from my PC, but when I call it from the
> SQL test server which is a Windows 2003 server with SQL Server 2000
> installed.
> The message we get when run the Extended Stored Procedure is.
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
> 126(The specified module could not be found.).
> Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
> version but that didn't help...
> Regards,
> Tommy Selggren
> Mandator Sverige AB
> http://www.mandator.com
>
>|||Many thanks!
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eT2QgflGFHA.3092@.tk2msftngp13.phx.gbl...
> Use DEPENDS.EXE to figure out which file(s) is/are missing. Download
> DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
> which hosts the XP
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
> news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
the
Reason:
>

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

Hi

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006

Problem With Dataset()

Hello all

i m facing a problem "There is already an open DataReader associated with this Command which must be closed first."

what is this alll the another page like thispage is currently working properly

so what is this problem

pleasse help me

ashwani

hi Friend,

do check your code you might be using same datareader without closing it before .

|||

Hi,

There is already an open DataReader associated with this Command which must be closed first.

From the error message you provided, it seems that you are using DataReader in your application, right?

Generally, a datareader represents an open connection to the database. If you want to run 2 readers simultaneously, you need to use two separate connections.

Thanks.