I am doing some work testing the running time of some dynamic SQL
statements on a remote machine. What I would like to do is execute
the SQL on the remote machine, without returning the result set to the
calling machine (this would skew the results, as my connection to the
remote machine is rather slow).
I believe SET ROWCOUNT 0 would work in principle, but the
documentation says:
"Causes Microsoft® SQL Server™ to stop processing the query after the
specified number of rows are returned."
I don't want processing to stop until all the records have been
identified. I also don't know if "SET ROWCOUNT" works with openquery
(althought a non-openquery solution would also be acceptable).
The code will be running in a stored procedure, if that matters.
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13 Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset  (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am usingÂ
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
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.
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
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.
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.
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
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
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
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 + ' ' ' ' )
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
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?
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''')
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
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
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."
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.
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
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".
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.
How much performance hit should I expect to use FOR XML to generate XML string directly from SQL Server 2000? I like this approach because it is simple and easy. However, others told me that it has a big performance hit. They prefer to use a COM+ component to create XML string from a Resultset. Any suggestions? Thanks.
Hi,I have 3 tables as follow :Kanji :kanji_id....References :ref_id....KanjiRefskref_idkanjikref_idrefkref_value....So, there is a many-to-many relationship between Kanjis and References(one kanji may have more than one reference type, and a reference typemay be set to more than one kanji).For example, one kanji may have the value 'abc' for reference type #1,and the value 'def' for reference type #2, another kanji may also havethe reference type #1, but have instead the value '123' and so on...I have two questions :1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)within their list of references?2) How to get the value of all the kanjis that have the reference #3,but still get other kanjis that do not have the reference #3...forexample, if I had 3 kanjis, with the two first having values 'abc' and'def' for reference #3, and the last one having no reference #3, I'dlike to get that resultset :kanji_id kref_value1 'abc'2 'def'3 NULLI manage to get all the kanjis that have reference # 3 with thefollowing query :SELECT kanji_id, kref_valueFROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_idWHERE kref_idref = 3however, this obviously does not include kanjis having no reference#3...any help would be greatly appreciated, thanks! :)ibiza
We have a MS SQL database with an Oracle linked server 'ALTTEST' We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction? I've tried applying the code below, but it doesn't work. Any help appreciated. BEGIN TRAN SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX) COMMIT
I'm performing a particular word Search in MS Word, Text, PDF docs and displaying the results through Index Server linked to SQL Server when it is matched. For which I'm using Openquery in the stored procedure which works fine in Query Analyzer of the SQL Server but doesn't work ( displays none of the results) when i call it from the ASP.NET Page. I am not able to figure out Where and What is the problem? The Stored Proc which is i'm using is shown below Any help will be greatly appreciated. Thanks for your time and help in Advance CREATE PROCEDURE SelectIndexServerCVpaths ( @searchstring varchar(100) ) AS SET @searchstring = REPLACE( @searchstring, '''', '''''' ) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'FileSearchResults') DROP VIEW FileSearchResults EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM OPENQUERY(FileSystem,''SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE('''' "c:inetpubwwwrootsap-resourcesUploads" '''') WHERE FREETEXT(''''' + @searchstring + ''''')'')') SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = F.PATH AND C.DefaultID=1 GO which works with followin stat in Query Analyzer Exec SelectIndexServerCVpaths @searchstring = 'The Search text' but doesn't work when i connect it to a Datagrid in my ASP.NET Page objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn); objcmd.CommandType = CommandType.StoredProcedure; objcmd.Parameters.Add("@searchstring",strsearchstrings); objConn.Open(); objRdr = objcmd.ExecuteReader(); dgcvs.DataSource=objRdr; dgcvs.DataBind(); objRdr.Close(); objConn.Close();
select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG where DEPTNAME=@deptname")
Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703 ]
Is there a way to use nested OPENQUERY's in a DTS package??
I have the following SQL statement (whichs execute without error) that I would like to make a DTS package out of:
INSERT tbl_Sku SELECT AX.sku_id, AX.style_id, AX.style_color_id, AX.style_size_id, AY.color_id, AY.size_master_id from OPENQUERY("SVR-1",'SELECT sku_id, style_id, style_color_id, style_size_id FROM merch.dbo.sku') as AX, OPENQUERY("SVR-2",'SELECT sku_id, style_id, color_id, size_master_id FROM ma.dbo.sku') as AY Where AX.sku_id = AY.sku_id
The problem I am having is the connection object of the DTS package. It will not allow an OPENQUERY inside an OPENQUERY. How do I set up a third comnnection??
