Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: SQL Connection error?

    Hi,

    I am getting two different errors when trying to run a stored procedure. The procedure updates a table and inserts a copy of the record into another table.

    this is one of the errors (they alternate):

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
    Server: Msg 11, Level 16, State 1, Line 0
    General network error. Check your network documentation.

    Connection Broken


    the other one i cant seem to replicate any more. It is infrequent.

    any help or information on what these errors mean is greatly appreciated.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I have seen this when an index is corrupted. Try running dbcc reindex - if you receive an error - drop the index and recreate. Can you post the stored procedure code ?

  3. #3
    Join Date
    Jun 2002
    Posts
    2
    Thanks for your reply,

    The tables I am dealing with dont have indexes. This same stored procedure works without a hitch in other databases and it only has a problem when I pass update to it in this db. Here is the code for the sp:

    CREATE PROCEDURE sp_insContentPool
    (

    --itemid - used only for updating
    @cpItemID integer,
    @aUserID VarChar(50),
    --mode variable (insert, update)
    @Mode varChar(20),
    -- the type variables (used only when inserting)
    @cpItem_cTypeID integer,
    @cpItem_ctVarietyID integer,
    --standard variables
    @cpItemTitle varChar(100),
    @cpItemTitleAbbrev varChar(100),
    @cpItemDescription nText,
    @cpItemMetaKeyword nText,
    @cpItemMetaDescription nText,
    @cpItemLiveDate smalldatetime,
    @cpItemArchiveDate smalldatetime,
    @cpItemAuthor varChar(100),
    @cpItemCreateDate varChar(100),
    @cpItemOwner varChar(100),
    @cpItem_pm_DivisionID integer,
    @cpItem_pm_BranchID integer,
    @cpItem_pm_SectionID integer,
    @cpItemTemplateName varChar(100),
    @cpItemRefreshDate smalldatetime,
    @cpItemExcludeSearch bit,
    @cpItemLogonAccess bit,
    @cpItemLastCheckedDate smalldatetime,
    @cpItemEnabled bit,
    -- if content type is external contact
    @cpItemExContactName varChar(100),
    @cpItemExContactPhone varChar(100),
    @cpItemExContactMobile varChar(100),
    @cpItemExContactEmail varChar(100),
    -- if content type is map
    @cpItemScale varChar(100),
    -- is isbn
    @cpItemISBN varChar(100),
    @errorCode integer OUTPUT,
    @identity integer OUTPUT
    )
    AS
    declare @BuildDate datetime
    declare @maxversion integer
    declare @Version integer
    select @errorCode = 0

    BEGIN TRANSACTION SaveRecord
    --if the mode is insert
    IF @mode = 'insert'
    BEGIN
    SELECT @version = 1

    --insert the new record
    INSERT INTO ContentPool
    (cpItem_cTypeID,cpItem_ctVarietyID,cpItemTitle,cpI temTitleAbbrev,cpItemDescription,cpItemMetaKeyword ,cpItemMetaDescription,cpItemLiveDate,cpItemArchiv eDate,cpItemAuthor,cpItemCreateDate,cpItemOwner,cp Item_pm_DivisionID,cpItem_pm_BranchID,cpItem_pm_Se ctionID,cpItemTemplateName,cpItemRefreshDate,cpIte mExcludeSearch,cpItemLogonAccess,cpItemLastChecked Date,cpItemEnabled,cpItemExContactName,cpItemExCon tactPhone,cpItemExContactMobile,cpItemExContactEma il,cpItemScale,cpItemISBN,cpItemVersion,cpItem_aUs erID)
    VALUES
    (@cpItem_cTypeID,@cpItem_ctVarietyID,@cpItemTitle, @cpItemTitleAbbrev,@cpItemDescription,@cpItemMetaK eyword,@cpItemMetaDescription,@cpItemLiveDate,@cpI temArchiveDate,@cpItemAuthor,@cpItemCreateDate,@cp ItemOwner,@cpItem_pm_DivisionID,@cpItem_pm_BranchI D,@cpItem_pm_SectionID,@cpItemTemplateName,@cpItem RefreshDate,@cpItemExcludeSearch,@cpItemLogonAcces s,@cpItemLastCheckedDate,@cpItemEnabled,@cpItemExC ontactName,@cpItemExContactPhone,@cpItemExContactM obile,@cpItemExContactEmail,@cpItemScale,@cpItemIS BN,@version,@aUserID)

    SELECT @cpItemID = @@identity
    SELECT @BuildDate = getdate()

    SELECT @identity = @@identity
    END

    --elseif mode is update
    ELSE
    BEGIN
    --get the version by counting the number of records with this id in the versions table
    SELECT @maxversion = max(vccItemVersion) from VersionControlContent where
    vccItem_cpItemID = @cpItemID

    SELECT @version = @maxversion + 1

    --update the existing record
    UPDATE ContentPool set
    cpItemTitle = @cpItemTitle,
    cpItemTitleAbbrev = @cpItemTitleAbbrev,
    cpItemDescription = @cpItemDescription,
    cpItemMetaKeyword = @cpItemMetaKeyword,
    cpItemMetaDescription = @cpItemMetaDescription,
    cpItemLiveDate = @cpItemLiveDate,
    cpItemArchiveDate = @cpItemArchiveDate,
    cpItemAuthor = @cpItemAuthor,
    cpItemOwner = @cpItemOwner,
    cpItem_pm_DivisionID = @cpItem_pm_DivisionID,
    cpItem_pm_BranchID = @cpItem_pm_BranchID,
    cpItem_pm_SectionID = @cpItem_pm_SectionID,
    cpItemTemplateName = @cpItemTemplateName,
    cpItemRefreshDate = @cpItemRefreshDate,
    cpItemExcludeSearch = @cpItemExcludeSearch,
    cpItemLogonAccess = @cpItemLogonAccess,
    cpItemLastCheckedDate = @cpItemLastCheckedDate,
    cpItemEnabled = @cpItemEnabled,
    cpItemExContactName = @cpItemExContactName,
    cpItemExContactPhone = @cpItemExContactPhone,
    cpItemExContactMobile = @cpItemExContactMobile,
    cpItemExContactEmail = @cpItemExContactEmail,
    cpItemScale = @cpItemScale,
    cpItemISBN = @cpItemISBN,
    cpItemVersion = @version,
    cpItem_aUserID = @aUserID
    WHERE cpItemID = @cpItemID

    SELECT @identity = @cpItemID

    --get the original build date
    SELECT @BuildDate = cpItemBuildDate from ContentPool
    WHERE cpItemID = @cpItemID


    END

    --now insert a copy into the version control table
    INSERT into VersionControlContent
    (vccItem_cTypeID,vccItem_ctVarietyID,vccItemTitle, vccItemTitleAbbrev,vccItemDescription,vccItemMetaK eyword,vccItemMetaDescription,vccItemLiveDate,vccI temArchiveDate,vccItemAuthor,vccItemCreateDate,vcc ItemOwner,vccItem_pm_DivisionID,vccItem_pm_BranchI D,vccItem_pm_SectionID,vccItemTemplateName,vccItem RefreshDate,vccItemExcludeSearch,vccItemLogonAcces s,vccItemLastCheckedDate,vccItemEnabled,vccItemExC ontactName,vccItemExContactPhone,vccItemExContactM obile,vccItemExContactEmail,vccItemScale,vccItemIS BN,vccItem_aUserID,vccItemBuildDate,vccItemUpdateD ate,vccItem_cpItemID,vccItemVersion)
    VALUES
    (@cpItem_cTypeID,@cpItem_ctVarietyID,@cpItemTitle, @cpItemTitleAbbrev,@cpItemDescription,@cpItemMetaK eyword,@cpItemMetaDescription,@cpItemLiveDate,@cpI temArchiveDate,@cpItemAuthor,@cpItemCreateDate,@cp ItemOwner,@cpItem_pm_DivisionID,@cpItem_pm_BranchI D,@cpItem_pm_SectionID,@cpItemTemplateName,@cpItem RefreshDate,@cpItemExcludeSearch,@cpItemLogonAcces s,@cpItemLastCheckedDate,@cpItemEnabled,@cpItemExC ontactName,@cpItemExContactPhone,@cpItemExContactM obile,@cpItemExContactEmail,@cpItemScale,@cpItemIS BN,@aUserID,@BuildDate,getdate(),@cpItemID,@versio n)

    if (@@ERROR <> 0)
    BEGIN
    ROLLBACK TRANSACTION SaveRecord
    select @errorCode = @@Error
    Goto OnExit
    END

    COMMIT TRANSACTION SaveRecord

    OnExit:
    return


    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •