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

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?

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.

Error creating programmatically a subscription with a parameter

Hi,
I'm using a script (through rs utility) to automate the creation of a
lot of subscriptions. Everything works fine if I don't use a parameter
in my report, but when I use it I always get the Error Code
"rsInvalidParameters", with the Error Message
"The value of parameter 'Parameters' is not valid. Check the
documentation for information about valid values"
What I'm doing is:
...
Dim parameter As New ParameterValue()
parameter.Name = "storeCod"
parameter.Value = "318"
Dim parameters(1) As ParameterValue
parameters(0) = parameter
then, when I call rs.CreateSubscription(), I pass the "parameters"
array as the last parameter of the method.
It seems weird because if I debug using the GetReportParameters method
to see exactly the parameter name and value, it's right, the name is
storeCod and one of the valid values is "318".
What can be wrong?
Thanks in advance,
Vinicius BellinoTry setting the DefaultValue for the parameter to that value.
--
| From: vbellino@.uol.com.br (Vinicius Bellino)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Error creating programmatically a subscription with a parameter
| Date: 30 Mar 2005 08:43:34 -0800
| Organization: http://groups.google.com
| Lines: 30
| Message-ID: <93d12908.0503300843.7da9f4f5@.posting.google.com>
| NNTP-Posting-Host: 200.215.178.109
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1112201014 31851 127.0.0.1 (30 Mar 2005
16:43:34 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 30 Mar 2005 16:43:34 +0000 (UTC)
| Path:
TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!not-for-mail
| Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46481
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
| I'm using a script (through rs utility) to automate the creation of a
| lot of subscriptions. Everything works fine if I don't use a parameter
| in my report, but when I use it I always get the Error Code
| "rsInvalidParameters", with the Error Message
| "The value of parameter 'Parameters' is not valid. Check the
| documentation for information about valid values"
|
| What I'm doing is:
| ...
| Dim parameter As New ParameterValue()
| parameter.Name = "storeCod"
| parameter.Value = "318"
|
| Dim parameters(1) As ParameterValue
| parameters(0) = parameter
|
| then, when I call rs.CreateSubscription(), I pass the "parameters"
| array as the last parameter of the method.
|
| It seems weird because if I debug using the GetReportParameters method
| to see exactly the parameter name and value, it's right, the name is
| storeCod and one of the valid values is "318".
|
| What can be wrong?
|
| Thanks in advance,
|
| Vinicius Bellino
||||Brad,
If I set the default value, it works, but I need to create
subscriptions with different parameter values for the same Report. I'm
reading a text file that defines which email must receive each
parameter value. This text file is something like:
ReportName;SharedScheduleName;
email1;StoreCode1;
email2;StoreCode2;
email3;StoreCode3;
So, I can't set the default parameter to StoreCode1 then create the
subscription to email1 then set to StoreCode2 and create a new
subscription to email2 because it won't work properly. Both
subscriptions will use the last default value set.
Do you happen to know what is going on? Because I think that setting
parameters programmatically is supposed to work well since it's a
"standard" need.
Thanks,
Vinicius Bellino
bradsy@.Online.Microsoft.com ("Brad Syputa - MS") wrote in message news:<o#jQ82UNFHA.2136@.TK2MSFTNGXA03.phx.gbl>...
> Try setting the DefaultValue for the parameter to that value.
> --
> | From: vbellino@.uol.com.br (Vinicius Bellino)
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Error creating programmatically a subscription with a parameter
> | Date: 30 Mar 2005 08:43:34 -0800
> | Organization: http://groups.google.com
> | Lines: 30
> | Message-ID: <93d12908.0503300843.7da9f4f5@.posting.google.com>
> | NNTP-Posting-Host: 200.215.178.109
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1112201014 31851 127.0.0.1 (30 Mar 2005
> 16:43:34 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Wed, 30 Mar 2005 16:43:34 +0000 (UTC)
> | Path:
> TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
> ne.de!news.glorb.com!postnews.google.com!not-for-mail
> | Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46481
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> |
> | I'm using a script (through rs utility) to automate the creation of a
> | lot of subscriptions. Everything works fine if I don't use a parameter
> | in my report, but when I use it I always get the Error Code
> | "rsInvalidParameters", with the Error Message
> | "The value of parameter 'Parameters' is not valid. Check the
> | documentation for information about valid values"
> |
> | What I'm doing is:
> | ...
> | Dim parameter As New ParameterValue()
> | parameter.Name = "storeCod"
> | parameter.Value = "318"
> |
> | Dim parameters(1) As ParameterValue
> | parameters(0) = parameter
> |
> | then, when I call rs.CreateSubscription(), I pass the "parameters"
> | array as the last parameter of the method.
> |
> | It seems weird because if I debug using the GetReportParameters method
> | to see exactly the parameter name and value, it's right, the name is
> | storeCod and one of the valid values is "318".
> |
> | What can be wrong?
> |
> | Thanks in advance,
> |
> | Vinicius Bellino
> |

Error concatenating string

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@.@.SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep@.job_id = @.JobID,
@.step_id = 1,
@.step_name = N'Import OCC Series Data',
@.command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME,
@.database_name = N'',
@.server = N'',
@.database_user_name = N'',
@.subsystem = N'CmdExec',
@.cmdexec_success_code = 0,
@.flags = 2,
@.retry_attempts = 0,
@.retry_interval = 1,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 3,
@.on_fail_step_id = 0,
@.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME, everything works fine. I even tried declaring a local
variable named @.command and setting it in the select statement, but no
dice."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0402051028.4aa7015e@.posting.google.c om...
> I am trying to create a job that, as one of its steps, will kick off a
> DTS package. As part of the command parameter, I need to concat a
> system variable (@.@.SERVERNAME) to a constant string. I am receiving an
> error about incorrect syntax near the +.
> Here is the code for the job step.
> -- Add the job steps
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
> @.step_id = 1,
> @.step_name = N'Import OCC Series Data',
> @.command = N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME,
> @.database_name = N'',
> @.server = N'',
> @.database_user_name = N'',
> @.subsystem = N'CmdExec',
> @.cmdexec_success_code = 0,
> @.flags = 2,
> @.retry_attempts = 0,
> @.retry_interval = 1,
> @.output_file_name = N'',
> @.on_success_step_id = 0,
> @.on_success_action = 3,
> @.on_fail_step_id = 0,
> @.on_fail_action = 3
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> If I just try SELECT N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME, everything works fine. I even tried declaring a local
> variable named @.command and setting it in the select statement, but no
> dice.

You can't build a parameter value 'dynamically' like that, but assigning the
entire string to a variable first should work:

declare @.mycommand nvarchar(1000)
set @.mycommand = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME

EXECUTE msdb.dbo.sp_add_jobstep
...
@.command = @.mycommand
...

What error did you get when you tried this?

Simon|||I ended up figuring that out and doing exactly what you suggested.
Something I did not know about passing parameters to stored
procedures...

The error I had gotten was
Incorrect syntax at '+'

Thanks for the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error BC30654

I get the following error ... Any advice?
The value expression for the query parameter â'@.JIDsâ' contains an error:
[BC30654] 'Return' statement in a Function or a Get must return a value.
--
Frederick Volking
System Architect
SC Dept of Juvenile JusticeIt looks like the parameter gets its value from a function...
Please post the function code...
--
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
"FlashMerlot" <FlashMerlot@.discussions.microsoft.com> wrote in message
news:5F5D73EC-6F29-4809-A1A2-6506E0B641EA@.microsoft.com...
>I get the following error ... Any advice?
> The value expression for the query parameter '@.JIDs' contains an error:
> [BC30654] 'Return' statement in a Function or a Get must return a value.
> --
> Frederick Volking
> System Architect
> SC Dept of Juvenile Justice

Error at parameter 93 during datastream processing of parameterize

Has anyone seen this error before? It ulimately causes replication to fail.
The error message is:
Error at parameter 93 during datastream processing of parameterized command.
Keino Jason
SQL DBA/Developer
Never seen or heard of this error before. Are you using the same service
pack/hotfix level on each of the servers involved in this replication setup?
If so, and it is >=sp3, can you give some more details on the setup you
have - replication type, which agent fails, at what stage does it fail, what
is the complete error message etc.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Error at parameter 34 during datastream processing of parameterized command.

replication is failing with mssg "Error at parameter 34 during datastream processing of parameterized command." any input what could be the reason for this failure and what action should i take to sort out this issue ?
From http://www.developmentnow.com/g/114_2003_7_22_0_0/sql-server-replication.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Not sure about this message. Have seen it reported a couple of times in the
newsgroup without there ever being a definitive fix (AFAIR). Please can you
tell us all the relevant info: type of replication, sp levels etc. Also do
some logging to try to get a more detailed message.
Rgds,
Paul Ibison