asked on

inner join

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

plummet

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

plummet

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

fwstealer

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ramrom

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

plummet

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

fwstealer

ASKER

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