SQLServerCentral / Discuss Content Posted by Jeff Moden / Article Discussions / Article Discussions by Author

Tally OH! An Improved SQL 8K “CSV Splitter” Function

Jeff Moden — Sun, 01 May 2011 09:02:31 GMT

Comments posted to this topic are about the item [B]Tally OH! An Improved SQL 8K “CSV Splitter” Function[/B]

Generating Test Data: Part 1 - Generating Random Integers and Floats

Jeff Moden — Sun, 25 Mar 2012 17:26:34 GMT

Comments posted to this topic are about the item [B]Generating Test Data: Part 1 - Generating Random Integers and Floats[/B]

Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Tue, 24 Apr 2012 21:40:48 GMT

Comments posted to this topic are about the item [B]Generating Test Data: Part 2 - Generating Sequential and Random Dates[/B]

Hidden RBAR: Counting with Recursive CTE's

Jeff Moden — Mon, 08 Aug 2011 00:13:18 GMT

Comments posted to this topic are about the item [B]Hidden RBAR: Counting with Recursive CTE's[/B]

Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Jeff Moden — Wed, 28 Mar 2012 21:51:33 GMT

Comments posted to this topic are about the item [B]Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)[/B]

Formatting Dates with 3 Character Months (SQL Spackle)

Jeff Moden — Wed, 19 Jan 2011 21:37:57 GMT

Comments posted to this topic are about the item [B]Formatting Dates with 3 Character Months (SQL Spackle)[/B]

Calculating Work Days

Jeff Moden — Tue, 04 Jan 2005 15:19:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp

Hey folks... I'm not sure why they decided to repost this old article, but before you make any comments, please realize this...

The name of the article should have been "Calculating WEEKDAYS in the U.S.A".

REPLACE Multiple Spaces with One

Jeff Moden — Sat, 14 Nov 2009 12:21:47 GMT

Comments posted to this topic are about the item [B]REPLACE Multiple Spaces with One[/B]

Query Tuning

Jeff Moden — Mon, 26 Dec 2011 22:16:57 GMT

Comments posted to this topic are about the item [B]Query Tuning[/B]

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Jeff Moden — Tue, 30 Nov 2010 21:00:58 GMT

Comments posted to this topic are about the item [B]Why doesn’t ISNUMERIC work correctly? (SQL Spackle)[/B]

Group Islands of Contiguous Dates (SQL Spackle)

Jeff Moden — Mon, 06 Dec 2010 21:36:25 GMT

Comments posted to this topic are about the item [B]Group Islands of Contiguous Dates (SQL Spackle)[/B]

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Jeff Moden — Wed, 14 Oct 2009 02:11:35 GMT

Comments posted to this topic are about the item [url=http://www.sqlservercentral.com/articles/T-SQL/68467/][b]Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)[/b][/url]Comments posted to this topic are about the item [url=http://www.sqlservercentral.com/articles/T-SQL/68467/][b]Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)[/b][/url]*** NOTICE *** This article is in the process of (hopefully) the final "rewrite". *** NOTICE *** In the meantime, you'll be very interested to know that Paul White and Tom Thompson have a method of error checking that not only checks for errors virtually without any additional overhead, the method they came up with also helps further guarantee the correct sort order for the updates. You can find an example of the coded method at the following URL which will lead to you a post very late in this thread...[url]http://www.sqlservercentral.com/Forums/FindPost981258.aspx[/url]

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Jeff Moden — Tue, 06 May 2008 21:42:30 GMT

Comments posted to this topic are about the item [B]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/B]

Displaying Sorted Hierarchies (SQL Spackle)

Jeff Moden — Wed, 09 Mar 2011 21:44:03 GMT

Comments posted to this topic are about the item [B]Displaying Sorted Hierarchies (SQL Spackle)[/B]

Create a Tally or Numbers Table

Jeff Moden — Wed, 12 Mar 2008 01:15:04 GMT

Comments posted to this topic are about the item [B]Create a Tally or Numbers Table[/B]

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Jeff Moden — Mon, 18 Aug 2008 22:59:44 GMT

Comments posted to this topic are about the item [url=http://www.sqlservercentral.com/articles/T-SQL/63681/][font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]Folks, Part 2 of this article can be found at the following link...[url=http://www.sqlservercentral.com/articles/cross+tab/65048/][font="Arial Black"](click here) Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/font][/url]

hi new project help with sql and creating a free money style script

jamied_uk — Wed, 12 Oct 2011 10:57:53 GMT

i have a user database setup and have uploaded the sql structure to jnet2012.0fees.net/attachmentsi would like the sql to be so that another table called balance is there and php files to add a text box input of the amount and also a selection box so i can select who balance is edited once these simple funtions are in place i can extend it later for when a visitor logs in or visits a page there session logged in user name will be used to add x amount of free credits to there acount balancelater will be free things as gifts and also gifts to send to other users etc.this script i feel will be very usefull for all web masters wanting new guests to sitesmy skills are very limited and get stuck on syntax etc any one help me at all would be very happy to share files from my site jnet2012.0fees.net and i can share parts from that if you like it and thanks to those who just want to helpi want pages made in php 1 showing all balences in desc ordera page so i can have a input box for the username or from a list somehow with amount edit input box that shows whats currently in that users balenceon a 3rd page i need an example page that when it is included with another php file (index for example) it will give that user a little extra points for using the site for examplethen from this i can add multipliers etc and add bits for games etcany one help me with this i have ideas but no nothing of real syntax when using php and mysql together in this way and want to add to my current database sql is in attachments and hopefully in the same place as users id tables can be added a field called balancei have got this far but cant get passed making these 3 simple pages as described above these are the most important as involves php

Forum Etiquette: How to post data/code on a forum to get the best help

Jeff Moden — Mon, 26 Nov 2007 21:22:52 GMT

Comments posted to this topic are about the item [B]Forum Etiquette: How to post data/code on a forum to get the best help[/B]

And the LINQ version looks like...

rhj4 — Fri, 11 Mar 2011 11:00:19 GMT

After coding the RBAR logic to populate a complex TreeView, I come across this article and say "Where were you a week ago?". I like what I have read, but have no idea how to do this in LINQ and the project rule is that all data access must be done in LINQ. Any translators out there?

Hidden Formatting Troubles with STR() (SQL Spackle)

Jeff Moden — Tue, 14 Dec 2010 20:30:36 GMT

Comments posted to this topic are about the item [B]Hidden Formatting Troubles with STR() (SQL Spackle)[/B]

Expand this to multiple concurrent hierarchies and effective date sensitive?

jerry-995988 — Thu, 10 Mar 2011 09:12:40 GMT

How can I apply this model with a couple of extended properties.Using an employee/manager model, I have added a 'hierarchy code' to both entities and an effective date. How could I incorporate these two additional properties to the process in this discussion?

Need help on Linked server Transactions cauasing Optimistic concurrency and to avoid DTS failure

kiran.konankal — Wed, 02 Mar 2011 05:25:48 GMT

1. My DTS is failing often with the below err: OLE DB provider 'MSDAORA' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDAORA' [b]IOpenRowset::OpenRowset returned 0x80004005[/b]: ]. [SQLSTATE 01000] (Error 7300). The step failed. 20110302 02:30:09 AM [WUPRDDB01A] 2. Executed as user: MYDB\MYLogon. Agent insert start [SQLSTATE 01000] (Message 0) School insert end [SQLSTATE 01000] (Message 0) School Update end [SQLSTATE 01000] (Message 0) School Status change end [SQLSTATE 01000] (Message 0) Notice order end [SQLSTATE 01000] (Message 0) OLD_STUDENTS deleted [SQLSTATE 01000] (Message 0) OLE DB provider 'MSDAORA' could not UPDATE table '[LNKSVRMY]..[CITY].[STUDENT]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized. [SQLSTATE 42000] (Error 7343) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.]. [SQLSTATE 01000] (Error 7300). The step failed.20110301 04:48:01 PM [PRODUCTION01A]I am using SQL Server 2000 and trying to Modify the data at Oracle 10G server. In production the STUDENT is being updated with multiple sources (apart from Sql 2000, it is being updated by some applications as well). We have kept all updates/inserts for that particular table under transactions.I have simulated the scenario by opening two parellel transaction on the same table.and found the error.Session #1SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSACTIONUPDATE LNKSVRMY..CITY.STUDENTSET ACTIVE = 0 WHERE STD_ID = '57FH'OutPut:(1 row(s) affected)Session #2SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSACTIONUPDATE LNKSVRMY..CITY.STUDENTSET ACTIVE = 0 WHERE STD_ID = '57FH'OutPut: No result since other transaction is openSEssion #1Will execute the COMMITSession#2i got the below concurrency problemServer: Msg 7343, Level 16, State 4, Line 3OLE DB provider 'MSDAORA' could not UPDATE table '[LNKSVRMY]..[CITY].[STUDENT]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].On the other hand if the Session #1 run for long without transaction been commited i get the err:Server: Msg 7399, Level 16, State 1, Line 3OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-02049: timeout: distributed transaction waiting for lock]OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80004005: ].Plese let me know any of you faced/know this error and found the solution.

Hidden RBAR: Triangular Joins

Jeff Moden — Wed, 05 Dec 2007 21:17:49 GMT

Comments posted to this topic are about the item [B] [url=http://www.sqlservercentral.com/articles/T-SQL/61539/]Hidden RBAR: Triangular Joins[/url] [/B]First, thank you for reading my article and any feedback that you my post... good or bad. The exchange of ideas is how we all learn and your feedback is certainly a part of that process and is most welcome.Second, there's a bit of a bug in the forum software... when they republish an article, it changes the date on the article. This article was originally published on 2007-12-06. The webmaster has been advised and there's nothing they can do about it for the moment. I appologize if this leads to any confusion and I'd still like to hear your comments even if you may have seen the article before.Third, Yes, I actually did do a followup on the running total problem. The link that follows will take you there, but be advised... although the high speed update method that uses an index hint does always work, don't use the method as a substitute for an ORDER BY on SELECTs. I'm working on a rewrite of the article that explains why and hope to have it out soon. Also be advised that if you don't follow the instructions that I've given to a "T", you could get wrong answers... of course, that would be true of any code, wouldn't it? Here's the link...[url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]Last, but not least, if you're interested in other articles that I've written, please see the following link... I'd be very interested in your comments on those, as well[url]http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/[/url]Thanks again, folks. I'm humbled by all of your responses and the time you spent writing them up.

Dissecting SQL Server Execution Plans

Jeff Moden — Thu, 17 Dec 2009 00:05:19 GMT

Comments posted to this topic are about the item [B]Dissecting SQL Server Execution Plans[/B]

Display More Than 8000 Characters (SQL Spackle)

Jeff Moden — Wed, 26 Jan 2011 21:26:31 GMT

Comments posted to this topic are about the item [B]Display More Than 8000 Characters (SQL Spackle)[/B]

Sorting Months By Number (SQL Spackle)

Jeff Moden — Sat, 13 Nov 2010 13:33:20 GMT

Comments posted to this topic are about the item [B]Sorting Months By Number (SQL Spackle)[/B]

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Jeff Moden — Mon, 12 May 2008 14:50:22 GMT

Comments posted to this topic are about the item [B]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/B]

Extract of tally data base to excel

chakramadhavan — Mon, 30 Aug 2010 08:06:55 GMT

We would like to use data available in tally table for generation of reports in excel. Please letus know if any way we can extract data from tally data baseThanks,Regards.C.Madhavan

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Jeff Moden — Tue, 02 Dec 2008 22:15:33 GMT

Comments posted to this topic are about the item [B]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/B]

Stored Procedures

singhbharat321 — Tue, 22 Jun 2010 12:54:00 GMT

hi all'i am new in sql server database.In my new project mostly i have to work in stored procedure side.This is support project,i have to change the stored procedure script..please provide me some tips in stored procedure then i can resolve any problem. Please give me some link of stored procedure.In SP which types of error we get if you guys any idea please share with me.Thanks in Advance.:-)Bharat

Virtual PM's for Jeff...

RBarryYoung — Tue, 17 Jun 2008 20:51:00 GMT

Jeff: Given that you've found a clever way to block annoying PM's :), I was forced to start this thread so that I could contact you:--======Jeff: Whats a good Split() type function/method for SQL 2005? I need to break downa bunch of syscomments entries into lines and I cannot locate the threads of the big tests that you and Matt did on split functions.Thanks, Barry

[HELP] dynamic header in cross tabs

rah_dix — Tue, 05 Jan 2010 20:29:44 GMT

helo..newbie here...:-Di'm geting mad [img]http://cinta.kaskusradio.com/onion/smiley/stress.gif[/img] [img]http://cinta.kaskusradio.com/onion/smiley/hun.gif[/img]just want to ask :[img]http://img519.imageshack.us/img519/8147/14730416.jpg[/img] please somebody help me..I'll give you guys 'cendol' for better answer :-D:-D, ok.. [img]http://cinta.kaskusradio.com/onion/smiley/2good.gif[/img]thank's b4 for your advice...[img]http://cinta.kaskusradio.com/onion/smiley/bye.gif[/img]

to show row wise data into column wise

syed_ahmedzama — Mon, 17 Aug 2009 01:43:11 GMT

hello,my requirment is that i have 5 rows of data but i want to show it as clomun ex:i have like thisrows jackmackpeterscottsteve i need this type columns jack mack peter scott steve

running total

chandreshgeria — Fri, 20 Nov 2009 02:27:38 GMT

Sir , I think that what you have given is to lengthy process i give procedure of my way how i calculate running total ------------------- drop TABLE #data -- drop temp table if exists CREATE TABLE #data (myctr INT IDENTITY,chrfolioid VARCHAR(20),numgrandtotal NUMERIC(18,2),running NUMERIC(18,2))INSERT INTO #data (chrfolioid,numgrandtotal)SELECT chrfolioid ,numgrandtotalFROM tARHeader t ORDER BY t.chrFolioID ,t.dteTxnDateDECLARE @ctr AS INT DECLARE @running NUMERIC(18,2)DECLARE @oldfolioid AS VARCHAR(20)SET @ctr = 1SET @running = 0SET @oldfolioid = ''WHILE @ctr <= (SELECT COUNT(*) FROM #data)BEGIN IF @oldfolioid <>(SELECT chrfolioid FROM #data WHERE myctr = @ctr) BEGIN SET @running= 0 SET @oldfolioid = (SELECT chrfolioid FROM #data WHERE myctr = @ctr) END UPDATE #data SET running = (SELECT SUM(numgrandtotal) from #data where myctr <= @ctr and chrfolioid = @oldfolioid GROUP BY chrfolioid ) WHERE myctr = @ctr SET @ctr = @ctr + 1 PRINT @ctrEND SELECT * FROM #data ORDER BY 1,2

Calculating the throughput time

DungLy — Thu, 10 Sep 2009 15:34:20 GMT

Sorry, put it in wrong place.

Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

Jeff Moden — Wed, 30 Jan 2008 22:21:24 GMT

Comments posted to this topic are about the item [url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/][font="Arial Black"] Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5 [/font][/url]The new article was published on 10 Nov 2009 and may be found at the following URL...[url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]The new article is basically a rewrite of the old article with some fixes and workarounds for some of the things we've encountered in the last year. A second article is in the works for some of the many uses for the "Quirky Update"... there was just too much in the first article to conveniently fit.

More RBAR and "Tuning" UPDATEs

Jeff Moden — Thu, 13 Mar 2008 21:26:35 GMT

Comments posted to this topic are about the item [B]More RBAR and "Tuning" UPDATEs[/B]

pivot table values help

psmg01 — Tue, 16 Dec 2008 13:42:44 GMT

Hi Jeff,I have a table that has the following values.col1 | col2 | col3 | col41 | val1| val2 | val3 2 | val21| val22 | val23 3 | val31| val32 | val33 what I want is for each col1 entries, I should have the rcol2,col3 and col4 values as col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33Could you please tell me how to do this? Thanks for your help.-Maria

To create dynamic columns in sql 2005

jsgodson-958000 — Thu, 04 Dec 2008 04:02:05 GMT

this is the query i have writtenDECLARE @cols NVARCHAR(2000)SELECT @cols = COALESCE(@cols + ',[' + Contribution.Contribution + ']', '[' + Contribution.Contribution + ']')FROM dbo.Contribution INNER JOIN dbo.ProposalFund ON dbo.Contribution.ContributionId = dbo.ProposalFund.ContributionIdWHERE (dbo.ProposalFund.Descriptionid = 'cement')DECLARE @query NVARCHAR(4000)SET @query = N'SELECT descriptionid, '+@cols +'FROM(SELECT t2.ProposalFundid , t1.Contribution, t2.Amount , t2.DescriptionidFROM Contribution AS t1 JOIN ProposalFund AS t2 ON t1.Contributionid = t2.Contributionid) pPIVOT(sum([amount])FOR contribution IN( '+@cols +' )) AS pvtORDER BY Descriptionid;' EXECUTE(@query)and im getting output asdescriptionid CBV PARTNERCEMENT 1000 NULLCEMENT NULL 2000but i want asdescriptionid CBV PARTNERCEMENT 1000 2000for this what i have to do???? PLz help meHere CBV and partner are there in contribution tabel and 1000,2000 are amount present in proposalfund tabel and cement is present in description of proposalfund

Converting rows to columns

Shree-903371 — Tue, 07 Oct 2008 14:07:16 GMT

Hi, I have two Columns State and City. When I select them from table, it displays like STATE CITY Connecticut Hartford Connecticut Stamford Connecticut New Haven Connecticut BridgeportBut I need them as STATE CITY1 CITY2 CITY3 CITY4 Connecticut Hartford Stamford New Haven BridgeportSo that I can use them in my SP. There are many cities for some states.Can you please help me out by using Pivot?