Can a Crystal Report in Crystal Enterprise use a data source that is a SQL view or SQL stored procedure that contains a select on a linked server? In other words, I have a SELECT statement that selects columns from three tables in a database on server A (same server that view or sproc reside) and also joins columns from two tables in a remote SQL Server. I have the remote server linked and am using a SQL account for the login. The account is on the databases for both servers. The syntax for selecting columns in the linked server is: SELECT * from remoteservername.remotedatabase.dbo.tablename . This works from my SQL Query Analyzer when I'm logged in as the SQL account, but I get an error in Crystal when I try to add either the stored procedure or the view to the report.
I can make a connection to Server A (not the same as my Crystal dev machine). I am using the Microsoft SQL Server driver under Other Data Sources. It allows me to enter the server name, user id, and password for the SQL user account, and it even shows me the databases to choose from in the dropdown. I can choose the database and click OK. Then, I can see the view and sproc in the Data Explorer, but when I try to add the sproc I just get a 'General SQL Server error: Check messages from the SQL Server.', and when I try to add the view I get 'Incorrect table name. If you are converting from another database, please use "Set Location" to point to the correct table.'
Obviously, I am not converting from another database. The tables in my view and sproc do exist, as typed, in the databases in Server A and Server B. I think the problem is that the Crystal driver cannot interpret the syntax to select from a linked remote server as shown above. It's looking for a regular table name with no punctuation, and the syntax for a linked server has three periods in it.
So, my question is, can Crystal use this type of data source? If so, what am I doing wrong?
Thanks,
Gerald
I can make a connection to Server A (not the same as my Crystal dev machine). I am using the Microsoft SQL Server driver under Other Data Sources. It allows me to enter the server name, user id, and password for the SQL user account, and it even shows me the databases to choose from in the dropdown. I can choose the database and click OK. Then, I can see the view and sproc in the Data Explorer, but when I try to add the sproc I just get a 'General SQL Server error: Check messages from the SQL Server.', and when I try to add the view I get 'Incorrect table name. If you are converting from another database, please use "Set Location" to point to the correct table.'
Obviously, I am not converting from another database. The tables in my view and sproc do exist, as typed, in the databases in Server A and Server B. I think the problem is that the Crystal driver cannot interpret the syntax to select from a linked remote server as shown above. It's looking for a regular table name with no punctuation, and the syntax for a linked server has three periods in it.
So, my question is, can Crystal use this type of data source? If so, what am I doing wrong?
Thanks,
Gerald
