fiogf49gjkf0d I'll look around for an example
I found my example! Its not a batch file but a .CMD file that was created. My example is as following
FILE: createview.cmd
osql -dslxremote -Usa -P -icreateview.sql
pause
FILE: createview.sql
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW sysdba.c_OppAccountDet
AS
SELECT TOP 100 PERCENT USERINFO.REGION, seccode.SECCODEDESC, USERINFO.USERID, USERINFO.USERNAME, seccodejoins.PROFILEID, ACCOUNT.ACCOUNT, OPPORTUNITY.DESCRIPTION,
OPPORTUNITY.ESTIMATEDCLOSE, OPPORTUNITY.ACTUALCLOSE, OPPORTUNITY.CLOSEPROBABILITY, OPPORTUNITY.STATUS, PRODUCT.FAMILY,
PRODUCT.NAME, CAST(OPPORTUNITY_PRODUCT.EXTENDEDPRICE AS int) AS ExtendedPrice, CAST(C_SIN_OPPPRODUCT.PROFIT_AMOUNT AS int)
AS Profit_Amount, CAST(C_SIN_OPPPRODUCT.PROFIT_MARGIN AS decimal(15, 2)) AS Profit_Margin
FROM sysdba.OPPORTUNITY OPPORTUNITY LEFT OUTER JOIN
sysdba.OPPORTUNITY_PRODUCT OPPORTUNITY_PRODUCT ON
OPPORTUNITY.OPPORTUNITYID = OPPORTUNITY_PRODUCT.OPPORTUNITYID INNER JOIN
sysdba.PRODUCT PRODUCT ON OPPORTUNITY_PRODUCT.PRODUCTID = PRODUCT.PRODUCTID LEFT OUTER JOIN
sysdba.C_SIN_OPPPRODUCT C_SIN_OPPPRODUCT ON
OPPORTUNITY_PRODUCT.OPPPRODUCTID = C_SIN_OPPPRODUCT.OPPPRODUCTID INNER JOIN
sysdba.ACCOUNT ACCOUNT ON OPPORTUNITY.ACCOUNTID = ACCOUNT.ACCOUNTID LEFT OUTER JOIN
sysdba.USERINFO USERINFO ON OPPORTUNITY.ACCOUNTMANAGERID = USERINFO.USERID INNER JOIN
sysdba.USERSECURITY USERSECURITY ON USERINFO.USERID = USERSECURITY.USERID INNER JOIN
sysdba.SECCODEJOINS seccodejoins ON USERSECURITY.DEFAULTSECCODEID = seccodejoins.CHILDSECCODEID INNER JOIN
sysdba.SECCODE seccode ON seccodejoins.PARENTSECCODEID = seccode.SECCODEID
WHERE (seccodejoins.CHILDSECCODEID =
(SELECT _seccodejoins.childseccodeid
FROM sysdba.usersecurity _usersecurity INNER JOIN
sysdba.seccodejoins _seccodejoins ON _usersecurity.defaultseccodeid = _seccodejoins.childseccodeid
WHERE _seccodejoins.childseccodeid = seccodejoins.childseccodeid
GROUP BY _seccodejoins.childseccodeid)) AND (SUBSTRING(seccode.SECCODEDESC, 5, 1) = '-')
ORDER BY
opportunity."estimatedclose" ASC,
USERINFO."REGION" ASC,
SECCODE."SECCODEDESC" ASC,
USERINFO."USERNAME" ASC,
ACCOUNT."ACCOUNT" ASC,
PRODUCT."FAMILY" ASC,
PRODUCT."NAME" ASC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
QUIT
You can create multiple views and/or stored procedure via the createview.sql file. Just add the appropriate create view/procedure and your set.
As for distribution.. you are depending on the user being able to get to the files and then saving them locally. This is easy if the user is in the office at times, just send them to a shared network resource. But for the truely remote users you'll have to get creative.
This is making me wonder if the Administrator SQL could just be used. That is, plug the SQL create view statement into the Administrator SQL, then when you execute it on the database, send it to all remote databases. Sounds tidier, but the technique I showed above was devised as a result of a phone call to support on this very question. I think there was a reason why it wouldn't work via the Admin, but I can't recall what that reason was.
John G.
|