[SQL Server] Valid UPDATE, INSERT syntax?
Are these statements valid? UPDATE Table1 FROM (SELECT * FROM Table2) INSERT INTO Table1 (SELECT * FROM Table2) ...
sql-server-2005
SQL order results by number of fields matched.
Bit of a complicated SQL question here. I currently have a SELECT statement which matches several fields, like this. SELECT field1, field2, field3, field4, field5 FROM table WHERE field1 = 'variable 1' AND field2 = 'variable 2' AND field3 = 'variable 3' AND field4 = 'variable 4' AND field5 = 'variable 5' I would like to modify t...
SqlServer 2005: deadlock problem with no shared records
I have a deadlock problem with two transactions that do not access any common records. There is also no lock escalation. So I can't explain why a deadlock is possible. The deadlock occurs when two such transactions are executed at the same time: begin transaction update A set [value] = [value] where id = 1; /* resp. 2 */ /* synchroni...
How to refresh a page when record in db table get changed in ASP.Net 3.5
I have a page that contains a Gridview showing a record from a db table e.g. "tblEmployee". If a record is inserted/updated/deleted in that table then I need my page to be reloaded so that the gridview will show the updated records. Records may be inserted/updated/deleted from any other application. To rebind the gridview I have to relo...
xml schema collection change - huge performance hit
I'm seeing a huge performance hit in our enterprise application when changing the XML schema collection of a given column on a large table. Simplistically put, we're doing something like this: ALTER TABLE HugeTable ALTER COLUMN CustomFields XML (note: CustomFields was previously bound to XML(CustomFieldsSchemaCollection, but of course...
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed.
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed. What does this mean ? is it that DB is currupted? ...
SQL Query to Get Max Value Based on Different Max Value Given Multiple Records
I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id. Note: The id column is not guaranteed to be sequential, I simplified it for this question. Code: create table temp ( ...
Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon.
Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a ...
How can I extract string 'foo' from a string [email protected]?
select left(emailaddress, len(emailaddress) - charindex('@', emailaddress)) I am getting the result below: foo@ma Please correct the select statement below. I need to do this with tones of email addresses and extract the username ONLY. ...
sql - beginning of hour, month etc..
I know how to get in SQL (SQL Server) the current date, but with the beginning of the day: select dateadd(DAY, datediff(day, 0, getdate()),0) (result:2009-09-17 00:00:00.000) I need to get (in SQL) the current date with the beginning of this hour. For example: 2009-09-17 17:00:00 (I don't care about the exact format) and I need to g...
SQL Server - Dynamic PIVOT Table - SQL Injection
Sorry for the long question but this contains all the SQL I've used to test the scenario to hopefully make it clear as to what I'm doing. I'm build up some dynamic SQL to produce a PIVOT table in SQL Server 2005. Below is code to do this. With various selects showing the raw data the values using GROUP BY and the values in a PIVOT as I...
How to determine if an instance of SQL Server is case sensitive or not
How can I find out if an instance of SQL Server 2005 allows case sensitive databases or not? By case sensitive, I mean case sensitivity of the objects in the database, i.e. the following two statements are not equivalent: SELECT * FROM TABLE SELECT * FROM table I've looked in the property pages of the server (in Management Studio) bu...
Multiple Connection Types for one Designer Generated TableAdapter
I have a Windows Forms application with a DataSet (.xsd) that is currently set to connect to a Sql Ce database. Compact Edition is being used so the users can use this application in the field without an internet connection, and then sync their data at day's end. I have been given a new project to create a supplemental web interface for...
SSRS - Keep a table the same width when hiding columns dynamically?
Greetings. I have a SSRS 2005 report that shows prices of things. For some customers, I hide a column from the table (with an expression on the Visibility - hidden property). When I do this, my table shrinks. I've searched long and hard for a way to dynamically resize this table (or to do something at design time to make it stay the ...
What happens to the transaction in this SQL if it's not committed?
If I have the following SQL: BEGIN TRANSACTION SELECT val FROM myTable and don't follow it up with a commit, what happens with the TRANSACTION? Does it depend upon the context/scope that it was executed (e.g. in a proc, in mgt studio)? Is there some place to see if there are transactions open that have not been committed/rolled back ...
SQL 2005 Standard Datatypes
I'm putting together a database of locations for looking up nearest locations for a given address. As I started laying out the table going about my business I wondered what other people were doing and if there was a 'best practices' for some common datatypes. Such as phone numbers, addresses and even latitude and longitude. This is wha...
SSIS 2005 XML Validation Failure Logging
I am working on implementing an SSIS package in SQL Server 2005 which deals with XML files. Multiple XML files will be read-in from a directory on the file system and data from each XML file will be imported into a database table. The XML to database row works fine and I can iterate over all *.xml files in a specific directory. I hav...
OPENXML with xmlns:dt
Use OPENXML to get dt element in MSSQL 2005. How can I get xmlns:dt element in xml? For example, get a result set of two rows that list product id and country code. 121403 GBR 121403 USA declare @xmldata xml set @xmldata = '<?xml version="1.0"?> <data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://w...
Database Query Slower with View
I kind of have a feel for why the view is slower: The where clause is probably not applied at the same time. The results do seem to be the same, though. I am not sure what I can do about this, short of not using a view...which is not ideal, as I added the view to avoid code repetition, and I don't want to remove it if it isn't necessar...
SQL that can pulls records by a possible three values or none at all
Say you have WIDGETS table: WidgetID int ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown) SizeID int (Lookup values: Small, Med, Big, Large) Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupva...