TM1 Forum

Hi all,

I have an SQL query that works in sqlserver but which gives my the following error in TM1.

SQL Statement failed

It produces the above error as soon as I open the process.

Note that I am very new to TM1.

Broadly the query is
IF I remove the D.VAR4 from the above query there is no problem (ie if I open the same process delete D.VAR4 ,then save, all is well). The entire query works well in SQLSERVER.

Any ideas on what could be causing the problem?

thanks

TM1NEWUSER wrote: ↑Wed Jan 13, 2021 2:51 am Broadly the query is

Code: Select all

Select    A.VAR1,  B.VAR2,  C.VAR3, D.VAR4 
 from DB1 A, DB2 B, DB3 C, DB4 D
          Where A.VAR5 = B.VAR6 AND B.VAR7 = C.VAR7 AND B.VAR7 = D.VAR7

IF I remove the D.VAR4 from the above query there is no problem (ie if I open the same process delete D.VAR4 ,then save, all is well). The entire query works well in SQLSERVER.

From the Request for Assistance Guidelines:

... posting the actual code and the real names of and structures of your cubes, dimensions and elements will be a thousand times more useful than an attempted description of them. You don't need to post real data, but the real code is needed. Pseudo code is obviously not "the real code". When you post something like "Suppose I have cube A and Cube B, and my rule is ['value'] = N:DB('CubeA', 'dim1, dim2'" etc, then three things happen. One, you reduce the chance that a syntax or typing error will be spotted, which means wasting time bouncing posts back and forth to try to get to the root of the problem. Two, you are usually describing what you think is happening, which may not be what is happening. Three, some more experienced members won't even look at the question because they've had too much time wasted by the first two issues in the past.

I'm going to take a wild guess that "DB4" is not the real table or view name and that "Var4" is not the real column name. If that's the case, I do have to wonder if there are some characters in the real names that are causing a problem in executing the code when it is passed through to the SQL Server instance from the TI process.

That aside, another thing to look at is the authentication that you are using when you open the query in TM1 vs the authentication that you are using in (presumably) Management Studio, which is presumably where you are running it "in SQLSERVER". (That, or Azure Data Studio, though usage of that is still limited, I believe.) Specifically, I'd be looking at whether the TM1 login has read permissions to the view or table that you've referred to as DB4 D.

If that isn't the issue, I would suggest posting the actual SQL code.

Thank you for your help. You are right I am using Management Studio.

You are also probably right that it is probably an authentication issue - unfortunately there is limited knowledge about TM1 within our company.

I fear that copying the entire code may be in breach of my company's security protocols so I will have to check with the relevant people before I do that.

thanks again

TM1NEWUSER wrote: ↑Wed Jan 13, 2021 4:35 am You are also probably right that it is probably an authentication issue - unfortunately there is limited knowledge about TM1 within our company.

Just to be clearer about that... I'm talking about SQL Server authentication, not TM1 Authentication.

TM1 authentication regulates access to TM1 objects only. It doesn't have any impact on data sources.

I don't know whether you're using Windows Authentication or Mixed Mode authentication on SQL Server, but for something like this I expect it would be Mixed Mode; that is, where you specify a login and password for the connection. (It doesn't have to be, but I suspect that it's more likely than not.)

If you are looking at the query in Management Studio, you will probably be logged in under your own login rather than the one that has been assigned to the TM1 server.

YOUR login in SSMS may well have access to table or view DB4, but it doesn't automatically follow that the login that TM1 is using to connect to SQL Server will have the same permissions. This isn't a TM1 authentication issue; a TurboIntegrator process runs with full Admin rights on the TM1 side, but on the SQL Server side it will still only have as many rights as the SQL Server administrator has granted to the SQL Server login that it is using .

Regardless of the security mode on the SQL server side, I'd be taking a stroll over to the SQL Server admin and asking him or her "Does this SQL Server login that TM1 is using have read rights to table / view DB4?"

If the answer is "no", that's where your problem is.