Recent content by PerlyGates

Thanks folks. What I didn't reveal (because I didn't think it relevant) was that I was running that code on SQL SERVER 2005 Express Edition SP2 (9.00.1399.06). So if I run the following SELECT on SQL SERVER Express ... SELECT TOP 5 CASE WHEN RAND() < .5 THEN RAND() - 1...

No - thankyou.

Hi George >>When I run your code, all the dates are the same. Is that on 2000?? I've just ran it on 2000 and all the dates are indeed the same. But on 2005 they are all different - I didn't know or would have thought there would have been a difference. Or is it to do with default settings...

The following will give you a range between 0.0 and 1.0 SELECT RAND() The following will give you a range between 0.0 and 10000 SELECT RAND() * 10000 The following will add up to 10000 days (~27 years) onto the current date SELECT GETDATE() + (RAND() * 10000) To subtract dates you...

Is this what you want? DECLARE @tv1 TABLE ( col_A varchar(10), col_B varchar(10), col_C int ) INSERT INTO @tv1 SELECT '123' ,'RED', 4 UNION ALL SELECT '234' ,'BLUE', 2 UNION ALL SELECT '123' ,'RED', 7 UNION ALL SELECT '345' ,'GREEN', 1 UNION ALL SELECT...

Hi Mike As kaht has so kindly supplied a ready-made solution I'd go with that (assuming SQLSister's suggestion is a non-runner). Not only is kaht's iterative solution easier to read and understand it will be "blisteringingly" faster than my donkey code. Apologies for leading you down a bit of...

... yeah I was afraid of that - whats the largest number of occurrences of any one ActBrch? How many rows are in the tables (as they seem pretty big - banking data and all that)? As I outlined at the outset it is quite probable that this query will just be too slow to make it anyway feasible...

Try this ... WITH t2( ActBrch, myCount,[SVCID], myLength ) -- Populate you're CTE t2 AS ( SELECT ActBrch ,COUNT(*) OVER (PARTITION BY ActBrch) ,CAST( [SVCID] AS VARCHAR(100)) ,1 FROM [400_Metafile].[dbo].[yad_srvmast] UNION ALL SELECT...

Hi Mike - if you want to post up all field names/table names etc. (or abbreviations/aliases of in the interests of privacy) together with their datatype definitions e.g. char,varchar,int etc I'll plug them into my code for you and re-post.

Hi Mike The following will work using recursion - however BE WARNED - it will be VERY slow (even the initial population of the CTE - common table expression - not to mention the actual UPDATE ) and its not fully tested. Bare in mind that it's not as complicated as it looks (famous last words)...

nickdel - yes you do have to set up the link to the other server - sorry my fault but I thought that was obvious. And yes you'll need permissions. TysonLPrice - you're query is the wrong way around it should be (assuming you have the link to the other server set up): SELECT * INTO newTABLE...

Yes you can. Qualify your server name with square brackets e.g. SELECT * INTO newTable FROM [Server].database.owener.table

This should get you started ... DECLARE @myColumn varchar(50) SET @myColumn = 'id' SELECT OBJECT_NAME(c.id) AS ObjectName ,CASE type WHEN 'P' THEN 'Stored Proc' WHEN 'RF' THEN 'Replication Filter Stored Proc' WHEN 'X' THEN 'Extended Stored Proc'...

Hi Mike - are you using 2000 or 2005? -PG

Running the following: INSERT INTO @WM_MultiCenterIntegration SELECT 'MC00068668', 1093967711 UNION ALL SELECT 'MC00069194', 1094047234 UNION ALL SELECT 'MC00069227', 1094067911 UNION ALL SELECT 'MC00069300', 1094103666 UNION ALL SELECT 'MC00069384', 1094128248 select alarm_ID...