Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     MS SQL Server Adapter - Default Database - What's the point?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
MS SQL Server Adapter - Default Database - What's the point?
 Login/Join
 
Francis Mariani
Expert
posted
I'd like to know what the Default Database in a MS SQL Server Adapter Configuration is useful for.

I decided to add a Default Database to an existing adapter.

That caused one of my SQL passthru fexes to fail - even though I qualified the columns in the SQL with database.owner.table.column

The code:
SET SQLENGINE = SQLMSS
SQL SET SERVER MSCRM
SQL
SELECT
GF_MSCRM.dbo.Contact.OwnerIdName,
GF_MSCRM.dbo.Contact.CreatedOn,
GF_MSCRM.dbo.Contact.CreatedByName,
GF_MSCRM.dbo.Contact.ModifiedOn,
GF_MSCRM.dbo.Contact.ModifiedByName,
GF_MSCRM.dbo.Contact.FirstName,
GF_MSCRM.dbo.Contact.LastName,
GF_MSCRM.dbo.Contact.DeletionStateCode,
GF_MSCRM.dbo.TeamBase.Name AS TeamBaseName,
GF_MSCRM.dbo.BusinessUnitBase.Name AS OwningBusinessUnitName
FROM
Contact
LEFT OUTER JOIN GF_MSCRM.dbo.BusinessUnitBase
ON GF_MSCRM.dbo.Contact.OwningBusinessUnit = GF_MSCRM.dbo.BusinessUnitBase.BusinessUnitId
LEFT OUTER JOIN GF_MSCRM.dbo.PrincipalObjectAccess
INNER JOIN GF_MSCRM.dbo.TeamBase
ON GF_MSCRM.dbo.PrincipalObjectAccess.PrincipalId = GF_MSCRM.dbo.TeamBase.TeamId
ON GGOF_MSCRM.dbo.Contact.ContactId = GF_MSCRM.dbo.PrincipalObjectAccess.ObjectId
WHERE
(GF_MSCRM.dbo.PrincipalObjectAccess.AccessRightsMask IS NULL) AND
(GF_MSCRM.dbo.Contact.DeletionStateCode = 0)
;
TABLE
ON TABLE HOLD AS H001 FORMAT ALPHA
END


The error:

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02
: Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name 'C
: ontact'. [42000] Statement(s) could not be prepared. [] Deferred prepare
:  could not be completed.
L    (FOC1405) SQL PREPARE ERROR.

Remove the Default Database and the fex works.

What gives?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Kamesh
Master
posted Hide Post
If you are not specifying the datasouce, by default you are hitting that datasource. If you want to point other datasource try this,

SET SQLENGINE=SQLMSS
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES BKREPOS server/user,pwd;datasource

SQL SQLMSS
your query

Hope this helps,


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
I thought I was specifying the datasource:

SET SQLENGINE = SQLMSS
SQL SET SERVER MSCRM
SQL
SELECT
GF_MSCRM.dbo.Contact.OwnerIdName,

GF_MSCRM is the datasource.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
Francis,

You are not specifying the origin of the Contact Table, try -

.....
FROM
GF_MSCRM.dbo.Contact
LEFT OUTER JOIN GF_MSCRM.dbo.BusinessUnitBase
ON GF_MSCRM.dbo.Contact.OwningBusinessUnit = GF_MSCRM.dbo.BusinessUnitBase.BusinessUnitId
LEFT OUTER JOIN GF_MSCRM.dbo.PrincipalObjectAccess
.....


T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Lenny Ward
Platinum Member
posted Hide Post
We have always had to issure a SQL COMMIT before changing connections.

This is the code we use:

-* COMMIT REQUIRED BEFORE CONNECTING TO NEW DATA SOURCE
SQL SQLMSS COMMIT

-* CONNECT TO NEWYORK DEV MACHINE
ENGINE SQLMSS SET DEFAULT_CONNECTION DBIBSDEV

-* AFTER CHANGING CONNECTION YOU MUST REISSURE SET COMMANDS

ENGINE SQLMSS SET IMPLICIT_TRANSACTIONS OFF
ENGINE SQLMSS SET ANSI_WARNINGS OFF
ENGINE SQLMSS SET ARITHABORT OFF
ENGINE SQLMSS SET ERRORTYPE DBMS
ENGINE SQLMSS SET DATETIME OFF
ENGINE SQLMSS SET NOCOUNT ON



SQL SQLMSS EX p_get_rpt_company_desc &XEDATE, &XACCT ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END


(Production: WebFOCUS 7.1.3 on Win 2K/IIS 6/CGI)
(Test: WebFOCUS 7.1.3 on Win 2K/IIS 6/CGI)
 
Posts: 104 | Location: Boston | Registered: April 23, 2003Report This Post
Francis Mariani
Expert
posted Hide Post
Oops!

Contact

should read GGOF_MSCRM.dbo.Contact

Thanks, Tony A.

Lenny, I am not switching connections, therefore, I don't think I have to issue the ENGINE commands.

Cheers.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     MS SQL Server Adapter - Default Database - What's the point?

Copyright © 1996-2020 Information Builders