Thanks for your advise.

I will return back to them - I thought I had missed a trick somewhere - but glad I was covered.

Regards

Steve

Hi Orlando,

You mentioned that I have some options missing - however, the options that you have mentioned are for screen width ( -w, -y )

The extract using SQLCMD and...

John Mitchell-245523 (9/3/2015)

---

You're confusing dates with date formats. Since this is a SQL Server forum, let's talk about SQL Server, which stores dates internally as a number (two numbers,...

Yes I do agree, however, with legacy...

Ahh right - stumbling block number 2..

You cannot use SET LANGUAGE British within a view !

Looks like the database needs to be set as dmy ?

Hi Thanks for the reply -

The simple answer is -

SET LANGUAGE British

GO

Thanks

Thanks for your help.

Unfortunately the data is from old data - so this is the cleansing routines.

Apologies -

Command line;

Create format file;

bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] format nul -n -f C:\XMLExport\StockCodeFormat.xml -x -T

Create data file;

bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] out c:\xmlexport\StockCodes.xml -f C:\XMLExport\StockCodeFormat.xml -T

I think I am getting there - having figured out the bcp command line now.

So I have created an xml format file and specified that when creating the data file....

Sorry - have answered this one now.

I am struggling getting the output to work within "BCP" - keeps asking for a format file !?

That will be what I am after - I think I have that bit all sorted now.

Just one more question -

With Regard to the XML format, Is it possible...

Thanks for the help.

The views now work a treat -

What would be the best solution for generating an XML file in a specific folder, calling a stored procedure to...

cracked it ....

With myCTE as

(

SELECT

ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, TrnMonth DESC ) as Row, TrnYear, right('00'+ rtrim(TrnMonth), 2) as TrnMonth, Count( Invoice ) as InvoiceCount

FROM ApInvoicePay

Where...

Think I have solved it ...using Row_NUMBER()

SELECT

ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, TrnMonth DESC ) as Row,TrnYear, right('00'+ rtrim(TrnMonth), 2) as TrnMonth, Count( Invoice ) as InvoiceCount

FROM ApInvoicePay

Where...

Both TrnYear, TrnMonth are decimal.

2014/09 will return NULL, I agree..... although I think finance want to put the forecast in there.... another topic !

Thanks