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 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 link server availability in sp

I am changing a stored proc to use linked servers.
We have 5 linked sql servers, each in different physical locations and
from time to time the network connection at one of the locations may go
down. It is critical that if I can't get to a linked server, that my sp
doesn't fail and return an error code. I just need to run a different
sql statement against just the one sql server.
I am trying to simulate this in my lab by doing any of the following:
- physically disconnecting the linked sql server's cat5 cable from the
network
- pausing the linked server
- changing the name of linked server on my local server so the names
don't match.
In each case I get a message saying
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can't change the code, I really need to accomplish this in stored
procedure(s)
Here is my stored procedure.
SET XACT_ABORT OFF
SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
EXEC @.iReturnCode = sp_executesql @.sSql
SET XACT_ABORT ON
PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
IF @.iReturnCode = 1
BEGIN
PRINT 'can't connect to linked server'
-- run sql statement1 on local sql server
END
ELSE
BEGIN
PRINT 'no error'
-- run sql statement2 on local and linked sql server
END
Any ideas?I have written a small activeX dll that I use to ping the target servers
using the sp_OA methods for active X integration with transactSQL. I've
wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
The server itself must be able to resolve the address.
You can have the DLL if you want, its a VB6 project.
Phil
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>|||Ping is probably a decent first step, but a small one.
If it's not on the same LAN, pings may get dropped on the floor due to
outright refusal of traffic, this is very common, e.g. ping
www.microsoft.com
Even a successful ping result does not mean everything is okay. Did you
check the actual port SQL Server is listening on (may be disabled or
blocked)? Are you sure that the specific SQL Server instance is running?
Are you sure that your login credentials are good?
Another approach might be to inspect the results of an osql call.
create table #foo( dbname sysname null )
insert #foo exec master..xp_cmdshell
'osql -S<linkedservername> -U<username> -P<password> -dMaster -Q"SELECT TOP
1 Name FROM sysdatabases"'
SELECT * FROM #foo
drop table #foo
This will take 10 seconds if <linkedservername> is not available. The
result will be:
(3 row(s) affected)
dbname
----
----
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
NULL
(3 row(s) affected)
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:uOu4tow2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I have written a small activeX dll that I use to ping the target servers
>using the sp_OA methods for active X integration with transactSQL. I've
>wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
>The server itself must be able to resolve the address.
> You can have the DLL if you want, its a VB6 project.
> Phil|||You can use SQLDMO as in this example
http://www.sqldbatips.com/displaycode.asp?ID=38
In SQL2005 there is a bultin system procedure to do this
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>

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
>

Check if files exist Stored Proc

Hi,
This stored proc code uses DOS Copy command and xp_cmdshell stored
procedure to copy files form one location to another. See below the
code. It is working fine!. What I need is to add a check if the files in
the Source location exist or not. If not, then send an email (using
xp_sendmail) to us saying "files do not exist". If yes, then start
copying the files. How do I do that?
Thanks for your help.
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
select @.source =
case @.@.servername
when 'A' then '\\server1\folder1\a*.*'
when 'B' then '\\server2\folder2\b*.*'
when 'C' then '\\server3\folder3\c*.*'
end,
@.destination =
case @.@.servername
when 'A' then '\\serverx\folderx'
when 'B' then '\\serverx\folderx'
when 'C' then '\\serverx\folderx'
end
-- copy only if the files in the source folder exist, otherwise send an
email for "files do not exixts".
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
GO
*** Sent via Developersdex http://www.examnotes.net ***Look up xp_fileexist in Books Online.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Whoops, never mind! I forgot this proc is not documented / supported.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwg8xEguFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Look up xp_fileexist in Books Online.|||A particular file can be checked with:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, Jens Suessmeyer.|||If this were implemented as a DTS package, you would perhaps find the file
system object more suitable for copying files, etc.
http://msdn.microsoft.com/library/d...ystemObject.asp
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks for the respone. The "xp_fileexist" works great if you know the
exact file name. In my case, I dont know the file name. All I need is to
capture the files start with "a" i.e. a*.*
So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||CREATE TABLE #files
(
filename SYSNAME NULL
)
SET NOCOUNT ON
INSERT #files EXEC master..xp_cmdshell 'dir \\server1\folder1\a*.* /b'
SELECT COUNT(*) FROM #files WHERE filename IS NOT NULL
SELECT * FROM #files WHERE filename IS NOT NULL
DROP TABLE #files
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uvoSyaguFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks for the respone. The "xp_fileexist" works great if you know the
> exact file name. In my case, I dont know the file name. All I need is to
> capture the files start with "a" i.e. a*.*
> So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
> Any other ideas?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If you're already planning on using xp_cmdshell, then try this:
declare @.cmd varchar(1024)
declare @.path varchar(1024)
create table #fs
(
fId int identity (1, 1) primary key
,fName varchar(1024)
)
set @.path = 'c:\Inetpub*.*'
set @.cmd = 'dir /b ' + @.path
insert #fs
(
fName
)
exec master.dbo.xp_cmdshell @.cmd
if (exists (
select fs.fId
from #fs fs
where (fs.fName is not null)
))
begin
print 'exists'
end
else
begin
print 'does not exist'
end
ML|||Aaron! Thanx a lot!!!. It works like a champ!!!
*** Sent via Developersdex http://www.examnotes.net ***

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

Check dependencies for stored procedures

Hello, everyone:
I have 40 stored procedures in a database. A table X is called by some procedures. How to check which procedure call table X, and how many procedures call table X? Thanks
ZYTquery the text field of syscomments in your database.

check constraint on input parameter

New to SQL server 2000 and wondering if it's possible to define a check
constraint on an input parameter
(stored procedure).
My input parameter(defined as smallint) should only accept the values 0, 1
or 2.
Would appreciate any help.
Message posted via http://www.webservertalk.comI dont think it is possible. But what you can do instead is the have a
check in the stored procedure and raise an error if the value passed is
not one of the expected values.|||you'd have to either
1) raise an error at the top of the procedure
2) default to 0 for <0 or 2 for >2
e.g.
create procedure myproc @.input1 smallint as
if @.input1 not between 0 and 2
begin
raiserror('Procedure myproc requires parameter @.input1 to be between
0 and 2', 16, 1)
return -1
end
...
or
create procedure myproc @.input1 smallint as
select @.input1 = case when @.input1<0 then 0 when @.input1>2 then 2 else
@.input1 end
...
Cismail via webservertalk.com wrote:
> New to SQL server 2000 and wondering if it's possible to define a check
> constraint on an input parameter
> (stored procedure).
> My input parameter(defined as smallint) should only accept the values 0,
1
> or 2.
> Would appreciate any help.
>|||That's what I thought.
Thanks for your help.
SQL novice wrote:
>I dont think it is possible. But what you can do instead is the have a
>check in the stored procedure and raise an error if the value passed is
>not one of the expected values.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1

Check Client IP

Hello,

How check Client IP address in stored procedure?

http://www.sqlserver2005.de/Articles/3/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.