exec master..xp_sendmail error converting character string to smalldatetime
-
martes, 01 de mayo de 2012 11:58
Hi,
I need to send an attachment to myself via xp.sendmail.
exec master..xp_sendmail @recipients = 'test', @query = 'SELECT * FROM db.dbo.table WHERE ([ID Not In (SELECT * FROM db.dbo.Table1)) and ([Date] >= CONVERT(DATETIME, ''2007-11-01 00:00:00'', 102)) AND ([Date2] < '' @Date2 ''), @subject = 'SQL Server Repor', @message = 'testing:',
the error i get is
ODBC error 295 (22007) Syntax error converting character string to smalldatetime data type.
if i just type the dat it works, it only fails when trying to pass a paramater @Date2
SQL2000
thanks
Todas las respuestas
-
martes, 01 de mayo de 2012 12:05Usuario que responde
I have not used previously xp_sendemail but in order to successful running this query you need to build dynamic sql , something like
declare @sql nvarchar(1000),@orderid int
select @orderid=10248
set @sql = 'select * from Northwind..Orders where OrderID= @ordid'
exec sp_executesql @sql, N'@ordid int',@orderidBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
-
martes, 01 de mayo de 2012 12:06What value are you assigning @Date2? What datatype is @Date2? What datatype is db.dbo.Table1.Date2 (I'm guessing small datetime)
Chuck
-
martes, 01 de mayo de 2012 12:10
declare @Date2 datetime set @Date2 = '2012-04-01'
-
martes, 01 de mayo de 2012 12:18
The resulting query is:
SELECT *
FROM db.dbo.table
WHERE ([ID Not In (SELECT * FROM db.dbo.Table1)) and ([Date] >= CONVERT(DATETIME, '2007-11-01 00:00:00', 102)) AND ([Date2] < ' @Date2 ')It's a surpise that the string ' @Date2 ' cannot be converted to smalldatetime. You have a variable called @Date2 somewhere, but here you have only a string starting with a space followed by an @-sign.
You would need:
SELECT @query =
'SELECT *
FROM db.dbo.table
WHERE ([ID Not In (SELECT * FROM db.dbo.Table1))
and ([Date] >= ''2007111'')
AND ([Date2] < ''' + convert(char(8), @Date2, 112) + ''''And then pass @query to xp_sendmail.
By the way, I use the format YYYYYMMDD as this format is safe and always interpreted the same.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Propuesto como respuesta Naomi NMicrosoft Community Contributor, Moderator martes, 01 de mayo de 2012 13:14
-
martes, 01 de mayo de 2012 12:43
i did use select @query than i got this error:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
-
martes, 01 de mayo de 2012 13:15ModeradorDeclare @Query as nvarchar(max)
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
martes, 01 de mayo de 2012 13:26
i did and the error i got is:
Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'max'
-
martes, 01 de mayo de 2012 13:38Maybe you should post the full script you are running - otherwise it might be difficult to guess where the error is.
Steen Schlüter Persson (DK)
-
martes, 01 de mayo de 2012 13:46
When i run this:
exec sp_executesql @Query , N'@date2 nvarchar(4000),@date2
it works, but when i try to put the exec sp_executesql in
exec master..xp_sendmail @recipients = 'test', @query = 'exec sp_executesql ''@sql'', ''@Date2 nvarchar(4000)'', ''@Date2''' ,
i get the error Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
-
martes, 01 de mayo de 2012 14:47
This is what i have done so far:
DECLARE @QuerySQL Varchar(200) SET @QuerySQL = 'exec DB.dbo.SendMail @Date2, @Name, @Email' exec master.dbo.xp_sendmail @recipients='Test ', @message='Test:', @subject='Test', @width=175, @query=@QuerySQL END GO
THe problem is when i try to pass the parameters to the SendMail SP.
when i exe the above code i get :
ODBC error 137 (42000) Must declare the variable '@TopupDate'.
when i add ''@Date2'' i get
ODBC error 295 (22007) Syntax error converting character string to smalldatetime data type.
-
martes, 01 de mayo de 2012 14:52Moderador
Erland already answered your question.
Try
declare @Query nvarchar(4000) SELECT @query = 'SELECT * FROM db.dbo.table WHERE ([ID Not In (SELECT * FROM db.dbo.Table1)) and ([Date] >= ''2007111'') AND ([Date2] < ''' + convert(char(8), @Date2, 112) + ''''
This should work.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
martes, 01 de mayo de 2012 15:04Naomi it still tels me
Msg 137, Level 15, State 2, Line 8
Must declare the variable '@Date2'.
-
martes, 01 de mayo de 2012 15:18ModeradorI assume that you do have @Date2 variable defined outside. Can you post your whole code now, it will be easier to debug the whole thing.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
martes, 01 de mayo de 2012 15:55
Declare @sql as nvarchar(4000);
declare @TopupDate nvarchar(50);
declare @Account nvarchar(50);
set @Account='111'
SET @TopupDate='2012-04-01 00:00:00'
exec master..xp_sendmail @recipients = 'test',
@query = 'SELECT *
FROM DB.dbo.Table
WHERE ([ID] Not In (SELECT * FROM DB.dbo.Table2)) and ([Date] >= CONVERT(DATETIME, ''2007-11-01 00:00:00'', 102)) AND ([Date2] < convert(datetime, ''@Date2'', 102)) AND (date1 >= CONVERT(DATETIME, ''2007-09-01 00:00:00'', 102)) and ([Account] = @Account) AND ([cost] = 0.00) and [Code] Not in (Select table2 From DB.dbo.CODES) ',
@subject = 'SQL Server Report 2ND',
@message = 'test:',
@attach_results = 'TRUE', @width = 250 -
martes, 01 de mayo de 2012 16:03Moderador
Try:
Declare @sql as nvarchar(4000); declare @TopupDate varchar(8); declare @Account nvarchar(50); set @Account='''111''' SET @TopupDate='''20120401''' set @sql = 'SELECT * FROM DB.dbo.Table WHERE [ID] Not In (SELECT ID FROM DB.dbo.Table2) and [Date] >= ''20071101'' AND [Date2] < ' + @TopupDate + ' AND date1 >= ''20070901''
and [Account] = ' + @Account + ' AND [cost] = 0.00 and
[Code] Not in (Select table2 From DB.dbo.CODES)' -- table2 - strange name for a column print @sql -- to test execute(@sql) -- to verify it works, comment once you got it working /* Only after verification comment the above and uncomment this exec master..xp_sendmail @recipients = 'test', @query = @sql, @subject = 'SQL Server Report 2ND', @message = 'test:', @attach_results = 'TRUE', @width = 250 */
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
- Editado Naomi NMicrosoft Community Contributor, Moderator martes, 01 de mayo de 2012 16:05
- Marcado como respuesta deti miércoles, 02 de mayo de 2012 13:56
-
miércoles, 02 de mayo de 2012 11:09
Hi Naomi,
when i run the above query it prints ok with no errors but when i try to execute @sql i get the following error:
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '20070901'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark before the character string ' AND [cost] = 0.00 and [Code] Not in (Select Codes From DB.dbo.CODES)'.
-
miércoles, 02 de mayo de 2012 11:22
Hi Can you try with following query
declare @Date2 varchar(20) set @Date2=Convert(Varchar(20),GETDATE(),120) declare @query1 varchar(2000) declare @query varchar(500) set @query1 = 'SELECT * FROM db.dbo.table WHERE ([ID Not In (SELECT * FROM db.dbo.Table1)) and [Date] >= '''+@Date2+''' AND [Date2] < '''+@Date2+''')' print @query1 exec master..xp_sendmail @recipients = 'test', @query=@query1, @subject = 'SQL Server Repor', @message = 'testing:'
Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com -
miércoles, 02 de mayo de 2012 13:24ModeradorPost the result of print @SQL in order for us to debug the problem in case you can not figure it out yourself.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
miércoles, 02 de mayo de 2012 13:55
Thank you all for your replyes,
to overcome the problem i was having i inserted the results i needet in to a tep table then just query that temp teble in @query.
Thanks again
- Marcado como respuesta deti miércoles, 02 de mayo de 2012 13:56
.png)
