MSIDXS OpenQuery, Paged, Very Slow

Jun 12, 2008

I have a paging query that uses OpenQuery to access the MSIDXS indexing service and return records where text is matched. The query looks like this:

select top 25 * from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst and catname_cst='Main'
and (v.docid_cin not in (select top 600 v.docid_cin from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst order by v.docid_cin)) order by v.docid_cin

Not the exact query, but it is just as slow as the query in my program. This query will retrieve the top 25 records starting at record 601 where 'report' is found in a document and where the category = 'Main'

What is really weird about this is that in management studio this query will sometimes return in like 3 seconds, while the exact same query will take 30 seconds using ASP.Net / System.Data.SqlClient (and I mean it actually sits on sqlCommand.ExecuteReader() for a long period of time). Also, does management studio cache the table randomly, because sometimes it'll take forever and other times it'll be fast.

I do not have response issues with this paging query in non-MSIDXS queries.. I can for example return the 5,000th - 5,025th of 50,000 records in less than a second while the OpenQuery manages to get progressively slower very quickly (like 3 extra seconds per page of 25 records, so it takes like 20 seconds to return 150-175 of 600).

View 3 Replies


ADVERTISEMENT

Memory Being Paged Out.

Jan 29, 2008



Can anybody shed some light on my issue? I'm reviewing my client's event log and finding the repetitive warning "A significant part of sql server process memory has been paged out. This may result in a performance degradation." This has been an ongoing issue that I've searched high and low to try and resolve.

The system is Windows Server 2003 Standard x64 Edition - SQL Server 2005 SP2 - 2.33 GHz - 4 GB Ram


Thanks in advance for any help!

View 6 Replies View Related

Paged Results From SQL Query?

Jan 25, 2006

I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?

View 4 Replies View Related

Process Memory Has Been Paged Out

Jun 9, 2008

Hi all

I frequently see the following message on SQL Server log

2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.

What does it indicates and what appropriate action has to be taken to fix it.

The database runs on

SQL 2005 Dev 64-bit SP2 9.00.3042.00
Win 2003 standard x64 SP2 16GB RAM

Thanks.

View 10 Replies View Related

Classic Paged Report

Oct 6, 2007

Is there a way, other than simply changing the format of the SRS report, to create an old fashond Paged style report - similar to the ones you can get from Crystal Reports?

View 6 Replies View Related

Paged Result Sets

Aug 28, 2006

What is the recommended mechanism for selecting paged results from SQL.

Presently I pass various params including the request Max Items Per Page and the requested page.

The I execute the query as a count with the search params.

Then comes the paging logic, which validates the page number against the request page and number of hits etc.

Then a temp table and record variables are created for the results.

Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).

Then I return the temp table & some additional return params with the Total Hits etc.

The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.

Thanks for any input,

Martin.

View 11 Replies View Related

Paged Query Not Working Via Program

Mar 27, 2008

I am trying to move my application (asp.net) from a non-paged
select to a paged query.

I am having a problem. Below is my Stored Procedure. The
first Query in the procedure works...the rest (which are commented
out ALL work interactively, but fail when the program tries to
access....The ONLY THING I change is the stored procedure
I switch the comment lines to the non-paged procedure and it
works, I try to use the any of the paged procedures and it
fails with the same error (included below)

I can't see where any of the queries are returning
different results. I have also included the program abort
that happens, it is the same for all of the paged queries.

ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment
(
@EquipmentTypeId int,
@StartRowIndex int,
@MaximumRows int
)
AS


-- ************************************************************************************************
-- Non-Paged OUTPUT
-- THIS WORKS!!!!!
SET NOCOUNT ON
SELECT Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0


/*
-- ************************************************************************************************
-- Using a Temp Table
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
SET NOCOUNT ON
create table #PagedEquipment
(
IndexId int IDENTITY(1,1) Not NULL,
EquipId int
)

-- Insert the rows from Equipment into the PagedEquipment table
Insert INTO #PagedEquipment (EquipId)
select EquipmentId From Equipment
WHERE IsDeleted = 0


SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category
FROM Equipment
INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId
INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1)
*/


/*
-- **********************************************************************************************
--Using the With to create a temp table (in memory)..works interactively but fails when
--called by the application..
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
Set NOCOUNT ON;
With PagedEquipment AS
(
SELECT EquipmentId,
ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber
FROM Equipment
WHERE EquipmentTypeId = @EquipmentTypeId AND
IsDeleted = 0
)

SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType
FROM PagedEquipment
INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId
INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId
INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId
WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows)
return
-- *******************************************************************************************
*/

/*
-- ********************************************************************************************
--nested selects
--THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM
SET NOCOUNT ON
Select * From
(
Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber,
Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0
) equip
Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows )
-- ************************************************************************************************
*/

Server Error in '/pUse' Application.
--------------------------------------------------------------------------------

Arithmetic overflow error converting expression to data type int.
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: Arithmetic overflow error converting expression to data type int.

Source Error:


Line 148: {
Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>();
Line 150: while (reader.Read())
Line 151: equipment.Add(GetEquipmentFromReader(reader));
Line 152: return equipment;


Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150

Stack Trace:


[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.Read() +9
PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150
PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Adapters.ControlAdapter.CreateChildControls() +12
System.Web.UI.Control.EnsureChildControls() +128
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

View 1 Replies View Related

SQL Server Paged Recordset Problem

Sep 19, 2005

I have implemented the Stored Procedure "RowCount" method of controllingpaged recordsets in ASP as shown in this pagehttp://www.aspfaq.com/show.asp?id=2120.I have had to make heavy alterations to the code in order for it to workwith my application.It all worked fine until I tried to sort my data by a field which containeda NULL value (even in just one record). Before I added additional criteriafor the Unique_ID field the stored procedure returned zero records whensorting by such a field. I then added the Unique_ID criteria to the ORDER BYand WHERE clauses (as seen below) and it excludes records.In short obviously what I want this to do is order by fields that containNULL values (e.g. fld4 in sample data below). It currently pages and ordersperfectly in fields which have data in every row.If anyone reads this and helps me, thanks *very* much in advance.(P.S. sorry about the horrible state of my code but I've been messing withit trying to get this fixed for ages)(P.P.S the reason for passing in the long SQL string as a parameter in theSP is because the string is generated using complicated loops and I foundthis much easier in ASP than doing it in SQL)******************************************Here is an example of a call to the procedure from ASP:EXEC st_paging_rowcount 7, 1, 50, 'SET ROWCOUNT 50 SELECT utbl7.*,utbl6_1.fld1 AS fld3_Name FROM utbl7 LEFT OUTER JOIN utbl6 utbl6_1 ONutbl7.fld3 = utbl6_1.Unique_ID WHERE utbl7.fld5 >= @fld1val ORutbl7.Unique_ID >= @uniqueid ORDER BY utbl7.fld5',5******************************************Here is the SQL SP:CREATE PROCEDURE st_paging_rowcount@tableid INT,@pagenum INT = 1,@perpage INT = 50,@finalselect NVARCHAR(1500),@sortfield INTASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INT,@querystring1 NVARCHAR(200),@querystring2 NVARCHAR(200),@querystring3 NVARCHAR(200)SELECT @querystring1 = N'SELECT @rows = COUNT(*),@pages = COUNT(*) / @perpageFROM utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)'--SELECT GOGOGO = @querystring1EXEC sp_executesql@querystring1,N'@rows INT OUTPUT, @pages INT OUTPUT, @perpage INT',@rows OUTPUT, @pages OUTPUT, @perpageIF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum < 1 SET @pagenum = 1IF @pagenum > @pages SET @pagenum = @pagesSET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,TotalPages = @pages,TotalRows = @rows-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE @fld1val NVARCHAR(64)DECLARE @uniqueid INTSELECT @querystring2 = N'SET ROWCOUNT ' + CAST(@lbound AS NVARCHAR(15))+ 'SELECT@fld1val = fld' + CAST(@sortfield AS NVARCHAR(15)) + ', @uniqueid= Unique_IDFROMutbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)ORDER BYfld' + CAST(@sortfield AS NVARCHAR(15)) +', Unique_ID 'SELECT test0 = @querystring2EXEC sp_executesql@querystring2,N'@fld1val NVARCHAR(64) OUTPUT, @uniqueid INT OUTPUT',@fld1val OUTPUT, @uniqueid OUTPUT--SELECT test = @finalselectEXEC sp_executesql@finalselect,N'@fld1val NVARCHAR(64), @uniqueid INT',@fld1val, @uniqueidSET ROWCOUNT 0END*********************************************Here is some sample data from the table (e.g. would like to sort by fld4):Unique_ID Date_Added Who_Added Locked fld1fld2 fld3 fld4----------- --------------------------- ----------- ----------- ------------------------------------------------------------3 2005-09-16 16:12:30.200 1 0 SmithJohn 1 NULL4 2005-09-16 16:12:41.013 1 0 JonesChris 1 NULL6 2005-09-16 16:13:10.187 1 0 StamovStilian 1 NULL7 2005-09-16 16:19:15.437 1 0 LewickiSteve 1 Colchester8 2005-09-16 16:19:36.937 1 0 JamesPhil 1 NULL9 2005-09-16 16:20:35.327 1 0 LeroyDidier 1 NULL

View 3 Replies View Related

SQLserver (sqlsrvr.exe) Process Is Being Paged Out!

Jun 15, 2007

We are running SQL 2005 SP2 x64 on Windows 2003 SP2 X64.

Server specs:
Quad Core, each core runs at 2.33Ghz.
4GB memory
OS and SQL on RAID 1 set
Transaction log on RAID 1 set
Databases on RAID 5 set

-The SQL service account is a domain account with the "lock pages in memory" rights.
-The min memory use for sql server is 3000MBand the maximum is 2147483647MB.


For whatever reason, the sqlsrvr.exe process uses much less physical memory than the Virtual Memory (VM). When I look at the Task manager, I see 230MB for Mem Usage column and 300MB for VM size column under full workload. Why is the SQL server process being paged out? I have 3GB for it to use. Should I be concerned about the my SQL server?

I didn't configure the AWE as MS doesn't recomment using it on x64 bit systems. When I do "dbcc memorystatus", I get the following:

VM Reserved 4269536
VM Committed 196680
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

I thought granting "lock pages in memory" rights to the sql service account automatically enables AWE in x64 bit version of SQL 2005 standard server.

Thanks

View 5 Replies View Related

Sql Server Process Memory Has Been Paged Out

May 20, 2008


Hi All

I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.


2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.

The server configuration is

SQL 2005 Dev edition SP2 64bit
Win 2003 R2 SP2 Standard X64 editioin
RAM size is 16GB

Thanks.

View 4 Replies View Related

Filtering A Custom Paged Stored Procedure

Oct 22, 2006

Hi,    I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:        Peter Annandale-- Create date: 22/10/2006-- Description:    Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged]     -- Add the parameters for the stored procedure here    @startRowIndex int,     @maximumRows int,    @columnName varchar(20),    @filterValue varchar(20)ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER    FROM         (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + '  ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,            ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum        FROM dbo.NAME n             LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE            LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED        WHERE @columnName LIKE @filterValue        ) as NameInfo    WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter. 

View 1 Replies View Related

SQL 2012 :: Server Process Memory Has Been Paged Out

Apr 1, 2015

In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.

View 5 Replies View Related

A Significant Part Of Sql Server Process Memory Has Been Paged Out

Jul 26, 2007

On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?

Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.

View 9 Replies View Related

Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
 

View 3 Replies View Related

Openquery???

Sep 14, 2004

Hi All,

Does anyone know the syntax for an insert statement using Openquery in a stored procedure? All the examples I've seen are Select statements, but I want to send data to a linked server.

Would I be better off using DTS??

Thanks,

Greg

View 1 Replies View Related

Openquery

Dec 13, 2007



How to create linked server to Dbf,

How to openquery util step by step

View 1 Replies View Related

OPENQUERY

Sep 19, 2007



HI,
Can we use OPENQUERY with a parameter? Something like this:
SELECT * FROM OPENQUERY(@SOURCE_SERVER_NAME, 'Select * from dbo.FEED')


Please let me know at the earliest. Thanks a lot,.

Mannu.

View 3 Replies View Related

Openquery Problem

Apr 18, 2008

Hi everybody,
I am having a problem using a servername with '' in the openquery statement. I'd really appriciate if someone could suggest how I should be using it. Here is the query:
select * from openquery(sqldev est,'SELECT COUNT(*) FROM t_login WHERE username=''Tom''') into count
thanks in advance
devmetz

View 2 Replies View Related

Openquery() Search With NOT LIKE

May 20, 2008

Hi All,I want to use the following code to use 'NOT LIKE' clause for my File system search here is the code:SELECT Docs.FileNameFROM OPENQUERY(OPINIONSERVER, 'SELECT Filename FROM SCOPE() WHERE FREETEXT(''Any text not to search'')') AS Docs I want to use the above code for my html file system search similar to:ColumnName NOT LIKE N'%1971%'The confusing part for me is that in normal queries we use the column name to search in, but while searching in the file system using the FREETEXT() function how we exclude the words user dont want to search.I am using Dotnetnuke.

View 2 Replies View Related

Using Functions In Openquery

Jul 20, 2001

I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :

select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')

The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.

Any suggestions??

View 1 Replies View Related

Openquery Error

Nov 20, 2002

Hello,
if I am running this query and getting the error the below error

SELECT * FROM OPENQUERY(AS400ODBC,'
SELECT CBH_CREDIT_MEMO_NMBR,CBH_CREDIT_MEMO_DATE,CBH_CUST _TAX_CODE,
CBH_TAXABLE_TOTAL,CBH_CUST_NMBR FROM CREDITBLDH CR1 WHERE CR1.
CBH_CREDIT_MEMO_DATE = (SELECT MAX(CR2.CBH_CREDIT_MEMO_DATE) FROM CREDITBLDH CR2 WHERE CR1.CBH_CUST_NMBR = CR2.CBH_CUST_NMBR)')

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 7211 exceeds limit 600.]


What can I do for this?.
I can able to run fine without subquery
You suggestion appreciated.
T's,
Ravi

View 1 Replies View Related

Openquery For Oracle

Jan 22, 2003

Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'

My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi

View 3 Replies View Related

Openquery Erro Help

Feb 5, 2003

Hi,
I need your help to solve this error.
I am running the open query against Oracle server and this shows blow.


UPDATE Lp.dbo.CB
SET oldest_invoice_date = x.oldest_invoice_date
FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date,
I.ACCOUNT_NUM
FROM CDWD..CDW.INVOICE I
WHERE I.INVOICE_NUM >= 0
AND INVOICE_DATE IS NOT NULL
GROUP BY (I.ACCOUNT_NUM)
)x
WHERE account_num = x.ACCOUNT_NUM

I am getting the below error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oldest_invoice_date'.


Anybody give solution what I am doing wrong?.
Thanks,
Ravi

View 3 Replies View Related

OpenQuery() Help Needed

Aug 25, 2004

Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )

View 4 Replies View Related

OPENQUERY Parameter

Oct 11, 2005

I am trying to do the following:

SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], Expire Date
From Product Where [ExpireDate] > 2005-12-31')
GO

However the above sql statement doesn't get the dates greater than the date provided unless there are quotes around the date. How do I add a variable that will cover this date and include the identifiers to get the correct records

View 4 Replies View Related

OpenQuery Using A Variable

Jan 29, 2004

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.

View 5 Replies View Related

How To Execute A SP Using OpenQuery

Jun 12, 2008

Hi,

I am trying to execute a ServerB stored Procedure which takes Int as paramter using OpenQuery in ServerA.But this doesnt seems to working.
Please Help !!!!

DECLARE @param1 INT
SET @param1 1

SELECT *
FROM OPENQUERY(ServerB,'DBNAME.dbo.SP_NAME ''@param1''')

View 3 Replies View Related

SQL Openquery && Oracle

Jul 23, 2005

In SQL Server 2000 I have set up an Oracle linked server. When I runthe following query it runs fine:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')However the following query does not work:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')This error is returned:-Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalididentifier]OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Preparereturned 0x80040e14].Basically select * works ok, but if I specify the field(s) I need thenit errors. I have tried entering the field names in upper and lowercase but it makes no difference.My real problem is that some dates in the Oracle database are pre 1753which SQL server does not recognise so I need to convert (decode) them.Any help would be appreciated.ThanksChris

View 2 Replies View Related

OpenQuery Performance

Mar 31, 2008



Hi,
To access data from Linked server, which of the following is good Performance-wise:
1. Use of OPENQUERY to access data from Linked server
2. Using Direct query to access data using the four part Name of Linked server

Thanks in Advance.

View 1 Replies View Related

OPENQUERY &&amp; Permissions

Oct 23, 2007

I have a dataming stored procedure. it works fine on it own, thanks to help from this forum. However, when i try to run aggregate funtions on the table it returns using sql server I get:

"The OLE DB provider "MSOLAP" for linked server "DM" indicates that either the object has no columns or the current user does not have permissions on that object."

This works:

CALL Assemby.Namespace.MyFunction('[model]', 'db','table',0)

And this works:

SELECT SUM([prediction]) as value, count([prediction]) as count FROM OPENQUERY(DM,'
--DMX query that sproc produces and executes
')

but this:
SELECT SUM([prediction]) as value, count([prediction]) as count FROM OPENQUERY(DM,'
CALL Assemby.Namespace.MyFunction(''[model]'', ''db'',''table'',0)
')

gives this:

The OLE DB provider "MSOLAP" for linked server "DM" indicates that either the object has no columns or the current user does not have permissions on that object.

Could anyone point me in the right direction?

Thanks,
Adam

View 3 Replies View Related

Problem With OPENQUERY

Feb 18, 2007

Hello,
I've got a problem with OPENQUERY. When I use SQL Server Management Studio, I don't have any errors (I'm logged as Admin via Windows Auth.). When I try to use Adomd via ASP.NET (user - ASPNET with admin role, which is set in Man. Studio), I've got an error: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Any idea? Other querries work fine (i mean that querries which don't use OPENQUERY statement).SELECT
t.[Name],
[DecisionTreeModel].[Party],
PredictProbability([Party])
From
[DecisionTreeModel]
PREDICTION JOIN
OPENQUERY([VotingRecordsRDS],
'SELECT
[Name],
[Permanent Tax Cuts],
[Campaign Finance Overhaul]
FROM
[dbo].[VotingRecords]
') AS t
ON
[DecisionTreeModel].[Permanent Tax Cuts] = t.[Permanent Tax Cuts] AND
[DecisionTreeModel].[Campaign Finance Overhaul] = t.[Campaign Finance Overhaul]
ORDER BY PredictProbability([Party]) DESC

Thank you in advance for any help with this.
MM

View 4 Replies View Related

Issues With OpenQuery

May 19, 2008

We are running the following query against a MYSQL database that runs a third party software.


SELECT Email, CONVERT(INT, count) AS clicks, date AS Last_Clicked_Date
FROM OPENQUERY(MYSQL, 'SELECT Email, SUM(count) count, MAX(date) date FROM tracking WHERE action="click" GROUP BY Email')

We are upgrading that software to the latest version. This requires a migration from MYSQL to PostGres.
There are some schema changes involved

Current MYSQL field name New PostGres field name
email email_address
date tracking_date
action action_name

I've modified the query to use the new fields

SELECT Email_address, CONVERT(INT, count) AS clicks, date AS Last_Clicked_Date
FROM OPENQUERY(MYSQL, 'SELECT Email_address, SUM(count) count, MAX(tracking_date) date FROM tracking WHERE action_name="click" GROUP BY Email_address')

and I get the following error message -

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "test" reported an error. The provider reported an unexpected catastrophic failure.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "test".



When I run this query it works -

select email_address, tracking_date

from openquery(test, 'select * from tracking')


What am I doing wrong?

Thanks

View 19 Replies View Related

OpenQuery With Inner Join

Feb 13, 2008

Hi All,
I would like to say Thank you in advance, i have a big problem with a deadline coming on friday, Here is my problem, i want to access data from remote server, the server name is "SeverName" just for some reason, and my table name is T1, T2, T3, T4. and My Comlumn Names are, Col1, Col2, .......Col11. Here is the code i used and the error i got: Note that T1=Table one, DB =DataBase, Col = Column.


Select

Col1 ,

Col2,

Col3,

Col4,

Col5,

Col6,

Col7,

Col8,

Col9,

Col10,

Col11



From

Openquery (ServerName , '

Select



T1.Col1,

CAST(substring(T1.Col2,1,255) AS Varchar(255)) AS Col2,

CAST(substring(T1.Col3,1,255) AS Varchar(255)) AS Col3,

CAST(substring(T1.Col4,1,255) AS Varchar(255)) AS Col4,

T1.Col5,

CAST(substring(T1.Col6,1,4000) AS Varchar(4000)) AS Col6,

T1.Col7,

CAST(substring(T1.Col8,1,255) AS Varchar(255)) AS Col8,

T3.Col9 As Col9

CAST(substring(T2.Col10,1,255) AS Varchar(255)) AS Col10,

T4.Col11 AS Col11,

FROM
DB.T1 a

Inner Join
DB.T2 b

on b.T2ID = a.T1ID

Inner Join

DB.T3 c

on c.T3ID = a.T1ID"

Inner Join

DB.T4 d

On d.T4ID = a.T1ID

')


Here is the error i got:

€œ[OLE/DB provider returned message: No query has been assigned to this statement.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. €œ


Please help, anyone who could have any alternative it will be fine, if the code is wrong let me know, what ever reason i will take it.

Thank you again,

Sami

View 10 Replies View Related







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