Scripting carraige return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Scripting carraige return

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-16 : 09:58:46

Hello all. I have written the below script. What i would like to know is how do i script in a carriage return so that instead of:

SELECT 'UPDATE EMPLOYEE_TABLE SET DEPARTMENT = ''', CAST(DeptName AS varchar(10)), ''' WHERE EMPLOY_REF = ', CAST(PayrollNumber AS varchar(10)), ';'
FROM Brookdale_dd...[Brook_Emp_MP$]

I want:

SELECT 'UPDATE EMPLOYEE_TABLE SET DEPARTMENT = ''', CAST(DeptName AS varchar(10)), ''' WHERE EMPLOY_REF = ', CAST(PayrollNumber AS varchar(10)), 'GO'
FROM Brookdale_dd...[Brook_Emp_MP$]

Obviously what i'm doing is taking the result from the above query and pasting that into QA to run. However its complaining as the GO is on the same like as the rest of the command.

Thanks in advance people.

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-16 : 10:06:57

CHAR(13)+char(10)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 10:36:31

Code the GO like this:

'G'+'O'

CODO ERGO SUM

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-16 : 10:40:25

Hi Mark.....do you mean:

SELECT 'UPDATE EMPLOYEE_TABLE SET DEPARTMENT = ''', CAST(DeptName AS varchar(10)), ''' WHERE EMPLOY_REF = ', CAST(PayrollNumber AS varchar(10)), CHAR(13)+char(10), 'GO'
FROM Brookdale_dd...[Brook_Emp_MP$]

If so.......its not working for me mate. in the results pane the GO is still on the same line as the rest of the command.

Cheers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 10:48:58

Change the result set to TEXT mode

Madhivanan

Failing to plan is Planning to fail

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-16 : 10:49:57

Hi.....how do i go about that madhivanan

thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 10:54:25

Press CTRL+T in Query Analyser window

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 10:58:13

[code]
SELECT
'
UPDATE EMPLOYEE_TABLE
SET
DEPARTMENT = '''+CAST(DeptName AS varchar(10)) +'''
WHERE
EMPLOY_REF = '+CAST(PayrollNumber AS varchar(10))+'
G'+'O'
FROM
Brookdale_dd...[Brook_Emp_MP$]

[/code]

CODO ERGO SUM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 11:06:01

What is so special with 'G'+'O'?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 11:12:01

quote:
Originally posted by harsh_athalye

What is so special with 'G'+'O'?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

It prevents Query Analyzer from thinking it is an end of batch, instead of part of a literal.

CODO ERGO SUM

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-16 : 11:15:39

Brilliant stuff...thanks very much guys!

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-16 : 11:23:15

Oh, for the love of...


USE Northwind
GO

SELECT 'UPDATE EMPLOYEE_TABLE SET DEPARTMENT = '''
	+ CAST(OrderId AS varchar(10))
	+ ''' WHERE EMPLOY_REF = '
	+ CAST(EmployeeID AS varchar(10))
	+ CHAR(13) + CHAR(10)
	+ 'GO'
FROM Northwind..Orders

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-04-16 : 13:51:04

"What is so special with 'G'+'O'?"

If GO appears at the beginning of a line, EVEN within a quoted multiline section, Q.A. things it is a statement terminator

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-16 : 13:57:41

quote:
Originally posted by Kristen
If GO appears at the beginning of a line, EVEN within a quoted multiline section, Q.A. things it is a statement terminator

I am pretty sure this quirk is fixed in SSMS.

www.elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 07:20:15

Thanks MVJ and Kristen for that little information on GO.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-17 : 11:15:24

Tools:Options:Connections tab: You can set your batch separator keyword there.

So instead of "GO", you can use "WTF"

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 12:43:41

Cool!

SELECT * FROM MyTable

WTF?

and SQL Server will obligingly provide the answer!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources