TartanTaurus
asked on
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
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
are you passing value for @iPersonId and @iOppoId both? error message clearly shows that you are not passing both the value with your SP
ASKER
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
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
Could you show how you are calling the sp?
Thanks,
Lee
ASKER
Hi,
The stored proc is called from a script that runs when the record is updated:
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;
}
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
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
ASKER
Hi Lee,
Thanks for that. But this now throws an error of:
Incorrect syntax near ',' my total script now looks like this:
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;
}
You missed the space after customerrefproc:
var r = connection.Execute('exec g41.dbo.customrefproc ' + iPersonId + ', ' + iOppoId);
Lee
var r = connection.Execute('exec g41.dbo.customrefproc ' + iPersonId + ', ' + iOppoId);
Lee
ASKER
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
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;
}
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
return @nvchCustomCode
after this line:
select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)
Lee
ASKER
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!
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
Looks good. Any issues?
Cheers,
Lee
Cheers,
Lee
ASKER
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
Cheers,
Lee
ASKER
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
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_FirstNam e', 1), '-') + isnull(left('pers_lastname ', 1), '-') -- If an initial isn't found it will replace it with a -
with:
select @nvchPersInitials = isnull(left('Pers_FirstNam e', 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
select @nvchPersInitials = isnull(left('Pers_FirstNam
with:
select @nvchPersInitials = isnull(left('Pers_FirstNam
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_FirstNam e', 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_Fir stName, 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
Change:
select @nvchPersInitials = isnull(left('Pers_FirstNam
to
select @nvchPersInitials = isnull(left(Pers_FirstName
from Person where pers_personid = @iPersonId
You might want the initials upper cased as well. In which case:
select @nvchPersInitials = upper(isnull(left(Pers_Fir
from Person where pers_personid = @iPersonId
Thanks,
Lee
ASKER
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...
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
Call it a hunch:
Change the UpdateRecord to:
function UpdateRecord()
{
var iPersonId = '3';
var iOppoId = '1';
var connection = new ActiveXObject("ADODB.Conne ction");
connection.Open("Data Source=localhost\\TabletPr oject,1433 ;Provider= SQLOLEDB;U ser Id=sa;Password=xxxx;Persis t 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
Change the UpdateRecord to:
function UpdateRecord()
{
var iPersonId = '3';
var iOppoId = '1';
var connection = new ActiveXObject("ADODB.Conne
connection.Open("Data Source=localhost\\TabletPr
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
ASKER
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;
}
eWare.GetContextInfo will return a string anyway.
Cheers,
Lee
Cheers,
Lee
ASKER
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
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
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
ASKER
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.
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
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");
}
ASKER
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
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
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
ASKER
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
In the sp, change the line:
select @nvchCustomCode as CustomRef
to
select @nvchCustomCode as CustomerRef
Lee
ASKER
Almost there!!
Error returned: Opportunity Test UpdateRecord jscript error: Expected ';' Line: 24 Char: 70
Code is now as follows with all changes!
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");
}
}
Could you paste the whole tablescript?
Lee
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
Change:
var sSQL = 'exec g41.dbo.customrefproc ' + iPersonId + ', ' + iOppoId);
to
var sSQL = 'exec g41.dbo.customrefproc ' + iPersonId + ', ' + iOppoId;
Lee
ASKER
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(sS QL);
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(sS
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");
}
}
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(sS QL);
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
var oQuery = eWare.CreateQueryObject(sS
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
ASKER
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
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
ASKER
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_Scri ptID,TLog_ UserID,TLo g_Function ,TLog_Mess age,TLog_W hereClause ,TLog_Crea tedBy,TLog _CreatedDa te,TLog_Up datedBy,TL og_TimeSta mp,TLog_Up datedDate, TLog_ID) VALUES (10009,1,N'UpdateRecord',N 'Opportuni ty 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.
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
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_Scri
',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
Cheers,
Lee
ASKER
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
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
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
begin tran
exec crm_live.dbo.customrefproc
rollback tran
How many records are returned?
Cheers,
Lee
ASKER
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.
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
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
ASKER
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!
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
Cheers,
Lee
ASKER
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.
The script just causes a timeout error in CRM. High level tablescript logs:
Opportunity Test UpdateRecordscripttimedout
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
Lee
ASKER
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 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
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
ASKER
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
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
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
ASKER
No nothing has been updated
ASKER
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
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
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
ASKER
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
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
ASKER
Hi,
Entire table script is as follows. Thanks
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
}
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
if(!oQuery.eof)
{
Values("oppo_customerref")
}
I can't test at the moment so I'm doing this from memory.
Thanks,
Lee
ASKER
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
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
Cheers,
Lee
ASKER
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
Thanks