This is sort of SQL newbie question, I think, but here goes. I have a SQL Query (SQL Server 2005) that I've put together based on an example user-defined function: SELECT CASEID, GetNoteText(CASEID) FROM ( SELECT CASEID FROM ATTACHMENTS GROUP BY CASEID ) i GO the UDF works great (i...
Are disabling and enabling FK constraints supported in SQL Server? Or is my only option to 'drop and then re-'create' the constraints? ...
Hi, I'm looking for a built-in function/extended function in T-SQL for string manipulation similar to the String.Format method in .NET. ...
I'm having to do some data conversion, and I need to try to match up on names that are not a direct match on full name. I'd like to be able to take the full name field and break it up into first, middle and last name. The data does not include any prefixes or suffixes. The middle name is optional. The data is formatted 'First Middle La...
I have a SQL Server 2005 table like this: create table Taxonomy( CategoryId integer primary key, ParentCategoryId integer references Taxonomy(CategoryId), CategoryDescription varchar(50) ) with data looking like CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar I'd like to query it into an xml document like this: ...
I recently had to rename a table (and a column and FK/PK contraints) in SQL Server 2000 without losing an data. There did not seem to be an obvious DDL T-SQL statements for performing this action, so I used sp_rename to directly fiddle with object names. Was this the only solution to the problem? (other, than give the table the correct ...
Warning: this is the actual code generated from my system: ;WITH RESULTS AS ( SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK) INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK) ON cat.APPL_CD = m.APPL...
Let me apologize in advance for the simplicity of this question (I heard Jeff's podcast and his concern that the quality of the questions will be "dumbed down"), but I'm stuck. I'm using AquaData to hit my Informix DB. There are quirky little nuances between MS SQL and Informix SQL. Anyway, I'm trying to do a simple nested expression ...
I need to get the Folder size and display the info on a report (SSRS). I need to do this for a number of Databases (loop!). These DB's are websites' backends. Has anyone done this before? Can you please point me to some samples or right direction? Does xp_filesize and the like the right solution? Help! ...
I think this is best asked in the form of a simple example. The following chunk of SQL causes a "DB-Library Error:20049 Severity:4 Message:Data-conversion resulted in overflow" message, but how come? declare @a numeric(18,6), @b numeric(18,6), @c numeric(18,6) select @a = 1.000000, @b = 1.000000, @c = 1.000000 select @a/(@b/@c) go H...
Sorry for the long question title. I guess I'm on to a loser on this one but on the off chance. Is it possible to make the calculation of a calculated field in a table the result of an aggregate function applied to a field in another table. i.e. You have a table called 'mug', this has a child called 'color' (which makes my UK head hu...
How do I in SQL Server 2005 use the DateAdd function to add a day to a date ...
I've been looking into implementing an external activator in SQL Server Express 2005, and I added the queues, services, contracts, and event notifications to the database. I also added a trigger to send a message to the target queue. Everything parses, runs, and the trigger is firing. However, when I select from the target queue, or use ...
I'm looking for a homegrown way to scramble production data for use in development and test. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I've come up against a wall trying to scramble customer names. I want to keep real names so we can still use or searches so r...
Hi, I am totally new to SQL. I have a simple select query similar to this: SELECT COUNT(col1) FROM table1 There are some 120 records in the table and shown on the GUI. For some reason, this query always returns a number less than the actual count. Can somebody please help me? ...
I basically have an xml column, and I need to find and replace one tag value in each record. ...
I'll go first. I'm 100% in the set-operations camp. But what happens when the set logic on the entire desired input domain leads to a such a large retrieval that the query slows down significantly, comes to a crawl, or basically takes infinite time? That's one case where I'll use a itty-bitty cursor (or a while loop) of perhaps most d...
In SQL Server 2005, is there a way of deleting rows and being told how many were actually deleted? I could do a select count(*) with the same conditions, but I need this to be utterly trustworthy. My first guess was to use the @@ROWCOUNT variables - but that isn't set, e.g. delete from mytable where datefield = '5-Oct-2008' se...
In SQL Server 2005, is there a way for a trigger to find out what object is responsible for firing the trigger? I would like to use this to disable the trigger for one stored prodecure. Is there any other way to disable the trigger only for the current transaction? I could use the following code, but if I'm not mistaken, it would affect...
What is the best way to get the names of all of the tables in a specific database on SQL Server? ...