SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 getting error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hardikkumar
Starting Member

USA
14 Posts

Posted - 11/18/2013 :  14:16:50  Show Profile  Reply with Quote


hardik

Edited by - hardikkumar on 11/18/2013 18:12:35

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/18/2013 :  14:36:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run this: PRINT @sqlCommand

And show us what it prints.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/18/2013 :  16:02:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/18/2013 :  17:03:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/18/2013 :  17:19:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

hardikkumar
Starting Member

USA
14 Posts

Posted - 11/19/2013 :  09:40:14  Show Profile  Reply with Quote
thanks works fine how woould i run this same query using bcp from command propmt??

hardik
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/19/2013 :  13:02:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

hardikkumar
Starting Member

USA
14 Posts

Posted - 11/19/2013 :  14:22:07  Show Profile  Reply with Quote
sorry but i amnew user i dont know whats wrong with my account. sorry

hardik
Go to Top of Page

hardikkumar
Starting Member

USA
14 Posts

Posted - 11/19/2013 :  14:23:10  Show Profile  Reply with Quote
can you please tell me how can i create batch file which export data to text file ?please help

hardik
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000