Embedding reporting services as a standalone product

Hi,
In the application that I am working on, we currently use Crystal Reports as
the embedded reporting engine. I am trying to evaluate if we should move
over to Microsoft Reporting Services. However, one thing confuses me. It
seems Microsoft Reporting Services will work only as a server under IIS.
This will not work for our customers. I am wondering if Reporting Services
has any embedded component much like Crystal Reports that we could use.
Thank you in advance for your help.
SunnyVersion 2 will have a web form and a winform that does not require the
server (works with the server if there but does not require it).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sansanee" <sansanee@.nospam.com> wrote in message
news:%23LD40RJ3EHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the application that I am working on, we currently use Crystal Reports
as
> the embedded reporting engine. I am trying to evaluate if we should move
> over to Microsoft Reporting Services. However, one thing confuses me. It
> seems Microsoft Reporting Services will work only as a server under IIS.
> This will not work for our customers. I am wondering if Reporting Services
> has any embedded component much like Crystal Reports that we could use.
> Thank you in advance for your help.
> Sunny
>

embedding a DTS package and logging/error reporting

Hi All,

When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:

COMException - error returned from a call to a COM component.

Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?

Cheers,

Tamim.

Hi Tamim,

Try configuring your DTS package (using the old SQL 2000 designer) to log to an msdb database (it can be a SQL 2005 msdb). Then you will find the DTS messages logged to the tables eg sysdtspackagelog and sysdtssteplog.

Good luck.

Mike

embedding a DTS package and logging/error reporting

Hi All,

When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:

COMException - error returned from a call to a COM component.

Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?

Cheers,

Tamim.

Hi Tamim,

Try configuring your DTS package (using the old SQL 2000 designer) to log to an msdb database (it can be a SQL 2005 msdb). Then you will find the DTS messages logged to the tables eg sysdtspackagelog and sysdtssteplog.

Good luck.

Mike

sql

Embedded Word Document in RDL

I have a word document that I would to migrate to reporting services
2005. Instead of recreating the word document in reporting services, I
would like to know if I can embed the word document in a report? If
not, what other options are there?
Thanks.If you would like the word document to be hosted by RS then in your project
add a new existing item and then select your Word file. Then you can deploy
it. Note that when you update it and want to redeploy I have found that I
have to delete it from the report server first prior to deployment.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TCogan" <tcogan@.starcon.org> wrote in message
news:1174500858.861820.168170@.y66g2000hsf.googlegroups.com...
>I have a word document that I would to migrate to reporting services
> 2005. Instead of recreating the word document in reporting services, I
> would like to know if I can embed the word document in a report? If
> not, what other options are there?
> Thanks.
>|||On Mar 21, 1:45 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> If you would like the word document to be hosted by RS then in your project
> add a new existing item and then select your Word file. Then you can deploy
> it. Note that when you update it and want to redeploy I have found that I
> have to delete it from the report server first prior to deployment.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "TCogan" <tco...@.starcon.org> wrote in message
> news:1174500858.861820.168170@.y66g2000hsf.googlegroups.com...
>
> >I have a word document that I would to migrate to reporting services
> > 2005. Instead of recreating the word document in reporting services, I
> > would like to know if I can embed the word document in a report? If
> > not, what other options are there?
> > Thanks.- Hide quoted text -
> - Show quoted text -
I have tried that and that does work. But I was trying to find the
best way to migrate the word document into an RDL file. I want it to
be an RDL file because I have some other data that I want to
incorporate into the report from a database. I am not sure this can
be done, but I would like to avoid from having to recreate the word
document in an RDL file.|||There might be a third party tool that allows this but not straight out of
the box.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TCogan" <tcogan@.starcon.org> wrote in message
news:1174503465.010122.233220@.p15g2000hsd.googlegroups.com...
> On Mar 21, 1:45 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> If you would like the word document to be hosted by RS then in your
>> project
>> add a new existing item and then select your Word file. Then you can
>> deploy
>> it. Note that when you update it and want to redeploy I have found that I
>> have to delete it from the report server first prior to deployment.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "TCogan" <tco...@.starcon.org> wrote in message
>> news:1174500858.861820.168170@.y66g2000hsf.googlegroups.com...
>>
>> >I have a word document that I would to migrate to reporting services
>> > 2005. Instead of recreating the word document in reporting services, I
>> > would like to know if I can embed the word document in a report? If
>> > not, what other options are there?
>> > Thanks.- Hide quoted text -
>> - Show quoted text -
> I have tried that and that does work. But I was trying to find the
> best way to migrate the word document into an RDL file. I want it to
> be an RDL file because I have some other data that I want to
> incorporate into the report from a database. I am not sure this can
> be done, but I would like to avoid from having to recreate the word
> document in an RDL file.
>

Embedded Video?

Hey everyone,

Is there any way to embed a video in a report? It doesn't seem like it but I thought I may be missing something. Maybe directly editing the xml code? Thanks.

-Keith

You may want to look at Sharepoint & possibly PerformancePoint rather than just Reporting Services. You could display the report and display a video in a separate webpart.

I am curious what the video requirement is for.

I don't believe there is a way to embed video - only to link to a page containing the video, or display outside of the report.

There is a way to create a custom control and bring it into Reporting Services, however I think this just renders html & images.

cheers,

Andrew

Embedded VB.NET Code Limitations?

What are the limitations to using the Embedded VB.NET Code within a RDL?
Is it possible to control the IE Browser features via the Embedded Code?
JeffThere are no syntax limitations in VB code used in report expressions and
Code element. You can also call custom assemblies that must only be managed
but could be writting in VB, C# or even managed C++, etc...
If I correctly understand your question you want to write a code hosted
inside a report that will run on client and control browser via ActiveX
interface. This is not possible because all code inside report is always
executed on server and never passed back to the client for execution.
RS also does not support embedding scripts into HTML output. If it was
allowed it would be a security hole...
You can write your own application that will host IE Browser control and
will talk to report server to retrieve reports.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:C762FC98-66B6-4358-9E91-28F2B3129475@.microsoft.com...
> What are the limitations to using the Embedded VB.NET Code within a RDL?
> Is it possible to control the IE Browser features via the Embedded Code?
> Jeff

Embedded Text Qualifiers no longer supported in Yukon ?

Hello,
I try to import the data below from a text file using the SQL Server 2005
import wizard with a "Flat File Source" and receive the error "SQL Server
Import and Export Wizard / The preview sample contains embedded text
qualifiers ("). The flat file parser does not support embedding text
qualifiers in data. Parsing columns that contain data with text qualifiers
will fail at run time."
With SQL Server 2000 this was no problem. The files I want to import are
about 100MB in size. Any workaround?
select @.@.version returns: Microsoft SQL Server 2005 - 9.00.1314.06 (X64)
Sep 2 2005 21:10:23 Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Sample data:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8"", 9"",""10""""",11
inches
Kind regards,
Frank
Hi Frank...

Found your post here:
http://www.eggheadcafe.com/forumarchives/sqlserverdts/oct2005/post24522404.asp

Any progress in the concerning topic?

Embedded Text Qualifiers no longer supported in Yukon ?

Hello,
I try to import the data below from a text file using the SQL Server 2005
import wizard with a "Flat File Source" and receive the error "SQL Server
Import and Export Wizard / The preview sample contains embedded text
qualifiers ("). The flat file parser does not support embedding text
qualifiers in data. Parsing columns that contain data with text qualifiers
will fail at run time."
With SQL Server 2000 this was no problem. The files I want to import are
about 100MB in size. Any workaround?
select @.@.version returns: Microsoft SQL Server 2005 - 9.00.1314.06 (X64)
Sep 2 2005 21:10:23 Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Sample data:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8"", 9"",""10""""",11
inches
Kind regards,
Frank
Hi Frank...

Found your post here:
http://www.eggheadcafe.com/forumarchives/sqlserverdts/oct2005/post24522404.asp

Any progress in the concerning topic?
|||Check this:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7952c866-807b-4715-80ba-498e0a16ab18&DisplayLang=ensql

Embedded text qualifiers

We have text files that are comma delimited, use double quotes as text qualifiers and sometimes have embedded double quotes. The embedded double quotes are escaped with an additional double quote like: below.

"123","product q"

"124","product ""a"""

DTS 2000 had no problem with this- it correctly parsed the files. The 2005 SSIS file connection manager correctly parses this in preview mode. But when the task is executed the task fails with the message "The column delimiter for column X was not found".

What is the recommended approach for this - we have alot of files in this format.

thanks

Please search the forums.

This should get you started: (in a sense, it's not going to be easy) http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=80634&SiteID=1|||I've had to do this as the other post with a script component. I import the whole row as a single column, then run via a regular expression a split command and then use replace function as well. Thats the best solution I have come across to your issue.|||Another workaround that I used successfully was to simply change the source file format. I changed it to |~| delimeter and no text qualifier. In addition I configured the SSIS source file connection manager to recognize the |~| as the delimiter and did not set the text qualifer option. In the end, it proved less painful than the other methods.

Embedded text qualifiers

We have text files that are comma delimited, use double quotes as text qualifiers and sometimes have embedded double quotes. The embedded double quotes are escaped with an additional double quote like: below.

"123","product q"

"124","product ""a"""

DTS 2000 had no problem with this- it correctly parsed the files. The 2005 SSIS file connection manager correctly parses this in preview mode. But when the task is executed the task fails with the message "The column delimiter for column X was not found".

What is the recommended approach for this - we have alot of files in this format.

thanks

Please search the forums.

This should get you started: (in a sense, it's not going to be easy) http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=80634&SiteID=1|||I've had to do this as the other post with a script component. I import the whole row as a single column, then run via a regular expression a split command and then use replace function as well. Thats the best solution I have come across to your issue.|||Another workaround that I used successfully was to simply change the source file format. I changed it to |~| delimeter and no text qualifier. In addition I configured the SSIS source file connection manager to recognize the |~| as the delimiter and did not set the text qualifer option. In the end, it proved less painful than the other methods.

Embedded tab Character

I have embedded tabs in a text field that I want to import to a destination table.

I was thinking I need to replace the tabs with spaces.

REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,LarryReplace ( fieldname, CHAR(10), ' ')|||

Thanks,

I beleive it is a char 9 , but I really wanted to change crlf Char(10) & CHAR(13).

Can I do this in one statement with the replace

Replace ( fieldname, CHAR(10) & CHAR(13), ' ')

Thanks,

Larry

|||

Regular expression work well to match and remove / replace string patterns.

To remove carriage return / new line combos, use a script component transform which replaces each occurrence of the pattern with an empty string.

To do so, drop a script component on the data flow canvas, selecting the desired column from the available input columns, and setting its usage type to Read/Write.

A regular expression is then used to match and remove the pattern.

Imports System
Imports

System.Data
Imports

System.Math
Imports

Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports

Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports

System.Text.RegularExpressions

Public Class ScriptMain
Inherits

UserComponent

Private

regex As Regex = New

Regex("\r\n",

RegexOptions.Compiled)

Public Overrides Sub

Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not Row.GeneratedStr1_IsNull Then
Row.GeneratedStr1 =

regex.Replace(Row.GeneratedStr1, String.Empty)
' Replace

all occurrences of pattern with empty string,
'provided input column is not null
End If
End Sub
End Class

|||

This is pretty Sweet, endless opportunity, unlimited potential.

Thanks,

Larry

|||

This is a SSIS forum, so how about a SSIS solution using REPLACE, may be easier than the Script Component route-

REPLACE(ColumnName, "\t", "")

\t is the escape sequenece for tab in a literal. This expression could be used in a Derived Column transform. Select the Replace "Column" option to clean existing columns in-place.

|||

Many ways to skin the Cat.

Can you do a crlf replace and a lf replace in the same replace statement.

There may be many line feeds and one crlf in the same text field that I am trying to clean.

Thanks,

Larry

|||

Nest the replace statements -

REPLACE(REPLACE(ColumenName, "\r\n", ""), "\n", "")

Or

REPLACE(REPLACE(ColumeName, CHAR(13) + CHAR(10), '', CHAR(10), '')

You could of course do just replace Cr and then Lf in a similar format, I just like the explicit nature of doing CrLf and Lf as units.

Embedded SQL Server Express Deployment - Unhandled Exception: Database already exists

Good Afternoon.

I am trying to write a short application within which I would like to embed a SQL Server Express database, all of which would be deployed via ClickOnce through a web site. The installation process seems to be working without trouble, including installation of the .NET Framework 2.0 and SQL Server Express 2005.

When the application launches on the new machine, however, I get a big Microsoft .NET Framework "Unhandled exception" error dialog box that indicates the database already exists and that the database file could not be attached. I am working with a clean virtual machine that I know has never had the database installed on it.

I fear I am overlooking something quite straightforward, but since this is the first time I have ever attempted to build a data-bound application, I am not sure where I am going wrong.

Perhaps the source of the problem, or a related problem, might be revealed even when I run the application in debug mode from within Visual Studio. I can run the application once, but if I try to run it again, I get an error when I try to open the database connection that is similar in content (database already exists...could not attach). I can run the SSEUtil and detach the database and then run the application again in debug mode and it works no problem (the first time!).

Lastly, if I install the application on the machine on which it was developed and make sure the database is detached, it will run without any trouble (even repeatedly). But, on a new blank machine, there is no database listed that matches my database name, so I cannot try to detach anything!?

My apologies for such a novice question!

Sean

Hi Sean,

I would suggest removing the ‘Initial Catalog’ keyword from your connection string. Among the things that are stored about a database when it is attached is the Name/Path pairing; SQL Server gets a little upset if you try to attach a database using an existing name but with a different path. Enter the |DataDirectory| macro…

I’m guessing you’re using ClickOnce deployment for this application. There are things that will cause the install directory of a ClickOnce deployment to move, when this happens, VS magically fixes up things as far as the application goes, but it cannot fix up the fact that SQL Server has recorded your explicit database as being linked to the old file location. When you attempt to open the application now, the attach fails because there is already a database named JMU_2007 listed in the master database, but it is associated with a different path, so you get the error that the database already exists. I’m guessing this is the problem you’re hitting.

By removing the ‘Initial Catalog’ keyword, you’re forcing SQL Express to give the database an autoname based on its path. If the database moves as a result of VS behavior, the name will be changed because the path has changed, so it will appear to be a different database from the SQL perspective. Hopefully this helps.

Mike

|||

Thanks very much Mike. That did the trick...I can't believe I overlooked that after spending too much time trying to fix this problem.

Sean

Embedded Select vs. UDF

Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?

Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

is much faster than something like

Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@.vmyIndex))

Given that dbo.FN_myUDF has the same code as the embedded select in the first example.

TIA,

KBActually I'm getting quite an opposite result:

select * from authors where au_id = (select au_id from authors where au_lname = 'Dull')

vs.

create function dbo.fn_get_au_id (
@.lname varchar(50) ) returns char(11)
as begin
declare @.au_id char(11)
select @.au_id = au_id from authors where au_lname = @.lname
return @.au_id
end
go

+

select * from authors where au_id = dbo.fn_get_au_id('Dull')

The first results in: Table 'authors'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.

The second yields: Table 'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.|||Perhaps because SQL Server is able to take your poorly written code:

SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

...and transform it into the much more efficient:

SELECT myValue1, myValue2
FROM myTable
INNER JOIN someTable on myTable.aValue = someTable.Value
WHERE someTable.myIndex = 800

...prior to executing it. The optimizer will streamline your statement whenever it can, and thus make use of any indexes on the tables and only make one pass through the subtable. Strictly following your code logic would result in a pass through someTable for every record in myTable, which is also what occurs when you call the UDF. The compiler wants none of that nonsense and fixes your code before executing it.|||Well, I just used the same sample code as you (Mr. Lindman) provided and converted it to my previously posted comparative case...and...hmmmmm...It is actually WORSE than the other two:

select * from authors a
inner join authors b
on a.au_id = b.au_id
where b.au_lname = 'Dull'

results in: Table 'authors'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.

In fact, even JOIN hints don't make any difference. Ironic, isn't it? ;)|||My bad. I was thinking he had the function call in his select clause. In the WHERE clause the UDF is only executed once. I get faster results with the UDF as well, though with a larger dataset I get identical results and execution plans using either the subquery method or the join method.sql

Embedded select query?

What I'd like to do is use the result of one query as the input for
another. eg:

INSERT INTO foo VALUES (
(SELECT id FROM people WHERE name = "bar"),
10,
'foobar'
) WHERE
id = 1;

Problem is, MSSQL wants a scalar value. Is there a way around this?
Can someone tell me the correct syntax for what I want to do, or is this
something that will have to be done outside SQL?

Err. Hope I've been clear. Thanks for any help you folks can give.Don't use the VALUES clause if you are INSERTing values from a query. It's
also good practice always to specify the column names in an INSERT
statement.

INSERT INTO foo (col1, col2, col3)
SELECT id, 10, 'foobar'
FROM people
WHERE name = 'bar';

I'm not sure what the final WHERE clause in your code was meant to be so
I've left it out. You can't have a WHERE clause on an INSERT statement.

--
David Portas
SQL Server MVP
--|||Eek. It's been a long day I guess. There wasn't supposed to be a where
on that :) But you've answered my question none the less. Thanks.

FYI, I didn't bother with the column specifications for the sake of
simplicity.

Thanks all the same!

David Portas wrote:
> Don't use the VALUES clause if you are INSERTing values from a query. It's
> also good practice always to specify the column names in an INSERT
> statement.
> INSERT INTO foo (col1, col2, col3)
> SELECT id, 10, 'foobar'
> FROM people
> WHERE name = 'bar';
> I'm not sure what the final WHERE clause in your code was meant to be so
> I've left it out. You can't have a WHERE clause on an INSERT statement.

Embedded Reports

hi,
do i have access to subreports '
for example: how many rows were created?
a have a tablerow with a sub report.
i want to set the visibility to hidden, if no rows were created in the
subreport...
regards
PatrickThe parent report doesn't have access to properties of the subreport.
You may want to consider setting the NoRows property on the subreport
control in the master report.
Or make the subreport control in the master report very small and then have
the subreport hide its own contents if there are no rows of data.
As a last resort, you could put a query in the master report that returns a
count of the rows of the subreport (or if you're calling the subreport
inside of a list, you would include the count as a column in your master
query) and then base the visibility on that count.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Patrick Ruhnow" <pruhnow@.dornbracht.de> wrote in message
news:uOSD0kAaEHA.556@.tk2msftngp13.phx.gbl...
> hi,
> do i have access to subreports '
> for example: how many rows were created?
> a have a tablerow with a sub report.
> i want to set the visibility to hidden, if no rows were created in the
> subreport...
>
> regards
> Patrick
>

Embedded Queries?

I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

ChrisIf the new column is always to be the product of two other columns, why not
use a computed column:

alter table MyTable
add
MyCol as (Col1 * Col2)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140277653.986097.250450@.g43g2000cwa.googlegr oups.com...
I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

Chris|||That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||You'd just have to cast things (and hope that there were no bugs in the
original data when you added the computed column). Going forward, the two
input columns would have to be numeric:

cast (Col1 as int) * cast (Col2 as int)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dranai" <dranai@.gmail.com> wrote in message
news:1140283416.620277.318370@.f14g2000cwb.googlegr oups.com...
That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Convert works, too. Cast is ANSI, while convert is T-SQL only.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140289709.094990.39720@.o13g2000cwo.googlegro ups.com...
Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Hey Chris,

Does your friend NEED to store the results of the mathematical
operation in the table, or will the value of this column always be
dependant on the other two columns? If the latter, then simply do the
calculation in the query returning data. No need to waste storage
space (or worry about validation) if the information stored is enough
to calculate your values.

Of course, Tom's suggestion about computed columns doesn't have
physical storage issues, but you have to be aware of the nature of a
computed column when doing an INSERT or UPDATE statement against the
table.

Stu|||Hmm...

I believe the answer to the question is "both". My friends needs to
store the results in the table as a new column, so that later queries
can access the data.

Your last paragraph has given me pause. Could using a computed column
from the query cause problems later on?

The query I was able to come up with is:

// -- Query --
add discquantity as ((cast(chrisslu.quantity as int)) *
cast(chrisslu.nr_of_disc as int))
// -- End Query --

And is returning "function name is missing )". Any thoughts on this?
I didn't believe that the query was related to any function call,
unless each query is considered a function call in MS SQL.

I definately believe we're getting close to a solution here.

Chris|||>From what I can see, you need to include the ALTER TABLE; eg:

ALTER TABLE chrisslu
ADD discquantity AS ((CAST(chrisslu.quantity AS int)) *
CAST(chrisslu.nr_of_disc as int))

As far as whehter or not to use a computed column, it shouldn't be a
problem if:

1. The value of the column is always dependant on the relationship with
the other columns;
2. You always use explicit column names when inserting or updating
data, and;
3. You don't plan on porting this database to another RDBMS engine.

Stu|||OK, great, none of those should be problems. Thank you for the
clarification.

Chris

Embedded PDF and Excel Files

I want to upload PDF and Excel files to the Report Server database. I
would like to display them in a custom web application. When working
with the ReportViewer control, I can easily show reports. I can't
figure out the process on how to show a PDF to a user. In particular,
if the user selects a PDF ( I can check the MIMETYPE), then, how do I
get the file from Report Server so I can stream it to the user?On Oct 10, 3:21 pm, "goo...@.landy.net" <goo...@.landy.net> wrote:
> I want to upload PDF and Excel files to the Report Server database. I
> would like to display them in a custom web application. When working
> with the ReportViewer control, I can easily show reports. I can't
> figure out the process on how to show a PDF to a user. In particular,
> if the user selects a PDF ( I can check the MIMETYPE), then, how do I
> get the file from Report Server so I can stream it to the user?
If you create a report that has the same format and output as the PDF
and Excel files, you could call the report and automatically render it
to PDF or Excel with a download prompt via URL. Here are a couple
examples.
http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=PDF
-or-
http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=Excel
Here's the URL format:
http://<ServerName>/reportserver?/<Directory>/<ReportName>&rs:Command=Render&<ParameterName>=<ParameterValue>&rs:Format=<PDF/CSV/XML/Excel/MHTML>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 10, 8:36 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 10, 3:21 pm, "goo...@.landy.net" <goo...@.landy.net> wrote:
> > I want to upload PDF and Excel files to the Report Server database. I
> > would like to display them in a custom web application. When working
> > with the ReportViewer control, I can easily show reports. I can't
> > figure out the process on how to show a PDF to a user. In particular,
> > if the user selects a PDF ( I can check the MIMETYPE), then, how do I
> > get the file from Report Server so I can stream it to the user?
> If you create a report that has the same format and output as the PDF
> and Excel files, you could call the report and automatically render it
> to PDF or Excel with a download prompt via URL. Here are a couple
> examples.http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=
=3DRend...
> -or-http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=3DRe=
nd...
> Here's the URL format:
> http://<ServerName>/reportserver?/<Directory>/<ReportName>&rs:Command=3DR=
ende=ADr&<ParameterName>=3D<ParameterValue>&rs:Format=3D<PDF/CSV/XML/Excel/=MHTML>
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
These are generic PDF files, not based on a report so they cannot be
rendered. We are using Reporting Services to provide security not
only to reports, but also some ancillary PDF and Excel files.|||Where does an uploaded pdf get stored'
Our uploaded pdf changes every week and rather than reupload everytime I was
hoping we could just save changes to the location it was uploaded to.
Many Thanks
"google@.landy.net" wrote:
> On Oct 10, 8:36 pm, EMartinez <emartinez...@.gmail.com> wrote:
> > On Oct 10, 3:21 pm, "goo...@.landy.net" <goo...@.landy.net> wrote:
> >
> > > I want to upload PDF and Excel files to the Report Server database. I
> > > would like to display them in a custom web application. When working
> > > with the ReportViewer control, I can easily show reports. I can't
> > > figure out the process on how to show a PDF to a user. In particular,
> > > if the user selects a PDF ( I can check the MIMETYPE), then, how do I
> > > get the file from Report Server so I can stream it to the user?
> >
> > If you create a report that has the same format and output as the PDF
> > and Excel files, you could call the report and automatically render it
> > to PDF or Excel with a download prompt via URL. Here are a couple
> > examples.http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=Rend...
> > -or-http://ServerX/reportserver?/SomeDirectory/ReportName&rs:Command=Rend...
> >
> > Here's the URL format:
> > http://<ServerName>/reportserver?/<Directory>/<ReportName>&rs:Command=Rende-r&<ParameterName>=<ParameterValue>&rs:Format=<PDF/CSV/XML/Excel/MHTML>
> > Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> These are generic PDF files, not based on a report so they cannot be
> rendered. We are using Reporting Services to provide security not
> only to reports, but also some ancillary PDF and Excel files.
>

Embedded or DB images not showing in ReportViewer

I have a report which has an embedded image in the heading and an image control that loads images from the database (Image control Data section configured as MIMEType=image/jpg, Source=Database, Value=Fields!Img_Data.Value).

In both cases I can see the embedded heading image and the database images in design mode (VS2005) and through the report manager web pages. When I try to render the report through the ReportViewer web control in my web application, the images do not display (standard missing image red "X" appears).

I have a button on my web form which re-runs the report and exports it to PDF, and when pressed the report is displayed correctly in PDF with all images.

What do I need to do to the ReportViewer web control for it to show embedded and database images? I am using the ReportViewer for 2005.

Please advise of give me some place to look.

Mark

Anyone?|||

Have the same problem.

Nowhere can find some answer.

Checked registration of http handler for reportViewer.

All seems ok.

But no one embedded image is not displayed.

We invested much time (read money) in the solution, and got stucked with this problem, because sorting images that uses reporting services dont display also.

If you found the solution, please help!

|||

I copied image URI from rendered report,

then tried to access embedded image from browser.

Copied url: http://localhost:2403/SITE/Reserved.ReportViewerWebControl.axd?ReportSession=w0bnfsz5qhowblqcixc22eix&ControlID=12fb7de86eea4d9080dda118312ed121&Culture=1049&UICulture=1049&ReportStack=2&OpType=ReportImage&StreamID=612c1bae-af83-408b-b385-25b08a827bc4

Result was Error

The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Reporting.WebForms.ReportServerException: The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[ReportServerException: The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)] Microsoft.Reporting.WebForms.ServerReport.InternalRenderStream(String format, String streamId, String deviceInfo, String& mimeType, String& encoding) +216 Microsoft.Reporting.WebForms.ServerReportControlSource.GetStreamImage(String streamID, String deviceInfo, String& mimeType) +59 Microsoft.Reporting.WebForms.ReportImageOperation.GetStreamImage(String streamID, HttpResponse response) +216 Microsoft.Reporting.WebForms.ReportImageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +94 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +215 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Seems, that is ugly implementation bug

|||Solved the PROBLEM by installing SQL Server Service Pack 2.)))|||

No, images showed only once, and then all goes wrong once again.

It's bad job that microsoft had done with this report control((

SP2 dosnt solve this problem!

sql

Embedded or DB images not showing in ReportViewer

I have a report which has an embedded image in the heading and an image control that loads images from the database (Image control Data section configured as MIMEType=image/jpg, Source=Database, Value=Fields!Img_Data.Value).

In both cases I can see the embedded heading image and the database images in design mode (VS2005) and through the report manager web pages. When I try to render the report through the ReportViewer web control in my web application, the images do not display (standard missing image red "X" appears).

I have a button on my web form which re-runs the report and exports it to PDF, and when pressed the report is displayed correctly in PDF with all images.

What do I need to do to the ReportViewer web control for it to show embedded and database images? I am using the ReportViewer for 2005.

Please advise of give me some place to look.

Mark

Anyone?|||

Have the same problem.

Nowhere can find some answer.

Checked registration of http handler for reportViewer.

All seems ok.

But no one embedded image is not displayed.

We invested much time (read money) in the solution, and got stucked with this problem, because sorting images that uses reporting services dont display also.

If you found the solution, please help!

|||

I copied image URI from rendered report,

then tried to access embedded image from browser.

Copied url: http://localhost:2403/SITE/Reserved.ReportViewerWebControl.axd?ReportSession=w0bnfsz5qhowblqcixc22eix&ControlID=12fb7de86eea4d9080dda118312ed121&Culture=1049&UICulture=1049&ReportStack=2&OpType=ReportImage&StreamID=612c1bae-af83-408b-b385-25b08a827bc4

Result was Error

The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Reporting.WebForms.ReportServerException: The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[ReportServerException: The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database. (rsStreamNotFound)] Microsoft.Reporting.WebForms.ServerReport.InternalRenderStream(String format, String streamId, String deviceInfo, String& mimeType, String& encoding) +216 Microsoft.Reporting.WebForms.ServerReportControlSource.GetStreamImage(String streamID, String deviceInfo, String& mimeType) +59 Microsoft.Reporting.WebForms.ReportImageOperation.GetStreamImage(String streamID, HttpResponse response) +216 Microsoft.Reporting.WebForms.ReportImageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +94 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +215 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Seems, that is ugly implementation bug

|||Solved the PROBLEM by installing SQL Server Service Pack 2.)))|||

No, images showed only once, and then all goes wrong once again.

It's bad job that microsoft had done with this report control((

SP2 dosnt solve this problem!

embedded loops and invalid local variables

Ugh. OK, I'm in well over my head here. I'm getting a couple of errors so I'll just start at the beginning and hope it all makes sense.

I'm planning to have this stored proc run by a scheduled job once a day. It will do three things: (a) grab all the forum posts that were submitted on that day, with their respective thread titles, (b) arrange this information into a nicely formatted list along the lines of:

Thread
Post
Post
Thread
Post
Thread
Post
Post

etc, and (c) email this list to everyone registered who's opted in for this daily spamfest.

Now, what I've currently got is not a stored proc but two chunks of code that, between them, would probably do most of what I need. But I've no idea how to combine them:

This bit will loop through my users and send them all an email

DECLARE @.em1 varchar(200), @.sbj1 varchar(500), @.bdy1 varchar(5000)
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE digest='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1='Forum Daily Digest'
select @.bdy1=('This is where my list of posts goes.')
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

and this bit will create something not unlike a list of forum posts, neatly arranged by thread:

SELECT
forum_posts.post_date
, forum_posts.body
, forum_posts.id AS postID
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

But how do I combine them? Apparently I can't just set the email body variable (in the first chunk) to be the SELECT statement in the second one, because "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified" - which I don't understand at all. And I can't just run that second chunk and drop it all into a few variables that I can reuse each time I loop through the users, because "The text, ntext, and image data types are invalid for local variables", and the body of the post is, of course, stored as text.

Can anyone help?how about ...

DECLARE @.em1 varchar(200)

select @.em1 = email FROM forum_users WHERE digest='yes'
while @.em1 is not null begin--CREATE EMAIL
exec master.dbo.xp_sendmail @.recipients = @.em1
, @.query = 'SELECT forum_posts.post_date ' +
', forum_posts.body ' +
', forum_posts.id AS postID ' +
', forum_threads.id ' +
', forum_threads.subject ' +
'FROM forum_threads INNER JOIN forum_posts ' +
'ON forum_posts.thread_id = forum_threads.id ' +
'WHERE day(post_date)=day(getdate()) ' +
'AND month(post_date)=month(getdate()) ' +
'AND year(post_date)=year(getdate()) ' +
'ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'
, @.subject = 'Forum Daily Digest'
select @.em1 = email FROM forum_users WHERE digest='yes' and email > @.em1
end

WARNING
This is untested and not optimized!|||Hmm, that looks good :) I get a "syntax error on line 6, near '+'", I tried:

@.query = 'SELECT forum_posts.post_date, ' +
' forum_posts.body, ' +
' forum_posts.id AS postID, ' +
' forum_threads.id, ' +
' forum_threads.subject ' +
'FROM forum_threads INNER JOIN forum_posts ' +
'ON forum_posts.thread_id = forum_threads.id ' +
'WHERE day(post_date)=day(getdate()) ' +
'AND month(post_date)=month(getdate()) ' +
'AND year(post_date)=year(getdate()) ' +
'ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'

but that didn't work either. I can't see what's wrong with it.

But assuming this is just a minor syntax error, it's still going to email the results of a query to all my forum users in a fairly unfriendly layout - is there any way I can format this into some sort of pretty email body?|||Sorry that probably should have been:
, @.query = 'SELECT forum_posts.post_date
, forum_posts.body
, forum_posts.id AS postID
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'

a. "is there any way I can format this into some sort of pretty email body?" you didn't mention that in your original post, is this scope creep?
b. define "pretty email body"|||Ah, that'll do it :) Nice one.

I'm trying to avoid "scope creep", sorry if I wasn't clear; by "pretty email body", I mean... well, objective (b) in my original post. The people who will receive this email are not battle-hardened DBA's, they're... well, they're nurses.

I assume that this email is going to take the form of a blandly-formatted table. I'd prefer to send them something that lists the days posts under subheaders of the thread titles, maybe with a bit of bold text throw in. But don't we then come back to the problem that I can't put text columns into local variables?

------
NB - Ah. I've just tried running that and it seems to be getting into an infinite loop. It continually returns the errors:

ODBC error 208 (42S02) Invalid object name 'forum_threads'.
ODBC error 208 (42S02) Invalid object name 'forum_posts'.

I'm definitely connected ok - I can, for example, run "SELECT * FROM forum_threads" in the same Query Analyser window without trouble. Any ideas what would be causing that?

Thanks...|||change your table references to:
<db name>.<table owner>.forum_threads
<db name>.<table owner>.forum_posts

or

set the xp_sendmail parameter '@.dbuse =' <to the db where your tables are located>.

Books Online has all of this...

as for jazzing up the output, Transact SQL wasn't explicitly designed for that. You could create a perminante table with a single, say varchar(100), attribute and process the results of your main select by build formatted text. The end result would be to select * from your single attribute table returning formatted text.|||Oops - I've just spammed my hotmail account with about 400 identical messages. :cool:

That SQL seems to get itself into an infinite loop - I don't get it. There's only one user record in the database where digest='yes'. Just to confirm, I've got:

DECLARE @.em1 varchar(200)

select @.em1 = email FROM forum_users WHERE digest='yes'
while @.em1 is not null begin--CREATE EMAIL
exec master.dbo.xp_sendmail @.recipients = @.em1
, @.query = 'SELECT his.dbo.forum_posts.post_date
, his.dbo.forum_posts.body
, his.dbo.forum_posts.id AS postID
, his.dbo.forum_threads.id
, his.dbo.forum_threads.subject
FROM his.dbo.forum_threads INNER JOIN his.dbo.forum_posts
ON his.dbo.forum_posts.thread_id = his.dbo.forum_threads.id
WHERE day(his.dbo.forum_posts.post_date)=day(getdate())
AND month(his.dbo.forum_posts.post_date)=month(getdate ())
AND year(his.dbo.forum_posts.post_date)=year(getdate() )
ORDER BY his.dbo.forum_threads.id ASC, his.dbo.forum_posts.post_date ASC'
, @.subject = 'Forum Daily Digest'
select @.em1 = email FROM forum_users WHERE digest='yes' and email > @.em1
end

I didn't understand the "WHERE digest='yes' and email > @.em1" bit - I changed that to "email <> @.em1", but it's still looping infinitely.

And about the formatting - I think I understand what you mean by using another table to temporarily store the output, but not how to drop large text field values into it?|||Again my appologies, that should be

select @.em1 = min(email) FROM forum_users WHERE digest='yes'

and

select @.em1 = min(email) FROM forum_users WHERE digest='yes' and email > @.em1

I am not sure I understand your question about large text fileds!!|||Aha, that's the one. Many thanks.

By the large text fields, I mean that one of the values in that SELECT statement - forum_posts.body - is datatype text, and I need that in a variable or something I can wrap in some sort of formatting. And it won't let me.

I think you're right, that what I'm asking exceeds the capabilities that Transact SQL was designed for. I think I'll probably be better off doing this in server-side code and simply calling that page in a scheduled task once a day - I can have one recordset holding a mailing list, another looping through the day's posts, organising them by thread title, and simply use an ASP mail component to ping it all off. But thanks very much for your help, you've been extremely patient and helped me understand a great deal.|||No problem... I think there are many ways to solve this problem. I would have used Perl, SMTP and a distrabution list in our Exchange server. The key here is to choose something that is easily to write, maintained and use in YOUR environment.

One last thought, and I am NOT sure this is possable, but would a DTS package do the trick? I know you can use VBScript, and send e-mail. Anyone with DTS experiance that can answer this one?