JOIN Across Two Servers Without Sp_addlinkedserver
Feb 10, 2008
QUESTION 1
Is it possible to run a JOIN on tables that are on seperate servers and youre not able to link the servers using "sp_addlinkedserver?"
QUESTION 2
I try to run
"exec sp_addlinkedserver 'sqlserver1'"
and I get the following:
"Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36
User does not have permission to perform this action."
I try to then run
"SELECT *
FROM server1.database1.schema1.table1 AS a
JOIN server2.database2.schema2.table2 AS b
ON a.[column1] = b.[column2]"
and I get the following:
"OLE DB provider "SQLNCLI" for linked server "ITCMSARPT" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
It would appear the the server is now linked, yet it does't accept my credentials. I do have the ability to log into the server on a seperate query.
Can anyone provide a solution to my issue?
View 5 Replies
ADVERTISEMENT
May 23, 2008
Hello I am doing update.
I have to table called REFERENCE in one database1 from server1 and REFERENCE in database2 from server2
how do i join them
please let me know thanks alot
View 1 Replies
View Related
Sep 27, 2013
How to join tables on different servers if one server, for instance, SERVER2 required authentication?
View 2 Replies
View Related
Feb 23, 2008
Code Snippet
SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1
FROM ReciptItems LEFT OUTER JOIN
Acc_mydbname.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1
How Replace Acc_mydbname with (SELECT AccountDBName FROM Config)
(SELECT AccountDBName FROM Config) ='Acc_mydbname_2008.dbo.'
View 6 Replies
View Related
Jan 17, 2006
Hi All,
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 I would simply do the following:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
What do I need to do to perform the same operation in SSIS world?
Thank you !
View 1 Replies
View Related
Aug 18, 2006
I am making a ASP.NET web application that involves 2 SQL Server(A & B).
I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.
Can anyone help me with this?
Thanks!
View 4 Replies
View Related
Jan 20, 2006
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
Thank you
View 3 Replies
View Related
Aug 22, 2007
Hey guys and gals,
I got asked a question yesterday about queries that JOIN between two databases on a single server... Simple enough
SELECT e1.employee_number
FROM Database1.dbo.employees As e1
INNER
JOIN Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But what about joining tables from two separate servers?
I figured that this would use full 4 part naming
SELECT e1.employee_number
FROM Server1.Database1.dbo.employees As e1
INNER
JOIN Server2.Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But I got the following error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
So off I went to trusty BoL and Google to see what sp_addlinkedserver was all about and came up with the following
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
Is this syntax correct?
I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
Is this process reversible?
I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
Does the link affect all databases on a single server, or just the one I'm playing with?
How do I connect to a specific database on a specific server?
Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
EDIT: highlighted in pink
View 10 Replies
View Related
Mar 31, 2008
Our Production server is Windows 2003 and SQL 2000 Enterprise. The server name is VB-GP.
I dumped 2 of the databases from the production server and restored them to server name SQL2K5-Pub (a TEST server) which was a fresh install of Windows 2003 and SQL 2000 Enterprise. I then upgraded this "TEST" server to SQL 2005 Enterprise. All is fine so far.
Now I'm setting up Transactional Replication in my test environment. I configured SQL2K5-Pub as a Distributor which went well. I created a a Publication of 1 database which went well. I attempted to create a Publication of the second database and received the following error.
Error Message
*********************************************************************
Could not find server 'VB-GP' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Could not use view or function '[dbo].ContractBillingStatus_
*********************************************************************
Remember - server name VB-GP is the production server but I have restored to a test server with a different server name. I do not want the production server to have any part of my test environment.
Any ideas?
Thanks,
Terry
View 4 Replies
View Related
Feb 8, 2008
I have used this command
sp_addlinkedserver 'linkedserver', 'Access', 'Microsoft.Jet.OLEDB.4.0','C:CATS_MDBintrax_1_db.mdb'
to link an .mdb databse. the trouble is, I do not know how to query tbl_user which is inside. When I try
Code Snippetselect * from [linkedserver].catalogs.dbo.tbl_user
it throws this error:
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linkedserver". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
View 1 Replies
View Related
Aug 9, 2000
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
Thanks
VENU
View 3 Replies
View Related
Aug 9, 2000
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
HOW DO WE MAP THE LOGINS USING EM AFTER ADDING THE SERVER USING SP_ADDLINKEDSERVER
Thanks
VENU
View 1 Replies
View Related
Aug 9, 2000
Hi
Can anyone explain me as to how to map my local_userid to the linked server.
Under the security tab of the linked server properties what options are to be used..any help in this regard is greatly appreciated
Thanks
VENU
View 1 Replies
View Related
Dec 6, 2005
HI!
i m using sp_ADDLINKEDSERVER for getting data from the other server name 'db est'.
And when i write
'
EXEC SP_ADDLINKEDSERVER 'db est', 'SQL SERVER'
GO
EXEC sp_addlinkedsrvlogin 'db est', FALSE ,NULL ,'sa','sa'
GO
SELECT * FROM db est.Practice.DBO.Employees
'
And then run it in query analyzer. it gives following error due to '' in the server name.
Incorrect syntax near ''.
how to resolve this issue?
Regards,
Shabber Abbas Rizvi.
View 5 Replies
View Related
Jan 10, 2008
Hi,
Iam trying to use database in different server. i have searched for that i got to know there is something called
"sp_addlinkedserver" & "sp_addlinkedsrvlogin " stored procs exist. i have tried using this code block to register server to sys servers.
Code Block
exec sp_addlinkedserver @server='ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname='ServerName', @useself=false, @rmtuser='testUser', @rmtpassword='pwd'
when i login using Domain user it is throwing an error as "Login Failed for the User(Domain user)", but when i use "sa" user it works fine.
Is there any settings which i have to take care while using domain user.
can anyone help on this.
Thanks,
-Badri
View 1 Replies
View Related
May 18, 2000
I'm trying to link access table and execute in Query analyzer or query window.
but I'm pretty sure that I connect properly....
1)
sp_addlinkedserver 'linkedserver', 'Access 97', 'Microsoft.Jet.OLEDB.4.0','C:Mag.mdb'
after i ran this it displays....
(1 row(s) affected)
(1 row(s) affected)
Server added.
2)
sp_addlinkedsrvlogin 'linkedserver', true, 'sa', 'sqluser','sqlpassword'
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
3)
after that when i try to run the sql statement like
Select * from linkedserver..tablename
I GOT THIS ERROR MESSAGE:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'linkedserver..tablename'.
I appreciate if anybody have idea about that.
Thankx a lot
View 6 Replies
View Related
Nov 30, 2000
I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
View 1 Replies
View Related
Mar 1, 2007
I wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database.
--Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins
if exists (select * from dbo.sysobjects where id = object_id(N'sp_compare_table_record_counts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_compare_table_record_counts
go
create proc sp_compare_table_record_counts as
----Creates the connection to the Remote Database. We need to make sure that the
----ODBC connections on each of the Production and Development Servers
----are named the same, so that we can use the same script and it will refer to the correct server
EXEC sp_addlinkedserver
@server = 'SERVER_BK',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
set quoted_identifier off
select substring(remote_table.tablename,1,40) as Tablename, local_table.tablerowcount as L_SERVER,
remote_table.tablerowcount as R_SERVER,
case when remote_table.tablerowcount = local_table.tablerowcount then 'Counts Match'
else 'Counts Do Not Match'
end as Result_of_Compare
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table, tablecounts local_table
where remote_table.tablename = local_table.tablename
order by Result_of_Compare, Tablename
-- table name on R_SERVER but not on L_SERVER
select substring(local_table.tablename,1,40) as 'Tables not on R_SERVER'
from tablecounts local_table where local_table.tablename not in (select * from OpenQuery([SERVER_BK], 'select tablename from tablecounts'))
-- table name on L_SERVER but not on R_SERVER
select substring(remote_table.tablename,1,40) as 'Tables not on L_SERVER'
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table where remote_table.tablename not in (select tablename from tablecounts)
----Deletes the connection to the Remote Database to free up resources. This is just cleanup.
EXEC sp_dropserver 'SERVER_BK', 'droplogins'
I tested, and the stored procedure works OK when it is run as a New Query (i.e. I copy the guts and run it). When I execute the following, it fails:
exec msdb..sp_send_dbmail
@profile_name = 'DB Mail Profile',
@recipients = 'Recipient List',
@query_no_truncate = 25,
@query_result_separator = ' ',
@subject = 'Record Counts',
@query = 'exec db..sp_compare_table_record_counts', <--db is the appropriate db where the sp is
@body_format = 'text'
Here is the error I get...any ideas would be greatly appreciated. I'm kind of new at this:
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 7399, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
The OLE DB provider "MSDASQL" for linked server "SERVER_BK" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER_BK".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
View 10 Replies
View Related
Mar 14, 2006
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'CONVERTSDB', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'F:Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';
View 4 Replies
View Related
Nov 21, 2007
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
View 1 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Aug 21, 2001
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
View 1 Replies
View Related
Sep 27, 2007
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
View 2 Replies
View Related
Apr 23, 2007
a different scenario leads to the same problem, the error message:
Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"
I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server.
this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away.
I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution?
View 6 Replies
View Related
Jun 4, 2008
HiI am getting the above error message when I try and bind a stored procedure to a gridview:string connectionString2 = ConfigurationManager.ConnectionStrings[project].ConnectionString; SqlConnection con_room_code2 = new SqlConnection(connectionString2); SqlCommand cmd_sofa = new SqlCommand("report", con_room_code2); cmd_sofa.CommandType = CommandType.StoredProcedure; cmd_sofa.Parameters.Add(new SqlParameter("@code", SqlDbType.VarChar)); cmd_sofa.Parameters["@code"].Value = Session["code"].ToString(); SqlDataSource1.ConnectionString = connectionString2; SqlDataSource1.SelectCommand = cmd_sofa.ToString(); plreport.Visible = false; plreport.Visible = true; test.Text = Session["code"].ToString();does any1 know why this is? thanks
View 2 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
       S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
       Production.Suppliers AS S LEFT OUTER JOIN
      (Production.Products AS P
        INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
       S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
       Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Dec 23, 2014
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
View 9 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
View Related
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
Jul 25, 2007
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
View 1 Replies
View Related
