data provider or other service returned an E_Fail status in sql server 2005

after converting database from sql server 2000 to sql server 2005. my program on vb6 is facing runtime error. i.e.data provider or other service returned an E_Fail status .

how can i get rid this problem

Could you please post your connection string and the exact error message that you are getting.

Jag

|||

Main_Module.STRCNNN1 = "driver={SQL Server};server=" & Main_Module.Server_Name & ";" & _
"uid=SA;pwd=;database=" & Main_Module.Common_Database
error msg is

runtime error

data provider or other service returned an E_Fail status in sql server 2005

when i am fetching record from view using order by clause in select statement then error is coming

as

select * from vew_emp where grade='C' order empno

at the first time it fetching record with order by

but when i am using recordset.Requiry

it cud not fetch the record

i think when the recordset is open with the record of view

i am deleting ,inserting record in the view base table then the main recordset is not working

without using order by it is running and working well
plz reply me quickly

Data modeling question

I'm facing the next problem:

I have a table with two columns (among others) modeling category and
subcategory data for each row. I need to summarize info on this two
columns, but with the next specs:

1.- Some grouping is only on the category column.
2.- Some other grouping consider the two columns.

The values for the two columns come from external source, i.e. I have
no means to know the precise universe of data (I suppose soon or later
we'll have a sufficient sample of data, but for now it's not the
case). So, I would like to have a grouping table so it's not necessary
to insert a row for every pair of category and subcategory (although
it would be the best approach for the sake of design's simplicity). As
I don't know every possible combination, I would prefer something like
'this category is a - no matter the subcategory', and 'this other
category + subcategory is b'. Let's go with a sample:

------------------

Create Table B ( -- groupings --
categ char(8),
subcateg char(5),
what_group char(10)
)

-- All rows with 432 code are cat. A --
Insert B ( '00000432', ' ', 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat.
B) --
Insert B ( '00000636', '08552', 'Category B' )
Insert B ( '00000636', ' ', 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8),
subcateg char(5)
)

Insert A ( '00000432', '01322' )
Insert A ( '00000432', '01222' )
Insert A ( '00000432', '01100' )
Insert A ( '00000432', ' ' )

Insert A ( '00000636', '08552' )
Insert A ( '00000636', '08552' )
Insert A ( '00000636', '01100' )
Insert A ( '00000636', ' ' )
Insert A ( '00000636', '01111' )

-- The query like:

Select b.what_group, count(*) as cnt
From a
Left Join b
On /* ? ? ? ? */

-- Should give --

what_group cnt
----- ----
Category A 4
Category B 2
Category C 3

--------------------

It would be easier knowing all the pairs categ - subcateg. If I don't
know them, is a good idea to model the grouping table as I've done
with rows in B?

TIA,

Diego
Bcn, Spain[posted and mailed, please reply in news]

Diego Buendia (dbuendiab@.yahoo.es) writes:
> I'm facing the next problem:
> I have a table with two columns (among others) modeling category and
> subcategory data for each row. I need to summarize info on this two
> columns, but with the next specs:
> 1.- Some grouping is only on the category column.
> 2.- Some other grouping consider the two columns.
> The values for the two columns come from external source, i.e. I have
> no means to know the precise universe of data (I suppose soon or later
> we'll have a sufficient sample of data, but for now it's not the
> case). So, I would like to have a grouping table so it's not necessary
> to insert a row for every pair of category and subcategory (although
> it would be the best approach for the sake of design's simplicity). As
> I don't know every possible combination, I would prefer something like
> 'this category is a - no matter the subcategory', and 'this other
> category + subcategory is b'. Let's go with a sample:

I have done one change to your set up: rather than using space to
mean "no subcategory", I'm using NULL. Here is a repro which appears
to give the correct result:

Create Table B ( -- groupings --
categ char(8) NOT NULL,
subcateg char(5) NULL,
what_group char(10) NOT NULL
)

-- All rows with 432 code are cat. A --
Insert B VALUES( '00000432', NULL, 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat. B)
--
Insert B VALUES ( '00000636', '08552', 'Category B' )
Insert B VALUES ( '00000636', NULL, 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8) NOT NULL,
subcateg char(5) NULL
)

Insert A VALUES( '00000432', '01322' )
Insert A VALUES( '00000432', '01222' )
Insert A VALUES( '00000432', '01100' )
Insert A VALUES( '00000432', NULL )

Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '01100' )
Insert A VALUES( '00000636', NULL )
Insert A VALUES( '00000636', '01111' )
go
SELECT what_group , COUNT(*)
FROM (
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND A.subcateg = B.subcateg
UNION ALL
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND B.subcateg IS NULL
WHERE NOT EXISTS (SELECT *
FROM B b1
WHERE A.categ = b1.categ
AND A.subcateg = b1.subcateg)
) AS x
GROUP BY what_group
go
DROP TABLE A, B

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

data in sqlserver is not case sensitive

I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.

Take a small example
Need a table to store all the alphabets in a table
the table structure is

CREATE TABLE [dbo].[ALPHABET] (
[Alphabet] [varchar] (1) NOT NULL ,
[Description] [varchar] (50) NOT NULL
)
GO

The data is

Alphabet

Description

a

Small a

b

Small b

C

Capital C

A

Capital A

sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.

I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary

change the collation if the field you want to be case-sencetive into SQL_Latin1_General_CP1_CS_AS

Data format conversion.

Hello,

I am jivesh, working on live project with asp.net & sql server2003. I am facing one problem, client is in belgium uses Dutch language, and we are english. when we enter decimal value in sql database it gets save but the client has server in dutch language and in Dutch "." is ",", so here sql gets conflict , it takes "," as character & gives error, eg .

Add an item and fill out the fields with numbers (e.g. 45.05 ). When you want to edit this item later on, the number has been changed to e.g. 45,05 and this gives an error .

While we input the values 45.05 it gets save as it is, but when we retrive the value in textbox to edit, it comes in the way 45,05. the dot gets converted in comma. please help me soon.

Thanx

As long as you use a numeric type, the comma or dot isn′t stored at all, this is just a case of client presentation issue. So that should be an issue. It depends on your client code how you handle those inserts to the database. Normally you pass a common decimal type to the server, so this one gets "converted" (or just inserted) into the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de