Community
Showing results for 
Search instead for 
Do you mean 
Reply

ACT Database Access

Accepted Solution Solved
Copper Contributor
Posts: 61
Country: United_Kingdom
Accepted Solution

ACT Database Access

Hi guys,

 

I'm currently developing a customised web solution for my company and I'm accessing ACT Database from SQL Server Managment Studio (SQL Server Version 2008 R2) and I need to select a view on my database but I'm keep getting errors like "Conversion failed when converting from a character string to uniqueidentifier." or "Operand type clash: uniqueidentifier is incompatible with decimal" errors. Do you have any ideas where these errors cauising from ?

 

Thanks


Accepted Solutions
Solution
Accepted by topic author hysezer
‎09-25-2015 03:20 AM
Employee
Posts: 1,163
Country: USA

Re: ACT Database Access

You can also change this in the initial connection window for SSMS. From the query window, right click -> Connection -> Change Connection. Thjis will open the Connect to Database window, choose Options then go to the Additional Connection Parameters window.

 

From that window add "Application Name= <put your USERID GUID here>" and this will impersonate an act user and allow you to perform the query. As Stan has mentioned the problem is that the query you are trying to run has some security requirements and as a result needs a qualified Act user to execute.

 

 

Matthew Wood
Act! SDK Support
Community Moderator

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: ACT Database Access

Does this only occur for specific tables/columns? I've gotten this error in the past but assumed the issue was with my query. 

 

 

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 61
Country: United_Kingdom

Re: ACT Database Access

Hi Matthew,

 

Yes it's on a specific table actually on a view. The view works perfectly when it's used by ACT itself it's the opportunity view but when I try to access it (Select top 1000 rows or try to edit them) from Managament Studio it shows this error. The view consists of 4 functions also created by ACT and I couldn't figure it out why these errors coming up.

 

this is the viewUntitled.jpg

 

this is the errors when I get when I select or edit the fields

 

Untitled2.jpg

 

Untitled3.jpg

Employee
Posts: 1,163
Country: USA

Re: ACT Database Access

The views are used by the OLEDB reporting provider, you should be able to connect to it, execute the same query and it should work, however if your doing this from within SSMS, then run the query against the table rather than the view.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 61
Country: United_Kingdom

Re: ACT Database Access

Hi Matthew,

 

My main goal is to access this view from php so i need to send a sql query to database, do you mean I need to change my query or use something else to connect to it. ( btw i can select the table from php )

Silver Super Contributor
Posts: 2,328
Country: USA

Re: ACT Database Access

Some of the views require that when you connect to the ACT! SQL database that you pass the unique id of the requesting user as a parameter.  If you don't you will get that error message.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Copper Contributor
Posts: 61
Country: United_Kingdom

Re: ACT Database Access

I'm connecting with the user sa but do you think when I try to see the raw data in the rows I need to access it with a specific user ? I can access it with admin but I don't think things will change I need to access the data stored in this view.

Silver Super Contributor
Posts: 2,328
Country: USA

Re: ACT Database Access

Like Matthew said you can access the data in the tables directly without using the userid as a parameter but if you want to access the data in some of the views you must supply the userid.  That is how security is enforced in the database.  So a user that doesn't have access to certain records can't view them, etc.


Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Copper Contributor
Posts: 61
Country: United_Kingdom

Re: ACT Database Access

So which user will that be I mean I logged in with ACTADMIN and ACTUSER but I still have the same error and when I try to login with one of the ACT login it doesn't allows because it doesn't appears on SQL Server's Login list, do I need to add this ACT login user there to accesss these data or do I need to provide this user parameters in my query?

Silver Super Contributor
Posts: 2,328
Country: USA

Re: ACT Database Access

No.  In the connection string that you use to access the SQL database you need to pass it the unique id of the user that you want to access the view with.  I believe the parameter is: 'APP='put the guid here'  It's been a while since I did it.  I'm going to do a little testing and I'll post back if that isn't it.


Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!