Tuesday, March 27, 2012
Check Roles assign to user
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
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
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
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
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
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
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
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
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
Table Users:
UserId
First_name
Surname
Age
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
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).sqlThursday, March 22, 2012
Check if job is running?
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
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
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
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
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
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'
sqlMonday, March 19, 2012
Check for constraint on delete
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?