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 procedure. Show all posts
Showing posts with label procedure. 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 installation of sql server 2005
I have sql server 2000 installed on my PC. Now it is giving following error during installation of sql server 2005.
"The procedure entry point _vswprintf_c_l could not be located in the dynamic link
library MSVCR80.dll."
What can be wrong?I would suggest posting this to SQL Server Setup & Upgrade forum for faster response.
Thanks,
-Vineet Rao
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.
Error during configuring distribution wizard
TITLE: Configure Distribution Wizard
An error occurred configuring SQL Server Agent.
ADDITIONAL INFORMATION:
SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476
How do I solve this?
Thanks,
Frank
System Engineer
I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".
Allen
|||Please execute
The following shows the available options
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;
The following enables the Agent XPs
EXEC sp_configure 'Agent XPs', '1';
|||Hi,
Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.
pls see below
TITLE: Microsoft SQL Server 2005 Setup
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
BUTTONS:
&Retry
Cancel
Thanks
|||SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.
Exactly how are you attempting to configure it as a distributor?
From http://msdn2.microsoft.com/en-us/library/ms151819.aspx
SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.
|||try this :
Change the system date to cureent day
Error during configuring distribution wizard
TITLE: Configure Distribution Wizard
An error occurred configuring SQL Server Agent.
ADDITIONAL INFORMATION:
SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476
How do I solve this?
Thanks,
Frank
System Engineer
I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".
Allen
|||Please execute
The following shows the available options
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;
The following enables the Agent XPs
EXEC sp_configure 'Agent XPs', '1';
|||
Hi,
Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.
pls see below
TITLE: Microsoft SQL Server 2005 Setup
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
BUTTONS:
&Retry
Cancel
Thanks
|||SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.
Exactly how are you attempting to configure it as a distributor?
From http://msdn2.microsoft.com/en-us/library/ms151819.aspx
SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber. |||try this :
Change the system date to cureent day
sqlError during configuring distribution wizard
TITLE: Configure Distribution Wizard
An error occurred configuring SQL Server Agent.
ADDITIONAL INFORMATION:
SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476
How do I solve this?
Thanks,
Frank
System Engineer
I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".
Allen
|||Please execute
The following shows the available options
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;
The following enables the Agent XPs
EXEC sp_configure 'Agent XPs', '1';
|||Hi,
Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.
pls see below
TITLE: Microsoft SQL Server 2005 Setup
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
BUTTONS:
&Retry
Cancel
Thanks
|||SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.
Exactly how are you attempting to configure it as a distributor?
From http://msdn2.microsoft.com/en-us/library/ms151819.aspx
SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.
|||try this :
Change the system date to cureent day
Error during configuring distribution wizard
TITLE: Configure Distribution Wizard
An error occurred configuring SQL Server Agent.
ADDITIONAL INFORMATION:
SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476
How do I solve this?
Thanks,
Frank
System Engineer
I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".
Allen
|||Please execute
The following shows the available options
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;
The following enables the Agent XPs
EXEC sp_configure 'Agent XPs', '1';
|||
Hi,
Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.
pls see below
TITLE: Microsoft SQL Server 2005 Setup
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
BUTTONS:
&Retry
Cancel
Thanks
|||SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.
Exactly how are you attempting to configure it as a distributor?
From http://msdn2.microsoft.com/en-us/library/ms151819.aspx
SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber. |||try this :
Change the system date to cureent day
Thursday, March 22, 2012
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
Wednesday, March 21, 2012
error creating named calculation
I'm new to MS DM and am using the book "Data Mining with SQL Server 2005" by Wiley. On page 95 I try to follow the procedure for creating a named calculation, but I get this error: IErrorInfo.GetDescription failed with E_FAIL(0x80004005). Any ideas what's wrong?Can you provide some more details? Are you using Access or SQL as your source database?|||Jamie, Thanks for the help. I'm using the Access MovieClick DB that I downloaded from the Wiley web site. Also, I can create other named calculations just fine, such as adding two columns, or taking the log of one. It just seems to be the CASE examples that give me the error.|||You need to import the data into a SQL Server database using the instructions in the text before using the named calculations.|||Thanks! For those not wanting to convert the Access DB to SQL Server, you can use the Access code =IIF([Num Bedrooms] = 1, 'One',IIF([Num Bedrooms] <=3, 'Two or three',IIF([Num Bedrooms] >= 4, 'Four or more')))sql
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.
Subscribe to:
Comments (Atom)