Monday, November 15, 2010

Importing Invoices into Sage

I find myself having to generate CSV files to import into Sage every now and then, problem is that whilst I can readily frin the CSV file structure in the Sage Help File, the Transaction Type often remains elusive. Now that I have tracked them down again, here they are:

BPBank Payment (not supplier)
BRBank Receipt (not customer)
CPCash Payment (not supplier)
CRCash Receipt (not customer)
JCJournal Credit
JDJournal Debit
PAPurchase Payment on Account
PCPurchase Credit Note
PIPurchase Invoice
PPPurchase Payment
PDDiscount on Purchase Payment
SASales Receipt on Account (Cash from Customer not allocated)
SCSales Credit
SISales Invoices
SDDiscount on Sales Receipt
VPCredit Payment
VRCredit Receipt

The CSV file structure is:



Field
Description
Length
Type
1
Transaction type
2
Text
2
Sales / Purchase / Bank Account reference
8
Text
3
Nominal code
8
Integer
4
Department number
3
Integer
5
Transaction date
10 (**/**/****)
Date
6
Transaction reference
8
Text
7
Transaction details
60
Text
8
Net amount
11 (up to 2 decimal places)
Decimal number
9
Tax code
3
Text
10
Tax amount
11 (up to 2 decimal places)
Decimal number
11
Extra reference
8
Text
12
User name
32
Text

Sunday, August 29, 2010

SqlBulkCopy with SQLAzure

Problem

Using SqlBulkCopy (.NET 4.0) with SQL Azure (10.25.9386) gives the error:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The SqlBulkCopy class is recommended by Microsoft for migration in their General Guidelines and Limitations (SQL Azure Database) document. However, it is clear to me that this does not work having tried many approaches, generally of the form:
  
Using cn As SqlConnection = New SqlConnection("connection string")
cn.Open()
Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(cn)
BulkCopy.DestinationTableName = "table"
BulkCopy.BatchSize = 5000
BulkCopy.BulkCopyTimeout = 120
Try
BulkCopy.WriteToServer(ds.Tables(0))
Catch ex As Exception

End Try
End Using
End Using


Alternatives

  1. Use a traditional row at a time insert.
  2. Write a wrapper around BCP which works fine.

Labels: ,

Tuesday, June 14, 2005

IIS 6 Worker Process Timeouts

Ever wondered why it doesnt matter what you set session.timeout to in asp.net, the session still times out after 20 minutes?

The IIS 6 Worker Process Idle Timeout is default to 20 minutes, and if you are using InProc session state then the session will be lost when the worker process is shut down.

You can change this in the Application Pool Properties.