question

natongm avatar image
natongm 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
linked-serveropenquery
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is that the query that you'll be running on the AS400? Do you have a linked server setup already?
0 Likes 0 ·

1 Answer

·
dona avatar image
dona answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.