Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts

Tuesday, March 27, 2012

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regards
sp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>sql

Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.
You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.
|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results

> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.
|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:

> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>

Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
> >Hello,
> >
> >i need to get information about users that belong to the local
> >administrators operating system group.
> >
> >I execute the following procedure and got my information:
> >
> >exec master..xp_cmdshell 'net localgroup administrators'
> >
> >The question is that i need to get this information without the NULLs and
> >the output lines that appear.
> >Is it possible? Is there any other way to get the same information?
> >
> >Thanks and best regards.
>

Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results

> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:

> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>

Sunday, March 25, 2012

check my SP

i m using SQL Server 2000, in this i have the follwowing SP,

CREATE PROCEDURE AddNewCountry
@.CountryName varchar(50),
@.CountryCode varchar(5)

AS
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName)
begin
raiserror('Country Already Exist',16,1)
end
else
begin
INSERT INTO Countries
VALUES
(@.CountryName,
@.CountryCode)
end
GO
in this SP in the where clause i used the following line :
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName) (used OR operator)
but it inserted the data if i give 092,'america'

and if i used AND operator it chekc 092, 'america' if yes then shows error but if i give 092,'Japan' it inserted new row in the Countries table.

plz give me some other idea what changes i made in my SP as unique Country added with unique Code. if code exists then not allowed to used to enter data in the table same in
the case of country name .

plz give me idea what updation i made as after this my SP works fine.

In this case you have to use Or operator instead of AND operator.

And you can enforce Unique Constraint on both tables (not composite)

ex.

Create Table Countries(

CountryCode Int Unique,

CountryName Varchar(100) Unique

)

Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...

> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/default...b;en-us;271624
Steve
|||Hi,
Check out this article
http://www.sql-server-performance.co...ta_caching.asp
For configuration, you can issue
sp_configure
Thanks
Yogish
sql

Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...
> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
Steve|||Hi,
Check out this articl
http://www.sql-server-performance.com/tp_analyzing_sql_server_2000_data_caching.asp
For configuration, you can issue
sp_configure
--
Thanks
Yogish

Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...

> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/defaul...kb;en-us;271624
Steve|||Hi,
Check out this article
http://www.sql-server-performance.c...>
g.as
p
For configuration, you can issue
sp_configure
Thanks
Yogish

Check mail and password with sp

How can I write a stored procedure with SQL 2000 Server to check an e-mail and password entered by the user, and if both exist select the Id field ?

Table Users:
UserId
First_name
Surname
Age
E-mail
Password

If it is possible I would like to know how can I write the error messages in three cases: If E-mail doesn't exist, if password doesnt exist and if both doesnt exist. And I would like that those messages would be able to appear on the website application.

ThanksMay check Planet SC (http://planet-source-code.com) for any CE.|||Ok thank you

Check login

Hello, everyone. I am trying to create a procedure that checks a user's login, in the database. The login is based on e-mail address and password.

How do I construct the stored procedure to check if the username and password are correct?

Thanks,

Antonio

You could either use a stored procedure, or a function.

Code Snippet


CREATE PROCEDURE dbo.VerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)

AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

(or)


CREATE FUNCTION dbo.fnVerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)
RETURNS varchar(10)
AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

|||

Just a couple of points to make about password storage in databases.

You should never store a password in a clear (unencrypted) form in a database. Preferably you should not even store it in a decryptable form, but rather a value managled by a one-way hash. SQL Server 2005 now has the HashBytes function which will perform a cryptographically valid hash operation.

It is also recommended that you not perform a straightforward hashing but rather salt the value before hashing with another value. This means adding another known piece of information to the value before hashing the combination. This has a 2-fold advantage:

It makes a brute force attack of the hash algorithm with all the possible values harder (basically impossible if the salt is different for each password). Be aware there are projects on the internet where people are building hash lists for all the characters strings up to a certain length composed of a standard set of characters (mainly alpha/digit - upper case only) for some standard hash algorithms. Good reason for using longer passwords (12+ characters) and at least one unusual character. If you use a different salt for each user (I normally use something based upon the primary ID in the User Table) then it prevents the insider attack of moving the password hash from a user whose password is known to another allowing access to the system as that user (restoring the old hash removes signs of the hack). If the salt is held as a field in the table they can move that too (though a unique constraint might make that more difficult).sql

Thursday, March 22, 2012

Check if job is running?

Hi,
I have a stored procedure that needs to start a job stored in sql =
server. How can I check if this job is already started before trying to =
start it? Right now, users are getting the "can't start job because it =
is started" error, and I'd like to get rid of that.
Thanks!
--MichaelLook here:
http://www.databasejournal.com/feat...cle.php/3491201
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
Hi,
I have a stored procedure that needs to start a job stored in sql server.
How can I check if this job is already started before trying to start it?
Right now, users are getting the "can't start job because it is started"
error, and I'd like to get rid of that.
Thanks!
--Michael|||http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Raterus" wrote:

> Hi,
> I have a stored procedure that needs to start a job stored in sql server. How can
I check if this job is already started before trying to start it? Right now, users
are getting the "can't start job because it is started" error, and I'd like to get
rid
of that.
> Thanks!
> --Michael
>|||Thanks, this is exactly what I was looking for!
"Jens S=FC=DFmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> =
wrote in message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
>=20
> http://www.databasejournal.com/feat...cle.php/3491201
>=20
> HTH, Jens Suessmeyer.
>=20
> --
> http://www.sqlserver2005.de
> --
>=20
>=20
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag=20
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql =
server.=20
> How can I check if this job is already started before trying to start =
it?=20
> Right now, users are getting the "can't start job because it is =
started"=20
> error, and I'd like to get rid of that.
>=20
> Thanks!
> --Michael=20
>=20
>|||Nice to get a feedback :-)
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:%23WoN0QFRFHA.4028@.tk2msftngp13.phx.gbl...
Thanks, this is exactly what I was looking for!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
> http://www.databasejournal.com/feat...cle.php/3491201
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql server.
> How can I check if this job is already started before trying to start it?
> Right now, users are getting the "can't start job because it is started"
> error, and I'd like to get rid of that.
> Thanks!
> --Michael
>

Tuesday, March 20, 2012

Check if a caractere exists in a SQL field

Hi everybody ,
I would like with a Stored procedure check if a caracter exists in a string stored in a SQL server field.
Example :
My field contains the caracters "ABCDEF" ,
I would like to check if "C" is in this string.
Tank Uselect charindex('C', 'ABCDEF') which would return 3. If the first expression is not found in the second, it returns zero.|||Thanks joan but i would like to make the expression 2 a variable wich contains my column name

Remeber the syntax must respect syntax of stored procedure

This is my stored procedure code :
@.level is the caracter i search
@.droits is the caracters contained in my field

IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
IF NOT SELECT charindex(@.Level, @.droits)


BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END
SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END

can you help me please ?|||DECLARE @.pos
IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
SELECT @.pos = charindex(@.Level, @.droits)
IF @.pos > 0
BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END

SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(), AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END|||It works

Thank U guy : ))

Check free space on database

You can use xp_fixeddrives to check the available space on disk drives, but
is there a stored procedure to check and see how much space is available in a
database?
What I would like to do before running our jobs is first check and see if
there is space in the database. If not, then check the disk drives to see if
there is room for the database to grow.
Thanks,
sp_spaceused
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" wrote:

> You can use xp_fixeddrives to check the available space on disk drives, but
> is there a stored procedure to check and see how much space is available in a
> database?
> What I would like to do before running our jobs is first check and see if
> there is space in the database. If not, then check the disk drives to see if
> there is room for the database to grow.
> Thanks,
|||Is it possible to insert the results of this into a temp table and then query
it? I would like to automate the process and if the amount of free space
isn't enough, raise an error.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> sp_spaceused
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Andy" wrote:
|||Hi,
See the below script:-
create table #space(spaceused varchar(1000))
execute master.dbo.xp_cmdshell 'osql -SSQLServername -E -Q"exec
sp_spaceused" -o"c:\\sp_space.txt" -s"" '
insert into #spaceexecute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt"
'
select spaceused from #space
Thanks
Hari
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:7B459A15-783C-45FC-BFD4-E6678015B44A@.microsoft.com...[vbcol=seagreen]
> Is it possible to insert the results of this into a temp table and then
> query
> it? I would like to automate the process and if the amount of free space
> isn't enough, raise an error.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:

Check free space on database

You can use xp_fixeddrives to check the available space on disk drives, but
is there a stored procedure to check and see how much space is available in a
database?
What I would like to do before running our jobs is first check and see if
there is space in the database. If not, then check the disk drives to see if
there is room for the database to grow.
Thanks,sp_spaceused
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" wrote:
> You can use xp_fixeddrives to check the available space on disk drives, but
> is there a stored procedure to check and see how much space is available in a
> database?
> What I would like to do before running our jobs is first check and see if
> there is space in the database. If not, then check the disk drives to see if
> there is room for the database to grow.
> Thanks,|||Is it possible to insert the results of this into a temp table and then query
it? I would like to automate the process and if the amount of free space
isn't enough, raise an error.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> sp_spaceused
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Andy" wrote:
> > You can use xp_fixeddrives to check the available space on disk drives, but
> > is there a stored procedure to check and see how much space is available in a
> > database?
> >
> > What I would like to do before running our jobs is first check and see if
> > there is space in the database. If not, then check the disk drives to see if
> > there is room for the database to grow.
> >
> > Thanks,|||Hi,
See the below script:-
create table #space(spaceused varchar(1000))
execute master.dbo.xp_cmdshell 'osql -SSQLServername -E -Q"exec
sp_spaceused" -o"c:\\sp_space.txt" -s"" '
insert into #spaceexecute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt"
'
select spaceused from #space
Thanks
Hari
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:7B459A15-783C-45FC-BFD4-E6678015B44A@.microsoft.com...
> Is it possible to insert the results of this into a temp table and then
> query
> it? I would like to automate the process and if the amount of free space
> isn't enough, raise an error.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
>> sp_spaceused
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Andy" wrote:
>> > You can use xp_fixeddrives to check the available space on disk drives,
>> > but
>> > is there a stored procedure to check and see how much space is
>> > available in a
>> > database?
>> >
>> > What I would like to do before running our jobs is first check and see
>> > if
>> > there is space in the database. If not, then check the disk drives to
>> > see if
>> > there is room for the database to grow.
>> >
>> > Thanks,

Check free space on database

You can use xp_fixeddrives to check the available space on disk drives, but
is there a stored procedure to check and see how much space is available in
a
database?
What I would like to do before running our jobs is first check and see if
there is space in the database. If not, then check the disk drives to see i
f
there is room for the database to grow.
Thanks,sp_spaceused
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" wrote:

> You can use xp_fixeddrives to check the available space on disk drives, bu
t
> is there a stored procedure to check and see how much space is available i
n a
> database?
> What I would like to do before running our jobs is first check and see if
> there is space in the database. If not, then check the disk drives to see
if
> there is room for the database to grow.
> Thanks,|||Is it possible to insert the results of this into a temp table and then quer
y
it? I would like to automate the process and if the amount of free space
isn't enough, raise an error.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> sp_spaceused
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Andy" wrote:
>|||Hi,
See the below script:-
create table #space(spaceused varchar(1000))
execute master.dbo.xp_cmdshell 'osql -SSQLServername -E -Q"exec
sp_spaceused" -o"c:\\sp_space.txt" -s"" '
insert into #spaceexecute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt"
'
select spaceused from #space
Thanks
Hari
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:7B459A15-783C-45FC-BFD4-E6678015B44A@.microsoft.com...[vbcol=seagreen]
> Is it possible to insert the results of this into a temp table and then
> query
> it? I would like to automate the process and if the amount of free space
> isn't enough, raise an error.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
>

Check for table and return true or false

How can I write a stored procedure to return if a table exist or not?

I put:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null RETURN "+@.ISYES+" ELSE RETURN "+@.ISNO+" "

EXEC(@.SQL)
GO

I ran it with: sp_BA_ReportExist '1','0'

but I get:

Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.

How can I make this work?

Thanks!

KenFigured it out... this works:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null PRINT "+@.ISYES+" ELSE PRINT "+@.ISNO+" "

EXEC(@.SQL)
GO

As always I find the answer right after I post!|||you could also use:

declare @.TableName sysname
set @.TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') = 1
print "+@.ISYES+"
else
print "+@.ISNO+"|||I have another problem now...

How do I get the return value?|||do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?|||A resultset will work.

Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.

Thanks so much for any light you can shed on this!

Ken|||try:

create procedure sp_BA_ReportExist(
@.ISYES VARCHAR (10)
, @.ISNO VARCHAR (10))
AS
if (object_id('BA_REPORT_MASTER') is not null)
select @.ISYES as Answer
else
select @.ISNO as Answer

return 0
GO

exec sp_BA_ReportExist 'Yes', 'No'|||Too Cool! Thank you so much!

I was kinda close, but didn't have it quite right!

Thanks for your help!

Ken|||or:
create procedure sp_TableExists(
@.TableName sysname
, @.ISYES VARCHAR (10) = 'Yes'
, @.ISNO VARCHAR (10) = 'No')
AS
select case OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') when 1 then @.ISYES else @.ISNO end as Answer
return 0
GO

exec sp_TableExists 'sysobjects','Yes', 'No'

or just

exec sp_TableExists 'sysobjects'

Check for NULL in stored procedure

How can i check if the value of the variable is NULL in the stored procedure

My variable data type is bigint and i did like

If varName =NULL

print 'Hello'

Else

....

But this is not working it never print "Hello" and later says that you are trying to insert NULL value .

Ashish

Something like this:

Code Snippet

declare @.varName varchar(20)

if @.varname is null

print 'Hello'

sql

Monday, March 19, 2012

Check for constraint on delete

How do you code a procedure to delete a record where if it has an
error (because of foeign key constraint , no cascade and related
records) it will continue and do an update instead.
I have tried this but it doesnt continue if the delete hits an error.
Thanks
Create procedure dbo.delete_record
@.id smallint
as
delete from table
where id=@.id
if @.@.error <>0
update table set deleted=1
where id=@.idwhy don't you update before deleting?