Error converting data type varchar to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error converting data type varchar to datetime

Author

Topic

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 08:16:28

Hi all,

Can anyone please tell me how to resolve this issue - I am calling a stored procedure with this statement from an application ;

exec test2 '@NOTES','@SUBJECT','@ActivityID','@CreationTime','@Creator','@DURATION','@contact'

but i keep getting the following error message:

Database call:
exec test2 'Please work','Please alright','03BBBD89-E454-4C',{ts'2008-07-29 13:28:26'},'Segun Palmer','Chula Ofili'

3700[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.(8114) (SyncFolder)

This is the actual procedure:

CREATE procedure test2
@createtime datetime,
@createuser varchar(16),
@duration numeric(10,0),
@subject varchar(50),
@uniqueid varchar(16),
@etable varchar(100),
@utable varchar(100),
@contact varchar(100)

AS
declare @contactid varchar(16)
declare @entityid varchar(16)
declare @creationtime datetime
set @contactid = (select UserID from wce_contact where contact = @createuser)
set @entityid = (select UNIQUEID from wce_contact where contact = @contact)

insert into wce_linkto (LEntityID, LETableName, LUniqueID, LUTableName)

VALUES (@entityid, @etable, @uniqueid, @utable)

insert into wce_activity (UNIQUEID, CREATEUSER, CREATETIME, DURATION, SUBJECT)

VALUES (@uniqueid, @contactid, @createtime, @duration, @subject)

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 08:20:59

the value and the parameter sequence does not match

exec test2 @CreationTime, @Creator, @DURATION, . . .

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-30 : 08:25:19

Drop the ODBC specific function call {ts }...

exec test2 'Please work', 'Please alright', '03BBBD89-E454-4C', '2008-07-29 13:28:26', 'Segun Palmer', 'Chula Ofili'

E 12°55'05.25"
N 56°04'39.16"

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 08:30:47

quote:
Originally posted by khtan

the value and the parameter sequence does not match

exec test2 @CreationTime, @Creator, @DURATION, . . .

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 08:34:06

quote:
Originally posted by tomex1

quote:
Originally posted by khtan

the value and the parameter sequence does not match

exec test2 @CreationTime, @Creator, @DURATION, . . .

KH
[spoiler]Time is always against us[/spoiler]

What is your reply?

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 09:16:25

quote:
Originally posted by khtan

the value and the parameter sequence does not match

exec test2 @CreationTime, @Creator, @DURATION, . . .

KH
[spoiler]Time is always against us[/spoiler]

Hi Khtan,
Thanks that seems to fixed the date issue. You might be able to help me. Basically what I am doing is integrating Microsoft Outlook Calendar using a tool called GeniusConnect with a web based CRM which uses Micrsoft SQL Server as its backend.

Now, the procedure works fine cos when an appointment is scheduled in Outlook it executes the procedure in SQL server. The challenge I have got now is, if a user clicks on the GeniusConnect link (which executes the procedure) twice, I get an error message which is:

Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

From the above message, I reckon it's trying to run the procedure again but I haven't made any room from updating the same activity again.

So my question is, how can I go about this?

Once again, thanks for all your support.

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 09:29:22

quote:
Originally posted by madhivanan

quote:
Originally posted by tomex1

quote:
Originally posted by khtan

the value and the parameter sequence does not match

exec test2 @CreationTime, @Creator, @DURATION, . . .

KH
[spoiler]Time is always against us[/spoiler]

What is your reply?

Madhivanan

Failing to plan is Planning to fail

Hi madhivanan,
Thanks that seems to fixed the date issue. You might be able to help me. Basically what I am doing is integrating Microsoft Outlook Calendar using a tool called GeniusConnect with a web based CRM which uses Micrsoft SQL Server as its backend.

Now, the procedure works fine cos when an appointment is scheduled in Outlook it executes the procedure in SQL server. The challenge I have got now is, if a user clicks on the GeniusConnect link (which executes the procedure) twice, I get an error message which is:

Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

From the above message, I reckon it's trying to run the procedure again but I haven't made any room from updating the same activity again.

So my question is, how can I go about this? Also, in order for the calendar in our CRM to display the Creation Time from Outlook it has to be in this format 2008-07-28 00:00:00.000.

The format that comes from Outlook is 2008-07-30 13:55:21.000. So how do I make the time in the Outlook date 00:00:00.000 as our CRM doesn't like the time bit in the date?

Once again, thanks for all your support.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 09:43:41

quote:
Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

As what the error message says you cannot update the view [activity_view] as the view is a query joining more than 1 table. You will have to update the base table of the views or create an update trigger on the view to perform the necessary update to the base tables.

quote:
So my question is, how can I go about this? Also, in order for the calendar in our CRM to display the Creation Time from Outlook it has to be in this format 2008-07-28 00:00:00.000.

The format that comes from Outlook is 2008-07-30 13:55:21.000. So how do I make the time in the Outlook date 00:00:00.000 as our CRM doesn't like the time bit in the date?

select dateadd(day, datediff(day, 0, datecol), 0)

KH
[spoiler]Time is always against us[/spoiler]

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 11:08:13

quote:
Originally posted by khtan

quote:
Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

As what the error message says you cannot update the view [activity_view] as the view is a query joining more than 1 table. You will have to update the base table of the views or create an update trigger on the view to perform the necessary update to the base tables.

quote:
So my question is, how can I go about this? Also, in order for the calendar in our CRM to display the Creation Time from Outlook it has to be in this format 2008-07-28 00:00:00.000.

The format that comes from Outlook is 2008-07-30 13:55:21.000. So how do I make the time in the Outlook date 00:00:00.000 as our CRM doesn't like the time bit in the date?

select dateadd(day, datediff(day, 0, datecol), 0)

KH
[spoiler]Time is always against us[/spoiler]

tomex1
Starting Member

47 Posts

Posted - 2008-07-30 : 11:10:05

quote:
Originally posted by khtan

quote:
Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

As what the error message says you cannot update the view [activity_view] as the view is a query joining more than 1 table. You will have to update the base table of the views or create an update trigger on the view to perform the necessary update to the base tables.

quote:
So my question is, how can I go about this? Also, in order for the calendar in our CRM to display the Creation Time from Outlook it has to be in this format 2008-07-28 00:00:00.000.

The format that comes from Outlook is 2008-07-30 13:55:21.000. So how do I make the time in the Outlook date 00:00:00.000 as our CRM doesn't like the time bit in the date?

select dateadd(day, datediff(day, 0, datecol), 0)

KH
[spoiler]Time is always against us[/spoiler]

What are the implications of triggers? Hope it won't slow down the system process on the server?

tomex1
Starting Member

47 Posts

Posted - 2008-07-31 : 06:37:26

quote:
Originally posted by tomex1

quote:
Originally posted by khtan

quote:
Database call:
UPDATE "dbo"."activity_view" SET "contact"='Chula Ofili',"Creator"='Segun Palmer',
"SUBJECT"='Gotomeeting2',"NOTES"='some notes'WHERE
"ActivityID"='9A38EA19-B134-4F'

37000 [Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.activity_view' is not updatable because the modification affects
multiple base tables. (4405) (SyncFolder)

As what the error message says you cannot update the view [activity_view] as the view is a query joining more than 1 table. You will have to update the base table of the views or create an update trigger on the view to perform the necessary update to the base tables.

quote:
So my question is, how can I go about this? Also, in order for the calendar in our CRM to display the Creation Time from Outlook it has to be in this format 2008-07-28 00:00:00.000.

The format that comes from Outlook is 2008-07-30 13:55:21.000. So how do I make the time in the Outlook date 00:00:00.000 as our CRM doesn't like the time bit in the date?

select dateadd(day, datediff(day, 0, datecol), 0)

KH
[spoiler]Time is always against us[/spoiler]

What are the implications of triggers? Hope it won't slow down the system process on the server?

Hi madhivanan,
Talking about the triggers, I reckon I'll need to declare some variables just like I did with the views right?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources