Will not write out the rest of my query

Question

Will not write out the rest of my query

SQL Girl

SSC Veteran

Points: 244
More actions
January 12, 2010 at 8:20 am

#214499

I have written a query na d add to take out the CRLF's but now it stops and will not export the rest of the query to excel.
REPLACE(RTRIM(dbo.Table Name.Column),char(13)+ Char(10),'') AS 'Column1',
RTRIM(dbo.Table Name.Column) AS 'Column2'
Can anyone tell me why it is not exporting the next row?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There are lots of people here that can help you. However you first need to help us help you. You need to post some table definitions, inserts for some temp data, and what you want that data to look like. It is impossible to tell what is wrong with a snippet of a query.

_______________________________________________________________

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/

SQL Girl SSC Veteran Points: 244 More actions · Answer 2

SELECTDISTINCT

dbo.MyTable.MyColumn AS 'Column1',

RTRIM(dbo.MyTable.MyColumn ) AS 'Column2',

RTRIM(dbo.MyTable.MyColumn ) AS Column3',

Replace(Rtrim(Ltrim(Replace(replace(dbo.MyTable.MyColumn , Char(13),''),Char(10),''))),',','') AS 'Column4',

--REPLACE(RTRIM(dbo.MyTable.MyColumn ), Char(13)+ Char(10),'') AS 'Column4',

RTRIM(dbo.MyTable.MyColumn ) AS 'Column5',

RTRIM(dbo.MyTable.MyColumn ) AS 'Column6',

dbo.MyTable.MyColumn AS 'Column7',

dbo.MyTable.MyColumn AS Column8',

ISNULL (dbo.MyTable.MyColumn ,'') AS 'Column9',

ISNULL (dbo.MyTable.MyColumn ,'') AS 'Column10',

dbo.MyTable.MyColumn AS 'Column11',

dbo.MyTable.MyColumn AS 'Column12',

dbo.MyTable.MyColumn AS 'Column13',

dbo.MyTable.MyColumn AS 'Column14',

dbo.MyTable.MyColumn AS 'Column15',

dbo.MyTable.MyColumn AS 'Column16',

dbo.MyTable.MyColumn AS 'Column17'

FROMdbo.MyTable(NOLOCK) INNER JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn LEFT OUTER JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn LEFT OUTER JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn LEFT OUTER JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn LEFT OUTER JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn

LEFT JOIN

dbo.MyTable(NOLOCK) ON

dbo.MyTable.MyColumn = dbo.MyTable.MyColumn

Where dbo.MyTable.MyColumn in (13,54,92,111,61,60)

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I don't quite understand the problem. You say:

"...Can anyone tell me why it is not exporting the next row? ..."

Does that mean 1 entire row (all fields) is ok, then it stops ?

As Sean said: "...You need to post some table definitions, inserts for some temp data, and what you want that data to look like...." In other words, what does the raw data look like, and what should it look like afterwards.

SQL Girl SSC Veteran Points: 244 More actions · Answer 4

When running this job it stops writing to excel where I have;

Replace(Rtrim(Ltrim(Replace(replace(dbo.MyTable.MyColumn, Char(13),''),Char(10),''))),',','') AS 'Column4', ---this one exports

After this line it will not export the rest of the query, if I place it at the bottom everything will export but I need it in column4.

Make sense?

And if I run it in Query Analyzer it shows that nothing is wrong.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

homebrew01

SSC Guru

Points: 55548

January 12, 2010 at 6:36 pm

#1102846

Well .... ?

WayneS SSC Guru Points: 95461 More actions · Answer 6

SQL Girl,

I have three thoughts on this:

1. Despite repeated requests, you have yet to provide any DDL / DML (CREATE TABLE / INSERT) statements to demonstrate this problem. This will help out all of us non-paid volunteers tremendously. Please read the first link in my signature for more details about this.

2. I see that in addition to removing the CR/LF, you are also removing commas. This is the only column where you are removing anything. This makes me wonder if there is something else in this column that is causing things to screw up. Again, if we had some DDL/DML, we could discern this.

3. Have you tried pumping the data from the select into a temp table, and from there pushing it to Excel?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2