Error Msg 7399, While Trying Run DMX Query From SQL Server

Sep 21, 2006



EXEC master.dbo.sp_addlinkedserver

@server = N'DMServer',

@srvproduct=N'Analysis Services 2005',

@provider=N'MSOLAP',

@datasrc=N'SVRRRDB10DEV',

@catalog=N'Adventure Works DW'

GO

select * from openquery (DMserver, 'SELECT DISTINCT [Gender] FROM TM_Cluster')

(I referred to 'Executing predictions from the SQL Server relational engine' article on this web site)


But I am getting following error :

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMserver" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for linked server "DMserver".



Pl. help.

Thanks,

Vikas

View 3 Replies


ADVERTISEMENT

Linked Server Error 7399

Mar 15, 2004

I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

The drives are secured so that only local administrators have rights to them.

I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398

In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.

My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.

Thanks

View 2 Replies View Related

Linked Server - Sybase - Error 7399

Jun 26, 2000

I am having trouble linking to a Sybase server using the ODBC OLEDB provider, with either the sp_addlinkedserver stored proc, or the "linked servers" wizard in Enterprise manager.

The SQL I am executing is:

/* add server */
exec sp_addlinkedserver
@Server = 'Linked Server Name',
@srvproduct = 'Sybase System 11',
@Provider = 'MSDASQL',
@provstr = 'Driver={INTERSOLV 3.11 32-BIT Sybase};SRVR=servername;DB=targetdatabase;UID=bob; PWD=bob;'

/* create security mapping */
exec sp_addlinkedsrvlogin
@rmtsrvname = 'Linked Server Name',
@useself = 'false',
@rmtuser = 'bob',
@rmtpassword = 'bob'


(Note that this does not use a DSN - I have tried it both ways, but with no joy).

The SQL executes without any errors or warnings. However, when I try to use the linked server connection (eg., visually list the tables available by double clicking the linked server in the Enterprise Manager, or Select * From a table in the Query Analyser) it give the following error:

"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error."
(That is - generic error)


It just seems that I am not putting the connect string components in the right places.

Does anyone have any suggestions on how to set up a linked server to Sybase in this this way?

Many TIA

View 1 Replies View Related

Linked Server To Access 97 - Error 7399

Oct 16, 2000

Hi all,

I have built several Linked Servers from my SQLServer 7 database to some Access 97 MDB files sitting on a Novell file server. When I am physically sitting at the SQLServer I can access/modify data through the links just fine. I have successfully built views, stored procs and jobs referencing these links and they all work perfectly ... As long as I am sitting at the SQLServer. So if I execute 'Select * from RT1...Emp' I get data back.

Now my problem. If I connect to the server from Query Analyser on a different machine I can not execute and anything that references a linked server. If I execute 'Select * from RT1...Emp' I get.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
[OLE/DB Provider returned message: DIsk or Network error.]

I want to be able access various views and stored procs via a VB app so. Eventually I will be moving all of the Access data into the SQLServer but for now I must access the data via links.

Any ideas?

Mark Armer
mark@armerville.com

View 2 Replies View Related

Linked Server - Error 7399 - MSDAORA

Jun 19, 2001

I'm trying to connect to an Oracle DB from my SQL 2000 server. I have the oracle client installed and I am able to use SQLPlus to connect to the oracle service and run queries. I set up the linked server just like I always do, but now when I attempt to access one of the tables on the linked server I get "Error 7399: OLE DB provider 'MSDAORA' reported an error. I am able to use the OLE DB provider for ODBC and connect with ODBC to oracle, but not with the MSDAORA OLEDB provider.

Windows 2000 Advanced Server, Service Pack 2; SQL 2000 Server

Any help would be great. Thank you in advance.

--Bryan Parke

View 1 Replies View Related

Error 7399: OLE DB Provider 'MSDAORA' Reported An Error. OLE DB Error

Feb 18, 2007

My link server was working just fine until friday evening.
It stopped worked over the week end.
with and error Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error .

---my oracle 10g client is working just fine
--TNS names looks fine
---i recreated the link but i am still getting the same error.

I need your help because a lot of jobs are using that link on Monday it is going to be crazzzy.

View 7 Replies View Related

7399 Access Denied On Vista

Jan 16, 2008

I am trying to import an Excel 2007 file into SQL Server 2005 Express using linked servers. My linked server was created without error as follows:


Linked server: XLWorkbook
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product Name: Excel
Data source: D:spreadsheet.xlsx (D: is on the same PC as the SQL Server)
Provider string: Excel 12.0

However, when I tried to import using

select * from OPENQUERY(XLWorkbook, 'Select * From [Sheet1$]')

I get:


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLWorkbook" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLWorkbook".


I had previously used the same exact query for Excel 2003 on Windows XP and it worked fine without me having to do anything else like doling out access rights.

What do I have to do to get this to work on Windows Vista (and Office 2007)?

Thanks

-kk

View 11 Replies View Related

SQL Server 2005 Error 8630: Internal Query Processor Error

Jan 22, 2007

I have a SProc that runs across many clients without any problems. Every now and then, though, I get the following error:

Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630).

All I am doing is populating Temp tables with some data and then joining them together to create a Global Temp table that is being BCP'd to a network share.

Has anyone come across this error in SQL Server 2005? I cannot find anything on Google or Microcsoft.

Thanks,
Robert

View 1 Replies View Related

SQL Server 7 Error 8630 - Internal Query Processor Error

Mar 15, 2004

Has anyone come across error 8630 "Internal Query Processor Error: The query processor encountered an unexpected error during execution." with SQL Server 7 with SP4 installed?

There are microsoft articles on the error, but the errors are suposidly fixed in SQL Server 7 SP2 and SP3.

We are selecting from one view left outer joined with another, but no unions are involved.

This is not a problem in SQL Server 2000, but unfortunatlely Microsoft are supporting SQL Server 7 until 2005 and so we have to do the same...

View 4 Replies View Related

Query Analyzer Error Unable To Connect Server Local Msg17, Level 16,state 1/ODBC SQL Server Driver [DBNETLIB]SQL Server Does Not

Oct 20, 2007

I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist

View 6 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

SQL Server 2005 9.0.3161 on Win 2k3 R2



I receive the following error:



"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."



I have traced this to an insert statement that executes as part of a stored procedure.



INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)



There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.



Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View 5 Replies View Related

Error Query Data Through Linked Server , SQL Server 2005

May 27, 2008

Hello,

I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used


The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.

Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?

Thanks,
Simba

View 1 Replies View Related

{query} Query Designer Error Encountered A Query Error

Jun 30, 2000

When I am trying to read rows from any table from any database in the offsite production
server with Pc Anywhere ( all other servers can be acess fine with pc anywhere


I get this error


An unexpected error happned during this operation
{query} query designer error encountered a query error
unexpected error



I checked the version of Enterprise manager
Help
About
microsoft managment console 1.1version 4.0 bulid 1381
service pack 4

View 1 Replies View Related

Linked Server Query Error

Feb 10, 2003

SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:

SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id

Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.

The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id


Please help I can't figure this one out.

Thanks.

View 1 Replies View Related

ODBC Sql Server Query Error

Jan 18, 2005

Hi,

I'm attempting to use the SQLExecute function to make a query to a SQL Server DB through ODBC.

However, the column I am trying to retrieve is called "date". When I try and query, I get back the message, "S0022: Invalid column name 'date'".

I believe I'm getting the error because date is a sql keyword (however if I run the query directly through SQL Interactive, I do not get a problem).

If anyone knows how I can get around this problem, that would be very helpful, as it is not possible to change the column name.

Thanks in advance.
Khalid.

View 7 Replies View Related

Error LINQ : Query A SQL Server Database !

Mar 31, 2008

I have an example : A Simple Database Query using LINQ to SQL
Before creating the project, I using SQLmetal to generate the Nosthwind Entity Classes "Northwind.cs" 
c:sqlmetal /namespace:nwind /code:Northwind.cs /pluralize /functions /sprocs /views "C:CongChinhNorthwindNorthwind.mdf"
and then, I add the Northwind.cs in my project.
CODE  1 using System;
2
3 using System.Linq;
4
5 using System.Text;
6
7 using nwind;
8
9
10
11 namespace Ex
12
13 {
14
15 class Program
16
17 {
18
19 static void Main(string[] args)
20
21 {
22
23 Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
24
25 var cust =
26
27 from c in db.Customers
28
29 where c.City == "Rio de Janeiro"
30
31 select c;
32
33 foreach (var kh in cust) //---> SQLException was unhandled
34
35 Console.WriteLine("{0}", kh.CompanyName);
36
37 Console.ReadLine();
38
39 }
40
41 }
42
43 }
 But when I run the program. It throws a SQLExecption : Login failed for user ''. The user is not associated with a trusted SQL Server connection.I'm using SQLExpress and server name : CONGCHINHSQLEXPRESS and Windows Authentication
I don't know how to solve this problem. Somebody help me,please !

View 1 Replies View Related

SQL Server 2008 :: Error Loading A Query Into DTA

Nov 12, 2012

I am trying to look into a query thats taking 5 mins to execute on our production. Its an LinQ generated 6000 Line SQL query that the dev team is trying to put into production without any testing. They want me to add indexes to the database( brand new) so that the query will run fast. The execution plan really looks weird. So Just as a starter I tried to tune it using the DTA and I am getting the below error. I am using a .sql query file as the input.

The minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least 441 MB.Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <Tuning Options>

My question is what value is the maximum value that I should consider adding per recommendation (2) in the above error.

View 1 Replies View Related

Internal SQL Server Error For Nested Query

May 29, 2008

Hi,

I have a following query:


select cu.Currencies_ShortName

from dbo.Currencies cu
inner join
(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,

/*v1*/ -- max(SpotBid) as SpotBid, max(SpotAsk) as SpotAsk

/*v2*/ SpotBid, SpotAsk

from dbo.Pairs p1 inner join dbo.PairsQuotes pq1

on p1.Pairs_Id=pq1.Pairs_Id
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)

where p1.Pairs_Shortname like 'EUR%'

/*v1*/ -- group by p1.Pairs_Shortname, right(p1.Pairs_Shortname,3)

) as sr
on cu.Currencies_ShortName=sr.Currency1


which would work like /*v2*/, but not like /*v1*/ - there would be a message:
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

Is Group By not manageable in a nested query, or is it some other problem?

thx
Katarina

View 5 Replies View Related

Query Notification / Server Log Error Messages

Oct 11, 2007

Two days ago I discovered that the drive on our test SqlServer2005 was full. The current Error_log had 54+ Gb (yes Gb) of records. Not only did I delete the file I restarted SS and the server.

That afternoon, I discovered that the current log had 33,678 records! I believe 33,616 of the records came from a single run of my app and set of program events. Obviously I'm doing something wrong in my Query Notification implementation and testing. And yet, when I try to to deliberately trigger the phenomenon, I can not.


Here is the head of the exported log file; the two msgs (query dialog closed and cannot drop queue) alternate thousands and thousands of times form a single test run. What is going on?! Pls help!

Date,Source,Severity,Message
10/11/2007 11:42:52,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
10/11/2007 11:42:52,spid5s,Unknown,SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
10/11/2007 11:42:49,spid14s,Unknown,Service Broker manager has shut down.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
10/11/2007 11:42:04,spid51,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'

View 2 Replies View Related

Linked Server Query - Timeout Error Under Load

Apr 23, 2002

Hello,

I am researching alternatives to a stored procedure that uses a linked server query that my ASP page uses to retrieve records to the page.

Should I:

Use replication (Data is constantly updated, so a snapshot won't do. Merge replication?)

Break up the stored procedure into subqueries?

Use a view on the remote server instead of the actual tables?

I am trying to reduce the amount of table scans, what is the best way to do this?

Here is the stored procedure I am trying to tune:
(@startDate and @endDate are parameters passed from the web page):

CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
SELECT Store.[Str#], Store.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (Store INNER JOIN Event E ON Store.[DemoID#] = E.event_number)
LEFT JOIN (Location L RIGHT JOIN POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((Store.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY Store.[Str#]
GO

Any suggestions on my sp or anything else that can implement would be greatly appreciated.

TIA,

Bruce Johnston
Programmer/Analyst

View 1 Replies View Related

SQL Server 2014 :: Error In Adding Condition In Query

Apr 4, 2015

Following is a working code

declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')

[Code] ...

I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id

I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'

When I tried this code I am getting error.

View 3 Replies View Related

'Timeout Expired' Error Due To Long Query Or Busy Server?

May 2, 2008

We have been bothered with this problem for a while.  Usually I happens in the early moring.  Later on after the error is gone on auser, the error never happens again on any user for the day.  Is this a web Server problem or an aspx.vb coding error? Thanks,Jeffrey 
Server Error in '/SSSSS' Application.


Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.Source Error:



Line 270: oDBCommand.Connection.Open()
Line 271:
Line 272: Dim myReader As SqlDataReader _
Line 273: = oDBCommand.ExecuteReader(CommandBehavior.CloseConnection)
Line 274:Source File: E:SSSSSScheduling.aspx.vb    Line: 272 Stack Trace:



[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
Soma.Scheduling.GetNextAutoAppointment() in E:SSSSSAScheduling.aspx.vb:272
Soma.Scheduling.Page_Load(Object sender, EventArgs e) in E:SSSSSScheduling.aspx.vb:61
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

View 7 Replies View Related

Query Server Error When Accessing Visual FoxPro 9.0 Via ODBC

Nov 6, 2007



I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:

Query Server Error
DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary

I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..

View 1 Replies View Related

Internal Query Processor Error: The Query Processor Encountered An Unexpected Error During Execution.

Nov 26, 2007



I am running a query on a SQL Server 2005 database and encounter the following error message


"Internal Query Processor Error: The query processor encountered an unexpected error during execution."

There is a join between a table on the 2005 database and another on a 2000 database. I have run DBCC CHECKTABLE and found no errors on the two tables.

Anybody with ideas?

Thanks

View 3 Replies View Related

SQL Server 2008 :: Error - Value Does Not Fall Within Expected Range In Delete Query?

May 28, 2015

I have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:

delete from STG_Shipped_Invoiced
where Transaction_Date=?
INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt,
trans_date, trans_type, Created_date, Transaction_Date)
select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,

[Code] ....

Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?

View 0 Replies View Related

Delete Query Failes On Some Databases With Internal Server Error 8624

Feb 5, 2008

Hiya,

We've got a strange problem.
We are running on SQL 2000 SP4.
Our structure uses database pairs, we have a Current and a History database so old records that don't change and aren't needed very often are only loaded into memory when needed.

The following query (the names have been changed) deletes the quote from the current database when the order raised from the quote has been moved into the history.




Code Snippet
DELETE FROM t_CurrentUnpaid
FROM db_Mule_History.dbo.t_History INNER JOIN
t_Quote ON
db_Mule_History.dbo.t_History.uid = t_Quote.uid INNER JOIN
t_CurrentUnpaid ON
t_Quote.uid_Parent = t_CurrentUnpaid.uid
where t_CurrentUnpaid.uid_cont != -2






This query runs fine on 4 identical database pairs, on the 5th pair it fails. All these pairs are on the same server, with the same permissions and the exact same structure. Only the data would differ

The error returned is:
Internal server error 8624
level 16 state 1 line 1


We have looked at MS articles, but all that I can find are about bugs fixed in SP1, 2, 3 and 4.
Nothing post SP4 that we are running.

Many thans in advance.

Ian Wilkinson

View 1 Replies View Related

Severe Error After Installing SQL2005 SP2 Query To Linked Server Foxpro With Oledb

Oct 24, 2007

After installing sql2005 sp2 a simple select query to a linked server reports the following error message:

Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.

The linked server is a Visual FoxPro database.

After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.

View 3 Replies View Related

DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins

Apr 26, 2015

I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?

Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.

View 3 Replies View Related

Table Query Fails With Object Not Found Error After Assining Sysadmin Server Role To Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.

View 5 Replies View Related

Query Execution Failed For Data Set &&<name&&> For More Information About This Error Navigate To The Report Server On The Local Serv

Jul 10, 2007

I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error


Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors


Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report.
The error message was slightly different, in that it couldn't even open the Datasource.

We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.

I could not figure out how to "enable remote errors" or find an error log on the server. The C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles Log files did not appear to record any errors.

Please advise!

View 6 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T

• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues

• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"

• We are using the last version of Excel Add-in

• We try to reinstall the MDS feature

• If I backup/restore MDS database to other server it works

• We updated to SQL 2012 SP2 + CU4 but the error persisted ...

Looking at the MDSTraceLog we are routed to the this msg

SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28

At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped

** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
   at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
   at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)

[code]....

View 3 Replies View Related

Query Designer Encountered A Query Error Via Enterprise Manager

Jan 7, 2002

I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error.
We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database
maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go
into production in a few days.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved