April 2, 2007 at 1:12 pm
Hello,
I have been trying to get the results of my statement in a SP to go to a file using BCP
What I have is:
Create Proc ARDoc_Extraction @Date char(8), @Filename varchar(100)
as
DECLARE @bcp AS VARCHAR(5000)
DECLARE @Values AS VARCHAR(20)
set @values = "('IN', 'DM', 'CM')"
SELECT @bcp = 'bcp "select d.* from
customer as c with (nolock)
join ardoc as d with (nolock) on c.custid = d.custid
where
d.docType in ' + @Values + ' and
d.LUPD_DateTime =' + @Date + ' " QUERYOUT "C:\' + @Filename + '.txt" -T'
PRINT @bcp
I used the @values because the system wouldn't parse 'IN' when included within the Select statement
When I run this with a couple of values @bcp =
bcp "select d.* from
customer as c with (nolock)
join ardoc as d with (nolock) on c.custid = d.custid
where
d.docType in ('IN', 'DM', 'CM') and
d.LUPD_DateTime =20070328 " QUERYOUT "C:\test.txt" -T
If I run this I get the message:
The identifier that starts with '...........' is too long. Maximum length is 128
I also get an error with regards the QUERYOUT piece but I'll get to that if I can get the first bit sorted.
Could someone please tell me if I am going about this the wrong way?
Ideally what we would like is for the file to be created with a name ending in the date it was ran - like the SQL Maintenance Plans name backup files.
Sincerely I would appreaciate any input with regards this
Thanks
Ian
April 2, 2007 at 1:50 pm
bcp "exec ReportProcedure @RepTime = 20070328" QUERYOUT "C:\test.txt" -T
_____________
Code for TallyGenerator
April 2, 2007 at 3:22 pm
Hello and thanks for the reply;
Are you telling me to create two stored procs suchs as:
1)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Proc ARDoc_CGS @Date1 char(8), @Date2 char(8)
as
select d.* from
customer as c with (nolock)
join ardoc as d with (nolock) on c.custid = d.custid
where
d.docType in ('IN', 'DM', 'CM') and
d.LUPD_DateTime BETWEEN @Date1 AND @Date2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
2)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Proc ARDoc_CGSBCP @SDate1 char(8), @SDate2 char(8)
as
DECLARE @bcp AS VARCHAR(8000)
SET @bcp = 'bcp "exec ReportProcedure' + @SDate1, @SDate2 + '" QUERYOUT "C:\" -T'
PRINT @bcp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My second proc gives an error when parsed:
Server: Msg 170, Level 15, State 1, Procedure ARDoc_CGSBCP, Line 5
Line 5: Incorrect syntax near ','.
I apologize, this is more than I am usually asked to do.
Thanks so much for your help
Ian
April 2, 2007 at 4:09 pm
Watch your syntax.
No matter how smart is the solution syntax must be correct:
SET @bcp = 'bcp "exec ReportProcedure' + @SDate1 + ', ' + @SDate2 + '" QUERYOUT "C:\" -T'
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply