Ensure compiler uses quote_schema hook for translates renders

Fixed regression where the special dotted-schema name handling for the SQL
Server dialect would not function correctly if the dotted schema name were
used within the ``schema_translate_map`` feature.

Fixes: #6697
Change-Id: Idb610755cbf8122e71223d5dd0a17fcb61b1b98d

3 files changed, 52 insertions, 13 deletions

diff --git a/doc/build/changelog/unreleased_14/6697.rst b/doc/build/changelog/unreleased_14/6697.rst
new file mode 100644
index 000000000..d77bfc02b
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/6697.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, regression, mssql
+    :tickets: 6697
+
+    Fixed regression where the special dotted-schema name handling for the SQL
+    Server dialect would not function correctly if the dotted schema name were
+    used within the ``schema_translate_map`` feature.
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 8ae56fd54..67da03683 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -4751,7 +4751,7 @@ class IdentifierPreparer(object):
                         "Dialect has no default schema name; can't "
                         "use None as dynamic schema target."
                     )
-            return self.quote(effective_schema)
+            return self.quote_schema(effective_schema)
 
         return re.sub(r"(\[SCHEMA_([^\]]+)\])", replace, statement)
 
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index a0127fa57..2e4a08713 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -614,52 +614,84 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             select(tbl), "SELECT [foo.dbo].test.id FROM [foo.dbo].test"
         )
 
-    def test_force_schema_quoted_name_w_dot_case_sensitive(self):
+    @testing.combinations((True,), (False,), argnames="use_schema_translate")
+    def test_force_schema_quoted_name_w_dot_case_sensitive(
+        self, use_schema_translate
+    ):
         metadata = MetaData()
         tbl = Table(
             "test",
             metadata,
             Column("id", Integer, primary_key=True),
-            schema=quoted_name("Foo.dbo", True),
+            schema=quoted_name("Foo.dbo", True)
+            if not use_schema_translate
+            else None,
         )
         self.assert_compile(
-            select(tbl), "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test"
+            select(tbl),
+            "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test",
+            schema_translate_map={None: quoted_name("Foo.dbo", True)}
+            if use_schema_translate
+            else None,
+            render_schema_translate=True if use_schema_translate else False,
         )
 
-    def test_force_schema_quoted_w_dot_case_sensitive(self):
+    @testing.combinations((True,), (False,), argnames="use_schema_translate")
+    def test_force_schema_quoted_w_dot_case_sensitive(
+        self, use_schema_translate
+    ):
         metadata = MetaData()
         tbl = Table(
             "test",
             metadata,
             Column("id", Integer, primary_key=True),
-            schema="[Foo.dbo]",
+            schema="[Foo.dbo]" if not use_schema_translate else None,
         )
         self.assert_compile(
-            select(tbl), "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test"
+            select(tbl),
+            "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test",
+            schema_translate_map={None: "[Foo.dbo]"}
+            if use_schema_translate
+            else None,
+            render_schema_translate=True if use_schema_translate else False,
         )
 
-    def test_schema_autosplit_w_dot_case_insensitive(self):
+    @testing.combinations((True,), (False,), argnames="use_schema_translate")
+    def test_schema_autosplit_w_dot_case_insensitive(
+        self, use_schema_translate
+    ):
         metadata = MetaData()
         tbl = Table(
             "test",
             metadata,
             Column("id", Integer, primary_key=True),
-            schema="foo.dbo",
+            schema="foo.dbo" if not use_schema_translate else None,
         )
         self.assert_compile(
-            select(tbl), "SELECT foo.dbo.test.id FROM foo.dbo.test"
+            select(tbl),
+            "SELECT foo.dbo.test.id FROM foo.dbo.test",
+            schema_translate_map={None: "foo.dbo"}
+            if use_schema_translate
+            else None,
+            render_schema_translate=True if use_schema_translate else False,
         )
 
-    def test_schema_autosplit_w_dot_case_sensitive(self):
+    @testing.combinations((True,), (False,), argnames="use_schema_translate")
+    def test_schema_autosplit_w_dot_case_sensitive(self, use_schema_translate):
         metadata = MetaData()
         tbl = Table(
             "test",
             metadata,
             Column("id", Integer, primary_key=True),
-            schema="Foo.dbo",
+            schema="Foo.dbo" if not use_schema_translate else None,
         )
         self.assert_compile(
-            select(tbl), "SELECT [Foo].dbo.test.id FROM [Foo].dbo.test"
+            select(tbl),
+            "SELECT [Foo].dbo.test.id FROM [Foo].dbo.test",
+            schema_translate_map={None: "Foo.dbo"}
+            if use_schema_translate
+            else None,
+            render_schema_translate=True if use_schema_translate else False,
         )
 
     def test_delete_schema(self):