November 1, 2010 at 9:49 am
I have some valid T-SQL that's tested and works as a passthru query to SQL server 2005 R2 using a passthru query in MS Access 2007.
Now that it works, I'd like to create view. However, when I paste the code in a view window in SQL Server Management Studio (2008 R2), management studio adds table aliases that I don't want or need.
Here's a stripped down example. This is is the code I pasted:
[font="Courier New"]WITH
CTE_ACCOUNT AS (
SELECT
ID,
DESCRIPTION
FROM lbpl.ACCOUNT
WHERE CODE='123456789'
)
SELECT
CTE_ACCOUNT.ID,
CTE_ACCOUNT.DESCRIPTION,
lbpl.FINANCE_FACT.VALUE
FROM lbpl.FINANCE_FACT
INNER JOIN CTE_ACCOUNT
ON lbpl.FINANCE_FACT.ACCT_ID = CTE_ACCOUNT.ID[/font]
Here's what management studio does to it:
[font="Courier New"]WITH CTE_ACCOUNT AS (SELECT ID, DESCRIPTION
FROM lbpl.ACCOUNT
WHERE (CODE = '123456789'))
SELECT CTE_ACCOUNT_1.ID, CTE_ACCOUNT_1.DESCRIPTION, lbpl.FINANCE_FACT.VALUE
FROM lbpl.FINANCE_FACT INNER JOIN
CTE_ACCOUNT AS CTE_ACCOUNT_1 ON lbpl.FINANCE_FACT.ACCT_ID = CTE_ACCOUNT_1.ID[/font]
CTE_ACCOUNT as been aliased as CTE_ACCOUNT_1, even though there's no need for it. If it were done consistently, it would not be a problem. However, in the real, much larger SQL, some references are not changed, which produces numerous "multi-part identifier . . . could not be bound" errors.
Is there any way that SQL can be pasted into a query or view designer in Management Studio without these aliases being added?
Thanks.
November 1, 2010 at 10:53 am
That's a known issue with this view gui.
So, don't use the create view window, but open a Query window, paste your code and type "create view yourschema.yourname as " on top of your code.
Double check you are connected to the correct database !!!
run the code and off you go.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 1, 2010 at 11:07 am
I agree with Johan here. You have to work around this yourself by writing your own header.
November 1, 2010 at 11:30 am
Thank you. That works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply