Solved

Trying to write data to a Linked Server Table on a AS400 from a 2008 SQL Server R1

Posted on 2011-02-18
1
166 Views
Last Modified: 2012-05-11
Hello everyone, thanks for you time.  I have a very unique situation here.  The company i work for has a IBM AS400 that is running Accounting systems on it.  We have a Sales and Marketing system that is running a SQL Server 2008 R1.  I have the AS400 as a linked the server and I can select*from from the table on the AS400 with zero problems.  I have set a blank table on the AS400 and I have a view setup on the SQL Server to add data to the table on the AS400.  I have done all of the PTF updates on the AS400 for the database and the hyper system.  I am trying to write a statement that will select the data from the SQL view and place it in the table on the AS400.  Here is what I have so far.  I am a novice at SQL, I am more of a network engineer.  Any help at all would be greatly appreciate.  The following is the statement I have thus far, that is giving me syntax errors.

SELECT*FROM [TSWDATA_ClientCustom].[dbo].[vw_rb_ar_CreateGLTrans]
            ([GLProject]
           ,[CardType]
           ,[TRType]
           ,[GLMajor]
           ,[SourceDesc]
           ,[TransactionDate]
           ,[PostingPeriod]
           ,[DepartmentSub]
           ,[Amount]
           ,[TransDescription])
    VALUES (<GLProject, varchar(20),>
           ,<CardType, varchar,>
           ,<TRType, varchar(2),>
           ,<GLMajor, varchar(4),>
           ,<SourceDesc, varchar(6),>
           ,<TransactionDate, varchar(10),>
           ,<PostingPeriod, varchar(7),>
           ,<DepartmentSub, varchar(7),>
           ,<Amount, decimal(15,2),>
           ,<TransDescription, varchar(20),>)
INSERT INTO [AS400].[S069E014].[TSW].[GLTRN]
           ([TCOPRJ]
           ,[TCARDT]
           ,[TRTTYPE]
           ,[TMAJOR]
           ,[TSOURC]
           ,[TDATE]
           ,[TPPER]
           ,[TCPTSU]
           ,[TAMT]
           ,[TRDESC])
     VALUES
           (<TCOPRJ, varchar(20),>
           ,<TCARDT, varchar,>
           ,<TRTTYPE, varchar(2),>
           ,<TMAJOR, varchar(4),>
           ,<TSOURC, varchar(6),>
           ,<TDATE, varchar(10),>
           ,<TPPER, varchar(7),>
           ,<TCPTSU, varchar(7),>
           ,<TAMT, decimal(15,2),>
           ,<TRDESC, varchar(20),>)

Thanks again for your time
0
Comment
Question by:PlantationResort
1 Comment
 
LVL 21

Accepted Solution

by:
Alfred1 earned 500 total points
ID: 35824968
OK.  Try this out:
INSERT INTO [AS400].[S069E014].[TSW].[GLTRN]
           ([TCOPRJ]
           ,[TCARDT]
           ,[TRTTYPE]
           ,[TMAJOR]
           ,[TSOURC]
           ,[TDATE]
           ,[TPPER]
           ,[TCPTSU]
           ,[TAMT]
           ,[TRDESC])
SELECT [GLProject]
           ,[CardType]
           ,[TRType]
           ,[GLMajor]
           ,[SourceDesc]
           ,[TransactionDate]
           ,[PostingPeriod]
           ,[DepartmentSub]
           ,[Amount]
           ,[TransDescription]
FROM [TSWDATA_ClientCustom].[dbo].[vw_rb_ar_CreateGLTrans]

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now