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%'
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"'.
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?
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
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.
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?
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:
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)
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.
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?
Just a quick question that I really couldn't find using google or anything because it was too bland. How do you return the whole database while still using the LIKE operator. I have a textbox that you can use to search the database but I want all the data to be displayed underneath the box so you can "browse" the data if you don't know the name of what you are looking for. What would I put as the default value? I used just defaultvalue="%" and that worked in 1 area, but not another. Is this the right way? Thanks, Chris
I have a query and for the life of me I cant figure it out. I have a web form with a number of textboxes on it e.g. Title, subtitle, isbn. I also have a search button. This search page allows a user to enter any combination of fields in the form and then when they click the search button, a datagrid is diaplyed showing the results (gets the results from a SQL DB). I have got the code working if the user selects say one item, but I dont know how to allow it so that the user can enter any combination of items in the form and the search button will search for it based on what the user says. I think im gettng confused with my logical operators in the sql statement. If any one can take a look I would be greatful.public void BindData(){String @BookID = TxtBookID.Text;String @Title = TxtTitle.Text;String @Subtitle = TxtSubtitle.Text;String @ISBN = TxtISBN.Text;String @AuthorFName = TxtForename.Text;String @AuthorLName = TxtSurname.Text; SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID_Internal, Title, Subtitle, ISBN, Edition, Publication_Date, Author.First_Name, Author.Last_Name FROM Book, Author WHERE Book.Author_ID = Author.Author_ID AND Book.Book_ID_Internal = '" + @BookID + "' AND Book.Title = '" + @Title + "' AND Book.Subtitle = '" + @Subtitle + "' AND Book.ISBN = '" + @ISBN + "' AND Author.First_Name = '" + @AuthorFName + "' AND Author.Last_Name = '" + @AuthorLName + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");GridView1.DataSource = ds;GridView1.DataBind();} protected void BtnSearch_Click(object sender, ImageClickEventArgs e){BindData();}
I have a select statement that consists on something like this :
SELECT * FROM Products WHERE Ref LIKE '%" & Search & "%'
well this works ....but the problem is that i have some Ref in this format "MC-909" and if the user makes a search with the word "MC909" ...it dosent return any value ...
where nt.Project_type='A' and nt.status='Done' and nt.project_id like '06%'
I also need to add an additional condition : like '07%'. How can I do this, i. e I need the projects where the type = A , status = Done and project_id starting with 06 and 07.
Hi , This is my first message on this forum. Well, I want to write a query which finds the length of the employee names whose name starts with J, A or S?.
This is what I did:
SELECT LENGTH(ENAME) FROM EMP WHERE ENAME LIKE 'A%' OR LIKE 'J%' OR LIKE 'S%'
This query does not work. Can anyone tell me what is wrong with it?
I have a column named tags which contains tagnames seperated by commas now i have to compare this column with a parameter named tagname which contains a single tagname how to do this.Please help me
here is my procedure
ALTER PROCEDURE [dbo].[ProcSoftwaresGetSoftwaresbyTags] ( @FileTag Varchar(64) ) AS BEGIN
SELECT Category.CategoryName, Softwares.SoftwareFileID, Softwares.CategoryID, Softwares.FileName, Softwares.FileImage,Softwares.FilePath, Softwares.FilePublisher, Softwares.Adminrating, Softwares.FileDownloadCount,substring(Softwares.FileDescription,1,200)as FileDescription,convert(Varchar(12),Softwares.FilePostedDate,109)as FilePostedDate,Softwares.FileSize, SystemRequirements.OperatingSystem FROM Category INNER JOIN Softwares ON Category.CategoryID = Softwares.CategoryID INNER JOIN SystemRequirements ON Softwares.SoftwareFileID = SystemRequirements.SoftwareRequirementID where substring(Softwares.FileTag,0,3) LIKE substring(@FileTag,0,3) and FileStatus=1 END
I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...
I'm having problems using the between operator in a query statement.
Select * from <mytable> where date between @date1 and @date2
The date values with a hour specified, aren't returned. What is the approach you would recommend here?
Thx
EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @fromDate + '00:00:00' and @toDate + '23:59:59'
this seem to work, but I'm not sure if this is correct
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?
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?
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.
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?
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
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.
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].
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.
