Help with SQL Group By please (results returned into / shown in C#.Net)!

Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).

Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOf

As i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:

"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...

Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)

Thanks in advance!

anyone?|||

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)

or this:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(ReviewRating/ReviewRatingOutOf)

If ReviewRating is an integer, and ReviewRatingOutOf is an integer, then you should cast one to a float like:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(CAST(ReviewRating AS float)/ReviewRatingOutOf)

otherwise you won't get what you expect because (int) / (int) will always return the floor of the result. So 1/2=0 1/8=0, 1/10=0, and the only way to score a non-zero result would be a perfect 5/5, 10/10, etc. By casting one to a float, then it the divide will return a float result. So (float)1/(int)2=(float)0.5

|||

Thanks for trying Motley - but all those methods give an 'overflow' error. I'm using Access 2003 by the way - just realised that may be important and that I hadn't mentioned it!

Any more help really appreciated!

|||Is there a record that ReviewRatingOutOf is 0?|||

Ahhh - yeah there is. Is there an easy way around that, or is it best to simply remove/change it? It's a review that didn't have an accompanying score (obviously!).

Thanks again for you help so far Motley.

|||

Think I got it working myself - here's my final code:

"SELECT ItemID FROM Reviews GROUP BY ItemID HAVING SUM(ReviewRatingOutOf) > 0 ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf) DESC"

That seems to give me exactly what I was after, with the highest-rated items arriving first. Unless you see something that'll cause problems, it's the perfect solution for me (that also allows for items with ReviewRatingOutOf = 0).

Thanks again for your help!

|||Just make sure that if both ReviewRating and ReviewRatingOutOf are both defined as an integer type (int,bigint,smallint,tinyint,bit) that you cast one or both to a float before the division or you'll get unexpected results.

Help with sql connection in C#

I have always programmed in VB.net but was given this site that is written in C#. Basically for some reason they can not get the

IDataReader reader

To work on the IIS server they are loading it on to. They are now asking me to fix the website so it will work. I have no clue how to do this in C#.

In VB.net I would do this:

Dim ConnectionString As String = "server=;uid=;pwd=;database="
Dim CommandText As String
Dim myConnection As New SqlConnection(ConnectionString)
Dim Adapter As SQLDataAdapter = New SQLDataAdapter
Dim MyCommandBuilder As SQLCommandBuilder
Dim MatcherDS As DataSet = New DataSet
Dim Row As DataRow
Dim Count

SelectStatement = " select M_ID, M_FirstName, M_LastName, M_Email, M_Login, M_Password, M_Level from Member where M_Login = '" & txtuser.text & "' and M_Password COLLATE Latin1_General_CS_AS = '" & txtpassword.text & "'"
Adapter.SelectCommand = New SQLCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SQLCommandBuilder(Adapter)
Adapter.Fill(MatcherDS, "temp")

count = MatcherDS.Tables("temp").Rows.Count

if count > 0 then
Row = MatcherDS.Tables("temp").Rows(0)
Session("M_FLevel") = Row.item("M_Level")
Session("M_ID") = Row.item("M_ID")
if Session("M_FLevel") = 0 then
Session("M_FLevel") = 5
end if

Response.Redirect("home.aspx")

else

response.write("Error Login")

end if

Now my question is how do I program this in C# so I can open the sql server and access the tables the same way??

Any help is appreciated...

The same the way you do it in vb.net except you use c#. In .NET all thelanguages use the same objects, but may differ in syntax. Look for avb.net to c# convertor on the net.

Rahul|||

Below is the C# equivelent.

string ConnectionString ="server=;uid=;pwd=;database=";
string CommandText;
SqlConnection myConnection =new SqlConnection(ConnectionString);
SqlDataAdapter Adapter =new SqlDataAdapter;
SqlCommandBuilder MyCommandBuilder;
DataSet MatcherDS =new DataSet;
DataRow Row;
int Count;

SelectStatement =" select M_ID, M_FirstName, M_LastName, M_Email, M_Login, M_Password, M_Level from Member where M_Login = '" + txtuser.Text +"' and M_Password COLLATE Latin1_General_CS_AS = '" + txtpassword.Text +"'";
Adapter.SelectCommand =new SqlCommand(SelectStatement, myConnection);
MyCommandBuilder =new SqlCommandBuilder(Adapter);
Adapter.Fill(MatcherDS,"temp");

count = MatcherDS.Tables["temp"].Rows.Count;

if (count > 0)
{
Row = MatcherDS.Tables["temp"].Rows[0];
Session["M_FLevel"] = Row.Item["M_Level"];
Session["M_ID"] = Row.Item["M_ID"];

if ((int)Session["M_FLevel"] == 0)
{
Session["M_FLevel"] = 5;
}

Response.Redirect("home.aspx");
}
else
{
Response.Write("Error Login");
}

HTH

|||Thank you, that worked...now I have something to working to work off of on the rest of this code. Thank you for your help...sql

Help with sp_send_cdosysmail

Current system
Windows 2000 server SP4
SQL Enterprise SP3a
We were using xp_SMTP_Sendmail from SQLDEV.net, and a few procedures
used CDO to send e-mail.
All was fine for years until this weekend.
We installed the following patches:
MS06-031
Microsoft Security Bulletin MS06-031
Vulnerability in RPC Mutual Authentication Could Allow Spoofing
(917736)
MS06-070
Microsoft Security Bulletin MS06-070
Vulnerability in Workstation Service Could Allow Remote Code Execution
(924270)
MS07-017
Microsoft Security Bulletin MS07-017
Vulnerabilities in GDI Could Allow Remote Code Execution (925902)
MS07-022
Microsoft Security Bulletin MS07-022
Vulnerability in Windows Kernel Could Allow Elevation of Privilege
(931784)
MS07-031
Microsoft Security Bulletin MS07-031 - Critical
Vulnerability in the Windows Schannel Security Package Could Allow
Remote
Code Execution (935840)
As soon as I hit this part of the code to create the mail object
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print @.hr
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
print ' Description: ' + @.description
I get the following error
-2147023782
Description: A dynamic link library (DLL) initialization routine
failed.
All other references to @.iMsg fail as the object does not exist.
Source code for Procedure at:
http://support.microsoft.com/kb/312839
Thanks!
David HayI've been able to drill a little further. the SP_OACreate refrences
stdole70.dll
On to the next...

Help with sp_executesql and return parameter

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:

 Public Function totalrecordsbysql(list as arraylist) as integer
 dim RetVal as new integer
 dim querystring as string
 Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
 Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
 cmd.commandtype = commandtype.storedprocedure
 dim mydr as SqlDataReader
 cmd.Parameters.add("@.TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
 cmd.Parameters.add("@.age",sqldbtype.int).value = 18
 cn.Open()
 try
 mydr=cmd.executereader()
 catch e as sqlexception
 dim err as sqlerror
 dim strErrorString as string
 for each err in e.Errors
 strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
 trace.write("sqlexception",strErrorString)
 Next
 finally
 RetVal = cmd.parameters("@.TotalRecords").value
 end try
 Return RetVal
 cn.close()
 End Function

Now here is the stored procedure:

CREATE PROCEDURE SimpleDynProfileCount
@.age int,
@.TotalRecords int output
AS
Declare @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
select @.sql = 'select @.xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@.sql, 16, 1)
IF @.age > 0
Select @.sql = @.sql + ' AND age > @.xage '
Select @.paramlist = '@.xage int, @.xTotalRecords int output'
Execute sp_executesql @.sql,@.paramlist,@.age,@.xTotalRecords = @.TotalRecords output
select @.TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.

You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.

Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.

Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.

I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.

rod|||I have resolved the syntax.

I made a mistake in the vb.net when I used a datareader to retrieve the output value!

Instead of:

mydr=cmd.executereader()

the syntax should be:

cmd.executeNonQuery()

Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.

After doing all that, everything worked as expected.

-rod

Help with simple query involving 3 tables

Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:

create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go

create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go

create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go

I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @.MinDate - @.MaxDate)

Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--- ------ ----- ------ --
HR 5 7 12 .84
Finance 10 15 25 1.75

So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.

One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!SELECT U.name, sum(J.NumberPrintedPages), sum(J.NumberPrintedPages * P.PaperCost)
FROM Jobs J INNER JOIN User_Tbl U ON J.User_ID = U.ID
INNER JOIN Printer_Tbl P ON J.Printer_ID = P.ID
GROUP BY U.Name

This example shows, how to join your tables, and how to return two of your fields. Since a user may have used 0 to n printers in his jobs, there isn't a clear indication of your fields #PagesPrinter1 and #PagesPrinter2.|||He wants a crosstab query.

Nicomachus, look up CROSSTAB in Books Online and you will see an excelent example of how to accomplish this using CASE statements. Unfortunately, it requires considerable programming to make your crosstab queries dynamic as the number of columns (printers) changes.
Supposedly this feature will be built into the next version of SQL Server, but in any case when you make your output dynamic you are going to have a hard time building reports around it, because the output format will not be consistent.

You are really best served by outputting your data in a standard normalized format and then letting your reporting application (Crystal, Access, whatever...) handle formatting as a crosstab.

Help with RS Report Viewer in .Net 1.1 (Sortable Columns)

Currently, we are using .net 1.1 and SQL 2005. To integrate RS without having to open new browser windows or launch the report manager, we are using the ReporService.asmx service to render the reports. One thing that we are having a difficult time with is allowing columns to be sortable. In report manager, we have sortable columns sorting without a problem, but once the page is rendered within our product using the service, it is rendered without the sortable columns.

While going through the different flags within the service I found that there is a flag for Javascript (which is what I'm assuming I need) but it doesn't change the report when I enable it. I am wondering if there is a combonation of flags that need to be set to allow the sorting to work or if it just not possible to do sorting when rendering the HTML sent from the service. Hopefully this is enough infomation to lead to some answers with this. Thanks.

bump

|||

I don't think it's necessarily a javascript issue -- although it might be if you are using ajaxy stuff since there are apparently issues there.

What's your doctype in your page?

>L<

|||

The 2000 version of the SSRS Web service (ReportService.asmx) doesn't support interactive features. The Report Manager uses URL access for report rendering which supports interactive features. The 2005 web service supports interactive features but you need to pass in the identifier of the sortable item wchich you don't know at design time. To make the long story short, I don't know of a hack to sort via the web service. My recommendation will be to upgrade to ASP.NET 2.0 and use the ASP.NET report viewer which supports all interactive features.

Help with RS Report Viewer in .Net 1.1 (Sortable Columns)

Currently, we are using .net 1.1 and SQL 2005. To integrate RS without having to open new browser windows or launch the report manager, we are using the ReporService.asmx service to render the reports. One thing that we are having a difficult time with is allowing columns to be sortable. In report manager, we have sortable columns sorting without a problem, but once the page is rendered within our product using the service, it is rendered without the sortable columns.

While going through the different flags within the service I found that there is a flag for Javascript (which is what I'm assuming I need) but it doesn't change the report when I enable it. I am wondering if there is a combonation of flags that need to be set to allow the sorting to work or if it just not possible to do sorting when rendering the HTML sent from the service. Hopefully this is enough infomation to lead to some answers with this. Thanks.

bump

|||

I don't think it's necessarily a javascript issue -- although it might be if you are using ajaxy stuff since there are apparently issues there.

What's your doctype in your page?

>L<

|||

The 2000 version of the SSRS Web service (ReportService.asmx) doesn't support interactive features. The Report Manager uses URL access for report rendering which supports interactive features. The 2005 web service supports interactive features but you need to pass in the identifier of the sortable item wchich you don't know at design time. To make the long story short, I don't know of a hack to sort via the web service. My recommendation will be to upgrade to ASP.NET 2.0 and use the ASP.NET report viewer which supports all interactive features.

sql

help with percision? if you enter a number in the trillions such 9,999,999,999,999 .net or sql m

can you please explian this chart:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

e1 = numeric(20,8)

e2 = numeric(20,8)

e1/e2

is this correct

max(6,s1 + p2 + 1)

8 + 20 + 1= 29 since under 38 use 29 scale

scale would be max(6,29) = 29 correct?

p1 - s1 + s2 + max(6, s1 + p2 + 1)

20 - 8 + 8 + 29 = 49 does that mean it truncate the least sugificant digits by 29 - 11 = 18 so the effective result should be numeric(38,18) or ##,###,###,###,###,###,###.000000000000000000 this does not seem to be what you get can some explain also we have seen that if you enter a number in the trillions such 9,999,999,999,999 neither .net or sql management studio cannot display the value?

I think you have it, though it turns out that the actual returned type is numeric(38,17). You can see this using a variant:

declare @.c sql_variant

declare @.a numeric(20,8) --set the datatypes here
declare @.b decimal(20,8) --set the datatypes here

set @.a = 1 --set a value here
set @.b = 1 --set a value here

select @.c = @.a / @.b --do the math
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
1.000000000000000000 numeric(38,18)

Don't quite understand your issue with trillions (though you have to use 21,8 instead of 20, 8 for the datatype:

declare @.c sql_variant

declare @.a numeric(21,8) --set the datatypes here

set @.a = 9999999999999 --set a value here

select @.c = @.a
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
9999999999999.00000000 numeric(21,8)

help with percision? if you enter a number in the trillions such 9,999,999,999,999 .net or s

can you please explian this chart:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

e1 = numeric(20,8)

e2 = numeric(20,8)

e1/e2

is this correct

max(6,s1 + p2 + 1)

8 + 20 + 1= 29 since under 38 use 29 scale

scale would be max(6,29) = 29 correct?

p1 - s1 + s2 + max(6, s1 + p2 + 1)

20 - 8 + 8 + 29 = 49 does that mean it truncate the least sugificant digits by 29 - 11 = 18 so the effective result should be numeric(38,18) or ##,###,###,###,###,###,###.000000000000000000 this does not seem to be what you get can some explain also we have seen that if you enter a number in the trillions such 9,999,999,999,999 neither .net or sql management studio cannot display the value?

I think you have it, though it turns out that the actual returned type is numeric(38,17). You can see this using a variant:

declare @.c sql_variant

declare @.a numeric(20,8) --set the datatypes here
declare @.b decimal(20,8) --set the datatypes here

set @.a = 1 --set a value here
set @.b = 1 --set a value here

select @.c = @.a / @.b --do the math
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
1.000000000000000000 numeric(38,18)

Don't quite understand your issue with trillions (though you have to use 21,8 instead of 20, 8 for the datatype:

declare @.c sql_variant

declare @.a numeric(21,8) --set the datatypes here

set @.a = 9999999999999 --set a value here

select @.c = @.a
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
9999999999999.00000000 numeric(21,8)

help with obtaining table metadata SQL Server

I'm trying to determine what tables in a database are involved in a cascade
update constraint. I've searched the net but found nothing. I know I can use
sp_help to get this info. or sp_helpconstraint, but I don't know where these
procedures are get their information. I would like to create a script that
would go through the DB and return what tables have this sort of constraint
on them. Can anyone advise or point me in the right direction.
Thanks.
WillUsing the information_schema tables, I wrote the following.
select t.table_name, r.constraint_name
from information_schema.referential_constraints r
inner join information_schema.table_constraints t on r.constraint_name =t.constraint_name
and delete_rule = 'cascade'
Does this help?
"Will" <kixman@.hotmail.com> wrote in message
news:u58GXD5wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I'm trying to determine what tables in a database are involved in a
cascade
> update constraint. I've searched the net but found nothing. I know I can
use
> sp_help to get this info. or sp_helpconstraint, but I don't know where
these
> procedures are get their information. I would like to create a script that
> would go through the DB and return what tables have this sort of
constraint
> on them. Can anyone advise or point me in the right direction.
> Thanks.
> Will
>

help with obtaining table metadata SQL Server

I'm trying to determine what tables in a database are involved in a cascade
update constraint. I've searched the net but found nothing. I know I can use
sp_help to get this info. or sp_helpconstraint, but I don't know where these
procedures are get their information. I would like to create a script that
would go through the DB and return what tables have this sort of constraint
on them. Can anyone advise or point me in the right direction.
Thanks.
Will
Using the information_schema tables, I wrote the following.
select t.table_name, r.constraint_name
from information_schema.referential_constraints r
inner join information_schema.table_constraints t on r.constraint_name =
t.constraint_name
and delete_rule = 'cascade'
Does this help?
"Will" <kixman@.hotmail.com> wrote in message
news:u58GXD5wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I'm trying to determine what tables in a database are involved in a
cascade
> update constraint. I've searched the net but found nothing. I know I can
use
> sp_help to get this info. or sp_helpconstraint, but I don't know where
these
> procedures are get their information. I would like to create a script that
> would go through the DB and return what tables have this sort of
constraint
> on them. Can anyone advise or point me in the right direction.
> Thanks.
> Will
>

help with obtaining table metadata SQL Server

I'm trying to determine what tables in a database are involved in a cascade
update constraint. I've searched the net but found nothing. I know I can use
sp_help to get this info. or sp_helpconstraint, but I don't know where these
procedures are get their information. I would like to create a script that
would go through the DB and return what tables have this sort of constraint
on them. Can anyone advise or point me in the right direction.
Thanks.
WillUsing the information_schema tables, I wrote the following.
select t.table_name, r.constraint_name
from information_schema.referential_constraints r
inner join information_schema.table_constraints t on r.constraint_name =
t.constraint_name
and delete_rule = 'cascade'
Does this help?
"Will" <kixman@.hotmail.com> wrote in message
news:u58GXD5wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I'm trying to determine what tables in a database are involved in a
cascade
> update constraint. I've searched the net but found nothing. I know I can
use
> sp_help to get this info. or sp_helpconstraint, but I don't know where
these
> procedures are get their information. I would like to create a script that
> would go through the DB and return what tables have this sort of
constraint
> on them. Can anyone advise or point me in the right direction.
> Thanks.
> Will
>

Help with my hosting!

I've hosted a website in a freehosting account which supports .net 2.0 and SqlServer 2005Express and they only gave me an ftp access which i can access my website, i found that there's the "App_Data" folder which i placed my database in.
Now here's the message i get when i try to connect to the SQL Express DB
An attempt to attach an auto-named database for file I:\Data\Web\qsh.eu\cipherasp_015f1bdd-1338-4a35-9873-b29232b472c9\www\App_Data\cipherasp.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share
So, what should i do!

Hi

This threadhttp://forums.asp.net/thread/903667.aspx is talking about this problem.

And you can take a look at this article:An attempt to attach an auto-named database for file failed.

And also you can google it for more.

Hope it helps.

|||Hey man, Belive it or not, i tried all of these and nothing worksThe problem also that i have no access to the site on the server except the ftp account which all i can do is copy and paste the database in the app_data folder.|||

Does the account used to open db connection has full-control on that remote folder where the database files locates?

|||How do i allow "everyone" to edit, modify delete on my database file before i ftp to the host?!|||I don't mean to allow everyone for that Then do you have "User Instance=true" in your conection string? If so remove it and try again.

Help with java connection to MS SQL 2000 with windows integrated security

Hi , I am trying to connect to MS Sql server 2000 from Java (1.4.2 /
1.5 ). I installed my Sql Server(8.00.382) from the one supplied with
VS.NET 2001. When I installed it on my laptop it did not ask me for a
user name and password. After install when I re-started my machine I
see the server started up with a green light. Now when I connect to the
server from VS.NET it works fine. This is because VS uses windows
integrated security. I now need to connect using Java , so I downloaded
the microsoft drivers for SQL2000-JDBC sp3 from the microsoft site. I
added the jar files to my Java project classpath. I manage to register
the driver in java :

Class dbClass = ClassLoader.getSystemClassLoader().

loadClass("com.microsoft.jdbc.sqlserver.SQLServerDriver");

DriverManager.registerDriver((Driver) dbClass.newInstance() );

Connection conn =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;_
integrated security=SSPI");

but cannot seem to get a connection as it gives an SQLException saying
that it is unable to connect:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.

I cant seem to figure it out.Can some one help ??

I am a newbie to sqlserver so couldnt quite figure out how to change
admin password or create a new user with the tools provided with this
version of sql (SQL Server Desktop Engine).

Any help will be appreciated.

EbbyHi ... I also tried the following connection string which I made after
looking at other peoples problems.

jdbc:microsoft:sqlserver://EBBY/VSdotNET:1433;DatabaseName=Ebby");

where EBBY is the server name
VSdotNET the instance name
Ebby the database name|||Hi

You may want to check out
http://support.microsoft.com/defaul...kb;en-us;313100

The following would imply that a names instance would require a different
port (or possibly an alias) to connect
http://support.microsoft.com/defaul...kb;en-us;313225

John
<ebrahimbandookwala@.gmail.com> wrote in message
news:1111297471.633617.217830@.g14g2000cwa.googlegr oups.com...
> Hi ... I also tried the following connection string which I made after
> looking at other peoples problems.
> jdbc:microsoft:sqlserver://EBBY/VSdotNET:1433;DatabaseName=Ebby");
> where EBBY is the server name
> VSdotNET the instance name
> Ebby the database name|||(ebrahimbandookwala@.gmail.com) writes:
> Hi , I am trying to connect to MS Sql server 2000 from Java (1.4.2 /
> 1.5 ). I installed my Sql Server(8.00.382) from the one supplied with
> VS.NET 2001.

8.00.382 is SQL 2000 SP1. The current service pack of SQL 2000 is SP3 (and
SP4 is in beta). I strongly recommend you to upgrade to SP3, as SP3
has a fix for the Slammer worm which could attack your SQL Server if
you were to expose it on the Internet.

> Connection conn =
> DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;_
> integrated security=SSPI");

1433 is normally the port for the default instance, and apparently you have
a named instance. Check out the links posted by John, and see if they help.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Help with installation of Standard SQL Server 2000

I have installed IIS, VS.NET 2003, .NET 2.0 Framework, and now I have tried to install the standard version of SQL Server 2000 and it is telling me that I can only install the client components. The O/S is Windows XP Pro on a laptop.

Thanks for any ideas,Thats fine. Just click ok. Install should go straight through.

It detected that its not running on a server thats all.

Once installed browse to MSDE folder and install that.

Everthing should work fine from there.