Error converting Varchar to Int - SQL 2000

Hello, I'm having problems with this piece of Transact SQL. What I
need to do is to get a date 2 months ahead of an entered date.
However, I am not supposed to use this: @.dt+60 (in order to get 60 days
exactly). Consequently, if I've entered '12/21/05' as the original
date, I need to get the 2 month date as '2/21/06' regardless if they're
60 days or not. As you can see, the only thing that the new date will
be changed is going to be the month. Thus, the dates would convert as
follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
for the months 11 and 12, which in that case would be 1 and 2 of the
following year.
This is what I have so far.
DECLARE @.dt DATETIME
DECLARE @.2dt VARCHAR
SET @.dt = '12/14/06'
SET @.2dt =
CASE
WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)
END
print @.2dt
When I test it in the query analyzer, I get the this error: Syntax
error converting the varchar value '12/' to a column of data type int.
I tried to use the CAST and CONVERT function, it did not work. Perhaps
I was doing it wrong.
Any help would be appreciated.Doesn't this work for you?
DECLARE @.dt DATETIME
DECLARE @.2dt datetime
SET @.dt = '12/14/06'
SET @.2dt = dateadd(m,2,@.dt)
print convert(varchar,@.2dt)
http://sqlservercode.blogspot.com/|||Gosh! Thank you! This is exactly what I was looking for! You're a
genius!
I had this code as part of an Access query that I needed to convert to
SQL 2000 and it was driving me crazy. I'm so glad SQL has a function
that does this.
Thanks again!
JR
SQL wrote:
> Doesn't this work for you?
> DECLARE @.dt DATETIME
> DECLARE @.2dt datetime
> SET @.dt = '12/14/06'
> SET @.2dt = dateadd(m,2,@.dt)
> print convert(varchar,@.2dt)
>
> http://sqlservercode.blogspot.com/|||note: the reason for the error is that the '&' operator in sql is the
bitwise AND operator - not a string concatenation operator
so it was trying to convert '12/' to an int before performing the
bitwise AND.
ILCSP@.NETZERO.NET wrote:
> Hello, I'm having problems with this piece of Transact SQL. What I
> need to do is to get a date 2 months ahead of an entered date.
> However, I am not supposed to use this: @.dt+60 (in order to get 60 days
> exactly). Consequently, if I've entered '12/21/05' as the original
> date, I need to get the 2 month date as '2/21/06' regardless if they're
> 60 days or not. As you can see, the only thing that the new date will
> be changed is going to be the month. Thus, the dates would convert as
> follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
> for the months 11 and 12, which in that case would be 1 and 2 of the
> following year.
> This is what I have so far.
> DECLARE @.dt DATETIME
> DECLARE @.2dt VARCHAR
> SET @.dt = '12/14/06'
> SET @.2dt =
> CASE
> WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)
> END
>
> print @.2dt
>
> When I test it in the query analyzer, I get the this error: Syntax
> error converting the varchar value '12/' to a column of data type int.
> I tried to use the CAST and CONVERT function, it did not work. Perhaps
> I was doing it wrong.
> Any help would be appreciated.
>

error converting nvarchar to int

I have this stored procedure and am getting errors.
I filtered on Info = 'I' which should only have numeric values. I have some
info with Info = 'L' that does have alpha code.
I have tried the case below but am still having problems.
Any help appreciated
CREATE PROCEDURE NTF_UpdateInfoAfterPost
@.orderNo varchar(30),
@.rUser int
AS
--Used to delete the Info messages from all users.
--Typically used before creating a new info message
update Notify
Set
signed = 4,
rUser = @.rUser,
DateSigned = GetDate()
where (Info = 'I' and Info is not null) and orderNo = Case When
isNumeric(@.OrderNo) = 1 then @.OrderNo Else 0 End
Stephen K. MiyasatoHi
Try
update Notify
Set
signed = 4,
rUser = @.rUser,
DateSigned = GetDate()
where (Info = 'I' and Info is not null) and orderNo = Case When
isNumeric(@.OrderNo) = 1 then @.OrderNo Else '0' End
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:%23IxODu4gGHA.1792@.TK2MSFTNGP03.phx.gbl...
>
> I have this stored procedure and am getting errors.
> I filtered on Info = 'I' which should only have numeric values. I have
> some info with Info = 'L' that does have alpha code.
> I have tried the case below but am still having problems.
> Any help appreciated
>
> CREATE PROCEDURE NTF_UpdateInfoAfterPost
> @.orderNo varchar(30),
> @.rUser int
> AS
> --Used to delete the Info messages from all users.
> --Typically used before creating a new info message
> update Notify
> Set
> signed = 4,
> rUser = @.rUser,
> DateSigned = GetDate()
> where (Info = 'I' and Info is not null) and orderNo = Case When
> isNumeric(@.OrderNo) = 1 then @.OrderNo Else 0 End
> Stephen K. Miyasato
>

Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitleVARCHAR(255)= NULL,
@.AbstractTextTEXT,
@.WebAddressVARCHAR(255)= NULL,
@.AbstractTypeVARCHAR(15)= NULL,
@.PresentChoiceVARCHAR(15)= NULL,
@.SessionChoice1VARCHAR(7)= NULL,
@.SessionChoice2VARCHAR(7)= NULL,
@.Keyword1VARCHAR(50)= NULL,
@.Keyword2VARCHAR(50)= NULL,
@.Keyword3VARCHAR(50)= NULL,
@.Keyword4VARCHAR(50)= NULL,
@.StudentType VARCHAR(15)= NULL,
@.Judge VARCHAR(3)= NULL,
@.Author1FirstInitialVARCHAR(1)= NULL,
@.Author1MIVARCHAR(1)= NULL,
@.Author1LastNameVARCHAR(50)= NULL,
@.Author1OrganizationVARCHAR(100)= NULL,
@.Author1CityVARCHAR(50)= NULL,
@.Author1StateVARCHAR(25)= NULL,
@.Author1CountryVARCHAR(35)= NULL,
@.Author1EmailVARCHAR(75)= NULL,
@.Author1PresentsVARCHAR(3)= NULL,
@.RegIDINT,
@.AbstractIDintoutput
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?
I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by position and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "positioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition for the parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to in
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it is
> indeed of datatype INT.
> So what am I missing?
|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas

"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by position and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "positioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition for the parameter.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to in
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it is
> indeed of datatype INT.
> So what am I missing?|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting
data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100)
= NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by p
osition and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "po
sitioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition
for the parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following erro
r:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error convertin
g data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to i
n
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it i
s
> indeed of datatype INT.
> So what am I missing?|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting
data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to int.

can anyone see as to why I would get this error with the following SP?

ALTER PROCEDURE [dbo].[SP]@.IDint = 0,@.emailFromVARCHAR(50) =Null,@.emailDateVARCHAR(50) =Null,@.emailSubjVARCHAR(50) =Null,@.emailTxtBodyVARCHAR(1000) =Null,@.emailHtmlBodyVARCHAR(1000) =NullAS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;DECLARE @.NotesVARCHAR (8000)DECLARE @.TicketIDINTDECLARE @.emailBodyVARCHAR (1000)DECLARE @.LengthIntSET @.Notes =''SET @.Length =LEN(@.emailSubj)-- insert a new entryINSERT INTO PopEmail ( emailFrom, emailDate, emailSubj, emailTxtBody, emailHtmlBody )VALUES ( @.emailFrom, @.emailDate, @.emailSubj, @.emailTxtBody, @.emailHtmlBody )-- get the new IDSET @.ID =@.@.identityIf ( @.ID <> 0 )AND (ISNUMERIC(@.emailSubj) = 1 )BeginIF @.emailTxtBodyISNULLBEGINSet @.emailBody = @.emailHtmlBodyPRINT'@.emailHtmlBody: ' + @.emailBodyENDELSEBEGINSET @.emailBody = @.emailTxtBodyPRINT'@.emailTxtBody: ' + @.emailBodyENDSET @.TicketID =CAST( @.emailSubjAS int )SET @.Notes = @.emailFrom +', ' + @.emailDate +', ' + @.emailBodySelect @.ID = IDFrom TicketDetailsWhere TicketDetails.TicketID = @.TicketIDExec differentSP @.ID, @.TicketID, @.NotesPRINT'Subject: [' + @.emailSubj +']'print'length: ' +CAST(@.Lengthas varchar (10))Print'emailSubj: ' +CAST( @.emailSubjAS int )PRINT'ID: ' +Cast( @.IDas varchar ( 10 ) )PRINT'TicketID: ' +Cast( @.TicketIDAS Varchar ( 10 ) )PRINT'Notes: ' + @.NotesPRINT'ID: ' +Cast( @.IDas varchar ( 10 ) )ENDELSEBEGINPrint'ID: ' +CAST(@.IDAS VarChar(10))PRINT'ISNUMERIC: ' +CAST(ISNUMERIC(@.emailSubj)AS VarChar (10))PRINT'Subject: [' + @.emailSubj +']'END

Which line caused this error? I found at least this line was wrong:

Print'emailSubj: ' +CAST( @.emailSubjAS int )

Should be

Print'emailSubj: ' +CAST( @.emailSubjAS Varchar(10) )

|||

I found that I am just new and prone to making mistakes... The SP that I am calling has several variables that it is looking for, and the @.Notes variable is not until the end of the list. So my call of the SP should look like this:

EXEC someSP @.ID, @.TicketID, , , , , @.Notes.

Now she works... ahhhh the joys of learning.

Thanks,

CK

'Error converting data type varchar to int' : Solution

Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.

This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.

This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.

It also occurs if the datatypes in your sql file do not match those of the original stored procedure.

You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:

Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.

Then go to File>Open>Projects>(save sql file). Click open.

View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.

Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.

Hi,

Thanks that you come back to the forums and want to give something of your solution to your actual problem back.

But I don′t know what your actual problem was (as you did not mentioned it clearly). Perhaps we could summarize this a bit more like Problem: xxxx, Solution: xxx. But from the information given I cannot tell what your actual problem was. I can′t even see why you do all the saves and deletes :-) ?

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

Error converting data type nvarchar to int.

With the stored procedure below if i do
DECLARE @.ProductName nvarchar(40),@.ProductID int
EXEC updateProduct
@.ProductName = ProductName,
@.ProductID = ProductID
why do i get error:- Error converting data type nvarchar to int.
---
ALTER PROCEDURE updateProduct
@.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
AS
UPDATE
Products
SET
ProductName = @.ProductName
WHERE
ProductID = @.ProductID AND
LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT > 0
-- This statement is used to update the DataSet if changes are done on the
updated record (identities, timestamps or triggers )
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
FROM Products
WHERE ProductID = @.ProductID
GOHi Patrick,
The order in which you pass the parameters looks wrong.
Just try it this way:
EXEC updateProduct
@.ProductID = ProductID,
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Patrick.O.Ige" wrote:

> With the stored procedure below if i do
> DECLARE @.ProductName nvarchar(40),@.ProductID int
> EXEC updateProduct
> @.ProductName = ProductName,
> @.ProductID = ProductID
> why do i get error:- Error converting data type nvarchar to int.
>
> ---
> ALTER PROCEDURE updateProduct
> @.ProductID int,
> @.ProductName nvarchar(40),
> @.LastUpdate datetime
> AS
> UPDATE
> Products
> SET
> ProductName = @.ProductName
> WHERE
> ProductID = @.ProductID AND
> LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT > 0
> -- This statement is used to update the DataSet if changes are done on t
he
> updated record (identities, timestamps or triggers )
> SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> FROM Products
> WHERE ProductID = @.ProductID
> GO|||Thx but if i do :-
DECLARE @.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
EXEC updateProduct
@.ProductID = ProductID,
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
It still gives the error...
I want results in the Query Analyzer!
"Chandra" wrote:
> Hi Patrick,
> The order in which you pass the parameters looks wrong.
> Just try it this way:
> EXEC updateProduct
> @.ProductID = ProductID,
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>|||Then try this way
EXEC updateProduct
@.ProductID = CAST(ProductID AS INTEGER),
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Patrick.O.Ige" wrote:
> Thx but if i do :-
> DECLARE @.ProductID int,
> @.ProductName nvarchar(40),
> @.LastUpdate datetime
> EXEC updateProduct
> @.ProductID = ProductID,
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
> It still gives the error...
> I want results in the Query Analyzer!
> "Chandra" wrote:
>|||There are a number of problems with your script. The primary problem is
that you are about how to declare and use local variables as the
arguments for a stored procedure, as well as the correct syntax to use for
executing a stored procedure. The second
problem is that you must supply values for all stored procedure arguments
that do not have defaults. Try the script below.
set nocount on
go
use Northwind
go
create PROCEDURE mytest
@.ProductID int,
@.ProductName nvarchar(40),
@.LastUpdate datetime
AS
if @.ProductID is not null
select * from Products where ProductID = @.ProductID
else
select * from Products where ProductName = @.ProductName
go
DECLARE @.ProductName nvarchar(40),@.ProductID int
EXEC mytest
@.ProductName = ProductName,
@.ProductID = ProductID
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID ,
@.LastUpdate = null
set @.ProductID = 2
EXEC mytest
@.ProductName = @.ProductName,
@.ProductID = @.ProductID ,
@.LastUpdate = null
go
drop PROCEDURE mytest
go|||This is not only pointless, it also will not work. Both you and OP have
failed to declare the variables ProductID, ProductName, and LastUpdate; all
three of these names are not valid variable names. Assuming the variables
were declared and used correctly, both @.ProductIDs (the variable and the
argument) are already defined as integer - the cast is pointless.
Lastly, the order of the arguments in the execute statement is **only**
important when the argument names are not used.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B7FD86EC-8A71-473A-A817-1CBB7B351EAA@.microsoft.com...
> Then try this way
> EXEC updateProduct
> @.ProductID = CAST(ProductID AS INTEGER),
> @.ProductName = ProductName,
> @.LastUpdate = LastUpdate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>
done on the|||On Thu, 12 May 2005 20:33:02 -0700, Patrick.O.Ige wrote:

>With the stored procedure below if i do
>DECLARE @.ProductName nvarchar(40),@.ProductID int
>EXEC updateProduct
>@.ProductName = ProductName,
>@.ProductID = ProductID
>why do i get error:- Error converting data type nvarchar to int.
(snip)
Hi Patrick,
In most cases, things like ProductID and ProdcutName (without preceding
@. and not enclosed in quotation marks) would refer to either table or
column names. But since you can't use either in an EXEC command, SQL
Server simply assumes that you omitted the quotation marks, but wanted
to write a string constant nonetheless. Run the following code to prove
this:
create proc test (@.a varchar(20))
as
select @.a
go
-- With quotes
exec test 'This is a test'
go
-- No quotes - but they are "assumed"
exec test SecondTest
go
-- It only works for single words
exec test Third try
go
drop proc test
go
So obviously, the value 'ProductID' is passed as value for the
@.ProductID parameter to your proc - and since @.ProductID is declared as
integer, SQL Server will attempt to conert it, and fail.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx guys for the replies
*** Sent via Developersdex http://www.examnotes.net ***

error converting data type int to tiny int

While trying to create a new database maintenance plan in SQL 2000 sp3, I'm
receiving the error "error converting data type int to tiny int". No matter
what I select in the plan, the same error appears. I have a bare bone backu
p
of 1 database to disk, and receive the error. I've not been able to find
anything, except for a few people having the same sort of problem, who are
running more than 1 instance of sql on their server, as is the case with me.
thank you,I don't know why the error but if you are simply doing a backup why not
create a scheduled job yourself and forget about the Maintenance plan?
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
> While trying to create a new database maintenance plan in SQL 2000 sp3,
> I'm
> receiving the error "error converting data type int to tiny int". No
> matter
> what I select in the plan, the same error appears. I have a bare bone
> backup
> of 1 database to disk, and receive the error. I've not been able to find
> anything, except for a few people having the same sort of problem, who are
> running more than 1 instance of sql on their server, as is the case with
> me.
> thank you,
>|||Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or from
Management Backup?
"Andrew J. Kelly" wrote:

> I don't know why the error but if you are simply doing a backup why not
> create a scheduled job yourself and forget about the Maintenance plan?
> --
> Andrew J. Kelly SQL MVP
>
> "Bill" <Bill@.discussions.microsoft.com> wrote in message
> news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
>
>|||Yes under Management, SQL agent, Jobs, you can right click and add a new
job. Then in the Jobstep you place the TSQL BACKUP DATABASE xxx command.
You can set a schedule as you see fit.
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:4F8344E5-0A46-499F-9A2C-6B263EB9F23C@.microsoft.com...[vbcol=seagreen]
> Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or
> from
> Management Backup?
>
> "Andrew J. Kelly" wrote:
>

error converting data type int to tiny int

While trying to create a new database maintenance plan in SQL 2000 sp3, I'm
receiving the error "error converting data type int to tiny int". No matter
what I select in the plan, the same error appears. I have a bare bone backup
of 1 database to disk, and receive the error. I've not been able to find
anything, except for a few people having the same sort of problem, who are
running more than 1 instance of sql on their server, as is the case with me.
thank you,I don't know why the error but if you are simply doing a backup why not
create a scheduled job yourself and forget about the Maintenance plan?
--
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
> While trying to create a new database maintenance plan in SQL 2000 sp3,
> I'm
> receiving the error "error converting data type int to tiny int". No
> matter
> what I select in the plan, the same error appears. I have a bare bone
> backup
> of 1 database to disk, and receive the error. I've not been able to find
> anything, except for a few people having the same sort of problem, who are
> running more than 1 instance of sql on their server, as is the case with
> me.
> thank you,
>|||Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or from
Management Backup?
"Andrew J. Kelly" wrote:
> I don't know why the error but if you are simply doing a backup why not
> create a scheduled job yourself and forget about the Maintenance plan?
> --
> Andrew J. Kelly SQL MVP
>
> "Bill" <Bill@.discussions.microsoft.com> wrote in message
> news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
> > While trying to create a new database maintenance plan in SQL 2000 sp3,
> > I'm
> > receiving the error "error converting data type int to tiny int". No
> > matter
> > what I select in the plan, the same error appears. I have a bare bone
> > backup
> > of 1 database to disk, and receive the error. I've not been able to find
> > anything, except for a few people having the same sort of problem, who are
> > running more than 1 instance of sql on their server, as is the case with
> > me.
> >
> > thank you,
> >
>
>|||Yes under Management, SQL agent, Jobs, you can right click and add a new
job. Then in the Jobstep you place the TSQL BACKUP DATABASE xxx command.
You can set a schedule as you see fit.
--
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:4F8344E5-0A46-499F-9A2C-6B263EB9F23C@.microsoft.com...
> Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or
> from
> Management Backup?
>
> "Andrew J. Kelly" wrote:
>> I don't know why the error but if you are simply doing a backup why not
>> create a scheduled job yourself and forget about the Maintenance plan?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Bill" <Bill@.discussions.microsoft.com> wrote in message
>> news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
>> > While trying to create a new database maintenance plan in SQL 2000 sp3,
>> > I'm
>> > receiving the error "error converting data type int to tiny int". No
>> > matter
>> > what I select in the plan, the same error appears. I have a bare bone
>> > backup
>> > of 1 database to disk, and receive the error. I've not been able to
>> > find
>> > anything, except for a few people having the same sort of problem, who
>> > are
>> > running more than 1 instance of sql on their server, as is the case
>> > with
>> > me.
>> >
>> > thank you,
>> >
>>

error converting data type int to tiny int

While trying to create a new database maintenance plan in SQL 2000 sp3, I'm
receiving the error "error converting data type int to tiny int". No matter
what I select in the plan, the same error appears. I have a bare bone backup
of 1 database to disk, and receive the error. I've not been able to find
anything, except for a few people having the same sort of problem, who are
running more than 1 instance of sql on their server, as is the case with me.
thank you,
I don't know why the error but if you are simply doing a backup why not
create a scheduled job yourself and forget about the Maintenance plan?
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
> While trying to create a new database maintenance plan in SQL 2000 sp3,
> I'm
> receiving the error "error converting data type int to tiny int". No
> matter
> what I select in the plan, the same error appears. I have a bare bone
> backup
> of 1 database to disk, and receive the error. I've not been able to find
> anything, except for a few people having the same sort of problem, who are
> running more than 1 instance of sql on their server, as is the case with
> me.
> thank you,
>
|||Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or from
Management Backup?
"Andrew J. Kelly" wrote:

> I don't know why the error but if you are simply doing a backup why not
> create a scheduled job yourself and forget about the Maintenance plan?
> --
> Andrew J. Kelly SQL MVP
>
> "Bill" <Bill@.discussions.microsoft.com> wrote in message
> news:2F56B630-C272-4125-A5D0-08ADE9E284A8@.microsoft.com...
>
>
|||Yes under Management, SQL agent, Jobs, you can right click and add a new
job. Then in the Jobstep you place the TSQL BACKUP DATABASE xxx command.
You can set a schedule as you see fit.
Andrew J. Kelly SQL MVP
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:4F8344E5-0A46-499F-9A2C-6B263EB9F23C@.microsoft.com...[vbcol=seagreen]
> Do you mean use a T-SQL statement under Management, SQL agent, Jobs, or
> from
> Management Backup?
>
> "Andrew J. Kelly" wrote: