Syntax Question for SQL Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax Question for SQL Query

Author

Topic

j_mact
Starting Member

17 Posts

Posted - 2003-06-10 : 12:16:41

Hi All,
For some reason, this query is getting the best of me. Here's the syntax:
declare @bcpCommand varchar(2000)
set @bcpCommand = 'bcp "select * from students where lastname =' + @laName + '" queryout c:\sims\result.txt -U sisapp -P sisapp -c'
exec master..xp_cmdshell @bcpCommand

(@laName is a parameter I have already declared)

Problem is, when I try put quotes around @laName, I keep getting an error when I try to run it in SQL Analyzer. (The syntax says it's okay but it's not.) Can anyone tell me where I am going wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-10 : 12:22:22

The best way to troubleshoot this it to PRINT out the @bcpCommand. So instead of executing it, put PRINT @bcpCommand. Check to make sure that the single quotes are in all the correct places. Also, run the @bcpCommand by copying the PRINT statement output and pasting it into a cmd window.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-10 : 13:07:31

Well,

1st. You're not using the "-S" parameter..I think you need to identify the server...
2nd. I wouldn't reccomend posting your login id AND password..
3rd. Give this a try:


USE Northwind
GO

DECLARE @bcpCommand varchar(2000), @CustomerId varchar(50)

SET @CustomerId = 'ERNSH' 

SET @bcpCommand = 'bcp "SELECT * FROM Orders WHERE CustomerId ='
		+ '''' 
		+ @CustomerId
		+ '''' 
		+ '" queryout c:\tax\Test.txt -S\servername\instance -U   -P  -c'

SELECT @bcpCommand
 
exec master..xp_cmdshell @bcpCommand

Brett

8-)

Edited by - x002548 on 06/10/2003 13:08:11

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-10 : 14:01:18

Try

set @bcpCommand = 'bcp "select * from students where lastname =''' + @laName + '''" queryout c:\sims\result.txt -U sisapp -P sisapp -c'

Will default to the local server but as Tara says it's always safer to put in the servername.

Thanks for giving us your username and password

.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

j_mact
Starting Member

17 Posts

Posted - 2003-06-10 : 14:02:45

Thanks for the advice! That account is a dummy account I'm using on a test db.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-06-10 : 14:05:48

Hey guys, it could be worse....it could be sa/blank!!
j_mact, good call on using the dummy account!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources