Solved

INSERTED records not appearing in SQL Server 2005

Posted on 2007-03-22
6
465 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!!!

0
Comment
Question by:chriscboy
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
put

Select  @@ERROR  as Errorcode, @@ROWCOUNT as rowsprocessed

after each select statement

and see what error is generated...

put a begin / end
around the scope of the IF (0) = 1
as well

0
 
LVL 11

Expert Comment

by:dready
Comment Utility
HI,

I don't think it can be the problem here, because then nothing would be inserted. But inserts not being updated sounds like a implicit transaction setting. If the connection is in implicit transaction mode, nothing is committed unless you call commit tran.
Just to make sure, try SET IMPLICIT_TRANSACTIONS OFF on one of the first lines.

worth a try.
0
 

Author Comment

by:chriscboy
Comment Utility
Thanks for the comments so far. I have tried these but they do not work. The problem is the query does not seem to be completing as it stops at the insert into company statement.

 If the statement completed I would see a record in the mytest table with a value of 'woohoo - stop'.
0
 
LVL 1

Accepted Solution

by:
vamshijanga earned 500 total points
Comment Utility
i think you are not calling commit
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 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

7 Experts available now in Live!

Get 1:1 Help Now