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

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
more ▼

asked Sep 27, 2012 at 08:42 PM in Default

natongm gravatar image

158 13 19 21

Is that the query that you'll be running on the AS400? Do you have a linked server setup already?
Sep 27, 2012 at 10:48 PM SirSQL
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Oct 26, 2012 at 09:26 AM

dona gravatar image

30 3 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 27, 2012 at 08:42 PM

Seen: 1273 times

Last Updated: Oct 26, 2012 at 09:26 AM