Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problem with using bcp

Hello all,
I try to execute the code below. Whatever I try I get the error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dc_temp_text'.
This also happens when I put the owner and the database name in from of the
table name.
Can anybody tell me what it is that I'm missing?
TIA
Ronald
CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
begin
dbcc checkident (dc_temp_text, reseed, 0)
declare @.BcpCommand varchar(5000)
declare @.TableName varchar(100)
set @.Tablename = QuoteName('dc_temp_text')
/*
Schrijf het text bestand
*/
delete from dbo.dc_temp_text
insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
set @.BcpCommand = 'bcp "select * from '
set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
set @.bcpCommand = @.BcpCommand + '" queryout
c:\testuser1\text.txt -Uuser -Ppassword -c'
exec master..xp_cmdshell @.BcpCommand
endI assume that you have created the stored procedure in the same database
that you have the table dc_temp_text.
If you comment out the BCP portion I am guessing that the proc works
correctly.
I do not see where you specify the database or the server within the BCP
string.
Instead of
exec master..xp_cmdshell @.BcpCommand
Try
SELECT @.BcpCommand AS HereIsTheBCPstatement
If the command is correct you should be able to run it within DOS from your
PC. Use the output of the query to test your BCP statement.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>|||Keith,

> If you comment out the BCP portion I am guessing that the proc works
> correctly.
Yes, ther is no problem then. The table is filled and everything checks out.

> I do not see where you specify the database or the server within the BCP
> string.
I added that a few minutes later with no effect. I also tried to run the
statement in de dos prompt with the same result.
Any other ideas?
Greetings
Ronald
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>I assume that you have created the stored procedure in the same database
>that you have the table dc_temp_text.
> If you comment out the BCP portion I am guessing that the proc works
> correctly.
> I do not see where you specify the database or the server within the BCP
> string.
> Instead of
> exec master..xp_cmdshell @.BcpCommand
> Try
> SELECT @.BcpCommand AS HereIsTheBCPstatement
> If the command is correct you should be able to run it within DOS from
> your PC. Use the output of the query to test your BCP statement.
> --
> Keith Kratochvil
>
> "Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
> news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||>I added that a few minutes later with no effect. I also tried to run the
>statement in de dos prompt with the same result.
Ok, now we have isolated the problem to the BCP statement.
Type BCP into Query Analyzer
Highlight the string BCP
Hit Shift_F1
Read up on BCP overview within Books Online. You will see that you are be
missing a few params.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:%23U3ffCEdGHA.380@.TK2MSFTNGP04.phx.gbl...
> Keith,
>
> Yes, ther is no problem then. The table is filled and everything checks
> out.
>
> I added that a few minutes later with no effect. I also tried to run the
> statement in de dos prompt with the same result.
> Any other ideas?
> Greetings
> Ronald
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
> news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>|||On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:

>Hello all,
>I try to execute the code below. Whatever I try I get the error:
>Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>'dc_temp_text'.
>This also happens when I put the owner and the database name in from of the
>table name.
>Can anybody tell me what it is that I'm missing?
Hi Ronald,
Where do you put the owner and database name in front of the table name?
The execution of bcp will open a new connection to the server that will
connect to the user's default database. It is really important that you
always database-qualify the tablename on the bcp command.
Also, try what happens if yoou replace
exec master..xp_cmdshell @.BcpCommand
with
PRINT @.BcpCommand
Maybe this helps you see the problem in the generated bcp command. And
if not, you can post the output of the PRINT command here and see if
someone else sees a problem with it.
Hugo Kornelis, SQL Server MVP|||Ronald Hermans (rhermans@.datamedicare.nl) writes:
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
Is the server you are running on a default instance? Or is it a named
instance?
Since you do not specify -S the BCP command will connect to the default
instance. You get can the server name from @.@.servername.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Yes It is a Default instance, but just to make sure I entered the the
servername with the -S parameter. Unfortunatly it had no effect.
Ronald
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns97C06E3A3171Yazorman@.127.0.0.1...
> Ronald Hermans (rhermans@.datamedicare.nl) writes:
> Is the server you are running on a default instance? Or is it a named
> instance?
> Since you do not specify -S the BCP command will connect to the default
> instance. You get can the server name from @.@.servername.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo,
bcp "select * from dc_temp_text" queryout
c:\\text.txt -Sservername -Uuser -Ppassword -c
I tried to put the db name and the owner name in front of the table name
with no effect. I still get the same error.
Ronald
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:ulo462drdh8s5rv72pjm9bd4l9v12afjd8@.
4ax.com...
> On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:
>
> Hi Ronald,
> Where do you put the owner and database name in front of the table name?
> The execution of bcp will open a new connection to the server that will
> connect to the user's default database. It is really important that you
> always database-qualify the tablename on the bcp command.
> Also, try what happens if yoou replace
> exec master..xp_cmdshell @.BcpCommand
> with
> PRINT @.BcpCommand
> Maybe this helps you see the problem in the generated bcp command. And
> if not, you can post the output of the PRINT command here and see if
> someone else sees a problem with it.
> --
> Hugo Kornelis, SQL Server MVP|||bcp "select * from [your_db_name_goes_here].[dbo].dc_temp_text" queryout
"c:\text.txt" -Uuser -Ppassword -c
-oj
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OaINpgMdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> bcp "select * from dc_temp_text" queryout
> c:\\text.txt -Sservername -Uuser -Ppassword -c
> I tried to put the db name and the owner name in front of the table name
> with no effect. I still get the same error.
> Ronald
>|||Hello all,
I think I may have found the problem (and if so, I'm going to cry). The
databasename starts with a 1 (one digit) and I think the bcp tool has a
problem with that.
Can anybody confirm this?
TIA
Ronald
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>