Running as a windows user in Visual Studio Team Services

I need to build a Visual Studio project in Team Services and then run some tests. The tests make REST API calls and then verify that the REST API calls worked by directly accessing the database. The database is a Microsoft SQL Server database that only allows for Windows Authenticated users to access it. It […]

How to design database tables for hierarchical data with unknown depth?

I want to create hierarchical data with unknown depth like this: Create categories and sub categories and for those subcategories they will have also subcategories and so on. The depth of the subcategories is unknown and only will be done in the runtime by the user. What I though about is to but them all […]

Unable know the exception in query

I’m writing the below query in SQL Server update time_tracker set logout = GETDATE(), totaltime = SUBSTRING(CONVERT(varchar(20), (LOGOUT – LOGIN),120),12,8) from Time_Tracker where userid = 0138039 and CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE()); Basically, when the user hits a logout button I’m trying to achieve the below. Enter the logout datetime stamp in my logout column […]

How to check email for file using c#?

I receive an email with an excel file every week. I know there are probably better ways to accomplish my goal, but would it be possible to have a script task in SSIS that can open email, look for a specific file name as an attachment and then copy that file to another location? Here […]

Getting GUID of recently inserted row

Consider these two SQL Server tables Person (PersonId uniqueidentifier, PersonName varchar(20)) Hobby (HobbyId int identity(1,1), PersonId uniqueidentifier, HobbyName) My user interface takes the person name and list of hobbies as input. When user submits the form, I would like to insert appropriate records in the two tables. For e.g. insert into Person values(NEWID(), ‘John’) I […]

query to update data in a table in SQL

I have a lookup table that has following data: AlcoholStatus Id Name 0 None 1 Not Drunk 2 Drunk Less 3 Drunk Medium 4 Heavy Drinker Another table reference this table data (ID) and so it acts as a foreign key in another table. Now I want to update this Table to the following with […]

Determine Non-Matching Item in Set

I have tables similar to this: #1 OriginalID | Area ========== | ==== 2 | Abdomen 3 | Abdomen #2 Area | Part ==== | ==== Abdomen| Abdomen Bottom | Bottom #3 Part | OriginalID ==== | ========== Abdomen| 2 Bottom | 3 Desired result: OriginalID | Area | Part ========== | ==== | ==== […]

Creating a trigger in SQL Server for the appropriate table based on the logic

I have an InventoryOnHand table with a Quantity column. When an InventoryTransaction record is INSERTED, I need to update the Quantity in the InventoryOnHand table. Can someone please explain the following: Should the trigger created in the InventoryOnHand table or the InventoryTransaction table? I would assume the InventoryTransaction table since this is the ‘INSERTED’ When […]

Convert date format doesn't take effect on self made date string in SQL Server

I have a rather strange issue here. I have a date string, which I’ve created partly by myself to incorporate a variable. The problem is, that I’m setting another language settings. In this case, I have to also convert the string to fit the language settings format. I’m using this code: cast(convert(varchar, cast(cast(getdate() as date) […]

Safest way to get the last inserted ID to be unique – SQL

I know that the SCOPE_IDENTITY() will get the last inserted row from insert statement. However, for the following case, I am not too sure is SCOPE_IDENTITY() is safe. As SELECT MAX(ID) FROM TableA will have go through scan the table to get the max id and it will have performance issue, even slightly, I believe. […]

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