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 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

Error executing Backup

Hello,
I've configure "HP Omniback" to perform backups on=20
databases servers under the account "omni_acc", when i=20
configured this account i assign this account the "System=20
Administrators" Server Role because i can=B4t did backups if=20
the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other=20
previleges since we have the "db_backupoperator" database=20
role but... i cant do the backups if the user is only=20
assign to this role. Can anybody explain to me this=20
situation?
Is not supposed that a user assign to=20
the "db_backupoperator" database role perform backups and=20
restores to databases?
How can i give permissions to a user to make backups and=20
restores of databases?
Best regards
For RESTORE you cannot use db_backup operator if the database doesn't exist, as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the software (HP).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i cant did backups if
the account wasnt assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards

Error creating Push subscription

Hi All,
I've had this problem for about 4 weeks, and I cna't seem
to find a solution! I'm trying to create a puch
subscription but get the following error when EM tries to
create the records:
FHQRY: Error 515: Cannot insert the value NULL into
column 'step_name', table 'msdb.dbo.sysjobsteps'; column
does not allow nulls. INSERT fails.
I'm running SQL 2000 sp3a on Win 2k sp4. Also I've updated
to MDAC 2.8. I've tried using the stored procs but still
get the same error.
Any suggestions as to why this is happening?
I was told from a nother forum that this kind of problem
occurred with a SP update SQL 7.0 and MS isued a patch.
Should this patch be applied to my server if available? as
it the replication was working prior to a major rebuild.
thanks for any help
Davej
There are probably some missing messages in the sysmessages table in the
master database. Some of the messages are used for dexcriptiosn of the
various replication jobs. You can run a profiler trace while the push
subscription is being created and probably captiure which message is
missing.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error Creating a new cluster on windows 2003 Server EE SP1

Hi,
I'm trying to install cluster service on a Windows 2003 Server Enterprise
Edition SP1.
I've configured Network and Disk.
When I try to create a new a new cluster for the first node, I receive this
error:
Found an existing cluster or node with the name "pippo.mydomain.com". Choose
another name.
A cluster cannot be created with the specified cluster name because that
cluster name is already in use. Specify a different name for the cluster.
I try to change netbios name but it does nothing.(NETBIOS name pippo does
not exsist in mydomain.....i try to use net view \\pippo)
Can You help Me ?
Tank you.
Have you tried to create this cluster before? Chances are that you still
have a DNS entry that needs to be flushed. All Address and Pointer records
for this host/IP address combination need to be flushed before you try and
reinstall.
Sincerely,
Anthony Thomas

"AS" <AS@.discussions.microsoft.com> wrote in message
news:7E9BC8F9-AA47-4D21-B7CF-71E876B9E6F1@.microsoft.com...
Hi,
I'm trying to install cluster service on a Windows 2003 Server Enterprise
Edition SP1.
I've configured Network and Disk.
When I try to create a new a new cluster for the first node, I receive this
error:
Found an existing cluster or node with the name "pippo.mydomain.com". Choose
another name.
A cluster cannot be created with the specified cluster name because that
cluster name is already in use. Specify a different name for the cluster.
I try to change netbios name but it does nothing.(NETBIOS name pippo does
not exsist in mydomain.....i try to use net view \\pippo)
Can You help Me ?
Tank you.

Error Converting data type

Hello,
I've already put one post about thos problem but i didn't put every problems
that i'm having
I need to get the difference between duration of my backups and the time
backups are queued.
The question is that the aplication use one repository in SQL Server and the
table where this information is inserted has this two fields with
[varchar](6) data type.
When i try to make this difference i've some problems like:
- select cast(substring(queuing,1,2) as int) from table
or
- select cast(substring(duration,1,2) as int) from table
Syntax error converting the varchar value '0:' to a column of data type int.
This is because(like i put at the end of this message) duration and queuing
are char and if they have less than one hour i cant treat this information
because they only have one 0(zero) instead of two(00)
Another problem that i had, were all records that had duration greater then
24:00
I need to get the difference of (duration - queuing)
based on the values that i put in the end
durat queuing
0:160:00
29:030:00
0:150:00
25:220:03
0:380:18
12:540:00
18:450:00
17:460:00
I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already try
to help me with this question but i guess that i didnt send all information
and so i cant use they solutions.
Hope you can help me again.
Thanks everybody and best regards.
try with replace
example
select cast(replace(substring(field,1,2),':','') as int)
"CC&JM" wrote:

> Hello,
> I've already put one post about thos problem but i didn't put every problems
> that i'm having
> I need to get the difference between duration of my backups and the time
> backups are queued.
> The question is that the aplication use one repository in SQL Server and the
> table where this information is inserted has this two fields with
> [varchar](6) data type.
> When i try to make this difference i've some problems like:
> - select cast(substring(queuing,1,2) as int) from table
> or
> - select cast(substring(duration,1,2) as int) from table
> Syntax error converting the varchar value '0:' to a column of data type int.
> This is because(like i put at the end of this message) duration and queuing
> are char and if they have less than one hour i cant treat this information
> because they only have one 0(zero) instead of two(00)
> Another problem that i had, were all records that had duration greater then
> 24:00
> I need to get the difference of (duration - queuing)
> based on the values that i put in the end
> durat queuing
> 0:160:00
> 29:030:00
> 0:150:00
> 25:220:03
> 0:380:18
> 12:540:00
> 18:450:00
> 17:460:00
> I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already try
> to help me with this question but i guess that i didnt send all information
> and so i cant use they solutions.
> Hope you can help me again.
> Thanks everybody and best regards.
>
|||You could do this several ways, but one that would work... Here are the
steps
Pick off the hours and minutes from each field. ( use Charindex to find the
':', then use substring or left and right functions to pull off the hours
and minutes... convert each to int, and multiply the hours * 60 and add
them together... Once you have done that for both fields simply subtract to
get the difference.
Hope this helps.
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" <CCJM@.discussions.microsoft.com> wrote in message
news:F744E35A-BC16-43A8-A58E-86BE53A18810@.microsoft.com...
> Hello,
> I've already put one post about thos problem but i didn't put every
> problems
> that i'm having
> I need to get the difference between duration of my backups and the time
> backups are queued.
> The question is that the aplication use one repository in SQL Server and
> the
> table where this information is inserted has this two fields with
> [varchar](6) data type.
> When i try to make this difference i've some problems like:
> - select cast(substring(queuing,1,2) as int) from table
> or
> - select cast(substring(duration,1,2) as int) from table
> Syntax error converting the varchar value '0:' to a column of data type
> int.
> This is because(like i put at the end of this message) duration and
> queuing
> are char and if they have less than one hour i cant treat this information
> because they only have one 0(zero) instead of two(00)
> Another problem that i had, were all records that had duration greater
> then
> 24:00
> I need to get the difference of (duration - queuing)
> based on the values that i put in the end
> durat queuing
> 0:16 0:00
> 29:03 0:00
> 0:15 0:00
> 25:22 0:03
> 0:38 0:18
> 12:54 0:00
> 18:45 0:00
> 17:46 0:00
> I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already
> try
> to help me with this question but i guess that i didnt send all
> information
> and so i cant use they solutions.
> Hope you can help me again.
> Thanks everybody and best regards.
>

Error connecting to sql server

I have a perplexing problem. I've developed an ASP application for our internal business use. I'm getting ready to launch, but every once in a while the application can't connect to the database. The error I get is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

We (IT and I) turned off the remote connections and it still happens. I'm afraid of launching without understanding what causes this and gettign a fix. To get it working again, we restart IIS and it works fine. I used VWD 2005 Express and it's running on SQL Server 2005 Express.

Thanks for any help you may have.
Scott J.

Don't let the bit about "does not allow remote connections" send you down the wrong path - this ridiculous error message is posted regardless of the actual problem (one of Microsoft's worst error messages).

The part to concentrate on is the final part of the error message, which differs depending on the actual error - in your case error: 40 - Could not open a connection to SQL Server. Are you sure that the SQL Express instance is actually running when you get this problem (is it periodically stopping?). Check it via the services applet - restarting IIS may simply be restarting the Express instance.

Error connectin to SQL 2005 Express

I've just installed Visual Studio 2005 and SQL Server 2005 Express beta 2
(all on my local PC). I try to connect to the SQL server from studio using
"Tools/Connect to database" selecting my local pc in the "Select or enter a
server name" combo box. I can select my pc's name but when I try to expand
the "Select or enter database name" combo or press the "Test connection"
button I get the following error:
"Shared memory provider: The specified module could not be found"
Any ideas?
Peter
http://www.aspfaq.com/sql2005/show.asp?id=3
Also, please use the SQL Server 2005 forums.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Hemmingsen" <phe@.softco.dk> wrote in message
news:#YcDp4UpEHA.4004@.TK2MSFTNGP10.phx.gbl...
> I've just installed Visual Studio 2005 and SQL Server 2005 Express beta 2
> (all on my local PC). I try to connect to the SQL server from studio using
> "Tools/Connect to database" selecting my local pc in the "Select or enter
a
> server name" combo box. I can select my pc's name but when I try to expand
> the "Select or enter database name" combo or press the "Test connection"
> button I get the following error:
> "Shared memory provider: The specified module could not be found"
> Any ideas?
> Peter
>

Error Adding SQL Server to Active Directory

Hi,
I've just installed SQL 2000 SE on a server and specified seperate domain
accounts for the services to run under. Now when I go to the server
properties and the Active Directory tab to add the server to AD I get the
following error:
SQL Server Enterprise Manager could not add server '(local)' to Active
Directory. Error 22039: xpadsi.exe failed.
Anyone else had this problem?
Thanks - Dave.
yes. did you ever work out how to fix it ?
"Dave Rhodes" wrote:

> Hi,
> I've just installed SQL 2000 SE on a server and specified seperate domain
> accounts for the services to run under. Now when I go to the server
> properties and the Active Directory tab to add the server to AD I get the
> following error:
> SQL Server Enterprise Manager could not add server '(local)' to Active
> Directory. Error 22039: xpadsi.exe failed.
> Anyone else had this problem?
> Thanks - Dave.
>
>