Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
Possible? yes.
Advisable? definitely NOT.
Supported? No.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||check kb below
http://support.microsoft.com/default...b;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default...b;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas

"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Kno
w
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel
it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files some
where else), and with
restore use the MOVE option to specify new file names. Or possibly only deta
ch, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives
you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000
).
> Databases are set up in reverse. Data is stored in .ldf, and log files ar
e
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (K
now
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log fil
e
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>sql

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database Size (Free,Used)

In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,See sp_spaceused in SQL Server Books Online. In master databse, you could
run the following to see what tables this procedure accesses:
sp_helptext sp_spaceused
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:805D5942-D569-4083-99B4-E537F845D880@.microsoft.com...
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many[vbcol=seagreen]
> daily.
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.

> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.

> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professionalsql

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

Database size

Hi, i'm using SQL Server 2005 Express Edition with advance services. Created a small database with 21 tables and 66 simple stored procedures. The database should not take up huge data in a short period of time. However, when i right click and check out the Database Properties, i noticed that the database size has grown to 1700 MB and available space is only about 0.9 MB. But when i locate the respective .mdf and .ldf file file, the file size of mdf is only about 4000KB and ldf file is about 1,650,000 KB.

I understand that SQL Server Express Edition allows up to 4GB of data file for each database created. But i'm puzzled and would like to know what is actually meant by the databse size and available space that i see via Database Properties. It seems worrying. Can anyone guide me? Thx in advance.

It sounds like your database is running in Full Recovery Model, and your transaction log (the ldf file) is growing. If so, you need to periodically back up your transaction log, which will allow it to release space internally. Then you will need to shrink the transaction log file to get the space back externally.

If you are running in Simple Recovery Model, then you may have a problem with an open transaction.

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.

> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.

> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql

Database Role that allows execution of stored procedures?

We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.
No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_sc..._sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>
|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>
|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> 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
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>

Database Role that allows execution of stored procedures?

We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_s...e_sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> 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
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>

Database Role that allows execution of stored procedures?

We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
--
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> 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
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>> We have a rule for developing database-driven applications that all
>> interaction with the database must be done through stored procedures i.e.
>> all selects, inserts, updates etc.
>> I am looking for simple ways to enforce & support this design principle -
>> and one would be if I could put the SQL login that the application uses
>> into a database role(s) that only allowed execution of stored procedure,
>> i.e. no direct access to tables or views. I know that the long way to do
>> this is to create my own role and grant it execute rights on each SP and
>> no rights to tables/views, but I was wondering if there was anything
>> already built into SQL Server.
>

database return issue

Hello all!

I have a stored procedure that I want to return a value to a C# varaiable:

Code:

public decimal GetSiloLevelForDate(string plantId, DateTime date)
{
 decimal total = 0.0M;
 Open();
 SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@.plantId", plantId);
 cmd.Parameters.Add("@.date", date);
 SqlDataReader reader = cmd.ExecuteReader();
 if (reader.Read())
 {
 if (!reader.IsDBNull(0))
 total = reader.GetDecimal(0);
 }
 reader.Close();
 Close();
 return total;
}

this would normally work just fine. However, it is not becuase the actual SP's end statement is:

return (select @.tempTotal)

which should return a value. But it doesnt... if I run this SQL query:

declare @.usedTonnes numeric(13,2)
exec @.usedTonnes = dbo.getSiloLevelForDate ' 11', @.date

@.usedTonnes is a value, its 7096...

So why doesnt the C# return a value?

(so, the reader is not reading anything)Return values from stored procedures are typically used to indicatesuccess or failure, not to communicate data. A resultset or anoutput parameter is better/typically suited for this task.

The way I see it, you have 3 options:

1. Change your C# code
The way to access the return value is via a parameter with ParameterDirection = ReturnValue. You should beperforming an ExecuteNonQuery, and capturing this parameter. The datareader is overkill for what you are doing.

2. Change your stored procedure
instead of :
return (select @.tempTotal)
use:
select @.tempTotal
return
This would allow you to continue to use the datareader.

3. Change your C# code AND your stored procedure code
The way I'd suggest would be to change @.usedTonnes to an outputparameter in your stored procedure instead of a variable. In yourcode, you should beperforming an ExecuteNonQuery, and capturing this outputparameter with a parameter whose ParameterDirection = Output.

SeeInput and Output Parameters, and Return Values for more background information.|||

return(SELECT @.tempTotal) isn't correct.

The return from a stored procedure is a int. You are trying to passback a resultset through the return statement.

In addition your code is looking for a resultset not passed back from return.

Change your stored proc to end in:

SELECT @.tempTotal

return

and you won't have to change your C# code.

|||thanks for the tips guys. I did use the direction method to solve the issue:

SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);

SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

total = (int)param.Value;

worked fine, and the SQL was

if( @.tempTotal is not null) begin
return @.tempTotal
end
else begin
set @.tempTotal = 0
return @.tempTotal
end

and i got the correct result|||I did end up having to use the direction method of the SqlCommand object:

SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);

SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

total = (int)param.Value;

Database results stored in a Array

How would I go about storing (1 column) database results in a Array?

And then how would I go about extracting the elements from the Array?What, you mean other than by loading it in a recordset/dataset and then looping through the data populating an array? I presume you wanna do this in an application, not SQL Server?

Because T-SQL does not support array structures as far as I know (unless you count the table variable as an array).

Some more info, please. :-)|||My problems is that I am trying to load e-mail values (from a database) into a listbox. The problem I'm facing is that when the e-mails are loaded into the listbox right now, they do not have index values. I was thinking of storing these results in a array so I could have an index and value together e.g. ArrayName( "1", "name@.domain.com").

If there would be an easier way to do this, then I'd like to hear about it. Eventually, I want to populate listbox1 with all the e-mails from the database and then use an "Add" button to insert the highlighted e-mail into listbox2. From there, I would have my chosen e-mails in listbox2 that would get an e-mail from me.

Would I still use a recordset/dataset? If so, how would I add indexes to make each value unique? Or would I have to convert the recordset/dataset into ListItems so that they would have indexes and values together?|||

Dim conPubs As SqlConnection
Dim cmdSelectAuthors As SqlCommand
Dim dtrAuthors As SqlDataReader
conPubs = New SqlConnection(ConfigurationSettings.appSettings("STDConnect"))
conPubs.Open()
cmdSelectAuthors = New SqlCommand("Select * from Email", conPubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
 While dtrAuthors.Read()
 Listbox1.Items.Add(dtrAuthors("email"))
 End While
dtrAuthors.Close()
conPubs.Close()

Would I have to add an index value to the listbox along with the "email" value? Right now, it populates the listbox1 but then can't select anything to add into listbox2.|||What I think is if you want to add some selected items from a list box to another list box, you could do it directly.

Read through (Traverse) the ListBox1. Write an "if" statement which selects only the selected values and add them directly to the ListBox2.

This is what I'm saying.

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
Dim i As Integer
For i = 0 To ListBox1.Items.Count - 1
If ListBox1.Items(i).Selected Then
ListBox2.Items.Add(ListBox1.Items(i).Text)
End If
Next
End Sub

If you want to use a key, value pair then you might have to use a Hash Table.

Database Resoration Error

Hi
I am getting the below error message when I restore the database.
Error Message
==========
Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
Could not adjust the replication state of database 'db_sss'. The database wa
s successfully
restored, however its replication state is inderminate. See the Troubleshoot
ing Replication secion in SQL Server Books Online.
RESTORE DATABASE successfully processed 10201 pages in 38.883 secounds(2.466
MB/sec
SQL Server Edition
============
MSDE with SP3
Operating System
============
Windows 2000 ProfessionalStupid question, but did you go through the troubleshooting section, as per
the error message recommendation? If that didn't help, I recommend you post
to the replication group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Balaji Prabhu.T" <anonymous@.discussions.microsoft.com> wrote in message
news:36AF45C6-BB29-411B-A6AD-9ADF6599E830@.microsoft.com...
> Hi
> I am getting the below error message when I restore the database.
> Error Message
> ==========
> Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
> Could not adjust the replication state of database 'db_sss'. The database
was successfully
> restored, however its replication state is inderminate. See the
Troubleshooting Replication secion in SQL Server Books Online.
> RESTORE DATABASE successfully processed 10201 pages in 38.883
secounds(2.466MB/sec
> SQL Server Edition
> ============
> MSDE with SP3
> Operating System
> ============
> Windows 2000 Professional
>

Database Problem related Clusterd data

Hello sir
We have a very huge database its around 6 lakh records
are being stored in it.
records are not being a sorted order so we checked all
record field through clustering option in Sql server.when we used
clustering records are showing in sorted order but speed of database is
very slow .
i want to know with clustering is there any implecation in database
regading performance od data.Can any one give his views to solve this
problem?and increase the performance of our database.
thanks a lot
vibha wrote:
> Hello sir
> We have a very huge database its around 6 lakh records
^^^^^^
What kind of multiplier is this? --+

> are being stored in it.
> records are not being a sorted order so we checked all
> record field through clustering option in Sql server.when we used
> clustering records are showing in sorted order but speed of database
> is very slow .
> i want to know with clustering is there any implecation in database
> regading performance od data.Can any one give his views to solve this
> problem?and increase the performance of our database.
> thanks a lot
Using a clustered index just because you want to get sorted results is not
a good idea. Now, first you need to determine why it's slow. Then you
can take countermeasures. Have a look around
http://www.sql-server-performance.com/default.asp
and especially
http://www.sql-server-performance.co...ering_2000.asp
Kind regards
robert

Database Permission

Could someone please tell me how I can trap permission errors in
vb.net on a stored procedure:
ie. Execute permission denied on object 'sel_mytable', database
'mydatabase', owner 'dbo'

I would like to print a message like
response.write("You don't have permission to select on this table") as
opposed to the cryptic message I am receiving.

Thanks in advance
Julie BarnetYou can trap a SqlException and translate the error as desired in your code.
VB.Net WinForm example:

Try
sqlCommand.ExecuteReader()
Catch ex As SqlException
Dim PermissionError As Boolean = False
For Each sqlError As SqlError In ex.Errors
If sqlError.Number = 229 Then
PermissionError = True
Exit For
End If
Next sqlError
If PermissionError = True Then
MessageBox.Show("You don't have permission to select on this
table")
Else
MessageBox.Show("Unexpected error: " & ex.ToString())
End If
End Try

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0312081052.d233a1f@.posting.google.co m...
> Could someone please tell me how I can trap permission errors in
> vb.net on a stored procedure:
> ie. Execute permission denied on object 'sel_mytable', database
> 'mydatabase', owner 'dbo'
> I would like to print a message like
> response.write("You don't have permission to select on this table") as
> opposed to the cryptic message I am receiving.
> Thanks in advance
> Julie Barnet|||I am databinding a datagrid. I tried that and it still doesn't seem
to work. I will paste my code...

Is there something I'm missing?

Public Function BindGrid(ProcName as String, myDataGrid as
DataGrid, cmd as SqlCommand, con as SqlConnection) as Integer
Dim ds As DataSet
Dim da As new SqlDataAdapter()

Try

cmd.CommandText = ProcName
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

da.Selectcommand = cmd

ds = new DataSet()
da.Fill(ds, "MyTable")

myDataGrid.DataSource=ds.Tables("MyTable").DefaultView
myDataGrid.DataBind()
return ds.Tables("MyTable").Rows.Count

catch exp as SqlException
HttpContext.Current.Response.Write("Exception3")

End Try
End Function|||I ran your code and got the "Exception3" message. What symptoms are you
getting?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0312090444.3e7dd4ab@.posting.google.c om...
> I am databinding a datagrid. I tried that and it still doesn't seem
> to work. I will paste my code...
> Is there something I'm missing?
> Public Function BindGrid(ProcName as String, myDataGrid as
> DataGrid, cmd as SqlCommand, con as SqlConnection) as Integer
> Dim ds As DataSet
> Dim da As new SqlDataAdapter()
> Try
> cmd.CommandText = ProcName
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Connection = con
> da.Selectcommand = cmd
> ds = new DataSet()
> da.Fill(ds, "MyTable")
> myDataGrid.DataSource=ds.Tables("MyTable").DefaultView
> myDataGrid.DataBind()
> return ds.Tables("MyTable").Rows.Count
>
> catch exp as SqlException
> HttpContext.Current.Response.Write("Exception3")
> End Try
> End Function

database performance management

Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.

please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.

As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.

It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.

At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.

DataBase Performance Issues

Hello,

A couple of things -
Does too many stored procedures in your database affect the performance and speed of the calls?
I have many stored procedures and it seems like the more I create the slower it is to create them. Each time I use enterprise manager it also seems slower and slower to open tables, create tables and create stored procedures for code.

How can I increase the performance of my database by starting and stopping the server at a specified time. and how often should I do this restart?

Thanks in advance for input,
EIt may depend on your DBMS. In general, I wouldn't think more stored procedures would slow down the database. In my experience running Enterprise Manager is always slow when connecting to a remote machine. It is sometimes fast and sometimes slow when connecting to my localmachine's SQL server. Using Query Analyzer is ALWAYS fast (remote or local).

You may want to post this question to the forum at SQLMag.com|||No. Number of stored procedures donot slow performance by any means.

The GUI of using Enterprise manager can be a little heavy ... Is the performance same when using isqlw (Query Analyzer) ... I suppose that would be pretty fast ...

::how often should I do this restart?::

I am not sure why you would like to do this ... I've a SQL Server running for quite a while now without a restart ... So whay are you keen on doing this ...