Hi,
Can we use SQL Linked Server (Database Engine) option to create Dynamic fields from tables where the database is hosted on different server?
Aman,
This is a reasonable/simple to do. Here is how I set mine up.
(Disclaimer - All my screenshots will show the names of my servers, dbs, and tables. You will need to insert your own.)
DB Server A (LF DB Server)
DB Server B (Data needed for external table)
First thing I did was setup a link between my SQL servers. I made a link from A to B here is a link with directions on doing this.
https://msdn.microsoft.com/en-us/library/ff772782.aspx
Right mouse click on Linked Servers and add new.
Enter the name and select the radio button for SQL Server
Then go to the security tab and select the last radio button and enter your log in info. Hit OK.
Once that is complete you can test it with a query like this...
This is a query that I used to get the distinct customer names from an accounting table.
The second thing you need to do is either create a destination DB and table on DB Server A where the data you are about to mine can be stored. All I did was make a empty DB and empty table.
The last thing you need to do is finish your SQL statement and test.
I did it by deleting what was already in the destination table and then inserting my select statement there. After that you can test if it worked with a simple select statement.
The third and final thing to do which is optional is make a job out of it. I need mine to update nightly so I created a SQL Job to run it each night.
Right mouse click on jobs and select new.
In the general area give it a name and description.
Add a step and name it and paste your SQL statement in the command box.
Select schedules and set a schedule suited to your needs.
If you want to test your job just run this against your destination DB.
Then right mouse click on your job and start it. Once complete run this to make sure your data was inserted into your destination table.
After that you just need to set it up in Laserfiche admin console which I am sure you know how to do but here are a few screenshots of that also.
Just remember your Alias can't have spaces and can't be over 63 characters.
There you have it all done.
I hope this helped you if you have any questions please let me know.
John Evans
The external table must be located on the same server as your LF repository. Here is the link to the help file on this. http://www.laserfiche.com/support/webhelp/Laserfiche/9.1/en-US/AdminGuide/LFAdmin.htm#cshid=Manage_External_Table.htm
What you might try is creating a view table in the Laserfiche SQL database that is linked to the database that you are needing the Dynamic fields for
If utilizing a view of the linked server tables does not work, another option that I have used would be to create a copy of the external table on your LF server SQL instance with a scheduled job that updates the table each hour or day.
Should a view of the linked server tables work? We have tried to add an external table in Laserfiche Administration Console to a linked server view and get the error message: Unable to locate the specified external database table. [9227]. Using Laserfiche version 9.1.
I have not been able to access a view of the linked server, however, I have been able to create a SQL view of the linked server view.
We have created a linked server on the LF SQL server to the PD server. Then we have a view that was created using a select statement to a view on the linked server. But still when we try to register it in Laserfiche Admin Console it gives an error. What version are you on? We are on 9.1.
I just wanted to attach a note of caution to people considering using the linked server as outlined in the above process. It is a really bad idea from a security perspective to use the "sa" account for the creation of a linked server. Also you should never use the "Be made using this security context:" option. Here is a good article that discusses the security concerns and outlines strategies to create a more secure linked server https://www.brentozar.com/blitz/linked-servers/.