Following some very helpful input from people, I’ve hit another issue with this.
I need to access the two identical tables in two different databases on two different SQL Instances on two different servers.
This has been relatively easy to do within Clarion… I’ve set up identical table copies in the dictionary with different connection strings, each pointing to different DB’s on different instances.
It works well.
Where I’m struggling is accessing the server/instances with T-SQL calls.
My procedure opens both tables (as defined with different connection strings in the DCT)… one in each instance. I can manipulate and retrieve data without issue, so I know that both connections are valid.
What I cant seem to do is to select a table in a different server/instance with a direct T-SQL call.
The ‘USE’ command doesn’t allow an instance to be specified, so whilst I can refer to a different DB, it only works when that DB is on the same (current) instance.
Can anyone explain how I would then access a DB on different server / instance ?
Just to be clear; You are saying you want to open the customer table twice, but pointing to different data tables on different servers.
So in TPS land you would use a filename variable and then open the Customer file, then change the filename variable to point to your other copy and then open a CustomerAlias file
Im assuming you are trying to do this on a thread which is already working with the correct sql server instance and std clarion file functions?
Ok just seen how you are doing this…
The way Ive done it in the past is one dct, one set of tables. At program setup choose the sql server instance, these could be ‘live’ instance and ‘backup’ instance, once db is selected, the whole program works with just one sql db. Then the T-sql just work.
In your situation, have tried starting a thread with a table from the one db instance you want to send tsql to and tested if that works?
You haven’t told us how you are trying to send the SQL command?
If you have Customer and Customer2 tables defined in your dct, one for each database, can you perform a…
Customer{Prop:SQL} = ‘SELECT * FROM Customer’
…and a …
Customer2{Prop:SQL} = ‘SELECT * FROM Customer’
…without any errors and retrieve the correct data?
I remember having to do this, a long, long time ago and I vaguely remember using the technique that @KevinErskine mentions, but it might not be necessary, depending on exactly what it is you are trying to do?
Jeremy,
First, the USE command only changes the database context on the server\instance to which you are connected
Second - The linked server approach that Kevin suggested is necessary if you need to query against both server\instance\database in one query. If you do not, avoid linked servers. While really handy when needed, linked servers can have major performance issues.
Last - The connection to the specific server\instance happens at OPEN(file). If you plan to use the same dct file definition for both calls you need to define the owner attribute with a variable that is set to the correct server\instance\database. So after the first T-SQL call, you need to CLOSE(file), reset the variable to the second server\instance\database, OPEN(file) to connect then issue the second T_SQL call
I have linked the servers (possible performance issues noted - thank you) and that appears to have helped.
Just to reiterate, the dictionary solution works absolutely fine.
My issue remains with direct T-SQL calls outside of the dictionary-defined tables.
Once I linked the servers, the Server\Instance,DB Name.Table calling syntax works perfectly… on my development machine.
When testing on the clients site however, the Select statement returns ‘Login failed for user ‘sa’’
Now I realise that this is a permissions issue and not a Clarion issue.
However, what I find baffling is that in the previous line of code (as a test) I do a ‘Get’ to a known record (using the dictionary defined table) and it returns the expected data from the second table successfully, so the connection to the secondary server/DB is open and valid at that point.
I have read that…
“for a cross database query, the login needs to be either a user in both databases, or in a group that exists in both databases.”
So that’s my next deep-dive, although I’m not permitted to make any Network changes without the DBA’s approval, so there’ll be a bit of a hiatus.
I’m perplexed however that Dictionary-defined calls can do this, but T-SQL calls can’t.
In the dct you are using different connect string for each. This will open 2 connections, one to each database.
T-SQL can only use one connection to do its thing, and so only talk to the one server it’s connected to. That’s why you need to play with the linked servers and such.
I’d say this would be a lot easier to work on outside of Clarion, and yes, I think it is a permissions issue. Essentially, when you set up a link, it says “If I refer to otherdata.xxx” then login to the otherdata server using these credentials…
Your “sa” user on the other database (in the real environment) probably has the ability to see tables and the data in them, possibly not to modify it, but does not have permission to run certain commands or procedures. So just because you can successfully run a GET (which translates to “Select … from …”) does not mean you can definitely execute a stored procedure, or do DDL, or possibly even update, add or delete a row.
John W’s post has just reminded me that I meant to say that you should never use use the “sa” user in your apps. The “sa” user is a super user and if your system is ever hacked and the “sa” password uncovered you are potentially in big trouble.
It’s possible that the other server has taken steps to safeguard the “sa” permissions, or disabled “sa” access altogether.