Extensive Use pf Case Statement

Hi,

I want to generate a table output as per table a based on table b. In this case Range field os populated based on Shape and Process field values. You can see in both processes range is different. Now this is varies from shapes & processes which is stored in seperate master table which contains range ex. For Process_1 it is 0.5 & for Process_2 it is 0.10.

I have tried used case statement but I am unable to generate this dynamically in a single query. Can anyone Help me How can I achive this task?

Table A

Barcode

Shape

Process

Weight

Range

1

Shap_1

Proc_1

0.12

0.11-0.15

2

Shap_1

Proc_1

0.16

0.16-0.20

3

Shap_1

Proc_1

0.06

0.06-0.10

4

Shap_1

Proc_1

0.21

0.21-0.25

5

Shap_1

Proc_1

0.13

0.11-0.15

6

Shap_1

Proc_2

0.18

0.11-0.20

7

Shap_1

Proc_2

0.13

0.11-0.20

8

Shap_1

Proce_2

0.24

0.21-0.30

9

Shap_1

Proce_2

0.07

0.00-0.10

10

Shap_1

Proce_2

0.33

0.31-0.40

Table B

Barcode

Shape

Process

Weight

1

Shap_1

Proc_1

0.12

2

Shap_1

Proc_1

0.16

3

Shap_1

Proc_1

0.06

4

Shap_1

Proc_1

0.21

5

Shap_1

Proc_1

0.13

6

Shap_1

Proc_2

0.18

7

Shap_1

Proc_2

0.13

8

Shap_1

Proce_2

0.24

9

Shap_1

Proce_2

0.07

10

Shap_1

Proce_2

0.33

Nilkanth Desai

create table TableB(Barcode int, Shape char(6), Process varchar(8), Weight decimal(5,2))
insert into TableB(Barcode ,Shape , Process ,Weight)
select 1 , 'Shap_1', 'Proc_1' , 0.12 union all
select 2 , 'Shap_1', 'Proc_1' , 0.16 union all
select 3 , 'Shap_1', 'Proc_1' , 0.06 union all
select 4 , 'Shap_1', 'Proc_1' , 0.21 union all
select 5 , 'Shap_1', 'Proc_1' , 0.13 union all
select 6 , 'Shap_1', 'Proc_2' , 0.18 union all
select 7 , 'Shap_1', 'Proc_2' , 0.13 union all
select 8 , 'Shap_1', 'Proc_2', 0.24 union all
select 9 , 'Shap_1', 'Proc_2', 0.07 union all
select 10 , 'Shap_1', 'Proc_2', 0.33

create table Master(Process varchar(8), Range decimal(5,2))
insert into Master(Process, Range) values('Proc_1',0.05)
insert into Master(Process, Range) values('Proc_2',0.10)

select t.Barcode,
t.Shape,
t.Process,
t.Weight,
floor(t.Weight/m.Range)*m.Range+0.01 as RangeFrom,
floor(t.Weight/m.Range)*m.Range+m.Range as RangeTo
from TableB t
inner join Master m on m.Process=t.Process
order by t.Barcode

Extending the CSV Export to add Text Padding etc.

Hi All,
I am looking at using are report to generate an export file that is similar
to the CSV export...
I need to be able to the text length of the columns and remove the ","
between columns eg:
CSV
Angus,Logan,Data#3
REQUIRED EXPORT
Angus Logan Data#3
text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to 10
(add 4 spaces)
I am thinking about programatically downloading the CSV (or Xml) and parsing
them into the right format but thought there may be an easier solution...
Any Ideas?
Regards
Angus Logan
MCDBA / MCADYou can use the PadRight() method to add the spaces and then use the CSV
renderering extension and set the deviceInfo named FieldDelimiter to
emptystring. You will have to use URL Access or the SOAP API.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angus Logan" <angus_logan@.data3.com.au> wrote in message
news:ePRIhZnYEHA.556@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking at using are report to generate an export file that is
> similar
> to the CSV export...
> I need to be able to the text length of the columns and remove the ","
> between columns eg:
> CSV
> Angus,Logan,Data#3
> REQUIRED EXPORT
> Angus Logan Data#3
> text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to
> 10
> (add 4 spaces)
> I am thinking about programatically downloading the CSV (or Xml) and
> parsing
> them into the right format but thought there may be an easier solution...
> Any Ideas?
> Regards
> Angus Logan
> MCDBA / MCAD
>

Exporting XML file format issue

Hello
I am trying to generate an XML file from SQL Server which gets pushed out to
a third party app using DTS. So Far I have created a sproc like so.
CREATE PROC querystrCR
AS
SELECT 1 as Tag,
NULL as Parent,
'<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
'<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
'<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
'<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
'<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
'<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
'<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
FROM CR
FOR XML EXPLICIT
It needs the CDATA fields as they contain several odd characters. Then I
export the file as part of a DTS package like so;
EXEC sp_makewebtask
@.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
@.query = 'EXEC querystrCR',
@.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
The template file looks like
<?xml version="1.0" encoding="utf-8" ?>
<DB>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</DB>
The problem is with the format of the output file. SQL Server spits it out
in rows which don't correspond to the tags e.g. a line end like
</CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
Obviously this means the third party app. will not parse it and a web
browser will not open the file e.g. Firefox reports "XML Parsing Error: not
well-formed". I need the output in the exported file to look
<CPB>
<CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
<CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
<CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
<CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
<CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
<PRIO><![CDATA[ "1" ]]></PRIO>
<MTIME><![CDATA[ "999999999" ]]></MTIME>
</CPB>
Any Help would be appreciated.
Cheers
Matt
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOADA couple of points:
1. Instead of manually constructing the <![CDATA[ use the !cdata directive.
2. However, the CDATA section should not give you anything that you cannot
achieve otherwise. In particular it does not help with invalid characters
(what are your odd characters?). So you may not need to use it.
3. FOR XML results per default return fragments. In order to export it as a
document, add the root node (there are a couple of ways depending on the API
used).
4. If you want to expose the XML, you should use either the respective
stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked XML
in approx 4kBytes blocks that you need to merge yourself.
HTH
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:4207e650$1@.news01.wxnz.net...
> Hello
> I am trying to generate an XML file from SQL Server which gets pushed out
> to a third party app using DTS. So Far I have created a sproc like so.
> CREATE PROC querystrCR
> AS
> SELECT 1 as Tag,
> NULL as Parent,
> '<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
> '<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
> '<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
> '<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
> '<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
> '<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
> '<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
> FROM CR
> FOR XML EXPLICIT
>
> It needs the CDATA fields as they contain several odd characters. Then I
> export the file as part of a DTS package like so;
> EXEC sp_makewebtask
> @.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
> @.query = 'EXEC querystrCR',
> @.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
> The template file looks like
> <?xml version="1.0" encoding="utf-8" ?>
> <DB>
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> </DB>
> The problem is with the format of the output file. SQL Server spits it out
> in rows which don't correspond to the tags e.g. a line end like
> </CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
> Obviously this means the third party app. will not parse it and a web
> browser will not open the file e.g. Firefox reports "XML Parsing Error:
> not well-formed". I need the output in the exported file to look
> <CPB>
> <CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
> <CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
> <CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
> <CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
> <CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
> <PRIO><![CDATA[ "1" ]]></PRIO>
> <MTIME><![CDATA[ "999999999" ]]></MTIME>
> </CPB>
> Any Help would be appreciated.
> Cheers
> Matt
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>|||I have everything I want in my XML file the only thing I need to format now
is the CR. I know this because when I open the example XML that the third
party app will parse, in notepad I see the square cube CR character. If I
manually edit my XML file in notepad and "paste" this char in after every
closing TAG the XML I generated is parsed and imported to the app.
I'm pulling the XML over HTTP using this script; Can I format this XML here?
var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
xml.async = false;
xml.load("http://localhost/routingtwo/template/test.xml");
xml.save("testoutput.xml")
or can I do it in my template test.xml
<?xml version="1.0" encoding="UTF-8"?>
<CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
exec CSM.dbo.querystrCR
</sql:query>
</CSM>
or is it in the actual SQL query?
Cheers
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>A couple of points:
> 1. Instead of manually constructing the <![CDATA[ use the !cdata
> directive.
> 2. However, the CDATA section should not give you anything that you cannot
> achieve otherwise. In particular it does not help with invalid characters
> (what are your odd characters?). So you may not need to use it.
> 3. FOR XML results per default return fragments. In order to export it as
> a document, add the root node (there are a couple of ways depending on the
> API used).
> 4. If you want to expose the XML, you should use either the respective
> stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
> or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked
> XML in approx 4kBytes blocks that you need to merge yourself.
> HTH
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:4207e650$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD|||Why would you want a carriage return added? An XML parser always replaces a
CR (or a CR/LF sequence) with a LF. So where do you need to send the CR to?
Does it also work without the CR present?
Thanks
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420a7ee3$1@.news01.wxnz.net...
>I have everything I want in my XML file the only thing I need to format now
>is the CR. I know this because when I open the example XML that the third
>party app will parse, in notepad I see the square cube CR character. If I
>manually edit my XML file in notepad and "paste" this char in after every
>closing TAG the XML I generated is parsed and imported to the app.
> I'm pulling the XML over HTTP using this script; Can I format this XML
> here?
> var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
> xml.async = false;
> xml.load("http://localhost/routingtwo/template/test.xml");
> xml.save("testoutput.xml")
> or can I do it in my template test.xml
> <?xml version="1.0" encoding="UTF-8"?>
> <CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <sql:query>
> exec CSM.dbo.querystrCR
> </sql:query>
> </CSM>
> or is it in the actual SQL query?
> Cheers
> Matt
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>|||My error it was an encoding problem - I was saving my XML template as ANSI
not Unicode.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Why would you want a carriage return added? An XML parser always replaces
> a CR (or a CR/LF sequence) with a LF. So where do you need to send the CR
> to? Does it also work without the CR present?
> Thanks
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:420a7ee3$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD|||Cool (well, now it is :-)).
Best regards
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420be8dd$1@.news01.wxnz.net...
> My error it was an encoding problem - I was saving my XML template as ANSI
> not Unicode.
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>

Exporting XML file format issue

Hello
I am trying to generate an XML file from SQL Server which gets pushed out to
a third party app using DTS. So Far I have created a sproc like so.
CREATE PROC querystrCR
AS
SELECT 1 as Tag,
NULL as Parent,
'<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
'<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
'<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
'<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
'<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
'<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
'<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
FROM CR
FOR XML EXPLICIT
It needs the CDATA fields as they contain several odd characters. Then I
export the file as part of a DTS package like so;
EXEC sp_makewebtask
@.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
@.query = 'EXEC querystrCR',
@.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
The template file looks like
<?xml version="1.0" encoding="utf-8" ?>
<DB>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</DB>
The problem is with the format of the output file. SQL Server spits it out
in rows which don't correspond to the tags e.g. a line end like
</CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
Obviously this means the third party app. will not parse it and a web
browser will not open the file e.g. Firefox reports "XML Parsing Error: not
well-formed". I need the output in the exported file to look
<CPB>
<CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
<CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
<CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
<CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
<CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
<PRIO><![CDATA[ "1" ]]></PRIO>
<MTIME><![CDATA[ "999999999" ]]></MTIME>
</CPB>
Any Help would be appreciated.
Cheers
Matt
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
A couple of points:
1. Instead of manually constructing the <![CDATA[ use the !cdata directive.
2. However, the CDATA section should not give you anything that you cannot
achieve otherwise. In particular it does not help with invalid characters
(what are your odd characters?). So you may not need to use it.
3. FOR XML results per default return fragments. In order to export it as a
document, add the root node (there are a couple of ways depending on the API
used).
4. If you want to expose the XML, you should use either the respective
stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked XML
in approx 4kBytes blocks that you need to merge yourself.
HTH
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:4207e650$1@.news01.wxnz.net...
> Hello
> I am trying to generate an XML file from SQL Server which gets pushed out
> to a third party app using DTS. So Far I have created a sproc like so.
> CREATE PROC querystrCR
> AS
> SELECT 1 as Tag,
> NULL as Parent,
> '<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
> '<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
> '<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
> '<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
> '<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
> '<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
> '<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
> FROM CR
> FOR XML EXPLICIT
>
> It needs the CDATA fields as they contain several odd characters. Then I
> export the file as part of a DTS package like so;
> EXEC sp_makewebtask
> @.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
> @.query = 'EXEC querystrCR',
> @.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
> The template file looks like
> <?xml version="1.0" encoding="utf-8" ?>
> <DB>
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> </DB>
> The problem is with the format of the output file. SQL Server spits it out
> in rows which don't correspond to the tags e.g. a line end like
> </CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
> Obviously this means the third party app. will not parse it and a web
> browser will not open the file e.g. Firefox reports "XML Parsing Error:
> not well-formed". I need the output in the exported file to look
> <CPB>
> <CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
> <CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
> <CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
> <CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
> <CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
> <PRIO><![CDATA[ "1" ]]></PRIO>
> <MTIME><![CDATA[ "999999999" ]]></MTIME>
> </CPB>
> Any Help would be appreciated.
> Cheers
> Matt
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||I have everything I want in my XML file the only thing I need to format now
is the CR. I know this because when I open the example XML that the third
party app will parse, in notepad I see the square cube CR character. If I
manually edit my XML file in notepad and "paste" this char in after every
closing TAG the XML I generated is parsed and imported to the app.
I'm pulling the XML over HTTP using this script; Can I format this XML here?
var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
xml.async = false;
xml.load("http://localhost/routingtwo/template/test.xml");
xml.save("testoutput.xml")
or can I do it in my template test.xml
<?xml version="1.0" encoding="UTF-8"?>
<CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
exec CSM.dbo.querystrCR
</sql:query>
</CSM>
or is it in the actual SQL query?
Cheers
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>A couple of points:
> 1. Instead of manually constructing the <![CDATA[ use the !cdata
> directive.
> 2. However, the CDATA section should not give you anything that you cannot
> achieve otherwise. In particular it does not help with invalid characters
> (what are your odd characters?). So you may not need to use it.
> 3. FOR XML results per default return fragments. In order to export it as
> a document, add the root node (there are a couple of ways depending on the
> API used).
> 4. If you want to expose the XML, you should use either the respective
> stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
> or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked
> XML in approx 4kBytes blocks that you need to merge yourself.
> HTH
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:4207e650$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Why would you want a carriage return added? An XML parser always replaces a
CR (or a CR/LF sequence) with a LF. So where do you need to send the CR to?
Does it also work without the CR present?
Thanks
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420a7ee3$1@.news01.wxnz.net...
>I have everything I want in my XML file the only thing I need to format now
>is the CR. I know this because when I open the example XML that the third
>party app will parse, in notepad I see the square cube CR character. If I
>manually edit my XML file in notepad and "paste" this char in after every
>closing TAG the XML I generated is parsed and imported to the app.
> I'm pulling the XML over HTTP using this script; Can I format this XML
> here?
> var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
> xml.async = false;
> xml.load("http://localhost/routingtwo/template/test.xml");
> xml.save("testoutput.xml")
> or can I do it in my template test.xml
> <?xml version="1.0" encoding="UTF-8"?>
> <CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <sql:query>
> exec CSM.dbo.querystrCR
> </sql:query>
> </CSM>
> or is it in the actual SQL query?
> Cheers
> Matt
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||My error it was an encoding problem - I was saving my XML template as ANSI
not Unicode.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Why would you want a carriage return added? An XML parser always replaces
> a CR (or a CR/LF sequence) with a LF. So where do you need to send the CR
> to? Does it also work without the CR present?
> Thanks
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:420a7ee3$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Cool (well, now it is :-)).
Best regards
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420be8dd$1@.news01.wxnz.net...
> My error it was an encoding problem - I was saving my XML template as ANSI
> not Unicode.
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>

Exporting to Excel Problems (URL Access)

Hello,
I'm using SQL Server 2005 Reporting Services. I'm having an issues when exporting to Excel. I'm using URL Access to generate the report and display it in a browser window. I then choose Excel and click the Export link. Doing this opens a temporary browser window and then a dialog that asks if I want to save the report or open it. If I choose to open it, the report opens in Excel without problem. The problem is that the temporary browser window doesn't go away (close) automatically like it should. What makes this a little more strange is that this behavior only seem to occur on a Windows XP computer. If I run the report from a Windows 2000 or Windows Server 2003 computer, the temporary browser windows closes automatically (and the report is exported to Excel and opened).

Has anyone experienced this same issue?

Does anyone know how to "fix" this issue so that exporting to open right away always closes the temporary browser window?

Thanks.

I've noticed that issue without using URL access. My users love exporting to Excel, and it bugs me when I test exporting, and have windows open all over the place. I'd like to know what the fix is as well. I imagine it is just an IE thing, and there's nothing we can do about it.|||

Did you fine an answer for this one. I am experiencing the same issue.

Thanks for help in advance.

Mehul

Exporting to Excel Problems (URL Access)

Hello,
I'm using SQL Server 2005 Reporting Services. I'm having an issues when exporting to Excel. I'm using URL Access to generate the report and display it in a browser window. I then choose Excel and click the Export link. Doing this opens a temporary browser window and then a dialog that asks if I want to save the report or open it. If I choose to open it, the report opens in Excel without problem. The problem is that the temporary browser window doesn't go away (close) automatically like it should. What makes this a little more strange is that this behavior only seem to occur on a Windows XP computer. If I run the report from a Windows 2000 or Windows Server 2003 computer, the temporary browser windows closes automatically (and the report is exported to Excel and opened).

Has anyone experienced this same issue?

Does anyone know how to "fix" this issue so that exporting to open right away always closes the temporary browser window?

Thanks.

I've noticed that issue without using URL access. My users love exporting to Excel, and it bugs me when I test exporting, and have windows open all over the place. I'd like to know what the fix is as well. I imagine it is just an IE thing, and there's nothing we can do about it.|||

Did you fine an answer for this one. I am experiencing the same issue.

Thanks for help in advance.

Mehul

Exporting to Excel Problems (URL Access)

Hello,
I'm using SQL Server 2005 Reporting Services. I'm having an issues when
exporting to Excel. I'm using URL Access to generate the report and display
it in a browser window. I then choose Excel and click the Export link.
Doing this opens a temporary browser window and then a dialog that asks if I
want to save the report or open it. If I choose to open it, the report
opens in Excel without problem. The problem is that the temporary browser
window doesn't go away (close) automatically like it should. What makes
this a little more strange is that this behavior only seem to occur on a
Windows XP computer. If I run the report from a Windows 2000 or Windows
Server 2003 computer, the temporary browser windows closes automatically
(and the report is exported to Excel and opened).
Has anyone experienced this same issue?
Does anyone know how to "fix" this issue so that exporting to open right
away always closes the temporary browser window?
Thanks.I am currently experiencing the same issue on Server 2003. The blank window
remains. I was unaware that it closes on other systems. Have you found the
solution?
"steggun" wrote:
> Hello,
> I'm using SQL Server 2005 Reporting Services. I'm having an issues when
> exporting to Excel. I'm using URL Access to generate the report and display
> it in a browser window. I then choose Excel and click the Export link.
> Doing this opens a temporary browser window and then a dialog that asks if I
> want to save the report or open it. If I choose to open it, the report
> opens in Excel without problem. The problem is that the temporary browser
> window doesn't go away (close) automatically like it should. What makes
> this a little more strange is that this behavior only seem to occur on a
> Windows XP computer. If I run the report from a Windows 2000 or Windows
> Server 2003 computer, the temporary browser windows closes automatically
> (and the report is exported to Excel and opened).
> Has anyone experienced this same issue?
> Does anyone know how to "fix" this issue so that exporting to open right
> away always closes the temporary browser window?
> Thanks.
>
>

Exporting stored procedures

Hello!
I look for a way to export the stored procedures from a database
automatically. The GUI way is "generate SQL script", but I want to do it via
script, or an API.
Thanks!
RoeeIt is all in the SQL-DMO API.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it via
> script, or an API.
> Thanks!
> Roee
>|||Hi,
You can use DTS packages to copy objects to destination.
The task name is "Copy SQL Server Objects Task" , in the copy tab you can
select all stored procedures.
Thanks
Hari
MCDBA
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eOMJaLSnDHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You can use DTS packages to copy objects to destination.
> The task name is "Copy SQL Server Objects Task" , in the copy tab you can
> select all stored procedures.
Although this will only work if the source and destination are both SQL
Servers
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||Here's an example of scripting all the stored procedures in a database with
Perl and SQL-DMO. Save the following code in a file, say, named scriptSP.pl:
################## Being script
# tested with ActivePerl 5.6.1, Binaray Build 631
use strict;
use Getopt::Std;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO'; # import the SQL-DMO symbolic
constants
my %opts;
getopts('S:d:o:', \%opts); # Get the command line arguments
my ($serverName, $dbName, $output) = ($opts{S}, $opts{d}, $opts{o});
# Check whether the required command line arguments are specified
(defined $serverName && defined $dbName && defined $output)
or printUsage();
# Create a SQLDMO SQLServer object
my $server = Win32::OLE->new('SQLDMO.SQLServer') or
die "***Err: could not create SQLDMO object.\n";
$server->{LoginSecure} = 1; # trusted connection
# Connect to the SQL instance via trusted connection
$server->connect($serverName);
! Win32::OLE->LastError() or
die "***Err: SQLDMO could not connect to $serverName.\n";
# Get the SQLDMO Database object
my $db = $server->Databases($dbName);
# Loop through the StoredProcedures collection to script each stored
procedure
foreach my $obj (in($server->Databases($dbName)->StoredProcedures())) {
$obj->Script(SQLDMOScript_Default | SQLDMOScript_AppendToFile |
SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$output); # the SQL script goes to this
file
}
# Clean up
$server->disconnect();
$server->DESTROY();
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl scriptSP.pl -S <SQL instance> -d <database name> -o <output
file>
--Usage--
exit;
} # printUsage
################# End script
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>