[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1174
  • Last Modified:

Stored Procedure: expects parameter

Hi I have this stored proc however when I run it in the Sage CRM interface I get the following error:

Procedure 'customrefproc' expects parameter '@iPersonId', which was not supplied. Line: 25 Char: 0

Any ideas? Thanks
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)
            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

Open in new window

0
TartanTaurus
Asked:
TartanTaurus
  • 31
  • 30
2 Solutions
 
RiteshShahCommented:
are you passing value for @iPersonId and @iOppoId both? error message clearly shows that you are not passing both the value with your SP
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
Hi,

Could you show how you are calling the sp?

Thanks,

Lee
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
TartanTaurusAuthor Commented:
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; 
}

Open in new window

0
 
Lee SavidgeCommented:
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

 
0
 
TartanTaurusAuthor Commented:
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; 
}

Open in new window

0
 
Lee SavidgeCommented:
You missed the space after customerrefproc:

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


Lee
0
 
TartanTaurusAuthor Commented:
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; 
}

Open in new window

0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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

Open in new window

0
 
Lee SavidgeCommented:
Looks good. Any issues?

Cheers,

Lee
0
 
TartanTaurusAuthor Commented:
Haha sorry i forgot to say the problem is it still returns nothing!
0
 
Lee SavidgeCommented:
Is customrefproc in the same database as CRM or in a different database?

Cheers,

Lee
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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

Open in new window

0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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; 
}

Open in new window

0
 
Lee SavidgeCommented:
eWare.GetContextInfo will return a string anyway.

Cheers,

Lee
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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

0
 
TartanTaurusAuthor Commented:
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.
0
 
Lee SavidgeCommented:
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");
}

Open in new window

0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
SQL SP wants me to: Must declare the variable '@nvchCustomRef'. when /I try to commit changes?
0
 
Lee SavidgeCommented:
Sorry, typo there:

In the sp, change the line:

select @nvchCustomCode as CustomRef

to

select @nvchCustomCode as CustomerRef

Lee
0
 
TartanTaurusAuthor Commented:
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");
}
}

Open in new window

0
 
Lee SavidgeCommented:
Could you paste the whole tablescript?

Lee
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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");
}
}

Open in new window

0
 
Lee SavidgeCommented:
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

0
 
TartanTaurusAuthor Commented:
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
0
 
TartanTaurusAuthor Commented:
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!

0
 
Lee SavidgeCommented:
Then you need to put an if statement in the UpdateRecord function that checks to see if the oppo_customerref has a value. Something like the function below.

As for the script timeout. Load SQL Profiler and run that at the time you run the tablescript and see what it happening at the database level. You might find you have a SQL lock. Anything in the log files to say what the error is?

Lee



function UpdateRecord()
{
  var sCustRef = eWare.GetContextInfo("opportunity", "oppo_customerref");
 
  if(sCustRef.length > 0)
  {
    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");
    }
  }
}

Open in new window

0
 
TartanTaurusAuthor Commented:
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.
0
 
Lee SavidgeCommented:
It says SQL error in the log file there. What is in the SQL logs?

Cheers,

Lee
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
Open Management Studio and type this:

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

How many records are returned?

Cheers,

Lee
0
 
TartanTaurusAuthor Commented:
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.
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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!
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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.
0
 
Lee SavidgeCommented:
On a side note you can load profiler on the server and point it to your local db...

Lee
0
 
TartanTaurusAuthor Commented:
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?
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
No nothing has been updated
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
Hi,

I've run the profiler and attached the results.

Thanks

Chris
tracetext.txt
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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
 
}

Open in new window

0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
I think you need to change the UpdateRecord so that the following lines:

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


are:

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

As for incrementing the number, I'm not sure I follow. Could you explain further?

Lee
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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
0
 
Lee SavidgeCommented:
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
0
 
TartanTaurusAuthor Commented:
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
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 31
  • 30
Tackle projects and never again get stuck behind a technical roadblock.
Join Now