Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INSERTED records not appearing in SQL Server 2005

Posted on 2007-03-22
6
Medium Priority
?
505 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 2000 total points
ID: 18817523
i think you are not calling commit
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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