wolfkillj (8/9/2012)

---

One way to see how much this issue is affecting your database would be to rewrite queries that join views to return the same results by querying the underlying...

Rather than using a Dynamic SQL query that substitutes in the table you want to SELECT FROM, why not instead consider using IF/ELSE IF/END with static SQL queries?

To quote Jeff Moden's signature:

First step towards the paradigm shift of writing Set Based code:

Stop thinking about what you want to do to a row... think, instead, of what...

Jeff Moden (8/9/2012)

---

dwain.c (8/9/2012)

---

Jeff Moden (8/9/2012)

---

Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the...

CELKO (8/9/2012)

---

This is design is wrong. A Chart of Accounts keeps its hierarchy in the encoding; think of the Dewey Decimal system. Your design flaw is called "Attribute Splitting" and...

Brandon Carl Goodman (8/9/2012)

---

My friend, if you are ever in the Des Moines, Iowa area, I owe you a beer or a glass of wine, whatever your preference may be....

Brandon Carl Goodman (8/9/2012)

---

If Reference_Account_Number_2 is 0 this would be a parent account as there are no other accounts associated with it at this point. A child account would...

In thinking about it a little further and a little clearer, I think what you need is this:

1. Anchor 1: Resolve parents --> children

2. Anchor 2: Resolve children (that are...

;WITH rCTE AS (

-- Anchor 1: ...

SELECT ...

UNION ALL

-- Anchor 2: ...

...

I can get you started but I have a question. Your rCTE looked close but I've made some modifications that I'll explain in a minute. First a revised...

This might work for you:

CREATE TABLE #Test1

( [ID] INT IDENTITY(1,1) NOT NULL,

[Trans] BIGINT NOT NULL,

[SubTrans] BIGINT NOT NULL,

[Date] VARCHAR(20) NOT NULL

)

INSERT INTO #Test1([Trans],[SubTrans],[Date])

SELECT 1,2,'09/2012'

UNION ALL SELECT 1,3,'09/2012'

UNION...

Something like this should get you close:

DECLARE @Languages XML

SET @Languages =

'<Languages>

<Language>

<LanguageId>9</LanguageId>

<ProficiencyId>4</ProficiencyId>

...

Interesting and you're right it doesn't throw a syntax error!

You can follow the comma with more columns or something like this:

PRIMARY KEY ([DateID]

Jeff Moden (8/9/2012)

---

Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the same question, I...

Here's a couple of ways, obviously fraught with hazards if you don't have a lot of control over the creation of the filenames:

DECLARE @Table TABLE (filename VARCHAR(200))

DECLARE @CurrentYear CHAR(4) =...