How to Use Open Query in a stored procedure on a linked server

Question

I am trying to use open query in a stored procedure to retrieve data from an AS400 linked server. It was suggested that I might need to create temp tables, then insert the data into them. Here is the basic query:

SELECT     Consultant.ConsultantID
, Consultant.FirstName
, Consultant.LastName
, cast(StartDate as varchar(12))as [Start Date]
, Consultant.CareerLevel, Sales.RetailSales
, Sales.RetailSalesTotal
, Sales.Unit
, Unit.UnitName
,Orders.OrderDate
,Orders.OrderType
,Consultant.AccountStatus

FROM         Consultant INNER JOIN
                      Sales ON Consultant.ConsultantID = Sales.ConsultantID INNER JOIN
                      Unit ON Consultant.UnitID = Unit.UnitID
WHERE StartDate between 'Jan 1 2010' and 'Dec 31 2010'

ORDER BY Sales.RetailSalesTotal desc

Answer 1

you can write: create table #temp ( ConsultantID int, FirstName varchar(50), LastName varchar(50), StartDate varchar(12), CareerLevel varchar(50),

RetailSales varchar(50),

RetailSalesTotal int, Unit char(10), UnitName char(10),

OrderDate datetime, OrderType char(10), AccountStatus char(10),

) insert into #temp select SELECT a.ConsultantID , a.FirstName , a.LastName , cast(a.StartDate as varchar(12))as [Start Date] , a.CareerLevel, Sales.RetailSales , Sales.RetailSalesTotal , Sales.Unit , Unit.UnitName ,Orders.OrderDate ,Orders.OrderType ,Consultant.AccountStatus from AS400.[DATABASE NAME].dbo.Consultant a INNER JOIN AS400.[DATABASE NAME].dbo.Sales ON a.ConsultantID = Sales.ConsultantID INNER JOIN AS400.[DATABASE NAME].dbo.Unit ON a.UnitID = Unit.UnitID WHERE a.StartDate between 'Jan 1 2010' and 'Dec 31 2010'

ORDER BY a.Sales.RetailSalesTotal desc

How to Use Open Query in a stored procedure on a linked server

Follow this question

Related Questions