sql

Simplest way to do a recursive self-join in SQL Server?

What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this: PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 5 YT NULL 6 IS 5 And I want to be able to get the records only related to a...

Outputting SQL query results to GeoRSS

I get my information from my SQL query (Name, Description, Latitude, Longitude) but I'm having problems getting it into the proper format for GeoRSS so that it may be consumed by my Virtual Earth map. FOR XML AUTO isn't giving me quite what I want and I can't seem to find any examples of how to extract the output of a SQL query to GeoRSS...

MySQL - Prevent duplicate records in table via index?

Using MySQL 5 I have a table like this: date (varchar) door (varchar) shift (varchar) route (varchar) trailer (varchar) + other fields This table contains user generated content (copied in from another 'master' table) and to prevent the users from creating the same data more than 1x the table has a unique index created based on the f...

SELECTing user with earliest time and joining with other data

I have an Oracle database where I'm trying to select a user field from the earliest row (based on a time field) where certain conditions are met, and I don't know how to do it. Here's the gist of my query: SELECT id, CASE WHEN value = 'xyz' THEN 'Pending' ELSE 'Not Pending' END AS status, ti...

Counting values in columns

What I am looking for is to group by and count the total of different data in the same table and have them show in two different columns. Like below. Data in table A Fields: Name - Type Bob - 1 John - 2 Bob - 1 Steve -1 John - 1 Bob - 2 Desired result from query: Name - Type 1 - Type 2 Bob - 2 - 1 ...

Is there an efficient way to index inside words in SQL server 2008?

We have a a few queries in our system that use LIKE '%'+@SomeCriteria+'%' to search a for a person's name. We are talking VARCHAR(50) fields in this case. We would really like to allow our users the ability to search within names. The way I understand it, indexing the field will only make this faster if we search for the first part of...

SQL "Join" on null values

For reasons beyond my control, I need to join two tables and I need null values to match. The best option I could think of was to spit out a UUID and use that as my comparison value but it seems ugly SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') = nvl(T2.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') ...

Unable to use fully qualified table name in dynamic SQL

I'm trying to update a table on several remote servers by iterating over a list of server names and executing some dynamic SQL. See below. DECLARE @Sql NVARCHAR(4000) DECLARE @Server_Name VARCHAR(25) SET @Server_Name='SomeServer' SET @Sql='UPDATE ' + @Server_Name + '.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' PRINT @Sql EXEC @Sql ...

Full table scan occured even when index exists?

We have a sql query as follows select * from Table where date < '20091010' However when we look at the query plan, we see The type of query is SELECT. FROM TABLE Worktable1. Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 32 Kbytes for data pages. With MR...

Fully qualified table names with SP_ExecuteSql to access remote server

Trying to update a table on a linked server (SQL 2000/2005) but my server name will not be known ahead of time. I'm trying this: DECLARE @Sql NVARCHAR(4000) DECLARE @ParamDef NVARCHAR(4000) DECLARE @SERVER_NAME VARCHAR(35) SET @Sql = 'UPDATE @server_name_param.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' SET @ParamDef = N'@server_n...

Retrieving data using select SQL statement in Powershell

My goal is to assign the value of the results returned to a variable: $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "select name from sysdata...

SQL Joins, Count(), and group by to sort 'posts' by # of yes/no 'votes'

I have posts, votes, and comments tables. Each post can have N 'yes votes', N 'no votes' and N comments. I am trying to get a set of posts sorted by number of yes votes. I have a query that does exactly this, but is running far too slowly. On a data set of 1500 posts and 15K votes, it's take .48 seconds on my dev machine. How can I opti...

SQL conflicted with the FOREIGN KEY constraint

I am trying to run some update scripts on my database and I am getting the following error: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_UPSELL_DT_AMRNO_AFMKTG_REF". The conflict occurred in database "ECOMVER", table "dbo.AFFILIATE_MKTG_REF", column 'AMRNO'. I am running the following script: ALTER TABLE [d...

Foreach in SQL?

I'm not quite sure how to do this in SQL. Here it is in pseudocode: Take the list of nodes with content type X. For each node, take the value of field Y. Insert into term_nodes VALUES ((tid that corresponds to Y), 4, (nid of node X)) The (tid that corresponds to Y) is given by SELECT `tid` FROM `term_data` WHERE `name` = Y (I'm tr...

Database Index not used if the where criteria is != ?

I have a index on a column and it is correctly used when the query is select * from Table where x = 'somestring' However it seems to be not used when the query is something like select * from Table where x != 'someotherstring' Is this normal or am I missing something else in the query? The actual query is of course much larger and...

Including Port numbers in %systemroot%\system32\drivers\etc\hosts File

Hi all, Is it possible to include port numbers in hosts file? Reason that I'm asking is because I have sites that I'm trying to migrate from one hosting server to another, and the sites are pointing to an old SQL server that uses the standard TCP/IP port (1433). On the new server, the SQL Server port is different, and I don't want to ch...

Why does TOP or SET ROWCOUNT make my query so slow?

I have a SQL Server 2008 database with approximately 14 millions rows. In it there are two tables Table1 rowId int, primary key someData1 int someData2 int... Table2 id int, primary key rowId ==> int, refers to the rowId from Table1 someCalculatedData int... Table2.rowId is not a foreign key, but I did mak...

insert a value into an integer column without updating it, but adding it's contents.

hello i have: ActiveRecord::Base.connection.execute "UPDATE ventas SET costo_de_compra = #{@nuevo_costo} WHERE id = #{@vid};" but this updates that column value every time it's recursed, what i want is just to insert that value to the already stablished values in that column... in proper instance i want to add the values to an intege...

ResultSet prematurely closing

I'm compiling a list of Page variables (which has a list of books in it) from a running MYSQL Database. When attempting the second iteration in the while(rs.next()) loop, I receive an SQL Exception saying that the ResultSet was already closed. I see nowhere that this code closes the rs object. try { stmt = con.createStatement(); ...

Info/Warning messages from SQL procedures to Java through JDBC driver

By seeing the ResultSet.getWarnings() method there must be some way to pass warning/info messages from stored procedures to Java layer but found most of the drivers doesn't implement this method. Are there any way to send info/warning message from stored procedure to Java layer? ...