Tag: sql-server-2005

Add values of a column and display added result in new table

The table with the data that I have In the above table I have the columns : weekNumber , weeklyHours , points_Rewarded. There are four employees : a,b,c,d I have the values for week1,week2,week3, and so on ( I can have data for many more weeks also such as week4,week5, etc) I want to write […]

Get Min In time and Max out TIme. Fom Attendance record in Sql Server

I am working on attendance system, and below is my data in sql server database |EmpCode|Date |WorkDate |CheckIn |CheckOut |TotalTime |143 |2017-02-13 |2017-02-13 |20:09:02 |22:38:50 |2.496666000 |143 |2017-02-13 |2017-02-13 |22:59:19 |23:18:15 |0.315555000 |143 |2017-02-13 |2017-02-13 |23:33:47 |05:04:24 |5.510277000 |143 |2017-02-14 |2017-02-13 |02:52:38 |05:12:04 |2.323888000 |143 |2017-02-14 |2017-02-14 |20:09:26 |21:59:27 |1.833611000 |143 |2017-02-14 |2017-02-14 |22:30:10 |22:49:26 […]

SQL delete objects having and id range

I want to create a function in sql server that take as input an id range and delete all the object in that range (included the start and the end). Let’s have for example: delete_objects_with_id(id_start,id_end) It will delete all the objects with the id in the range id_start and id_end. The problem is that the […]

Tsql add column using synonyms

I have to add two columns to a table and I have a synonym associated with that table For example: My table name is table_abc and the synonyms is table_1 When i try to add column to the table in normal way, ALTER TABLE [dbo].[table_1] ADD test_1 varbinary(MAX), test_2 varbinary(MAX); Following error occurs : Cannot […]

How to get element values from an XML column?

I have the XML below in a column. I need to get to \Report\Criterias\Criteria (where name=”Advertisers”)\Elements\Element(where name=”ListViewAvailable”). From here I need to list all the numbers that are in the Value element. So far I got: SELECT xmlColumn.query(‘/Report/Criterias/Criteria/Elements/Element’) from tbl but no idea how to filter. <Report> <Criterias> <Criteria name=”Date Range”> … </Criteria> <Criteria name=”Advertisers”> […]

How To Set Limit Table Row SQL?

How to set limit table row in SQL Server? I want to set limit of my table rows to 100 rows only. So when the table have more than 100 rows, I want to delete first row then add new row to last row (100). How can I do this?

In SQLServer, why doesn't granting object permissions add the user to db properly?

This is my setup: SQL server 2005 UserA already setup as user on the server but not on databaseA. GRANT Execute ON [GetOrders] TO [UserA] AS [dbo] As you can see, i have missed out a step. I haven’t added UserA to the database yet. This doesn’t fail and lets me grant execute on the […]

Sum returns 0 on a money column

When I run this query: SELECT [id],[amount],sum(amount) as Total FROM transaction where id=’035′ group by id, amount I get some results like id amount Total 035 -55115.24 -55115.24 035 -2126.14 -2126.14 However when I try to get a total (for the amount column), using this query; select id, count(*) as howMany, sum(amount) as Total from […]

How can do a dynamic query in this scenario . sql 2005

I am trying to enter userids from the query to send them emails . it does not work and gives me this error Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 238 At least one of the following parameters must be specified. “@recipients, @copy_recipients, @blind_copy_recipients”. declare @bodymsg nvarchar(max) select @bodymsg = ‘<font face=”calibiri” size=”4″ […]

What SQL command grants a securityadmin access to a database?

My account is in the securityadmin role and I cannot grant myself sysadmin permission. I wish to gain access to a database so I can add my account to a particular role within it. As I don’t yet have access to the database I can’t use the UI. Does anyone know if this is possible […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.