OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,

Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.

Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).

I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]

In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.

I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!

- The Code -

---

create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

---

Thanks in advance...

Hi,

normally the OLEDB provider is expecting to get something back from the query. So try to put in either a return 0 or a simple Select or string stating ('Statement executed') at the bottom of the query.

BTW, why don′t you just use the select statement rather than creating tables and so on.. select 1 union select 2 ?

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de

sql

OPENQUERY problem with dynamic result set

I am having a problem with OPENQUERY in SQL2005.
SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
This generates an error along the lines of:
The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
that either the object has no columns or the current user does not have
permissions on that object.
I am pretty sure this is becuase my procedure returns a dynamic
resultset with a dynamic number of columns.
I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
other suggestions; however, I still get the same error. I am guessing
possibly, becuase my procedure in turn calls another dynamically
executed OPENQUERY MDX query against an OLAP data source.
The whole reason I am doing this is becuase I need to get the results
of the stored procedure into a table for further manipulation.
Modifying the underlying procedures is not really an option.
My procedure returns a dynamic number of columns so I can't create a
temp table ahead of time to stick the results into.
I have been going around in circles for a while on this one. Any
advice would be much appreciated.Hi
If you granted EXECUTE permission to stored procedure which performs dynamic
sql , it will not be enough , you'll have to grant permissions on the
object/ underlaying tables
However , in SQL Server 2005 you are be able to perfom EXECUTE AS ( If I
remember well) as more power user
<jalbenberg@.yahoo.com> wrote in message
news:1148426602.112398.67180@.i39g2000cwa.googlegroups.com...
>I am having a problem with OPENQUERY in SQL2005.
> SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
> This generates an error along the lines of:
> The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
> that either the object has no columns or the current user does not have
> permissions on that object.
> I am pretty sure this is becuase my procedure returns a dynamic
> resultset with a dynamic number of columns.
> I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
> other suggestions; however, I still get the same error. I am guessing
> possibly, becuase my procedure in turn calls another dynamically
> executed OPENQUERY MDX query against an OLAP data source.
> The whole reason I am doing this is becuase I need to get the results
> of the stored procedure into a table for further manipulation.
> Modifying the underlying procedures is not really an option.
> My procedure returns a dynamic number of columns so I can't create a
> temp table ahead of time to stick the results into.
> I have been going around in circles for a while on this one. Any
> advice would be much appreciated.
>|||Sorry if my post was misleading. This is not a permissions issue - the
queries run fine outside of the OPENQUERY context.
The problem is the dynamic recordset that comes back with an unknown
number of columns, so OPENQUERY cannot properly prepare the statement.

OPENQUERY problem with dynamic result set

I am having a problem with OPENQUERY in SQL2005.
SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
This generates an error along the lines of:
The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
that either the object has no columns or the current user does not have
permissions on that object.
I am pretty sure this is becuase my procedure returns a dynamic
resultset with a dynamic number of columns.
I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
other suggestions; however, I still get the same error. I am guessing
possibly, becuase my procedure in turn calls another dynamically
executed OPENQUERY MDX query against an OLAP data source.
The whole reason I am doing this is becuase I need to get the results
of the stored procedure into a table for further manipulation.
Modifying the underlying procedures is not really an option.
My procedure returns a dynamic number of columns so I can't create a
temp table ahead of time to stick the results into.
I have been going around in circles for a while on this one. Any
advice would be much appreciated.Hi
If you granted EXECUTE permission to stored procedure which performs dynamic
sql , it will not be enough , you'll have to grant permissions on the
object/ underlaying tables
However , in SQL Server 2005 you are be able to perfom EXECUTE AS ( If I
remember well) as more power user
<jalbenberg@.yahoo.com> wrote in message
news:1148426602.112398.67180@.i39g2000cwa.googlegroups.com...
>I am having a problem with OPENQUERY in SQL2005.
> SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
> This generates an error along the lines of:
> The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
> that either the object has no columns or the current user does not have
> permissions on that object.
> I am pretty sure this is becuase my procedure returns a dynamic
> resultset with a dynamic number of columns.
> I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
> other suggestions; however, I still get the same error. I am guessing
> possibly, becuase my procedure in turn calls another dynamically
> executed OPENQUERY MDX query against an OLAP data source.
> The whole reason I am doing this is becuase I need to get the results
> of the stored procedure into a table for further manipulation.
> Modifying the underlying procedures is not really an option.
> My procedure returns a dynamic number of columns so I can't create a
> temp table ahead of time to stick the results into.
> I have been going around in circles for a while on this one. Any
> advice would be much appreciated.
>|||Sorry if my post was misleading. This is not a permissions issue - the
queries run fine outside of the OPENQUERY context.
The problem is the dynamic recordset that comes back with an unknown
number of columns, so OPENQUERY cannot properly prepare the statement.sql

OPENQUERY ISSUE

I am trying to pass a variable to an openquery stmt within a proc. The
following worked a couple of times, but I am receiving the error below now ?
Any suggestions on a method to pass the variable into the OPENQUERY Statemen
t
?
CREATE PROCEDURE usp_GetData (@.REQUEST_ID as varchar(10))
AS
declare @.RESULT varchar(20)
Declare c Cursor For
SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM oracle_view
WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Open C
Fetch c into @.request_ID, @.RESULT -- Get First record data
While @.@.FETCH_STATUS = 0
Begin
INSERT INTO RESULTS (REQUEST_ID, RESULT)
VALUES (@.request_ID, @.RESULT)
Fetch c into @.request_ID, @.RESULT -- Get Next Record
End
Close C
Deallocate C
GO
-- Error received.
Server: Msg 7330, Level 16, State 2, Procedure usp_GetRequestID_Data, Line 2
2
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01722: invalid number> SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM
> oracle_view WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Usually it helps to manually evaluate the expression you are trying to execu
te.
DECLARE @.TEST VARCHAR(200)
SET @.TEST = 'SELECT REQUEST_ID, RESULT FROM oracle_view WHERE REQUEST_ID=''+
@.REQUEST_ID+'''
SELECT @.TEST|||Hi,
as far as I know, OPENQUERY cannot accept variable.
normally what I do is to create a dynamic sql string first then only execute
it.
In BOL, you should be able to find something like this:
--
OPENQUERY
Executes the specified pass-through query on the given linked server, which
is an OLE DB data source. The OPENQUERY function can be referenced in the
FROM clause of a query as though it is a table name. The OPENQUERY function
can also be referenced as the target table of an INSERT, UPDATE, or DELETE
statement, subject to the capabilities of the OLE DB provider. Although the
query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
Arguments
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
Remarks
OPENQUERY does not accept variables for its arguments.
--
As you can see, variable is not allowed. I am not so sure about SQL Server
2005.
I get this from SQL Server 2000 BOL.
hope this will help.
Leo

Openquery from SQL Server to Oracle error

I want to insert records into an Oracle 8.03 database from MS SQL 2000. I have created a link and have used OPENQUERY to successfully query my Oracle tables. See example, DEV is the LINK name. I need to insert and update records from MS SQL to Oracle and also I need update MS SQL from
Oracle. Can you please give me a working example of insert and update?

example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')

It makes sense that this update would work, but it got WORSE after running this:

update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL

Server is terminating this process.

Connection Broken

From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.

The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.

Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO

UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY

UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3

There are 3 articles on technet that may help or are just a wild goose chase.

PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:

FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:

FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY

UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3

Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:

UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1

Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):

UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1

I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc

I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.

I will try the syntax you suggested early next week (the server crashed and needs new drives).

What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.

In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.

SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.

Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.

FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106

Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.

OpenQuery Error?

Hi All,

I have an Openquery insert within a trigger. When i go to check the syntax it errors with the following message...

Error 403. Invalid Error for Data Type. Operator equals add, type equals varchar.

Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'

All variables are varchar except for @.KEYE which is Varbinary and this is the one that is causing the error on, because if I take it out the syntax is correct.

Any ideas why this occurs and how do I add a varbinary variable to the statement??

Regards
Anthonyyou are trying to concat string with varbin - this will not work. you have to convert to varchar before...|||Originally posted by msieben
you are trying to concat string with varbin - this will not work. you have to convert to varchar before...

How do I do this?? Can you show me an example.|||Originally posted by aljubicic
:
Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'
:


the code you posted will build the insert statement togeter and put it into @.TSQL2 as a varchar. so everything you put toghether needs to be varchar or to be converted to (either by the sql-server or by using "convert(". i don't know what you really want to do - but look alt sp_executesql in BOL. you can store your statement into varchar and use parameters, which will be replaced at runtime. so you don't have to push your values list into the varchar at all.

Openquery Error

Hi I am trying to connect to teradata using SQL Query Analyzer
Teradata is linked through our Linked Server
Below is the query I run
select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
mktg is schema is teradata database
The error it gives me is:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
returned 0x80040e14].
Did you try the exact command on your terradata ? Are you sure that you
are connected to the right entity (database etc, don=B4t know the
details of that one) ?
HTH; Jens Suessmeyer.
|||Jens wrote:
> Did you try the exact command on your terradata ? Are you sure that you
> are connected to the right entity (database etc, don=B4t know the
> details of that one) ?
> HTH; Jens Suessmeyer.
I can access the same teradata database using Queryman and also using
SAS no problems. I have access to three databases in Teradata and out
of three i can acess one easily using Query Analyzer but other two it
gives me error as described above.
Since I can connect to teradata through Queryman and SAS that means my
ODBC drivers and access to these database both are fine. But I am
failing to understand why Openquery is failing.
|||pradeep_raina@.hotmail.com (pradeep_raina@.hotmail.com) writes:
> Hi I am trying to connect to teradata using SQL Query Analyzer
> Teradata is linked through our Linked Server
> Below is the query I run
> select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
> mktg is schema is teradata database
> The error it gives me is:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDASQL'.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
> returned 0x80040e14].
The errors from queries to linked servers are often very difficult to
understand. Error 0x80040e14 is DB_E_ERRORSINCOMMAND, and the explanation
I find in the description for ICommandPrepare::Prepare is "The command text
contained one or more errors. Providers should use OLE DB error objects to
return details about the errors."
My interpretation is that the command fails for some reason.
Now, I don't know Teradata at all, but it looks a little funny
when you say that mktg is your schema, and then you say that you
have access to three databases on Teradata. Shouldn't you specify
the database as well? My guess is that your command fails, because
Teradata cannot find mktg.vtcsr in the database where it is looking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

OPENQUERY end-of-file error

I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '

SELECT

*

FROM

(

WITH a AS

(

SELECT

a,

b,

c

FROM

table1

)

SELECT

x,

y,

z

FROM

a a1

INNER JOIN

table2 t2

ON a1.a = t2.a

)

')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.

I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.

Hope that helps,

John

|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?

|||

Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.

|||

Did this link offer you any help?

http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.

Thanks,

John

OPENQUERY end-of-file error

I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '

SELECT

*

FROM

(

WITH a AS

(

SELECT

a,

b,

c

FROM

table1

)

SELECT

x,

y,

z

FROM

a a1

INNER JOIN

table2 t2

ON a1.a = t2.a

)

')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.

I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.

Hope that helps,

John

|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?

|||

Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.

|||

Did this link offer you any help?

http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.

Thanks,

John

sql

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
Thanks
I have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:
> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>sql

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event lo
g
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within
10
> where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of err
or
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt erro

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?

Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.

ThanksI have the same problem any help would be appreciated :D
|||

We get the same "select *" error message and found that it was probably resulting from a unisys sentinel script; practically the exact verbage could be found in several such .vbs scripts. Certainly some event is triggered and the sentinel software attempts to run one of the scripts and fails. The failure may be due to the sentinel software trying to perform a sql2000 task on a sql2005 server. We're still hunting for an exact cause though.

I hope that this points you in the right direction for your particular case.

Opening up SQL 2005 Management studio generates Winmgmt erro

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?

Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.

ThanksI have the same problem any help would be appreciated :D|||

We get the same "select *" error message and found that it was probably resulting from a unisys sentinel script; practically the exact verbage could be found in several such .vbs scripts. Certainly some event is triggered and the sentinel software attempts to run one of the scripts and fails. The failure may be due to the sentinel software trying to perform a sql2000 task on a sql2005 server. We're still hunting for an exact cause though.

I hope that this points you in the right direction for your particular case.

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank Jones
Hi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank JonesHi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Opendatasource

I was able to use the following select qry with an OpenDataSource, but I
recieve and error when I change it to an Update qry.
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
but when I change it to and Update it errors out. See code below
update h
set h.LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
I get the following error; Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'h'.
Any suggestions.
Thank you in advance for your helpdouble post by mistake?
"bhorwitz" <bhorwitz@.discussions.microsoft.com> wrote in message
news:33E09107-36E6-4131-A5F8-7D1053521CD3@.microsoft.com...
> I was able to use the following select qry with an OpenDataSource, but I
> recieve and error when I change it to an Update qry.
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> but when I change it to and Update it errors out. See code below
> update h
> set h.LOCATION = b.Location
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> I get the following error; Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'h'.
> Any suggestions.
> Thank you in advance for your help|||try this..
untested..
update h
set LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1

Open stream Error

Hi
Has anyone come across this error before? and if so do you know what's
causing it.
"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Found 1 open streams. All streams must be closed at this moment. "
It occurs when running submitted reports in Report Manager, these reports
run OK in the Report Designer.
Many ThanksHi,
I am having the same problem. Have you found out what's causing it?
cheers
"Matt" wrote:
> Hi
> Has anyone come across this error before? and if so do you know what's
> causing it.
> "An internal error occurred on the report server. See the error log for more
> details. (rsInternalError) Get Online Help
> Found 1 open streams. All streams must be closed at this moment. "
> It occurs when running submitted reports in Report Manager, these reports
> run OK in the Report Designer.
> Many Thanks
>|||Hi
It seemed to be a problem with my SQL server rather than Reporting Services.
So I got our DBA to restart the server, all the reports now work OK.
"Bach" wrote:
> Hi,
> I am having the same problem. Have you found out what's causing it?
> cheers
> "Matt" wrote:
> > Hi
> >
> > Has anyone come across this error before? and if so do you know what's
> > causing it.
> >
> > "An internal error occurred on the report server. See the error log for more
> > details. (rsInternalError) Get Online Help
> > Found 1 open streams. All streams must be closed at this moment. "
> >
> > It occurs when running submitted reports in Report Manager, these reports
> > run OK in the Report Designer.
> >
> > Many Thanks
> >|||Restarting the server didnt do it for me. Applying SP1 fixed it though.
Thanks
"Matt" wrote:
> Hi
> It seemed to be a problem with my SQL server rather than Reporting Services.
> So I got our DBA to restart the server, all the reports now work OK.
>
> "Bach" wrote:
> > Hi,
> >
> > I am having the same problem. Have you found out what's causing it?
> > cheers
> >
> > "Matt" wrote:
> >
> > > Hi
> > >
> > > Has anyone come across this error before? and if so do you know what's
> > > causing it.
> > >
> > > "An internal error occurred on the report server. See the error log for more
> > > details. (rsInternalError) Get Online Help
> > > Found 1 open streams. All streams must be closed at this moment. "
> > >
> > > It occurs when running submitted reports in Report Manager, these reports
> > > run OK in the Report Designer.
> > >
> > > Many Thanks
> > >

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

Open SSIS project error: Unable to cast COM object of type

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?

TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Did you get a solution to this problem? If so could you please post it? I'm having the exact same problem on my development server.

Sincerely

Svein Terje Gaup

|||

I have the same problem as well, anyone know how to solve it?

Many Thanks.

Warren

|||

i got it fixed without knowing how to fix it. it is kind of rediculous.

Can anyone from SSIS team or any SSIS guru answer this ?

Steve

|||

I've no idea but it seems some realated with the apartment for a COM object, maybe STA which is being used

in another thread.

My wonder is why BIDS needs to make a RCW between managed code and unmanaged code when you're going to open it.

|||

Have seen this error several times opening BIDS.

Just close BIDS and open it up again, error gone. Don't know why. I think it happens when I'm a little bit impatient and try to open a recent project while BIDS is not fully up and running yet.

It's a bit annoying, but I don't think it's a serious problem.

Pipo1

|||Have this problem too - any fixes?|||

It seems SQL 2005 does not do a good job to tell us what the error is...

Not sure if you guys did the same task I did, I got the same error when I tried to add a Maintenance Plan. I tried to find the reason/solution but no luck.. and I fixed it not because I found the answer and it is all about security...

My story is...

I set up two clustered SQL 2005 servers and tried to add a maintanience plan by using sa account authentication. However, sa account is not a network account but a SQL account. So I kept getting the error no matter I close/open how many times. And unfortunately SQL 2005 does not report this error "precisely" to me to identify where the problem is... So instead of using my local Management Tools and sa accoutn authentication, I logged in to another server on the same network domain as those two clustered servers. Then I used Management Tool installed on it and Window Authentication account which is the admin account of two clustered servers, and hahaha, it just works like I want it to.

Hope this helps,
Jet

|||Hello

Does anyone have the solution to this issue?
Everytime I open a new SSIS package, i get the error
"Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface...failed due to the following error: Interface not registered."

Which interface do I need to register?

Doesnt happen for other packages, only SSIS.

Thanks!