tsql

how do i get the column name and or value?

select t.slotmachinebk, t.gamingdate, t.freeplaydownloaded, t.freeplayadjusted, t.freeplayplayed, t.freeplayabandoned, t.freeplaybalance from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t where not exists (select * from testtable where slotmachinebk = t.slotmachinebk and auditdate = t.gamingda...

SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_...

SQL query to return one single record for each unique value in a column

I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data. Behold, [Addresses]: Name Street City State -------------------------------------------------------- Bob 123 Fake Street Peoria IL Bob 234 Other...

Query times out in .Net SqlCommand.ExecuteNonQuery, works in SQL Server Management Studio

Update: Problem solved, and staying solved. If you want to see the site in action, visit Tweet08 I've got several queries that act differently in SSMS versus when run inside my .Net application. The SSMS executes fine in under a second. The .Net call times out after 120 seconds (connection default timeout). I did a SQL Trace (and col...

How to write an xquery containing sequence elements?

I've got a very large xml data set that is structured like the following: <root> <person> <personid>HH3269732</personid> <firstname>John</firstname> <lastname>Smith</lastname> <entertime>01/02/2008 10:15</entertime> <leavetime>01/02/2008 11:45</leavetime> <entertime>03/01/2008 08:00</e...

Which tables/views contain the list of objects used by Service Broker?

Hey StackOverflow, I work with Service Broker. I need to know which Service Broker entities (message types, contracts, etc.) have been added to the database I am dealing with. The question refers to MS SQL Server 2005. Thank you! ...

Delete duplicate records from a SQL table without a primary key

I have the below table with the below records in it create table employee ( EmpId number, EmpName varchar2(10), EmpSSN varchar2(11) ); insert into employee values(1, 'Jack', '555-55-5555'); insert into employee values (2, 'Joe', '555-56-5555'); insert into employee values (3, 'Fred', '555-57-5555'); insert into employee values (4, '...

How do I update tables in SQL so that related strings match?

Suppose I have 2 tables Table1 ID Path -- ---- 1 PathA 2 PathB 3 PathC 4 PathD Table2 ID Path Table1ID -- ---- -------- 23 PathA 1 24 PathX 2 25 PathC 3 26 PathZ 4 In the above, PathX should be PathB and PathZ should be PathD How do I sync all the path values in Table2 with values in Table 1, the tables could be large so woul...

Increasing Alphanumeric value in user defined function

Hello, I'm trying to code a user defined function under SQL Server 2005 that will increase integer part of alphanumeric value by one. For example, uf_AlphanumericIncrease ('A000299') should return 'A000300'. Here's what I've done so far; ALTER FUNCTION uf_AlphaNumericIncrement ( @ID varchar(10) ) RETURNS VARCHAR(10) AS BEGIN DECL...

Return an Oracle Ref Cursor to a SqlServer T-SQL caller

Is it possible to return an Oracle Ref Cursor to a caller that is in SqlServer T-SQL? When dealing with a normal .Net program there is this Knowledge Base article: http://support.microsoft.com/kb/322160 But is this same type of thing possible from T-SQL? ...

Using a variable for table name in 'From' clause in SQL Server 2008

I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have: Select * From [dbo].[TableA] I need something like: Select * From [dbo].[@TableName] The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can...

T-SQL equivalent of Excel "MAX" function to return larger of two numbers

In Excel, there's a function called "MAX" that accepts numbers and returns the largest one in the set. Is there a function in T-SQL that duplicates this functionality? I haven't been able to find one, and I've written a UDF that does it for me, but I thought it was worth asking. Here is the function I've been using: CREATE FUNCTION dbo...

Using XQuery in SQL 2005 to replace a node's value?

Given the following XML variable, how can I replace "UNKNOWN" in the StateCode node with "FOO" in TSQL for MS SQL 2005? declare @xmldata xml set @xmldata = '<Collection> <Plan> <StateCode>UNKNOWN</StateCode> <Type>Tubular</Type> </Plan> </Collection>' Unlike a similar question that I found, this is si...

Calculating variances on dates in T-SQL

Guys, I am trying to write a stored procedure in T-SQL (SQL Server) that will select records based on a date field, keeping a variance of minutes in mind. Something like this: CREATE PROCEDURE spGetCustomers(@DateRange DATETIME, @Variance int) AS -- The next line is where I need help -- I'm trying to subtract X amount of minutes from th...

Weird trigger problem when I do an INSERT into a table

Hi folks, I've got a trigger attached to a table. ALTER TRIGGER [dbo].[UpdateUniqueSubjectAfterInsertUpdate] ON [dbo].[Contents] AFTER INSERT,UPDATE AS BEGIN -- Grab the Id of the row just inserted/updated DECLARE @Id INT SELECT @Id = Id FROM INSERTED END Every time a new entry is inserted or modified, I wish to update a si...

Select Same Customer Name but that has different customer Address

Trying to select records that are all for the same customer, but where the address is different. So I can later let the user choose Bob Yonkers, then choose to update all of Bob's records to a specific address. So I want to show all the available records. Data Example: CUSTOMER_NAME, CUSTOMER_ADDRESS Bob Yonkers , 42 Satellite Cir ...

Rewrite T-SQL from using IN to using JOIN/WHERE

How can I rewrite this t-sql to avoid using IN - and use joins in stead? --Members with membership in 2008 but not in 2009, using IN and NOT IN SELECT * FROM @Members WHERE MemberId NOT IN (SELECT MemberId FROM @Memberships WHERE [Year] = 2009) AND MemberId IN (SELECT MemberId FROM @Memberships WHERE [Year] = 2008 ) I want to fi...

t-sql udf, get the data type of a parameter

is it possible to get a numeric parameter to my udf and do stuff according to its type, like: if type of @p1 is decimal(10,3) ... else if type of @p1 is decimal(15,3) ... else if type of @p1 is integer ... ...

Severe error when trying to FREETEXTTABLE an indexed view with a CTE

Hi, Where stockView is an indexed view with a full-text index, I receive the error message below. The database is running on a 2008 Express engine in 2005 compatibility mode. Code: with stockCte (title, grade, price, weighted) as ( select sv.[title] , sv.[grade] , sv.[price] , (case when sv.[issue] = ...

SQL: script to create country, state tables

Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territori...