Miracle, Bug or My Ignorance ??

select

'[Cust Code]' ColName
,RowId
from
Temp_Upload_Table
where

[cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)

The code above is giving an error

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

The [cust code] column is float .

The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...

Is this a miracle, a bug or is there something I need to know ?I can't seem to replicate the problem over here. Here is what I am using:

create table temp_upload_table
([cust code] float,
rowid int)

create table KNVV_View
(KUNNR varchar(10))

insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')

insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)

Am I missing something?|||dont know what is happening .. the code runs good on your tables ... gives an error on mine|||There was a thread a little while ago, maybe February that I remember had a similar problem. Danged if I can find it, now, but it may not apply. In the thread, it was found that some of the older notations for numbers (like 2.0e05 for 200,000) were making life miserable for the guy with the question. I think Pat Phelan may have been in on that thread, but I can't be too sure. That was a few too many beers ago.|||Cut and paste this, and tell me it doesn't work...

USE Northwind
GO

SET NOCOUNT ON

create table temp_upload_table
([cust code] float,
rowid int)

create table KNVV_View
(KUNNR varchar(10))

insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')

insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)

SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
GO|||Found the thread I was after. Here it is:

http://www.dbforums.com/t993650.html

I think Pat was up a bit late last night (or early this morning), so he may not be able to reply for a while.|||Added that link to my SQL Server favorites folder...|||Huh?!?! What, who me?

Did anyone check to see if 1 = IsNumeric([cust code])?

-PatP|||He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)|||Yes I did pat ...

CREATE TABLE [KNVV] (
[VTWEG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKORG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKGRP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKBUR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPART] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR5] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR4] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KUNNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KDGRP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BZIRK] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create view KNVV_View as
select distinct VKGRP
,VKBUR
,SPART
,MANDT
,KVGR5
,KVGR4
,KVGR2
,KVGR1
,KUNNR
,KDGRP
,BZIRK
from KNVV
where BZIRK <> '999999'
and kvgr1 <> '999'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TABLE [Temp_Upload_Table] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[AOP-Value] [float] NULL ,
[AOP-Volume] [float] NULL ,
[Cust Code] [float] NULL
) ON [PRIMARY]
GO

Brett .. bcp in the data and tell me this works ;)

SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)|||And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ... and hey this works on the same data ...

select * from Temp_Upload_Table where convert(varchar(20),Cust_Code) not in ( select convert(float,(select KUNNR where isnumeric(kunnr) = 1)) from KNVV_View )

Any explanations ?|||Hmm. Having a bit of trouble BCP-ing the temp_upload_table file in. I must be missing something obvious.

bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t\t -Smyserver

is giving me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.

anyone else getting this, or is this because of the big bowl of stupid flakes I had this morning?|||Checked that out ... try using EM ... import/export wizard ;D|||bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver

?|||Enterprise Manager? Pfft! I can't stand Enterprise Manager. It worked, though. ;-) And I am now getting the error. See if I get any results, though....|||OK, I have a piece of this figured out. It looks from the query plan that when you use "not in", the optimizer is checking for nulls. This check is not necessary when you are only interested in "in". I think that the implicit comparison for null is what is getting you. This apparently works:

SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in (
select isnull (Convert(Float, KUNNR), 0) from KNVV_View
where isnumeric(kunnr) = 1

)|||Are you talking about the nulls in [cust code] column|||I think the optimizer is more worried about nulls returned by the subquery. I think if there is a null in the not in list, SQL Server may get confused. Something about that three way logic. This may be why the optimizer if pulling the possibility aside in the query plan.
What confuses me, though, is why it is not doing that extra "side comparison" for queries that simply use "IN (select...)". It may be that in a query with "in (...)", the nulls in the list are simply tossed, since null compared with anything returns nothing. You, however have a "not in (...)" in your query, so the ghost of DeMorgan may be haunting you.
Make any better sense?|||The funny thing here is that KUNNR in KNVV_View table does not have nulls ... so why is that happening ...|||Probably the optimizer does not want to risk it, so it has to check.

Migration Wizard Fails Interop.DSO.IDBGroup90Ex

I am attempting to convert several 2000 AS databases to AS 2005. I have not been able to successfully convert any databases. The error message is:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Interop.Dso.IDbGroup90Ex'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{CE55E9E9-9597-4214-B21F-30C7683467B1}' failed due
to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I have attempted to convert the FoodMart sample database and it also fails to convert. I have unregistered and registered all DSO related dlls. The problem still persists. I have attempted to convert at least 4 other databases and they all fail. The failure point occurs during the conversion of the Cubes.

Thanks

Ron Cotton

Problem solved. Unfortunately, I am not sure which step actually solved the problem. I will state both here.

1. I registered all of the dlls associated with DSO, which reside in the directory: C:\Program Files\Common Files\Microsoft Shared\DSO using regsvr32. BTW, I don't believe that this solve the problem, but I have state it here because of item 2.

2. I downloaded and installed Service Pack 2 for SQL 2005. The Service Pack installation forced a reboot so I not exactly sure that it was the cure. My gut says that it was an not option 1.

Anyway, I hope that this helps anyone that has been struggling with this issue.

Thanks

Ron Cotton

migration sql Server 2000 to sqlServer2005

hi,

i must convert my SqlServer2000 to SqlServer2005. I just do it on my Computer and i run a component "SQL Server upgrade wizard" it convert my SQLServer.
But now i must do it on my Server Web and if i run that component it give me an error : "must run SQL 6.5" and i don't installed it.

Can i download it with all right migration?

thank you

If you get such error you may have inherited a database upgraded from 6.5 to 2000 with a wizard that could be a problem because I always advice 6.5 migration should be manual done by a skilled SQL Server expert. So create a blank database and run the SSIS(sql server integration services) and the error will go away. Hope this helps.

Migration MySQL to SSCE...big problems in SQL CE Language...

hello,(sorry for my english language but im french :-) )

I have a lots of problems to convert the SQL language from MySQL to SSCE because the SSCE language is so poor...

If somebody have some URL with a lots of examples of request (select, update).
for example, im not able to convert this update request :

Update reservation,lockers set reservation.ERS_ID='A' where lockers.LCK_CONF!='LW' AND lockers.LCK_PHYS_NO=reservation.LCK_PHYS_NO AND reservation.ERS_ID IN('V','M','E','S','F','D','P');

how to use 2 tables in the same update?
i saw the MSDN but there arent explicit and GOOD examples...
http://msdn2.microsoft.com/fr-fr/library/ms174122.aspx

Thanks you

Hello,

try using

update reservation set ERS_ID = 'A' where ERS_ID IN ('V', 'M', 'E', 'S', ....) and exists

(select 1 from lockers where lockers.LCK_CONF != 'LW' and lockers.LCK_PHYS_NO = reservation.LCK_PHYS_NO);

migration from sybase to sql

Our company planning to migrate database server(Unix)
sybase 11.9.2 . This database are avaliable in tape(DAT).
I want to down load & convert to seq 7 or seq 2000
directly from tap to disk.
If I am using dump statment it is showing error msg.
Any one can solve this Issue.
Thanking U
r.varadharajanThe "SQL Server 2000 for the Sybase Customer Resource Kit" might help you -
check http://www.microsoft.com/sql/evaluation/compare/sybaseresourcekit.asp.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Varadharajan" <rvrajanmdu@.hotmail.com> wrote in message
news:07f901c33f9d$6e8e49e0$a301280a@.phx.gbl...
> Our company planning to migrate database server(Unix)
> sybase 11.9.2 . This database are avaliable in tape(DAT).
> I want to down load & convert to seq 7 or seq 2000
> directly from tap to disk.
> If I am using dump statment it is showing error msg.
> Any one can solve this Issue.
> Thanking U
> r.varadharajan
>

Migration Assistant For Access Can''t Find SQL Database?

I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.

It is not practical to use standard Express to migrate Access so see if you can use the one with Management Studio instead, this will give you control of all the steps. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Yes, Management Studio is the one I used. I am able to connect via Management Studio, however, the Migration Assistant can't locate it? Looking for other suggestions? Thanks.

|||

Try the eval good for 6 months, you could buy the developer for under $40 online if you have the ram this can help you move Access if all you have is tables and queries. If you have VBA you may need professional help. I have seen Access migration taking six months or more because it lets users add stuff SQL Server will not accept and it uses limited data types.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

I'm not sure I understand what you mean by "default database" in this context. SQL Server does not create any default databases when you install it.

In my use of SSMS I find that the drop down box that show the database name does not actually show the list of databases in the server because it has not actually connected to the server yet, so it can not list the databases. You can either specify a new database to be created or specify an existing database to open once you connect, but you need to type the name of the database, it won't populate the list for you.

Regards,

Mike

migrating to ms sql from db2

currently running db2 database on IBM AS/400, looking to down size. We want to convert the db2 database to MS SQL and run it on windows 2003.
Is this a latteral move or do we have to change anything within DB2 or MS SQL(naming conventions, file types or sizes, schemas, etc) is there any tools out there for this type of migration?
Thank you for your help.You can reverse engineer your DB2 with ERwin and forward generate the model
to SQL to get your schema accross. But that is not necessarily a 1-button
push type operation. I imagine there will be subtle difference in how chars
and such are handled and they may manifest them selves as truncation bugs
and such when you run you stored procedures and such later.
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"dossman10" <anonymous@.discussions.microsoft.com> wrote in message
news:F0CF5A34-6196-44D1-9FA7-4C086AD70BCF@.microsoft.com...
> currently running db2 database on IBM AS/400, looking to down size. We
want to convert the db2 database to MS SQL and run it on windows 2003.
> Is this a latteral move or do we have to change anything within DB2 or MS
SQL(naming conventions, file types or sizes, schemas, etc) is there any
tools out there for this type of migration?
> Thank you for your help.

migrating to ms sql

we are wanting convert to MS SQL and windows 2003 running on an Intel platfrom, converting from IBM DB2 AS/400. Is it possible and what would it take to convert
Thank you.Sure it's possible.
It will take time, money and resources. Resources should
include people knowledgeable of both DB2 and Sql Server.
Your question wasn't very specific, so it's hard to give
you a very good answer.
>--Original Message--
>we are wanting convert to MS SQL and windows 2003
running on an Intel platfrom, converting from IBM DB2
AS/400. Is it possible and what would it take to convert?
>Thank you.
>.
>|||"dossman10" <anonymous@.discussions.microsoft.com> wrote in message
news:D1D753CE-78FC-4553-A36E-14FE075CC5BA@.microsoft.com...
> we are wanting convert to MS SQL and windows 2003 running on an Intel
platfrom, converting from IBM DB2 AS/400. Is it possible and what would it
take to convert?
> Thank you.|||Hi,
If it is only tables, We can do the data transfer using DTS.
But to connect from Win2003 server to AS/400 machine you should install
DB2CONNECT for AS400 provided
by IBM.
Thanks
Hari
MCDBA
"dossman10" <anonymous@.discussions.microsoft.com> wrote in message
news:D1D753CE-78FC-4553-A36E-14FE075CC5BA@.microsoft.com...
> we are wanting convert to MS SQL and windows 2003 running on an Intel
platfrom, converting from IBM DB2 AS/400. Is it possible and what would it
take to convert?
> Thank you.