Help get this topic noticed by sharing it on Twitter, Facebook, or email.

sf_refresh 'repair' and 'subset' throwing NULL error

I am using sf_refresh against the attachment table -- however, I do not have the [body] column locally (I do not need the actual attachment data, just the metadata).

When I ran with 'repair' option I got a NULL not allowed error today, so I changed to 'subset' and got the following error?

--- Starting SF_Refresh for Attachment
11:45:05: Using Schema Error Action of subset
11:45:06: Using last run time of 2012-03-27 11:04:00
11:45:12: Indentified 11 updated/inserted rows.
11:45:12: Warning: Table schema has changed. SF_Refresh will use the valid subset of columns.
11:45:12: Indentified 0 deleted rows.
11:45:12: Adding updated/inserted rows into Attachment
Warning: Null value is eliminated by an aggregate or other SET operation.
11:50:14: Warning: The row counts of the local table and salesforce differ by -289 rows.
--- Ending SF_Refresh. Operation successful.

Note the NULL issue in the warning section and now the difference of 289 rows?

Any ideas what the issue is?

I am on version 2.14.4
1 person has
this question
+1
Reply
  • also note: I have set all the local columns in the attachment table to nullable, sitll get the error; its somewhere in the sproc.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Can you send me a SQL Create Table statement of your current local Attachment table ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • CREATE TABLE [dbo].[Attachment]
    (
    [BodyLength] [int] NULL,
    [ConnectionReceivedId] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ConnectionSentId] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ContentType] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreatedById] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreatedDate] [datetime2] NOT NULL,
    [Description] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Id] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsDeleted] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsPartnerShared] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsPrivate] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LastModifiedById] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LastModifiedDate] [datetime2] NOT NULL,
    [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [OwnerId] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ParentId] [nchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SystemModstamp] [datetime2] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • sorry, the last post was the cached version from my red-gate sql prompt:

    CREATE TABLE [dbo].[Attachment](
    [BodyLength] [int] NULL,
    [ConnectionReceivedId] [nchar](18) NULL,
    [ConnectionSentId] [nchar](18) NULL,
    [ContentType] [nvarchar](120) NULL,
    [CreatedById] [nchar](18) NULL,
    [CreatedDate] [datetime2](7) NULL,
    [Description] [nvarchar](500) NULL,
    [Id] [nchar](18) NULL,
    [IsDeleted] [varchar](5) NULL,
    [IsPartnerShared] [varchar](5) NULL,
    [IsPrivate] [varchar](5) NULL,
    [LastModifiedById] [nchar](18) NULL,
    [LastModifiedDate] [datetime2](7) NULL,
    [Name] [nvarchar](255) NULL,
    [OwnerId] [nchar](18) NULL,
    [ParentId] [nchar](18) NULL,
    [SystemModstamp] [datetime2](7) NULL
    ) ON [PRIMARY]

    GO
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • I tried to recreate the error by eliminating the body column but did not get the same error.

    I would suggest doing an sf_replicate of the Attachment table to resync it. Then eliminate the Body column and try the SF_Refresh again.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • So I took the contents of the sf_refresh proc and debugged it as just t-sql...

    here is what is happening:
    its getting to this line (its the line that gets delta data from SF):
    exec sp_executesql @sql, @parmlist, @LastTimeIN=@last_time

    here are the parameters:
    @sql
    Select * into [Attachment_Delta2012-04-02T12:53:00.030] from salesforce...[Attachment] where SystemModstamp > @LastTimeIN }}

    @paramlist
    @LastTimeIN datetime

    @last_time
    2012-03-29 19:52:00.000

    and this is where it fails:
    Msg 681, Level 16, State 7, Line 1
    Attempting to set a non-NULL-able column's value to NULL.

    so, you see, its the SELECT INTO statement that is failing -- which is strange because the INTO statement is making the table on the fly per dbamp's definition of the table then trying to push data into the table - so could the data in the SalesForce attachment table had records that are not valid per the columns nullability?

    Note: I am testing this by changing the tablelastrefresh value and debugging again...
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • I would NOT recommend changing the tablelastrefreshvalue.

    Did you recently upgrade DBAmp ? Was there a SQL restart performed as part of the upgrade ?

    I would recommend doing an sf_replicate (not sf_refresh) of the Attachment table to see if you can get a full copy down.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • thanks for the reply bill, this is on a test server at the moment so I am not worried about the tablerefresh..

    however, I did find the offending attachment in salesforce.
    In the UI it shows the file size as 10.23MB... in running the sql I get NULL for the 'Body' field but in the UI I can open the attachment...

    here are the two queries (I have changed the ID of the attachement)

    SELECT * FROM salesforce...Attachment WHERE Id = 'theBadAttachmentId'
    SELECT * FROM OPENQUERY(salesforce, 'select * from Attachment WHERE Id = ''theBadAttachmentId''') o

    So, could your dbamp code or SF be NULLing the BODY column due to the bodylength size (10824704) ? Its somewhere in your ODBC or upstream from that..
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • we did upgrade to 2.14.4 but we did reboot after the upgrade (and one more time since due to patching the server) - however, as noted above, this is happening for a particular record to date...
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • DBAmp does indeed return a NULL when the length of the Body exceeds a value in the registry. This is a change from the earlier version.

    For some reason, the Body column is missing from your Attachment table.

    That is why I wanted to run a sf_replicate. SF_Replicate will rebuild the table in the correct schema.

    In the upgrade instructions, we ask you to sf_replicate your tables before resuming your sf_refresh. My guess is that the initial error was caused by the fact that the Body column was NOT NULL but the upgraded code wanted to put a NULL in the value. The sf_replicate will change the schema to allow nulls.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Thanks Bill, for the quick responses.

    I still see the following issues:
    1. sf_refresh will have to take this into account for any record created since the last replicate otherwise it errors out. In my case this record is NOT in the table before the sf_refresh went to find it. This record was created and my sf_refresh found it but the SELECT INTO in the sf_refresh sproc will always error out now or anytime it exceeds....

    2. Admittedly, I am not using your sf_refresh the way you expect me to. I NEVER want the 'body' column in my local table, just the other columns. So using sf_refresh with 'subset' seemed the easiest course of action...

    3. which brings me to my feature request/fix: in your sf_refresh the code brings back all the data and columns since the last date X into a local table. Given that I selected 'subset' or even 'subsetdelete' or 'repair' we dont need all the data to come down.. seems best to build the temp table to either match my local table and/or just get the schema from salesforce to compare columns, then only get the actual data against the salesforce table that matches columns...

    A) in my case, since I do not have the Body column, the above feature/fix would solve my issue..
    B) however, in general, the non-nullability of a column in your SELECT INTO table per the SF schema then getting a NULL from SF is a fatal error that seems to be a bug in the current version....

    thanks again, you are always timely and accurate...
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • PS - I just realized you have a statement in your post that the sf_replicate will make the column NULLable. That may work for the attachment local table but it doesnt happen on the SELECT INTO statement your sf_refresh code dynamically generates against SF...
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Run the following Select statement and then examine the type of the Body column in the Attachment_Schema. On my system it is image NULL.

    What is it on your system ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Sorry here is the SQL:
    select * into Attachment_Schema from salesforce...attachment where 1 =0
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • I did the following:
    1. ran your sql test script -- Body is NOT Nullable
    2. ran your 2.14.5 update
    3. restarted sql/agent/sql browser
    4. ran your 'Create DBAmp SPROCS.sql' script
    5. ran your sql test script -- Body is NOT Nullable
    6. rebooted sql server
    7. ran your sql test script -- Body is NOT Nullable

    A. could it be that blackboard set the field to not nullable in the salesforce UI (customized the attachment table?)?
    B. does it really make sense that the ATTACHMENT table can have NULL for the actual attachment? seems pointless :)

    what now?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Send an email to support at forceamp.com with some date /times that would work for a web meeting.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated