9.5 Upgrade & Live Report With Stored Procedures

We just recently upgraded from LL 921 to 951 using SQL 2000 SP4 and an existing LiveReport that calls a stored procedure in LL921 will not run now in 951 - databaseuser is set as owner.We get the following error message (see screen shot)only thing that makes it work is to add the username with database owner in front of every view or table name.Have already checked for "SET NOCOUNT ON"Have tried changing every sysobjects to dbo.Have tried adding "exec " in front of stored procedure name in the LiveReport.Does anyone have an example of a LiveReport & Stored Procedure that calls out Livelink Tables that we could try ? Thanks,Chad Latka (504)654-3386Melinda Lew (504)654-2747Northrop Grumman

Find more posts tagged with

Comments

Chad Latka

Attached is the stored procedure that gives trouble.I will follow up with the LiveReport next.

Chad Latka

Here is the LiveReport that calls out the stored procedure that errors out in LL 951. It runs in LL921Note: The LiveReport has inputs from a customview.html file that passes the values to the stored procedure.

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Just curious: Are any of the values being passed into parameters A1, A2, A3, or A4 blank?

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 3:16 PM
To: eLink Recipient
Subject: 9.5 Upgrade & Live Report With Stored Procedures

9.5 Upgrade & Live Report With Stored Procedures

Posted by northo01user2 (Latka, Chad) on 2009/06/15 15:13

We just recently upgraded from LL 921 to 951 using SQL 2000 SP4 and an existing LiveReport that calls a stored procedure in LL921 will not run now in 951 - database
user is set as owner.

We get the following error message (see screen shot)
only thing that makes it work is to add the username with database owner in front of every view or table name.

Have already checked for "SET NOCOUNT ON"
Have tried changing every sysobjects to dbo.
Have tried adding "exec " in front of stored procedure name in the LiveReport.

Does anyone have an example of a LiveReport & Stored Procedure that calls out Livelink Tables that we could try ?

Thanks,

Chad Latka (504)654-3386
Melinda Lew (504)654-2747
Northrop Grumman

Chad Latka

Bhupinder,Yes my co-worker Melinda said with regards to this particular stored procedure that when the tables/views wer built - they were imported from mainframe tables and that some of the records have blank spots. So there are rows that may not contain some data for the columns.I tried from an SQL Query Analyzer window:exec sp_LLReport_EmpRec_PAS_Search '','','', '2004'but get:Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'uv_ER_PAS_BadgeYear'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'DTree'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'DVersData'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblPASEmployees'.SELECT DTree.*, DVersData.MimeType, DVersData.Version, uv_ER_PAS_BadgeYear.Badge, tblPASEmployees.Name AS Employee, tblPASEmployees.Dept, uv_ER_PAS_BadgeYear.[Year] FROM uv_ER_PAS_BadgeYear INNER JOIN DTree ON uv_ER_PAS_BadgeYear.DataID = DTree.DataID INNER JOIN DVersData ON DTree.DataID = DVersData.DocID LEFT OUTER JOIN tblPASEmployees ON uv_ER_PAS_BadgeYear.Badge = tblPASEmployees.Badge AND uv_ER_PAS_BadgeYear.[Year] = tblPASEmployees.[Year] WHERE uv_ER_PAS_BadgeYear.[Year] = '2004' ORDER BY uv_ER_PAS_BadgeYear.Badge, uv_ER_PAS_BadgeYear.[Year]

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

You had indicated "The LiveReport has inputs from a customview.html file that passes the values to the stored procedure". Are any of the inputs blank when the stored procedure is called? If so, arrange a test where none of the inputs are blank to see if the problem goes away. I don't think having blank spots in the records in the tables would be problematic, but having blank inputs could be.

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:23 PM
To: eLink Recipient
Subject: Bhupinder,

Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21
In reply to: RE 9.5 Upgrade & Live Report With Stored Procedures		Posted by bsingh (Singh, Bhupinder) on 2009/06/15 15:44

Bhupinder,
Yes my co-worker Melinda said with regards to this particular stored procedure that when the tables/views wer built - they were imported from mainframe tables and that some of the records have blank spots. So there are rows that may not contain some data for the columns.

I tried from an SQL Query Analyzer window:

exec sp_LLReport_EmpRec_PAS_Search '','','', '2004'

but get:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'uv_ER_PAS_BadgeYear'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DTree'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DVersData'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblPASEmployees'.
SELECT DTree.*,
DVersData.MimeType,
DVersData.Version,
uv_ER_PAS_BadgeYear.Badge,
tblPASEmployees.Name AS Employee,
tblPASEmployees.Dept,
uv_ER_PAS_BadgeYear.[Year]
FROM uv_ER_PAS_BadgeYear
INNER JOIN DTree ON uv_ER_PAS_BadgeYear.DataID = DTree.DataID
INNER JOIN DVersData ON DTree.DataID = DVersData.DocID
LEFT OUTER JOIN tblPASEmployees ON uv_ER_PAS_BadgeYear.Badge = tblPASEmployees.Badge
AND uv_ER_PAS_BadgeYear.[Year] = tblPASEmployees.[Year]
WHERE uv_ER_PAS_BadgeYear.[Year] = '2004'
ORDER BY uv_ER_PAS_BadgeYear.Badge, uv_ER_PAS_BadgeYear.[Year]

Appu_Nair

Login to sqlstudio and find the tables owner and schema and see if fully qualifying it helpsFor eg my dversdata is called thus[LES971].[livelink].dversdata when executing thru livereport it creates the right connection so you don't need the FQ schema qullaifiers.That could be worth a try

CBancroft

Message from Chris Bancroft <cbancrof@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Chad,

from all the invalid object name messages it could be an ownership issue. you mentioned earlier that it worked if you prefixed the table names.

generally if you have to prefix a database table with a userid/ownerid it means that the user accessing the table is not the true owner.

if the tables and such were imported from mainframe your dba may want to check the ownership and make sure it is the same as the user that runs livelink

Chris

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:23 PM
To: eLink Recipient
Subject: Bhupinder,

Bhupinder, Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21
In reply to: RE 9.5 Upgrade & Live Report With Stored Procedures Posted by bsingh (Singh, Bhupinder) on 2009/06/15 15:44

Bhupinder,
Yes my co-worker Melinda said with regards to this particular stored procedure that when the tables/views wer built - they were imported from mainframe tables and that some of the records have blank spots. So there are rows that may not contain some data for the columns.

I tried from an SQL Query Analyzer window:

exec sp_LLReport_EmpRec_PAS_Search '','','', '2004'

but get:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'uv_ER_PAS_BadgeYear'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DTree'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DVersData'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblPASEmployees'.
SELECT DTree.*,
DVersData.MimeType,
DVersData.Version,
uv_ER_PAS_BadgeYear.Badge,
tblPASEmployees.Name AS Employee,
tblPASEmployees.Dept,
uv_ER_PAS_BadgeYear.[Year]
FROM uv_ER_PAS_BadgeYear
INNER JOIN DTree ON uv_ER_PAS_BadgeYear.DataID = DTree.DataID
INNER JOIN DVersData ON DTree.DataID = DVersData.DocID
LEFT OUTER JOIN tblPASEmployees ON uv_ER_PAS_BadgeYear.Badge = tblPASEmployees.Badge
AND uv_ER_PAS_BadgeYear.[Year] = tblPASEmployees.[Year]
WHERE uv_ER_PAS_BadgeYear.[Year] = '2004'
ORDER BY uv_ER_PAS_BadgeYear.Badge, uv_ER_PAS_BadgeYear.[Year]

Chad Latka

Message from Latka, Chad J (IT Solutions) <Chad.Latka@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Yes, all the tables, views etc. are owned by a SQL database user "sssxxx"

This user has the same SID number in our production and dev instances so its not an orphaned user issue

unless its at another level. Haven't encountered this before.

If I go into the stored procedure and put the qualifier in front of every view and tablename

meaning [sssxxx.tablename] or [sssxxx.viewname] and for all the remainder SQL items

and then in SQL Query Analyzer do:

exec sssxxx.sp_LLReport_EmpRec_PAS_Search '','','','2004' this will run.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 3:38 PM
To: eLink Recipient
Subject: Login to sqlstudio and find the tables owner and schema and see if fully...

Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by anair@alitek.com (Nair, Appu) on 2009/06/15 16:33
In reply to: Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21

Login to sqlstudio and find the tables owner and schema and see if fully qualifying it helps

For eg my dversdata is called thus

[LES971].[livelink].dversdata when executing thru livereport it creates the right connection so you don't need the FQ schema qullaifiers.

That could be worth a try

Jim_Coursey

Message from Coursey, Jim (AS) <jim.coursey@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Chad, was there also a change in which(which version) DB associated with the upgrade?

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:49 PM
To: eLink Recipient
Subject: RE Login to sqlstudio and find the tables owner and schema and see if fully...

RE Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:48
In reply to: Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by anair@alitek.com (Nair, Appu) on 2009/06/15 16:33

Message from Latka, Chad J (IT Solutions) <Chad.Latka@ngc.com> via eLink

Yes, all the tables, views etc. are owned by a SQL database user "sssxxx" This user has the same SID number in our production and dev instances so its not an orphaned user issue unless its at another level. Haven't encountered this before. If I go into the stored procedure and put the qualifier in front of every view and tablename meaning [sssxxx.tablename] or [sssxxx.viewname] and for all the remainder SQL items and then in SQL Query Analyzer do: exec sssxxx.sp_LLReport_EmpRec_PAS_Search '','','','2004' this will run. From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 3:38 PM
To: eLink Recipient
Subject: Login to sqlstudio and find the tables owner and schema and see if fully...

Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by anair@alitek.com (Nair, Appu) on 2009/06/15 16:33
In reply to: Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21

Login to sqlstudio and find the tables owner and schema and see if fully qualifying it helps

For eg my dversdata is called thus

[LES971].[livelink].dversdata when executing thru livereport it creates the right connection so you don't need the FQ schema qullaifiers.

That could be worth a try

Chad Latka

Message from Latka, Chad J (IT Solutions) <Chad.Latka@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

We have a simple customview.html file that has inputs that pass the values to the LiveReport which calls

the stored procedure. All the other LiveReports listed work fine. Its only this one with the stored procedure

that is giving us the trouble that we've found so far.

<table summary="" cellspacing="5">
<tr valign="top">
   <td>
   <FORM NAME="Search Documents" ACTION="/livelink XXXXXX /livelink.exe">
   <INPUT TYPE="hidden" NAME="func" VALUE="ll">
   <INPUT TYPE="hidden" NAME="objId" VALUE="1594815">
   <INPUT TYPE="hidden" NAME="objAction" VALUE="runReport">
   Badge Number
   </td>
   <td>
   <input type="text" Name="InputLabel1" SIZE= "5" MAXLENGTH= "5">
   </td>
<tr>
   <td>
   Employee Last Name
   </td>
   <td>
   <input type="text" Name="InputLabel2">
   </td>
<tr>
   <td>
   Department Number
   </td>
   <td>
   <input type="text" Name="InputLabel3" SIZE= "2" MAXLENGTH= "2">
   </td>
<tr>
   <td>
   Year
   </td>
   <td>
   <SELECT Name="InputLabel4">
   <OPTION VALUE="" SELECTED></OPTION>
   <OPTION VALUE="2004">2004</OPTION>
   <OPTION VALUE="2005">2005</OPTION>
   <OPTION VALUE="2006">2006</OPTION>
   <OPTION VALUE="2007">2007</OPTION>
   <OPTION VALUE="2008">2008</OPTION>
   <OPTION VALUE="2009">2009</OPTION>
   <OPTION VALUE="2010">2010</OPTION>
   </SELECT>
   </td>
<tr>
   <td>
   <INPUT type="submit" value="Find">
   </td>
   <td>
   <INPUT type="reset" value="Reset">
   </td>
   </FORM>
   </td>
</tr>
</table>

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 3:33 PM
To: eLink Recipient
Subject: RE Bhupinder,

RE Bhupinder,		Posted by bsingh (Singh, Bhupinder) on 2009/06/15 16:32
In reply to: Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

You had indicated "The LiveReport has inputs from a customview.html file that passes the values to the stored procedure". Are any of the inputs blank when the stored procedure is called? If so, arrange a test where none of the inputs are blank to see if the problem goes away. I don't think having blank spots in the records in the tables would be problematic, but having blank inputs could be. - Bhupinder
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:23 PM
To: eLink Recipient
Subject: Bhupinder,

Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21
In reply to: RE 9.5 Upgrade & Live Report With Stored Procedures		Posted by bsingh (Singh, Bhupinder) on 2009/06/15 15:44

Bhupinder,
Yes my co-worker Melinda said with regards to this particular stored procedure that when the tables/views wer built - they were imported from mainframe tables and that some of the records have blank spots. So there are rows that may not contain some data for the columns.

I tried from an SQL Query Analyzer window:

exec sp_LLReport_EmpRec_PAS_Search '','','', '2004'

but get:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'uv_ER_PAS_BadgeYear'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DTree'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DVersData'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblPASEmployees'.
SELECT DTree.*,
DVersData.MimeType,
DVersData.Version,
uv_ER_PAS_BadgeYear.Badge,
tblPASEmployees.Name AS Employee,
tblPASEmployees.Dept,
uv_ER_PAS_BadgeYear.[Year]
FROM uv_ER_PAS_BadgeYear
INNER JOIN DTree ON uv_ER_PAS_BadgeYear.DataID = DTree.DataID
INNER JOIN DVersData ON DTree.DataID = DVersData.DocID
LEFT OUTER JOIN tblPASEmployees ON uv_ER_PAS_BadgeYear.Badge = tblPASEmployees.Badge
AND uv_ER_PAS_BadgeYear.[Year] = tblPASEmployees.[Year]
WHERE uv_ER_PAS_BadgeYear.[Year] = '2004'
ORDER BY uv_ER_PAS_BadgeYear.Badge, uv_ER_PAS_BadgeYear.[Year]

Chad Latka

Message from Latka, Chad J (IT Solutions) <Chad.Latka@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Jim if I understand correctly... nope this particular instance is still connected to SQL 2000 SP4

When I upgraded, I did the parallel upgrade method where I took a LL921 database and connected it up to a LL951 instance,

performed the dbupgrade where it made the schema changes etc.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:00 PM
To: eLink Recipient
Subject: RE RE Login to sqlstudio and find the tables owner and schema and see if fully...

RE RE Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by grmmun02user8 (Coursey, Jim) on 2009/06/15 16:59
In reply to: RE Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:48

Message from Coursey, Jim (AS) <jim.coursey@ngc.com> via eLink

Chad, was there also a change in which(which version) DB associated with the upgrade?
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:49 PM
To: eLink Recipient
Subject: RE Login to sqlstudio and find the tables owner and schema and see if fully...

RE Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:48
In reply to: Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by anair@alitek.com (Nair, Appu) on 2009/06/15 16:33

Login to sqlstudio and find the tables owner and schema and see if fully...		Posted by anair@alitek.com (Nair, Appu) on 2009/06/15 16:33
In reply to: Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21

Login to sqlstudio and find the tables owner and schema and see if fully qualifying it helps

For eg my dversdata is called thus

[LES971].[livelink].dversdata when executing thru livereport it creates the right connection so you don't need the FQ schema qullaifiers.

That could be worth a try

Chad Latka

Message from Latka, Chad J (IT Solutions) <Chad.Latka@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Chris,

Yes, I even made a new database and restored this one in question over top, with same username that owns the tables etc.

changed ownership to ALL sysobjects to dbo. and then all the views, tables, etc. were owned by

dbo. (system user not actual account) - I still had to then put dbo. in front of everything to qualify in order to run in

SQL Query Analyzer

I had even connected up to query analzyer as " sa " to try and that didn't do the trick either.

This Livelink instance is connected to the database as user [ sssxxx ]

In this particular database, user [sssxxx] has "dbo_owner" - for the Permit In Database Role.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 3:49 PM
To: eLink Recipient
Subject: RE Bhupinder, 2

RE Bhupinder, 2		Posted by cbancrof (Bancroft, Chris G) on 2009/06/15 16:48
In reply to: Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21

Message from Chris Bancroft <cbancrof@opentext.com> via eLink

Chad, from all the invalid object name messages it could be an ownership issue. you mentioned earlier that it worked if you prefixed the table names. generally if you have to prefix a database table with a userid/ownerid it means that the user accessing the table is not the true owner. if the tables and such were imported from mainframe your dba may want to check the ownership and make sure it is the same as the user that runs livelink Chris
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 15, 2009 4:23 PM
To: eLink Recipient
Subject: Bhupinder,

Bhupinder,		Posted by northo01user2 (Latka, Chad) on 2009/06/15 16:21
In reply to: RE 9.5 Upgrade & Live Report With Stored Procedures		Posted by bsingh (Singh, Bhupinder) on 2009/06/15 15:44

Bhupinder,
Yes my co-worker Melinda said with regards to this particular stored procedure that when the tables/views wer built - they were imported from mainframe tables and that some of the records have blank spots. So there are rows that may not contain some data for the columns.

I tried from an SQL Query Analyzer window:

exec sp_LLReport_EmpRec_PAS_Search '','','', '2004'

but get:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'uv_ER_PAS_BadgeYear'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DTree'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DVersData'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblPASEmployees'.
SELECT DTree.*,
DVersData.MimeType,
DVersData.Version,
uv_ER_PAS_BadgeYear.Badge,
tblPASEmployees.Name AS Employee,
tblPASEmployees.Dept,
uv_ER_PAS_BadgeYear.[Year]
FROM uv_ER_PAS_BadgeYear
INNER JOIN DTree ON uv_ER_PAS_BadgeYear.DataID = DTree.DataID
INNER JOIN DVersData ON DTree.DataID = DVersData.DocID
LEFT OUTER JOIN tblPASEmployees ON uv_ER_PAS_BadgeYear.Badge = tblPASEmployees.Badge
AND uv_ER_PAS_BadgeYear.[Year] = tblPASEmployees.[Year]
WHERE uv_ER_PAS_BadgeYear.[Year] = '2004'
ORDER BY uv_ER_PAS_BadgeYear.Badge, uv_ER_PAS_BadgeYear.[Year]