tsql

SQL error on insert statments with runtime controls

I am using C#, VS 2005 and SQL 2000 My code is: StringBuilder sb = new StringBuilder(); sb.Append("insert into dummy(name,amount)values"); foreach (Control ctl in this.flowLayoutPanel1.Controls) { if (ctl.Name.Contains("tb") && ctl is TextBox) { sb.Append(ctl.Text); } } foreach(Control bbl in this.flowLayoutPanel1.Contro...

t-sql Aggregate Max

Hi. I have table: CREATE TABLE [dbo].[test] ( [name] nvarchar(max) NULL, [date] datetime NULL ) And records on it: a 2010-09-02 12:00:00 a 2010-09-02 11:00:00 b 2010-09-02 12:00:00 b 2010-09-02 11:00:00 And i want to get all name with the newest date: I may do: select t.[name] from test t group by t.[name] having max(date)...

Finding Out If Datetime Is In a Specific Time Interval Using SQL

Hi everybody, How can I find out if a datetime column in my table is in a specific hour interval (e.g 13:07 - 15:15) and in a specific day (e.g Thursdays) in a SQL select statement? Thanks, ...

T-SQL How to run procedure recursively ?

Hi, in my procedure, I try to run itself recursively when some conditions appear (I simplified the code just to find out how to do the recursion) Firstly I created the procedure with commented the BEGIN..END body, and then: alter PROCEDURE [dbo].[insert_new_customer_task] @TaskTypeID decimal, @TaskName nvarchar(1000), @...

converting date field format

I have a field with birthday as either '7/08/1986' or '07/08/1986'. I want to convert the value to like this '1986/08/07'. Simply converting from DD/MM/YYYY to YYYY/MM/DD. Kindly note down this is all in SQL Server 2005. Additionally, if the date is in single digit means still the output shd be shown in double digit like '07' not '7'. ...

delete large amount of data

i have to delete a large amount of data. truncate is not possible because of relations. And I don't wanna drop the table because of views. I am using code below but is there better idea? delete from table WHILE 1=1 BEGIN BEGIN TRAN DELETE top (1000000) from table IF @@rowcount < 1000000 BREAK WAITFOR DELAY '00:00:00:010' COMMIT end D...

Can you give me Alternative to Group By sql server 2008

Hi, I have a situation where I have a Select with 6 fields but I only want to group by 3 fields. As you know this looks not possible. What do you do in these situations? Thanks for any feedback ...

T-SQL - How to get values from row 10 to 20?

is this possible using T-SQL? ...

How i to get the exact match join for the below scenario?

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 ...

Complicated daily automatic insert query in 2 tables

I have the following tables with their columns (only the relevant columns are listed): Entry EntryID (int/auto-inc) EmployeeNumber (int) JustifyDate (datetime) EntryDate (datetime) Employee EmployeeNumber (int) CoreHourID (int) WorkingFromHome (bit/bool) Hour EntryID (int) InHour (datetime) OutHour (...

How to store and extract XML information from an nvarchar(max) type column, and use it in joins?

Hi, I have a column of type 'nvarchar(max)' that should now hold XML information instead of just a string. Say: col1 has value 'abc' Now it has values, with additional info: <el1>abc</el2> <el2>someotherinfo</el2> Storing the information to the column is fine, since it can still be pushed in as a string. However, extracting the same...

Which query seems efficient?

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...

sql help please

Hi All, The following sql snippet below is a subselect of a parent sql. Is there a way to say, "If customer doesn't exist I want to run a different sql"? select orderdate, (select contactname from customers where customerID = 'ALFKI' or select 'No Customer') as contactname from orders I know this can be solved with a join but...

TSQL Loop through table records and compare against 2nd table

I have two temp tables, both populated by TSQL queries. Temp Table A containing a Location, an Item, and a total inventory count. Temp Table B containing a Location, an Item, a Sales Order Number, and negative inventory count. I would like to loop through each item in Table B and subtract the negative inventory count from table A wher...

Sorting in CTE expression

Hi guys, I am retreiving all users from DB ordered by number of followers for each user DESC with TH_Users as ( SELECT [ID] ,[FullName] ,[UserName] ,[ImageName] ,dbo.GetUserFollowers(ID) AS Followers , ROW_NUMBER() OVER (order by ID ) AS 'RowNumber' from dbo.TH_Users Where CultureID = @cultureID ) Select ...

Can anyone fathom a why do this or how it works correctly with this bit of SQL?

I've changed the table names but I found this FROM statement in a SP I'm optimizing and I'm wondering the how this could come into being, why would you ever do anything like this, and why does it still work. FROM tblPezFill pf RIGHT OUTER JOIN tblWolveLocation stfl RIGHT OUTER JOIN tblDuckPez pp RIGHT OUTER JOIN tblChaos o I...

Timezone Daylight Saving problem in PHP and SQL

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...

Can I ensure the order of dialog conversations in sql service broker?

So, I've got an 'A' server and a 'B' server. We are using SQL Service Broker to perform replication. An application we have will need to write data to either the 'A' server, or the 'B' server. (one may be down, and the other one should take over) Data is written to the database only within stored procedures, which are wrapped in a ...

SQL 2005 - two tables Join on some id,

Let's say I have 2 tables. I want join them so that for every account I get 1 row where the account's information is there PLUS the primaryContact's information appended to the table. Is this possible? ID's are unique keys. ACCOUNT TABLE accountid | name | income | primaryContact 123456789 Jack Johnson 1...

SQL Error on Audit Trail Insert after Scope_Identity()

I'm trying to use a stored procedure to do an insert on a main table, then also insert that data into a historical data table. I don't want to use a trigger, but rather just do a simple insert of all columns into the history table. I am getting this error, however. "An explicit value for the identity column in table 'ipaudittrail' can...