Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regards
You can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
....
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>
|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Thursday, March 29, 2012
error executing sprocedure
Hello,
I've created one stored procedure that has one input parameter(number of days), if i don=B4t pass any value when executing this procedure sql reports one error. I would like to execute this procedure and don't pass the input paramenter to the stored procedure but if the number of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
--
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in which case you don't >have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message >news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards >
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regardssql
I've created one stored procedure that has one input parameter(number of days), if i don=B4t pass any value when executing this procedure sql reports one error. I would like to execute this procedure and don't pass the input paramenter to the stored procedure but if the number of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
--
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in which case you don't >have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message >news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards >
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regardssql
error executing sprocedure
Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
Tuesday, March 27, 2012
Error executing extended stored procedure: Invalid Parameter
When I try to connect to sql server instance I received this error:
TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
when i click refresh button i am able to see my schema, but i can't see the
properties of my base and any table properties. I receive error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
--
BUTTONS:
OK
--
also the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
locked and can not be opened.
After a reboot the key is now readable and the 2005 instance can be
connected to without these messages.
But after a while it does it again.
does anyone have any idea what is going wrong?Hi
My guess is that you have some group policy or Anti-virus/ISD system that is
locking down this registry key.
John
"nikolakg" wrote:
> When I try to connect to sql server instance I received this error:
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> when i click refresh button i am able to see my schema, but i can't see the
> properties of my base and any table properties. I receive error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> --
> BUTTONS:
> OK
> --
>
> also the registry key
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
> locked and can not be opened.
> After a reboot the key is now readable and the 2005 instance can be
> connected to without these messages.
> But after a while it does it again.
> does anyone have any idea what is going wrong?
TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
when i click refresh button i am able to see my schema, but i can't see the
properties of my base and any table properties. I receive error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
--
BUTTONS:
OK
--
also the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
locked and can not be opened.
After a reboot the key is now readable and the 2005 instance can be
connected to without these messages.
But after a while it does it again.
does anyone have any idea what is going wrong?Hi
My guess is that you have some group policy or Anti-virus/ISD system that is
locking down this registry key.
John
"nikolakg" wrote:
> When I try to connect to sql server instance I received this error:
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> when i click refresh button i am able to see my schema, but i can't see the
> properties of my base and any table properties. I receive error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> --
> BUTTONS:
> OK
> --
>
> also the registry key
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
> locked and can not be opened.
> After a reboot the key is now readable and the 2005 instance can be
> connected to without these messages.
> But after a while it does it again.
> does anyone have any idea what is going wrong?
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
Hello All,
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
Matt
If the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp
See http://www.databasejournal.com/featu...le.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
Matt
If the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp
See http://www.databasejournal.com/featu...le.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
Hello All,
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
MattIf the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
sz_8sdm.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
See http://www.databasejournal.com/feat...cle.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
MattIf the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
sz_8sdm.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
See http://www.databasejournal.com/feat...cle.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
Hello All,
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
MattIf the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
See http://www.databasejournal.com/features/mssql/article.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>sql
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
MattIf the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
See http://www.databasejournal.com/features/mssql/article.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>sql
error execute sp_addlinkedserver in stored procedure
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
Hello,
What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?
From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.
If you have no joy, can you post the entire sp code?
Cheers
Rob
|||The stored procedure:
ALTER PROCEDURE [dbo].[LoadConvertsDB]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create Linked Server
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
--StockTable
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];
SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable
--More Tables here
-- Drop linked server
EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
END
The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.
1. If the linked server is already the system, this sp will work.
2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.
3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.
I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.
Thanks!
|||Did you find the solution for your error.
I am also stuck with the similar kind of error
|||
Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.
When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.
So the solution is:
1. Create a linked server by using sp_addlinkedserver
2. Declare @.SQL varchar(1000)
3. SET @.SQL = 'SELECT * FROM ......'
4. EXEC (@.SQL)
You won't get error when complie the stored procedure and execute it.
error execute sp_addlinkedserver in stored procedure
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
Hello,
What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?
From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.
If you have no joy, can you post the entire sp code?
Cheers
Rob
|||The stored procedure:
ALTER PROCEDURE [dbo].[LoadConvertsDB]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create Linked Server
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
--StockTable
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];
SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable
--More Tables here
-- Drop linked server
EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
END
The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.
1. If the linked server is already the system, this sp will work.
2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.
3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.
I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.
Thanks!
|||Did you find the solution for your error.
I am also stuck with the similar kind of error
|||Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.
When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.
So the solution is:
1. Create a linked server by using sp_addlinkedserver
2. Declare @.SQL varchar(1000)
3. SET @.SQL = 'SELECT * FROM ......'
4. EXEC (@.SQL)
You won't get error when complie the stored procedure and execute it.
error execute sp_addlinkedserver in stored procedure
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
Hello,
What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?
From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.
If you have no joy, can you post the entire sp code?
Cheers
Rob
|||The stored procedure:
ALTER PROCEDURE [dbo].[LoadConvertsDB]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create Linked Server
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';
--StockTable
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];
SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable
--More Tables here
-- Drop linked server
EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';
END
The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.
1. If the linked server is already the system, this sp will work.
2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.
3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.
I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.
Thanks!
|||Did you find the solution for your error.
I am also stuck with the similar kind of error
|||
Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.
When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.
So the solution is:
1. Create a linked server by using sp_addlinkedserver
2. Declare @.SQL varchar(1000)
3. SET @.SQL = 'SELECT * FROM ......'
4. EXEC (@.SQL)
You won't get error when complie the stored procedure and execute it.
Monday, March 26, 2012
Error during Execute sp with IMAGE parameter
Hello.
It is the first time that Im using IMAGE field type..
I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@.OutImg IMAGE declared as an output parameter of the procedure.)
(Select @.OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.
Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:ofrom 2005 BOL:
text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.
So you'll have to return this in a record set, not in an OUT param.
It is the first time that Im using IMAGE field type..
I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@.OutImg IMAGE declared as an output parameter of the procedure.)
(Select @.OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.
Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:ofrom 2005 BOL:
text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.
So you'll have to return this in a record set, not in an OUT param.
Thursday, March 22, 2012
Error deploying managed stored proc
When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later
I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
Try the simple code to get a .dll file:
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SampleSP(string connstring, out int spid)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT @.@.SPID", conn);
spid = (System.Int16)cmd.ExecuteScalar();
}
}
}
And then let's manually create assmebly from the dll file in Management Studio:
1. Login to SQL2005 as 'sa', locate a database->new query->execute following command:
alter database yourdb set trustworthy on
go
sp_configure 'clr enabled',1
reconfigure with override
go
2. create an assembly from the dll file
3. create a stored procedure which references the assembly:
create procedure testCLR @.conn nvarchar(max),@.i int output as
external name ClassLibrary2.[StoredProcedures].SampleSP
4. Test the procedure:
declare @.i int
exec testCLR 'Data Source=.;Integrated Security=SSPI;Database=master',@.i output
select @.i
And here is a good torturial for using CLR in SQL2005:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp
Error deploying a CLR Stored Procedure that uses a web service
Ok, first some background.
I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.
This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.
At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:
<code>
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, S
</code>
Anyone have any ideas?
Thanks!
Dave Borneman
Solution Architect,
anyWare Mobile Solutions.
OK, so when you are using Web services the .NET framework will dynamically generate an assembly based on your proxy code. Dynamic assembly generation is not allowed inside SQL Server (SQLCLR).What you need to do is to pre-generate the proxy assembly by using the sgen tool and deploy that generated assembly into the database.
Look at this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74480&SiteID=1 and specifically the 6:th message in that thread how to achieve the pre-generation.
Niels
Monday, March 19, 2012
Error create a second independent transaction inside a CLR stored procedure?
I use the context connection for the "normal" work in the CLR procedure.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.
What is the best way to do this inside a CLR procedure?
Thanx a lot
Hi!
Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).
|||Thank you,I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block
Any idea about that?
I will try to build a small example an post it here.|||
Hello,
I made a small example to reproduce the error.
CLR procedure
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Test(ByVal sConnectString As String)
Dim t As SqlClient.SqlTransaction = Nothing
Dim c As SqlClient.SqlConnection = Nothing
c = New SqlClient.SqlConnection(sConnectString)
c.Open()
t = c.BeginTransaction()
t.Commit()
c.Close()
End Sub
T-SQL caller:
begin tran
execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
commit tran
Error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
System.Data.SqlClient.SqlException: Transaction context in use by another session.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
Hi!
Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.
More on "Enlist" option is in doc on SqlConnection.ConnectionString property
|||Than you!
We've been stuck for a week. We had a .Net CLR stored procedure that worked locally but, failed when invoked remotely. That connection string setting is exactly what we needed.
-Joe
Sunday, March 11, 2012
error converting varchar to numeric
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion...
thanksNever tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind.
Walkthrough: Debugging Hello World, a SQL Stored Procedure|||hi MMS
I was able to isolate the line tht was causing the problem..however i will go through the article to see if it will help with some info for future use.
thanks.
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 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?
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?
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?
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?
Subscribe to:
Posts (Atom)