tsql

Search and replace part of string in database

I need to replace all iframe tags, stored as nvarchar in my database. I can find the entries using the following sql-question: SELECT * FROM databasename..VersionedFields WHERE Value LIKE '%<iframe%' Say I want to replace the following code segment: code before iframe <iframe src="yadayada"> </iframe> code after iframe With this: ...

sqlserver date arithmetic problem

I have a records of events with starttime and endtime for a calendar control. I want to get the events done on a particular date say 2/28/2009 But the db table has date data in form 2/28/2009 10:00:00, 2/28/2009 12:00:00. I tried this query in a sproc created in VS 2005 IDE but it didn't work ALTER PROCEDURE dbo.spSelectBenchEvent (...

SQL Tables - Pattern for either/or data

Hello All, I have a quick question - is there a best practice in SQL Table design for storing "either/or" data? I have the following problem - I need to store template data (defining folder structure) in a SQL table. Any given folder might have a static name (for example "Emails") or it might be dynamically generated for each instance...

Convert integer to a list of week days

I have an integer stored in a database (SQLAgent Frequency Interval) This integer is actually the sum of the selected days of the week that the schedule is to run The possible values are any combination of these values Sunday =1 Monday = 2 Tuesday =4 Wednesday= 8 Thursday = 16 Friday = 32 Saturday =64 ex 65 means that the schedul...

How can I tell if an index contains a column of type varchar(max)?

I'm working on my MSSQL index defragmentation script. Certain kinds of indexes can be rebuilt online, and other kinds can't. For clustered indexes, it's easy enough to see if the table contains any LOB columns, but for a non-clustered index I need to specifically know if there is any LOB columns covered by that specific index. I used ...

Get dates from week number in T-SQL

In Microsoft SQL Server, I have a week number (from DATEPART(wk, datecol)) but what I would like to do is turn this back into the date span for that week. For example, SELECT DATEPART(wk, GETDATE()) yields 10. I would like to derive 3/1/2009 and 3/7/2009 from this number. Does anyone know if this is possible? ...

How to get lowest, common parent for 2 rows in recursive table (SQL)

Let's say that we have we have a table with the classic 'manager id' recursive relationship: Users user_id int manager_id int (refers to user_id) If you randomly select 2 rows in the table- or 2 nodes- how do you find the lowest level, common ancestor? My platform is SQL Server 2005 (Transact-SQL) but any ANSI compliant SQL will ...

How do I remove an Identity completely

I'm currently putting together some changes in our data model which include changing a column (that happens to be part of the primary key) so that it is no longer an identity. Is there a way to do this short of actually removing and recreating the entire column or table? The autogenerated code from SSMS does just that but I was wondering...

How to get a proper count in sql server when retrieving a lot of fields?

Here is my scenario, I have query that returns a lot of fields. One of the fields is called ID and I want to group by ID and show a count in descending order. However, since I am bringing back more fields, it becomes harder to show a true count because I have to group by those other fields. Here is an example of what I am trying to d...

Passing a list to a TSQL 2008 Stored Procedure

In MSSQL Server 2008 is there a way to construct the flowing as a stored procedure where the ... is passed in as a parameter. And not have to have a "separating" stored procedure or function which splits a csv and returns a table? select * from atable where atable.id in (...) ...

Find a table across multiple databases SQL SERVER 2005

I exported a table to a server but I can't find the table. Maybe I didn't put the right destination database. How can I find this table if my server has multiple databases, without opening each one of them? I use MS Sql Server Management Studio 2008. ...

SQL MAX of column including its primary key

Short: From below sql select I get the cart_id and the value of the maximum valued item in that cart. select CartItems.cart_id, max(ItemValues.value) from CartItems inner join ItemValues on CartItems.item_id=ItemValues.item_id group by CartItems.cart_id but I also need item_id for that item (ItemValues.item-id). Long: Two tables, C...

MSSQL 2005 C# Assembly & OUTPUT

I'm having a problem OUTPUTing a variable in my assembly. Do I need to add a "out string var1" to the parameter list of the function in C#? I get an error - something related to var1 not being set... I tried parameter.Direction = ParameterDirection.Output I can't find any good examples Edit: My assembly SP currently returns a records...

Howto rewrite phone numbers in TSQL?

I have a beginners question in TSQL. We imported Excel Sheets into a SQL Server 2008. Too bad these excel files were not formatted the way they should be. We want a phone number to look like this: '012345678', no leading and trailing whitespace and no whitespace within. Even worse sometimes the number is encoded with a prefix '0123-2349...

ROW_NUMBER() column is invalid and trying to speed up query help.

I am trying to use the ROW_NUMBER function, but I am running into two problems. 1) Because I am ordering the count in descending order, the rowid does not start at 1, it starts at 41 for example. 2) When I try to bring back rowid between 42 and 52, it says rowid is an invalid column. Here is the toned down query with some extra columns ...

Can I use SQL to plot actual dates based on schedule information?

If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using some sort of CROSS JOIN, perhaps? Consider a payment schedule table with these columns: StartDate - the date the schedule begins (1st payment is due on this d...

T-SQL Global / Shared Data

When writing TSQL stored procedures I find myself wanting to centralize / normalize some parts of the code. For example, if I have a query like this: SELECT * FROM SomeTable WHERE SomeColumn IN (2, 4, 8) For cases like this I would like to put (2,4,8) in some place outside of the procedure that I could reuse in some other query later ...

In TSQL, how would you convert from an int to a datetime and give the age?

What would be the sql for the following, I have a date of birth in an int field, ie YYYYMMDD = 19600518 I would like to get the age. ...

Removing non-numeric characters in T-SQL

I'm using the function sp_spaceused to get the details of all the tables in a DB. The index_size column is VARCHAR returned complete with ' KB' on the end, however I want to to display in MB. All I need to know is how to strip out the KB, I can do the rest! :D ...

TSQL string concatenation is not working within WHILE loop ??

Code bellow is not working, any ideas why? declare @Counter int set @Counter = 0 declare @ConcText nvarchar(1000) while @Counter < 5 begin --set @ConcText = @ConcText + cast(@Counter as nvarchar(10)) + N' counter,' --set @ConcText = @ConcText + convert(nvarchar(10), @Counter) + N' counter,' set @ConcText = @ConcText + N' co...