An odd quandry.

So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.

The three fields in question are identical.

Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value

And these three fields are populated from a substring of the filenames
which are generated in another table.

Filenametbl.pickno

here is the rub.

If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)

should return 200 records.
It returns 0 records.

So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)

returns 200 records in this scenario,

this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)

I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))

Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
WHERE ... NOT IN.

select
*
from
Filenametbl f
where not exists (select * from
document_link_staging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_storage dls
where dls.key2_value = f.pickno)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.

The three fields in question are identical.

Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value

And these three fields are populated from a substring of the filenames
which are generated in another table.

Filenametbl.pickno

here is the rub.

If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)

should return 200 records.
It returns 0 records.

So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)

returns 200 records in this scenario,

this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)

I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))

Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.|||Hey, whoa, that works.
You're the man Tom.

So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?

Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.|||(KyussWren@.gmail.com) writes:
> Hey, whoa, that works.
> You're the man Tom.
> So just use EXISTS when there are nulls in the select list?
> Kinda like coalesce, but for subqueries?

EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:

x IN (1, 2, 3 null)

... means:

x = 1 or x = 2 or x = 3 or x = null

So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.

Now consider this:

x NOT IN (1, 2, 3 null)

... means:

x <> 1 and x <> 2 and x <> 3 and x <> null

Google de Morgan's Law.

What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146665188.587472.63170@.v46g2000cwv.googlegro ups.com...
Hey, whoa, that works.
You're the man Tom.

So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?

Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead
> of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.

Ambiguous column name on Insert

I am getting an Ambiguous column name on this insert for all fields.
Insert Into Agentlocatordata ([Agent],
[AgentLoc],
[AppointmentDate],
[BusinessName],
[BusinessPhone1],
[BusinessPhone2],
[BusinessType],
[CommercialProp],
[Contact],
[ContactEmail],
[CPRepGotit],
[Crossstreet],
[DateCreated],
[DateLastChanged],
[DirectConnect],
[Direction1],
[Direction2],
[Direction3],
[DisplayName],
[E_Mail],
[FaxByEmail],
[FaxPhone],
[License],
[MailAddress1],
[MailAddress2],
[MailCity],
[MailName],
[MailState],
[MailZip],
[MKTGRep],
[NoOfAgents],
[OfficeAddress1],
[OfficeAddress2],
[OfficeCity],
[OfficeState],
[OfficeZip],
[OfficeZipp4],
[Owner],
[Profile],
[RepGotit],
[ShortName],
[ShowPage],
[Specialty],
[StarfishGroup],
[State],
[TaxPayerShortName],
[TaxType],
[Unit],
[Website],
[Zone])
Select Distinct
[Agents]
,[AgentLoc]
,[AppointmentDate]
,[BusinessName]
,[BusinessPhone1]
,[BusinessPhone2]
,[BusinessType]
,[CommercialProp]
,[Contact]
,[ContactEmail]
,[CPRepGotit]
,[Crossstreet]
,[DateCreated]
,[DateLastChanged]
,[DirectConnect]
,[Direction1]
,[Direction2]
,[Direction3]
,[DisplayName]
,[EMail]
,[FaxByEmail]
,[FaxPhone]
,[License]
,[MailAddress1]
,[MailAddress2]
,[MailCity]
,[MailName]
,[MailState]
,[MailZip]
,[MKTGRep]
,[NoOfAgents]
,[OfficeAddress1]
,[OfficeAddress2]
,[OfficeCity]
,[OfficeState]
,[OfficeZip]
,[OfficeZipp4]
,[Owner]
,[Profile]
,[RepGotit]
,[ShortName]
,[ShowPage]
,[Specialty]
,[StarfishGroup]
,[State]
,[TaxPayerShortName]
,[TaxType]
,[Unit]
,[Website]
,[Zone]
from Agent full outer
join Agent0315 on Agent.Agent = Agent0315.agents
where Agent.agent = Agent0315.agents
ANy Help would be great!Lontae Jones wrote:
> I am getting an Ambiguous column name on this insert for all fields.
> Insert Into Agentlocatordata ([Agent],
> [AgentLoc],
> [AppointmentDate],
> [BusinessName],
> [BusinessPhone1],
> [BusinessPhone2],
> [BusinessType],
> [CommercialProp],
> [Contact],
> [ContactEmail],
> [CPRepGotit],
> [Crossstreet],
> [DateCreated],
> [DateLastChanged],
> [DirectConnect],
> [Direction1],
> [Direction2],
> [Direction3],
> [DisplayName],
> [E_Mail],
> [FaxByEmail],
> [FaxPhone],
> [License],
> [MailAddress1],
> [MailAddress2],
> [MailCity],
> [MailName],
> [MailState],
> [MailZip],
> [MKTGRep],
> [NoOfAgents],
> [OfficeAddress1],
> [OfficeAddress2],
> [OfficeCity],
> [OfficeState],
> [OfficeZip],
> [OfficeZipp4],
> [Owner],
> [Profile],
> [RepGotit],
> [ShortName],
> [ShowPage],
> [Specialty],
> [StarfishGroup],
> [State],
> [TaxPayerShortName],
> [TaxType],
> [Unit],
> [Website],
> [Zone])
> Select Distinct
> [Agents]
> ,[AgentLoc]
> ,[AppointmentDate]
> ,[BusinessName]
> ,[BusinessPhone1]
> ,[BusinessPhone2]
> ,[BusinessType]
> ,[CommercialProp]
> ,[Contact]
> ,[ContactEmail]
> ,[CPRepGotit]
> ,[Crossstreet]
> ,[DateCreated]
> ,[DateLastChanged]
> ,[DirectConnect]
> ,[Direction1]
> ,[Direction2]
> ,[Direction3]
> ,[DisplayName]
> ,[EMail]
> ,[FaxByEmail]
> ,[FaxPhone]
> ,[License]
> ,[MailAddress1]
> ,[MailAddress2]
> ,[MailCity]
> ,[MailName]
> ,[MailState]
> ,[MailZip]
> ,[MKTGRep]
> ,[NoOfAgents]
> ,[OfficeAddress1]
> ,[OfficeAddress2]
> ,[OfficeCity]
> ,[OfficeState]
> ,[OfficeZip]
> ,[OfficeZipp4]
> ,[Owner]
> ,[Profile]
> ,[RepGotit]
> ,[ShortName]
> ,[ShowPage]
> ,[Specialty]
> ,[StarfishGroup]
> ,[State]
> ,[TaxPayerShortName]
> ,[TaxType]
> ,[Unit]
> ,[Website]
> ,[Zone]
> from Agent full outer
> join Agent0315 on Agent.Agent = Agent0315.agents
> where Agent.agent = Agent0315.agents
> ANy Help would be great!
You didn't qualify any of your columns in the SELECT portion. If you use
a join, you need to qualify your columns as a matter of good practice.
You probably have the same column name in the Agent and Agent0315
tables.
David Gugick
Imceda Software
www.imceda.com|||My select follows my insert but I am still getting ambiguos errors on all
columns.
Insert Into Agentlocatordata ([Agent],
[AgentLoc],
[AppointmentDate],
[BusinessName],
[BusinessPhone1],
[BusinessPhone2],
[BusinessType],
[CommercialProp],
[Contact],
[ContactEmail],
[CPRepGotit],
[Crossstreet],
[DateCreated],
[DateLastChanged],
[DirectConnect],
[Direction1],
[Direction2],
[Direction3],
[DisplayName],
[E_Mail],
[FaxByEmail],
[FaxPhone],
[License],
[MailAddress1],
[MailAddress2],
[MailCity],
[MailName],
[MailState],
[MailZip],
[MKTGRep],
[NoOfAgents],
[OfficeAddress1],
[OfficeAddress2],
[OfficeCity],
[OfficeState],
[OfficeZip],
[OfficeZipp4],
[Owner],
[Profile],
[RepGotit],
[ShortName],
[ShowPage],
[Specialty],
[StarfishGroup],
[State],
[TaxPayerShortName],
[TaxType],
[Unit],
[Website],
[Zone])
Select Distinct [Agents] ,[AgentLoc],[AppointmentDate]
,[BusinessName]
,[BusinessPhone1]
,[BusinessPhone2]
,[BusinessType]
,[CommercialProp]
,[Contact]
,[ContactEmail]
,[CPRepGotit]
,[Crossstreet]
,[DateCreated]
,[DateLastChanged]
,[DirectConnect]
,[Direction1]
,[Direction2]
,[Direction3]
,[DisplayName]
,[EMail]
,[FaxByEmail]
,[FaxPhone]
,[License]
,[MailAddress1]
,[MailAddress2]
,[MailCity]
,[MailName]
,[MailState]
,[MailZip]
,[MKTGRep]
,[NoOfAgents]
,[OfficeAddress1]
,[OfficeAddress2]
,[OfficeCity]
,[OfficeState]
,[OfficeZip]
,[OfficeZipp4]
,[Owner]
,[Profile]
,[RepGotit]
,[ShortName]
,[ShowPage]
,[Specialty]
,[StarfishGroup]
,[State]
,[TaxPayerShortName]
,[TaxType]
,[Unit]
,[Website]
,[Zone]
from Agent full outer
join Agent0315 on Agent.Agent = Agent0315.agents
where Agent.agent = Agent0315.agents
"David Gugick" wrote:

> Lontae Jones wrote:
> You didn't qualify any of your columns in the SELECT portion. If you use
> a join, you need to qualify your columns as a matter of good practice.
> You probably have the same column name in the Agent and Agent0315
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||For each column that is in both the Agent and Agent0315 tables,
you must specify whether you want Agent.theColumn or Agent0315.theColumn.
Your column names are ambiguous because they appear in two different tables
in the FROM clause, but you don't say which you want in your result.
SK
Lontae Jones wrote:
>My select follows my insert but I am still getting ambiguos errors on all
>columns.
>Insert Into Agentlocatordata ([Agent],
> [AgentLoc],
> [AppointmentDate],
> [BusinessName],
> [BusinessPhone1],
> [BusinessPhone2],
> [BusinessType],
> [CommercialProp],
> [Contact],
> [ContactEmail],
> [CPRepGotit],
> [Crossstreet],
> [DateCreated],
> [DateLastChanged],
> [DirectConnect],
> [Direction1],
> [Direction2],
> [Direction3],
> [DisplayName],
> [E_Mail],
> [FaxByEmail],
> [FaxPhone],
> [License],
> [MailAddress1],
> [MailAddress2],
> [MailCity],
> [MailName],
> [MailState],
> [MailZip],
> [MKTGRep],
> [NoOfAgents],
> [OfficeAddress1],
> [OfficeAddress2],
> [OfficeCity],
> [OfficeState],
> [OfficeZip],
> [OfficeZipp4],
> [Owner],
> [Profile],
> [RepGotit],
> [ShortName],
> [ShowPage],
> [Specialty],
> [StarfishGroup],
> [State],
> [TaxPayerShortName],
> [TaxType],
> [Unit],
> [Website],
> [Zone])
>Select Distinct [Agents] ,[AgentLoc],[AppointmentDate]
> ,[BusinessName]
> ,[BusinessPhone1]
> ,[BusinessPhone2]
> ,[BusinessType]
> ,[CommercialProp]
> ,[Contact]
> ,[ContactEmail]
> ,[CPRepGotit]
> ,[Crossstreet]
> ,[DateCreated]
> ,[DateLastChanged]
> ,[DirectConnect]
> ,[Direction1]
> ,[Direction2]
> ,[Direction3]
> ,[DisplayName]
> ,[EMail]
> ,[FaxByEmail]
> ,[FaxPhone]
> ,[License]
> ,[MailAddress1]
> ,[MailAddress2]
> ,[MailCity]
> ,[MailName]
> ,[MailState]
> ,[MailZip]
> ,[MKTGRep]
> ,[NoOfAgents]
> ,[OfficeAddress1]
> ,[OfficeAddress2]
> ,[OfficeCity]
> ,[OfficeState]
> ,[OfficeZip]
> ,[OfficeZipp4]
> ,[Owner]
> ,[Profile]
> ,[RepGotit]
> ,[ShortName]
> ,[ShowPage]
> ,[Specialty]
> ,[StarfishGroup]
> ,[State]
> ,[TaxPayerShortName]
> ,[TaxType]
> ,[Unit]
> ,[Website]
> ,[Zone]
>from Agent full outer
>join Agent0315 on Agent.Agent = Agent0315.agents
>where Agent.agent = Agent0315.agents
>"David Gugick" wrote:
>
>

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.