Solved

INSERTED records not appearing in SQL Server 2005

Posted on 2007-03-22
6
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
How to check if SQL server database is being backed up by SQL management studio ? 13 82
store vs query adhoc - no show rows 4 37
SQL query 45 41
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

752 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