foreign key

Hello,

I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me? Thanks in advance.

Regards,

Roel AlblasHi Roel,

I'm not quite sure what you mean. Do you need to know how to write the SQL statement that uses a foreign key? Or create tables with a foreign key?

Tell us more about what you want to do and we'll try to help.

Don|||I'm making a poll following an example. In that example I have to make tables wich has an relation to each other with a foreing key. I use SQL Server 2000 and Web Matrix.

Roel|||So you're building a table. And you'll use EM to do it. Okay.

Here's a simple example. Since I don't know the particulars of the data you'll use, I'll use a simple contact management example, where each person can have multiple phone numbers.

The Person table would look something like this:

PersonID int (identity, primary key)
Name varchar(30)
...

The Phone table would look something like this:

PhoneID int (identity, primary key)
PhoneNumber varchar(15)
PersonID int
... (type of number, etc.)

In this case, I've named the linking field, PersonID, the same in each table but that is not necessary. You may want to use a naming standard that identifies both primary and foreign keys in your tables. Note in particular that Phone.PersonID isnot an identity field, because it can have duplicate data when a person has several phone numbers.

Using this structure you can now do joins on the two tables to return all of the numbers for a person, or a list of everyone and their phone numbers.

If you want the database to enforce referential integrity (make sure that there are no phone numbers without a person, cascade deletes, etc.) you can also create a relationship between the tables. The easiest way to do this in EM is to create a database diagram with the two tables and create it visually.

Is this enough information? If not, ask away.

Don|||Hi

Just wondering if you know of a similar feature in web matrix to create relationships?
I am using MSDE?

Thanks

Ramila|||No, I sure don't. It's been a while since I did a project with Web Matrix.

There are some other admin tools available, such as these, but I don't know their capabilities for creating relationships:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also use T-SQL through the osql command-line utility to create or modify your tables.

How are you creating the structure of your database? Through Matrix? Another way?

Don

forcing report to refresh

I have a RS report that I am integrating in to an ASP web site. I have
a menu page that gathers some user parameters (report dates etc) and
then opens the report in a new browser window, passing the user
parameters as querystring variables. I have noticed that the report
does not refresh if you use the same parameters twice in a row. For
example, I opened the report, tweaked the underlying data then ran it
again with the exact same parameters. The report did not channge. It
did change when I hit the refresh button in the report itself though. I
would prefer if my users did not have to perform this additional step.
Is there a parameter I can pass, or some other way to force this to
refresh every time?I never found a parameter that RS recognizes to automatically refresh
the report. What I ended up doing was to make up my own. Basically, I
pass all the parameters the report takes, plus another querystring
variable I called "GenDate", which contains the current date and time.
Than way the report parameters are always different, so the report
always refreshes. The problem with this is that RS does not like to
receive any parameters that it is not expecting, so I have to add this
parameter to every report. I just add it as a report parameter, but
don't associate it with anything in the recordset.
If anyone knows of a parameter the RS will recognize, please let me
know. For that matter, is there a master list somewhere? I have a few
that RS tacks on automatically (rs:Command=Render, rs:Format=HTML4.0,
rc:Toolbar=True, rc:JavaScript=True, rc:LinkTarget=_top,
rc:Area=Report), but the documentation is rather sparse.|||I finally came across rs:ClearSession=True which appears to accomplish
what I'm after.sql

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forbidden access to ReportServer (error 403)

Hi,

I am gradually tearing my hair out and other bits of my anatomy tryong
to get web access to my reportserver.

Currently I can access the rportmanager via a web interface and
configure user access etc etc, at which point the fun begins.

When I try to browse to the URL
http://domain/reportserver?/reachUmobile/Users I get a 403 error,
so....

I check the permissions on the directory and the web directories and
add what I think are the user accounts needed to access these resources
but unfortunately I am at a position where I have added EVERY user
account with FULL control to both the directories AND the report
manager via the web browser.

I cant possibly add any more user/permissions and I have been trying to
find the problem for a day now and getting rather "angry"..

Anyway, apologies if there is not enough info here but if any of you
guys know of any good resources that are available (I have tried
books/web etc) then I would be grateful for any pointers to debugging,
logs etc (I have checked the usual logs and nothing!)

Short of uninstalling the report server I have tried everything, and I
dont really want to go that route as the previous developer didn;t
leave me the report project (.NET). bugger...

Anyway really appreciate some help on this..

Many thanks

Richard

place in the url create and update the settings for all my datasources
via the reportmanager.
2.I have no idea myself, but there are a number of messages about error
403 in microsoft.public.sqlserver.reportingsvcs:

http://groups-beta.google.com/group...arch+this+group

Simon|||Thats a great shout Simon,

I found this comment from your link and hey presto I now have access to
the pages (Still have a data connection problem but I think this
shoulld be easily fixed)

"Quote"
It sounds like your IIS setting may be incorrect. Have you done
anything to
them, outside of switching them to basic? If you click on the
properties
tab for the ReportServer virtual directory, then click the
configuration
button. You should see a mapping for extension * that maps to the
aspnet_isapi.dll. If that mapping is not there then RS will not work.

Many thanks

Richard

FOR XML in web page- OK what's next

(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

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

To do this you do not need to use for xml or even the XML-DT on the server.

FOR XML EXPLICIT and namespaces

I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
However, then I cannot create a client proxy for my web service.
If I specify a (matching) targetNamespace in the web service description and the xsd file then I can create a proxy. However, at this point the XML produced by the SQL FOR XML EXPLICIT no longer validates.
I imagine this will be fixed if I specifiy a target namespace for my FOR XML EXPLICIT query (that is the root element should have a targetNamespace attribute).
I cannot see how to do this. There is sem reference to templates, but I believe these are use from http and I can't see any logical way to embed these in my SPROCS or ExecuteXmlReader calls.
Iain
"Iain" wrote:

> I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
> I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
...
Well, I found somewhere someone putting the xmlns in as a separate column in the select query. So ...
'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
and equivalent nulls in all the union locations.
This resulted in a query that worked sort of. Now for some reason it was rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a <DeliveryAddress> element a couple of nests down.
I fixed this by changing the elements in the element to attributes.
I don't know why this worked.
Iain
|||Adding xmlns or xmlns:foo (and then using !foo:bar) columns as you do are
the way to add namespaces in FOR XML currently (note that the PATH mode in
Beta2 will not support this and I am working on getting a better solution
for at least the PATH mode made available in Beta3).
If you use xmlns without specifying a prefix, all descending elements will
be in that namespace (unless another xmlns overwrites it). If your schema
does not allow those elements to be in that namespace, you may have a
problem.
Attributes on the other hand are never part of the default namespace. They
belong to the element unless an explicit namespace prefix has been
associated.
So this difference may account for what you see.
HTH
Michael
"Iain" <Iain@.discussions.microsoft.com> wrote in message
news:4B201E22-E0AF-4B12-9D98-E8B2DE3BD143@.microsoft.com...
> "Iain" wrote:
> ...
> Well, I found somewhere someone putting the xmlns in as a separate column
> in the select query. So ...
> 'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
> and equivalent nulls in all the union locations.
> This resulted in a query that worked sort of. Now for some reason it was
> rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a
> <DeliveryAddress> element a couple of nests down.
> I fixed this by changing the elements in the element to attributes.
> I don't know why this worked.
> Iain
>