Solved

INSERTED records not appearing in SQL Server 2005

Posted on 2007-03-22
6
470 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
ID: 18771980
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
ID: 18775556
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
ID: 18778467
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
ID: 18817523
i think you are not calling commit
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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

914 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

16 Experts available now in Live!

Get 1:1 Help Now