???? ????? ??? ??? ??????? ???? ??? ????? ??? ????? ?? SQL Server 2005: "?? ???? ????? ????? ??????? ???? ?? ?????? ???? ????"

???? ???????: 913668 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???????? ????? ????? ??????? ?????? ?????? ???????
????? ???: 101935 (SQLBUDT)
????? ???? | ?? ????

?? ??? ??????

???????

??? ??????? ???? ??? ????? (CLR) ??? ????? ?? Microsoft SQL Server 2005 ?? ????? ????? ??? ?????? ??????? ???????:
msg 6522 ? ????? 16 ???? 2? ????? ?????
??? ??? .NET Framework ????? ????? ????? ??????? ?? ??? ???????? ?? ??????? 'ObjectName':
System.InvalidOperationException: ?? ???? ????? ????? ??????? ???? ?? ?????? ???? ????. ???? ?? ??????? ????? ??????? ??? ????? ??????? ?? ?? ???????? ??????? ?????? pre-generated. ?????? ?????? ????????? ??????? ?????? ??? ???? ?? ?????????. ---> System.IO.FileLoadException: ??? ?? ????? LoadFrom() ? LoadFile() Load(byte[]) ? LoadModule() ?????? ??????.
System.IO.FileLoadException:
?? System.Reflection.Assembly.nLoadImage (???? rawAssembly [] ? ???? rawSymbolStore [] ? ???? ???? ? StackCrawlMark & stackMark fIntrospection ??????)
?? System.Reflection.Assembly.Load (rawAssembly [] ???? ???? [] rawSymbolStore ? securityEvidence ????)
?? Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch (?????? CompilerParameters ? ????? ????? ??????? [])
?? Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
?? Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
?? System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] s
...
System.InvalidOperationException:
?? System.Xml.Serialization.Compiler.Compile (??????? ????? ?? ns ????? ? ?????? CompilerParameters ?? ???? ????)
?? System.Xml.Serialization.TempAssembly.GenerateAssembly (XmlMapping xmlMappings [] ?? ????? [] ??? ? defaultNamespace ????? ? ???? ???? ? ?????? CompilerParameters ? ??????? ??????? ?? ????????? Hashtable)
System.Xml.Serialization.TempAssembly??????? (XmlMapping xmlMappings [] ?? ????? [] ??? ? defaultNamespace ????? ? ???? ????? ?? ???? ????)
?? System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace)
System.Xml.Serialization.XmlSerializerctor(Type type, String defaultNamespace)
?? System.Xml.Serialization.XmlSe...
??? ???? ??????? ?? ????? ????? ????? ??? ??????? ???? CLR ??????? ???? ??? ?? ????? ??????? ?? ??????? ??????? ?? ??? ???????? ??? XML ???? SQL Server.
???? ??? ?????? | ????? ???????

?????

???? ??? ??????? ??? ?? ????? ???? CLR ??? XML ??? ????????. ??? ??? ??????? ???? Foundation ??????? Windows ????? (code-named ?????? "??????") ?? ??? ???:
  • ????? ????? ????? ????? ???????? ??? ??? XML ????.
  • ??? ??????? ??? ?????.
  • ????? ??????? ?? ???? ??????? ??????.
??? ???? ?? ???? SQL Server ?????? ??? ??? ????? ?? ?????? ??? ????? ?? CLR SQL ?????? ?????. ?????? ???? ????? ????? ???????? ?? ??? "???????". ?? ???? ??? ?????????? ?????? CLR ???? ??????? ??? ??? ?????? XML.

????? ?? ????????? ??? Foundation ??????? Windows ?? ?????? ???? ???? ????? Microsoft (MSDN) ?????? ??? ?????:
http://msdn2.microsoft.com/en-us/library/ms735119.aspx
(http://msdn2.microsoft.com/en-us/library/ms735119.aspx)
?? ????? ????? ????? ???????? ?? ??? "???????" ?????? ???????:
  • ?????? ???????? ???????? CLR ????? ???????? CLR ???? ???? ??? XmlSerializer. ?? ????? ??? ???????? CLR ????????? ??????? ? ????? ??????? ??????? ?? ??? ???????? ??????? ? ????????.
  • ??????? ???? ??? ?? ????????? ???????? CLR.
  • ????? ?? ???? ???????? CLR ??? ?? ?? SQL Server ???????? ???? HTTP/SOAP ????? ??? SQL Server.
  • ????? ?????? CLR ??? ???? ?? ??? ???????? ??? ??? ?????? XML.
???? ??? ?????? | ????? ???????

????

??? ??? ???????? ??? ??????? ?????? ???? ?????? XML (Sgen.exe) ????? ????? ????? ????? ???????? ??? ??? XML ?? ????? ?????? ??????. ?? ????? ????????? ?? SQL Server ????? ????????.

???? ???????? ????????

For example, you may want to create a CLR function that returns XML data by using an assembly that is created by the following code example:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;   
using System.Xml;
using System.Xml.Serialization;
using System.Text;
using System.IO;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static  SqlString XMLTest()
    {
        Person p = new Person();
        return new SqlString(p.GetXml());
        
    }
    public class Person
    { 
        public String m_FirstName = "Jane";
        public String m_LastName = "Dow";

        public String GetXml()
        {
          XmlSerializer ser = new XmlSerializer(typeof(Person));
          StringBuilder sb = new StringBuilder();
          StringWriter wr = new StringWriter(sb);
            ser.Serialize(wr, this);

            return sb.ToString();
        }

    }
}
When you call the XMLTest function in SQL Server Management Studio, you expect to receive the following result:
<?xml version="1.0" encoding="utf-16"?>
<Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<m_FirstName>Jane</m_FirstName>
<m_LastName>Dow</m_LastName>  
</Person>
?????? ??????? ??????? ??? ???? ?????? ????? XML ????? ??????? ??????? ??????. ?????? ???? ????? ??????? ????? ????? ??????? ??????.

?????? ????? ??? ????? ????? ?????? ???????:
  • ??? ??? ?????? ????? ?????? dbTest ?? ???? SQL Server 2005.
  • ??? ??? ???? ????? ??????? ?? C:\CLRTest ??????.

??????? 1: ????? ????? CLR ???? SQL ???????? Microsoft Visual Studio 2005

????? ????? ??????? ????? ????? ???????? ??? ??? ???????? ?????? ????? ????? ?? Microsoft Visual Studio 2005. ?????? ????? ???? ??????? ???????:
  1. ??? ????? Visual Studio 2005.
  2. ????? ????? SQL Server ???? ???? MyTest.
  3. ?? ???? ?????? ????? ?????? ????? ?????? ? ???? ??? ?????? ???? ??????? ?????? ?????? dbTest ?? ?? ???? ??? ?????.

    ??? ?? ??? ?????? ?? ??????? ??? ?? ???? ?????? ???? ????. ?????? ????? ???? ??? ????? ???? ????.
  4. ?? ??????? ??????? ???? ??? ????? "???? ????? ?? ??? ????????". ???? ???? ?????? ????? ???? ????.
  5. ???? ??? ????? ?????? ??? ????. ???? ???????? ???? ????? Function1.cs.

    ?????? ???? ????? ????? ???????? ?? ??? "?????? ?????" ?????? ?? ???? ????? ??????? ??? ????? ???????? ?? ????? ??????? SQL ???????? ???????: 
    SELECT [dbTest].[dbo].[XMLTest] ()
    ???? ??? ?? ???? ??????? 6-16 ??? ??? ???????.
  6. ?? ?????? ???????? ???????? ?? ?????? "??? ??????" ??? ??? Function1.cs.
  7. ?? ??????? ??????? ???? ??? MyTest ?????.
  8. ?? ???? ?????? MyTest ???? ??? ?????? ????? ?????.
  9. ???? ????? ?????? ?? ?????? Post-build ??? ??????? ?????:
    "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe"/force"$ (TargetPath)"
    ?????? ???? ??????/??? ?????? ????? ????? ????? ???????? ??? ??? ???? ?? ??? ??? ????? ??????? ??????. ???????? ??? ???? ??? ????? ??? ????? ??? ??? ?????? Visual Studio 2005 ?? ???? ???.
  10. ?? ?????? C:\CLRTest ????? ????? ?? ????? Predeployscript.sql ? Postdeployscript.sql.
  11. ????? ?????? SQL ???????? ??????? ??? ??? Predeployscript.sql: 
    IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyTest.XmlSerializers') 
	DROP ASSEMBLY [MyTest.XmlSerializers]
  12. ????? ?????? SQL ???????? ??????? ??? ??? Postdeployscript.sql: 
    CREATE ASSEMBLY [MyTest.XmlSerializers] from
'C:\CLRTest\MyTest\MyTest\bin\Debug\MyTest.XmlSerializers.dll'
WITH permission_set = SAFE
  13. ?? ??????? ??????? ???? ??? ??????? ????? ??????.
  14. ?? ???? ?????? ????? ???? ????? ??? ???? ?????? C:\CLRTest ?? ???? ??? All Files (*. *) ?? ????? ????? ???????.
  15. ?? ?????? ??? ????? ? ???? Postdeployscript.sql;Predeployscript.sql ??? ?? ???? ??? ?????.
  16. ?? ??????? ????? ? ???? ??? ??? MyTest.
  17. ????? ??????? SQL ???????? ??????? ?? SQL Server Studio ?????: 
    SELECT [dbTest].[dbo].[XMLTest] ()
    ????? ???? ??????? ???????.

??????? 2: ????? ????? CLR SQL ?? ???? ????? ??????? Visual Studio

  1. ??? ???? ?????? C:\CLRTest.
  2. ????? ??? ??? ???? MyTest.cs.
  3. ?? ?????? ???????? ???????? ?? ?????? "??? ??????" ??? ??? MyTest.cs.
  4. ???? Visual Studio 2005 ???? ?????.
  5. ???? C:\CLRTest ????? ?? ?? ???? ENTER.
  6. ???? /t:library csc MyTest.cs ?? ?? ???? ENTER.
  7. ???? /force sgen.exe MyTest.dll ?? ?? ???? ENTER.
  8. ????? ?????? SQL ???????? ??????? ?? SQL Server ????? Studio: 
    USE dbTest
GO
CREATE ASSEMBLY [MyTest] from 'C:\CLRTest\MyTest.dll'
GO
CREATE ASSEMBLY [MyTest.XmlSerializers.dll] from 'C:\CLRTest\MyTest.XmlSerializers.dll'
GO

CREATE FUNCTION XMLTest()
RETURNS nvarchar (max)
AS
EXTERNAL NAME MyTest.StoredProcedures.XMLTest
GO
  9. ????? ??????? SQL ???????? ??????? ?? SQL Server Studio ?????: 
    SELECT [dbTest].[dbo].[XMLTest] ()
    ????? ???? ??????? ???????.

??? ??? ?????? ????? ????? ????? ????????? ?????? ??? ????? ??????? ????? ????? ???????? ??? ??? XML ????? ????????? ???? ??? ?????? ????? ?????? ??????? ???????. ??? ??? ????? ??? ????????? ????? ????? ???????? ??? ??? XML ?? ????? ?????? SQL Server ???????? ????? CREATE ???????.
???? ??? ?????? | ????? ???????

?????

????? ??? ?????? ??? ???????.
???? ??? ?????? | ????? ???????

?????

?????? ??? ???? ?? ????????? ??? ????? ????? XML ?? CLR ?????? ????? ???????? ?? ?????? ???? MSDN ??????:
http://msdn2.microsoft.com/en-us/library/ms131088.aspx
(http://msdn2.microsoft.com/en-us/library/ms131088.aspx)
?????? ??? ???? ?? ????????? ??? ???? ???? ?????? XML ?? ?????? ???? MSDN ??????:
http://msdn2.microsoft.com/en-us/library/bk3w6240.aspx
(http://msdn2.microsoft.com/en-us/library/bk3w6240.aspx)
???? ??? ?????? | ????? ???????

???????

???? ???????: 913668 - ????? ??? ??????: 06/????? ??????/1428 - ??????: 2.1
????? ???
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
????? ??????: 
kbmt kbsql2005clr kbexpertiseadvanced kbprb KB913668 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????913668
(http://support.microsoft.com/kb/913668/en-us/ )
???? ??? ?????? | ????? ???????

????? ???????

 
???? ??? ?????????? ??? ??????