Configure Component 'Ad Hoc Distributed Queries'
Jan 25, 2008
When i try to use the OPENROWSET function on a particular server i get the error:
"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.."
Does anyone know how exacly you do configure this?
View 7 Replies
ADVERTISEMENT
May 30, 2007
I am trying to configure distributed transaction and XA support using Microsoft SQL Server 2005 JDBC Driver. I have coppied SQLJDBC_XA.dll from XA directory and placed in my sql server binn directory and trying to run the script xa_install.sql from binn directory with command as below :
C:Program FilesMicrosoft SQL Server80ToolsBinn>
osql -U sa -n -P admin -S localhost -i C:JavaLibrariesMS SQL Driversqljdbc_1.2enuxa xa_install.sql
But I am getting error saying :
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
when I replaced local host with the machine name it gives error :
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
where in I am able to test connection from Websphere using the same connection.
Please help some one ....... I am in URGENT need.... I need to enable XA suport for my application to run........
Thanks ----
View 2 Replies
View Related
Oct 3, 2001
I have a ODBC data source setup for the AS400DB2 which is AS400JDE
The linked server has been created and all the tables are visible within enterprise manager.
If I had a table F0101, datasource = AS400JDE and linked server of AS400
How would I query it.
I gave tried AS400.AS400JDE.DBO.F0101, but i get the following error message.
OLE DB provider 'MSDASQL' does not contain table '"as400jde"."dbo"."f0101"'.
View 1 Replies
View Related
Jan 20, 2000
i added a linked sql 7 production server to my sql 7 development server so i could perform some data comparisons between the two
servers. after i linked in the server, i was able to see all the icons for the various tables in the production server. i went to query
analyzer and executed the following openquery:
select * from openquery(itdev_s08, 'select * from dbo.tablename')
it returned all the rows in the table. however, i want to comapre several different values so i needed a better way to query.
i then decided to try the distrbuted query below:
select * from linkedserver.dbname.dbo.tablename
however instead of a result set i got the error message below:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"dbname"."dbo"."tablename"'.
i went to BOL and it told me the only reason i would get this error is if the object did not exist or if i did not have permission
to use it. however, i know the object exists and i have the appropriate permission becuase i can obtain a result set from the
first query in the same query analyzer window. is my syntax incorrect? what am i doing wrong?
thanks!
tammy moisan
View 1 Replies
View Related
Aug 30, 2006
I am getting the following error when with SQL Express.SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on... I tried sp_configure 'Ad Hoc Distributed Queries', 1 but got the following error The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have.... Could someone help me out with this?Thanks - Mark
View 3 Replies
View Related
Nov 24, 1999
Hi,
I am trying to use linked servers (all SQLServer 6.5 and 7.0). When I issue a distributed query utilizing the four-part qualified table name for the distributed machine and using the LIKE operator, the execution plan shows that the remote query is returning ALL the rows from the remote distributed server, then performing the LIKE filter locally on my server! Needless to say, performance sux!
Any ideas what I may be doing wrong or what settings I am unaware of?
Example query:
select * from server.database.schema.table where column1 like 'A%'
Thanx in advance for any help.
View 1 Replies
View Related
Feb 6, 2002
Hi there,
I am trying to link one sql server to other sql server(version 7.0). I was able to link server1 to server2 by creating an odbc source and am able to see the tablenames when i click on the linked server tables.
My problem is..when i am trying to query on these tables its giving me error saying "OLE DB provider 'MSDASQL' does not contain table xxxxxx"
i am using select * from servername.tablename.dbo.tablename.
Any help on this will be appreciated.
Ravi
View 1 Replies
View Related
Aug 17, 2006
Hi,
Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:
SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')
I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:
SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..
Any suggestions??????
Rgds
Bob
View 1 Replies
View Related
Sep 20, 2007
Hi,
I'm deploying an OLE DB provider to be used as linked server in on customer pc. The provider works ok on both machines I used to test it, one with Vista (64-bit), the other with XP (32-bit). Both machines have SQL server express 2005 SP2 (9.0.3042) installed.
Now, customer machine is again Vista (64-bit) with SQL server express 2005 SP2 (9.0.3054), and customer tested it on another Vista machine with SQL server express 2005 SP2 (9.0.3042). On both machines, following error occurs when SELECT statement is issued to the provider (though linked server creation runs smoothly):
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
1) registry entry for ThreadingModel is Both
2) the provider works on my side
3) I can't seem to instantiate the provider on customer side even when I disallow provider to be inproc for SQL server, which would hint on some registration issue (maybe some leftovers on my local machines that are not present on customer side)
What is going on, again..?
Thanks for ideas..
L.
View 1 Replies
View Related
Mar 13, 2008
We are migrating a database that makes use of distributed queries. In other words, it queries data from other databases on the same server, as well as from databases on linked servers.
The use of linked servers as well as linked databases (for lack of a better term) presents a challenge since we will need to register various new linked servers in the target environment. Ideally, we would like the database to be portable such that we will not have to worry about registering linked servers regardless of where it is hosted.
Is there a way to write distributed queries so that do not rely on linked servers/databases? For example:
SELECT * FROM [ServerName].[Database].[Owner].[TableName]
If there is such a method, it would make our database much more portable in terms of server migration.
TIA.
El Salsero
View 1 Replies
View Related
Apr 9, 2008
Hi,
My company told me to prepare document on long runnings queries.
We have components and developed in vb6 and some methods are doing call to as400,db2,informix.
Here I am can not know how tackle and do reaserch on the same.
Question infront me as:
1. why timeout not happen on com+ components.
2. how to prepair document for my reaserch.
Thanks,
Bimal
View 2 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Feb 29, 2008
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live.
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
The stored procedure uses linked servers and a transaction.
We're using the following transaction code in the stored procedure
BEGIN
BEGIN TRANSACTION
BEGIN TRY
---
procedure stuff here
---
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
END
I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517
The link includes a workaround which is the following:
"To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "
How do I use the SQLNCLI provider to fully consume any rowsets?
View 11 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
May 6, 2004
Hello,
does the .NET framework support distributed transactions somehow?
The SqlConnection/SqlTransaction classes doesn't seem to support them...
TIA. -julio
View 2 Replies
View Related
Mar 28, 2001
Hi Folks,
Is there anyway of running a Store Procedure in Database A that's going against database B? I have a store procedure that does a select on table A in database A and a select on table B in database B.
The problem is that the user have exec rights to the store procedure, which is an object in database A. The store procedure won't run because of permission rights to table B in database B.
Is there anyway of queryiny table B without giving the user select rights to that table? Anyone out there had the same problem?
thank you
Joe R.
View 1 Replies
View Related
May 24, 2001
Hi to everyone!
Any experience handling a Store Procedure that has distribution transactions?
Here is a story.
We have a store procedure #1 that inserts or updates data in two tables A and B on SQL Server 7.0 then it calls another store procedure #2 that:
1. updates C table in the same database on SQL Server from Oracle Server
2. deletes that record on Oracle Server
3. inserts a record on Oracle from table A
4. inserts the same record into table D on SQL Server from Oracle Server.
People who wrote store procedure #1 put Commit Tran before executing #2.
The questions are:
1. Is it correct that we don't have Commit Tran for #2?
2. What do you think about Nested Begin - Commit Tran?
Any help is highly appreciated.
Sima
View 2 Replies
View Related
Feb 4, 2004
Hi,
I am trying to execute a proc on Linked Server. Now as the Linked server name starts with 2, all of the following fails...
Any help to make it work highly appreciated...
exec [2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail]
exec 2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail
exec "2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail"
View 4 Replies
View Related
Aug 3, 1998
Does SQL Server actually support distributed transactions over, say, two tables in different databases but that reside on the same server? When I try to execute such a transaction in which one part of the transaction violates referential integrity and hence should not be executed, causing the rest of the transaction to, supposedly, rollback, the transaction does not roll back but instead produces an error message and executes the second, valid half of the transaction anyway.
Any help or suggestions gratefully accepted.
View 1 Replies
View Related
Mar 25, 2002
Hi Guys.
Many posted this message and no one anwered. i am facing the same problem now.
Got the error message
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
My MSDTC is on. I had setup linked server using OLEDB.
Microsoft did'nt give enough info or solution for this. ANybody faced this prob and solved it?
Any suggestions , comments, solutions?
-MAK
View 1 Replies
View Related
Apr 17, 2001
I have a procedure where it runs a procedure on another server and returns the results to the calling procedure and dumps it into a temp table..
I get the following message:
-----
Server: Msg 7391, Level 16, State 1, Procedure proc1, Line 60
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]
----
But both the servers are running the distributed transaction corordinator
example:
create procedure dbo.proc1
@param1 int
as
create table #temp
(col1 int
col2 varchar(255)
)
insert into #temp
EXEC server.database.dbo.proc2 @param1 = @param1
go
View 4 Replies
View Related
Sep 28, 2004
Hi all,
I am trying to merge data of 2 tables on different servers with an insert statement.
INSERT INTO SERVER1.db.owner.table
select s2.* from SERVER2.db.owner.table as s2
LEFT JOIN SERVER1.db.owner.table as s1
ON s1.key=s2.key
where s1.key is null
I got this error.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
while the select query is giving the result.
I have done simmilar inserts on some other tables which worked fine
I have created sp_addlinkedserver.
and DTC set on both servers.
any help will be greatly appreciated
View 3 Replies
View Related
Dec 27, 2004
Hi All,
I am trying to use distributed transaction (using linked Server).
But getting the folloing error..
Some one please help...
following is the error...
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
View 7 Replies
View Related
Oct 19, 2006
Hey,
I have a Sql 2000 w/ SP4 on Server 2003 running an sp that inserts and updates to a 2005 64 bit DB w SP 1 on Server 2003. The SP has worked forever. No code or server changes. Now all of the sudden I randomly get this error. It will work, then fail 2Xs, then work, then fail. Very bazaar.
Any help is appreciated! I have done the MSDTS, ran catalog sps, forced TCP/IP. I'm lost.
If I run the sp on the 64bit box it works fine, but on the 32bit box now, I get the frequent error below.
[OLE/DB provider returned message: Unspecified error]
Msg 8525, Level 16, State 1, Procedure sp_xxx_xxx_xxx, Line 1496
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
The line number in the error will change as well and is not always consistent.
Thank you!!!!
View 1 Replies
View Related
Jun 27, 2007
Hi everybody.
I have a question about the database performance.
Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.
which of these solutions have better performance? Distributed databases or single one?
Thanks
View 4 Replies
View Related
Jul 11, 2006
Hi again,
I do not know when to use Distributed Transaction.
Would you please give an example about its usage or give a tutorials about this ?
Thanks
View 14 Replies
View Related
Mar 27, 2006
Hi There
I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.
BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.
I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.
When i execute the update or insert i get the error:
Cannot start a distributed transaction.
I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.
Can anyone please reccomend a good link as to how to get distributed transactions to work.
Thanx
View 3 Replies
View Related
Mar 8, 2007
I work in the data warehouse team of my organization. We are currently rearchitecting our server environment. One of the ideas on the table is to devote a separate server for ETL processing. The databases would reside on several other servers. The ETL server would run the SSIS packages. I'm questioning if this would be a good idea.
The database servers would continue to carry the load of the query processing for the ETL. But with the ETL process on a separate server, the resultsets would need to go over the network for the SSIS package to then work on them. Plus, they would then have to go back over the wire to the destination server.
Are there advantages to this setup? Does this setup have better scalability? Or, would it be better to run the ETL from either the source or destination database server?
Thanks for your consideration.
View 1 Replies
View Related
Mar 7, 2007
How to make a SQL Server database distributed?
View 1 Replies
View Related
