RE: SQL Server Stored procedures and ODI

This is a multi-part message in MIME format.

------_=_NextPart_001_01C75B46.CA1832B6
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi there,
=20
Have you tried using a sql block in the 4gl code to shield the statement =
from the compiler? =20
=20
=20
=20
sql
       exec spname parameters
end sql
=20
=20
=20
rt
=20

________________________________

From: [email protected] on behalf of [email protected]
Sent: Tue 2/27/2007 12:34 PM
To: [email protected]
Subject: [fourjs-users] SQL Server Stored procedures and ODI



I need to call a SQL Server (SS) stored procedure from 4GL using ODI.
I am not returning anything, I am creating a table on the SQL server
side so that the user can use Crystal reports to report on the data and
also join with other tables on the SS side.

I run a program on the informix side using a beginning and ending date
as parameters.  This creates a text file unload.
The user will currently have to run the sp from inside of Crystal
Reports (CR) using that same date range (remembering to type it in
correctly).  I inserts the contents of the text file into a tempfile
and then does other magic on tables in the SS DB.  In this case, it
returns a record set to CR for analysis

I want to have the 4gl program call the sp.  I realize I can't easily
return data to the 4gl program.  I was planning just create a table on
the SS side that would get over written every time they push the button
in Informix.

Sound grand, doesn't it?  The problem is that once I tell the ODI that
I want to use the SS database, how do I cast the sp statement?  I tried
exec spname parameter, parameter and it won't compile.





------_=_NextPart_001_01C75B46.CA1832B6
Content-Type: application/ms-tnef;
	name="winmail.dat"
Content-Transfer-Encoding: base64

eJ8+IhYOAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEgAEAOAAAAFJFOiBbZm91cmpzLXVzZXJz
XSBTUUwgU2VydmVyIFN0b3JlZCBwcm9jZWR1cmVzIGFuZCBPREkAhBMBBYADAA4AAADXBwIAHAAJ
ACsAEwADAEYBASCAAwAOAAAA1wcCABwACQArABQAAwBHAQEJgAEAIQAAADE0MkM1NjE1MEZFNTFG
NDNBRURFQTU0NkMyMUQ2QTU2ADQHAQOQBgAYEgAAOAAAAAMANgAAAAAAQAA5ALYyGMpGW8cBHgA9
AAEAAAAFAAAAUkU6IAAAAAACAUcAAQAAADsAAABjPVVTO2E9IDtwPUZpcnN0IE9yZ2FuaXphdGk7
bD1TT0xTVElDRS0wNzAyMjgxNDQzMTlaLTEwMjEwAAAeAEkAAQAAADQAAABbZm91cmpzLXVzZXJz
XSBTUUwgU2VydmVyIFN0b3JlZCBwcm9jZWR1cmVzIGFuZCBPREkAQABOAIAAf4iVWscBHgBaAAEA
AAAbAAAAb3duZXItZm91cmpzLXVzZXJzQDRqcy5jb20AAAIBWwABAAAAUwAAAAAAAACBKx+kvqMQ
GZ1uAN0BD1QCAAAAAG93bmVyLWZvdXJqcy11c2Vyc0A0anMuY29tAFNNVFAAb3duZXItZm91cmpz
LXVzZXJzQDRqcy5jb20AAAIBXAABAAAAIAAAAFNNVFA6T1dORVItRk9VUkpTLVVTRVJTQDRKUy5D
T00AHgBdAAEAAAAVAAAAbWljcm9zb2xAaGV2YW5ldC5jb20AAAAAAgFeAAEAAABHAAAAAAAAAIEr
H6S+oxAZnW4A3QEPVAIAAAAAbWljcm9zb2xAaGV2YW5ldC5jb20AU01UUABtaWNyb3NvbEBoZXZh
bmV0LmNvbQAAAgFfAAEAAAAaAAAAU01UUDpNSUNST1NPTEBIRVZBTkVULkNPTQAAAB4AZgABAAAA
BQAAAFNNVFAAAAAAHgBnAAEAAAAbAAAAb3duZXItZm91cmpzLXVzZXJzQDRqcy5jb20AAB4AaAAB
AAAABQAAAFNNVFAAAAAAHgBpAAEAAAAVAAAAbWljcm9zb2xAaGV2YW5ldC5jb20AAAAAHgBwAAEA
AAA0AAAAW2ZvdXJqcy11c2Vyc10gU1FMIFNlcnZlciBTdG9yZWQgcHJvY2VkdXJlcyBhbmQgT0RJ
AAIBcQABAAAAGwAAAAHHW0M0rxo8FxUceUpWhBqvAP4l16MAAIwDDwAeAHQAAQAAABUAAABmb3Vy
anMtdXNlcnNANGpzLmNvbQAAAAAeABoMAQAAAA8AAABSaWNoYXJkIFR1cm5lcgAAHgAdDgEAAAA0
AAAAW2ZvdXJqcy11c2Vyc10gU1FMIFNlcnZlciBTdG9yZWQgcHJvY2VkdXJlcyBhbmQgT0RJAAIB
CRABAAAAhgoAAIIKAAB9KAAATFpGdUP/3TQDAAoAcmNwZzEyNYIyA0NodG1sMQMwPwEDAfcKgAKk
A+MCAGNowQrAc2V0MCAHEwKA/xADAFAEVghVB7IR1Q5RAwHdENcyBgAGwxHVMwRGENlZEu9mNBBv
EXs1A8ZUfGFoA3ECgBHjCO8J9zt7HB8OMDUdPx0REeEMYGNnAFALCQFkMzYRYAulNHIgEAIqXA6y
AZAOEDkAIDxIVE1MIGSIaXI9IrByPn0ic+cAIQMwI8FkbwDgI8EKsfxccRwAI8EY4AMwJCURYI0i
KzMiACMgRUFEI/AJIiw3NyMQVElUTG5FKB0h8A7wWwIQCHBqWHMtdRkgGRBdBgBRyyNgBmF2EzFT
dAWwCYD0IHADYGMJgAhwB5EAcJEswE9ESSgtODUjEO4vKV8iwgrjIC+vML8OEAI2DvA8TUVUQSDH
BaACMAnwdD0iBeAjMwA2LjAwLjM3OQk08DI4KQAiIG5hAQeAPUdFTkVSQfhUT1IoHSqhLyAn3ypx
SzHvIsE1EWA8Qi3QWccoHSFxOU9nOTYjEC3gIFYgaWQ9PaBPVwhBUmULUHlUZXhgdDgwMTEiACOP
IP8AACUlJcMlgSYfO288fz2Azz8fQC9BOiJJNjRFGkFvCSKkNDgjEEZPTlR8IGYA0DXwBxMzsRux
PVYjNOBMIiAAkHo18DL3RQsYMAMwYxPwA7IB0EFJIEhpIHRoBJBlLPUiLDU9MS9KsiP5JAdHW78B
wCQHCqJR+AqAIiwwJ6H+Lz1hUU9JT0K/Q89E30Xv/1IvSA9XT0ovSzZMn02kTl0vT99Q71cfIoU4
IgAmbphic3ACgCQYJ2EBQP9cv1MvVD9VT1ZfZl9Yf1mP/1qfW69pD13Pbi9f72D/Yg/lTnxhLDAg
eQhgT2AIgfcswCtQC4BnLYB4YEEhAmAdJPBrPZADoE9xIDRnV0uSAQBPYG94YGgIkGz7LMB9EnMB
kDPwB4ACMErwZwNhfQMFoG1wAxAEkD//Zr9nz2jaY99k71Hvae9q//9sD20fdc9vP3BPcV9yb3N/
/3SPdZ92r3e/eM9i74OPhJ//lu+A/4IPhf+HD4gfiS+ZL/+LT4xfjW+Of4+PkJ+Rr6IP/5PPlN+V
76lvmA+hH5ovmz//nE+dX55vn3+gj7CPoq+jv/+kz6Xfpu+n/6kPqh+rL6w//61Prl+vb7h/sY+y
n75PtL//tc+238bfx++6D7sfvC+9P//Kn79fz7/Bf8KPw59OinxB/8Vvxn+Fn8t/zI/Nn86v11//
0M/R39Lv0//VD9Yf1y/YP//ZT9pfxH/IX8lv7//xD/If//Mv9D/1T/Zf92/4f/mP+p/vgvY+gAWQ
7iBwNcIs0ArA/zXRM/AZENyP3Z/er9+/4M//4d/i7/uf5Q/mH+cv6D/pT//qX+tv7H/tj+6f760c
oC2w/9xfAU8CXwNvBH8FjwafB6//CL8JzwrfC+8M/w4PDx8QL/8RPxJPE1/FXxXvHB/8b/1//yHv
GF8Zbxp/Kn8rjx2vHr//H88g3y4/Iv8zXyUfJi8nP/8oTylfMm8/nyyPLZ8ury+//zDPMd9B3zP/
NQ82HzcvOD//OU86XztvPH89jz6fUh9Av/9Jz0LfQ+9Pn0YPRx9IL0k//1k/S19Mb01/To9b71Cv
YQ//Us9T31TvVfxdsFbfV+8XD/9c713/Xw9gH2jPYj9jT2Rf/2VvZn9nj2ifaa9qv2vPVe//bg9v
H4FvWi9bP3B/cY9yn/9zr4OvfW2K74v/dh93L3g/J3lPel59fDEykcBCUj9/+ZByj4CUb4//kQxI
UjIgmJBiSRSA/wA9LSoxf/wxenBjenBqXBB1bGRigOB+IF/HnRKc0ZPAaW5liQOXPx+YTntffG99
f36JVGFo2G9tYX+PgJI1gO+Ezk+WEH/7nMGmZ0ZypFA6G5lMlfEvqApwCSBvdwGdwHItZm91cmqQ
cy11cwAxQDStMAwuY6RQrIBuIGJlFmh/YICAb4CAbWljAamwc29sQGhldvphncB0rfKVDYLhliyd
oq+eP6dPqF+zElMUcHSp7xOq//53VHX/oDIvMkI3uUAwMDcgleA68jOFoFBNsF+xb7J/s4/ztJ+8
9FRvtk+3X/53rP8/sC+7P7xPvV++b7V4dWJeav8gtj/BL/5oW8LaXcm18FFMtfFydgAwtfDQdG9y
ZRSQcKmwfwBiZMLwZXMgr9AUkE//keDEH8Uvxj+CL4M/0p/Qr//Rv4dfiG+Jf41P1L+e35jvv5GH
f/vdf96PoGmRwFCWT3/hWOC/1b9+V6SfgKGmHEnOIJ3AzsHOkCBjf2B6kMOkcM3ZKFNTKeiwzp3j
ftCpsSA0R83wrWCdsPpnz9Iu1e/W/9I86uD/gLnq8G90bbAAEMLwbu6y0a/QeXRo7rEs8iSvMNhl
YXTzI5pybP+grkH/85D/oM3SrXHOQe8/8E/SPP/owJrQ6LDrYPOQ9ID1g61ih+txrlCtYSBDcnns
8Ot/Yc6wcM6gdM+A61H71P31VmT0gKRwz6H2f/eP0jwTf2D5wWpvnbAgd2l/85CsgPWR3yD1A8+A
9WZTf/lj7y/+79IvA78Ez/F8cs51rlCkcM7xZ3LyUfVl652wwtByrxB4+WTulKRw/a5wZ52w8xUU
kBRx7rL9Yf8FwQa/B88AK8+A4vHyUPLQ/8NhA4yFn4auuODzoM+A9FPPz3KacJrg8qBmafUxCcDw
bG9hZAOPDv8FrRTAd/plAYDroWPC8M6wtiBs/HkgrpDOQPxDCcH1khMA/+30nbD5c67h+1UWzxff
BbziUvvlKENS7NDulPnzXnMRUQ2jbbD64Gf5oCh7zrARYG2ucH9AIdL50Xn+cArh8qCdsB3fHu8F
vK4A9xrByhAbECkRzxLfE+zq4P8cwcNg/CL1oa4AtiC2EQKBP4ag9ZIVuJ2w61EVkm1wfxYCJP8m
DwANz7H1ka5QZN5vAnIB46RgDIBj9VMCRPMBUQLVREIonymvKr31cvsU4hDwZfPgAZAvDzAfBbz/
8sQVcighzqDrMK1w+iHrYP8hYMLB3yD64H9g+3AU4DkP3zofBb8+rz+/8Yt3+uA9M/MbRPWhNGd6
kAoW64McBP81DzYfKr70YZ2g6ODq0frR/ifyoPRw6MAbEEF/Qo87L//y8f1UJCJFfEcfSC8qvkSQ
7REBbPrg8xNqrWDyoBUUv/TZS59Mr0CcAtn55HfC4F2coCAjIPzRzkJ3f0B0/yyhDTDOQRsg9JAi
cfWRGyDOcK1gAbD1kmJ1WvBVr/9Wv0B+AVGasAskFr9eP0CP92EfYi9jOlPC4M+xCkGawO/z4DKS
SvIBkD9Qj1GfE/3/+aDO8fUhHKEsEvoRrkB/AP/q0Q3QRoXP4fnjY/9lD0Ofb0Sl+xIC1f1iYjiT
pEB3/zKBSqNUgRwF+4EugbYRaG/3aX8qvpJwac7Abi9vP2Mc/5rgygAcMT4AInERJ/PgESffz5Mk
oVnQSvLD4XAWEWDv/3lvYw9+73//gQ+CH4MvhD//05/cz+Xp2k7kj4dP2VTcQd/kUdtP3F+ND6CU
NaNRy1Adz+BZjt25UI/xSFRNCkyO0H2UwAAAHgA1EAEAAABFAAAAPDJBMTI2QUE4OUE1NjdCNEJB
Q0VBMDc1NjgwM0FFOTQyOTg0NkZCQFNPTFNUSUNFLmhhcnJpc2NvbXB1dGVyLmNvbT4AAAAAHgA5
EAEAAAAxAAAAPDIwMDcwMjI3MTczNC5sMVJIWVRzMjU4NDBAYnJvYWR3YXkuaGV2YW5ldC5jb20+
AAAAAB4ARxABAAAADwAAAG1lc3NhZ2UvcmZjODIyAAALAPIQAQAAAB8A8xABAAAAfAAAAFIARQAl
ADMAQQAgAFsAZgBvAHUAcgBqAHMALQB1AHMAZQByAHMAXQAgAFMAUQBMACAAUwBlAHIAdgBlAHIA
IABTAHQAbwByAGUAZAAgAHAAcgBvAGMAZQBkAHUAcgBlAHMAIABhAG4AZAAgAE8ARABJAC4ARQBN
AEwAAAALAPYQAAAAAEAABzC85LtkRVvHAUAACDBNFqDKRlvHAQMA3j+vbwAAAwDxPwkEAAAeAPg/
AQAAAA8AAABSaWNoYXJkIFR1cm5lcgAAAgH5PwEAAABpAAAAAAAAANynQMjAQhAatLkIACsv4YIB
AAAAAAAAAC9PPUZJUlNUIE9SR0FOSVpBVElPTi9PVT1GSVJTVCBBRE1JTklTVFJBVElWRSBHUk9V
UC9DTj1SRUNJUElFTlRTL0NOPVRVUk5FUlIAAAAAHgD6PwEAAAAVAAAAU3lzdGVtIEFkbWluaXN0
cmF0b3IAAAAAAgH7PwEAAAAeAAAAAAAAANynQMjAQhAatLkIACsv4YIBAAAAAAAAAC4AAAADAP0/
5AQAAAMAGUAAAAAAAwAaQAAAAAADAB1AAAAAAAMAHkAAAAAAHgAwQAEAAAAIAAAAVFVSTkVSUgAe
ADFAAQAAAAgAAABUVVJORVJSAB4AMkABAAAAGwAAAG93bmVyLWZvdXJqcy11c2Vyc0A0anMuY29t
AAAeADNAAQAAABUAAABtaWNyb3NvbEBoZXZhbmV0LmNvbQAAAAAeADhAAQAAAAgAAABUVVJORVJS
AB4AOUABAAAAAgAAAC4AAAADAHZA/////wsAKQAAAAAACwAjAAAAAAADAAYQRqEpjwMABxB1BAAA
AwAQEAAAAAADABEQAQAAAB4ACBABAAAAZQAAAEhJVEhFUkUsSEFWRVlPVVRSSUVEVVNJTkdBU1FM
QkxPQ0tJTlRIRTRHTENPREVUT1NISUVMRFRIRVNUQVRFTUVOVEZST01USEVDT01QSUxFUj9TUUxF
WEVDU1BOQU1FUEFSQU0AAAAAAgF/AAEAAABFAAAAPDJBMTI2QUE4OUE1NjdCNEJBQ0VBMDc1Njgw
M0FFOTQyOTg0NkZCQFNPTFNUSUNFLmhhcnJpc2NvbXB1dGVyLmNvbT4AAAAAIKQ=

------_=_NextPart_001_01C75B46.CA1832B6--