I have a sql DB table columns which carries data in "0000-00-0000" format. For ex: "8753-11-2010" Now i need to change this value from "8753-11-2010" to "008753-0011-2010" i.e. i need to pad "00" in front of "8753" and "11" i mean the first two strings u can call it. please advise how i can achieve this in sql server 2005. I need to ...
I have a group of people who have taken a test. I can select their IDs with this query: SELECT person_id FROM tests WHERE test_code = 1234 I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do so: SELECT * FROM demographics d WHERE d.person_id IN ( SELECT t.person_id FROM tests...
Table 1 (history data) SiteName OutcomeType SpeciesType Count DateType ------------------------------------------------------------- S1 Adopted Dog 3 0 S2 Adopted Cat 12 0 S1 Transferred Puppy 2 0 S1 Transfer...
Using SQL Server 2005. I was doing some simple queries on a table that has about 200k records. As of today when I got to work, a simple SELECT * FROM executes till it retrieves about 20k rows...then stops. It won't go past 20k rows. If I try to select just ONE row while using ORDER BY Created DESC, the query runs indefinitely. I've neve...
Given the following table: CREATE TABLE BitValues ( n int ) Is it possible to compute the bitwise-OR of n for all rows within a subquery? For example, if BitValues contains these 4 rows: +---+ | n | +---+ | 1 | | 2 | | 4 | | 3 | +---+ I would expect the subquery to return 7. Is there a way to do this inline, without creating a UDF...
One of the most important databases that I administer is poorly designed. The program which uses it uses only 1 login which happens to be a System Administrator. Edits and deletes are done in place so change tracking is difficult. Further, it lacks proper auditing functionality so I cannot tell which user edited or deleted a certain re...
I want to call a web service from my SQL code, but it cannot be using CLR code. Can this even be done? ...
I am creating a new schema in SQLServer 2008. Should I create a new user with the same name as schema owner? Should I use 'dbo' user as schema owner? ...
Hi I have the following table with 10 unique rows, BookingID is a FK containing a random number. The number doesn't need to be in sequence. BookingID, Description 1000 Foo 3000 Bar 1500 Zoo I need to insert an sequential index called ID which goes from 1..x how do I do that in SQL Server 2005? I was thinking to wri...
How does eBay end their auctions and mark the winner? Assume there is an auction end date in the database, once that time has passed the current time, the auction needs to be closed, mark the winner, etc. How is something like this handled in sql 2005? Do they query the db every second to find the expired auctions? Obviously they need to...
I know that there are several questions around this exception on SO, but nothing seen that helps me. I have following query giving me a "Multi-part identifier 'claim.fiData' could not be bound"-Exception: SELECT claim.idData FROM tabData as claim INNER JOIN dbo._previousClaimsByFiData(claim.fiData) AS prevClaim ON prevClaim.idData...
If I have a large table with a column which has a rather limited range of values (e.g. < 100), is it reasonable to divide this table into several tables with names tied to that column value? E.g. a table like with columns: table "TimeStamps": [Id] [DeviceId] [MessageCounter] [SomeData] where [DeviceId] is the "limited range" column w...
I want to delete about 90% of rows from a million+ row table. Anything I can do to make this faster? e.g. if I turn on Simple Recovery Mode will this help? ...
I thought that must be obvious but I can't figure it out. Say there is a table tblData with a column ID and a table-valued-function (_tvf) that takes an ID as parameter. I need the results for all ID's in tblData. But: SELECT * FROM tblData data INNER JOIN dbo._tvf(data.ID) AS tvfData ON data.ID = tvfData.ID gives me an error: T...
I have a column which has decimal value of 18,8. I was asked to extend it to 18,18 to hold more places after ,. ALTER TABLE [dbo].[TransakcjeGotowkowe] ALTER COLUMN TransakcjeGotowkoweKwota decimal (18,18) NULL Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been...
Has anyone used SQLite before with asp.net? Is there a SQLite Linq provider? Do you recommend something other than SQLite? Would like to hear your thoughts. Will this work as the master database and then backup to a sql server 2005 db? Can you run a hybrid of the two? ...
I am trying to use Sp_configure Proc in another stored procedure, but getting errors. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE Test01 AS BEGIN SET NOCOUNT ON; sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE Go END GO T...
I have the following query which runs fine on all of my sql server 2005/2008 databases SELECT sysprocesses.spid FROM master.dbo.sysprocesses However for one of my databases it give me a binding error on the spid column (cannot bind multipart identifier). I've check the compatibility mode of the db and it's set to 2005 so I'm sure th...
In the context of MS SQL Server 2005 Simply put is there a way to stop delete, and update sql statements being executed against the database that don't have a WHERE clause? Ideally it would be nice to restrict this 'blocking' to a set of users/roles. ...
Given the following table: create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (e.g., WHERE ID = n)? It's ea...