Config Help for Server2003/sql2000/iis6 Reporting Services

Newbie alert. Need some configuration help please. Have web server with 3
websites on box above with same ip using host headers (well-established sites
working fine). Installed reporting services, went to one website and created
virtual directories with IIS to same folder/name as items under Default Web
Site. Authentication is set to IUSR_svrname, Integrated Windows on website
and virtual directories.
Created a basic report & deployed to http://serverName/reportServer. I can
view report by url similar
to:http://serverName/Reportserver?%2fFolderName%2freportName&rs:Command=Render Data connection and report retrieval is fine.
But, I need folks to be able to enter the www.domainname.com type of url and
that gives me a HTTP Error 403.8 - Forbidden: DNS name of the client is
rejected. As soon as I remove the report services part of the url, site
pops up fine. And, if possible, I'd like to be able to use more than one of
my domains, just referencing the virtual directory in IIS for each site.
Also, I cannot get to the http://serverName/Reports/Pages/Folder.aspx to set
roles and permissions because it only sees me as IUSR and I can't "see"
anything under that role. Even though I'm a domain administrator, believe
it's seeing me as IUSR.
Tried a few things but may be making it worse. Thanks for any advice-would
really like to be able to use this tool, but can't at this point.Update - think I'm really close, but still need help. Uninstalled,
reinstalled RS. Deployed sample. Added <ReportServerExternalURL>
http://www.domainname.org/Reports </ReportServerExternalURL> to
rswebapplication.config Deployed.
Created new role for IUSR_MachineName and gave it browse capabilities on
home folder.
Added virtual directory is IIS under web site above for Reports, with
documents of home.aspx and enable IUSR in IIS for directory. (Deployment is
still okay.) If I view Reports via machinename, site settings are there.
Browse to folder, click on report, the folder pops up again. Report will not
generate. If I view via www, site settings are not there (good, good).
Report does the same thing - popping up another folder view but not
generating.
Added virtual directory is IIS under web site above for ReportServer, with
document sof default.htm, default.asp, index.htm, iisstart.htm, default.aspx.
Same thing happens again.
Think I'm very close. Anybody?|||Finally got a reply on another forum so thought I'd pass it along. Even
though the documentation explicitly says:
"To access a report server on the Internet, you must add a
ReportServerExternalURL configuration setting that specifies the fully
qualified domain name of the report server. The external client uses this
value to access a report server. In this scenario, the browser that is
hosting Report Manager must send a fully qualified domain name when making
requests on behalf of Report Manager to the report server. The following
example illustrates the syntax for ReportServerExternalURL:
<ReportServerExternalURL> reports.adventure-works.com
</ReportServerExternalURL>"
When I replaced the UI with the following, things started rendering properly.
<UI>
<ReportServerVirtualDirectory>/ReportServer</ReportServerVirtualDirectory>
</UI>
Hope it helps out someone else.

Concatinating SmallInt Data Type

Newbie question regarding a db I have inherited.

A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.

DocNohas numbers that represent persons. SequenceNo has numbers thatrepresent specific documents associated with each person (DocNo).

So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.

My SELECT statement looks like this:

SELECT ReadingNo, SequenceNo

This returns data like this: 5 3

I would like to concatenate the SELECT statement to return like this: 5-3

So I made Sql like this:

SELECT ReadingNo + '-" + SequenceNo

Whichreturns a alias ('No Column Named') result value of 8 which is anarithmetic result instead of a string concatination that I want.

So my questions are:

1. Should the original database designer have used string data types forthese columns since they will never be used for math purposes?

2. Do I need to cast them to string data type (like nchar(4) - sinceneither column will ever exceed 4 digits) to get the result I desire?

3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?

Select Cast(ReadingNo As VarChar(5)) + '-' + Cast(SequenceNo As VarChar(5)) As DocNo

Basically what you need to do is cast (or convert) the two columns as either char or varchar so the sql engine knows to concat instead of adding.

|||

Excellent. Thanks so much. For future reference (should I have to create such a db from scratch), should I use varchar data type in this situation? Or is there a good reason (such as file size) to use the smallint type for such data?

|||

Depends on the business needs of the colums in your database.

If you have a need for the columns as int, smallint ect then use that.

When designing databases always look at 1st what your going to do with the data you are storing to help you determine best model.

|||

Thanks again for your input.

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.