Unidentified SQL failure in TI

[bunchukokoy]

Guys,

Here I am again needing help.

I have this query below. I made sure that it returns data well in SQL Management analyzer, in fact I'm not the one who created it but it's been used ever since and it's definitely working. I also have the same rights as the one who created it has. All in all, this query is working when I execute in SQL analyzer. But, when in TI, it just prompts SQL failure. How will I know what causes the error?, when I know TI doesn't log exactly what the error is.
Here's the query.

TRUNCATE TABLE [SAMP-TABLE].[dbo].[Company$Sales Accountability]

DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
DECLARE @FilterDate DateTime

SET @FromDate = '12/1/10'
SET @ToDate = '12/1/10'

SET @FilterDate = @FromDate - 1

WHILE @FilterDate < @ToDate
BEGIN

SET @FilterDate = @FilterDate + 1

INSERT INTO [SAMP-TABLE].[dbo].[Company$Sales Accountability]
([Store No_]
,[Store Name]
,[Date]
,[Staff ID]
,[Staff Name]
,[Tender Type]
,[Trans_ Amount]
,[Counted Amount]
,[Difference Amount])

SELECT TPE.[Store No_]
,''
,TPE.[Date]
,TPE.[Staff ID]
,''
,TPE.[Tender Type]
,SUM(TPE.[Amount Tendered]) [Trans Amount]
,0
,0

FROM [SAMP-TABLE].[dbo].[Company$Trans_ Payment Entry] TPE

WHERE TPE.[Date] = @FilterDate AND
TPE.[Store No_] <> '000100'

GROUP BY TPE.[Store No_], TPE.[Date], TPE.[Staff ID], TPE.[Tender Type]
ORDER BY TPE.[Date] ASC, TPE.[Store No_] ASC

END

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]

SET [Counted Amount] = STL.[Counted Amount],
[Difference Amount] = SAC.[Trans_ Amount] - STL.[Counted Amount]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC

JOIN [SAMP-TABLE].[dbo].[Company$Statement Line] STL
ON STL.[Store No_] = SAC.[Store No_] AND
STL.[Staff ID] = SAC.[Staff ID] AND
STL.[Tender Type] = SAC.[Tender Type]

JOIN [SAMP-TABLE].[dbo].[Company$Statement] STM
ON STM.[No_] = STL.[Statement No_] AND
STM.[Store No_] = SAC.[Store No_] AND
STM.[Posting Date] = SAC.[Date]

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Difference Amount] = SAC.[Trans_ Amount] - SAC.[Counted Amount]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Store Name] = SRE.[Name]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC
JOIN [SAMP-TABLE].[dbo].[Company$Store] SRE
ON SRE.[No_] = SAC.[Store No_]

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Staff Name] = STF.[First Name] + ' ' + STF.[Last Name]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC
JOIN [SAMP-TABLE].[dbo].[Company$Staff] STF
ON STF.[ID] = SAC.[Staff ID]

SELECT [Date]
,[Store No_]
,[Store Name]
,[Staff ID]
,[Staff Name]
,[Tender Type]
,[Trans_ Amount]
,[Counted Amount]
,[Difference Amount]
FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability]

ORDER BY [Date], [Store No_], [Staff ID], [Tender Type]
GO

Sorry guys, I just need your help. Thank you.

Bunch

[Gregor Koch]

Hi
Maybe this
http://www.tm1forum.com/viewtopic.php?f=3&t=1273
or that
http://www.tm1forum.com/viewtopic.php?p=5400
might give some hints.

I think you are better off having this query as a stored procedure in your source system and calling that from TI.

A good way of testing whether a query will work through TI is to use MS Query through Excel (Data - Import External Data - Import Data..., this is also mentioned in on of the linked threads) on the TM1 server while being logged on with the TM1 service account. Obviously you can test it locally as well if you have the ODBC connection set up and you are sure that TM1 (not you personally) has the same access rights.

[csjean]

Hi,

It's pretty late so maybe someone else will have a better / more complete response but,
without more info on where you use your Query (data tab or a ODBCOutput command):

Here is my checklist:

• INSERT and UPDATE are not allowed in the data tab.
• although ANYTHING is allowed it an ODBCOutput function, you have to split your text in 255 char segments and put them one after the other (eg:

  Code: Select all

  ODBCOutput(Source, SQLQuerySeg1, SQLQuerySeg2, SQLQuerySeg3, ...);

  .)
• finaly if you have done all that, I would remove the empty lines, sometimes the ODBC doesn't like empty lines in statement.

Hope this helps.

[bunchukokoy]

Hi csjean,

What do you mean INSERT and UPDATE are not allowed in data tab when as I understand Sir Alan's post in viewtopic.php?p=5400, TI doesn't care about any syntax, because they're just treated as text. Am I missing part of the technicality?

Thanks.

Bunch

[Gregor Koch]

I guess Alan's post is related to query you use in the the Datasource Tab ("Query:" Box), which I think is what you are doing.

[csjean]

Hi,

I should have made myself clearer.

Actually I beleive that Sir Allan's post was refering to the SELECT statement only in the "Query" Box.

As far as I know, you can't script in that box (do other things than that one SELECT statement because INNER JOIN, LEFT OUTER JOIN, UNION, etc are part of a SELECT STATEMENT).

I just tried to insert in a test table and select my answer in the Query tab and it doesn't work.

The best advise I can give you is to follow is

I think you are better off having this query as a stored procedure in your source system and calling that from TI.

Well there you go.

Hope this helps.

[rfielden]

I agree with the procedure call.

Here is a sample of one I use which works well in Oracle.

ODBCOpen('Server Name','User','Password');
ODBCOutput('Server Name','{call procedure_name}');
ODBCClose('Server Name');

[vinnusea]

Yes,
Even me using Insert Statement in Data tab from an year its working good

In Data tab:
sDSNODBC= myODBCName ;
ODBCOpen( sDSNODBC , sUser, sPswd );

# Insert into table1
SqlExecString = ' INSERT INTO tablename; ';
ODBCOutput( sDSNODBC, SqlExecString );

# Closing ODBC
ODBCClose( sDSNODBC);


Thanks