How do i join the below tables TableA TableB TableC TableD ID ID_C ID ID_A Value ID ID ID_C Value 1 1 1 1 a 1 1 1 a 2 1 b 2 1 b in order to get the Result like Result ID ID_B Value ID_C ID_D ...
I want to know which of the 2 queries below is faster :- Select s.*, sm.* from tblStudent s Inner Join (SELECT studentId,SUM(marks) As Sum from tblStudentsMarks Group By studentId) as sm on s.StudentID = sm.StudentID; ...or: Select s.studentId, s.Name, SUM(Marks) From tblStudent s...
I have trouble in setting the equal condition on a datetime field after converting it to hh:mm:ss.mmm: select top 1 convert(varchar, timestamp, 114) from TEST_TABLE /* returns a single entry of 11:33:35:000 */ Then I want to find all entries with that timestamp: select * from TEST_TABLE where convert(varchar, timestamp, 114) = '11:3...
I am using LINQ select statement wrapped in a TransactionScope (to change the locking) but according to SQL Profiler, it doesn't seem to be working. My code looks like: using (var ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted} )) { using (myDBDataCo...
I have created a stored procedure that takes a table valued parameter that is a table with a single column of type int. The idea is to simply pass a list of ids into the store procedure and allow the sp to work with the data. However, in the case where there is no data to pass in, I am encountering problems (things work correctly when ...
What I mean is being able to access it through Windows Explorer or other programs. I believe the answer is that it isn't possible. But I really want to know why it's not allowed. It seems that the files could be made available read-only through the network share. ...
So in my PHP code, I do timezone selection for the user, where I have a table of timezones and their hour-shift values from GMT. I then use that to add to the DATETIME column values whenever the user picks his timezone in the SETTINGS. So I have two functionalities: reading from DATETIME column in the database, and WRITING to the DATETI...
Hi guys, I am trying to write a SQL query where I am joining two tables and retrieving a few columns. Once this is done, based on a two fields (source table enum, id in the corresponding source table), I need to retrieve a value matching the id in the source table. The issue is that I cannot join all the source tables, and want to do ...
I imported data into csv table via .net app, pls help, how can i aInsert into all the tables so that i can query; student avg score to a question or test student answers to a question or answer frequency to a question etc My tables are in this form; Csv Ans Quest Test Ans_Quest_Test --------- ------ ...
I'm trying to create a stored procedure which will fetch data from sys.databases and sys.database_files and combine this information into a single result set. Here's the code CREATE PROCEDURE dbo.PROC_getDbInfo AS SET NOCOUNT ON GO TRUNCATE TABLE dbo.dbinfo GO EXECUTE sp_msforeachdb 'insert into dbo.dbinfo ...
Update: This is a bug but won't get fixed until the next release of SQL Server due to backward compatibility concerns. This is following on from this question which I answered but am still puzzled by. Adding TOP (1) to a query is sufficient to change the result from "Sep 3 2010" to "2010-09-03" (at least on my machine with British se...
SO really there are two parts of the question: 1) how to use CONTAINSTABLE() with bunch of inner joins? Suppose there are two tables: Item (ItemID, CategoryID, Name) Category (CategoryID, ParentCategoryID, Name) using "dumb" like search this looks like this: create view [db].[vItem] as select i.*, c1.Name as SubCategory, c2.Name as...
Hi there. I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table. This is the code i have - CREATE TABLE Questions( QuestionID int IDENTITY PRIMARY KEY, QuestionNo as 'Q'+Cast(QuestionID as Varchar(10), Que...
I have records in a table representing bus routes, with SQL Server spatial columns for the actual route geometry. (They're stored in a Geography column type.) I use OpenLayers to display these bus routes on top of an OpenStreetMap layer. In instances where the bus routes overlap, currently you cannot see anything but the top route. I'v...
my date column type NVARCHAR(50) example date 02 September 2010 Friday i want to CONVERT DATETIME for can make ORDER BY .. i use ORDER BY CONVERT(DATETIME,M.DeletedDate,102) DESC and i tried 103,104,105...... error message = Conversion failed when converting date and/or time from character string. how can i convert that value to DATET...
hi i have my C# program that work with sql-server 2008 Enterprise Edition i have 40 users that connect to this database. i dont close the connection. how many open connection is recommended to leave ? or is it better to close connection ? thank's in advance ...
I'm facing a decision on how to store URL (bookmark) in a table. I have following requirements: Customer must be able to search the title of a bookmark. I decided to use a FTS with ranking option for this requirement Customer must be able to search an address Here is where I have doubts on how to store URL. First of all, UR...
I have an SQL Server 2008 database with many tables. I've been using the now lame datetime datatype and want to use the new and better datetime2. In most places where I have a datetime field, the corresponding column name is Timestamp. Is there anywhere to do a bulk change from datatime to datetime2? ...
Hi There are many tools out there some free and some not that offer a codesnippet facility. For some reasons in the company I work for they dont want to install or buy any third party stuff. I need to work with just sql server 2008. Is there a way to modify the templates so that when you choose create new SP it loads the custom sp. In a...
Hi, Recently in an interview I was asked to write a query where I had to fetch nth highest salary from a table without using TOP and any sub-query ? I got totally confused as the only way I knew to implement it uses both TOP and sub-query. Kindly provide its solution. Thanks in advance. ...