Hello everybody, i'm writing here because i'm really becoming crazy, i read every kind of internet pages but I'm not able to find a solution but i'm also sure that the solution is behind the corner.
I have an Access database with different tables. The main table has different foreign keys to other tables. I do an example with 2 tables to make the problem easier:
Field1 - Primary Key: Product Code - text
Field2: Description - text
Field3: Product Type - integer
Field1: Primary Key - ID - integer - FK on Field3 of "Table Product"
Field2: Description - Text
Now, I open Visual Studio, I add my database and I drag&drop the table "Product" on my Windows Form as a DatagridView. The wizard create for me, the tableAdapter, bindinsource ecc....
If I start my app the DatagridView is populate with the correct data but in the column "Product Type" I see the number from the PK of the table "ProductTypes" while I'd like to see the Description.
I created a new TableAdapter query that give me the result i want to see but when I confirm I get the message that i'm created a query that give a different result from the original schema. If I continue the query is created but i'm not able to retrieve data using this new query.
THe only way I've to retrieve datas as I want is to modify the original Fill query but in this case the relation between the 2 tables will be removed and also the TableAdapter Insert method will be removed.
So, I will see the data in datagridview as i'd like to see but I don't have anymore the insert method.
Can someone help me by telling I could I retrieve the data in the way I desider without losing the chance to insert new record in the table?
Yes, I already did it.
I created a View in Access and then I imported it in my Dataset. In this way I'm able to show the correct data in my DataGridView but the Wizard didn't create for me the Insert, Delete and Update method.
Do you think I could be able to update the Access database through the view?
I think to have solved the problem....maybe I'm using a bad solution but the result is good.
In my dataset I have the Main table that is filled with the original database records. In the same datased I inserted a second table that is filled with a my personal Fill query.
In my form I have inserted both table adapter. The first one is only in background while the second one is shown in datagridview.
I insert, delete and modify the records one the datagrid and when I click "Save", I reflect the changes in the background table adapter....
This question was originally asked over a week ago in quick answers.
Edit: (2018-01-06) There is no problem getting the sqloledb driver to connect to an availability group. The problem cited below has been resolved. (possibly by forcing protocol as in tcp: ag-listener_name for the server) The client's dba tweaked some settings and it all works now...not sure what he did. Thanks to all responders.
I am posting it again here due to bad timing (right before holiday break) which possibly resulted in this question not being noticed by an expert before getting pushed back to page 50+. @OriginalGriff did post a solution, albeit not a viable one. The original question is as follows:
I've been connecting to SQL Server databases for almost 20 years, but my latest client has me baffled. For the first time that I know of, our database has been made part of an Availability Group under SQL Server 2012. This is a new client and so far, their impression of me is probably not that great.
Quite simply, the problem is that I am unable to connect a legacy application using the OLEDB provider to a database hosted in a SQL 2012 Availability Group. Despite 2 hours of working with a junior tech. on their end, and trying many different combinations, my application is not able to find the server. What I'm wondering, is if there is any special parameter required in the connection string when connecting to an availability group?
The current form of the connection string that is failing is:
While on the remote with the junior tech., we were able to remote into the server and verify that the database was available, and that our sql login was setup correctly...also verified that sql authentication was enabled. (I've been bit by that dog before) Everything seems to be setup correctly...even made sure that the client firewall was disabled. No joy!
This fiasco took place on the last business day of this year for the client, so I have been unable to try anything since learning a little more about AGs/Listeners. It would be ideal to replicate the customer's environment for testing, but setting up a failover cluster and AG seems like overkill when there's probably a simple parameter that I am missing.
Other info: From the workstation having the problem, I can successfully ping the database servers. (primary and replica) The error message returned is 'The Server does not exist or access is denied'. It seems more like a timeout than a permissions issue. Also, if it matters, the application uses ADODB connections/objects.
It will be over two weeks until the customer returns from the holidays. In the meantime, I'm charged with explaining to their lead tech. (who was unavailable when we were having problems) what the problems are, and what we are going to do to fix it. (in addition to a lot more crap they want now)
What I have tried:
0) Searched connectionstrings.com
1) Spent the last two days reading up on Availability Groups and client connections. I have a few things to try, but it'll be trial by fire unless I invest the time to replicate the environment.
Things I would try if I were able:
0) Prepend the server name (in the connection string) with tcp: (is this important?)
1) Use the listener name instead of server/instance (even though all the documentation I read says this should work)
2) Add a connection string parameter (MultiSubnetFailover=True)
3) Check the port numbers being used
4) Try a different variation of OLEDB connection string
What I'm hoping is that someone here may have access to an AG test environment and can help confirm that it is even possible to connect with the OLEDB provider per the connection string example given. Any help or hints are greatly appreciated!
Update: The customer is requesting another remote and conference tomorrow to try to resolve this problem. I have a few things now to try, and they have senior staff on hand that probably will know exactly how to reach the server/AG. What I'm still in the dark about is if I will need to add another connection parameter for it to work. If that's the case, it will require an update to the code that constructs/saves/recalls the connection details. Thanks again for any hints!
Oh, right, MultiSubnetFailovercannot be set via OleDb -- OleDb considered harmful. Without that setting our OleDb connections failed about half the time.
We started hearing the "Microsoft is deprecating OleDb" rumor a few years back (2012?) but we hadn't had any issues until this past summer when one of our partners changed their configuration.
Our partner said we had to use the SQL Client via ADO.net and I said I bet we don't -- so we use the Native Client via ODBC via ADO.net . ODBC can set MultiSubnetFailover
First, thanks PIEBALDconsult for the help and information!
Second, I've just gotten the connectivity issue worked out...partially. Our program connects and works perfectly on the client's application server. Previously, we were trying to run the program from one of the user's desktop. (actually from a shared drive on the same application server) We had made sure that there was no firewall on the local machine so I still don't understand how/where the connection from the client to the database server was being blocked.
Third, it really sucks when the quality of your software gets called into question due to a problem on the client's end. <sarcasm>Of course the problem had to be that old sqloledb driver! Why are you still using that ancient technology? So I spent many hours modifying the available sql connection options to include the sql native driver/provider (both with and without ODBC) only to find that the changes were not really necessary.
I have a feeling this is going to be a difficult client...
I had a similar problem a while ago. Sometimes it would connect and other times it would time out depending on which server was the current primary. I believe there is a DNS setting you can use to get around this but that was not an option in our case. The only solution I came up with was to set a long timeout (300 seconds) in the connection string. So now sometimes it connects right away and the rest of the time it will take about a minute. Not ideal but we've been able to live with it.
Thanks Joe! You may be on to something here...extending the timeout. It was the one thing I didn't try. Still, it's strange that their app server is able to connect instantly...maybe a hosts file entry??? This has been confirmed on two end user workstations...reports back that the server can't be found or having trouble locating server, depending on which provider is in play. I will give this a shot tomorrow...also checking into remote app as a possible solution for them.
I really hope I don't need to extend the timeout as I and my customer consider more than a few seconds to connect to an intranet resource unacceptable.
[Snippet] Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout.
Workarounds: We recommend that you do one of the following tasks.
If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).
Pros: If a cross-subnet failover occurs, client recovery time is short.
Cons: Half of the client connections will take more than 20 seconds
If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting” later in this section.
Pros: You do not need to increase your client-connection timeout value.
Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.[/Snippet]
In all likely hood the issue was the RegisterAllProvidersIP. If the HA Group is running in a multi subnet cluster. Then for every Node in the HA group its IP is published to DNS, So if there are 5 nodes that it can live on, there are five different IPs listed in DNS. Traversing them all can take some time to find the right one currently active.
Easy fix, set RegisterAllProvidersIP to 0.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
Now there is an FK error in SP_WRITE_COURSE and I can see it (and fix) when running SP_WRITE_RESPONSE from SSMS, but running from the application (IIS hosted) with the same parameters I get 'Some error message', even I can see that SP_WRITE_MESSAGE executed...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
The return statement is never reached, because the raiserror exits the statement block.
So you should either add an else statement or add try and catch blocks. And if you use SqlServer 2012 or newer you should also consider using throw instead of raiserror.
It was true, but the RAISERROR statement itself never reached as the if before it resolves to FALSE...
Pay attention to the flow:
1. IF is FALSE
2. Does EXEC SP_WRITE_MESSAGE with success (can see the record in DB)
3. Does EXEC SP_WRITE_COURSE and fails. This is the point of interest... Running from SSMS I receive the expected FK violation error, running from ASP.NET (C#) I receive 'Some error message'...
And I do not prefix my stored procedures SP_ in real life, just done it here to identify them as SPs...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Which means the IF behaves differently in the two environments.
One common difference between SSMS and dotNet connection is the environment settings. The most interesting in this case is probably the ARITHABORT setting. It's normally ON in SSMS and OFF in a client connection.
Try to set ARITHABORT ON or OFF in your code to check if there is a difference in behavoiur.