Problem rendering PDF with Diagram in code...

Hi!
I have the problem that I can not render a PDF file containing a diagram via
code. My function is essentially:
Private Sub SaveAs()
... Some initalization here...
Try
results = rs.Render(selItem.Path, format, historyID, deviceInfo, param,
credentials, showHide, encoding, mimetype, reportHistoyrParameters,
warnings, streamIDs)
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
Catch exception As Exception
HandleException(exception)
End Try
End If
End Sub
When I use a format other than PDF (e.g. Excel or MHTML) everything works
fine and I am able to retrieve the Report with the diagram included, no
Problem. On the other hand when I am rendering a PDF-File without any
Diagrams, everything works fine as well. Only when I try to render a report
that includes a Diagram in PDF, an exception is thrown. The text says
something like: "Retrieval of contents with invalid configuration for
contentstype: text/html - SOAP expects text/xml" (Sorry for not giving the
accurate error message - I am using the German version which displays German
error texts).
Thank you for your help and have a nice day
FrankHi!
There is something I like to add to my initial posting. The same error (or
some similar error) occurs when I try to render the report with the diagram
from the Report Manager Web Interface. Here the Error is that the Server
Application is not avialable (an error I get from within my code too from
time to time).
Have a nice day and thank you
Frank
"Frank Geisler" <frank_geisler@.geislers.net> schrieb im Newsbeitrag
news:O1uv2p3VEHA.1164@.tk2msftngp13.phx.gbl...
> Hi!
> I have the problem that I can not render a PDF file containing a diagram
via
> code. My function is essentially:
> Private Sub SaveAs()
> ... Some initalization here...
> Try
> results = rs.Render(selItem.Path, format, historyID, deviceInfo, param,
> credentials, showHide, encoding, mimetype, reportHistoyrParameters,
> warnings, streamIDs)
> Dim stream As FileStream = File.OpenWrite(fileName)
> stream.Write(results, 0, results.Length)
> stream.Close()
> Catch exception As Exception
> HandleException(exception)
> End Try
> End If
> End Sub
> When I use a format other than PDF (e.g. Excel or MHTML) everything works
> fine and I am able to retrieve the Report with the diagram included, no
> Problem. On the other hand when I am rendering a PDF-File without any
> Diagrams, everything works fine as well. Only when I try to render a
report
> that includes a Diagram in PDF, an exception is thrown. The text says
> something like: "Retrieval of contents with invalid configuration for
> contentstype: text/html - SOAP expects text/xml" (Sorry for not giving the
> accurate error message - I am using the German version which displays
German
> error texts).
> Thank you for your help and have a nice day
> Frank
>|||When you say diagram, do you mean an image using the image control? Are you
setting the correct MIME type for the image?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:%23Ppg%23t3VEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hi!
> There is something I like to add to my initial posting. The same error (or
> some similar error) occurs when I try to render the report with the
diagram
> from the Report Manager Web Interface. Here the Error is that the Server
> Application is not avialable (an error I get from within my code too from
> time to time).
> Have a nice day and thank you
> Frank
>
> "Frank Geisler" <frank_geisler@.geislers.net> schrieb im Newsbeitrag
> news:O1uv2p3VEHA.1164@.tk2msftngp13.phx.gbl...
> > Hi!
> >
> > I have the problem that I can not render a PDF file containing a diagram
> via
> > code. My function is essentially:
> >
> > Private Sub SaveAs()
> > ... Some initalization here...
> >
> > Try
> > results = rs.Render(selItem.Path, format, historyID, deviceInfo,
param,
> > credentials, showHide, encoding, mimetype, reportHistoyrParameters,
> > warnings, streamIDs)
> >
> > Dim stream As FileStream = File.OpenWrite(fileName)
> > stream.Write(results, 0, results.Length)
> > stream.Close()
> > Catch exception As Exception
> > HandleException(exception)
> > End Try
> > End If
> > End Sub
> >
> > When I use a format other than PDF (e.g. Excel or MHTML) everything
works
> > fine and I am able to retrieve the Report with the diagram included, no
> > Problem. On the other hand when I am rendering a PDF-File without any
> > Diagrams, everything works fine as well. Only when I try to render a
> report
> > that includes a Diagram in PDF, an exception is thrown. The text says
> > something like: "Retrieval of contents with invalid configuration for
> > contentstype: text/html - SOAP expects text/xml" (Sorry for not giving
the
> > accurate error message - I am using the German version which displays
> German
> > error texts).
> >
> > Thank you for your help and have a nice day
> >
> > Frank
> >
> >
>|||Hi Brian!
There is another idea that pops to my mind. I have two collegues who do not
have difficulties to render reports with charts in PDF. Both of them have
Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
architecture of RS right, this should not be a prerequisit. As I understand
it PDF rendering is done autnomously by the Report Server without any
additional software. Or am I wrong?
Have a nice day
Frank|||You are correct. You do not have to have PDF installed to export a report
using the PDF render.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Brian!
> There is another idea that pops to my mind. I have two collegues who do
not
> have difficulties to render reports with charts in PDF. Both of them have
> Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> architecture of RS right, this should not be a prerequisit. As I
understand
> it PDF rendering is done autnomously by the Report Server without any
> additional software. Or am I wrong?
> Have a nice day
> Frank
>|||Hi Bruce!
Thank you for your quick response. That is what I thought too. But of course
this does not solve my initial problem: Reports with charts can not be
renderd to PDF in my environment here. Today I have installed SP1 but this
did not fix my problem. Reports with charts can still not be rendered to
PDF. What shall I do? Where can I start to search the error?
Thank you and have a nice day
Frank
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> You are correct. You do not have to have PDF installed to export a report
> using the PDF render.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > Hi Brian!
> >
> > There is another idea that pops to my mind. I have two collegues who do
> not
> > have difficulties to render reports with charts in PDF. Both of them
have
> > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> > architecture of RS right, this should not be a prerequisit. As I
> understand
> > it PDF rendering is done autnomously by the Report Server without any
> > additional software. Or am I wrong?
> >
> > Have a nice day
> >
> > Frank
> >
> >
>|||Would you send me a zip of the most recent RS*.log files under C:\Program Files\Microsoft SQL
Server?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> Hi Bruce!
> Thank you for your quick response. That is what I thought too. But of course
> this does not solve my initial problem: Reports with charts can not be
> renderd to PDF in my environment here. Today I have installed SP1 but this
> did not fix my problem. Reports with charts can still not be rendered to
> PDF. What shall I do? Where can I start to search the error?
> Thank you and have a nice day
> Frank
>
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > You are correct. You do not have to have PDF installed to export a report
> > using the PDF render.
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > > Hi Brian!
> > >
> > > There is another idea that pops to my mind. I have two collegues who do
> > not
> > > have difficulties to render reports with charts in PDF. Both of them
> have
> > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> > > architecture of RS right, this should not be a prerequisit. As I
> > understand
> > > it PDF rendering is done autnomously by the Report Server without any
> > > additional software. Or am I wrong?
> > >
> > > Have a nice day
> > >
> > > Frank
> > >
> > >
> >
> >
>|||Hi Donovan!
I have done the following:
I have used the Report Manager to view the Sample-Report Employee Sales
Summery of the Adventureworks Example and here I have selected one of the
employees to get a report with some nice charts on it. Then I tried to
export it (within Report Manager) to PDF. The accordings log-entires in the
log file (ReportServer_*.log) are:
aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to GetPermissions:/
aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
GetPermissions:/SampleReports
aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
GetPermissions:/SampleReports/Employee Sales Summary
aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
'/SampleReports/Employee Sales Summary' )
aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream=''
aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
GetReportChunk('C_6_S', 1), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary',
Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
GetReportChunk('C_20_S', 1), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
'/SampleReports/Employee Sales Summary' )
aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
I hope this helps a lillte bit. Thank you very much for you effort.
Have a nice day
Frank
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
> Would you send me a zip of the most recent RS*.log files under C:\Program
Files\Microsoft SQL
> Server?
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> > Hi Bruce!
> >
> > Thank you for your quick response. That is what I thought too. But of
course
> > this does not solve my initial problem: Reports with charts can not be
> > renderd to PDF in my environment here. Today I have installed SP1 but
this
> > did not fix my problem. Reports with charts can still not be rendered
to
> > PDF. What shall I do? Where can I start to search the error?
> >
> > Thank you and have a nice day
> >
> > Frank
> >
> >
> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > You are correct. You do not have to have PDF installed to export a
report
> > > using the PDF render.
> > >
> > > --
> > > Bruce Johnson [MSFT]
> > > Microsoft SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Brian!
> > > >
> > > > There is another idea that pops to my mind. I have two collegues who
do
> > > not
> > > > have difficulties to render reports with charts in PDF. Both of them
> > have
> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand
the
> > > > architecture of RS right, this should not be a prerequisit. As I
> > > understand
> > > > it PDF rendering is done autnomously by the Report Server without
any
> > > > additional software. Or am I wrong?
> > > >
> > > > Have a nice day
> > > >
> > > > Frank
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you install SP1 yet? I know we fixed some 'Chunk not found' errors.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:eRDg9poWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hi Donovan!
> I have done the following:
> I have used the Report Manager to view the Sample-Report Employee Sales
> Summery of the Adventureworks Example and here I have selected one of the
> employees to get a report with some nice charts on it. Then I tried to
> export it (within Report Manager) to PDF. The accordings log-entires in
> the
> log file (ReportServer_*.log) are:
> aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to
> GetPermissions:/
> aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> GetPermissions:/SampleReports
> aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
> GetPermissions:/SampleReports/Employee Sales Summary
> aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
> '/SampleReports/Employee Sales Summary' )
> aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
> EnableExecutionLogging to 'True' as specified in Server system
> properties.
> aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream=''
> aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
> GetReportChunk('C_6_S', 1), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary',
> Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
> aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
> aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
> GetReportChunk('C_20_S', 1), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
> aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
> '/SampleReports/Employee Sales Summary' )
> aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
> GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> I hope this helps a lillte bit. Thank you very much for you effort.
> Have a nice day
> Frank
>
> "Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
> Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
>> Would you send me a zip of the most recent RS*.log files under C:\Program
> Files\Microsoft SQL
>> Server?
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
>> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
>> > Hi Bruce!
>> >
>> > Thank you for your quick response. That is what I thought too. But of
> course
>> > this does not solve my initial problem: Reports with charts can not be
>> > renderd to PDF in my environment here. Today I have installed SP1 but
> this
>> > did not fix my problem. Reports with charts can still not be rendered
> to
>> > PDF. What shall I do? Where can I start to search the error?
>> >
>> > Thank you and have a nice day
>> >
>> > Frank
>> >
>> >
>> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
>> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
>> > > You are correct. You do not have to have PDF installed to export a
> report
>> > > using the PDF render.
>> > >
>> > > --
>> > > Bruce Johnson [MSFT]
>> > > Microsoft SQL Server Reporting Services
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > >
>> > >
>> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
>> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
>> > > > Hi Brian!
>> > > >
>> > > > There is another idea that pops to my mind. I have two collegues
>> > > > who
> do
>> > > not
>> > > > have difficulties to render reports with charts in PDF. Both of
>> > > > them
>> > have
>> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand
> the
>> > > > architecture of RS right, this should not be a prerequisit. As I
>> > > understand
>> > > > it PDF rendering is done autnomously by the Report Server without
> any
>> > > > additional software. Or am I wrong?
>> > > >
>> > > > Have a nice day
>> > > >
>> > > > Frank
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Hi Brian!
Yes I installed SP1 and hoped that the error went away. Unfortunately it did
not. I use the german version of Reporting Services and also the german SP1.
Have a nice day
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> schrieb im
Newsbeitrag news:OSebB7rWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Did you install SP1 yet? I know we fixed some 'Chunk not found' errors.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:eRDg9poWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > Hi Donovan!
> >
> > I have done the following:
> >
> > I have used the Report Manager to view the Sample-Report Employee Sales
> > Summery of the Adventureworks Example and here I have selected one of
the
> > employees to get a report with some nice charts on it. Then I tried to
> > export it (within Report Manager) to PDF. The accordings log-entires in
> > the
> > log file (ReportServer_*.log) are:
> >
> > aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to
> > GetPermissions:/
> > aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> > GetPermissions:/SampleReports
> > aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
> > GetPermissions:/SampleReports/Employee Sales Summary
> > aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
> > '/SampleReports/Employee Sales Summary' )
> > aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
> > EnableExecutionLogging to 'True' as specified in Server system
> > properties.
> > aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream=''
> > aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
> > GetReportChunk('C_6_S', 1), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> > aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary',
> > Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
> > aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
> > aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
> > GetReportChunk('C_20_S', 1), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> > aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
> > aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
> > '/SampleReports/Employee Sales Summary' )
> > aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
> > GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> >
> > I hope this helps a lillte bit. Thank you very much for you effort.
> >
> > Have a nice day
> >
> > Frank
> >
> >
> > "Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
> > Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
> >> Would you send me a zip of the most recent RS*.log files under
C:\Program
> > Files\Microsoft SQL
> >> Server?
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> >> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> >> > Hi Bruce!
> >> >
> >> > Thank you for your quick response. That is what I thought too. But of
> > course
> >> > this does not solve my initial problem: Reports with charts can not
be
> >> > renderd to PDF in my environment here. Today I have installed SP1 but
> > this
> >> > did not fix my problem. Reports with charts can still not be
rendered
> > to
> >> > PDF. What shall I do? Where can I start to search the error?
> >> >
> >> > Thank you and have a nice day
> >> >
> >> > Frank
> >> >
> >> >
> >> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> >> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> >> > > You are correct. You do not have to have PDF installed to export a
> > report
> >> > > using the PDF render.
> >> > >
> >> > > --
> >> > > Bruce Johnson [MSFT]
> >> > > Microsoft SQL Server Reporting Services
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> > >
> >> > >
> >> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> >> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> >> > > > Hi Brian!
> >> > > >
> >> > > > There is another idea that pops to my mind. I have two collegues
> >> > > > who
> > do
> >> > > not
> >> > > > have difficulties to render reports with charts in PDF. Both of
> >> > > > them
> >> > have
> >> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I
understand
> > the
> >> > > > architecture of RS right, this should not be a prerequisit. As I
> >> > > understand
> >> > > > it PDF rendering is done autnomously by the Report Server without
> > any
> >> > > > additional software. Or am I wrong?
> >> > > >
> >> > > > Have a nice day
> >> > > >
> >> > > > Frank
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi Brian!
Another thought comes to my mind. How can I determine if the update
succeded?
Have a nice day
Frank|||When you go to the http://server/reportserver, you should see version
8.00.878.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:eq62ROPXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Brian!
> Another thought comes to my mind. How can I determine if the update
> succeded?
> Have a nice day
> Frank
>|||Hi Brian!
Thank you very much. I have found the page and there is indeed Version
8.00.878.00. So my Reporting Services Server is up to date. I wonder what is
wrong here with my stuff because I heared from different people that they
could perfectly render PDFs with Charts. Maybe I should start up from
scratch and install the whole Reporting Services Server (including OS and
SQL-Server) again. What do you think?
Have a nice day
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> schrieb im
Newsbeitrag news:ef0V7nZXEHA.3892@.TK2MSFTNGP09.phx.gbl...
> When you go to the http://server/reportserver, you should see version
> 8.00.878.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:eq62ROPXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Hi Brian!
> >
> > Another thought comes to my mind. How can I determine if the update
> > succeded?
> >
> > Have a nice day
> >
> > Frank
> >
> >
>

Problem related to trigger

hi ,
is it possible to use indexof and substring function in trigger.
i am using sql server 2005, please help me how to use string function
in trigger.
thanks
sathya narayanan
narayanan@.gsdindia.com
Hi
SQL Server 2005 Beta/CTP questions to:
http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:

> hi ,
> is it possible to use indexof and substring function in trigger.
> i am using sql server 2005, please help me how to use string function
> in trigger.
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>
|||hi,
when i am creating update trigger , i cannot read binary or image data
in trigger , i am using sql server 2005 , please help me how to use
image data in after update trigger
my sample code:
alter trigger docsUpdate
on docs
after update
as
declare @.metainfo binary(8000)
declare @.startIndex int
declare @.endIndex int
declare @.metainfoProcc varchar(8000)
declare @.guid varchar(8000)
declare @.order varchar(8000)
Declare metaInfoVal CURSOR for select metainfo from deleted
open metaInfoVal
fetch next from metaInfoVal into @.metainfo
while @.@.fetch_status =0
begin
set @.metainfoProcc = cast(@.metainfo as varchar(8000))
set @.startIndex = charindex('GUID:SW|',@.metainfoProcc,1)+9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex)
set @.guid = substring(@.metainfoProcc ,@.startIndex ,
@.endIndex-@.startIndex)
set @.startIndex = charindex('Order:SW|',@.metainfoProcc,1)+9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex)
set @.order = substring(@.metainfoProcc ,@.startIndex
,@.endIndex-@.startIndex)
print 'Guid:'+@.guid
print 'order:'+@.order
fetch next from metaInfoVal into @.metainfo
end
close metaInfoVal
deallocate metaInfoVal
it shows error as :
Msg 311, Level 16, State 1, Procedure docsUpdate, Line 11
Cannot use text, ntext, or image columns in the 'inserted' and
'deleted' tables.
sathya narayanan v
narayanan@.gsdindia.com

Problem related to trigger

hi ,
is it possible to use indexof and substring function in trigger.
i am using sql server 2005, please help me how to use string function
in trigger.
thanks
sathya narayanan
narayanan@.gsdindia.comHi
SQL Server 2005 Beta/CTP questions to:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:
> hi ,
> is it possible to use indexof and substring function in trigger.
> i am using sql server 2005, please help me how to use string function
> in trigger.
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>|||hi,
when i am creating update trigger , i cannot read binary or image data
in trigger , i am using sql server 2005 , please help me how to use
image data in after update trigger
my sample code:
alter trigger docsUpdate
on docs
after update
as
declare @.metainfo binary(8000)
declare @.startIndex int
declare @.endIndex int
declare @.metainfoProcc varchar(8000)
declare @.guid varchar(8000)
declare @.order varchar(8000)
Declare metaInfoVal CURSOR for select metainfo from deleted
open metaInfoVal
fetch next from metaInfoVal into @.metainfo
while @.@.fetch_status =0
begin
set @.metainfoProcc = cast(@.metainfo as varchar(8000))
set @.startIndex = charindex('GUID:SW|',@.metainfoProcc,1)+9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex)
set @.guid = substring(@.metainfoProcc ,@.startIndex ,
@.endIndex-@.startIndex)
set @.startIndex = charindex('Order:SW|',@.metainfoProcc,1)+9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex)
set @.order = substring(@.metainfoProcc ,@.startIndex
,@.endIndex-@.startIndex)
print 'Guid:'+@.guid
print 'order:'+@.order
fetch next from metaInfoVal into @.metainfo
end
close metaInfoVal
deallocate metaInfoVal
it shows error as :
Msg 311, Level 16, State 1, Procedure docsUpdate, Line 11
Cannot use text, ntext, or image columns in the 'inserted' and
'deleted' tables.
sathya narayanan v
narayanan@.gsdindia.com

Problem related to trigger

hi ,
is it possible to use indexof and substring function in trigger.
i am using sql server 2005, please help me how to use string function
in trigger.
thanks
sathya narayanan
narayanan@.gsdindia.comHi
SQL Server 2005 Beta/CTP questions to:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:

> hi ,
> is it possible to use indexof and substring function in trigger.
> i am using sql server 2005, please help me how to use string function
> in trigger.
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>|||hi,
when i am creating update trigger , i cannot read binary or image data
in trigger , i am using sql server 2005 , please help me how to use
image data in after update trigger
my sample code:
alter trigger docsUpdate
on docs
after update
as
declare @.metainfo binary(8000)
declare @.startIndex int
declare @.endIndex int
declare @.metainfoProcc varchar(8000)
declare @.guid varchar(8000)
declare @.order varchar(8000)
Declare metaInfoVal CURSOR for select metainfo from deleted
open metaInfoVal
fetch next from metaInfoVal into @.metainfo
while @.@.fetch_status =0
begin
set @.metainfoProcc = cast(@.metainfo as varchar(8000))
set @.startIndex = charindex('GUID:SW|',@.metainfoProcc,1)+9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex
)
set @.guid = substring(@.metainfoProcc ,@.startIndex ,
@.endIndex-@.startIndex)
set @.startIndex = charindex('Order:SW|',@.metainfoProcc,1)+
9
set @.endIndex = charindex('*',@.metainfoProcc,@.startIndex
)
set @.order = substring(@.metainfoProcc ,@.startIndex
,@.endIndex-@.startIndex)
print 'Guid:'+@.guid
print 'order:'+@.order
fetch next from metaInfoVal into @.metainfo
end
close metaInfoVal
deallocate metaInfoVal
it shows error as :
Msg 311, Level 16, State 1, Procedure docsUpdate, Line 11
Cannot use text, ntext, or image columns in the 'inserted' and
'deleted' tables.
sathya narayanan v
narayanan@.gsdindia.com

Problem performing a join on a function in a SQL query

Hello,

Can someone explain why this code contains the following error:

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "TheTable.StartValue" could not be bound.

CREATE FUNCTION MyFunction(@.StartValue int)

RETURNS @.MyTable TABLE

(

NextValue int NOT NULL

)

AS

BEGIN

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 1)

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 2)

RETURN

END

GO

CREATE TABLE TheTable

(

StartValue int NOT NULL

)

GO

INSERT INTO TheTable(StartValue)

VALUES (10)

INSERT INTO TheTable(StartValue)

VALUES (20)

GO

SELECT *

FROM TheTable CROSS JOIN

MyFunction(TheTable.StartValue)

You can′t do that per row. The logic is quite simple that you presented here, what about doing

SELECT StartValue, StartValue+1,StartValue+2
From SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

In SQL Server 2000, this is not possible. However, in 2005, you can use the CROSS APPLY join operator:

SELECT *
FROM TheTable
CROSS APPLY MyFunction(TheTable.StartValue)

Interesting function. If you don't mind, could you share the purpose?

|||

Hi,

you cannot use Table's Column as a parameter to the function. Only variables or Static Literals can be passed as an argument to the function

|||

Thanks for your reply.

I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.

|||

Thanks for your reply.

I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.

problem passing UDF scalar result to UDF table function

I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.

My functions are defined like so:

drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go

drop function dbo.table_func
go
create function dbo.table_func(@.p int)
returns table
return (select @.p as id )
go

Given the above, I can do the following:

Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
----
1

Selecting from the table function works, if i pass a
constant value (or a variable)

1> select id from dbo.table_func(1)
2> go
id
----
1

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly"roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...

<snip
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?
> Thanks kindly

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

--
Paul Horan
VCI Springfield, MA|||roger (rogerr@.softix.com) writes:
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?

I don't think you can pass expressions as parameters to table-valued
functions; you can only pass constants and variables.

However, I was looking around in Books Online, but I could not find
a passage which actually says so.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI2dnT_yvLdgPQndRVn-hQ@.adelphia.com>...
> "roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...
> <snip>
> It's barking at the period in the nested call. Try running it without qualifying the function name:
> 1> select id from dbo.table_func( scalar_func() )

No, that isn't it.

You have to qualify a scalar function with the owner name.
Curiously, you don't have to qualify a table valued function
in this way.

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.

I can't see any particular rhyme or reason to this,
it's just the way it seems to be.|||roger (rogerr@.softix.com) writes:
> So, this works
> select * from table_function()
> but this does not
> select scalar_function() from table
> requiring instead
> select dbo.scalar_function() from table.
>
> I can't see any particular rhyme or reason to this,
> it's just the way it seems to be.

The reason is that with out the reqiurement of a two-part name there
would be no possibility to distinguish between scalar system functions
and scalar UDF. And if they look the same syntactically, they share the
same name space, which would mean that each time MS added a new system
function, they would risk to break existing code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem passing UDF scalar result to UDF table function

I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.

My functions are defined like so:

drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go

drop function dbo.table_func
go
create function dbo.table_func(@.p int)
returns table
return (select @.p as id )
go

Given the above, I can do the following:

Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
----
1

Selecting from the table function works, if i pass a
constant value (or a variable)

1> select id from dbo.table_func(1)
2> go
id
----
1

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly"roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...

<snip
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?
> Thanks kindly

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

--
Paul Horan
VCI Springfield, MA|||roger (rogerr@.softix.com) writes:
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?

I don't think you can pass expressions as parameters to table-valued
functions; you can only pass constants and variables.

However, I was looking around in Books Online, but I could not find
a passage which actually says so.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI2dnT_yvLdgPQndRVn-hQ@.adelphia.com>...
> "roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...
> <snip>
> It's barking at the period in the nested call. Try running it without qualifying the function name:
> 1> select id from dbo.table_func( scalar_func() )

No, that isn't it.

You have to qualify a scalar function with the owner name.
Curiously, you don't have to qualify a table valued function
in this way.

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.

I can't see any particular rhyme or reason to this,
it's just the way it seems to be.|||roger (rogerr@.softix.com) writes:
> So, this works
> select * from table_function()
> but this does not
> select scalar_function() from table
> requiring instead
> select dbo.scalar_function() from table.
>
> I can't see any particular rhyme or reason to this,
> it's just the way it seems to be.

The reason is that with out the reqiurement of a two-part name there
would be no possibility to distinguish between scalar system functions
and scalar UDF. And if they look the same syntactically, they share the
same name space, which would mean that each time MS added a new system
function, they would risk to break existing code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.softix.com) writes:
> > So, this works
> > select * from table_function()
> > but this does not
> > select scalar_function() from table
> > requiring instead
> > select dbo.scalar_function() from table.
> > I can't see any particular rhyme or reason to this,
> > it's just the way it seems to be.
> The reason is that with out the reqiurement of a two-part name there
> would be no possibility to distinguish between scalar system functions
> and scalar UDF. And if they look the same syntactically, they share the
> same name space, which would mean that each time MS added a new system
> function, they would risk to break existing code.

Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg

select * from table(my_user_function(args))

I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.

Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.

As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.

Still, I'm thankful that it is there at all I guess.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.softix.com) writes:
> > So, this works
> > select * from table_function()
> > but this does not
> > select scalar_function() from table
> > requiring instead
> > select dbo.scalar_function() from table.
> > I can't see any particular rhyme or reason to this,
> > it's just the way it seems to be.
> The reason is that with out the reqiurement of a two-part name there
> would be no possibility to distinguish between scalar system functions
> and scalar UDF. And if they look the same syntactically, they share the
> same name space, which would mean that each time MS added a new system
> function, they would risk to break existing code.

Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg

select * from table(my_user_function(args))

I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.

Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.

As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.

Still, I'm thankful that it is there at all I guess.|||roger (rogerr@.softix.com) writes:
> Oracle PL/SQL uses the keyword TABLE to introduce a function
> that returns a table. eg
> select * from table(my_user_function(args))
> I would have thought that the context in which the function
> is being called would tell you whether a scalar or table result
> is required, but maybe there are more scenarios than I can imagine
> off hand.

The issue is about distinguishing scalar functions from table functions.
The issue is about distinguishing different sorts of scalar functions,
system functions vs. user-defined ones. SQL Server have no problem
to tell whether you are using a table-valued or scalar function. Or
more correctly: there are no places in the grammar where you can use both.

The system functions had already invaded the flat name space for

SELECT fun(x)

Of course, there are a couple of more options MS could have chosen. For
instance decide that all future scalar system functions would have a
prefix.

This is actually what they have done for table-valued functions:

SELECT * FROM ::fn_helpcollations()

This was possible, since there was no table-valued system functions
prior to SQL2000.

> As is not being able to pass the result of another function as
> an argument. Kind of smells like this stuff was added in a hurry.

It is quite consistent with how you call stored procedures. You
cannot pass expressions in calls to stored procedures with the
EXEC statement. And in fact, you can invoke scalar functions with
the EXEC statement too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Problem passing a variable into a table-valued function

Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?select car.id, car.licenceNumber, car.brand, car.model,
dbo.getCurrentDriver(car.id) as driverName
from car|||[sniped]

select car.id, car.licenceNumber, car.brand, car.model,
, dbo.getCurrentDriver(car.id) as driverName
from car

??|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...|||dote. had'nt thought about that.|||also, part of the problem is that "license" is spelled wrong ;)|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...

Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.
It's certainly fun trying to write SQL for tables whose columns are called "identifer" and "sirname"|||Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...
What do you expect to happen if your table function returns more than one record or more than one column? And if it always returns one record and one column, then it is a scalar function and should be defined as such.|||Hasn't this something to do with the missing schema name (owner in SQL 2000) when calling the function? Althought it beats me why the @.CarID example seems to work.

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from dbo.getCurrentDriver(car.id)) as driverName
from carsql

Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....