Excel export

Hello,
I am having a problem when exporting a report to Excel. I am using the
SQL RS web service to render a report.
It seems that the report is always rendered using parameters default
values. It is not using parameters that i pass to the service. I am
having this problem only when rendering in Excel format. PDF is
working ok.
Anyone already seen that problem?
using SQL RS with SP1
ThanksWhat are the execution options for your report? If you've configured the
report execution options to be 'snapshot' then you cannot alter the values
of any parameters that affect the query after the snapshot has been taken.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Renaud Langis" <rlangis@.sympatico.ca> wrote in message
news:l273g0lrcb8ao5ibu0du8rcvnhlbfuvi5v@.4ax.com...
> Hello,
> I am having a problem when exporting a report to Excel. I am using the
> SQL RS web service to render a report.
> It seems that the report is always rendered using parameters default
> values. It is not using parameters that i pass to the service. I am
> having this problem only when rendering in Excel format. PDF is
> working ok.
> Anyone already seen that problem?
> using SQL RS with SP1
> Thanks|||i am using the web service through ServerXmlHttp. with command =Render.
It is working well when exporting to pdf.
On Mon, 26 Jul 2004 14:39:58 -0700, "Lukasz Pawlowski [MSFT]"
<lukaszp@.online.microsoft.com> wrote:
>What are the execution options for your report? If you've configured the
>report execution options to be 'snapshot' then you cannot alter the values
>of any parameters that affect the query after the snapshot has been taken.
>-Lukasz

Excel "Saved as Web Page" import

I am trying to import spreadsheets from web pages on a regular basis. I do not have any control over their format but they have all been saved as web pages from Excel.

There are two parts to this:
a) Downloading the files
b) Importing the downloads

Right now I am focusing on b) Importing the downloads.

I have tried
1) Using an Excel connection in DTS
2) Using OPENROWSET to access the spreadsheets more directly from TSQL
3) Using a linked server to the file.

All of these seem to use the OLEDB 4.0 provider and they all work if I convert the *.htm file to a *.xls file using Excel first.

With one curious exception none of them work from the *.htm files. DTS is the exception, if I first open the *.htm files in Excel the transformation task succeeds.

I don't really want to use Excel at all on the server. It's strange that DTS does work when the *.htm file is open.

I am trying to get some insight into the various OLEDB options to use hoping that will help me avoid having to use Excel. Perhaps I would just be better off using SQL Server 2005 and SSIS?A couple of things here, Oliver.

First, Excel exported to HTML isn't a valuable format when it comes to being able to import off of that data. Why even export it at all when you can just hook into the raw Excel file?

Secondly, if you want to use SSIS, we can help you with that, though I'm not sure that loading an Excel generated HTML file will work. If you are going to stick with DTS, then you should ask this on the forum for that tool: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en|||Phil, thanks for the reply but as I mentioned the files are in html and I cannot change that.

Has anyone here used SSIS with an html formatted Excel file?

Ian.|||Best of luck with that! I'd work on getting a new format, 'cause you're going to have to write your own script, or scour the Internet for a custom module already built. There isn't an HTML source in SSIS, nor should there be, in my opinion. It's not really suitable for data transmission.sql

Examples of Web ReportViewer with Remote Processing

We have tried a bunch of different ways to run reports in the ASP.NET Web
ReportViewer. Every attempt to do it ourselves has failed to render.
We have a number of examples of local processing in Web pages. Heck, we
even have built a number of Windows Forms applications that use both local
and remote processing. But we cannot find anything that will cause ASP.Net
to render.
Is it possible that using the Viewer in this case does not work?
Walt Crosby
Chief Architect
Everyday Wirelessdoes your RS and ReportViewer server are on 2 different servers?
and do you use the NTLM authentication?
if yes, you suffer Kerberos issue, search on the MS web site to know how to
setup it.
can you send the the code of your ASPX page which contains the control?
"Microsoft" <Walt.Crosby@.everydaywireless.com> wrote in message
news:ugKGzreWHHA.600@.TK2MSFTNGP05.phx.gbl...
> We have tried a bunch of different ways to run reports in the ASP.NET Web
> ReportViewer. Every attempt to do it ourselves has failed to render.
> We have a number of examples of local processing in Web pages. Heck, we
> even have built a number of Windows Forms applications that use both local
> and remote processing. But we cannot find anything that will cause
> ASP.Net to render.
> Is it possible that using the Viewer in this case does not work?
> Walt Crosby
> Chief Architect
> Everyday Wireless
>|||The Reports Manager and ReportServer are on the same machine as the Web
Site. We have used Report Viewer many times from Windows Forms applications
with very few problems. We have yet to find any examples of Remote Server
processing with Web Report Viewer...
Walt Crosby
"Jeje" <willgart@.hotmail.com> wrote in message
news:1215D799-3869-4CAC-8550-3C5B049AC185@.microsoft.com...
> does your RS and ReportViewer server are on 2 different servers?
> and do you use the NTLM authentication?
> if yes, you suffer Kerberos issue, search on the MS web site to know how
> to setup it.
> can you send the the code of your ASPX page which contains the control?
>
> "Microsoft" <Walt.Crosby@.everydaywireless.com> wrote in message
> news:ugKGzreWHHA.600@.TK2MSFTNGP05.phx.gbl...
>> We have tried a bunch of different ways to run reports in the ASP.NET Web
>> ReportViewer. Every attempt to do it ourselves has failed to render.
>> We have a number of examples of local processing in Web pages. Heck, we
>> even have built a number of Windows Forms applications that use both
>> local and remote processing. But we cannot find anything that will cause
>> ASP.Net to render.
>> Is it possible that using the Viewer in this case does not work?
>> Walt Crosby
>> Chief Architect
>> Everyday Wireless

Example of SRS Graph in ASP.Net Page?

I'm looking for an example of a programically generated graph (report) embeded
on an ASPX web page?
TIA
JeffP....what do you want?
Display a chart coming from an RS report in an ASPX page?
Display a complete report in an ASPX page?
Display a complete report as an image in an ASPX page? (like the exporting
to a TIFF image feature)
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> a écrit dans le message de news:
ezAYnXJmEHA.3612@.TK2MSFTNGP12.phx.gbl...
> I'm looking for an example of a programically generated graph (report)
> embeded
> on an ASPX web page?
> TIA
> JeffP....
>|||Jeje,
Do you have links to examples of any of the following?
> Display a chart coming from an RS report in an ASPX page?
> Display a complete report in an ASPX page?
> Display a complete report as an image in an ASPX page? (like the exporting
> to a TIFF image feature)
However, I do think I want to do this one, > Display a chart coming from an RS
report in an ASPX page?
JeffP...
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23bhmltSmEHA.3968@.TK2MSFTNGP11.phx.gbl...
> what do you want?
> Display a chart coming from an RS report in an ASPX page?
> Display a complete report in an ASPX page?
> Display a complete report as an image in an ASPX page? (like the exporting
> to a TIFF image feature)
>
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> a écrit dans le message de news:
> ezAYnXJmEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > I'm looking for an example of a programically generated graph (report)
> > embeded
> > on an ASPX web page?
> >
> > TIA
> >
> > JeffP....
> >
> >
>|||Please have a look at the following link : http://msdn.microsoft.com/library/default.asp?
url=3D/library/en-
us/rsprog/htm/rsp_ref_soapapi_service_lz_49f6.asp
Moreover you can also render only an report returned by the report in an aspx page.
All you have to do is read the stream that follows you report(a byte array) and write it out on an aspx page.
You can also render the whole report as an image all you have to do is specify the rendering format as image.
>--Original Message--
>Jeje,
>Do you have links to examples of any of the following?
>> Display a chart coming from an RS report in an ASPX page?
>> Display a complete report in an ASPX page?
>> Display a complete report as an image in an ASPX page? (like the exporting
>> to a TIFF image feature)
>However, I do think I want to do this one, > Display a chart coming from an RS
>report in an ASPX page?
>JeffP...
>
>"J=E9j=E9" <willgart@.BBBhotmailAAA.com> wrote in message
>news:%23bhmltSmEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> what do you want?
>> Display a chart coming from an RS report in an ASPX page?
>> Display a complete report in an ASPX page?
>> Display a complete report as an image in an ASPX page? (like the exporting
>> to a TIFF image feature)
>>
>> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> a =E9crit dans le message de news:
>> ezAYnXJmEHA.3612@.TK2MSFTNGP12.phx.gbl...
>> > I'm looking for an example of a programically generated graph (report)
>> > embeded
>> > on an ASPX web page?
>> >
>> > TIA
>> >
>> > JeffP....
>> >
>> >
>>
>
>.
>|||Thanks Ravi, I haven't checked out the links but I will.
I got a bit of a reprieve in that they realized that each time they would come
to the firt pg of the web app, they graph would try to refresh, which would be
way to many round-trips to the server, they opted for a once a week data view
per market center TIFF either scheduled or manually created.
I'll still try a one-off render of the graph in my ASPX pg, but I was comming to
the conclusion that if they "had" needed the real-time graph, that the entire
first pg would be a report with links to do stuff.
TTA
JeffP....
"Ravi" <ravikantkv@.rediffmail.com> wrote in message
news:158101c499a9$b9fd6250$a401280a@.phx.gbl...
Please have a look at the following link :
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/rsprog/htm/rsp_ref_soapapi_service_lz_49f6.asp
Moreover you can also render only an report returned by
the report in an aspx page.
All you have to do is read the stream that follows you
report(a byte array) and write it out on an aspx page.
You can also render the whole report as an image all you
have to do is specify the rendering format as image.
>--Original Message--
>Jeje,
>Do you have links to examples of any of the following?
>> Display a chart coming from an RS report in an ASPX
page?
>> Display a complete report in an ASPX page?
>> Display a complete report as an image in an ASPX page?
(like the exporting
>> to a TIFF image feature)
>However, I do think I want to do this one, > Display a
chart coming from an RS
>report in an ASPX page?
>JeffP...
>
>"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>news:%23bhmltSmEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> what do you want?
>> Display a chart coming from an RS report in an ASPX
page?
>> Display a complete report in an ASPX page?
>> Display a complete report as an image in an ASPX page?
(like the exporting
>> to a TIFF image feature)
>>
>> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> a écrit dans le
message de news:
>> ezAYnXJmEHA.3612@.TK2MSFTNGP12.phx.gbl...
>> > I'm looking for an example of a programically
generated graph (report)
>> > embeded
>> > on an ASPX web page?
>> >
>> > TIA
>> >
>> > JeffP....
>> >
>> >
>>
>
>.
>

Example of SetReportParameters (please)

I am building a web application which uses the report service to provide the
reports. (This is to allow outside users access to specific reports, I
render the report on the web-server, not the sql server).
Currently I have some reports built and tested (http://[Report server
name]/reports). I have build my web-interface and I can list the reports I
want to allow access to (while hiding the rest). I can also pull back the
name and default values for each of the parameters for the report. I can
even render and display the reports in question PROVIDED the parameters have
defaults.
I can ONLY render reports that have default parameters and cannot figure out
how to change the values in those parameters to render a different report.
For example, I have a report that takes 3 parameters: ApplicationID,
AppVersion, Language. All parameters have a default value so I can render
the report on the web server. I can also (and do) add to the calling page, a
label and textbox for each parameter. The label is the Parameter prompt, the
textbox contains the default value.
What I cannot do is change the value of the parameters.
I have found SetReportParameters in the BOL.
Public Sub SetReportParameters(
ByVal Report As String _
ByVal Parameters() As [Namespace].ReportParameter _
)
That's pretty much it.
Anyone have an example of this?
I need to do something like (pseudo code):
Parameter("ID") = "125"
Parameter("Version") = "3"
Parameter("Language") = "3"
etc.
I also searched the BOL for this: Parameters.SetValue but had no hits.
This should not be hard but I can't find an example.
begin 666 icon_smile_angry.gif
M1TE&.#EA#P`/`+,``/____R!AOK5J/IE9OI52OI&./H\*?HO&_HC%/H8#0``
M`,# P ```````````````"'Y! $```L`+ `````/``\`0 17<$FI:IWTE$+Z
M+L:!),JB<-4P$-584@.DBSPF)G9LWVB9'#(J @.A5RF6('2^4@.>MV4F.?G0'+V
C?!Z1$?<3`D&BJ@.^H(H8KR _(4+0I8@.JF7&&<H&NUBR0"`#L`
`
endHow are you calling the Report Server and receiving/displaying the reports?
You can affectively set the value of parameters by passing them in the URL
call to the report.
Would this work in your case?
Brian
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:%23AK%23c6DaEHA.3016@.tk2msftngp13.phx.gbl...
> I am building a web application which uses the report service to provide
the
> reports. (This is to allow outside users access to specific reports, I
> render the report on the web-server, not the sql server).
> Currently I have some reports built and tested (http://[Report server
> name]/reports). I have build my web-interface and I can list the reports I
> want to allow access to (while hiding the rest). I can also pull back the
> name and default values for each of the parameters for the report. I can
> even render and display the reports in question PROVIDED the parameters
have
> defaults.
> I can ONLY render reports that have default parameters and cannot figure
out
> how to change the values in those parameters to render a different report.
> For example, I have a report that takes 3 parameters: ApplicationID,
> AppVersion, Language. All parameters have a default value so I can render
> the report on the web server. I can also (and do) add to the calling page,
a
> label and textbox for each parameter. The label is the Parameter prompt,
the
> textbox contains the default value.
> What I cannot do is change the value of the parameters.
> I have found SetReportParameters in the BOL.
> Public Sub SetReportParameters(
> ByVal Report As String _
> ByVal Parameters() As [Namespace].ReportParameter _
> )
> That's pretty much it.
> Anyone have an example of this?
> I need to do something like (pseudo code):
> Parameter("ID") = "125"
> Parameter("Version") = "3"
> Parameter("Language") = "3"
> etc.
> I also searched the BOL for this: Parameters.SetValue but had no hits.
> This should not be hard but I can't find an example.
>
>|||No.
I am using the application on an external web-server which communicates
(securely we would hope!) to the SQL server Reporting Service on an internal
server.
It is imperative that we do not give users the ability to guess or
ultimately spoof some of the hidden parameters.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OR9NEPEaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> How are you calling the Report Server and receiving/displaying the
reports?
> You can affectively set the value of parameters by passing them in the URL
> call to the report.
> Would this work in your case?
> Brian
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:%23AK%23c6DaEHA.3016@.tk2msftngp13.phx.gbl...
> > I am building a web application which uses the report service to provide
> the
> > reports. (This is to allow outside users access to specific reports, I
> > render the report on the web-server, not the sql server).
> >
> > Currently I have some reports built and tested (http://[Report server
> > name]/reports). I have build my web-interface and I can list the reports
I
> > want to allow access to (while hiding the rest). I can also pull back
the
> > name and default values for each of the parameters for the report. I can
> > even render and display the reports in question PROVIDED the parameters
> have
> > defaults.
> >
> > I can ONLY render reports that have default parameters and cannot figure
> out
> > how to change the values in those parameters to render a different
report.
> >
> > For example, I have a report that takes 3 parameters: ApplicationID,
> > AppVersion, Language. All parameters have a default value so I can
render
> > the report on the web server. I can also (and do) add to the calling
page,
> a
> > label and textbox for each parameter. The label is the Parameter prompt,
> the
> > textbox contains the default value.
> >
> > What I cannot do is change the value of the parameters.
> >
> > I have found SetReportParameters in the BOL.
> >
> > Public Sub SetReportParameters(
> > ByVal Report As String _
> > ByVal Parameters() As [Namespace].ReportParameter _
> > )
> >
> > That's pretty much it.
> >
> > Anyone have an example of this?
> >
> > I need to do something like (pseudo code):
> >
> > Parameter("ID") = "125"
> > Parameter("Version") = "3"
> > Parameter("Language") = "3"
> >
> > etc.
> >
> > I also searched the BOL for this: Parameters.SetValue but had no hits.
> >
> > This should not be hard but I can't find an example.
> >
> >
> >
>|||The Render method of the Web service takes a Parameters argument that
represents the report-specific parameters. The Render documentation has an
example of passing parameters:
http://msdn.microsoft.com/library/?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_86ea.asp?frame=true
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:Oz0HxhEaEHA.1000@.TK2MSFTNGP12.phx.gbl...
> No.
> I am using the application on an external web-server which communicates
> (securely we would hope!) to the SQL server Reporting Service on an
internal
> server.
> It is imperative that we do not give users the ability to guess or
> ultimately spoof some of the hidden parameters.
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OR9NEPEaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> > How are you calling the Report Server and receiving/displaying the
> reports?
> > You can affectively set the value of parameters by passing them in the
URL
> > call to the report.
> >
> > Would this work in your case?
> >
> > Brian
> >
> >
> > "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> > news:%23AK%23c6DaEHA.3016@.tk2msftngp13.phx.gbl...
> > > I am building a web application which uses the report service to
provide
> > the
> > > reports. (This is to allow outside users access to specific reports, I
> > > render the report on the web-server, not the sql server).
> > >
> > > Currently I have some reports built and tested (http://[Report server
> > > name]/reports). I have build my web-interface and I can list the
reports
> I
> > > want to allow access to (while hiding the rest). I can also pull back
> the
> > > name and default values for each of the parameters for the report. I
can
> > > even render and display the reports in question PROVIDED the
parameters
> > have
> > > defaults.
> > >
> > > I can ONLY render reports that have default parameters and cannot
figure
> > out
> > > how to change the values in those parameters to render a different
> report.
> > >
> > > For example, I have a report that takes 3 parameters: ApplicationID,
> > > AppVersion, Language. All parameters have a default value so I can
> render
> > > the report on the web server. I can also (and do) add to the calling
> page,
> > a
> > > label and textbox for each parameter. The label is the Parameter
prompt,
> > the
> > > textbox contains the default value.
> > >
> > > What I cannot do is change the value of the parameters.
> > >
> > > I have found SetReportParameters in the BOL.
> > >
> > > Public Sub SetReportParameters(
> > > ByVal Report As String _
> > > ByVal Parameters() As [Namespace].ReportParameter _
> > > )
> > >
> > > That's pretty much it.
> > >
> > > Anyone have an example of this?
> > >
> > > I need to do something like (pseudo code):
> > >
> > > Parameter("ID") = "125"
> > > Parameter("Version") = "3"
> > > Parameter("Language") = "3"
> > >
> > > etc.
> > >
> > > I also searched the BOL for this: Parameters.SetValue but had no hits.
> > >
> > > This should not be hard but I can't find an example.
> > >
> > >
> > >
> >
> >
>

example of Asynchronous.

Hello :

I have a Web application with reports which lasts for a long time, for it I reflected to make call Asynchronous for reports

(that is I execute the report and warned the user when the report is ready).

I found an example which supplied with SQL Server, but as I am novice C# I understands not everything in the example

( AsynchronousRenderCS).

Please indicate me a simpler example.

Thank you.

Actually, this one is quite simple, what do you want to have at least ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Example code for user tree

One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks
GO
DROP TABLE LI_Users
GO
DECLARE @.d1 DATETIME
, @.d2 DATETIME
, @.d3 DATETIME
, @.d4 DATETIME
, @.d5 DATETIME
, @.d6 DATETIME
, @.d7 DATETIME
, @.d8 DATETIME

SELECT @.d1 = GetDate()

CREATE TABLE LI_Users (
uid INT
PRIMARY KEY (uid)
)

SELECT @.d2 = GetDate()

CREATE TABLE LI_UserLinks (
uid_from INT
CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from)
REFERENCES LI_Users (uid)
, uid_to INT
CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to)
REFERENCES LI_Users (uid)
CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to)
)

ALTER TABLE LI_Userlinks
ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)

SELECT @.d3 = GetDate()

INSERT INTO LI_Users (
uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z5

SELECT @.d4 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 0 AND 99
AND uid != 100 * uid + n0 + 10 * n1

SELECT @.d5 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 100 AND 9999

SELECT @.d6 = GetDate()

SELECT u.uid, r1.uid_to, r2.uid_to
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
WHERE 1 = u.uid

SELECT @.d7 = GetDate()

SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to)
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)

SELECT @.d8 = GetDate()

SELECT
DateDiff(ms, @.d1, @.d2)
, DateDiff(ms, @.d2, @.d3)
, DateDiff(ms, @.d3, @.d4)
, DateDiff(ms, @.d4, @.d5)
, DateDiff(ms, @.d5, @.d6)
, DateDiff(ms, @.d6, @.d7)
, DateDiff(ms, @.d7, @.d8)
, DateDiff(ms, @.d1, @.d8)-PatPWhat do you want us to discuss? Where is the description of the problem he/she wants to solve? Is it an organization hierarcy, a family tree?

Really don't know what to discuss or comment on.|||There is no need to discuss anything unless you have something to contribute. I haven't gotten an answer from the other site's management about whether they'll even tolerate cross-site references (some sites object to that as a form of advertising, DBForums is often abused by other sites using posts here to attract new users), so I'll hide the site name for the moment.

The original question was:I'm trying to come up with a solution on how <<removed by PatP>> stores relationships between users, specifically, how it recognizes, and retrieves the network tree.

What I'm after is how this happens in the backend, and database. How it says "David is a 2nd tier connection to Joe". I can't seem to come up with any solution that would be scalable. I had originally considered using Joe Celko's "nested set" model, but this was simpler, efficient enough for this use, and uses much more common schema and code constructs.

-PatP|||Well, that site was easy enough to find using Google|||My only comment would be that the logic is not recursive.|||the network on that site isn't really a tree, it's a graph where there can be more than one way to get from one node to another. There's no parent-child relationship, just a "we're connected" relationship.

the trick is to find the *shortest* path to get from one to the other.

a well known algorithm for this is Djikstra's. Peso has a slick impementation in sql here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77262

although my instinct is this algorithm would be better done in compiled code.|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77262

although my instinct is this algorithm would be better done in compiled code.

I thought the goal was to get rid of all the developers|||Well, that site was easy enough to find using GoogleYeah, I know that... Until I get the "mother may I" from their admins to link to another site (like here) I'm not going to just lay it out.

They are relatively new to the question and answer format, and don't really have any good way that I can find to present code. I tend to follow the "ravioli" model for posting and keep things completely contained on one site when I can, but at least to me this problem just itched for a DBForums-like presentation for the solution.

-PatP|||the trick is to find the *shortest* path to get from one to the other.This isn't much of a trick because they only support three levels, and because this is a directed graph it is functionally identical to a tree. Someone of interest to you is either your own direct connection, or a connection of your direct connections... There is no need to search the entire graph because that site limits the search to two levels.

-PatP|||I thought the goal was to get rid of all the developers

without developers, your beloved SQL Server goes away.

besides, I am a dev. ;)|||This isn't much of a trick because they only support three levels, and because this is a directed graph it is functionally identical to a tree. Someone of interest to you is either your own direct connection, or a connection of your direct connections... There is no need to search the entire graph because that site limits the search to two levels.

-PatP

a directed graph is not the same as a tree. trees don't have cycles (unless you are into incest?), but a graph can.

also here's a current thread on sqlteam on this topic: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89323|||An unconstrained graph can certainly have cycles, but a directed graph can never have cycles by its definition. Because a directed graph has "direction" and the start node of any link must be nearer the root than the end node of that link, cycles can't happen in a directed graph. That's part of why a directed graph can't have circular links (where a node connects to itself) because the end node must be further from the root than the start node for any given link.

-PatP|||One thought about the code sample that I posted that I do need to add is how to find the "degree of relation". You are in the U alias, your direct connections appear in the R1 alias, and their direct connections (your second level connections) appear in the R2 alias. When checking to see how "close" to you another node in the graph is, check R1 first then check R2 (because it is possible that someone could be both your direct connection as well as an indirect connection (through a common friend).

-PatP|||An unconstrained graph can certainly have cycles, but a directed graph can never have cycles by its definition. Because a directed graph has "direction" and the start node of any link must be nearer the root than the end node of that link, cycles can't happen in a directed graph. That's part of why a directed graph can't have circular links (where a node connects to itself) because the end node must be further from the root than the start node for any given link.

-PatP

a directed graph can certainly have cycles. a directed graph is just a normal graph where you replace the edges by "arcs" where arcs have a direction. edges have no direction.

see the section here on directed graphs. there's a diagram there of a directed graph with a cycle.
http://en.wikipedia.org/wiki/Graph_(mathematics)

if a directed graph has no cycles, then it's called a directed acyclic graph.

also I don't know what you mean by root. a graph has no root. trees have roots.|||Christmas trees don't have roots.

Shoe trees don't have roots.|||I was taught (granted this was 30 years ago) that a CSDG (Computer Sicent Directed Graph) was fundamentatlly different than a mathematical graph in several ways, in order to make it possible to solve some problems with the existing hardware in usable time frames.

A mathematical graph is fundamentally a set made up of nodes and associated vectors. As a set, there is no implicit order of the nodes. A CSDG is fundamentally a *possibly multiply) linked list, so that nodes always have an implicit order (this makes pruning possible/practical).

A mathematical graph is undirected, meaning that any two related nodes have two vectors that connect them. These vectors are commonly represented as a single, bi-directional vector even though that is definitely NOT the same thing from the standpoint of a formal proof. A CSDG always has unidirectional vectors that point away from the root, with the understanding that they can and should be inverted if both necerssary and possible (obviously this is irrelevant if the vector or either node can be pruned).

Mathematical graphs are always treated as sets, and as such can only have set operations applied to them. A CSDG on the other hand can be treated as a set (in which case you need to apply special handling to deal with the unique representation of the vectors), but in order to avoid needless processing they can also be handled as a linked list.

I will conceed that Wikipedia considers mathematical graphs and CSDG as interchangable, and in an ideal world they should be, but I have to solve real problems with real hardware... For the purpose of solving the kinds of problems that I use a CSDG to solve, a mathematical graph would either consume more RAM than any machine I've got, execute longer than I'll live, or both. I'll live with the limitations of what I was taught until I can afford hardware that will let me use the purer solution.

-PatP|||Christmas trees don't have roots.

Shoe trees don't have roots.

they did at one time, they were just pruned!

:)|||i have never heard of a CSDG. then again I've never taken any CS courses. :)

when i need to store a graph in code, i'll use either an adjacency list or an adjacency matrix. former is more space efficient but slower on lookups. both are covered in most books on data structures.

Every other page is blank when saving .PDF in SSRS

I'm using SSRS web service to generate a report and saving it to a .pdf file. The number of pages in the report is always doubled because every other page is blank. Anyone come accross this issue?

When I save the report as an excel file everything is fine.

Thank in advance,

I found it was the Body, Page Header or Footer field contents not accounting for margins. So if the Report size value is (8.5,11in) or (11,8.5in), check your textboxes, tables layout fields and Body parts to ensure the margin is accounted for in the size. I usually use a max of 10.5 for by landscape reports.|||

shview:

I found it was the Body, Page Header or Footer field contents not accounting for margins. So if the Report size value is (8.5,11in) or (11,8.5in), check your textboxes, tables layout fields and Body parts to ensure the margin is accounted for in the size. I usually use a max of 10.5 for by landscape reports.

You're correct. I figured that out after I made the post. Thanks for your feedback.

Event ID 17052 - c0000005 EXCEPTION_ACCESS_VIOLATION

I have a windows 2000 server that hosts sql 2000 (sp3)
and IIS for a web based payroll application that runs on
our LAN. There are 25 users that use the application.
In the last two weeks I have experienced the below error
in the application log on the server.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 58 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
This error only happens when a certain area of the
payroll application is used, however not all of the time;
when the error occurs it also prevents the payroll
application from working.
The really strange part is that one day we will not
receive the error and then the next day the error might
occur all day. There is nothing that has changed in the
last few weeks in either SQL, IIS or the payroll
application.
Any ideas why I am receiving this error?Have you don consistency check on the DB? DBCC CHECKDB and DBCC
CHECKCATALOG.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> I have a windows 2000 server that hosts sql 2000 (sp3)
> and IIS for a web based payroll application that runs on
> our LAN. There are 25 users that use the application.
> In the last two weeks I have experienced the below error
> in the application log on the server.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> This error only happens when a certain area of the
> payroll application is used, however not all of the time;
> when the error occurs it also prevents the payroll
> application from working.
> The really strange part is that one day we will not
> receive the error and then the next day the error might
> occur all day. There is nothing that has changed in the
> last few weeks in either SQL, IIS or the payroll
> application.
> Any ideas why I am receiving this error?|||Yes I have, no problems there.
>--Original Message--
>Have you don consistency check on the DB? DBCC CHECKDB
and DBCC
>CHECKCATALOG.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
>> I have a windows 2000 server that hosts sql 2000 (sp3)
>> and IIS for a web based payroll application that runs
on
>> our LAN. There are 25 users that use the application.
>> In the last two weeks I have experienced the below
error
>> in the application log on the server.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 58 generated fatal
>> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server
>> is terminating this process.
>> This error only happens when a certain area of the
>> payroll application is used, however not all of the
time;
>> when the error occurs it also prevents the payroll
>> application from working.
>> The really strange part is that one day we will not
>> receive the error and then the next day the error might
>> occur all day. There is nothing that has changed in
the
>> last few weeks in either SQL, IIS or the payroll
>> application.
>> Any ideas why I am receiving this error?
>
>.
>|||The I suggest you go with the general recommendations: Search KB if there
are specifics re your problem, make sure you are current on service pack and
open a case with MS if needed.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:d0ea01c3eef7$026a0060$a401280a@.phx.gbl...
> Yes I have, no problems there.
>
> >--Original Message--
> >Have you don consistency check on the DB? DBCC CHECKDB
> and DBCC
> >CHECKCATALOG.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> >> I have a windows 2000 server that hosts sql 2000 (sp3)
> >> and IIS for a web based payroll application that runs
> on
> >> our LAN. There are 25 users that use the application.
> >>
> >> In the last two weeks I have experienced the below
> error
> >> in the application log on the server.
> >>
> >> Error: 0, Severity: 19, State: 0
> >> SqlDumpExceptionHandler: Process 58 generated fatal
> >> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
> Server
> >> is terminating this process.
> >>
> >> This error only happens when a certain area of the
> >> payroll application is used, however not all of the
> time;
> >> when the error occurs it also prevents the payroll
> >> application from working.
> >>
> >> The really strange part is that one day we will not
> >> receive the error and then the next day the error might
> >> occur all day. There is nothing that has changed in
> the
> >> last few weeks in either SQL, IIS or the payroll
> >> application.
> >>
> >> Any ideas why I am receiving this error?
> >
> >
> >.
> >|||What type of memory configuration do U have for SQL server (fixed / dynamic) sounds like a memory contention issue btw iis and sql server, tried fixed memory?
Also possible to monitor memory object (pages/sec) counter to see if there is excessive paging when this problem occurs?|||Do you have the stack trace from the SQL Server event log?
This will provide more information for us to go on and help us debug.
Also, make sure you are on the latest version of SQL Server, as this SQL
Server bug might have already been fixed.
--
Paul Wehland
pwehland at msn dot com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> I have a windows 2000 server that hosts sql 2000 (sp3)
> and IIS for a web based payroll application that runs on
> our LAN. There are 25 users that use the application.
> In the last two weeks I have experienced the below error
> in the application log on the server.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> This error only happens when a certain area of the
> payroll application is used, however not all of the time;
> when the error occurs it also prevents the payroll
> application from working.
> The really strange part is that one day we will not
> receive the error and then the next day the error might
> occur all day. There is nothing that has changed in the
> last few weeks in either SQL, IIS or the payroll
> application.
> Any ideas why I am receiving this error?