Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab
|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error deleting database

Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first). An
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
John
I think you have to post this in the GP group or contact GP support. It
looks like the database is not there in SQL Server, but somehow GP is
thinking the db is there.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.discussions.microsoft.com> wrote in message
news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first).
An
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
John
|||Thanks, I'll try there first.
John
"Narayana Vyas Kondreddi" wrote:

> I think you have to post this in the GP group or contact GP support. It
> looks like the database is not there in SQL Server, but somehow GP is
> thinking the db is there.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
> Hi,
> We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
> having a problem removing a database that was added (via Gt.Plains first).
> An
> error message telling me that SQL failed to install the database correctly
> appeared, but whenever I try to remove it from Great Plains I get an error,
> even though I'm logged into GP and SQL with full admin rights:
> From Great Plains I get:
> "[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
> sysdatabases for database 'TEST'. No entry found with that name. Make sure
> that the name is entered correctly."
> When clicking OK, the following appears
> "The stored procedure smCreateCompanyDeleteButtonCHG returned the following
> results: DBMS: 911, Great Plains: 0."
> If you click MORE, you get:
> "Please consult the alert message documentation provided by your DBMS."
> So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
> so I've been finding snippets of info on the web, and so far I have tried a
> few things including running the following from Query analyzer:
> SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
> But, that has not shown the rogue database name. But, when you are in
> Gt.Plains the TEST database is listed in the 'logon to' options, even though
> it's not listed anywhere in SQL.
> So, how do I remove all traces of a database (as full systems admin) in
> Gt.PLains and SQL since it failed?
> Many thanks
> John
>
>

Error deleting database

Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first). An
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
JohnI think you have to post this in the GP group or contact GP support. It
looks like the database is not there in SQL Server, but somehow GP is
thinking the db is there.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.discussions.microsoft.com> wrote in message
news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first).
An
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
John|||Thanks, I'll try there first.
John
"Narayana Vyas Kondreddi" wrote:
> I think you have to post this in the GP group or contact GP support. It
> looks like the database is not there in SQL Server, but somehow GP is
> thinking the db is there.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
> Hi,
> We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
> having a problem removing a database that was added (via Gt.Plains first).
> An
> error message telling me that SQL failed to install the database correctly
> appeared, but whenever I try to remove it from Great Plains I get an error,
> even though I'm logged into GP and SQL with full admin rights:
> From Great Plains I get:
> "[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locate entry in
> sysdatabases for database 'TEST'. No entry found with that name. Make sure
> that the name is entered correctly."
> When clicking OK, the following appears
> "The stored procedure smCreateCompanyDeleteButtonCHG returned the following
> results: DBMS: 911, Great Plains: 0."
> If you click MORE, you get:
> "Please consult the alert message documentation provided by your DBMS."
> So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
> so I've been finding snippets of info on the web, and so far I have tried a
> few things including running the following from Query analyzer:
> SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
> But, that has not shown the rogue database name. But, when you are in
> Gt.Plains the TEST database is listed in the 'logon to' options, even though
> it's not listed anywhere in SQL.
> So, how do I remove all traces of a database (as full systems admin) in
> Gt.PLains and SQL since it failed?
> Many thanks
> John
>
>

Error deleting database

Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first). A
n
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locat
e entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
JohnI think you have to post this in the GP group or contact GP support. It
looks like the database is not there in SQL Server, but somehow GP is
thinking the db is there.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.discussions.microsoft.com> wrote in message
news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
Hi,
We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
having a problem removing a database that was added (via Gt.Plains first).
An
error message telling me that SQL failed to install the database correctly
appeared, but whenever I try to remove it from Great Plains I get an error,
even though I'm logged into GP and SQL with full admin rights:
From Great Plains I get:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Could not locat
e entry in
sysdatabases for database 'TEST'. No entry found with that name. Make sure
that the name is entered correctly."
When clicking OK, the following appears
"The stored procedure smCreateCompanyDeleteButtonCHG returned the following
results: DBMS: 911, Great Plains: 0."
If you click MORE, you get:
"Please consult the alert message documentation provided by your DBMS."
So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
so I've been finding snippets of info on the web, and so far I have tried a
few things including running the following from Query analyzer:
SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
But, that has not shown the rogue database name. But, when you are in
Gt.Plains the TEST database is listed in the 'logon to' options, even though
it's not listed anywhere in SQL.
So, how do I remove all traces of a database (as full systems admin) in
Gt.PLains and SQL since it failed?
Many thanks
John|||Thanks, I'll try there first.
John
"Narayana Vyas Kondreddi" wrote:

> I think you have to post this in the GP group or contact GP support. It
> looks like the database is not there in SQL Server, but somehow GP is
> thinking the db is there.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:808D30A3-4F06-4567-89B0-6087D5245AAC@.microsoft.com...
> Hi,
> We currently have Great Plains 7.5 linked to our SQL 2000 server. I'm now
> having a problem removing a database that was added (via Gt.Plains first).
> An
> error message telling me that SQL failed to install the database correctly
> appeared, but whenever I try to remove it from Great Plains I get an error
,
> even though I'm logged into GP and SQL with full admin rights:
> From Great Plains I get:
> "[Microsoft][ODBC SQL Server Driver][SQL Server] Could not loc
ate entry in
> sysdatabases for database 'TEST'. No entry found with that name. Make sure
> that the name is entered correctly."
> When clicking OK, the following appears
> "The stored procedure smCreateCompanyDeleteButtonCHG returned the followin
g
> results: DBMS: 911, Great Plains: 0."
> If you click MORE, you get:
> "Please consult the alert message documentation provided by your DBMS."
> So, I can't remove the TEST database. Now, I'm relatively new to SQL 2000,
> so I've been finding snippets of info on the web, and so far I have tried
a
> few things including running the following from Query analyzer:
> SELECT CATALOG_NAME from INFORMATION_SCHEMA.SCHEMA
> But, that has not shown the rogue database name. But, when you are in
> Gt.Plains the TEST database is listed in the 'logon to' options, even thou
gh
> it's not listed anywhere in SQL.
> So, how do I remove all traces of a database (as full systems admin) in
> Gt.PLains and SQL since it failed?
> Many thanks
> John
>
>sql

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 DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote