asked
How to Use Open Query in a stored procedure on a linked server
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
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
9 People are following this question.
