Join Translation In SQL Server
Jun 8, 2004
Hi
Can any one please translate these oracle inner join and outer join in SQL
Server. Any help will be highly appreciate.
SELECT V1.DB_VENDOR V1.NAME1,1,21)||DECODE(B1.JOINT_DESC,NULL,'',
'(JV)')||
DECODE(B1.NON_RESP,'Y','(NR)',
'N','') V1_NAME1,
B2.DB_CONTRACT B2_DB_CONTRACT, B2.BID_VENDOR B2_BID_VENDOR, B2.RANK_NUMB B2_RANK_NUMB,
V2.DB_VENDOR V2_DB_VENDOR,
SUBSTR(V2.NAME1,1,21)||DECODE(B2.JOINT_DESC,NULL,' ',
'(JV)')||
DECODE(B2.NON_RESP,'Y','(NR)',
'N','') V2_NAME1
FROM BID_TOTAL B1, BID_TOTAL B2, VENDOR V1, VENDOR V2
WHERE V1.DB_VENDOR = B1.BID_VENDOR
AND V2.DB_VENDOR (+) = B2.BID_VENDOR
AND B1.DB_CONTRACT = B2.DB_CONTRACT (+)
AND B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.RANK_NUMB (+) = B1.RANK_NUMB + 1
AND B2.DB_CONTRACT IS NOT NULL
and b2.db_Contract=39624
ORDER BY B1.RANK_NUMB
View 10 Replies
ADVERTISEMENT
Aug 17, 2006
Hi,I'm trying to convert MS Access 97 .mdb application to Access 2003 .adpapplication with SQL Server as Backend.I'm having trouble converting Access Query into SQL Query. The Query isgiven below:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~SELECT DISTINCTROW Buildings.BuildingNumber,First(Buildings.BuildingName) AS FirstOfBuildingName,First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,First(CityCodes.CityName) AS FirstOfCityName,First(CountyCodes.CountyName) AS FirstOfCountyName,First(Buildings.Address) AS FirstOfAddress,First(Buildings.YearConstructed) AS FirstOfYearConstructed,First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,First(Buildings.NumberLevels) AS FirstOfNumberLevels,First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,First(Buildings.SpecialArea) AS FirstOfSpecialArea,First(Buildings.CoveredUnenclosedGrossArea) ASFirstOfCoveredUnenclosedGrossAreaFROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodesINNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =Buildings.OwnershipCode) ON CountyCodes.CountyCode =CityCodes.CountyCodeGROUP BY Buildings.BuildingNumber;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~Please can any one tell me substitue for First Function in Acess to SQLFunction.Any help is appreciated.Thanks,S
View 3 Replies
View Related
Jul 17, 2014
I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....
I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.
The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.
The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.
It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.
View 5 Replies
View Related
Feb 1, 2006
Hi
Can anyone help me translate this mysql-statement to mssql??
SELECT nickname FROM cb_chat_users WHERE activetime < DATE_ADD(now(), INTERVAL -" & cLng("0" & UserDrop) & " SECOND) AND active = 1"
thanks in advance
View 1 Replies
View Related
Aug 23, 2006
Hi friends, i want to know is there a possibility of translation to XML
in DAO or ADO , I'm working with MS Access.
View 2 Replies
View Related
Nov 14, 2001
I have a SQL Server 2000 database which uses a Cyrillic collation. The database itself is in English, but some of the tables contain text which use the Cyrillic character set. When creating an ODBC connection to the database, ODBC forces me to have the "Translate character data" option set, which means that a query or stored procedure parameter containing Cyrillic characters have the Cyrillic characters translated to plain ascii. Data returned from a query or SP with Cyrillic characters work fine.
So the question is: how can I send Cyrillic (unicode) strings to an English SQL Server, without it translating the characters between Workstation and Server? Ideas appreciated.
Thanks,
ChrisH
View 1 Replies
View Related
Dec 1, 2000
Dear All,
I wanted to post this message to all of those people whom might have experieinced the same situation that we are currently experiencing.
The architecture is transactional replication from a Unix/Sybase environment to a WINNT/MSSQL environment.
When a developer ran a query utilizing a Sybase SQL Advantage window and a ISQL window from Unix, this data field with these values is being report as such:
col1 (SQL Advant) Nov 17 2000 (ISQL) Nov 17 4336
col2 (SQL Advant) Nov 17 2000 (ISQL) Nov 17 1510
However, when the problem was brought to my attention, I opend a SQL Advantage window and a MSSQL QA window and ran the same query and the dates matched as listed above in the (SQL Advant) col1 and col2. Can anyone explain this wierd phenomena?
Any help with be appreciated.
Thanks in Advance,
Daimon
daimon.russell@bridge.com
View 2 Replies
View Related
Oct 27, 1999
I am importing data where the date is in "797867443" format. I believe this in seconds taken
place since 1/1/1980 GMT. How can I translate this to 8am 10/1/1999 EST?
Any input would be appreciated.
-aw
View 1 Replies
View Related
Mar 26, 2008
Hello Guys,
I have a source table with a varchar(1000) field. I want to abbreviate words in source string and store them as a concatenated string in target field which is varchar(100). Translation of source word to abbreviated word can be provided in flat file or database table.
eg.
Source Records:
1 "Infrastructure Innovation Information"
2 "Industrial Imaging"
Target Records:
1 "Infr Innov Inf"
2. "Ind Img"
I am planning to use script component with in-memory table or an array but I am hesitant because of limitation of script debugging capabilities.
Any ideas that how can we implement this?
Kapil
View 4 Replies
View Related
Jul 20, 2007
Hi There,
I have a stored procedure which is written in SQL Server and I want to translate it in Oracle. This stored procedure find a value in whole database, all the tables and columns.
Any help will be highly appreciated.
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
Thanks,
View 5 Replies
View Related
Jul 20, 2005
Hi,I have a problem regarding the perfomance of a stp in combination withcharacter translation.The following happens.We have an automated installation script (nt command file) forcreating all stored procedures in a database.At first we used osql in this script, but with osql the internationalcharacters (é, ï etc) were not entered correctly into the database.The tip given in some newsgroups was to use isql in stead of osql, andturn off automatic ansi to oem translation.This worked perfectly. However now some stored procedures are executedmuch slower than before (and they take up much cpu). When I drop andrecreate the stored procedure using Query Analyser, the performancegoes up again.When I look through the messages in this group, the advise is to useosql instead of isql. But I just changed to isql because of thecharacter translation!Does anyone have a solution for this?
View 3 Replies
View Related
Apr 24, 2007
I have deveoped a replacement for some an old bcp based applications in the .Net Framework that uses the SqlBulkCopy class.
I have run into some difficulties with code page translation:
The original BCP client runs with OEM Codepage 437 , thus the data "ëÄÆòÖ" gets loaded as "d-¦=+". DB is SQL_Latin1_General_CP1_CI_AS, column is varchar.
I have been unable to perform any code page Encoding in .Net that yields the same result.
I want to emulate this behaviour in my database loader but as yet have been able to find a way....
Any Ideas???
Thanks,
Niall
View 7 Replies
View Related
Sep 4, 2007
I have translated my ReportViewer by setting myReportViewer.Messages to my own implementation. This helps me with the translation of most of the interface. However, errormessages such as
"The value provided for the report parameter 'SomeDateParameter' is not valid for it's type"
is not translated. Is there any way to fix the translation of such error messages?
Regards Andreas
View 1 Replies
View Related
Dec 20, 2007
While trying to set up an unpivot transformation to load data from excel (2003) into sql server db, the dates as column headers get lost in the translation.
To simplify the problem I created a very simple package with an excel source and an excel destination.
The test Excel Source looks like
ID 1/1/2008 3/1/2008 5/1/2008
A 5 7 9
B 10 12 24
After running the package The destination looks like this:
ID F4 F5 F6
A 5 7 9
B 10 12 24
I need to keep the dates since I am loading a large volume of data often.
Any suggestions?
View 4 Replies
View Related
Jul 3, 2007
Hello,I'm struggling a giant fight with my webprovider (ASP.net 2.0) in the following case:I'm using the default connectionstring which automatic is provided when I create an ASP.net webstarterskit. This connectionstring is working fine when i deployed that kit to a W2003 server testenvironment and is also running on my laptop. But when I tried to deploy the website to my (new) hostingserver, also a W2003 server, I landed in ERROR-land. The last error I received was:Cannot open database "Club" requested by the login. The login failed.>
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.NB: I gave that user the right privileges and roles. My provider is not willing in sending me an example of a good working connectionstring and is directing me to their FAQ-site in which the right connectionstring is mentioned. That string looks like this (for a MS SQL database):strConnectie = "Provider=SQLOLEDB;" & _ "Data Source=(local)SQLEXPRESS;"
& _ "Initial Catalog=[GEBRUIKERSNAAM];" & _ "User Id=[GEBRUIKERSNAAM];" & _ "Password=[WACHTWOORD]"My connectionstring in web.config looks like this:<connectionStrings><clear/><add name="ClubSiteDB"
connectionString="Data
Source=.SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User
Instance=True" providerName="System.Data.SqlClient"/><remove
name="LocalSqlServer"/><add name="LocalSqlServer"
connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;User
Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf"
/></connectionStrings>Is there anybody in this forum that can help me out and explain to me how to use the string, as provided by the host, in the default connectionstring i use in web.config ??Any help is welcome and appreciated.NB: I'm using VWD 2005 Express Edition and SQL Server Management Studio ExpressHarry Elzinga
View 6 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
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
Apr 24, 2007
Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
View 2 Replies
View Related
Dec 24, 2013
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
Below is my Implict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow
[Code] ....
and below is explict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow
ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
[Code] ....
the count returned from both the queries is different.
I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.
View 7 Replies
View Related
Feb 5, 2015
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]
View 3 Replies
View Related
Aug 17, 2007
Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
View 3 Replies
View Related
Nov 7, 2006
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
View 2 Replies
View Related
May 6, 2008
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
View 3 Replies
View Related
Mar 9, 2015
I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?
View 9 Replies
View Related
May 24, 2006
If this is a SQL-Server bug, what do I do from here? If it's my problem, I sure can't see it.
The second SELECT in the UNION below is virtually identical to the first. However, the second SELECT joins on a different fields (blue & red ink) and is limited (green ink) in the WHERE clause to show only partially null rows for brevity, but fails to show both partially null rows that do show up in the first. (The Join_Field values are different for each SELECT, so the missing field should not be due to a coalescing of similar rows)
As can be seen, all qualifying source rows are fully shown in the first SELECT because this is a FULL OUTER JOIN, so there is no EmpID = 1 for TeamID = 2 in table X and there is also no EvaluatorID = 1 for TeamID = 2 in table X, but a record for EmpID =1 and TeamID =2 does exist in table Y. Nonetheless, EvaluatorID does not produce the respective, partially null records in the second SELECT.
This appears to me to be a bug in SQL Server, but must be due to something not obvious. I've cloned the data and SQL to a test database, where everything works correctly, so unless there is something else to consider in the schema, there seems to be a bug based on a database with more than just the test tables and fields.
Ancillary information: all fields shown for both tables are members of a compound primary key for their respective tables. There are additional, unused fields in both tables (table X has dozens of defined fields with lots of potential comments and scores). The database currently has no indexes (under development, not yet production, but the deadline is looming large). The database is runing remotely on a test internet site.
SQL Statement:
SELECT 'Y.EmpID=X.EmpID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpIDFROM EvalAnswers X FULL OUTER JOIN MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EmpIDWHERE (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1)UNIONSELECT 'Y.EmpID=X.EvaluatorID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpIDFROM EvalAnswers X FULL OUTER JOIN MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EvaluatorIDWHERE (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1) AND (X.CoID IS NULL OR Y.CoID IS NULL)ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9
Results:
Join_Field X.CoID X.EvalID X.TeamID X.Ev...ID X.EmpID Y.CoID Y.TeamID Y.EmpIDY.EmpID=X.EmpID null null null null null 113 2 1Y.EmpID=X.EmpID 113 1 1 1 1 113 1 1Y.EmpID=X.EmpID 113 1 1 1 2 113 1 2Y.EmpID=X.EmpID 113 1 1 2 1 113 1 1Y.EmpID=X.EmpID 113 1 1 2 2 113 1 2Y.EmpID=X.EmpID 113 1 1 3 3 null null nullY.EmpID=X.EmpID 113 1 2 2 2 113 2 2Y.EmpID=X.EmpID 113 1 2 2 3 113 2 3Y.EmpID=X.EmpID 113 1 2 3 2 113 2 2Y.EmpID=X.EmpID 113 1 2 3 3 113 2 3Y.EmpID=X.EvaluatorID 113 1 1 3 3 null null null
The results for the second SELECT in the UNION should be two partially null rows, not just one.
View 16 Replies
View Related
Aug 10, 1999
I am having major trouble joining 2 tables in SQL Server. Each table has about 300,000 records and the join operation is taking well over 30 minutes. Any suggestions ??
View 1 Replies
View Related
