11-23-2012 06:39 AM
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 ?
11-26-2012 11:54 AM
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.
11-26-2012 07:15 AM
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 view
this is the errors when I get when I select or edit the fields
11-26-2012 07:28 AM
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.
11-26-2012 07:43 AM
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 )
11-26-2012 07:52 AM
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.
11-26-2012 08:31 AM
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.
11-26-2012 08:40 AM
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.
11-26-2012 08:58 AM
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?
11-26-2012 11:37 AM
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.