Stored Procedure: expects parameter

are you passing value for @iPersonId and @iOppoId both? error message clearly shows that you are not passing both the value with your SP

The record in question certainly has a vaild person id. CRM calls associated person to an opportunity using the field name oppo_primarypersonid.

I'm thinking does the stored proc need to refer to this field in order to get the personid through. I'm not an expert in stored procs but I would presume from my knowldege of SQL that some reference to this field is required?

Please help!

Thanks

Hi,

Could you show how you are calling the sp?

Thanks,

Lee

Hi,

The stored proc is called from a script that runs when the record is updated:

function UpdateRecord()
 
{
var connection = new ActiveXObject("ADODB.Connection");
connection.Open("Data Source=localhost\\TabletProject,1433;Provider=SQLOLEDB;User Id=sa;Password=xxx;Persist Security Info=True;Initial Catalogue=G41;user instance=true;");
 
var r = connection.Execute('exec g41.dbo.customrefproc');
 
oppo_customref = r; 
}

Select allOpen in new window

Hi,

If this SP is in the CRM database then you don't need the connection string. You can just reference it directly. The problem is that you're not passing in any parameters. You are just executing the sp. You have:

var r = connection.Execute('exec g41.dbo.customrefproc');
 
It should be something like:

var r = connection.Execute('exec g41.dbo.customrefproc ' + persid + ', ' + oppoid);

You will need to define both these variables and ensure they have values before callinig it.

Cheers,

Lee

 

Hi Lee,

Thanks for that. But this now throws an error of:

Incorrect syntax near ',' my total script now looks like this:

function UpdateRecord()
 
{
var iPersonId = eWare.GetContextInfo("opportunity", "oppo_youngperson");
var iOppoId = eWare.GetContextInfo ("opportunity", "oppo_opportunityid");
var connection = new ActiveXObject("ADODB.Connection");
connection.Open("Data Source=localhost\\TabletProject,1433;Provider=SQLOLEDB;User Id=sa;Password=xxxx;Persist Security Info=True;Initial Catalogue=G41;user instance=true;");
 
var r = connection.Execute('exec g41.dbo.customrefproc' +  iPersonId + ', ' + iOppoId);
 
oppo_customref = r; 
}

Select allOpen in new window

You missed the space after customerrefproc:

var r = connection.Execute('exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);


Lee

Thanks that sorted that error. For the purposes of testing I have suppled the variables iPersonId and iOppoId with intger values as below, however when I runm the script nothing appears to happen.

The field oppo_customref is not updated yet no error message appears within the CRM interface. Any ideas?!

Thanks

function UpdateRecord()
 
{
var iPersonId = 3;
var iOppoId = 1;
var connection = new ActiveXObject("ADODB.Connection");
connection.Open("Data Source=localhost\\TabletProject,1433;Provider=SQLOLEDB;User Id=sa;Password=xxxx;Persist Security Info=True;Initial Catalogue=G41;user instance=true;");
 
var r = connection.Execute('exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);
 
oppo_customref = r; 
}

Select allOpen in new window

Your sp isn't returning anything. You need to set the return statement in the sp. A line at the end something like:

return @nvchCustomCode

after this line:

select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)

Lee

Right the updated stored proc looks like this:

I really appreciate this help, as you can see I'm an absolute beginner on stored procs!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER procedure [dbo].[customrefproc] (@iPersonId as int, @iOppoId as int)
as
begin
    declare @nvchPersInitials as nvarchar(2),
            @nvchRegionCode as nvarchar(2),     -- I'm assuming it's always a 2 letter string. Alter to suit needs
            @iCompanyId as int,
            @iCustomId as int,
            @nvchCustomCode as nvarchar(15)
 
    if exists (select * from person where pers_personid = isnull(@iPersonId, 0) and pers_deleted is null) -- Does the person exist?
    begin
        select @nvchPersInitials = isnull(left('Pers_FirstName', 1), '-') + isnull(left('pers_lastname', 1), '-') -- If an initial isn't found it will replace it with a -
 
        if exists (select * from opportunity where oppo_opportunityid = isnull(@iOppoId, 0) and oppo_deleted is null) -- Does the oppo exist?
        begin
            select @iCompanyId = isnull(oppo_primarycompanyid, 0)
              from Opportunity
             where oppo_opportunityid = @iOppoId
               and oppo_deleted is null
 
            if @iCompanyId > 0 -- Did we get a company ID back?
            begin
                -- I'll assume a one to one relationship between soci and company
                select @nvchRegionCode = soci_code
                  from company
 
                  join SocialWorkRegion
                    on soci_socialworkregionid = comp_socialworkregionid
 
                 where comp_companyid = @iCompanyId
                   and comp_deleted is null
            end
            else
            begin
                -- No company ID found
                return 4
            end
 
            -- Now we have all the bits we need the next ID number in the series by calling the Sage CRM sp to generate the next ID properly
            exec @iCustomId = eware_get_identity_id 'CustomRefs'
 
            if isnull(@iCustomId, 0) > 0
            begin
                -- This is the line that will give you your code in the form XX-YY-NNNNN
                select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)
				return @nvchCustomCode
            end
            else
            begin
                -- Didn't get an ID back from the internal sp
                return 3
            end
        end
        else
        begin
            -- Oportunity doesn't exist or one wasn't passed in
            return 2
        end
    end
    else
    begin
        -- Person doesn't exist or an ID wasn't passed in
        return 1
    end
end

Select allOpen in new window

Looks good. Any issues?

Cheers,

Lee

Haha sorry i forgot to say the problem is it still returns nothing!

Is customrefproc in the same database as CRM or in a different database?

Cheers,

Lee

it's in the same database named G41

Now I've actually got this message returned when I hard code the person and oppo id:

Syntax error converting the nvarchar value 'Pp-DG-06003' to a column of data type int.

Everything other than the Pp bit looks ok...I can confirm there are no persons in the database with the initials Pp...so I'm lost as to where this would come from.

Thanks

Replace:

        select @nvchPersInitials = isnull(left('Pers_FirstName', 1), '-') + isnull(left('pers_lastname', 1), '-') -- If an initial isn't found it will replace it with a -

with:

        select @nvchPersInitials = isnull(left('Pers_FirstName', 1), '-') + isnull(left('pers_lastname', 1), '-') -- If an initial isn't found it will replace it with a -
          from Person where pers_personid = @iPersonId

Also, what type is this field: oppo_customref

If it is an int, you will get that error. You need to change the field type to text in CRM.

Cheers,

Lee

Just realised where the Pp has come from...


Change:

        select @nvchPersInitials = isnull(left('Pers_FirstName', 1), '-') + isnull(left('pers_lastname', 1), '-') -- If an initial isn't found it will replace it with a -

to

        select @nvchPersInitials = isnull(left(Pers_FirstName, 1), '-') + isnull(left(pers_lastname, 1), '-') -- If an initial isn't found it will replace it with a -
          from Person where pers_personid = @iPersonId

You might want the initials upper cased as well. In which case:


        select @nvchPersInitials = upper(isnull(left(Pers_FirstName, 1), '-') + isnull(left(pers_lastname, 1), '-')) -- If an initial isn't found it will replace it with a -
          from Person where pers_personid = @iPersonId

Thanks,

Lee

Brilliant stuff that's got rid of the Pp and is returning the correct initials for each record along with the correct region code and a number.

Still returning the error: Syntax error converting the nvarchar value 'AW-H -06015' to a column of data type int. Line: 27 Char: 0

Full Stored Proc script is now below. The field customref is a text field...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER procedure [dbo].[customrefproc] (@iPersonId as int, @iOppoId as int)
as
begin
    declare @nvchPersInitials as nvarchar(2),
            @nvchRegionCode as nvarchar(2),     -- I'm assuming it's always a 2 letter string. Alter to suit needs
            @iCompanyId as int,
            @iCustomId as int,
            @nvchCustomCode as nvarchar(15)
 
    if exists (select * from person where pers_personid = isnull(@iPersonId, 0) and pers_deleted is null) -- Does the person exist?
    begin
	select @nvchPersInitials = upper(isnull(left(Pers_FirstName, 1), '-') + isnull(left(pers_lastname, 1), '-')) -- If an initial isn't found it will replace it with a -
          from Person where pers_personid = @iPersonId
 
 
        if exists (select * from opportunity where oppo_opportunityid = isnull(@iOppoId, 0) and oppo_deleted is null) -- Does the oppo exist?
        begin
            select @iCompanyId = isnull(oppo_primarycompanyid, 0)
              from Opportunity
             where oppo_opportunityid = @iOppoId
               and oppo_deleted is null
 
            if @iCompanyId > 0 -- Did we get a company ID back?
            begin
                -- I'll assume a one to one relationship between soci and company
                select @nvchRegionCode = soci_code
                  from company
 
                  join SocialWorkRegion
                    on soci_socialworkregionid = comp_socialworkregionid
 
                 where comp_companyid = @iCompanyId
                   and comp_deleted is null
            end
            else
            begin
                -- No company ID found
                return 4
            end
 
            -- Now we have all the bits we need the next ID number in the series by calling the Sage CRM sp to generate the next ID properly
            exec @iCustomId = eware_get_identity_id 'CustomRefs'
 
            if isnull(@iCustomId, 0) > 0
            begin
                -- This is the line that will give you your code in the form XX-YY-NNNNN
                select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)
				return @nvchCustomCode
            end
            else
            begin
                -- Didn't get an ID back from the internal sp
                return 3
            end
        end
        else
        begin
            -- Oportunity doesn't exist or one wasn't passed in
            return 2
        end
    end
    else
    begin
        -- Person doesn't exist or an ID wasn't passed in
        return 1
    end
end

Select allOpen in new window

Call it a hunch:

Change the UpdateRecord to:

function UpdateRecord()
 
{
var iPersonId = '3';
var iOppoId = '1';
var connection = new ActiveXObject("ADODB.Connection");
connection.Open("Data Source=localhost\\TabletProject,1433;Provider=SQLOLEDB;User Id=sa;Password=xxxx;Persist Security Info=True;Initial Catalogue=G41;user instance=true;");
 
var r = connection.Execute('exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);
 
oppo_customref = r;
}




I've changed the person and oppo ID's to strings.

Lee

The finished script that I'm calling will be this. this eWare.GetContext is from the eWare object model and simply pulls the integers of the oppportunity id and person id. So I can't change those numbers to strings, I'd only put them there for testing.

function UpdateRecord()
 
{
var iPersonId = eWare.GetContextInfo("opportunity","oppo_youngperson");
var iOppoId = eWare.GetContextInfo("opportunity","oppo_opportunityid");
var connection = new ActiveXObject("ADODB.Connection");
connection.Open("Data Source=localhost\\TabletProject,1433;Provider=SQLOLEDB;User Id=sa;Password=xxxx;Persist Security Info=True;Initial Catalogue=G41;user instance=true;");
 
var r = connection.Execute('exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);
 
oppo_customref = r; 
}

Select allOpen in new window

eWare.GetContextInfo will return a string anyway.

Cheers,

Lee

Any ideas then what this means: Syntax error converting the nvarchar value 'AW-H -06015' to a column of data type int. Line: 27 Char: 0

That seems to suggest my column is an integer, but the field customref is a Text field. WOuld the Stored Proc be saying it is an Integer?

Thanks

Hi,

Just to satisy my curiosity that you're doing the right thing here... Could you tell me why you're creating a connection object and executing a string against it? I am going to make some assumptions here so please tell me which are right and wrong:

1. You have a new table in CRM
2. This table was created either using the entity wizard or created via tables and databases in the admin part of CRM.
3. You need to call an SP to generate an ID string to write into a newly created record in that table.
4. This new table has a 1 to 1 relationship with an opportunity record. ie. One opportunity can have one customref and one customref can belong to one opportunity only.

Lee

1 &2. This table is the opportunity table not a new one.

3. The SP is used to generate an ID string on update but not on insert.

4. Standard relationship exists between Opportunity and Person. However in the opportunity table I'm not using the oppo_primarypersonid foreign key field...I'm using a custom one called oppo_youngperson which operates as a search select advanced within CRM. Ultimately I believe it still returns an integer.

Hi,

Ok, then you don't need to use the connection string in the way you are. CRM will do it for you. First of all, chane the sp return statement so that it reads:

select @nvchCustomCode as CustomRef

This forces the sp to return a recordset rather than a return value.

Then you can do the UpdateRecord function below. If this tablescript is against the opportunity, you may need to do a SaveChanges() against the record. If not it will need special handling. Where is the tablescript?

Cheers,

Lee

function UpdateRecord()
 
{
var iPersonId = eWare.GetContextInfo("opportunity","oppo_youngperson");
var iOppoId = eWare.GetContextInfo("opportunity","oppo_opportunityid");
var sSQL = 'exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);
var oQuery = eWare.CreateQueryObject(sSQL);
oQuery.SelectSql();
 
while(!oQuery.eof)
{
  oppo_customref = oQuery("CustomRef");
}

Select allOpen in new window

My apologies the field is actually called oppo_customerref, I'm getting confused with the name of the stored proc. However should I just change each instance of CustomRef above to customerref?

And what about the SP line should that read CustomerRef instead of CustomRef?

Thanks

Hi,

change this:

while(!oQuery.eof)
{
  oppo_customref = oQuery("CustomRef");
}

to

while(!oQuery.eof)
{
  oppo_customerref = oQuery("CustomerRef");
}


In the sp, change the line:

select @nvchCustomCode as CustomRef

to

select @nvchCustomRef as CustomerRef

That should do it.

Lee

SQL SP wants me to: Must declare the variable '@nvchCustomRef'. when /I try to commit changes?

Sorry, typo there:

In the sp, change the line:

select @nvchCustomCode as CustomRef

to

select @nvchCustomCode as CustomerRef

Lee

Almost there!!

Error returned: Opportunity Test UpdateRecord jscript error: Expected ';' Line: 24 Char: 70

Code is now as follows with all changes!

function UpdateRecord()
 
{
var iPersonId = eWare.GetContextInfo("opportunity","oppo_youngperson");
var iOppoId = eWare.GetContextInfo("opportunity","oppo_opportunityid");
var sSQL = 'exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);
var oQuery = eWare.CreateQueryObject(sSQL);
oQuery.SelectSql();
 
while(!oQuery.eof)
{
  oppo_customerref = oQuery("CustomerRef");
}
}

Select allOpen in new window

Could you paste the whole tablescript?

Lee

Ahh, no I see the error:

Change:

var sSQL = 'exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId);


to

var sSQL = 'exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId;

Lee

Thanks I couldn't see that even after 6 attempts! However i've now got this error:

Opportunity Test UpdateRecord jscript error: Object doesn't support this property or method Line: 25 Char: 0

Line 25 appears to point to the:
var oQuery = eWare.CreateQueryObject(sSQL);

function UpdateRecord()
 
{
var iPersonId = eWare.GetContextInfo("opportunity","oppo_youngperson");
var iOppoId = eWare.GetContextInfo("opportunity","oppo_opportunityid");
var sSQL = 'exec g41.dbo.customrefproc ' +  iPersonId + ', ' + iOppoId;
var oQuery = eWare.CreateQueryObject(sSQL);
oQuery.SelectSql();
 
while(!oQuery.eof)
{
  oppo_customerref = oQuery("CustomerRef");
}
}

Select allOpen in new window

This might sound daft, but I would change the single quotes to double quotes on the line with the sp exec. Also, this line:

var oQuery = eWare.CreateQueryObject(sSQL);

should be

var oQuery = eWare.CreateQueryObj(sSQL);

Do you have a copy of the CRM developer guide? It is usually bundled in one of the folders in the Sage installer.

Lee

Hi Lee,

Thanks for that. I dug out my developer guide this morning and changed the Object to Obj. I've changed the exec line to:

var sSQL = "exec g41.dbo.customrefproc " +  iPersonId + ", " + iOppoId;

However with single or double quotes the following error occurs:

Opportunity Test UpdateRecordscripttimedout
Opportunity Test UpdateRecord jscript error: Line: 28 Char: 0

I've been into the Administration>System> Database and changed the query timeout value from 60 to 120secs. Strange thing is though it actually worked on one record returning the expected result...but now it just throws this error.

Thanks

Chris

Right just noticed something else...on the record it did update, it continues to update that field but it increases the ID number everytime the record is updated!

CRM system log file shows:
SQL Error : Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done (exec crm_live.dbo.customrefproc 614, 253

I've loaded this onto my live server to make use of the profiler...now I haven't a clue what to be looking for. I've run a trace etc and can't make much sense of what's come back:

INSERT INTO TableScriptsLogs(TLog_ScriptID,TLog_UserID,TLog_Function,TLog_Message,TLog_WhereClause,TLog_CreatedBy,TLog_CreatedDate,TLog_UpdatedBy,TLog_TimeStamp,TLog_UpdatedDate,TLog_ID)  VALUES (10009,1,N'UpdateRecord',N'Opportunity YoungPersonID UpdateRecord jscript error: SQL Error Line: 30 Char: 4
',N'Oppo_OpportunityID = 253',1,'20090513 11:28:00',1,'20090513 11:28:00','20090513 11:28:00',4)

Much more was returned but I'm not sure what's relevant or how I could filter the trace to only get back the important bit.

It says SQL error in the log file there. What is in the SQL logs?

Cheers,

Lee

It's quite similar to the above:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done
May 13 2009 11:45:06.562      7712      3512      1      fqopen,time,sql,errormsg      8625      exec crm_live.dbo.customrefproc 614, 253

Thanks

Chris

Open Management Studio and type this:

begin tran
exec crm_live.dbo.customrefproc 614, 253
rollback tran

How many records are returned?

Cheers,

Lee

I get numerous lines of:
Msg 515, Level 16, State 2, Procedure eware_get_identity_id, Line 51
Cannot insert the value NULL into column 'Lock_TableId', table 'CRM_live.dbo.Locks'; column does not allow nulls. INSERT fails.
The statement has been terminated.

And then hundreds of:
The statement has been terminated.

Just out of interest, the table that you created... It is called CustomRefs and it is in the database and it was defined using the tables and databases or the entity wizard?

The table referenced here...

exec @iCustomId = eware_get_identity_id 'CustomRefs'


That SQL error suggests that there was no table found by the eware_get_identity_id sp.

Cheers,

Lee

Sorry I moved the stuff to the live server where I've got the SQL profiler and forgot to create that table!

Having created the table I ran the roll back and it returned one row with a valid and correct looking value for the custom ID....

However as I run the update now CRM just stalls and won't go back to the record in view mode after editing. It actually returns me to the login screen saying i'm not currently logged in!

Put the tablescript error logging to high and then try again. Are there any error returned? Run profiler against it when you do it and see what happens. Where does profiler stop and show you getting booted?

Cheers,

Lee

Hi,

The script just causes a timeout error in CRM. High level tablescript logs:
Opportunity Test UpdateRecordscripttimedout Opportunity Test UpdateRecord jscript error: Line: 32 Char: 4

Clearly it works given that the roll back showed a valid result. I can't see why it's timing out though. I don't have SQL profiler on my development server only SQL express.

On a side note you can load profiler on the server and point it to your local db...

Lee

Right....I assumed by express version didn't have profiler but it does - problem solved no need for remote connections!

I can't see any obvious errors in the trace nothing jumps out to say failure. Is there some key words I should look for?

I just had a thought. As you're passing in the opportunity ID into the sp why don't you just update the opportunity in the sp rather than pass back to the UpdateRecord?

Where the code reads:

select @nvchCustomCode

You could do an update there:

update opportunity set oppo_customerref = @nvchCustomCode where oppo_opportunityid = @iOppoId

Technically speaking you're not supposed to update CRM records directly without going through the DLL. But for the puposes of eliminating a level of complexity, alter that line and try again. Let me know if that works and doesn't lock. If it doesn't lock there is another option.

Lee

Right tried that, made no changes to the tablescript and this error is returned:

Opportunity Test UpdateRecord jscript error: Operation is not allowed when the object is closed Line: 32 Char: 4

Thanks

Hi,

That's fine. The tablescript will expect a recordset which didn't come back from the sp. Did the opportunity record get updated with the correct value in oppo_customerref?

Thanks,

Lee

No nothing has been updated

Hi there,

Beenm playing around with a couple of things this morning:
Changed the line where we updated the opportunity directly in the SP back to select @nvchCustomCode as CustomerRef

I had a hunch when I woke up this morning and it proved a good one! The code works fine where a region code has a value i.e is not null. I had been trying to update on null values. When I ensure a 2 charatcer code is there it works spot on AB-DG-0601 for example.

However I still get the error that the Script timed out. Functionally it appears ok, is there anyway I can get around that error message?

Thanks

Chris

Hi,

You need to run this against profiler to see what line is causing the timeout. Run profiler just at the point before you hit save to run the tablescript and then stop it once it has saved. Post back the output from profiler here and I'll take a look.

Thanks,

Lee

Hi,

I've run the profiler and attached the results.

Thanks

Chris
tracetext.txt

Hi,

I don't think there is a fault with the SQL now. I think that is doing everything it should. We may need to rethink the tablescript. Could you post the whole script, including any stub functions?

Thanks,

Lee

Hi,

Entire table script is as follows. Thanks

function InsertRecord()
 
{
 
  // Handle insert record actions here
 
}
 
 
function PostInsertRecord()
 
{
 
 
 
}
 
 
function UpdateRecord()
 
{
  var sCustRef = eWare.GetContextInfo("opportunity", "oppo_customerref");
 
  if(sCustRef.length < 1)
  {
    var iPersonId = eWare.GetContextInfo("opportunity","oppo_youngperson");
    var iOppoId = eWare.GetContextInfo("opportunity","oppo_opportunityid");
    var sSQL = "exec g41.dbo.customrefproc " +  iPersonId + ", " + iOppoId;
    var oQuery = eWare.CreateQueryObj(sSQL);
    oQuery.SelectSql();
 
    while(!oQuery.eof)
  {
     oppo_customerref = oQuery("CustomerRef");
    }
  }
}
 
function DeleteRecord()
 
{
 
  // Handle delete record actions here
 
}

Select allOpen in new window

Hi again,

The above script still times out but it's working as expected so that'll do. I believe others have had similar problems with the UpdateScript so it maybe this is a CRM bug...not sure.

My other question is in relation to this one. I'll open another thread with points etc if it's possible although I have a feeling you might say it's not!!

I'd like to run the above script as we have done but is it possible to start the incrementing number from a given number say 300? We already have a numbering convention in place and I'd like to continue with that if possible. I'm thinking the 6000 comes from the Sage SPs so I'm not sure if its possible to alter the start point.

Thanks

Chris

Does the sp now just return the value of CustomerRef? If it does it might be that changing the javascript to:

   if(!oQuery.eof)
   {
     Values("oppo_customerref") = oQuery("CustomerRef");
   }

I can't test at the moment so I'm doing this from memory.

Thanks,

Lee

Hi,

Changing the Javascript to "if" has done the trick!! The SP runs in a flash with no timeouts etc.

Thanks very much. Appreciate all your help with this one!

As for the incrementing number...id like the number to start at 300 instead of the 6000 which appears to be a sage default. Is this possible?

Chris

It is possible to alter. The number exists in the SQL_Identity table. The id_TableID is the ID for the custom table which you can get from the custom_tables table. If you do alter it, do be careful to update the rep_ranges table accordingly if you use solo. If you don't use solo this isn't too important.

Cheers,

Lee

Ok I've done that but it's thrown a couple of questions so I think I'll fire it up as a new thread with points. I'll post the link here shortly.

Thanks