May 11, 2014 at 3:26 am
I don't know what version of SQL the Programmers used to create those tables. Is there a way of finding out?
I'm using SQL Server 2008 R2 Express
May 11, 2014 at 3:32 am
The transheaders table contains the TradingDate which is (datetime,null)
The Customers table contains the column ZLastSale which is (text,null)
May 11, 2014 at 3:52 am
Use SQL Server Management Studio to connect to the database, find the database and tables in question. Right click on each table and select Script table as, CREATE, to new query window.
Copy and paste the results for each table here.
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 4:01 am
Ok ...
Customers
USE [EvaluationCompany7AU]
GO
/****** Object: Table [dbo].[CUSTOMERS] Script Date: 05/11/2014 20:00:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CUSTOMERS](
[UserLockNo] [int] NULL,
[CompanyID] [char](11) NOT NULL,
[UniqueID] [char](17) NOT NULL,
[UserFindNo] [int] NULL,
[CustomerCode] [char](16) NOT NULL,
[CustomerTitle] [char](50) NOT NULL,
[CustomerGroup] [char](20) NOT NULL,
[GroupNo] [int] NULL,
[MailingAddress] [char](200) NULL,
[MailingStreet1] [char](50) NULL,
[MailingStreet2] [char](50) NULL,
[MailingSuburb] [char](50) NULL,
[MailingState] [char](30) NULL,
[Postcode] [char](20) NULL,
[MailingCountry] [char](30) NULL,
[Category] [char](10) NULL,
[TaxStatus] [char](25) NULL,
[Notes] [text] NULL,
[BalanceOpening] [decimal](15, 4) NOT NULL,
[BalanceNext] [decimal](15, 4) NOT NULL,
[BalanceCurrent] [decimal](15, 4) NOT NULL,
[BalanceAge1] [decimal](15, 4) NOT NULL,
[BalanceAge2] [decimal](15, 4) NOT NULL,
[BalanceAge3] [decimal](15, 4) NOT NULL,
[BalanceAge4] [decimal](15, 4) NOT NULL,
[TotalDue] [decimal](15, 4) NOT NULL,
[OpeningDate] [datetime] NULL,
[DeliveryBasis] [char](3) NULL,
[LastReceiptAmt] [decimal](15, 4) NULL,
[LastReceiptDate] [datetime] NULL,
[OpenItem] [bit] NOT NULL,
[Remarks] [char](30) NULL,
[IsParentAc] [bit] NULL,
[BankName] [char](40) NULL,
[BranchName] [char](30) NULL,
[BankAccount] [char](30) NULL,
[HoldOrders] [bit] NULL,
[HoldDeliveries] [bit] NULL,
[HoldInvoices] [bit] NULL,
[CreditLimit] [decimal](15, 4) NULL,
[TaxNumber] [char](16) NULL,
[Interest] [decimal](15, 4) NOT NULL,
[DeliveryAddress] [char](200) NULL,
[DeliveryStreet1] [char](50) NULL,
[DeliveryStreet2] [char](50) NULL,
[DeliverySuburb] [char](50) NULL,
[DeliveryState] [char](30) NULL,
[DeliveryPostcode] [char](20) NULL,
[DeliveryCountry] [char](30) NULL,
[AnalysisNo] [int] NULL,
[AnalysisTitle] [char](20) NULL,
[ParentAccount] [char](11) NULL,
[Status] [char](4) NOT NULL,
[QuoteMethod] [char](1) NOT NULL,
[OrderMethod] [char](1) NOT NULL,
[DeliveryMethod] [char](1) NOT NULL,
[InvoiceMethod] [char](1) NOT NULL,
[StatementMethod] [char](1) NOT NULL,
[DocumentFolder] [char](128) NULL,
[Terms] [char](4) NOT NULL,
[Discount] [decimal](15, 4) NULL,
[CompanyFax] [char](25) NULL,
[CompanyPhone] [char](25) NULL,
[SalesContact] [char](50) NULL,
[AccountsContact] [char](50) NULL,
[AccountsEmail] [char](50) NULL,
[AccountManager] [char](20) NULL,
[CurrencyCode] [int] NOT NULL,
[CountryName] [char](24) NOT NULL,
[RunNumber] [int] NULL,
[DeliveryRunNo] [char](8) NULL,
[LastEditDateTime] [datetime] NULL,
[DeliveryRule] [char](4) NULL,
[BankSort] [char](30) NULL,
[NZParticulars] [char](12) NULL,
[NZAnalysis] [char](12) NULL,
[EFTReference] [char](20) NULL,
[LiquorNo] [char](15) NULL,
[ContractDate] [datetime] NULL,
[AlertEmail] [char](50) NULL,
[LimitAlert] [bit] NULL,
[TermsAlert] [bit] NULL,
[BranchID] [int] NULL,
[LocationNo] [int] NULL,
[EmailFolder] [text] NULL,
[PolicyID] [int] NULL,
[QuoteAction] [int] NULL,
[OrderAction] [int] NULL,
[SaleAction] [int] NULL,
[RecurringAction] [int] NULL,
[CreditAction] [int] NULL,
[KeywordsList] [char](255) NULL,
[EU_BIC] [char](11) NULL,
[EU_IBAN] [char](34) NULL,
[EU_TransType] [char](4) NULL,
[EU_MandateID] [char](35) NULL,
[EUMandateDate] [datetime] NULL,
[ZLastSale] [text] NULL,
CONSTRAINT [PK_CUSTOMERS_UniqueID] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Transheaders
SE [EvaluationCompany7AU]
GO
/****** Object: Table [dbo].[TRANSHEADERS] Script Date: 05/11/2014 20:01:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TRANSHEADERS](
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
[TransactionID] [char](25) NOT NULL,
[TransactionType] [char](2) NOT NULL,
[AccountID] [char](17) NOT NULL,
[DeliveryID] [char](17) NULL,
[HistoryID] [char](32) NOT NULL,
[EntryUser] [int] NOT NULL,
[TradingDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[OverDueDate1] [datetime] NULL,
[OverDueDate2] [datetime] NULL,
[OverDueDate3] [datetime] NULL,
[EntryDateTime] [datetime] NOT NULL,
[AccountingDate] [datetime] NOT NULL,
[PeriodNo] [int] NULL,
[TaxPeriod] [int] NULL,
[CurrentVersionNo] [int] NOT NULL,
[AccountingRef] [char](20) NULL,
[TradingRef] [char](20) NULL,
[OrderRef] [char](25) NULL,
[BatchNumber] [int] NOT NULL,
[Logged] [char](1) NOT NULL,
[Outstanding] [char](1) NOT NULL,
[Presented] [char](2) NULL,
[TempPresented] [char](2) NULL,
[HoldPayment] [bit] NULL,
[CountryCode] [int] NOT NULL,
[Decimals] [int] NULL,
[HedgeMethod] [char](1) NULL,
[Description] [char](70) NULL,
[ExtraText] [text] NULL,
[PayeeName] [char](50) NULL,
[BankAccountID] [char](17) NULL,
[AnalysisNo] [int] NULL,
[TaxCategory] [int] NULL,
[TaxCategory2] [int] NULL,
[InvoiceDiscount] [decimal](15, 4) NULL,
[WithholdingRate] [decimal](15, 4) NULL,
[Quantity] [decimal](15, 4) NULL,
[LocalCost] [decimal](15, 4) NOT NULL,
[LocalCharge] [decimal](15, 4) NULL,
[LocalGross] [decimal](15, 4) NOT NULL,
[LocalFreight] [decimal](15, 4) NULL,
[LocalWithheld] [decimal](15, 4) NOT NULL,
[LocalTax] [decimal](15, 4) NOT NULL,
[LocalTax2] [decimal](15, 4) NULL,
[LocalTax3] [decimal](15, 4) NULL,
[LocalDiscount] [decimal](15, 4) NOT NULL,
[LocalNet] [decimal](15, 4) NOT NULL,
[LocalOffset] [decimal](15, 4) NOT NULL,
[ForeignGross] [decimal](15, 4) NOT NULL,
[ForeignFreight] [decimal](15, 4) NULL,
[ForeignWithheld] [decimal](15, 4) NOT NULL,
[ForeignTax] [decimal](15, 4) NOT NULL,
[ForeignTax2] [decimal](15, 4) NULL,
[ForeignTax3] [decimal](15, 4) NULL,
[ForeignDiscount] [decimal](15, 4) NOT NULL,
[ForeignNet] [decimal](15, 4) NOT NULL,
[ForeignOffset] [decimal](15, 4) NOT NULL,
[ExchangeRate] [decimal](15, 4) NOT NULL,
[Unrealised] [decimal](15, 4) NULL,
[Realised] [decimal](15, 4) NULL,
[Notes] [text] NULL,
[NotesStatus] [int] NULL,
[PayMethod] [char](8) NULL,
[Bank_Expiry] [char](40) NULL,
[Branch_Authority] [char](30) NULL,
[BankAutomatic] [bit] NULL,
[BankNet] [decimal](15, 4) NULL,
[GrossValueTaxBox] [char](3) NULL,
[TaxAmountTaxBox] [char](3) NULL,
[DeliveryRunNo] [char](8) NULL,
[TotalWeight] [decimal](15, 4) NULL,
[QuoteExpiryDate] [datetime] NULL,
[Status] [char](8) NULL,
[StatementID] [int] NULL,
[PurchaseID] [char](25) NULL,
[Terms] [char](4) NULL,
[ProjectNo] [int] NULL,
[SourceID] [char](20) NULL,
[ZMSTRANTYE] [int] NULL,
[TaxCategory3] [int] NULL,
[CashType] [char](2) NULL,
[ContractID] [int] NULL,
[RCTActivityID] [int] NULL,
[TPARlogged] [bit] NULL,
CONSTRAINT [PK_TRANSHEADERS_TransactionID] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_TRANSHEADERS_UniqueID_UniqueID] UNIQUE NONCLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
May 11, 2014 at 4:10 am
Your problem is CUSTOMERS UniqueID is char(17) and TRANSHEADERS UniqueID is int
SQL Server is trying to implicitly convert CUSTOMERS UniqueID to int to match against TRANSHEADERS UniqueID but at least one row in CUSTOMERS has a blank UniqueID
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 4:15 am
Ok.. so what should I do?
May 11, 2014 at 4:18 am
Is UniqueID the correct key to link TRANSHEADERS to CUSTOMERS ?
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 4:23 am
Well I've checked all the crystal reports written which come with the software and they all link as:
Customers.UniqueID > Transheaders.AccountID
So I would assume yes.
May 11, 2014 at 4:24 am
No it should be AccountID
May 11, 2014 at 4:39 am
Then your trigger should be
CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
AFTER INSERT, UPDATE
AS
IF UPDATE(TradingDate)
BEGIN
UPDATE c
SET c.ZLastSale = i.TradingDate
FROM inserted i
JOIN dbo.Customers c ON c.UniqueID = i.AccountID
END
This will update ZLastSale (text column!!!) with implicit conversion of TradingDate (date column)
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 5:27 am
I feel like such a pain - I ran the script in SQL and was ok.
Tested a transaction in my system and now get this error.:
Microsoft ODBC SQL Server Driver Connection is busy with results for another hstmt
May 11, 2014 at 6:21 am
AFAIK this is a result of not closing current result set and trying to open another.
Multiple result sets are only allowed when MARS is activated using sql client driver.
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 3:29 pm
I re-booted my PC hoping this would fix the issue and it has not.
Can't understand why this is so difficult...
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply