Cannot access my SQL Server 2008 by T4 template error is return to me how to get tables?

Question

I want to generate some code using a T4 template. I am connecting using "sa" and sa's password. But I cannot connect to a remote SQL Server. I am using SQL Server 2008.

Error is below :

Error Running transformation: Microsoft.SqlServer.Management.Smo.FailedOperationException: SetParent failed for Database 'FlyAnt'. --->
Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server MROAPPSQL. --->
System.Data.SqlClient.SqlException: Login failed for user 'MROTECHNIC\yusuf.karatoprak'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetServerVersion()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_ServerVersion()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ValidateParent(SqlSmoObject newParent)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.SetParentImpl(SqlSmoObject newParent)
--- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.SqlSmoObject.SetParentImpl(SqlSmoObject newParent)
at Microsoft.SqlServer.Management.Smo.Database..ctor(Server server, String name)
at Microsoft.VisualStudio.TextTemplating3B02A40A7B5CFF5BC85C47D46E8EE44A.GeneratedTextTransformation.CodeGenerator.RunCore()
at T4Toolbox.Generator.Run()
at Microsoft.VisualStudio.TextTemplating3B02A40A7B5CFF5BC85C47D46E8EE44A.GeneratedTextTransformation.TransformText()
at Microsoft.VisualStudio.TextTemplating.TransformationRunner.RunTransformation(TemplateProcessingSession session, String source, ITextTemplatingEngineHost host, String& result)

My code is this:

<#@ template language="C#"#>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>
<#@ assembly name="System.Xml"#>


<#@ import namespace="System.Reflection"#>


   <#+
    public class CodeTemplate : Template
    {
        public string DatabaseName;
        public string ServerName;
        public string TableName;

        public override string TransformText()
        {
            Server server = new Server(ServerName);
                server.ConnectionContext.LoginSecure = false;

                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "123456";
            server.ConnectionContext.Connect();
            Database database = new Database(server, DatabaseName);
            Table table = new Table(database, TableName);
            table.Refresh();

                string DataType= string.Empty;
            WriteLine("using System;\nusing System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Text;\n");

            WriteLine("namespace MyCustomEntities\n{");

            WriteLine("//Select");
                WriteLine("\tpublic partial class My" +table.Name+ "\n\t{");
            GenerateProperties(table);
                   //output the contructor
            WriteLine("\n\t\tpublic My" + table.Name + "()\n\t\t{\n\t\t//constructor\n\t\t}" );

            //end class
            WriteLine("\t}");
              WriteLine("//Update Delete Save");
                WriteLine("\tpublic partial class My" +table.Name+ "\n\t{");

                WriteLine("\t}");
                WriteLine("}");
            return this.GenerationEnvironment.ToString();
          }

        protected void GenerateProperties( Table table)
        {
                 foreach (Column  column in table.Columns)
                {
                    switch(column.DataType.Name)
                    {
                        case "nvarchar":
                        case "varchar":
                        case "nchar":
                        case "char":
                            WriteLine("\t\tpublic string "+column.Name+"{ get; set;}");
                            break;
                        case "int":
                        case "smallint":
                        case "bigint":
                            WriteLine("\t\tpublic int "+column.Name+" { get; set;}");
                            break;
                        case "datetime":
                            WriteLine("\t\tpublic DateTime "+column.Name+" { get; set;}");
                            break;
                        case "image":
                            WriteLine("\t\tpublic byte[] "+column.Name+" { get; set;}");
                            break;
                        case "bit":
                            WriteLine("\t\tpublic bool "+column.Name+" { get; set;}");
                            break;
                    }
            }
        }
    }

   #>

My connection code :

<#@ template language="C#" hostspecific="True"  #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>
<#@ assembly name="System.Xml"#>
<#@ output extension="txt" #>
<#@ import namespace="System.Reflection"#>

<#@ include file="T4Toolbox.tt" #> 
<#@ include file="CodeGenerator.tt" #> 

<# 
    CodeGenerator gen = new CodeGenerator();

    gen.ServerName="MYSQLSERVER";
    gen.DatabaseName="FlyAnt";
    gen.Run();


#>

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! Then you also don't need any annoying > and so on ... just format your code properly! — marc_s

Enrico Campidoglio Enrico Campidoglio · Accepted Answer · 2011-11-24T10:16:28

The exception message states that you're attempting to login using a Windows account, which is the identity of the running process:

System.Data.SqlClient.SqlException: Login failed for user 'MROTECHNIC\yusuf.karatoprak'

If you look at the stack trace, you'll find that the failed connection attempt actually originates from the constructor of the Database class that takes the Server object as argument:

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
[...]
at Microsoft.SqServer.Management.Smo.Database..ctor(Server server, String name)
[...]
at T4Toolbox.Generator.Run()

It also seems that the generation process is being kicked off by T4 Toolbox.

Cannot access my SQL Server 2008 by T4 template error is return to me how to get tables?

1 Answers