Create A New UserName

I am using the following ConnectionString in an ASP file so that the ASP
application can communicate with SQL Server 7.0:
CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
Persist Security
Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
In order to ensure that my ASP page interacts with the database ForestDB, I
have to create a new user named SoGuest whose password will be SoGuest. To
do this, I went to 'Users' under the ForestDB tree, right-clicked & then
clicked 'New Database User'. A dialog box opened up. Next when I clicked the
drop-down list for assigning a login name, I find that there's not a single
login name in the drop-down list as a result of which I am not being allowed
to proceed forward to create the new user SoGuest. How do I create this new
user? Please note that when I try to create a new user in some other DB like
Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
drop-down list of the login name. The authentication mode I am using is both
SQL Server & Windows NT (I am working on Windows 2000 Professional).
Thanks,
ArpanIt sounds as though you have not yet created a login. In Enterprise Manager
open up the Security container and right-click Logins. Create a new login
called whatever you want. Assign him to those databases you want him to
access. That will create both your login and database user.
A login allows someone to be verified when they connect to SQL. It is an
entry in the sysxlogins table in the Master db.
A User is someone who already has a login in the sysxlogins table, but
requires access to a specific database. They are placed in the sysusers
table in the particular database they are trying to access.
The Enterprise Manager dialog allows you to add a login and user at the same
time.
HTH
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003|||You need to create the login in the Security folder first (and you can create the user as well from
that dialog).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Arpan" <arpan_de@.hotmail.com> wrote in message news:%23JLbRLchDHA.2420@.TK2MSFTNGP10.phx.gbl...
> I am using the following ConnectionString in an ASP file so that the ASP
> application can communicate with SQL Server 7.0:
> CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
> Persist Security
> Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
> In order to ensure that my ASP page interacts with the database ForestDB, I
> have to create a new user named SoGuest whose password will be SoGuest. To
> do this, I went to 'Users' under the ForestDB tree, right-clicked & then
> clicked 'New Database User'. A dialog box opened up. Next when I clicked the
> drop-down list for assigning a login name, I find that there's not a single
> login name in the drop-down list as a result of which I am not being allowed
> to proceed forward to create the new user SoGuest. How do I create this new
> user? Please note that when I try to create a new user in some other DB like
> Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
> drop-down list of the login name. The authentication mode I am using is both
> SQL Server & Windows NT (I am working on Windows 2000 Professional).
> Thanks,
> Arpan
>|||Thanks, Bob, for your advice. You have hit the nail on the head in saying
that I might not have created a login for the database ForestDB. Thanks to
Tibor as well.
Regards,
Arpan
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:4XCdb.408$_d.312@.news-binary.blueyonder.co.uk...
> It sounds as though you have not yet created a login. In Enterprise
Manager
> open up the Security container and right-click Logins. Create a new login
> called whatever you want. Assign him to those databases you want him to
> access. That will create both your login and database user.
> A login allows someone to be verified when they connect to SQL. It is an
> entry in the sysxlogins table in the Master db.
> A User is someone who already has a login in the sysxlogins table, but
> requires access to a specific database. They are placed in the sysusers
> table in the particular database they are trying to access.
> The Enterprise Manager dialog allows you to add a login and user at the
same
> time.
> HTH
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003
>

Create a custom webpage to edit/update a table in a SQL database.

Hi everyone, this is is my first post, so please reply and help.

I'm working on a project right now that uses asp 2.0 and SQL server 2005 express edition. This is a general idea of the project. In our company some of us receive ECO notifications (engineering change orders) for our products and we need to implement these to the test scripts that are on the production floor. So the project is about entering the new ECO into a database which will send an automatic notification to our test team. When they receive the notification they will have to sign in to the website and introduce their login and password to sign off the ECO (Following some checkpoints already defined by me, for example, Area ready, Test script modification necessary, new firmware introduction, comments, etc...) but I also need to record WHO and WHEN sign that ECO. We have 3 different test areas in our factory: Electrical, Functional and Systems, so all THREE areas must be signed off in order to the ECO go to a IMPLEMENTED state (at this point i need to send a new email saying that the eco has been implemented in all three areas).

So far I've completed the following things:

-users validation (logins, areas)

-New custom entry form for the ECOs and automatic email notification (part of what I did is described below).

Dim ECODataSourceAsNew SqlDataSource()ECODataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ECO_ICSConnectionString1").ToString()

ECODataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

ECODataSource.InsertCommand ="EcoNew"

ECODataSource.InsertParameters.Add("EcoNumber", EcoNumberTextBox.Text)

ECODataSource.InsertParameters.Add("EcoDescription", EcoDescriptionTextBox.Text)

ECODataSource.InsertParameters.Add("EcoMandatory", EcoMandatoryDropDownList.Text)

-Depending on which test area is the the engineering from, I can filter the ECOs and just shows the ones that their test area is pending. (using GridView)

But I'm stuck right now when the engineers have to sign the ECO for their test areas. I was able to use the Gridview and DetailsView to EDIT most of the things that I need. But there are somethings that I don't like:

1. When using the EDIT option on Gridview or Detailsview, all fields can be edited including ECO number, description and mandatory, which I don't want them to change. If I set those columns to read only, when editing that row again. It gives me an error that says that the ECOnumber can't be NULL, but if I remove these 3 columns the Engineer will not know which ECO they have sign. They are only going to be able to see the EcoId, which doesn't say much.

2. Also I saw that I wasn't able to do is to enter the USER login and CURRENT system date and time automatically. I don't want them to manually enter the date and their login manually.

3. Finally, when the last area signs the ECO, I want to update that record and set a flag that tells me that the ECO has been completed.

So what I really want is to create some sort of form (textboxes, labels, checkboxes, etc.) that will UPDATE the selected ECO from the gridview for instance. So when I select the row from the GridView, It will show the data (Econumber, description and mandatory as READ ONLY) and use the rest of the things as INPUT for the engineer to complete. At the end an "update button" and when I click it, It will enter/update the data on that specific row, but including the time and user login as well.

Also to check if the other 2 areas have signed and if so, change the ECOReadiness flag to 1 and send the email.

Is there a code like the one I used above to do this ? Or if you think there a better way to do this, I'll be very glad to hear it.

I'm new using sql and asp, so If i'm asking some dumb questions please forgive me..

Here's my table definition for your reference:

EcoId - primary key.

EcoNumber

EcoDescription

EcoMandatory

EcoReadiness <- Flag for the entire ECO, when ALL 3 areas have signed, this will be 1.

ATE < - Flag for Electrical area.

ATEscripts < - Just a Yes/no input.

ATEengineer <- user login

ATEdatetimestamp <- Date.Now()

FAT < - Flag for functional.

FATscripts

FATengineer

FATdatetimestamp

SYSTEMS < - Flag for systems.

SYSTEMSscripts

SYSTEMSengineer

SYSTEMSdatetimestamp

THANKS IN ADVANCE,

Regards,

Jesus

dearjaguerrero, ur query#01 have been confusing me.. if possible send ur page.aspx and page.aspx.cs(code file) file...

Query#2:

USER LOGIN

if r u using ASP.NET built-in authentication feature than

In the Page_Load event handler, add the following line of code:

string  UserIdValue ;

UserIdValue = Membership.GetUser().ProviderUserKey.ToString()

CURRENT system date and time

at database set ATEdatetimestamp 's default value - getdate()

Query#3:

//after updating each area signing - write these code :
string strSQL = "";
string strCon = ConfigurationManager.ConnectionStrings["ConnectionStringNAME"].ConnectionString; //"Data Source=.;Initial Catalog=NorthWind;User ID=sa";
SqlConnection con = new SqlConnection(strCon);
con.Open();
strSQL = "Select ATE, FAT, SYSTEMS From tableName Where EcoNumber = 'E001'"; //insert all selecting conditions
SqlDataAdapter sda = new SqlDataAdapter(strSQL, con);
DataTable dt = new DataTable();
sda.Fill(dt);
//i guess ur ATE, FAT, SYSTEMS and EcoReadiness - fields are bit field
if (dt.Rows[0]["ATE"].ToString() == "True" && dt.Rows[0]["FAT"].ToString() == "True" && dt.Rows[0]["SYSTEMS"].ToString() == "True")
//if all three flags are true then final flag is set to true.. so EcoReadiness flag tells u that the ECO has been completed
{
strSQL = "Update tableName Set EcoReadiness = 1 Where EcoNumber = 'E001'"; //
SqlCommand cmdUpdate = new SqlCommand(strSQL, con);
cmdUpdate.ExecuteNonQuery();
cmdUpdate.Dispose();
}

con.Close();
con.Dispose();

|||

Hello Patuary! Thank you for your reply !...

I've checked your ideas.. but is your code c# ?.. because I've got a lot of errors.. I forgot to tell that I was using Visual Basic... I tried to change some of the parts but I keep getting errors when running the app... There are a couple of questions I have regarding your code...

For the user login.. I changed a little bit your code..

CR10 web viewer width 100%

Hello,
I'm trying to set my CrystalReportViewer to 100% of page width in ASP.NET. When I set the Width property on the control in the ASPX page itself, the viewer renders itself correctly when the page first loads, but on each postback the "%" is changed to "px" causing the viewer to be only 100 pixels wide.

I can apparently get around that by setting the width to Unit.Percentage(100) on every Page_Load event. Then the viewer appears as I expect with 100% width everytime I navigate pages, search, or export.

My problem is this workaround doesn't seem to work for the group tree. When I toggle the group tree, the two divs that get rendered are of width 14px and 86px (which add up to 100px).

Any ideas how I can get around this bug?

Thanks,
JamesI have a lousy solution but it seems to work (this is C# code but same should apply to VB):
public class MyCrystalReportViewer : CrystalDecisions.Web.CrystalReportViewer
{
protected override void Render(HtmlTextWriter output)
{
string renderedControl = string.Empty;

// Render the control to a string builder
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
base.Render(htw);

// Extract the string for processing
renderedControl = sb.ToString();

// Replace certain strings in the group tree div style tag
// Be sure to check for all indices along the way and just do nothing if things aren't found.
int groupTreeIndex = renderedControl.IndexOf("<div class=\"crgrptr\"");
if ( groupTreeIndex != -1 )
{
int groupTreeStyle = renderedControl.IndexOf("style=\"",groupTreeIndex);
if ( groupTreeStyle != -1 )
{
int groupTreeStyleEnd = renderedControl.IndexOf("\"",groupTreeStyle+7);
if ( groupTreeStyleEnd != -1 )
{
string groupTreeStyleTag = renderedControl.Substring(groupTreeStyle+7,groupTreeStyleEnd-groupTreeStyle-7);
renderedControl = renderedControl.Substring(0,groupTreeStyle+7)
+ "display:inline;" + groupTreeStyleTag.Replace("width:14px","width:14%").Replace("position:absolute","position:static")
+ renderedControl.Substring(groupTreeStyleEnd);
}
}
}

// Replace certain strings in the page div style tag
// Be sure to check for all indices along the way and just do nothing if things aren't found.
int pageIndex = renderedControl.IndexOf("<div class=\"crystalstyle\"");
if ( pageIndex != -1 )
{
int pageStyle = renderedControl.IndexOf("style=\"",pageIndex);
if ( pageStyle != -1 )
{
int pageStyleEnd = renderedControl.IndexOf("\"",pageStyle+7);
if ( pageStyleEnd != -1 )
{
string pageStyleTag = renderedControl.Substring(pageStyle+7,pageStyleEnd-pageStyle-7);
renderedControl = renderedControl.Substring(0,pageStyle+7)
+ "display:inline;" + pageStyleTag.Replace("width:86px","width:86%").Replace("position:absolute","position:static")
+ renderedControl.Substring(pageStyleEnd);
}
}
}

// Now output the reformatted HTML of the control
output.Write(renderedControl);
}
}

CPU 100%

Hi,

I have sql server on a web server.
The machine grinds almost to a halt at peak times, as the asp pages are
causing sql server to use most of the cpu.
Looking at the graph it is at 100% for about 20 seconds at a time, then
drops, then goes back up.

Is there anything I can do to make sql server use less of the cpu? (Without
causing the asp pages to hand.)

Thanks

JJJJ (nospam) writes:
> I have sql server on a web server.
> The machine grinds almost to a halt at peak times, as the asp pages are
> causing sql server to use most of the cpu.
> Looking at the graph it is at 100% for about 20 seconds at a time, then
> drops, then goes back up.
> Is there anything I can do to make sql server use less of the cpu?

Probably. But exactly what I cannot tell. I can however give some
suggestions of what you can do to track down the problem. The best
approach is to use the Profiler. With this tool you can track commands
send to SQL Server, and you can get information about them. Assuming
that the source of the problem is a resource-intensive query, you
can use the Profiler to narrow down which query/ies it may be. Once
you have located one or more queries, you can look into how to improve
them, for instance by adding indexes.

Depending on your budget, you may also consider putting SQL Server on a
separate box, so that the web server and SQL Server does not have to
compete for resources.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Many Thanks for the reply.
I am on the verge of investing in a new server anyway, as it seems easier to
throw more hardware at it, although I appreciate this is not necessarily the
best solution.
However, if I can get sql server on a separate box, hopefully my problems
will be solved!

JJ
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94ECF2D31D7D6Yazorman@.127.0.0.1...
> JJ (nospam) writes:
> > I have sql server on a web server.
> > The machine grinds almost to a halt at peak times, as the asp pages are
> > causing sql server to use most of the cpu.
> > Looking at the graph it is at 100% for about 20 seconds at a time, then
> > drops, then goes back up.
> > Is there anything I can do to make sql server use less of the cpu?
> Probably. But exactly what I cannot tell. I can however give some
> suggestions of what you can do to track down the problem. The best
> approach is to use the Profiler. With this tool you can track commands
> send to SQL Server, and you can get information about them. Assuming
> that the source of the problem is a resource-intensive query, you
> can use the Profiler to narrow down which query/ies it may be. Once
> you have located one or more queries, you can look into how to improve
> them, for instance by adding indexes.
> Depending on your budget, you may also consider putting SQL Server on a
> separate box, so that the web server and SQL Server does not have to
> compete for resources.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JJ (nospam) writes:
> I am on the verge of investing in a new server anyway, as it seems
> easier to throw more hardware at it, although I appreciate this is not
> necessarily the best solution.

Hey, if a new server costs 30000 crowns (a modest and cheap one), and
a consultant costs 1000 crowns an hour, and it takes him 40 hours to
track down the problem and fix it, throwing hardware on it may even
be a better solution.

Although judging from your description it sounds like you will still
have performance problems even with a new box, but hopefully less
severe - and maybe you can live with them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Counting records

Hello,

I want to count some records in a table I have. My table is updated when someone posts information to an .asp page. Then, I use SSRS to create reports on this information in the table. I want to filter the results that the report shows based on how many records are in the table.

For instance:

Name last4 ID

John 2112 54432

John 3222 21223

John 7777 88888

John 3333 22222

John 3212 88722

Carol 2122 12111

Carol 5555 12111

Carol 3342 83635

Carol 1211 98363

Steve 2122 21331

James 2113 21123

Teresa 3223 21154

I want to filter the results shown in the report to records with > 3 occurances. So, the results for the above table would show only Carol and John.

Is there any way I can do this either in the SQL statement or in the reporting services statement?

Any help is appreciated.

Thanks

I think this ought to meet your needs

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

)

Just replace Some_Table with your table name.|||

Great. Thanks

I had something similar, but I had a few parts backwards. : )

So, if I wanted to add some more parameters should those go after the HAVING?

Like: HAVING COUNT (*) >3 AND Date BETWEEN @.startdate AND @.enddate

Thanks again.

|||Typically you would put the predicates that doesn't have any aggregate functions or GROUPING function in the WHERE clause. Logically the HAVING clause is seen as being evaluated after the GROUP BY clause has been evaluated so that the aggregate function results can be compared. This is different from the WHERE clause which can be evaluated at different stages (join conditions, table / index scans / seeks etc). So put the Date predicate in the WHERE clause and the COUNT(*) has to be in the HAVING clause.|||

So, if I understand correctly, it should be something like this:

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

) AND Date BETWEEN @.begindate AND @.enddate

Would that be the correct placement to include the date range in the WHERE clause?

Thanks again.