No data found error in sql server



No data found error

May 10, 2011 at 7:38 am

We get the below error when using order by clause in the select query using the linked server to Oracle table (LS..USER2.UNIT3)

SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

AND U.CREATE_DT = ’18/01/2009′

AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

ORDER BY U.ABO, U.LED_ID

OLE DB provider «ORAOLEDB.ORACLE» for linked server «ls» returned message «ORA-01403: no data found».

Msg 7346, Level 16, State 2, Line 1

Cannot get the data of the row from the OLE DB provider «ORAOLEDB.ORACLE» for linked server «ls».

But the query is successful if we comment out the order by clause & also if we remove the U.CREATE_DT condition in the where clause..

I’m not sure what we are missing.. Please help.

May 10, 2011 at 8:25 am

oops i thought the issue might be how Oracle is less forgiving than SQL when it comes to conversions from string to DAteTime, but most of what you qare doing is executed locally.

are any of the columns coming from the linked server CLOB/BLOBS? some drivers don’t bring them over nicely,

here’s your SQL re-written to use the updated ANSI format with JOIN syntax:

INNER JOIN unit2 L

INNER JOIN LS..USER2.UNIT3 P

ON L.CT_ID = P.CT_ID

INNER JOIN unit4 T

AND U.FJT_TYP = T.FJT_TYP

WHERE U.ABO like ‘LE%’

—AND U.CREATE_DT = TO_DATE(’18/01/2009′ ,’DD/MM/YYYY’)

AND U.CREATE_DT =’2009-01-18′

ORDER BY U.ABO, U.LED_ID

Lowell — help us help you! If you post a question, make sure you include a CREATE TABLE. statement and INSERT INTO. statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

May 10, 2011 at 8:39 am

Thanks a lot for your reply.

We don’t have any CLOB/BLOBS columns in the oracle tables.. Since our application code is already written and we are testing the linked server with this code, we are trying as much not to change the queries so it reduces huge amount of time altering all the queries in the code.. Is there anything else that we could do to make this work without altering the query? Like may be change the format etc..

SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

AND U.CREATE_DT between ’01/18/2009′ and ’04/20/2009′

AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

ORDER BY U.ABO, U.LED_ID

The above query & the query that you posted using the inner join works only if I remove the order by clause or if I remove one of the where clause condition. Is this a limitation in linked server?

Источник

FIX: «The requested name is valid, but no data of the requested type was found» error when you try to configure BRE to use an instance of SQL Server as a data store in BizTalk Server 2010

Symptoms

Consider the following scenario:

You install Microsoft BizTalk Server 2010 on a computer.

You configure an instance of Microsoft SQL Server to use a nondefault TCP port.

Note The default TCP port is 1433.

You try to configure Business Rule Engine (BRE) to use the instance of SQL Server as a data store.

In this scenario, the configuration fails, and the following error message is logged in the SQL Server log file:

[ Error RulesEngine] System.Net.Sockets.SocketException (0x80004005): The requested name is valid, but no data of the requested type was found
at System.Net.Dns.GetAddrInfo(String name)
at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
at Microsoft.BizTalk.Configuration.Rules.RulesConfig.ConfigureB2BOperatorsRole(String rulesDbServer, String rulesDbDatabase)
at Microsoft.BizTalk.Configuration.Rules.RulesConfig.ConfigureFeature(String strXMLDoc, String strFullXMLDoc, String& strErrorDescription)

Resolution

Cumulative update information

The hotfix that resolves this issue is included in cumulative update package 1 for BizTalk Server 2010.

Читайте также:  Exception has occurred attributeerror

For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2497789 Cumulative update package 1 for BizTalk Server 2010

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

References

For more information about how to configure BRE by using the BizTalk Server Configuration Manager, visit the following MSDN website:

How to configure BRE by using BizTalk Server Configuration ManagerFor more information about BizTalk Server hotfixes, click the following article number to view the article in the Microsoft Knowledge Base:

2003907 Information about BizTalk Server hotfixes

Источник

Database

FirstPrevNext
How to encrypt SQL Server 2005 Database? A k ch 4-Jun-09 22:49
Hi frd’s

Our client needs that his data base should be encrypted. Here i want to encrypt data base in such manner that it should work normally from .net web application but when some one tries to open it in SQL server IDE it should not show raw data. Even password protection is also OK for me for .MDF and .LDF files.

Sign In· View Thread
Re: How to encrypt SQL Server 2005 Database? Mycroft Holmes 6-Jun-09 5:27

Sign In· View Thread
query not working correctly

Mathew P V 4-Jun-09 21:49
i have to display leave allotted record of a single employee in gridview but because i have stored three records of permanent employee in generalinfo table its displaying three times my query is as follows

SELECT LeaveMaster.LeaveType, LeaveAllotted.NoOfDays FROM LeaveMaster INNER JOIN LeaveAllotted ON LeaveMaster.LeaveTypeID = LeaveAllotted.LeaveTypeID INNER JOIN EmpCategType ON LeaveAllotted.EmpCategID = EmpCategType.EmpCategID INNER JOIN GeneralInfo ON EmpCategType.EmpCategID = GeneralInfo.EmpCategID

what should i do to rectify this problem

a help from somebody will be deeply appreciated.

Sign In· View Thread
Re: query not working correctly

smcnulty2000 5-Jun-09 0:54

Have you tried the distinct keyword ?

SELECT distinct LeaveMaster.LeaveType, LeaveAllotted.NoOfDays FROM LeaveMaster INNER JOIN LeaveAllotted ON LeaveMaster.LeaveTypeID = LeaveAllotted.LeaveTypeID INNER JOIN EmpCategType ON LeaveAllotted.EmpCategID = EmpCategType.EmpCategID INNER JOIN GeneralInfo ON EmpCategType.EmpCategID = GeneralInfo.EmpCategID

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Sign In· View Thread
Re: query not working correctly Mathew P V 5-Jun-09 1:26

Thank you smcnulty
i forgot to do that now its working
once again thankyou very much

Sign In· View Thread
Re: query not working correctly Niladri_Biswas 6-Jun-09 20:01

Yes I fully agree with Scott M

Use distinct clause

Sign In· View Thread
MSOLAP.2 provider problem

ThaScorpion 4-Jun-09 4:26
Team,

I have a web Windows server 2003 64-bit and installed Sql server analysis services 2000 along with SP4, the problem is that: our application is ASP classic and has the connection string:
«Provider=MSOLAP.2;Timeout=60;Data Source=localhost;Initial Catalog=myCube;Client Cache Size=25»
I can not find a provider for MSOLAP.2, I have copied most of the dlls from the legacy server (32-bit) and registered them, but still cannot connect to that cube.
when I check with a .udl file I don’t see any provider for OLAP.

What do I need to setup? Any idea? What is wrong here?

Note: I can open analysis services manager and connect to my cubes successfully.

Thanks in advance.

Sign In· View Thread
how to handle no data found error in sql server lakshmichawala 4-Jun-09 4:25
how to handle no data found error in sql server
Sign In· View Thread
Re: how to handle no data found error in sql server Ashfield 4-Jun-09 22:04

How is it an error? It just means youur query returned no results. Handle it where? Please explain exactly what the problem is, my mystic powers are worn out by Friday.

Sign In· View Thread
One more query problem sujithkumarsl 4-Jun-09 0:36
Suppose i have a table like this..

i want to select col1 where 100=1 and 200=2 and 300=3

Sign In· View Thread
Re: One more query problem Mycroft Holmes 4-Jun-09 20:42

Sign In· View Thread
Re: One more query problem Niladri_Biswas 6-Jun-09 20:38

Please vote

Sign In· View Thread
Query problem [modified]

sujithkumarsl 3-Jun-09 23:31
i am using sql server database

please check the below table two columns say column A and Column B

if the user give 100,200 and 300 i want return 1 ( which has all these values)
if the user give 100 and 200 i have to give 1 and 2. Hope this is clear. Please help me to build the query

Sign In· View Thread
Re: Query problem [modified] Niladri_Biswas 6-Jun-09 21:01

Hello this time the question is not clear.

Please be more specific.

This is a very poor way of asking your question. Please define the function mapping your input to output more specifically

Tell us the desired output.

For Case 2:
I/P: 100 , 200

Please from next time whenever you post, give the sample output so that it will be easy to solve.

Also, are you passing a comma delimited value or what?

Nothing clear!:

Sign In· View Thread
Re: Query problem Niladri_Biswas 6-Jun-09 21:44

SELECT MIN(A), B
FROM tbl_test
WHERE B IN (100, 200, 300)
GROUP BY B;

SELECT A, B
FROM tbl_test
WHERE B IN (100, 200)
ORDER BY A, B;

hope this helps.

And vote me

And from next time please define the function mapping your input to output more specifically

Sign In· View Thread
Default Collation

hadad 3-Jun-09 23:03
Hello,
When I try to insert unicode chars (Arabic chars) in my database which has its default collation to (latin) these char are inserted incorectly even if I use the nvarchar data type is this because of the collation, can II use the Arabic bin collation to resolve this proplem or ther is an other raison?

Sign In· View Thread
Perfomance on the Following Query

Vimalsoft(Pty) Ltd 3-Jun-09 21:54
Good Morning All

I have Table Defined as

And i have Two insert statements that takes 29 seconds each

How can i improve the Perfomance of this Insert statements

Thank you

Sign In· View Thread
Re: Perfomance on the Following Query

Ashfield 3-Jun-09 22:04

Have you had a look at the execution plan? That should show where the most time is taken. Just in passing, why 2 inserts when 1 will do — unless I’m missing something this has the same effect

It may be quicker to use a temp table, take the distinct off this query and do a distinct from the temp table as it may have many less records to process.

Sign In· View Thread
Re: Perfomance on the Following Query Vimalsoft(Pty) Ltd 3-Jun-09 22:34

Good Morning Ashfield

Thank you for your reply.

Ashfield: wrote
why 2 inserts when 1 will do — unless I’m missing something this has the same effect

If i use <> or != it will take 3:47 Seconds. by separating the Query it took less than that and there thing to note is that there is not Unique key in these table.

Few companies that installed computers to reduce the employment of clerks have realized their expectations. They now need more and more expensive clerks even though they call them «Developers» or «Programmers.»

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Sign In· View Thread
Re: Perfomance on the Following Query Ashfield 4-Jun-09 2:31

Something I just noticed, it would probably benefit to change your index to put on domn then slot. If you think about it, you are matching on domn and looking for slot within in that domn.

Sign In· View Thread
Re: Perfomance on the Following Query smcnulty2000 4-Jun-09 2:58

And consider telling us three more things;

1. Which version sql server, 2005 or 2008?
2. What is the description of tempslot?
3. What is the result when you run the select statements without the insert portion of the statement?

In other words, do they run faster by quite a bit or the same?

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Sign In· View Thread
Re: Perfomance on the Following Query smcnulty2000 3-Jun-09 23:36

Short answer: try removing the index.

How many records are you putting in? That’s pretty critical to discussing this. 29 seconds with a billion records is different than 29 seconds with a 1000 records.

You might be better off without the index. I recommend you drop the index and retest your speeds without it.
Realize that indexes are great for lookup but there is a price to pay when you write to a table.

Every time you write to the table, the system has to rebuild the index and since you are using a clustered index you are reordering the data blocks in the same order as the index. So you write, then insert the next line, then reorder, then rewrite the whole mess. That’s not exactly efficient.

And because it is a primary key the system will check the uniqueness of each line entered every one of those checks costs you.

Equally important is the fact that you are indexing all columns in one index. You really are just trading disk reads in one place for disk reads in another.

On the other side; when someone runs a select against this table what percentage of the data will be returned on a regular basis? Will it be 5% or less? Or will it be most of the table.

If you aren’t going to see 5% or less then having an index is just a waste. Full table scans would be quicker.

With all of that in mind; if you must have the index then see if you can code the situation so that it drops the index, then inserts all the data in one fell swoop, then rebuilds the index.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Sign In· View Thread
Re: Perfomance on the Following Query Vimalsoft(Pty) Ltd 4-Jun-09 1:00

Good Morning smcnulty2000

Thanks for your reply

smcnulty2000 wrote :
Short answer: try removing the index.

Without indexes it run 2 Minutes 10 seconds

Thank you for the Advice.

Few companies that installed computers to reduce the employment of clerks have realized their expectations. They now need more and more expensive clerks even though they call them «Developers» or «Programmers.»

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Sign In· View Thread
Re: Perfomance on the Following Query Ashfield 4-Jun-09 2:24

Short answer: try removing the index.

The index is on the source table, not the destination so its not going to help at all, — if it was the destination then I would agree (in principle) that it may help, but as you sya, how many records?

Sign In· View Thread
Re: Perfomance on the Following Query smcnulty2000 4-Jun-09 2:37

My error. I’m translating from another sql language but catching up.

At least now we know it is using the index.

Vuyiswa Maseko I’m eager to read how many records get processed.

Post the execution plan as well, if you can. That might get us some clues.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Sign In· View Thread
Last Visit: 31-Dec-99 19:00 Last Update: 12-Jan-23 13:23 Refresh ᐊ Prev1 . 831832833834 835 836837838839840 Next ᐅ

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Источник

Оцените статью
toolgir.ru
Adblock
detector