Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Чем дальше в лес, тем толще партизаны.
Ситуация: есть некий MS SQL Server 2000, в нем есть база, в ней таблица F, хранимая процедура H и две вьюшки A и B.
Есть программа, которая на сервер отправляет запрос, который вызывает хранимую процедуру, которая вызывает вьюшки.
Эта программа, а следовательно запрос, процедура и вьюшки прекрасно себя чувствовали до ноября месяца, но, сейчас запрос перестал работать.
Выяснилось: Запрос передает в процедуру 4 параметра: дату начала операции, дату конца операции, параметр отвечающий за сортировку и параметр отвечающий за выбор по какой-то конкретной компании.
Сейчас эта процедура работает при некоторых значениях даты начала и конца операции, а при некоторых выдает ошибку
автор
Сообщение 242, уровень 16, состояние 3, процедура H, строка 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


Гугленье привело к возможным вариантам решения проблемы:
1) возможно перепутан месяц и день при разборе передаваемой даты, нужно установить
dateformat DMY
или сделать
Convert
на передающихся данных чтобы они были в формате MDY
( dateformat добавляла в хранимую процедуру, но не помогло. С convertом вообще ничего не получилось, как только я переставила день и месяц с помощью него местами, так запрос стал работать неправильно. кроме сервер корректно ищет по некоторым другим датам с идентичным переданным параметром, отличающимся только числом, да и переданный параметр находится в виде близком к mdy, а не к dmy)
2)возможно был изменен язык для учетных записей на сервере, попробовать установить другой
(изменила для sa язык, под которым проверяла работоспособность программы, с английского на русский, но это вообще никак не сказалось на выполнении запроса)
3) возможно неправильно записались некоторые даты в саму таблицу F. я посмотрела, но не нашла в том поле в разных записях (на которых запрос работает и на которых нет) хотя бы какой-то разницы в виде даты.

Что удалось выяснить:
1) на сервер параметры даты-времени передаются как строка вида
'Nov 1 2011 12:00:00:000AM'
На сервере, в поле по которому производится сравнение, дата-время хранится например так:
2011-11-1 23:28:00.000
Я проверяла потом корректно ли сервер переводит время из той строки, которую получает, в формат даты-времени, получила параметр типа datetime такого же вида как и поле по которому идет сравнение.
2) Если вызывать хранимую процедуру с теми же параметрами на самом сервере, то я получаю ту же ошибку.
Но, если вызвать не хранимую процедуру (которая вызывает вьюшки), а просто создать запрос с selectом к таблице F, таким же как и select в хранимой процедуре, на котором (видимо строка 5 как я поняла из сообщения об ошибке) происходит ошибка, то никаких ошибок нет.

выглядит этот select в процедуре так:
select * into #t from вьюшка_A  where FTime>=@datebeg and FTime<@dateend
- есть ошибка.
мой запрос для проверки выглядит так:
select * from таблица_F  where FTime>=@datebeg and FTime<@dateend
- нет ошибки.
(параметры передаю одни и те же, например 'Nov 10 2011 12:00:00:000AM' и 'Nov 11 2011 12:00:00:000AM' для даты начала и конца операции соответственно)

Помогите разобраться пожалуйста, я уже двое суток ничего понять не могу, а пользователи сделать свою работу ;(

Если нужны тексты хранимой процедуры и вьюшек, то могу привести, только они несколько ээ.. запутанные.
2 дек 11, 04:31    [11693350]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Sack
Member

Откуда:
Сообщений: 163
pharmakis,
Почитайте, про передачу параметра типа дата-время как строковой константы https://www.sql.ru/faq/faq_topic.aspx?fid=109
2 дек 11, 05:31    [11693355]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Sack,
я читала это, несколько раз, прежде чем задать вопрос я поискала и по форуму и вообще в интернете. Давайте предположим что я не могу ничего поменять в программе, которая выдает запрос серверу (а фактически так оно и есть, исходники есть только не те и кривые, компилироваться они не желают), могу только хранимую процедуру или вьюшки изменить.
Convert, как я уже говорила, я использовала (перепробовала и dmy и mdy), либо вообще запрос выдает какую-то фигню, перепутав месяц и день, либо всё остается по-старому, где-то работает, а где-то нет.
SET DATEFORMAT просто не дает никаких результатов.

В любом случае тогда не понятно, почему строка
'Nov 11 2011 12:00:00:000AM' обрабатывается нормально
а строка
'Nov 10 2011 12:00:00:000AM' нет.
Не может же SQL-сервер их по-разному интерпретировать сам по себе как ему вздумается..
Ну и "работало же раньше!" на том же сервере с теми же настройками.
2 дек 11, 05:47    [11693358]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
aleks2
Guest
pharmakis
SET DATEFORMAT просто не дает никаких результатов.


Канешно не даст. Надо ишо LANGUAGE

declare @s varchar(100)

set @s='Nov 11 2011 12:00:00:000AM' 

set LANGUAGE english
select cast(@s as datetime)

set LANGUAGE russian
select cast(@s as datetime)

set LANGUAGE 'Simplified Chinese'
select cast(@s as datetime)
2 дек 11, 06:11    [11693369]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
aleks2,
я пробовала установить язык для пользователя - мне не помогло. сейчас в хранимую процедуру пробовала запихнуть то, что вы посоветовали, ну то есть сначала русский попробовала, потом английский, все так же не работает.
2 дек 11, 08:36    [11693520]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
pharmakis
сейчас в хранимую процедуру пробовала запихнуть то, что вы посоветовали, ну то есть сначала русский попробовала, потом английский, все так же не работает.
В процедуру передаётся строка?

Тогда convert 100% поможет и будет работать надёжно.

С языком хуже играть - мало ли, чего там поменяют админы, что и как будут устанавливать и настраивать... Хотя это простое и быстрое решение, если конечно помогает :-)
2 дек 11, 08:44    [11693549]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
alexeyvg
С языком хуже играть
Хотя да, раз дата передаётся словами, то выхода нет :-(

Ну, на этот раз при переписывании приложения нужно будет всё таки взять нормальных программистов :-)
2 дек 11, 08:46    [11693556]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
alexeyvg,

автор
Тогда convert 100% поможет и будет работать надёжно.

автор
Хотя да, раз дата передаётся словами, то выхода нет :-(

то есть convert мне всё же не поможет?
*я пробовала, но мало ли, вдруг руки кривые просто..

меня больше всего настораживает то, что не работает она всего на 8 днях в месяце, причем каких попало. на остальных работает.
2 дек 11, 09:06    [11693612]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
toly_m
Member

Откуда:
Сообщений: 89
А рядом с sql 2000 2005/2008-й устанавливали? Или хотя бы cisual studio?
Есть предположение, что драйверы подключения обновились/изменились.
2 дек 11, 09:28    [11693673]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Можно показать как именно параметры заходят в процедуру.
Можно подсмотреть через профайлер.

Т.к. не очень ясно что за слова.
2 дек 11, 09:33    [11693687]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
toly_m,
мм.. на той же машине нет 2005 Sqlя, но у нас настроен linked Server один, как раз был 2000 сервер, а стал 2005 там, туда сторонней программой перекачиваются данные с 2000.

Volochkova,
не совсем поняла что вы хотите видеть. под профайлером я как раз и посмотрела что там творится, вот например что происходит при нажатии на кнопку в программе

--пример неработающего запроса

exec sp_executesql N'exec PDSA_FlightInfo @P1,@P2,@P3,@P4

', N'@P1 datetime,@P2 datetime,@P3 varchar(4),@P4 varchar(3)', 'Nov 3 2011 12:00:00:000AM', 'Nov 3 2011 12:00:00:000AM', 'time', ''


--пример работающего запроса

exec sp_executesql N'exec PDSA_FlightInfo @P1,@P2,@P3,@P4

', N'@P1 datetime,@P2 datetime,@P3 varchar(4),@P4 varchar(3)', 'Nov 1 2011 12:00:00:000AM', 'Nov 1 2011 12:00:00:000AM', 'time', ''
2 дек 11, 09:44    [11693746]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
pharmakis
exec sp_executesql N'exec PDSA_FlightInfo @P1,@P2,@P3,@P4

', N'@P1 datetime,@P2 datetime,@P3 varchar(4),@P4 varchar(3)', 'Nov 1 2011 12:00:00:000AM', 'Nov 1 2011 12:00:00:000AM', 'time', ''

здесь не стоит пугаться того, что два раза передается одинаковая дата, потому что хранимая процедура в начале самом прибавляет к @dateend ровно 1 день. я проверяла, действительно добавляет и действительно куда надо.. более того когда я включила это добавление дня в прямой запрос к таблице_F то, этот запрос так же нормально сработал.
2 дек 11, 09:49    [11693772]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Пардон..
Все поняла...
2 дек 11, 09:50    [11693783]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Glory
Member

Откуда:
Сообщений: 104751
А параметер у процедуры PDSA_FlightInfo разумеется datetime ?
2 дек 11, 09:51    [11693787]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Glory,

разумеется..

Volochkova,

нет проблем, главное понять что это и как его решать..
2 дек 11, 09:56    [11693815]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Такой тогда вопрос.
А параметры в процедуре как идут?
Create procedure .... AS и вот переменные идут как varchar или datetime

@datebeg и @dateend - это datetime или vacrhar?
2 дек 11, 09:59    [11693829]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Если параметры на входе уже datetime то ошибка должна была бы быть в 1 строке...
Или параметры перечисляются один параметр в строке?

Declare
...
@d1 smalldatetime,
@d2 smalldatetime,
@d3 ...
As
2 дек 11, 10:03    [11693855]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Glory
Member

Откуда:
Сообщений: 104751
pharmakis
Glory,

разумеется..


Ну так конвертация тогда происходит на строке вызова
где у вас уствновка dateformat ДО вызова процедуры то ?

Сообщение было отредактировано: 2 дек 11, 10:04
2 дек 11, 10:04    [11693859]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Volochkova,

начинается она вот так:

ALTER PROCEDURE [dbo].[PDSA_FlightInfo] @datebeg datetime,@dateend datetime,@sort char(4),@ac char(3)

AS

begin

  set @dateEnd=dateadd(dd,1,@dateEnd)

  select * into #t from вьюшка_А where FTime>=@datebeg and FTime<@dateEnd


то есть @datebeg @dateend в процедуре datetime
2 дек 11, 10:05    [11693865]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Тогда у Вас видимо данные в табличке нормальные, а вот View пытается из какого то поля сделать дату вот и ошибка.
2 дек 11, 10:10    [11693896]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Volochkova,

нене, там до ALTER Table есть несколько строк. я только сейчас поняла что вы имели ввиду *sorry*

USE [isg]

GO

/****** Объект:  StoredProcedure [dbo].[PDSA_FlightInfo]    Дата сценария: 11/30/2011 11:12:02 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[PDSA_FlightInfo] @datebeg datetime,@dateend datetime,@sort char(4),@ac char(3)

AS

begin

  set @dateEnd=dateadd(dd,1,@dateEnd)




Glory,

я если честно не совсем поняла. В оригинальной процедуре нет dateformat, но даже когда я пишу dateformat до ALTER PROCEDURE, то ничего не меняется, всё так же на некоторых датах не работает. Причем, я уже говорила, я проверяла.. вот как:

declare @dateEnd datetime, @datebeg datetime
set @datebeg='Nov  10 2011 12:00:00:000AM' 
set @dateEnd='Nov  10 2011 12:00:00:000AM'
set @dateEnd=dateadd(dd,1,@dateEnd)
SELECT * from таблица_F Where ftimev<=CAST(@dateEnd AS DATETIME) and ftimev>=CAST(@datebeg AS DATETIME)


где таблица_F это та же таблица с которой работает вьюшка, которая вызывается из хранимой процедуры PDSA_FlightInfo.
2 дек 11, 10:14    [11693923]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
А почему вы проверяете селектом по табличке, а не по VIEW?
И при чем тут алтеры?
2 дек 11, 10:23    [11693968]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Glory
Member

Откуда:
Сообщений: 104751
pharmakis
я если честно не совсем поняла. В оригинальной процедуре нет dateformat, но даже когда я пишу dateformat до ALTER PROCEDURE, то ничего не меняется, всё так же на некоторых датах не работает. Причем, я уже говорила, я проверяла.. вот как:


Конвертирование символьной строки в дату происходит в момент вызова процедуры. Т.е. _снаружи_ процедуры. Потому что входной параметер у вас уже дата
Поэтому все ваши настройки и конвертации _внутри_ процедуры никак не помогут. Потому что до их выполнения очередь просто не доходит
Поймите это
2 дек 11, 10:30    [11694022]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
pharmakis
Member

Откуда:
Сообщений: 16
Volochkova,

потому что по view я пробовала, оно не работает. ну такой же запрос точно как тот, который по таблице_F, только по view, ругается точно так же как при вызове хранимой процедуры.
а вот по таблице запрос прекрасно работает, несмотря на то что ему приходится переконвертировать строку в datetime и в том и в том случае.

Если это проблемы с view, то такой вопрос, как понять что не так во view? есть какое-нибудь средство мониторинга чтоб хотя
определить место в тексте view на которой оно перестает выполняться?

эм.. а я если честно не знаю причем тут они, но хранимая процедура выглядит именно так (когда и кем она писалась я даже подумать боюсь, описания нет, спросить не у кого)
USE [isg]

GO

/****** Объект:  StoredProcedure [dbo].[PDSA_FlightInfo]    Дата сценария: 11/30/2011 11:12:02 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[PDSA_FlightInfo] @datebeg datetime,@dateend datetime,@sort char(4),@ac char(3)

AS

begin

  set @dateEnd=dateadd(dd,1,@dateEnd)


-- и т.д.
2 дек 11, 10:31    [11694031]     Ответить | Цитировать Сообщить модератору
 Re: Перестала работать хранимая процедура, ошибка 242  [new]
Glory
Member

Откуда:
Сообщений: 104751
pharmakis
Причем, я уже говорила, я проверяла.. вот как:

declare @dateEnd datetime, @datebeg datetime
set @datebeg='Nov  10 2011 12:00:00:000AM' 
set @dateEnd='Nov  10 2011 12:00:00:000AM'
set @dateEnd=dateadd(dd,1,@dateEnd)
SELECT * from таблица_F Where ftimev<=CAST(@dateEnd AS DATETIME) and ftimev>=CAST(@datebeg AS DATETIME)



А зачем @dateEnd типа datetime еще раз преобразовывать к datetime в запросе ?
По-моему у вас полное непонимание, когда и как происходит преобразование типов
2 дек 11, 10:32    [11694035]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить