|
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. 
https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^]
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:
Provider=sqloledb;Server=servername\instance;Database=databasename;User ID=username;Password=password
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!
"Go forth into the source" - Neal Morse
modified 6-Jan-18 15:15pm.
|
|
|
|
|
|
|
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.
Hope this helps.
|
|
|
|
|
|
Have your cluster manager review
Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs
In a powershell admin window, run the following command to get a listing of RegisterAllProvidersIP
Get-ClusterResource "Network" | Get-ClusterParameter | Where-Object {$.name -eq "RegisterAllProvidersIP" -and $.Value -ne "0"}
If that setting is not 0 then all IPs associated with the cluster resources are published to DNS.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Interesting, I've setup a couple of Availability groups for applications but never had any issues (well once I got the devs to stop trying to use the IPs).
I wonder if the issues you faced are related to
Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs
[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.
|
|
|
|
|
Imagine this pseudo-code:
SP_WRITE_MESSAGE
INSERT INTO MSG
SP_WRITE_COURSE
INSERT INTO COURSE
SP_WRITE_RESPONSE
IF(@SOME_COMPUTED_VALUE = 0)
BEGIN
RAISERROR('Some error message', 16, 1)
RETURN
END
EXEC SP_WRITE_MESSAGE
EXEC SP_WRITE_COURSE
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...
Anyone?
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.
You should also be careful with adding the sp_ prefix to your stored procedures, it doesn't mean "stored procedure".
Is the sp_ prefix still a no-no? - SQLPerformance.com[^]
This post should also be taken with a grain of salt, my brain is still pretty well baked.
|
|
|
|
|
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.
|
|
|
|
|
Jörgen Andersson wrote: The return statement is never reached, because the raiserror exits the statement block.
If the RAISERROR statement is executed, neither of the EXEC calls will be reached either. There's no need for ELSE blocks.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Don't you need errorlevel 20 or above to terminate batch?
|
|
|
|
|
Looking at the documentation[^], a severity of 20 to 25 will not only terminate the batch; it will terminate the connection, and write an error to the error and application logs.
It's not entirely clear, but as far as I can see, a severity of 11 to 19 will terminate the batch (or transfer to the CATCH block), but leave the connection intact.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It certainly is unclear. So I tested by creating a couple of procedures.
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
PRINT 'Start SubSP1'
RAISERROR('Some Error', 16,1) WITH LOG
PRINT 'End SubSP1'
END
GO
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
PRINT 'Inside SubSP2'
END
GO
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
PRINT 'Start MainSP'
EXEC dbo.SubSP1
EXEC dbo.SubSP2
PRINT 'End MainSP'
END
GO
Running the MainSP results in:
Start MainSP
Start SubSP1
Msg 50000, Level 16, State 1, Procedure SubSP1, Line 6
Some Error
End SubSP1
Inside SubSP2
End MainSP This does not stop the execution at all, Neither on statement, scope, batch or connection level.
Changing the errorlevel to 17 results in:
Start MainSP
Start SubSP1
End SubSP1
Inside SubSP2
End MainSP
Msg 50000, Level 17, State 1, Procedure SubSP1, Line 5
Some Error Still doesn't stop the execution, but note the difference in where the error message is printed.
I suspect there will be some real differences between the errorlevels if I add TRY-CATCH or more probably a transaction.
Errorlevel 19 or higher should stop the execution of the current batch according to Database Engine Error Severities | Microsoft Docs[^] but it doesn't.
Errorlevel 20 and above closes connection as expected from the manual.
Couldn't be bothered to check any further. I've proved myself wrong and thereby only proven that one shouldn't try to answer questions on New Years day.
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: with the same parameters
One possibility of course is that you are not in fact using the same parameters.
You didn't mention but presumably in both test cases you are using exactly same database instance (not a copy) and that you have verified that you are using the same instance.
|
|
|
|
|
Same DB instance, same parameters, same error - only that I see the wrong error from code...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Then trace it while running it through ASP.Net.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
https:
I tried the link above but receiving a syntax error, my is easier then that one.
I have datetime, temperature, and humidity written in a table called tempdata.
I would like to have trigger that if value is above 65 for temperature (float), write to a different table or database called test.
Then email out when test is modified.
I've been trying phpmyadmin because I'm not good with command line.
I have phpmyadmin 4.5.4
mysql ver 14.14 distrib 5.7.20
python 2.7.12
Ubuntu mate 1.16.2
Gnome 2
Raspberry pi 2
Thank You
CREATE TRIGGER TemperatureHigh
AFTER INSERT
ON RawTemperature FOR EACH ROW
BEGIN
IF (new.temperature > 65
THEN
INSERT INTO `test`;
END IF;
END
|
|
|
|
|
brian1210 wrote: receiving a syntax error
In what line is the error detected?
Isn't it the problem with "open" bracket without the "close" one here:
IF (new.temperature > 65
|
|
|
|
|
Your INSERT statement does not look complete.
|
|
|
|
|
You have already posted this in QA:
Trigger value > ? write in different table[^]
Your question was incomplete there too.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi all,
If I want to run the query today(12/2/2017) then my data should be inbetween 2016 January 1st to December 31st --I don't want to hard code dates.
if I want to run the query on jan 1st 2018 or in any day in 2018 then my data should be inbetween 2017 January 1st to 2017 December 31st.
if I want to run the query in 2019 then my data should be inbetween 2018 January 1st to 2018 December 31st.
how to write query, please advice.
|
|
|
|
|
- Obtain current date -> obtain "current year" -> obtain ("current year" - 1)
- Make the dates for "between": [("current year" - 1)-01-01] and [("current year" - 1)-12-31]
- use them in your query.
|
|
|
|
|
WHERE YEAR([date_column]) = YEAR(DATEADD(yy,-1,getdate()))
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
<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.
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???