Помогите пожалуйста отладить запрос.
SELECT
сМестаХранения.Code as СкладКод,
сМестаХранения.Descr as Склад,
сСотрудники.Code as ПродавецКод,
сСотрудники.Descr as Продавец,
0,
ДокС3.sp288 * 1 as Сумма
FROM
dt151 as ДокС3
INNER JOIN
dh151 as ДокШ3 ON ДокШ3.IDDoc = ДокС3.IDDoc
INNER JOIN
_1SJourn as Жур ON Жур.IDDoc = ДокС3.IDDoc
AND Жур.Date_Time_IDDoc BETWEEN ‘20011001’ AND ‘20011031Z’
AND Жур.Closed & 1 = 1
LEFT JOIN
sc65 as сСотрудники
ON сСотрудники.ID = ДокШ3.sp2784
LEFT JOIN
sc32 as сМестаХранения
ON сМестаХранения.ID = ДокШ3.sp139
LEFT JOIN
sc77 as сТовар
ON сТовар.ID = ДокС3.sp141
LEFT JOIN
sc77 as сТоварР
ON сТоварР.ID = сТовар.ParentID
LEFT JOIN
sc77 as сТоварРР
ON сТоварРР.ID = сТоварР.ParentID
WHERE
сТовар.сТоварР.Code = ‘24970’
OR сТовар.сТоварРР.Code = ‘24970’
State 42S22, native 207, message [Microsoft][ODBC SQL Server Driver][SQL Server]Недопустимое имя столбца «сТоварР».
Currently running into an issue on a customer QA environment for software we wrote and distributed. I am not given access to their system so the only information I can get is what server logs I am able to glean from their overworked and overstressed sys admins. For political reasons I have to get this right the first time. All eyes are on me.
In our internal test environment we are running SQLServer version 10.50.1600. The application is a Java web application using Hibernate 3.5.5 + c3p0 0.9. We recently had to add a database column to fix a bug, it is basically a boolean flag that signifies deletion. Here is the column that I added to the table. This one-line script was part of the deployment package that was delivered to the client.
alter table foo.bar add expired tinyint not null default 0;
I added the following Hibernate mapping to the application:
<property name="expired" type="boolean">
<column name="expired" precision="1" scale="0" not-null="true" />
</property>
Unit tested, code reviewed, integration tested, QA accepted in-house, packaged and delivered to client. Client correctly applied updates and provided screenshots proving such. The column exists. Application fails with the following exception in the logs:
2012-03-09 14:50:18,374 WARNING [org.hibernate.util.JDBCExceptionReporter] (ajp-####) SQL Error: 207, SQLState: 42S22
2012-03-09 14:50:18,374 SEVERE [org.hibernate.util.JDBCExceptionReporter] (ajp-####) Invalid column name ‘expired’.
The only discernable difference between the two environments is that they are running SQLServer version 10.0.4000. Their DBA’s may have also tinkered it in other ways that they haven’t told me. Do you see a connection here or have any other advice about what the problem may be? Could this be a character set problem?
Currently running into an issue on a customer QA environment for software we wrote and distributed. I am not given access to their system so the only information I can get is what server logs I am able to glean from their overworked and overstressed sys admins. For political reasons I have to get this right the first time. All eyes are on me.
In our internal test environment we are running SQLServer version 10.50.1600. The application is a Java web application using Hibernate 3.5.5 + c3p0 0.9. We recently had to add a database column to fix a bug, it is basically a boolean flag that signifies deletion. Here is the column that I added to the table. This one-line script was part of the deployment package that was delivered to the client.
alter table foo.bar add expired tinyint not null default 0;
I added the following Hibernate mapping to the application:
<property name="expired" type="boolean">
<column name="expired" precision="1" scale="0" not-null="true" />
</property>
Unit tested, code reviewed, integration tested, QA accepted in-house, packaged and delivered to client. Client correctly applied updates and provided screenshots proving such. The column exists. Application fails with the following exception in the logs:
2012-03-09 14:50:18,374 WARNING [org.hibernate.util.JDBCExceptionReporter] (ajp-####) SQL Error: 207, SQLState: 42S22
2012-03-09 14:50:18,374 SEVERE [org.hibernate.util.JDBCExceptionReporter] (ajp-####) Invalid column name ‘expired’.
The only discernable difference between the two environments is that they are running SQLServer version 10.0.4000. Their DBA’s may have also tinkered it in other ways that they haven’t told me. Do you see a connection here or have any other advice about what the problem may be? Could this be a character set problem?
According to Wikipedia this syntax looks correct…
INSERT INTO dbo.metadata_type ("name", "publishable")
VALUES
("Content Owner", 0),
("Content Coordinator", 0),
("Writer", 0),
("Content Type", 0),
("State", 1),
("Business Segment", 0),
("Audience", 0),
("Product Life Cycle Stage", 0),
("Category", 0),
("Template", 0)
I’m getting errors. I’ve tried wrapping the column names in ` but that didn’t work either…
Error code 207, SQL state 42S22: Invalid column name ‘Content Owner’.
Error code 207, SQL state 42S22: Invalid column name ‘Content Coordinator’.
Error code 207, SQL state 42S22: Invalid column name ‘Writer’.
Error code 207, SQL state 42S22: Invalid column name ‘Content Type’.
Error code 207, SQL state 42S22: Invalid column name ‘State’.
marc_s
721k173 gold badges1320 silver badges1442 bronze badges
asked Mar 27, 2012 at 17:03
1
In SQL Server, string values are delimited by ', not".
Also, column names should either be enclosed in square brackets, or left as they are (if they don’t contain spaces).
Your query should, therefore, look like this:
INSERT INTO dbo.metadata_type (name, publishable) VALUES
('Content Owner', 0),
('Content Coordinator', 0),
('Writer', 0),
('Content Type', 0),
('State', 1),
('Business Segment', 0),
('Audience', 0),
('Product Life Cycle Stage', 0),
('Category', 0),
('Template', 0)
answered Mar 27, 2012 at 17:05
Cristian LupascuCristian Lupascu
38.3k15 gold badges97 silver badges137 bronze badges
You must use Single quotes instead of double quotes for your values and no quotes at all to specify which column to insert:
INSERT INTO dbo.metadata_type (name, publishable) VALUES
('Content Owner', 0),
('Content Coordinator', 0),
('Writer', 0),
('Content Type', 0),
('State', 1),
('Business Segment', 0),
('Audience', 0),
('Product Life Cycle Stage', 0),
('Category', 0),
('Template', 0)
answered Mar 27, 2012 at 17:08
Francis PFrancis P
13.1k2 gold badges26 silver badges50 bronze badges
The ‘Error 207’ of sql is related to the incorrect column name of the table. I seems that you are trying to retrieve the data about the column name which doesn’t exist in the specified table. I suggest you to make sure that your are using correct column name in you query. Also check the table name is correct mentioned in query or not. Try this and let me know if you are still getting same error or not.
if you are trying to insert values to a Varchar using «» try to use simple »
like:
INSERT INTO dbo.metadata_type (name, publishable) VALUES
('Content Owner', 0),
('Content Coordinator', 0),
('Writer', 0),
('Content Type', 0),
('State', 1),
('Business Segment', 0),
('Audience', 0),
('Product Life Cycle Stage', 0),
('Category', 0),
('Template', 0)
answered Mar 27, 2012 at 17:09
2
If you’re looking to insert multiple rows with one insert statement, here’s another way to do it
INSERT INTO dbo.metadata_type (name, publishable)
SELECT 'Content Owner', 0
UNION ALL
SELECT 'Content Coordinator', 0
UNION ALL
and so on
answered Mar 27, 2012 at 17:10
Chetter HumminChetter Hummin
6,5678 gold badges31 silver badges44 bronze badges
В настоящее время возникает проблема с клиентской средой контроля качества для программного обеспечения, которое мы написали и распространяли. Мне не предоставлен доступ к их системе, поэтому единственная информация, которую я могу получить, — это журналы серверов, которые я могу почерпнуть из их перегруженных и перегруженных системных администраторов. По политическим причинам я должен сделать это правильно с первого раза. Все взгляды обращены на меня.
В нашей внутренней тестовой среде мы запускаем SQLServer версии 10.50.1600. Приложение представляет собой веб-приложение Java, использующее Hibernate 3.5.5 + c3p0 0.9. Недавно нам пришлось добавить столбец базы данных, чтобы исправить ошибку, это в основном логический флаг, обозначающий удаление. Вот столбец, который я добавил в таблицу. Этот однострочный сценарий был частью пакета развертывания, доставленного клиенту.
alter table foo.bar add expired tinyint not null default 0;
Я добавил в приложение следующее сопоставление Hibernate:
<property name="expired" type="boolean">
<column name="expired" precision="1" scale="0" not-null="true" />
</property>
Модульное тестирование, проверка кода, проверка интеграции, QA принято внутри компании, упаковано и доставлено клиенту. Клиент правильно установил обновления и предоставил подтверждающие скриншоты. Столбец существует. Приложение не работает со следующим исключением в журналах:
2012-03-09 14: 50: 18,374 ПРЕДУПРЕЖДЕНИЕ [org.hibernate.util.JDBCExceptionReporter] (ajp — ####) Ошибка SQL: 207, SQLState: 42S22 2012-03-09 14: 50: 18,374 СЕРЬЕЗНАЯ [org. hibernate.util.JDBCExceptionReporter] (ajp — ####) Недействительное имя столбца «просрочено».
Единственное заметное различие между этими двумя средами заключается в том, что они работают под управлением SQLServer версии 10.0.4000. Их администраторы баз данных, возможно, также переделали это другими способами, о которых они мне не сказали. Вы видите здесь связь или можете посоветовать, в чем может заключаться проблема? Может ли это быть проблема с набором символов?