Deprecated Database Features: SQL 2005 – SQL 2014 Links

Below are reference links to Microsoft documentation web pages listing deprecated database features from SQL 2005 to SQL 2014….  

2005   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.90).aspx  

2008   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx  

2012  http://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx

2014   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx  

Resolving SQL Server Agent Job Issue – System.InvalidOperationException: Unable to generate a temporary class (result=1)

Below is the final error message l kept getting when setting up a SQL Server Agent Job which ran an SSIS package. The SSIS package is designed to create a folder in a network share as well as copy some files into that folder.

 

Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started:  11:03:22 AM
Error: 2014-10-07 11:03:23.35
Code: 0x00000001
Source: Create Reports
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidOperationException: Unable to generate a temporary class (result=1).
error CS2001: Source file ‘C:WindowsTEMPp6e47evu.0.cs’ could not be found
error CS2008: No inputs specified

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at ReportExecutionService..ctor()
at ST_960eb2bb9aa341f4a31d1409aa0aadbb.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  11:03:22 AM
Finished: 11:03:23 AM
Elapsed:  0.546 seconds

 

Resolution:

  • Create a credential on the security level of the sql server instance
  • USE [master]

    GO

    CREATE CREDENTIAL [CredOne] WITH IDENTITY = N’DomainCredOneUser’, SECRET = N’Password’

    GO

  • Create a proxy on the job module of the sql server instance
  • USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_proxy @proxy_name=N’CredOne’,@credential_name=N’CredOne’,

    @enabled=1,

    @description=N’My description’

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=2

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=3

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=10

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=9

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=11

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=12

    GO

  • Ensure the network share folder has full control permission given to the user domainuser
  • Ensure modify permission is given to that same domain account to the folder c:windowstemp on the server box
  • The SQL Job should now run without issues