Ошибка недопустимое имя столбца sql

I suspect that you have two tables with the same name. One is owned by the schema ‘dbo’ (dbo.PerfDiag), and the other is owned by the default schema of the account used to connect to SQL Server (something like userid.PerfDiag).

When you have an unqualified reference to a schema object (such as a table) — one not qualified by schema name — the object reference must be resolved. Name resolution occurs by searching in the following sequence for an object of the appropriate type (table) with the specified name. The name resolves to the first match:

• Under the default schema of the user.
• Under the schema ‘dbo’.

The unqualified reference is bound to the first match in the above sequence.

As a general recommended practice, one should always qualify references to schema objects, for performance reasons:

• An unqualified reference may invalidate a cached execution plan for the stored procedure or query, since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query. This results in recompilation of the query/stored procedure, a performance hit. Recompilations cause compile locks to be taken out, blocking others from accessing the needed resource(s).

• Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object (that owned by ‘dbo’). This is the usual case. The only time a single probe will resolve the name is if the current user owns an object of the specified name and type.

[Edited to further note]

The other possibilities are (in no particular order):

• You aren’t connected to the database you think you are.
• You aren’t connected to the SQL Server instance you think you are.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

Перейти к контенту

ALTER TABLE Location 
ADD TransitionType SMALLINT NULL
GO   

UPDATE Location SET TransitionType =  4

ALTER TABLE Location 
    ALTER COLUMN TransitionType SMALLINT NOT NULL

select id
from products

create view app.foobar as
select id
from products

There can be many things:
First attempt, make a select of this field in its source table;
Check the instance of the sql script window, you may be in a different instance;
Check if your join is correct;
Verify query ambiguity, maybe you are making a wrong table reference
Of these checks, run the T-sql script again

[Image of the script SQL][1]
  [1]: https://i.stack.imgur.com/r59ZY.png`enter code here

+-------------+    +-------------+    +-------------+    
| table1      |    | table2      |    | table3      |    
+-------------+    +-------------+    +-------------+    
| row1 | col1 |    | row2 | col2 |    | row3 | col3 |    
+------+------+    +------+------+    +------+------+    
| ...  | ...  |    | ...  | ...  |    | ...  | ...  |    
+------+------+    +------+------+    +------+------+    

SELECT * FROM table1, table2 LEFT JOIN table3 ON row1 = row3; --throws an error

SELECT * FROM table2, table1 LEFT JOIN table3 ON row1 = row3; --works as expected

SELECT * FROM table1, table2 LEFT JOIN table3 ON row1 = row3; --throws an error

USE MyDatabase;

BEGIN TRANSACTION;

-- some statements

PRINT(N'#1');

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table1' AND COLUMN_NAME = 'Table2_Id'))
BEGIN
    ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2_Table2_Id];
    ALTER TABLE [dbo].[Table1] DROP COLUMN [Table2_Id];
    DROP TABLE [dbo].[Table2];

    PRINT(N'Table2 was dropped.');
END

PRINT(N'#2');

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'Table2'))
BEGIN
    CREATE TABLE [dbo].[Table2]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Number] INT NOT NULL UNIQUE,
        [Name] NVARCHAR(200) NOT NULL,
        [RowVersion] TIMESTAMP NOT NULL
    );
PRINT(N'Table2 was re-created.');
    INSERT INTO [dbo].[Table2]([Number], [Name]) VALUES(-1, N'Default value');
PRINT(N'Default value was inserted in Table2.');
END

-- some statements

COMMIT TRANSACTION;

   WHILE  @startissuetime<='23:30'
        BEGIN
        ALTER TABLE Intraday_Forecast_temp
            ADD IssueTimeUTC SMALLDATETIME 

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
            WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

WHILE  @startissuetime<='23:30'
    BEGIN
        ALTER TABLE Intraday_Forecast_temp
        DROP COLUMN IssueTimeUTC 
        --------
        BULK INSERT..... 
        --------
        ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

Если вы используете SSMS (бесплатную SQL-Server Management Studio), чтобы создать оператор INSERT INTO, щелкнув правой кнопкой мыши нужную таблицу, выберите таблицу сценария как, выберите INSERT To в новом окне запроса, которое мы получим (используя таблицу с именем Customers) .

INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[Address]
           ,[City]
           ,[State]
           ,[ZipCode]
           ,[AccountNumber]
           ,[JoinDate])
     VALUES
           (<FirstName, nvarchar(max),>
           ,<LastName, nvarchar(max),>
           ,<Address, nvarchar(max),>
           ,<City, nvarchar(max),>
           ,<State, nvarchar(max),>
           ,<ZipCode, nvarchar(max),>
           ,<AccountNumber, nvarchar(max),>
           ,<JoinDate, datetime2(7),>)

Теперь измените раздел VALUES, используя DECLARE для каждого значения.

DECLARE @FirstName nvarchar(max)
DECLARE @LastName nvarchar(max)
DECLARE @Address nvarchar(max)
DECLARE @City nvarchar(max)
DECLARE @State nvarchar(max)
DECLARE @ZipCode nvarchar(max)

INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode)

Затем создайте класс, а не помещайте операции с данными в Program.cs с методом, специфичным для добавления новой записи (в следующем примере по-прежнему используется таблица Customers).

Полный исходный код, откуда взят следующий код.

• Альтернативой cmd.Parameters.AddWithValue является cmd.Parameters.Add, которая обеспечивает точную настройку типа параметра.

• Альтернативой получению нового первичного ключа, если это необходимо, является добавление точки с запятой в конец INSERT INTO и добавление SELECT CAST(scope_identity() AS int); затем используйте Convert.ToInt32(cmd.ExecuteScalar()) для получения нового ключа. Поэтому после тестирования с помощью SSMS просто вставьте запрос в строковую переменную, и если это не сработает, произойдет что-то еще.

  public bool AddCustomer(string FirstName, string LastName, string Address, string City, string State, string ZipCode, ref int NewPrimaryKeyValue) { bool success = false;

   using (var cn = new SqlConnection { ConnectionString = ConnectionString })
   {
       using (var cmd = new SqlCommand { Connection = cn })
       {
           cmd.CommandText = 
               "INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) " + 
               "VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode)";
  
           try
           {
               cmd.Parameters.AddWithValue("@FirstName", FirstName);
               cmd.Parameters.AddWithValue("@LastName", LastName);
               cmd.Parameters.AddWithValue("@Address", Address);
               cmd.Parameters.AddWithValue("@City", City);
               cmd.Parameters.AddWithValue("@State", State);
               cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
  
               cn.Open();
  
               int result = cmd.ExecuteNonQuery();
  
               if (result == 1)
               {
                   cmd.CommandText = "Select @@Identity";
                   NewPrimaryKeyValue = Convert.ToInt32(cmd.ExecuteScalar());
                   success = true;
               }
           }
           catch (Exception ex)
           {
               HasErrors = true;
               ExceptionMessage = ex.Message;
               NewPrimaryKeyValue = -1;
               success = false;
           }
       }
   }
  
   return success;
  

  }

вызов вышеуказанного метода.

Вы также можете проверить имена столбцов, используя следующее (все еще сохраняя таблицу клиентов)

SELECT ORDINAL_POSITION, 
       COLUMN_NAME, 
       DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customer'
      AND TABLE_SCHEMA = 'dbo';

Полученные результаты

1,id,int
2,FirstName,nvarchar
3,LastName,nvarchar
4,Address,nvarchar
5,City,nvarchar
6,State,nvarchar
7,ZipCode,nvarchar
8,AccountNumber,nvarchar
9,JoinDate,datetime2

Изменить

Другой вариант — создать класс, который представляет данные для вставки, например.

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
    public string AccountNumber { get; set; }
    public DateTime? JoinDate { get; set; }

}

Затем здесь мы используем переданные значения. Обратите внимание, что в этой версии cmd.Parameters.AddWithValue заменяется на cmd.Parameters.Add, а запрос на получение нового первичного ключа добавляется после INSERT INTO, разделенных точкой с запятой.

Чтобы вызвать создание экземпляра класса Customer, заполните свойства и вызовите метод.

public bool AddCustomer(Customer customer)
{
    bool success = false;

    using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new SqlCommand { Connection = cn })
        {
            cmd.CommandText =
                "INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) " + // insert
                "VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode);" +             // insert
                "SELECT CAST(scope_identity() AS int);";                                      // get new primary key

            try
            {
                cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar))
                    .Value = customer.FirstName;
                
                cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar))
                    .Value = customer.LastName;
                
                cmd.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar))
                    .Value = customer.Address;
                
                cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar))
                    .Value = customer.City;
                
                cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar))
                    .Value = customer.State;
                
                cmd.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.NVarChar))
                    .Value = customer.ZipCode;

                cn.Open();

                customer.Id = Convert.ToInt32(cmd.ExecuteScalar());
                success = true;

            }
            catch (Exception ex)
            {
                HasErrors = true;
                ExceptionMessage = ex.Message;
                customer.Id = -1;
                success = false;
            }
        }
    }

    return success;
}

person
Karen Payne
  
schedule
24.01.2021