inner join

fwstealer
fwstealer used Ask the Experts™
on
i've got an inner join that uses two separate sql servers and tables as linked servers. when i provide an id it works but when i try a variable it returns no data and im not sure why

DECLARE @MyFlightId varchar
SET @MyFlightId = '1242740'
select flights.flightid as 'Astro.Flights.FlightID', ExpenseReport.FlightID as 'Pronto.ExpenseReport.FlightID',
ExpenseReport.ID,ExpenseReport.ProntoID,ExpenseReport.TodaysDate,ExpenseReport.PIC,ExpenseReport.SIC,
ExpenseReport.TailNo,ExpenseReport.FlightLog,ExpenseReport.NoReceiptsAttached,
ExpenseReport.NoFuelCards,ExpenseReport.DepICAO,ExpenseReport.PAX,ExpenseReport.FBOName,
ExpenseReport.FBOPaymentType,ExpenseReport.Raft,ExpenseReport.Catering,
ExpenseReport.RetailFuelPrice,ExpenseReport.GallonsPurchased,ExpenseReport.GeoStamp,
ExpenseReport."1Fees",ExpenseReport."1FeeAmount",ExpenseReport."2Fees",
ExpenseReport."2FeeAmount",ExpenseReport."3Fees",ExpenseReport."3FeeAmount",
ExpenseReport."4Fees",ExpenseReport."4FeeAmount",ExpenseReport."5Fees",
ExpenseReport."5FeeAmount",ExpenseReport."6Fees",ExpenseReport."6FeeAmount"
from ExpenseReport with(nolock)
inner join astrosql2.astro.dbo.tm_flights flights with(nolock) on ExpenseReport.FlightID = flights.flightid
where ExpenseReport.FlightID = @MyFlightId -- returns no row
--where ExpenseReport.FlightID = '1242740'  -- returns my row
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you sure ExpenseReport.FlightID is a varchar, not an int or something?

Commented:
Also, try removing those with (nolock) table hints, see what that does..

Author

Commented:
i removed the no lock  - returned nothing
yes one is a varchar.

--astro flights [FlightID] [dbo].[PrimaryKey] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
--pronto expense report [FlightID] [varchar](1000) NULL
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
set a size for the variable:
DECLARE @MyFlightId varchar(100)
SET @MyFlightId = '1242740'

Open in new window


otherwise it's varchar(1) ...
ramromconsultant

Commented:
Perhaps you need
SET @MyFlightId = "'1242740'"

Commented:
I  think AngelIII has the solution - sizing the varchar to something sensible

Author

Commented:
can't believe i didn't see that one  - duh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial