Views on Remotes

If you have a database view on your SalesLogix Remote clients, which you’re using for running Crystal reports off of, and which pulls data from the UserSecurity table, you may have encountered this:

“Crystal Report Runtime Error: This field name is not known.”

The way around that is to create the view using the T-SQL setuser function, in Management Studio, on each Remote database (you cannot run this SQL in WG Admin through the SLX Provider):

setuser N'sysdba'
GO

CREATE VIEW sysdba.USERREQV
AS
SELECT sysdba.USERINFO.USERID, sysdba.USERINFO.USERNAME,
sysdba.USERSECURITY.ENABLED, sysdba.USERSECURITY.TYPE
FROM sysdba.USERINFO INNER JOIN
sysdba.USERSECURITY
ON sysdba.USERINFO.USERID = sysdba.USERSECURITY.USERID

GO
setuser
GO

Without the setuser function, the code will execute, and the view will populate as if nothing was wrong; but you won’t be able to see the fields in your Crystal report, in design mode. (Same thing if you try running it in WG Admin, without that function.)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: