We help IT Professionals succeed at work.
Get Started

INSERTED records not appearing in SQL Server 2005

chriscboy
chriscboy asked
on
548 Views
Last Modified: 2008-06-28
Hi,
SQL2005 Problem inserting data from visual foxpro 7.

I have got a problem with INSERT statements not INSERTING records into some tables in our Sage CRM database. I have run the SQL Profiler and the queries are appearing there, but no data appears in the table. Previously it was working perfectly, then suddenly it stopped. It does not effect UPDATE statements. Below is an exampe of the query:
insert into mytest (statement) values ('start debug -1')

/*******************COMPANY*****************************/
DECLARE @CompTabId int
SELECT @CompTabId = bord_tableid FROM custom_tables WHERE bord_name = 'company'
DECLARE @CompId int
EXECUTE @CompId = crm_next_id @CompTabId
insert into mytest (statement) values ('start debug -2')

DECLARE @AccMgrID int
SELECT @AccMgrID = User_UserId FROM users WHERE user_initials = ''
insert into mytest (statement) values ('start debug -3')
DECLARE @CompTypeCode nchar(40)
DECLARE @WebEnabled nchar(1)

EXECUTE sp_TransToCaptCode 'comp_sector' ,'Shopping Centre', @CompTypeCode OUTPUT
insert into mytest (statement) values ('start debug -4')
SET @WebEnabled = 'N'
IF (0) = 1
  SET @WebEnabled = 'Y'
insert into mytest (statement) values ('start debug -5')
INSERT INTO company
(
Comp_CompanyId,
Comp_PrimaryPersonId,
Comp_PrimaryAddressId,
Comp_PrimaryUserId,
Comp_Name,
Comp_Type,
Comp_Status,
Comp_Source,
Comp_Territory,
Comp_Revenue,
Comp_Employees,
Comp_Sector,
Comp_IndCode,
Comp_WebSite,
Comp_MailRestriction,
Comp_PhoneCountryCode,
Comp_PhoneAreaCode,
Comp_PhoneNumber,
Comp_FaxCountryCode,
Comp_FaxAreaCode,
Comp_FaxNumber,
Comp_EmailAddress,
Comp_CreatedBy,
Comp_CreatedDate,
Comp_UpdatedBy,
Comp_UpdatedDate,
Comp_TimeStamp,
Comp_Deleted,
Comp_LibraryDir,
Comp_SegmentID,
Comp_ChannelID,
Comp_SecTerr,
Comp_WorkflowId,
Comp_UploadDate,
comp_SLAId,
comp_ncompany_id,
comp_webenabled,
comp_refno,
comp_fpdeleted,
comp_contcoor,
comp_caccmanager,
comp_logofilename
)
VALUES
(
@CompId, --Comp_CompanyId
NULL, --Comp_PrimaryPersonId
NULL, --Comp_PrimaryAddressId
@AccMgrID, --Comp_PrimaryUserId
Cast('Debug1' as nchar(60)), --Comp_Name
'Prospect', --Comp_Type
'Active', --Comp_Status
NULL, --Comp_Source
NULL, --Comp_Territory
NULL, --Comp_Revenue
NULL, --Comp_Employees
@CompTypeCode, --Comp_Sector
NULL, --Comp_IndCode
Cast('kjhkhkjhkhk' as nchar(40)), --Comp_WebSite
NULL, --Comp_MailRestriction
NULL, --Comp_PhoneCountryCode
NULL, --Comp_PhoneAreaCode
NULL, --Comp_PhoneNumber
NULL, --Comp_FaxCountryCode
NULL, --Comp_FaxAreaCode
NULL, --Comp_FaxNumber
NULL, --Comp_EmailAddress
40, --Comp_CreatedBy
getdate(), --Comp_CreatedDate
40, --Comp_UpdatedBy
getdate(), --Comp_UpdatedDate
getdate(), --Comp_TimeStamp
NULL, --Comp_Deleted
NULL, --Comp_LibraryDir
NULL, --Comp_SegmentID
NULL, --Comp_ChannelID
-2147483640, --Comp_SecTerr
NULL, --Comp_WorkflowId
NULL, --Comp_UploadDate
NULL, --comp_SLAId
1576.0000000000, --comp_ncompany_id
@WebEnabled, --comp_webenabled
Cast('1000' as nchar(10)), --comp_refno
NULL, --comp_fpdeleted
NULL, --comp_contcoor
Cast('' as nchar(20)), --comp_caccmanager
Cast('' as nchar(200)) --comp_logofilename
)
insert into mytest (statement) values ('start debug -6')
/*******************PHONE******************************/
--//company phone
DECLARE @PhonTabId int
SELECT @PhonTabId = bord_tableid FROM custom_tables WHERE bord_name = 'phone'
DECLARE @PhonId int
EXECUTE @PhonId = crm_next_id @PhonTabId


INSERT INTO PHONE
(
Phon_PhoneId,
Phon_CompanyID,
Phon_PersonID,
Phon_Type,
Phon_CountryCode,
Phon_AreaCode,
Phon_Number,
Phon_CreatedBy,
Phon_CreatedDate,
Phon_UpdatedBy,
Phon_UpdatedDate,
Phon_TimeStamp,
Phon_Deleted,
Phon_SegmentID,
Phon_ChannelID
)
VALUES
(
@PhonId, --Phon_PhoneId
@CompId, --Phon_CompanyID
NULL, --Phon_PersonID
'Business', --Phon_Type
NULL, --Phon_CountryCode
NULL, --Phon_AreaCode
Cast('jdhkjhs' as nchar(20)), --Phon_Number,
40, --Phon_CreatedBy
getdate(), --Phon_CreatedDate
40, --Phon_UpdatedBy
getdate(), --Phon_UpdatedDate
getdate(), --Phon_TimeStamp
NULL, --Phon_Deleted
NULL, --Phon_SegmentID
NULL --Phon_ChannelID
)
insert into mytest (statement) values ('woohoo - stop')

The query seems to "bail out" when it does the INSERT into the company table, because the last value inserted into the table myest is "start debug -5" indicating a problem with the insert into the company table. The strange thing is if I copy the query from SQL Profile and run it as a new query in the management studio all the records are inserted!!!

Any ideas on how I can try and resolve the problem. I am supposed to be going live with the CRM system next week and without the integration working I don't think I will be!!!

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE