March 18, 2024 at 6:55 pm
All,
Need a linked server guru. Trying the following in a stored procedure:
SET @SQL = 'DROP TABLE IF EXISTS DB_NAM.SCHMA_NAME.TBL_NAME';
IF @@SERVERNAME = 'QASERVERNAME'
BEGIN;
SET @HUBServerName = '[QAHUBSERVERNAME]';
EXEC (@SQL) AT QAHUBSERVERNAME;
END;
Then I get this:
Msg 3906, Level 16, State 2, Line 10
Failed to update database "DB_NAM" because the database is read-only.
As I know for certain that this database is NOT in a read-only state, so perhaps it is permissions ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2024 at 3:44 am
Can you provide more details about the Instance and Store procedure ?
-- Is the instance part of AG Group or Standalone?
-- Is stored procedure switching to different linked server based on the environment?
=======================================================================
March 19, 2024 at 1:31 pm
The database is part of an AG group, but the server it is on is the publisher and is thus where we go when we need to make changes. I'm pretty sure the AG groups have just databases in them and NOT servers as a whole.. And yes, there is a pair of if statements based on which server it runs on (QA or Prod) to determine which server to EXEC AT ...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2024 at 9:12 pm
As it turns out, my error is NOT from the DROP TABLE IF EXISTS statement. It is the:
...
')
SELECT SD.*
' + CASE
WHEN @FileFrom = 'ONETIME' THEN 'INTO ' + @HUBServerName + '.HUB_DB_NAME.SCHMA.HUB_TABLE_NAME
'
ELSE ''
END +
'FROM SEQUENCED_DATA AS SD
ORDER BY SD.RowNumberWithinTheFile;
';
The ,.. refers to the CTE portion which is entirely irrelevant here. The failure comes from the attempt to SELECT * INTO the "to be newly created" table on the hub server. Can I for some reason NOT do this ? I know for certain that the server being referenced is where the updateable copy of that database is. Might there be some kind of vague permissions issue associated with linked servers that I don't know enough about to go find or look at ?
H E L P ! ! !
I'm seriously at my wits end with this. If I was referencing an actual read-only copy of the database, or I was getting a syntax error, this would be a whole different ballgame. Is there some odd thing associated with a linked server name being the exact same name as the server itself ???
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2024 at 9:35 pm
Okay... new update.... take out the CTE entirely, and just try to test the concept, and suddenly I have a syntax error with "too many prefixes" for the object name. Why the heck does the presence of a CTE affect syntax for the final query beyond allowing the CTE name to be referenced ??? Seems to me that something is seriously wrong here and I just can't figure it out...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2024 at 9:46 pm
An AG doesn't have a 'Publisher' - directing a connection to a specific node in an AG will connect to that specific node. If that node is not the primary for the database you are attempting to access - then you will get the above error because that replica is in a read-only state (and then, only if you have the replica configured for read-only).
To ensure you are connecting to the correct node - you need to connect to the listener. The listener will direct the connection to the appropriate replica based on the connection criteria. For example, if you have read-routing configured - and the connection includes the read only flag then the listener would redirect the connection to the secondary replica instead of the primary replica.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2024 at 10:01 pm
Intriguing Jeff... but you'll need to go into slightly more detail.... I know for certain that the server I am referencing is the PRIMARY for that database. It is where we can add indexes and do all that "change" stuff for our QA environment. Now the question is how the AG is configured, perhaps ? Is there some way around this? Also, I've heard the term "listener" for some time, and I don't know that I understand what exactly that is. How can one find the "listener" for a given database? Is there a T-SQL query I can run, or do I just need to navigate SSMS somehow ?
EDIT: There is no listener for this AG group. Does that matter? This is definitely NOT any kind of read-only access... need full rights of the login provided via the linked server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2024 at 11:38 pm
How can you be sure you are on the primary?
If you have permissions - you can query the DMV's: SELECT * FROM OPENQUERY(qahubservername, 'SELECT sys.fn_hadr_is_primary_replica(''dbname'')' where dbname is the name of the database you are using.
You should also check the actual server you are connecting to: SELECT * FROM OPENQUERY(qahubservername, 'SELECT @@servername')
An AG is a cluster with multiple nodes - the primary will be the node in the cluster hosting the read/write version of the database. The other node(s) will be secondary replicas and can be set to read only or read intent or not set to be able to read. To connect to the primary regardless of which node is currently the primary (in case of fail over) - you set and configure a listener. The listener has an IP address and a name and can be seen in SSMS under the Always On High Availability folder - or directly in the Failover Cluster Manager.
If you are not referencing the listener and connecting directly to one of the servers in the cluster - then you could be connecting to the server that is now the secondary after a fail over.
If there is no listener configured - and the availability group has been failed over to the other node - then you need to update your linked server to direct it to the new primary node.
As for all of your other issues - hard to tell without seeing the actual code, but it appears that you are attempting to generate a dynamic SQL statement that creates a table using INTO which will give the same read/write error when the database is read-only.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 20, 2024 at 4:15 am
Jeff,
Pretty sure you've nailed it... I think we need a listener, and there isn't one. I know for sure the server name of the primary, and it seems unlikely that it has ever failed over. However, that said, I will verify and see if I can get a listener set up correctly...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2024 at 10:41 am
Few details about AG Group Setup/Listener
=======================================================================
March 21, 2024 at 3:23 pm
Okay folks... day 3 and I'm currently awaiting today's action to create a listener... However.... There IS a listener in our production environment, and just to try and test things, I tried creating a copy of our relatively small Dim.Date table as DataFeed..DimDate, and ran the suggested queries Jeff provided to prove that I am on the "correct server", and I am. However, I still appear to be getting read-only access, and I just don't quite understand why. The fn_hadr function is returning 1, and the @@SERVERNAME is returning the primary server name for the AG group the relevant database is in. And I continue to get the same error.
For giggles, I tried not using a CTE and then I get the error about too many prefixes.. which seems rather odd that the presence of a CTE suddenly allows a form of the query that isn't otherwise valid... outside of any reference to the CTE name. This is frustrating me to no end... It's almost as if nothing I do will get me write access.. So at this point, I am thinking maybe it is configuration of the AG... but I don't really know what to look for that would tell me HOW it will work as opposed to merely knowing how it is configured...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply