how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.
Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
restrict user access
how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
restrict user access
how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql
Restrict The Number Of Records From Query
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?
select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS
I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?
select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS
I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>
restrict query results
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
(
Select Row_number() Over (Order by MyField) as RunningNumber,*
From MyTable
)
Select * from SQLPaging
Where RunningNumber between 100 and 150
SELECT TOP 50 * FROM yourTable
|||thanks alot, another question.. what if i wanted the result 100 to 150?|||the way I phrased that question was kinda ambigous.. i'll put it in a clearer way. say if out of that 1,000 results i wanted record 100 to record 150 basically skipping the first 99 records and returning those 50 results in the middle, how could i code that in sql?|||With SQLPaging As
(
Select Row_number() Over (Order by MyField) as RunningNumber,*
From MyTable
)
Select * from SQLPaging
Where RunningNumber between 100 and 150
Restrict decimals
I have a SQL Query which is returning result as:
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.
Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>
|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegr oups.com...
>
|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?
|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3
|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
sql
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.
Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>
|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegr oups.com...
>
|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?
|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3
|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
sql
Restrict decimals
I have a SQL Query which is returning result as:
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.Formatting of the binary data that SQL Server returns from a query is done b
y the client
application. What client app are you using? Sometimes you don't have control
over the client app and
need to hint or force formatting so client app does it the way you want. Thi
s is generally done by
CAST(colname AS datatype) in the SELECT list. What datatype to cast to you n
eed to experiment to
find out (since different client apps has different default formats). Howeve
r some MUMERIC(x,2)
seems a reasonable guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?|||SELECT
CAST(col1*col2 AS desiredType) AS colname
,col3
,...
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518532.283693.123430@.e9g2000prf.googlegroups.com...
>I am using C# 2005. The column data type is decimal (10,2) but when an
> expression is retrieved by multiplying with anything this result is
> returned.
> How to use cast in SQL itself?
>|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.Formatting of the binary data that SQL Server returns from a query is done b
y the client
application. What client app are you using? Sometimes you don't have control
over the client app and
need to hint or force formatting so client app does it the way you want. Thi
s is generally done by
CAST(colname AS datatype) in the SELECT list. What datatype to cast to you n
eed to experiment to
find out (since different client apps has different default formats). Howeve
r some MUMERIC(x,2)
seems a reasonable guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?|||SELECT
CAST(col1*col2 AS desiredType) AS colname
,col3
,...
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518532.283693.123430@.e9g2000prf.googlegroups.com...
>I am using C# 2005. The column data type is decimal (10,2) but when an
> expression is retrieved by multiplying with anything this result is
> returned.
> How to use cast in SQL itself?
>|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
Wednesday, March 28, 2012
Restrict decimals
I have a SQL Query which is returning result as:
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.Formatting of the binary data that SQL Server returns from a query is done by the client
application. What client app are you using? Sometimes you don't have control over the client app and
need to hint or force formatting so client app does it the way you want. This is generally done by
CAST(colname AS datatype) in the SELECT list. What datatype to cast to you need to experiment to
find out (since different client apps has different default formats). However some MUMERIC(x,2)
seems a reasonable guess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>>I have a SQL Query which is returning result as:
>> 1000.0000
>> 2000.0000
>> I want to restrict it to two decimal places. Which function to use? I
>> want to show as 1000.00.
>|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?|||SELECT
CAST(col1*col2 AS desiredType) AS colname
,col3
,...
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518532.283693.123430@.e9g2000prf.googlegroups.com...
>I am using C# 2005. The column data type is decimal (10,2) but when an
> expression is retrieved by multiplying with anything this result is
> returned.
> How to use cast in SQL itself?
>|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > SELECT
> > CAST(col1*col2 AS desiredType) AS colname
> > ,col3
>
>
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.Formatting of the binary data that SQL Server returns from a query is done by the client
application. What client app are you using? Sometimes you don't have control over the client app and
need to hint or force formatting so client app does it the way you want. This is generally done by
CAST(colname AS datatype) in the SELECT list. What datatype to cast to you need to experiment to
find out (since different client apps has different default formats). However some MUMERIC(x,2)
seems a reasonable guess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>>I have a SQL Query which is returning result as:
>> 1000.0000
>> 2000.0000
>> I want to restrict it to two decimal places. Which function to use? I
>> want to show as 1000.00.
>|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?|||SELECT
CAST(col1*col2 AS desiredType) AS colname
,col3
,...
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518532.283693.123430@.e9g2000prf.googlegroups.com...
>I am using C# 2005. The column data type is decimal (10,2) but when an
> expression is retrieved by multiplying with anything this result is
> returned.
> How to use cast in SQL itself?
>|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > SELECT
> > CAST(col1*col2 AS desiredType) AS colname
> > ,col3
>
>
Wednesday, March 21, 2012
Restoring rows accidentaly modified
Hello!
How can I Restore rows which are accidentaly modified.
I forgetted to put where condition in "Update" Query.
Now I want my data back. I have not taken back up manually yet.
I am using SQL Server 2000.
Please help.
Thank you,There is a way, but none via sql. As far as I know, you'rebasically SoL. Transactions can be rolled back, but you can't doit after it's already been commited. There's applications outthere that supposedly can do it, but I can't for the life of meremember the names of them.
Sorry man.
|||
Well, I would assert that there are times where a DML script isneeded in production, and you don't have the luxury of using a devdatabase first. My suggestion is to ALWAYS wrap it in atransaction, with a ROLLBACK at the end, until you obtain the desiredresults.
How can I Restore rows which are accidentaly modified.
I forgetted to put where condition in "Update" Query.
Now I want my data back. I have not taken back up manually yet.
I am using SQL Server 2000.
Please help.
Thank you,There is a way, but none via sql. As far as I know, you'rebasically SoL. Transactions can be rolled back, but you can't doit after it's already been commited. There's applications outthere that supposedly can do it, but I can't for the life of meremember the names of them.
Sorry man.
|||
Tsk tsk tsk ... let this be a lesson.
1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for
2) Backup your database every night or if possible, more often
Alex Papadimoulis wrote:
Tsk tsk tsk ... let this be a lesson.
1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for
Well, I would assert that there are times where a DML script isneeded in production, and you don't have the luxury of using a devdatabase first. My suggestion is to ALWAYS wrap it in atransaction, with a ROLLBACK at the end, until you obtain the desiredresults.
BEGIN TRANSACTIONI ALWAYS ALWAYS ALWAYS do this, lesson learned the hard way.
UPDATE
someTable
SET
someColumn = 'someValue'
WHERE
someCondition = 'True'
ROLLBACK -- change to COMMIT once you know you are updating the correct records
Subscribe to:
Posts (Atom)