getting error - 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 2008 Forums

Transact-SQL (2008)

getting error

Author

Topic

hardikkumar
Starting Member

14 Posts

Posted - 2013-11-18 : 14:16:50

hardik

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-18 : 14:36:30

Run this: PRINT @sqlCommand

And show us what it prints.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-18 : 16:02:25

I don't see -d listed in the bcp help. Could you remove "-d Train" and instead use "from Train.dbo.ledger, Train.dbo.ledcode"?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-18 : 17:03:02

No, that's not what I meant.

SET NOCOUNT OFF

DECLARE @sqlCommand VARCHAR(1000)

SET @sqlCommand = 'bcp "select
b.lmatter ,
convert(char(10),max(ltradat),101) lbilldt,

(select convert(char(10),max(ltradat),101)
from Train.dbo.ledger, Train.dbo.ledcode where lmatter = b.lmatter
and ldocumnt is not null
and lzero != ''R''
and llcode = lccode
and lccollhs = ''Y'') lpaydt
from Train.dbo.ledger b, Train.dbo.ledcode
where ldocumnt is null and lzero != ''R''
and llcode = lccode
and lcfco != ''I''
group by lmatter
order by lmatter"
queryout C:\Temp\TextFile_Export.txt -S TBACDBTST -t"|" -c -T'

PRINT @sqlCommand
EXEC master..xp_cmdshell @sqlCommand

GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-18 : 17:19:22

Oh it's because of your hard returns! @sqlCommand must be one continuous query without any line breaks. Line breaks will cause cmd to think it's a new command.

DECLARE @sqlCommand VARCHAR(1000)

SET @sqlCommand = 'bcp "select b.lmatter , convert(char(10),max(ltradat),101) lbilldt, (select convert(char(10),max(ltradat),101) from Train.dbo.ledger, Train.dbo.ledcode where lmatter = b.lmatter and ldocumnt is not null and lzero != ''R'' and llcode = lccode and lccollhs = ''Y'') lpaydt from Train.dbo.ledger b, Train.dbo.ledcode where ldocumnt is null and lzero != ''R'' and llcode = lccode and lcfco != ''I'' group by lmatter order by lmatter" queryout C:\Temp\TextFile_Export.txt -S TBACDBTST -t"|" -c -T'

PRINT @sqlCommand
EXEC master..xp_cmdshell @sqlCommand

GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

hardikkumar
Starting Member

14 Posts

Posted - 2013-11-19 : 09:40:14

thanks works fine how woould i run this same query using bcp from command propmt??

hardik

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-19 : 13:02:40

Why did you delete all of your replies and erased your original post? I do have the answer to your question, but I'm hesitant to help someone that does things like this. Leaving your posts helps people in the future.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

hardikkumar
Starting Member

14 Posts

Posted - 2013-11-19 : 14:22:07

sorry but i amnew user i dont know whats wrong with my account. sorry

hardik

hardikkumar
Starting Member

14 Posts

Posted - 2013-11-19 : 14:23:10

can you please tell me how can i create batch file which export data to text file ?please help

hardik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources