bcp/BULK INSERT and blank lines

Does anyone know of a way to make BULK INSERT or bcp ignore blank lines in the file? I am having trouble with a bunch of data files coming back with 1 or 2 blank lines at the end, and it causes the entire bcp to fail.

I suppose I could write a utility to trim the files but that seems a bit overkill. Any thoughts?

You will need to trim the data 'cuz bcp/bulk insert is just a _dumb_ data loader.|||One option is to use the -L parameter of BCP to specify the last row. This will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT.|||ah, -L! Thanks for the correction. I've never used that flag. It seems much simpler to just trim the blank lines...

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE and NTILE problem?

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTE is only visible within a batch in a connection. When you execute BCP using xp_cmdshell, you are actually invoking an external process (bcp in this case) which makes a new connection to SQLServer. So it will not have any context about the declared CTE. Also, the way you are using the CTE doesn't make sense also. You declare a CTE but then you update the base table directly. Even though it is legal I am not sure what you are doing. CTE is also not a peristent object like view or table-valued function.

You could do one of the following:

1. Query from the base table directly

2. Specify CTE definition in the queryout parameter itself

3. Dump the results of the table into a global temporary table and export from there (this will prevent the BCP from being executed simultaneously by different connections though)

Basic SSRS Question

I am trying to get a count of all the reports that do not use shared
datasource. We are finding a bunch of reports that do not use shared
datasource we would like to get a list of all the reports that do not
use shared datsource.
Also we would like to get a list of all the reports that are set for
subscriptions. There are way too many reports that are on our server
it would be really hard to go in manually to find this information.
I have been quering the dbo.catalog table in the report server to
extract this information however I have had no luck. Are there any
dyanamic views that will givbe me that info? I am sure other have run
into this requirement as well and already figured it out.
Could one of you please help me or point me in the right direction?
ThanksOn Apr 30, 8:59=A0am, shub <shubt...@.gmail.com> wrote:
> I am trying to get a count of all the reports that do not use shared
> datasource. We are finding a bunch of reports that do not use shared
> datasource we would like to get a list of all the reports that do not
> use shared datsource.
> Also we would like to get a list of all the reports that are set for
> subscriptions. =A0There are way too many reports that are on our server
> it would be really hard to go in manually to find this information.
> I have been quering the dbo.catalog table in the report server to
> extract this information however I have had no luck. Are there any
> dyanamic views that will givbe me that info? I am sure other have run
> into this requirement as well and already figured it out.
> Could one of you please help me or point me in the right direction?
> Thanks
Not sure where to look for the datasource info as all of our reports
run from shared data sources i.e. all look the same in that regard.
For the subscription stuff, try this query against the reportserver
database on server you run SSRS on to get a list of all reports with
subscriptions.
SELECT DISTINCT c.name AS RptName, c.Description AS RptDescription,
c.path AS Path, sc.name AS SchedName,
su.Description AS SubsDescription
FROM dbo.Catalog c JOIN
dbo.ReportSchedule rs ON c.ItemID =3D rs.ReportID
JOIN
dbo.Schedule sc ON rs.ScheduleID =3D sc.ScheduleID
JOIN
dbo.Subscriptions su ON rs.SubscriptionID =3D
su.SubscriptionID|||On May 1, 3:30=A0pm, toolman <t...@.infocision.com> wrote:
> On Apr 30, 8:59=A0am, shub <shubt...@.gmail.com> wrote:
>
> > I am trying to get a count of all the reports that do not use shared
> > datasource. We are finding a bunch of reports that do not use shared
> > datasource we would like to get a list of all the reports that do not
> > use shared datsource.
> > Also we would like to get a list of all the reports that are set for
> > subscriptions. =A0There are way too many reports that are on our server
> > it would be really hard to go in manually to find this information.
> > I have been quering the dbo.catalog table in the report server to
> > extract this information however I have had no luck. Are there any
> > dyanamic views that will givbe me that info? I am sure other have run
> > into this requirement as well and already figured it out.
> > Could one of you please help me or point me in the right direction?
> > Thanks
> Not sure where to look for the datasource info as all of our reports
> run from shared data sources i.e. all look the same in that regard.
> For the subscription stuff, try this query against the reportserver
> database on server you run SSRS on to get a list of all reports with
> subscriptions.
> SELECT DISTINCT =A0c.name AS RptName, c.Description AS RptDescription,
> c.path AS Path, sc.name AS SchedName,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 su.Description AS SubsDescript=ion
> FROM =A0 =A0 =A0 =A0 dbo.Catalog c JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.ReportSchedule rs ON c.Ite=mID =3D rs.ReportID
> JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.Schedule sc ON rs.Schedule=ID =3D sc.ScheduleID
> JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.Subscriptions su ON rs.Sub=scriptionID =3D
> su.SubscriptionID- Hide quoted text -
> - Show quoted text -
Thank you so much. This query certainly helps a lot. I will keep
poking around for the other solution to find all reports that do not
use shared datasource. Thanks again