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

Get number of records for an sObject using DBAmp in an OPENQUERY call via a stored procedure?

So the DBAmp stored procs are great, but I'm struggling to get the number of records for a given sObject as a value. I want to do this so that I can use a different **SF_Retrieve** approach based on the number of sObject records.

The docs recommend using:

Select * from
OPENQUERY(SALESFORCE,'Select Count() from Account')

...and this will return the value I want. However, I'm struggling to return this out of a stored proc - looking like due to the use of OpenQuery and the dynamic inner query.

CREATE PROCEDURE [dbo].[proc](
@SFObjectName AS VARCHAR(100))
AS
BEGIN
DECLARE @thisQuery nvarchar(max) = 'SELECT Count(*) FROM ' + @SFObjectName;
EXEC('SELECT * FROM OPENQUERY (SFDC_PBS, ''' + @thisQuery + ''')')
END

GO

The above works, but when I try to add an Output variable to the proc, I just can't get hold of the return value from the EXEC call.
1 person has
this question
+1
Reply
  • Here's my other attempt at this:

    CREATE PROCEDURE [dbo].[Return_GetCountOfSFObject](
    @SFObjectName AS VARCHAR(100),
    @returnVal as INTEGER OUTPUT)
    AS
    BEGIN
    DECLARE @sql nvarchar(max)

    SET @sql = 'SELECT @returnValOuter = @ReturnValInner FROM OPENQUERY (SFDC_PBS,
    ''SELECT @ReturnValInner = Count() FROM ' + @SFObjectName + ''') A';

    EXEC SP_executesql @SQL, N'@SFObjectName VARCHAR (100), @returnValOuter varchar(50) OUTPUT', @SFObjectName, @returnValOuter = @returnVal OUTPUT;

    END
    GO

    ----------/use created proc.
    DECLARE @RC int
    DECLARE @SFObjectName varchar(100)
    DECLARE @returnVal int

    -- TODO: Set parameter values here.

    EXECUTE @RC = [dbo].[Return_GetCountOfSFObject]
    @SFObjectName = 'User'

    PRINT (@RC + 1)
    GO

    ...where the number of users (plus 1) in the SF object should be printed
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Here is code that works for me:


    -- Get count of rows of salesforce table
    declare @sf_count int
    declare @sql nvarchar(max)
    declare @parmlist nvarchar(2000)
    declare @table_server nvarchar(30)
    declare @table_name nvarchar(100)
    set @table_server = 'Salesforce'
    set @table_name = 'Account'

    select @sql = 'Select @SFCountOUT = expr0 from '
    select @sql = @sql + ' openquery(' + @table_server + ',''Select Count(Id) from ' + @table_name + ' '')'
    select @parmlist = '@SFCountOUT int OUTPUT'

    exec sp_executesql @sql,@parmlist, @SFCountOUT=@sf_count OUTPUT

    print Cast(@sf_count as nvarchar(10))
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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