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

avatar image

308 21 27 31

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

avatar 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.

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: 1725 times

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

Copyright 2018 Redgate Software. Privacy Policy