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
169 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

778 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