SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 has anyone noticed?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/29/2005 :  19:07:21  Show Profile  Reply with Quote
As others have stated, a place for some sticky topics would be good, but it should probably be moderated.

Suggested topics:
1. How to post a question and other info for a new poster.
2. Links to other resources: MSDN, BOL, blogs, training, etc.
3. Answers to really common questions: date formatting, setting up backups, restores, etc.
4. Rules of the road: forum manners

I'm sure other people can come up with a lot of suggestions.



quote:
Originally posted by graz
I've been thinking about a pretty serious forum reorg based around SQL Server 2005. I think I will try a beginners forum and see how it works.

I have been following this thread with some interest as I've seen the same things every one else has. I'm not sure the best way to return to those days -- or if it's even possible. But I certainly appreciate everyone's thoughts on it.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.



CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2005 :  05:42:15  Show Profile  Reply with Quote
"Answers to really common questions"

I would suggest links to [old] threads that have the answers. That way the "Sticky" is relatively short - otherwise trying to find details of "How do I handle CSV" will mean wading through lots of posts, and follow-ups.

My thinking for this has come from an Astronomy forum that I frequent (my other Anorak interest!). In their beginners forum they have sticky's for:

Using your telescope more effectively (aka "Seeing more")
Best of the Beginner's Forum & How-Tos (bunch of links)
What Scope(s) Do You Have?? (that is too TOO sad!)
Useful external web links
Wondering which forum to post to?
Astro[nomy] Glossaries & FAQs

If you feel the need for a peep here's the link:
http://www.cloudynights.com/ubbthreads/postlist.php/Cat/0/Board/beginners

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2005 :  06:12:54  Show Profile  Reply with Quote
"I'm not sure the best way to return to those days -- or if it's even possible"

I'm not sure if this is helpful in finding an answer, but the Cloudy Nights [astronomy] forum I just mentioned seems to be a really friendly place. Couple of observations:

All the stalwarts post "Welcome to Cloudy Nights" when then see someone posting "Post No 1" in the Beginners forum - I got a bit fed up with it at first, but then I also did when I heard "Have a nice day" 100 times a day the first time I went to USA - but then I started to realise, in both instances, that it was genuinely meant.

Moderators there come down like a ton of bricks on people who transend the T&Cs. It's a family forum, so I can understand that, and I suppose in their parlance a whole load of "My scope is better than yours" or more specifically "I heard on the grapevine that Manufacturer X is rubbish" is like "Home work" or "Can't be bothered to read the manual" here.

I don't imagine a bunch of moderating of the sort "Homework question deleted" is feasible - peoples time and also determining if it IS homework and so on and so forth.

But I think we could do with a standard, non argumentative, way of telling people to "get off the ride"

Now I'm not very good at that, but I did do so the other day when I got fed up, and I was very pleased to see Tara say something similar in another post to the same person - it was reassuring for me to see that I wasn't alone in my view!

All that said I think the Beginners Forum is the best way of moving back to the "good old days".

I would also appreciate a private forum where questions about people's behaviour could be discussed without it being in public, as this is. There again you might not invite me to such a forum!

Having a quick look at the Members list (I appreciate this is "since the beginning of time" and lacks some relevance):

1 post = 9,900 peeps
2-5 = 4,500
6-10 = 1,000
11-20 = 700
21-50 = 500
51-100 = 200
101+ = 200

that's a lot of people asking one question (which assuming some of them include a "Can you clarify" and a "Thank you" might pretty much use up all the 1-5 posters ...

Its not my call Graz, but if it was I'd be keen to encourage them back to ask more than once.

Finally, if you think I can help please ask. Sorry for the long Rabbit

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2005 :  06:17:15  Show Profile  Reply with Quote
Sorry, thought of one more.

Is it possible to use the SwearWords filter in Snitz (or something similar) to make specific substitutions - rather than them all just being converted to "**BEEP**" or somesuch?

I was wondering if "BOL" could be autoMagically converted to [url]LaDiDah[/url] - thus a frequent response of "See BOL", which might be currently be seen as "dismissive" - and might even not be understood by Newbies! - would become a useful time-saver quick-answer.

It takes a while to dig out a relevant link each time ....

Kristen

Edited by - Kristen on 07/31/2005 04:44:32
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/30/2005 :  10:43:15  Show Profile  Visit graz's Homepage  Reply with Quote
Wow! Great set of very practical suggestions. We do have a FAQ (http://www.sqlteam.com/faq.asp) but it hasn't been updated in quite a while. It also isn't very well integrated with the forums. I think it may be time to give it a makeover. A few of you have already posted topics you'd like to see covered. Please continue to post those -- especially with a suggested link for it -- even if the link is off-site (like the SQL tutorials).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/31/2005 :  04:39:09  Show Profile  Reply with Quote
I'm gonna try the "Have a nice day" approach for a while and see what happens ...
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 08/01/2005 :  04:17:38  Show Profile  Reply with Quote
quote:
I would also appreciate a private forum where questions about people's behaviour could be discussed without it being in public, as this is.


How about making this for all those with over say 1000 posts? That would at least show that the people involved had some sort of commitment to the forums

I'm in favour of a beginners forum. I think it might encourage some of the newer members (I include myself here) to give more advice and thereby build there confidence a little

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Edited by - elwoos on 08/01/2005 04:20:59
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  04:18:58  Show Profile  Visit spirit1's Homepage  Reply with Quote
you'll have to get past 1000 first, steve!

Go with the flow & have fun! Else fight the flow
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  04:47:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
This is a list of some stuff (mostly begginer) i've learned here and i've written them down over time...

1. How to update a column with incrementing numbers:

	- whole table (identity stlye)
		declare @table1 table (id int, name varchar(50))
		insert into @table1
		select null, 'text1' union all
		select null, 'text2' union all
		select null, 'text3' union all
		select null, 'text4'
		select * from @table1 
		declare @inc int
		set @inc = 0
		UPDATE @table1 SET @inc = id = @inc + 1
		select * from @table1 
	- groups of data:
		declare @table table (id int, diag int, count1 int, rank int)
		insert into @table
		select 1, 42, 75, null union all
		select 1, 49, 50, null union all 
		select 1, 38, 22, null union all
		select 2, 70, 48, null union all
		select 2, 33, 27, null union all
		select 2, 30, 12, null union all
		select 2, 34, 5, null union all
		select 2, 54, 3, null union all
		select 3, 42, 75, null union all
		select 3, 49, 50, null union all 
		select 3, 38, 22, null 

		declare @cnt int
		set @cnt = 0
		UPDATE 	t1
		SET 	@cnt = rank = 	case when 
					exists (select top 1 id from @table where id<t1.id) and
					not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1)
					then 1
					else @cnt + 1
					end
		from @table t1
		select * from @table
	

2. How to get top N rows for each group?

	Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
	Insert Into @myTable 
	Select 'A', 'a', 2 
	Union All Select 'A', 'b', 52 
	Union All Select 'A', 'c', 14 
	Union All Select 'A', 'd', 46
	Union All Select 'A', 'e', 37 
	Union All Select 'A', 'f', 95 
	Union All Select 'A', 'g', 73 
	Union All Select 'A', 'h', 67 
	Union All Select 'A', 'i', 80 
	Union All Select 'A', 'j', 03 
	Union All Select 'B', 'a', 18 
	Union All Select 'B', 'b', 44 
	Union All Select 'B', 'c', 52 
	Union All Select 'B', 'd', 60 
	Union All Select 'B', 'e', 28 
	Union All Select 'B', 'f', 06 
	Union All Select 'B', 'g', 70 
	Union All Select 'B', 'h', 90 
	Union All Select 'B', 'i', 89 
	Union All Select 'B', 'j', 31
	declare @n int
	Set @n = 5
	Select Cat, subCat, rank
	From @myTable as A
	Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
	Order By Cat, Rank Desc	
	

3. How to pivot/cross tab/transpose data?

	http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
	

4. How to make a search with multiple optional arguments?

   	SELECT 	... 
   	WHERE 	(((Col1 = @Col1) or (@Col1 is null)) 
   		and ((Col2 = @Col2) or (@Col2 is null))
   		and ((Col3 = @Col3) or (@Col3 is null)) and ...) and (other conditions)
	

5. How to put a column into a delimited form?

	use northwind
	Declare @ColumnList varchar(1000)
	SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
	FROM INFORMATION_SCHEMA.Columns
	WHERE table_name = 'Orders' 
	

6. How to export results of a stored procedure to a txt file?

	Run this in the DOS command line
		- arguments are case sensitive
		osql /U sa /P password /d pubs /S Server9 /Q "sp_help" -o ofile.txt
	

7. how to reset an identity column?

	DBCC CHECKIDENT('TableName', RESEED, 'StartValue')
	or 
	Truncate table TableName
	


8. Parsing delimited words from a column:

	Create table myTable (myCol varchar(50), Col1 varchar(10), Col2 varchar(10), 
	col3 varchar(10), col4 varchar(10), col5 varchar(10))

	Insert Into myTable Values ('AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null)
	Insert Into myTable Values ('AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null)

	declare @i1 int
	declare @i2 int
	declare @i3 int
	declare @i4 int

	update myTable set
		@i1 = charindex('|', myCol),
		col1 = left(myCol, @i1-1),
		@i2 = charindex('|',myCol,@i1+1),
		col2 = substring(myCol, @i1+1, @i2-@i1-1),
		@i3 = charindex('|',myCol, @i2+1),
		col3 = substring(myCol, @i2+1, @i3-@i2-1),
		@i4 = charindex('|',myCol, @i3+1),
		col4 = substring(myCol, @i3+1, @i4-@i3-1),
		col5 = substring(myCol, @i4+1, 50)
	select * from myTable
	

9. How to get Nth max value?

	delcare @N int
	set @N = 5
	Select 	* 
	From 	Employee E1 
	Where 	(@N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
	


10. How to use a variable in an IN part of where clause?

	- declare @values nvarchar(50)
	  set @values = ',2,3,4,'
	  select * from Table1 WHERE charindex(',' + TableId + ',', @values) > 0
	- use dbo.Split() function
	

11. How to get a random row from a table?

	- select top 1 columns....
	  from table
	  order by newid()
	- choosing between first 20 rows
	  set ROWCOUNT 20
	  select top 1 *
	  from (Select * from table)
	  order by newid()
	  set ROWCOUNT 0
	

12. How to LTrim any character from a value?

	- SUBSTRING(@mystring, CHARINDEX(LEFT(REPLACE(@mystring, @char, ''),1), @mystring), LEN(@mystring))
	


13. How to dynamicaly rank rows?

	- select rank=count(*), a1.au_lname, a1.au_fname
	  from authors a1, authors a2
  	  where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
	  group by a1.au_lname, a1.au_fname
	  order by 1
	- groups:
	  Declare @data table(idn int, diag int, recCount int)
	  
	  insert into @data
	  Select 1, 42, 75 union
	  Select 1, 49, 50 union
	  Select 1, 38, 22 union
	  Select 2, 70, 48 union
	  Select 2, 33, 27
	  
	  select a.*,
	     (select count(*) 
	      from @data b 
	      where a.idn = b.idn and a.RecCount >= b.RecCount) as Rank
	  from 
  	  @data a
	

14. How to get a running total (value in current row = sum of all previous values)?

	- SELECT DayCount, Sales, Sales+COALESCE((SELECT SUM(Sales) 
	                      			  FROM Sales b 
	                      			  WHERE b.DayCount < a.DayCount),0)
	                         AS RunningTotal
	  FROM Sales a
	  ORDER BY DayCount
	

15. How to get just date or just time from a datetime value?

	- just date: 	SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
	- just time:	SELECT DATEADD(d, -DATEDIFF(d, 0, GetDate()), GetDate())
	

16. how to get a number of repeating chars in a string?

	- select len(@str) - len(replace(@str, @delimiter, ''))
	

17. How to multiply all of the values in a column?

	- SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(col,0))))),0),0) AS INTEGER) AS output_value FROM @mytable
	- set nocount on
	  declare @mytable table (col smallint)
	  insert @mytable(col) select 6 union select -7 union select 7 union select null union select 2
   	  declare @x bigint
 	  set @x = 1
	  select @x = @x * col from @mytable where coalesce(col,0) > 0
	  select col from @mytable
	  select @x as positive_product
	

18. Split function:

	CREATE FUNCTION dbo.Split
	(
		@RowData nvarchar(2000),
		@SplitOn nvarchar(5)
	)  
	RETURNS @RtnValue table 
	(
		Id int identity(1,1),
		Data nvarchar(100)
	) 
	AS  
	BEGIN 
		Declare @Cnt int
		While (Charindex ( @SplitOn,@RowData)>0)
		Begin
			Insert Into @RtnValue (data)
			Select 
				Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

			Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
		End

		Insert Into @RtnValue (data)
		Select Data = ltrim(rtrim(@RowData))

		Return
	END
	


Go with the flow & have fun! Else fight the flow
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 08/01/2005 :  05:02:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well Mladen
Why dont you blog these?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  05:04:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
i have no idea...
maybe i should...

EDIT:
blogged:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 08/01/2005 05:46:37
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 08/01/2005 :  08:18:06  Show Profile  Reply with Quote
quote:
you'll have to get past 1000 first, steve!


Spirit - that's the point


steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/01/2005 :  10:12:23  Show Profile  Reply with Quote
What'd I miss?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  10:15:41  Show Profile  Visit spirit1's Homepage  Reply with Quote
so what did you miss, brett???
where have you been anyway?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  10:30:05  Show Profile  Reply with Quote
"What'd I miss?"

A fight broke out over the uselessness of the visitors, and all the regulars first got huffy, then went on strike. I mean - fancy people wanting help, eh?

Graz decided to completely rebuild the site to "Make it just like the old days"

So he created a forum specially for Paul.

Spirit, Rocky, Elwoos and I asked for a Beginners forum to keep all the riff-raff together, and we got nothing. Graz is also not going to provide us with an RSS feed - how do I know? He told me he was amazed how I could read his mind.

I couldn't answer any of the questions in the MS Access forum.

Most of the questions in the SQL Server forums were about Access.

But all the questions asked have been answered correctly, and received a thank you note from the poster.

And all the pigs are fed and ready to fly

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  10:52:59  Show Profile  Visit spirit1's Homepage  Reply with Quote
nicly put...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 08/02/2005 :  03:41:10  Show Profile  Reply with Quote
Kristen - you're on good form these days think you need a holiday, how about a nice cruise



steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 08/02/2005 :  04:11:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Kristen - you're on good form these days think you need a holiday, how about a nice cruise

Yes.
He is fully thinking SQL in mind

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2005 :  04:29:38  Show Profile  Reply with Quote
SQL in mind, SQL in body

For you lot that didn't study Latin that's an updated, modern, translation of "Mens sana in corpore sano"

"how about a nice cruise"

"nice cruise" sounds like an oxymoron to me! just come back from a nice weekend in France though, I do find France like England should have become - what's the French for "Laisser Faire"? .

No 1 Daughter [Year 7] is going to spend next summer term down in the South of France. Sounds like a good excuse to rent a villa down there with a decent broadband connection for 9 or 10 weeks

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/02/2005 :  06:00:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
south of france??? uuuu.... hmm... can i drop by?

Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 08/02/2005 06:04:23
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000