Using bcp in stored procedure with external input

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

Using bcp in stored procedure with external input

Author

Topic

RJ
Starting Member

11 Posts

Posted - 2011-06-03 : 10:44:00

I am very new to using bcp and need help with the following.

I have a stored procedure that is used by Crystal reports. When running the report the user can enter the date range for the variable date (@StartDate @EndDate). However, the bcp fails to export the data. If I remove these variable dates and have the date entered against @StartDate1 & @EndDate1 then the bcp outputs the data. Here is a copy of my SP.

ALTER PROCEDURE [dbo].[A001_SVC_DATES]
--StartDate and EndDate are for inputting the date range via Crystal.
(
@StartDate Datetime,
@EndDate Datetime
)
AS

Declare @sql varchar(500);
Declare @StartDate1 datetime;
Declare @EndDate1 datetime;

set @Startdate1 = @StartDate --'2010-08-30 00:00:00.000';
set @Enddate1 = @EndDate --'2010-08-31 00:00:00.000';

select cust.custno, cust.fname, cust.lname, cust.addr1, cust.addr2, cust.addr2e, cust.addr3, cust.po, cust.postcd,
cust.email, cust.htel, cust.wtel, cust.tel3, cust.consent, cust.nocontact,

vehi.licno, vehi.serialno, convert(varchar,vehi.fregd, 103) as RegDate, convert(varchar,vehi.inspecd, 103) as InspecDate,
convert(varchar,vehi.nmd, 103) as NextSvcDate, convert(varchar,vehi.ninspecday, 103) as NxtInspDate, convert(varchar,vehi.nrustprday, 103) as NxtRustDate,
convert(varchar,vehi.npurchday, 103) as NxtPurdate, convert(varchar,vehi.next_emission_inspect, 103) as NxtEmInspDate,
convert(varchar,vehi.next_extwarr_purchase, 103) as NxtWarDate, convert(varchar,vehi.last_service, 103) as LastSvcDate,
convert(varchar,vehi.last_emission_inspect, 103) as LastEmInspDate, vehi.make, vehi.model, vehi.submodel
from vehi
join cust on cust.custno = vehi.custno

where nmd between @StartDate1 and @EndDate1

select @sql = 'bcp "exec AMDEMSYS.dbo.A001_SVC_DATES" queryout C:\SVC_Data\SVC.CSV -S cc-rjs-xplaptop\sqlexpress -q -f C:\SVC_Data\SVC.fmt -U sa -P sa'
exec master..xp_cmdshell @sql

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 11:11:34

Not sure what you are doing here.
It appears that you are calling an sp from crystal reports which then runs a query then calls itself in an exec statement - or is that version on a different server/database, if not it looks like you would be hit by recursion.

If it is the same sp then you would have to include the date parameters in the exec statement or make them optional.

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

RJ
Starting Member

11 Posts

Posted - 2011-06-03 : 11:27:35

What I need to do is run a report based upon a date range and at the same time export the data into a csv file that appears in the report.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 11:45:37

Try it with parameters in the exec statement and a flag to say where the sp is being called from

something like this - not sure the concept is a good idea though.

ALTER PROCEDURE [dbo].[A001_SVC_DATES]
--StartDate and EndDate are for inputting the date range via Crystal.
(
@StartDate Datetime,
@EndDate Datetime ,
@source int = 0
)
AS

Declare @sql varchar(500);

select cust.custno, cust.fname, cust.lname, cust.addr1, cust.addr2, cust.addr2e, cust.addr3, cust.po, cust.postcd,
cust.email, cust.htel, cust.wtel, cust.tel3, cust.consent, cust.nocontact,

vehi.licno, vehi.serialno, convert(varchar,vehi.fregd, 103) as RegDate, convert(varchar,vehi.inspecd, 103) as InspecDate,
convert(varchar,vehi.nmd, 103) as NextSvcDate, convert(varchar,vehi.ninspecday, 103) as NxtInspDate, convert(varchar,vehi.nrustprday, 103) as NxtRustDate,
convert(varchar,vehi.npurchday, 103) as NxtPurdate, convert(varchar,vehi.next_emission_inspect, 103) as NxtEmInspDate,
convert(varchar,vehi.next_extwarr_purchase, 103) as NxtWarDate, convert(varchar,vehi.last_service, 103) as LastSvcDate,
convert(varchar,vehi.last_emission_inspect, 103) as LastEmInspDate, vehi.make, vehi.model, vehi.submodel
from vehi
join cust on cust.custno = vehi.custno
where nmd between @StartDate and @EndDate

if @source = 0
begin
select @sql = 'bcp "exec AMDEMSYS.dbo.A001_SVC_DATES '
+ '''' + CONVERT(varchar(8),@StartDate,112) + ''''
+ ',''' + CONVERT(varchar(8),@EndDate,112) + ''''
+ ',1"'
+ ' queryout C:\SVC_Data\SVC.CSV -S cc-rjs-xplaptop\sqlexpress -q -f C:\SVC_Data\SVC.fmt -U sa -P sa'
exec master..xp_cmdshell @sql
end

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

RJ
Starting Member

11 Posts

Posted - 2011-06-03 : 12:29:01

Thanks for the assistance, the SP runs through and the bcp runs (csv file created) but no data is returned in either the file or the report. Any ideas would be appriciated. I did think of using a temp table but had no idea how to bcp the data out.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 12:42:24

Sounds like the dates aren't being sent from the report to the SP properly.
Try tracing the call using profiler to see what is being passed.

I would say to log the call in the SP but that might involve a dummy slect statement at the begining to give the report the resultset format.
The SP probably gets called twice - once with fmtonly on to get the format and then with fmtonly off to get the data - that might also cause an issue. But if it works with static dates then it is probably the parameter passing.

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

RJ
Starting Member

11 Posts

Posted - 2011-06-03 : 13:32:56

Hi many thanks for your assistance - found the issue, it was, as you said not passing the date from Crystal to the SP. Sorted and now working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources