Change Graph type at run time

Hi
Can the user not change the graph type at run time the same way you can
change for example background colour. I want to have a parameter for the user
so he can choose line, bar or pie for the same graph.
Thanks
FrancoisSetting chart types dynamically is not supported in the current release.
However, you could approximate the desired behavior by using multiple charts
(of various types) and use an expression to dynamically hide all charts but
the one you want displayed.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Francois" <Francois@.discussions.microsoft.com> wrote in message
news:7CDB8D7C-2444-4C9B-916C-59A2A8946FB2@.microsoft.com...
> Hi
> Can the user not change the graph type at run time the same way you can
> change for example background colour. I want to have a parameter for the
user
> so he can choose line, bar or pie for the same graph.
> Thanks
> Francois

change formula thru SQLServer

Hello

how can i adjust formula thru SQLServer?? for example --the computation Gross Salary less Deduction then the total will be for computing of witholdingTax..

thanks pipz..I didn't understand your formula.
Can you give me an example of what you have and what you are trying to get.

change fonts existing reports

Is it possible to change the font, for example to font-family Arial, for all
the existing reports made in reporting services.
--
regards, pakamSorry, this is not supported.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pakam" <pakam@.discussions.microsoft.com> wrote in message
news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> Is it possible to change the font, for example to font-family Arial, for
> all
> the existing reports made in reporting services.
> --
> regards, pakam|||You could do it dynamically, by using getreportdefinition to get one of the
existing reports as xml, then use SelectNodeList to get all the <FontFamily>
nodes.
Then foreach node in the node list, change the InnerText to the new font &
republish the report
"Robert Bruckner [MSFT]" wrote:
> Sorry, this is not supported.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "pakam" <pakam@.discussions.microsoft.com> wrote in message
> news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > Is it possible to change the font, for example to font-family Arial, for
> > all
> > the existing reports made in reporting services.
> > --
> > regards, pakam
>
>|||Hi mark-s,
Thank you for your reaction. For your solution I have to write code ? I did
find the GetReportDefinition but not the SelectNodeList. Can you explain more
how to do this ? .
I did find out that it is possible to use Edit -> Find and Replace ->
Replace in Files. This is not perfect but it helps to avoid changing every
item. Then you can change all reports in the project to the font-family arial
for example.
Regards pakam
"mark-s" wrote:
> You could do it dynamically, by using getreportdefinition to get one of the
> existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> nodes.
> Then foreach node in the node list, change the InnerText to the new font &
> republish the report
> "Robert Bruckner [MSFT]" wrote:
> > Sorry, this is not supported.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > Is it possible to change the font, for example to font-family Arial, for
> > > all
> > > the existing reports made in reporting services.
> > > --
> > > regards, pakam
> >
> >
> >|||Hi pakam
Yes, you will have to write code. SelectNodeList is in the System.Xml.XPath
namespace.
However, if you only want to change the font once, then Find/Replace might
be easier. I was thinking that you may need to change the font regularly.
"pakam" wrote:
> Hi mark-s,
> Thank you for your reaction. For your solution I have to write code ? I did
> find the GetReportDefinition but not the SelectNodeList. Can you explain more
> how to do this ? .
> I did find out that it is possible to use Edit -> Find and Replace ->
> Replace in Files. This is not perfect but it helps to avoid changing every
> item. Then you can change all reports in the project to the font-family arial
> for example.
> Regards pakam
>
> "mark-s" wrote:
> > You could do it dynamically, by using getreportdefinition to get one of the
> > existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> > nodes.
> > Then foreach node in the node list, change the InnerText to the new font &
> > republish the report
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Sorry, this is not supported.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > > Is it possible to change the font, for example to font-family Arial, for
> > > > all
> > > > the existing reports made in reporting services.
> > > > --
> > > > regards, pakam
> > >
> > >
> > >

Change Default Font "Arial" to "Gotik"?

Hello,
is it possible to change the default font "Arial" to an other font (for example "Gotik")?
where can i do this?
Thanks in advance,
RalphOn Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for e=xample "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi EMartinez,
The default font for designing reports in the layout is set to Tahoma. This
font should be changed to different font family. So that all the text of
report header, column headers, data text will be shown in the selected font
by default. This can not be achieved by following the steps specified below.
Is there any way to achieve it?
Thanks!
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
On Oct 1, 7:36 am, "Ralph Hüttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for
> example "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 4, 7:09 am, "Venky Dosap" <ven...@.translogicsys.com> wrote:
> Hi EMartinez,
> The default font for designing reports in the layout is set to Tahoma. Th=is
> font should be changed to different font family. So that all the text of
> report header, column headers, data text will be shown in the selected fo=nt
> by default. This can not be achieved by following the steps specified bel=ow.
> Is there any way to achieve it?
> Thanks!
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
> On Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
> wrote:
> > Hello,
> > is it possible to change the default font "Arial" to an other font (for
> > example "Gotik")?
> > where can i do this?
> > Thanks in advance,
> > Ralph
> If I understand you correctly, you can either set the fonts via
> selecting the Properties Window (F4) once selecting a cell/etc. Then
> select Font and Font Family. -or- You can select the Layout tab in
> BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
> Environment -> Fonts and Color -> then Font. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
As far as I know, controlling the default font is not feasible. Sorry
that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Change default data folder for MSSQL2000

Please can anyone tell me how I can change the default
location of the data folder for SQL2000?
For example it's currently
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change it so that each time a user creates a
database or a backup, the default location for the file to
be created is:
D:\MSSQL\
Can you help?
Thank youFrom Enterprise Mangler, right click the server name, select PROPERTIES.
Choose the Database Settings tab. Near the bottom are the fields for
default database and log file locations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||I found it - apologies
Knowledge base 272705
To change:
Right click your instance, SQL Server Properties, Database
settings tab, enter the location you want eg
D:\MSSQL
If only they were all so easy.|||Val
The default data directory is determined at the time SQL Server was
installed.
create database test
on
primary ( name=test,
filename='d:\my_test.mdf',
size=100mb,
maxsize=200,
filegrowth=20)
LOG on
(
name=test1,
filename='d:\my_test.ldf',
size=100mb,
maxsize=200,
filegrowth )
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||there is some sort of bug when you try and do this - you
cannot save the default location all the time.
>--Original Message--
>From Enterprise Mangler, right click the server name,
select PROPERTIES.
>Choose the Database Settings tab. Near the bottom are
the fields for
>default database and log file locations.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Val" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
>> Please can anyone tell me how I can change the default
>> location of the data folder for SQL2000?
>> For example it's currently
>> C:\Program Files\Microsoft SQL Server\MSSQL\Data
>> I want to change it so that each time a user creates a
>> database or a backup, the default location for the
file to
>> be created is:
>> D:\MSSQL\
>> Can you help?
>> Thank you
>
>.
>

Change default data folder for MSSQL2000

Please can anyone tell me how I can change the default
location of the data folder for SQL2000?
For example it's currently
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change it so that each time a user creates a
database or a backup, the default location for the file to
be created is:
D:\MSSQL\
Can you help?
Thank youFrom Enterprise Mangler, right click the server name, select PROPERTIES.
Choose the Database Settings tab. Near the bottom are the fields for
default database and log file locations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||Val
The default data directory is determined at the time SQL Server was
installed.
create database test
on
primary ( name=test,
filename='d:\my_test.mdf',
size=100mb,
maxsize=200,
filegrowth=20)
LOG on
(
name=test1,
filename='d:\my_test.ldf',
size=100mb,
maxsize=200,
filegrowth=20
)
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||there is some sort of bug when you try and do this - you
cannot save the default location all the time.

>--Original Message--
>From Enterprise Mangler, right click the server name,
select PROPERTIES.
>Choose the Database Settings tab. Near the bottom are
the fields for
>default database and log file locations.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Val" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
file to
>
>.
>

change datetime to date

I have a datetime field (example: 08/06/2007 9:05:06AM) that I want to change so only the date is in a select statement (so I only get 08/06/2007). How do I write the expression?
Help is appreciated.
Thanksthere is no good way that i know of to do this in ANSI SQL

which database are you using?|||Thanks for posting to my thread. I found a workaround (even though it ain't pretty) but it's working so thanks again for posting.|||what was your workaround?

change Datasource at runtime

Hi

Is it possible to change the datasource dynamically in reporting services.

For example if I have a dataset named DataSet1 whose datasource1 connects to server1, how can I change the same DataSet1 to use datasource2 to points to a different server. I would like to know if this datasource change is possible out side the dataset (dynamically).
Thanks

Hi pzmrcd
I've had the same problem. I found that it was not possible without re-rendering the SRS code(XML). It is not possible in SRS since you can't use an expression to change the dataset. Also the field names of datasets may differ.

Change Data Capture in Integration Services Packages

Hi,

Is available any tutorial or example which shows how to use Change Data Capture in Integration Services Packages?

I have found Using Change Data Capture in Integration Services Packages topic in BOL but there is an error and I can not open it. There is also Change Data Capture live meeting but "this meeting has already finished".

Thanks,

Rafal

Hi Rafal,

You can still view the LiveMeeting. If you've registered for Katmai on Connect, go here to access the archived LiveMeetings.

-Chris
|||

Hi Rafal,

Change Data Capture is working fine in Katmai, I have already tested it.

Go to the below mentioned site. It clearly demonstrates the way to achieve it.

http://www.kodyaz.com/articles/change-data-capture.aspx

If still u find any problem doing so, do tell me where exactly you failing, might help you out|||

Rafal,

I can send the sample i used in the live meeting presentation, if you haven;t already got one, We plan to provide samples publicly very shortly, but this should help you out in the short term.

ThanksGopal

|||

Hi Gopal, The SSIS sample will be of help to us. I would appreciate if you cand send the sample. Thanks.

Change Data Capture in Integration Services Packages

Hi,

Is available any tutorial or example which shows how to use Change Data Capture in Integration Services Packages?

I have found Using Change Data Capture in Integration Services Packages topic in BOL but there is an error and I can not open it. There is also Change Data Capture live meeting but "this meeting has already finished".

Thanks,

Rafal

Hi Rafal,

You can view the archived LiveMeeting. If you've registered for Katmai on Connect, go here to select an archived LiveMeeting.

-Chris

Change Data Capture in Integration Services Packages

Hi,

Is available any tutorial or example which shows how to use Change Data Capture in Integration Services Packages?

I have found Using Change Data Capture in Integration Services Packages topic in BOL but there is an error and I can not open it. There is also Change Data Capture live meeting but "this meeting has already finished".

Thanks,

Rafal

Hi Rafal,

You can still view the LiveMeeting. If you've registered for Katmai on Connect, go here to access the archived LiveMeetings.

-Chris
|||

Hi Rafal,

Change Data Capture is working fine in Katmai, I have already tested it.

Go to the below mentioned site. It clearly demonstrates the way to achieve it.

http://www.kodyaz.com/articles/change-data-capture.aspx

If still u find any problem doing so, do tell me where exactly you failing, might help you out|||

Rafal,

I can send the sample i used in the live meeting presentation, if you haven;t already got one, We plan to provide samples publicly very shortly, but this should help you out in the short term.

ThanksGopal

|||

Hi Gopal, The SSIS sample will be of help to us. I would appreciate if you cand send the sample. Thanks.

Change attribute value in stored procedure

Can someone show me the syntax (if it is possible) to modify an attribute value in a stored proc? For example if I have an element that is being passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to change the 2 to something else (like a 3) depending on a condition? Or do I have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to change the value? Will someone
please let me know? Many Thanks!
There's no XML manipulation functionality built into SQL Server 2000 (other
than shredding the XML into a table, performing your update, and retrieving
it back out as XML again using a FOR XML query - definitely not an efficient
approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
better performing this kind of logic in the client application before
submitting it to the stored procedure (apply a style sheet or use an XML API
like the DOM or the XmlDocument in .NET).
The next release of SQL Server includes some extensions to XQuery that allow
you to modify data within an XML value, which is great if you can wait until
then, but probably of no practical use to you at the moment - sorry!
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
Can someone show me the syntax (if it is possible) to modify an attribute
value in a stored proc? For example if I have an element that is being
passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to
change the 2 to something else (like a 3) depending on a condition? Or do I
have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
change the value? Will someone please let me know? Many Thanks!
|||Thanks Graeme! That is exactly what I was looking for.
"Graeme Malcolm" wrote:

> There's no XML manipulation functionality built into SQL Server 2000 (other
> than shredding the XML into a table, performing your update, and retrieving
> it back out as XML again using a FOR XML query - definitely not an efficient
> approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
> better performing this kind of logic in the client application before
> submitting it to the stored procedure (apply a style sheet or use an XML API
> like the DOM or the XmlDocument in .NET).
> The next release of SQL Server includes some extensions to XQuery that allow
> you to modify data within an XML value, which is great if you can wait until
> then, but probably of no practical use to you at the moment - sorry!
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
> news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
> Can someone show me the syntax (if it is possible) to modify an attribute
> value in a stored proc? For example if I have an element that is being
> passed in as text like
> <CustomerName id="2" /> Is there SQLXML functionality that I can use to
> change the 2 to something else (like a 3) depending on a condition? Or do I
> have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
> change the value? Will someone please let me know? Many Thanks!
>
>

challenging likes wildcard

Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?

Thanks

Spencer

Hi

You could use

SELECT [name] FROM table WHERE REPLACE ([name] , ' ' , '' ) LIKE '%{{apple}}%'

this will remove all spaces for the prurposes of the search query

|||

Thanks Rod,

But this query will search the keyword ap ple , a pple , app le etc. This is the challenge for me.

Spencer

|||

Hi Spencer,

Can you try the below query

SELECT [name]

FROM "table" (nolock)

WHERE [name] LIKE '%' + LTRIM(RTRIM(REPLACE(REPLACE('{{ apple }}','{',''),'}',''))) + '%'

Eralper

http://www.kodyaz.com

|||

You could do something like below:

declare @.a varchar(30), @.b varchar(30), @.c varchar(30)
set @.a = '{{apple }}' -- '{{$apple$}}'
set @.b = substring(@.a, patindex('%{{%apple%}}%', @.a) + 2, 8000)
set @.c = substring(reverse(@.a), patindex(reverse('%{{%apple%}}%'), reverse(@.a)) + 2, 8000)
if @.a like '%{{%apple%}}%' and substring(@.b, 1, patindex('%apple%}}%', @.b) - 1) not like '%[^ ]%'
and substring(@.c, 1, patindex(reverse('%{{%apple%'), @.c) - 1) not like '%[^ ]%'
print 'Y'
else
print 'N'

You can perform this in a SELECT statement by using a single expression. I am assuming that there could be multiple spaces before and after the keyword. So it is not possible to write a single search pattern for LIKE using the TSQL support. The code checks for any non-space character in addition to keyword match.

|||

Thanks Umachandar

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>

cdosysmail with attachment

With cdosysmail I have gotten my code to work to programatically send emails
out as HTML.
Does anyone know how to tweak the standard CDO example to send the query
results as an attachment?
thanksI strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sample
of using CDO to send an attachment, but please read my first sentence
again).
A
"26point2er" <26point2er@.discussions.microsoft.com> wrote in message
news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
> With cdosysmail I have gotten my code to work to programatically send
> emails
> out as HTML.
> Does anyone know how to tweak the standard CDO example to send the query
> results as an attachment?
> thanks
>|||Thank you for the input. I will post my results here.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
> cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sampl
e
> of using CDO to send an attachment, but please read my first sentence
> again).
> A
>
>
> "26point2er" <26point2er@.discussions.microsoft.com> wrote in message
> news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
>
>

CDATA & FOR XML PATH

Hi,
Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is it
possible to create a node creating cdata?
For example, the statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit, type)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
Produces;
<info>
<details>
<description>text...</description>
<description>text...</description>
</details>
</info>
Using the nested for xml explicit I was hoping to be able to display the
second description node as;
<description><![CDATA[text...]]></description>
When the 'type' directive is specified it ignores the fact it should contain
cdata.
Am I missing something?...Is this possible to do?
Thanks
Pete
Hi Pete
If you use a FOR XML expression with the TYPE directive, you get an XML
datatype. And the XML datatype (since it is based on the XQuery Datamodel)
does not preserve the CDATA section information.
So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
directive at the top.
Now, I would like to better understand why you want to generate a CDATA
section in the first place. The only impact it has is to allow people to
author certain XML content without having to explicitly entitize characters
such as <, & etc.. Why would that be important during serializing a FOR XML
result?
Thanks
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is
> it possible to create a node creating cdata?
> For example, the statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit, type)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> Produces;
> <info>
> <details>
> <description>text...</description>
> <description>text...</description>
> </details>
> </info>
> Using the nested for xml explicit I was hoping to be able to display the
> second description node as;
> <description><![CDATA[text...]]></description>
> When the 'type' directive is specified it ignores the fact it should
> contain cdata.
> Am I missing something?...Is this possible to do?
> Thanks
> Pete
>
|||Hi Michael,
Thanks for your reply. My belief is that the use of the cdata directive is
necessary as I have html content stored in the database and need to be able
to be rendered as html when producing an xslt transformation. is there an
alternative way that you know of to do this in SQL Server 2005.
When I try using the same statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
without using the 'TYPE' directive the result displays as follows;
<info>
<details>
<description>text...</description>
<description><![CDATA[text...]]></description>
</details>
</info>
How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '<element>...')?...Is
there any alternative way to do this that I'm missing?
Thanks
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi Pete
> If you use a FOR XML expression with the TYPE directive, you get an XML
> datatype. And the XML datatype (since it is based on the XQuery Datamodel)
> does not preserve the CDATA section information.
> So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
> directive at the top.
> Now, I would like to better understand why you want to generate a CDATA
> section in the first place. The only impact it has is to allow people to
> author certain XML content without having to explicitly entitize
> characters such as <, & etc.. Why would that be important during
> serializing a FOR XML result?
> Thanks
> Michael
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
>
|||XSLT should not make a difference between the CDATA section and content that
just had been entitized.
Did you try to take your original output (with the TYPE directive) and pass
it through your XSLT style sheet?
Alternatively, if you need to preserve your CDATA section, you need to use a
top-level EXPLICIT mode query:
select 1 as Tag, NULL as Parent, pt_description as
[details!1!description!element],pt_description as
[details!1!description!cdata]
from property where pt_id = 8957627
for xml explicit, root('info')
Best regards
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eHmIDS0dFHA.1612@.tk2msftngp13.phx.gbl...
> Hi Michael,
> Thanks for your reply. My belief is that the use of the cdata directive
> is necessary as I have html content stored in the database and need to be
> able to be rendered as html when producing an xslt transformation. is
> there an alternative way that you know of to do this in SQL Server 2005.
> When I try using the same statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> without using the 'TYPE' directive the result displays as follows;
> <info>
> <details>
> <description>text...</description>
> <description><![CDATA[text...]]></description>
> </details>
> </info>
> How can I ensure the both the html and the element containing the cdata
> are rendered correctly (eg. <element> rather than
> '<element>...')?...Is there any alternative way to do this that I'm
> missing?
> Thanks
> Pete
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>

Categorise Bar Chart

How to create a bar chart by setting the static category myself?

I mean, for example, I have a dataset which record the number of coins different people have.

I would like to draw a bar chart which shows 3 bars with the following 3 different categories:

1) n < 5,

2) n >= 5 and n <10,

3) n>=10.

where n is the number of coins.

How can this be expressed in the categories grouping?

Thanks in advance.

Sorry, this is currently not directly supported through chart groupings. You would need to write the query (or add a calculated field on the dataset) so that you get this categorization in the data and then use those fields in the chart.

-- Robert

Catching more then one Error

Hello all, not to sure if this can be done but I am attempting to catch a series of errors with in a single TRY CATCH statement.

For example, the following code triggers a single error ( which work fine)

SET NOCOUNT ON
declare @.errmsg nvarchar(4000)
begin try
select 0/0
end try
begin catch
SET @.errmsg = 'Msg ' +
cast(ERROR_NUMBER() as varchar(20)) + ', Level ' +
cast(ERROR_SEVERITY() as varchar(20)) + ', State ' +
cast(ERROR_STATE() as varchar(20)) + ', Line ' +
cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' +
isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'')
end catch
select @.errmsg AS ERRMSG

RESULT:
Msg 8134, Level 16, State 1, Line 4, Procedure Divide by zero error encountered.

But when i execute the same TRY CATCH but use a backup routine that i purposly fail the query result shows the following ( which is what i want...)

Msg 4208, Level 16, State 0, Procedure usp_dbbackup2005_v1_5, Line 599
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Instead i only get the last error in the series.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Any Thoughts?

Thanks

Anybody have any thoughts on this? i can't see why i couldnt grab all the errors within a transaction.

Thanks

DM

|||

With my understanding about Try..Catch block (any language); it always cath one exception object and transform the control to Catch block from the try block (even C#.NET or VB.NET does the same..); I dont think it is logically possible ..

Folks what about your opinion here..

|||You wont get the stack are any further executed command errors in the error functions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Bummer, you would think you would be able to grab entire stack upon error. Obviously as far as a client is concerned the entire stack is pushed out.( which is what you would see if the statement was executed from a .net client or query analyzer.) so I guess that begs the question; how do you build a solid error handler if you are not provided all the tools to dissect the issue.

Thanks

DM

Catalog size/ratio?

I want to get a estimate of size of the catalog based on the size of the
data it is indexing.
for example, for 10G of Table size(table contains just a column of
varchar(255)), what is the size of its catalog?
I know that it depends on its content, but just want to get a idea of how
big catalog will be since I am thinking using 64 bit platform and want to
load the whole catalog inside the memory. Knowing the size of catalog help
me determine how many Gigs of memory I have to buy...
Thanks
--Xin Chen
That's really quite unpredictable. It not only depends on content, but then
on compressibility as the catalog files are compressed.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:%23uyFxuLJFHA.2640@.TK2MSFTNGP09.phx.gbl...
> I want to get a estimate of size of the catalog based on the size of the
> data it is indexing.
> for example, for 10G of Table size(table contains just a column of
> varchar(255)), what is the size of its catalog?
> I know that it depends on its content, but just want to get a idea of how
> big catalog will be since I am thinking using 64 bit platform and want to
> load the whole catalog inside the memory. Knowing the size of catalog
help
> me determine how many Gigs of memory I have to buy...
> Thanks
> --Xin Chen
>

Cast question

I'm trying to set up a query to check several fields for the value of 'Debug
'
for example. I've been successful in doing this with LIKE 'Debug' or LIKE
'Debug%'
however, I'd like to do an = comparison. Some fields are varchar, some are
text. The text ones seem to be a problem.
Is there a way to do a Cast, so that I can do an = comparison with these
types of fields?>> Is there a way to do a Cast, so that I can do an = comparison with these
If the length of the TEXT values are less than 8000, you can cast them to
VARCHAR type.
Anith