September 28, 2016 at 6:43 am
Please see attached. This works fine and gets the Definitions of views saved in a txt file.
One little issue: Take a look at the first highlighted circle ( with red ink )
Why are the 2 words pasted together ( ASSELECT )? When it should have been "AS SELECT " ?
September 28, 2016 at 7:07 am
mw112009 (9/28/2016)
Please see attached. This works fine and gets the Definitions of views saved in a txt file.
One little issue: Take a look at the first highlighted circle ( with red ink )
Why are the 2 words pasted together ( ASSELECT )? When it should have been "AS SELECT " ?
How would we know? This is obviously something you write in SSIS that reads in a text file. We can't see the text file or the details of the step. In essence we have no information to provide an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2016 at 8:45 am
A picture pf the text ( a portion of it ) is attached ( I can not give you the entire file due to security reasons )
As for getting the definition of the view I use the following SQL stmt
select [definition] AS from sys.sql_modules where name = 'XXXXXXXXXX';
Also when you get the definition directly from SSMS ( See attached picture, ssis2.PNG ) it does show proper formatting.
Question: Why is it that only a few views are getting effected ?
September 28, 2016 at 8:59 am
mw112009 (9/28/2016)
A picture pf the text ( a portion of it ) is attached ( I can not give you the entire file due to security reasons )As for getting the definition of the view I use the following SQL stmt
select [definition] AS from sys.sql_modules where name = 'XXXXXXXXXX';
Also when you get the definition directly from SSMS ( See attached picture, ssis2.PNG ) it does show proper formatting.
Question: Why is it that only a few views are getting effected ?
With out seeing the code used to create the files all we can do is guess. My guess, you are forcing a space between the AS and SELECT while concatenating strings.
September 28, 2016 at 9:02 am
mw112009 (9/28/2016)
A picture pf the text ( a portion of it ) is attached ( I can not give you the entire file due to security reasons )As for getting the definition of the view I use the following SQL stmt
select [definition] AS from sys.sql_modules where name = 'XXXXXXXXXX';
Also when you get the definition directly from SSMS ( See attached picture, ssis2.PNG ) it does show proper formatting.
Question: Why is it that only a few views are getting effected ?
I don't know how or why that would happen. I have never seen that from looking at view definitions like that.
A side note, I noticed that you have nested views. That means you have views that are calling other views. This is horrible from a performance standpoint. It makes so much sense from a logical perspective but the optimizer gets totally confused and generates incredibly bad execution plans. Here is a great explanation of this. https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/[/url] (You have to scroll down about half way to find this section).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply