MS Sql Server User ID, Password and connection string

Hi;
I am making a software (.Net) which is going to connect a ms sql server but I cant conect yet :(.

1- I dont know what I have to type for user id (name)
2- What is my password, it has been long time that I hvae installed it but I dont need any password with Microsoft SQL Server Management Studio Express which has windows authentication option. My software can work via internet or network. So I need to know my user name and password. I am using oledbconnection. Do I need any password?
3- I visited www.connectionstrings.com already so I have the format of it.

Thanks very much for any help...you need to either use your windows account and use the example from connection strings dot com that uses windows auth or you need to go to SQL Server management studio to create a sql server login and password and you will need to give that account access to the specific database you are accessing.

man do the redskins suck.

MS SQL server management studio express

hi there !

Well i hope u all great people are having great time. Well i am new to asp.net and struggling in database deployment to server. I took webhosting from datapacket.net, and using helm control panel. to deploy database, i used sql management studio express as suggested by some one. Now i am not able to access any of database or tables, although i got connected when i altered configuration surface setting to allow server to go to remote server. Now please help me, how to deploy database ?

Thanks

I did not understand you question.

You get connected to the server, but you cannot see the database ? So how do you know you are connected to the server ?

Or

you can connect to the server all of the databases in the server are listed in the object explorer of you Managment studio express and you cannot connect to the database ? If is this option it is probably your credencials.

Are there any errors message ? Can you post them if it is ?

|||

Hi,

Since you haven't give us the detail situation, so I just conclude some of the reasons which is generally happened for you to refer.

From your words, " i got connected when i altered configuration surface setting to allow server to go to remote server", it seems you have connected to the remote database server successfully by your management tool. So I guess, the problem is occurred while connecting to your database in your application. If so, there's nothing related with the deployment of the database. Please do check your connection string which your application use to see if it is working properly.

If the situation is just like after you have uploaded your database onto your server, you can see the database and tables but you can't implement some action such as select,update,delete and etc, then it seems the problem is caused by the wrong setting of database or tables' owners or roles. Just try to find if the role on your SqlServer has the permission to implement these database objects.

Thanks.

MS SQL Server Express connection error from VB.net Program

I am trying to make new connection with SQL Server 2005 Express Database fro
m
my Visual Basic.Net program. I am using connection string as "Dim
connectionString As String = "Data Source=.\SQLEXPRESS;Initial
Catalog=MgmtSystem;User Instance=True".
But it is giving me error as " Failed to generate a user instance of SQL
Server. Only integrated connection can generate a User Instance".
I can connect to this "MgmtSystem" database successfully using Microsoft SQL
Server Management Studio Express though.
What could be the possible reason for this error? How do I get the correct
connection string? and what does integrated connection mean? Your help will
be appreciated.
My application is in Access at the moment, but I am planning to migrate it
to Microsoft SQL Server Express 2005. I am completely new to Microsoft SQL
Server Express 2005 Express version.Fixed it by myself. I changed the connectiuon string to "Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial Catalog=MgmtSystem;Persist
Security Info=True;" ..and it worked..
"Sujata P" wrote:

> I am trying to make new connection with SQL Server 2005 Express Database f
rom
> my Visual Basic.Net program. I am using connection string as "Dim
> connectionString As String = "Data Source=.\SQLEXPRESS;Initial
> Catalog=MgmtSystem;User Instance=True".
> But it is giving me error as " Failed to generate a user instance of SQL
> Server. Only integrated connection can generate a User Instance".
> I can connect to this "MgmtSystem" database successfully using Microsoft S
QL
> Server Management Studio Express though.
> What could be the possible reason for this error? How do I get the correct
> connection string? and what does integrated connection mean? Your help wil
l
> be appreciated.
> My application is in Access at the moment, but I am planning to migrate i
t
> to Microsoft SQL Server Express 2005. I am completely new to Microsoft SQL
> Server Express 2005 Express version.|||Fixed it by myself. I changed the connectiuon string to "Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial Catalog=MgmtSystem;Persist
Security Info=True;" ..and it worked..
"Sujata P" wrote:

> I am trying to make new connection with SQL Server 2005 Express Database f
rom
> my Visual Basic.Net program. I am using connection string as "Dim
> connectionString As String = "Data Source=.\SQLEXPRESS;Initial
> Catalog=MgmtSystem;User Instance=True".
> But it is giving me error as " Failed to generate a user instance of SQL
> Server. Only integrated connection can generate a User Instance".
> I can connect to this "MgmtSystem" database successfully using Microsoft S
QL
> Server Management Studio Express though.
> What could be the possible reason for this error? How do I get the correct
> connection string? and what does integrated connection mean? Your help wil
l
> be appreciated.
> My application is in Access at the moment, but I am planning to migrate i
t
> to Microsoft SQL Server Express 2005. I am completely new to Microsoft SQL
> Server Express 2005 Express version.

MS SQL Server Date problem

hi...

i am using MS SQL 2000 as my server in my project.

but when i enter date from vb.net datagrid somtimes it shows me error of char can't be converted to date.Date Overflow problem.

it also has problem with the date modification for date which has month and date values btween

1 - 12 (i.e. like 04/11/2005 and 11/04/2005)

please help me regarding this as i have spend so much time on this but not able to get soluation

Go into the advanced options of your update statement parameters and change the datatype from "Empty" to "DateTime".

|||

how to Go into the advanced options of update statement parameters and change the datatype from "Empty" to "DateTime".

will it cause the problem when i will try to update the data which will be not of the 'datetime' type?

|||

Depends on the tool you are using I suppose. You can also just add the attribute Type="datetime" on the parameter.

And yes, it will cause a problem if you try to update the data if it's not covertable to a datetime type. For example the string "blah" will cause a problem, but the string "2/15/2006" will not cause a problem IF your current culture accepts that as a valid date format (Like en-US).

|||

A workaround for different cultural date settings that I use is to manually build the date string from the different components of the date.

For example, if I need to insert DateTime.Now into the database, I will build the following:

string dateNow = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + " " + DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second + ".000";

This will build 2006-02-22 11:55:44.000 which will work everytime, no matter what culture settings the user's browser is set to.

On another note, here is a helper method to determine if a string is in a date format (doesn't help with cultural issues though):

public static bool IsDate(object dt)
{
try
{
System.DateTime.Parse(dt.ToString());
return true;
}
catch
{
return false;
}
}

MS SQL Server 2000: Can Insert New Rows, but can't Update or Delete!!

I'm facing a strange problem here (SQL Server 2000).
I can Insert new rows from My ASP.NET 2.0 Pages but Can't Update or Delete.
usualy I test and build my web applications offline on my home workstation before I uppload them to my Server (Windows Server 2003 Standard), and I make sure that its error free.
The problem goes as follows:
1- I created a data grid that you can insert, delete and update and it worked fine when I tested it on my home computer.
2- When I updated the application to my server, I've noticed that I can Insert new columns to the database but When I try to update or delete the old columns, it doesn't allow me (so I have to use interprise manager to delete them from under- using same username and password!!!!).

I've never faced anything like this before, so please if anyone can give me any advice...

The problem only happened when I was using ASP.NET 2.0 Beta on my Home computer, and the full version on my server. so it was obvious that i need to install the same thing on both computers. but when I did I had to reconfigure my connection to accomodate the changes with the version. and now its working fine...

MS SQL Server 2000: Can Insert New Rows, but can't Update or Delete!!

I'm facing a strange problem here (SQL Server 2000).
I can Insert new rows from My ASP.NET 2.0 Pages but Can't Update or Delete.
usualy I test and build my web applications offline on my home workstation before I uppload them to my Server (Windows Server 2003 Standard), and I make sure that its error free.
The problem goes as follows:
1- I created a data grid that you can insert, delete and update and it worked fine when I tested it on my home computer.
2- When I updated the application to my server, I've noticed that I can Insert new columns to the database but When I try to update or delete the old columns, it doesn't allow me (so I have to use interprise manager to delete them from under- using same username and password!!!!).

I've never faced anything like this before, so please if anyone can give me any advice...

The problem only happened when I was using ASP.NET 2.0 Beta on my Home computer, and the full version on my server. so it was obvious that i need to install the same thing on both computers. but when I did I had to reconfigure my connection to accomodate the changes with the version. and now its working fine...

sql

MS SQL Server 2000, and ASP.NET 2.0 membership controls (login)

when my ASP.NET 2.0 web site working against MS SQL Server 2000,
I Usually have a connection string to the DB, I create a connection, and start working on the data.

While reading about the MemberShip provider of ASP.NET 2.0 (Login control, Forgot password, etc.),
I read that it's possible to save the MDF file of the membership controls, within the APP_DATA folder.

But how can it be ? the MDF file must be at the server folder,
How can I access this MDF file ? with which connection string ?
How does MS SQL Server 2000 syncronize against this MDF file within my APP_DATA folder ?

And by the way, if my hosting company allow me to use 1 db schema of the MS SQL Server 2000 server,
Does this method (saving the MDF file within thr APP_DATA folder) allow me to use multiple schemas ?

Thanks.

Hi,

But how can it be ? the MDF file must be at the server folder

For your first question, the built-in database is a new feature in .NET 2.0 which needs SQLExpress to support. The database is together with your application, you don't need to deploy the database additionally. The MDF file is stored in a folder called APP_DATA. When you application runs, the files in APP_DATA will be shipped as a database with your application automatically.

How can I access this MDF file ? with which connection string ?

If you want to access the database in SQLExpress, you also need connection string to achieve that. By default, the connection string for SQLExpress (built-in database) is located in Machine.Config, the name of the connection string is "LocalSqlServer", and you also can overwrite it and declare it at web.config file, even change the name of the connection string. But what you should note is, don't forget to modify the connection string attribute of the provider so that all the features supported by provider mechanism (i.e. Membership,Profile and etc) can work.

How does MS SQL Server 2000 syncronize against this MDF file within my APP_DATA folder

When you have decided to use SQLExpress to support your provider mechanism application, you don't need to use SQLServer2000. But if you want those provider supported application to work with SQLServer2000. You should use aspnet_regsql tool to transport the ASPNETDB to SQLServer2000 database, and modify the connection string attribute of corresponding provider.

For aspnet_regsql.exe tool, see:http://msdn2.microsoft.com/en-us/library/ms229862(VS.80).aspx

For more information about ASP.NET provider, see:
http://msdn2.microsoft.com/en-us/library/aa478948.aspx

Thanks.

|||

Thanks,

At my development machine :
But I removed SQL Server express from my machine, that means I can't just add the login control, than the MDF file will be created at the APP_DATA,
And everything will be ok ? I must install MS SQL Server express ?

At the hosting server :
There is ms sql server 2000, just copying the MDF data to the AAP_DATA, I guess will not work, right ?
I will need to mograte this MDF file, from my APP_DATA folder, to the real DB, right?

|||

Hi,

But I removed SQL Server express from my machine, that means I can't just add the login control, than the MDF file will be created at the APP_DATA,
And everything will be ok ? I must install MS SQL Server express ?

When you remove your SQLExpress edtion, you still can use such features like membership. (Login controls,CreateUserWizard controls and etc.) But you should export the ASPNETDB in SQLExpress to SQLServer Database before you removing your SQLExpress, and change the membership provider.

There is ms sql server 2000, just copying the MDF data to the AAP_DATA, I guess will not work, right ?

It will not work. To use the database in APP_DATA, you must have SQLExpress installed on your server.

I will need to mograte this MDF file, from my APP_DATA folder, to the real DB, right

Right, you need to export the ASPNETDB.mdf to your SQLServer 2000 database.

Thanks.

|||

Thanks

MS SQL SERVER 2000 INSTALLATION

Hi,
I'm learning ASP.net and I just bought Visual Studio .net professional. I tried to install MS SQL server 2000 on my two computers. One OS is windows server 2003 standard edition and another is Win XP professional.

Both of them appeared SQL Server Upgrade Wizard "from server 6.5 upgrade to server 2000". I don't think I have server 6.5 installed and there is no server 6.5 application interface to run the server. What's the problem? I cannot run the Wizard successfully. Can I just ignore it?
Thank you,
William

Yes ignore it because there is no upgrade from SQL Server 6.5 to 2000, maybe you answered questions Windows asked you about SQL Server please don't answer them. Windows doesnot know any application installed in it, you know SQL Server so ignore any question that is not relevant to you and answer only when you are forced. Hope this helps.|||Thank you for your reply.
It comes another question.
I'm learning Accessing and Binding Data. It's a sample from chapter 9 of the book "MICROSOFT ASP.NET PROGRAMMING WITH MICROSOFT VISUAL BASIC .NET"
I got the error message as followings. I think it is the problem from set up the ASPNET account. I used SQL Enterprise Manager to set up. It looks fine. I feel not. But I don't know how to check it.
And then I tried to open a command prompt and typed in
oSql -S(local)\VSdotNET DE
And Icannot get a 1> prompt.
I got the message:
usage: osql [-U login id] [-P password]
[-S server] ..


Can you let me know where the problem is?

Thank you very much!

Server Error in '/ASPNETSBS/Chapter_09' Application.

---

Login failed for user 'WILLIAM\ASPNET'.

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:System.Data.SqlClient.SqlException: Login failed for user 'WILLIAM\ASPNET'.
Source Error:

Line 31: Line 32: Dim MySqlConn As New SqlConnection(ConnStr)Line 33: MySqlConn.Open()Line 34: TryLine 35: Dim SQL As String

Source File:C:\Microsoft Press\ASPNETSBS\Chapter_09\ExecuteXmlReader.aspx.vb Line:33
Stack Trace:

[SqlException: Login failed for user 'WILLIAM\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Chapter_09.WebForm1.Page_Load(Object sender, EventArgs e) in C:\Microsoft Press\ASPNETSBS\Chapter_09\ExecuteXmlReader.aspx.vb:33 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +750

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032|||First there are two permissions in SQL Server you may have created the database permissions now go to the security section in Enterprise Manager and create the server permissions. Second you connect through the Datalink property at the top of Visual studio not through osql. Try the link below for data bind.
Hope this helps.
http://www.gotdotnet.com/team/vsrapideval/|||Thank you Caddre,
I got some information fromhttp://weblogs.asp.net/coltk/archive/2003/07/10/9920.aspx
So I fixed the problem by following the codes. I'm so happy to make a progress. All of you make the forum great.
William|||I am glad it helped and thanks for the link I know I will need it in the future. Thanks again.

MS SQL Server 2000 Enterprise Edition! Need Help!

hi everyone, i am doing a project on asp.net and i'm want to use the sql server 2000 EE and is using WinXp Pro.
I have problems installing the s/w. It says "Microsoft Server 2000 Enteprise Edition Server Components is not supported on this OS. Only Client components will be available for installation."
Is it true that i can't install SQL Server 2000 EE on XP Pro?
And is it true that i can only use the Standard Edition?

Need Help!
ThanksYes it is true, but you cannot use Standard neither as it needs Server OS as well.

See this page for exact details:
http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp|||ok..
thanks very much..|||On WindowsXP Pro, you can either use:

a) MSDE
b) SQL Server Developer Edition (available through MSDN Subscription etc)
c) SQL Server Personal Edition

Cheers
Ken

Edit: oops, didn't scroll down far enough on Teemu's link - personal edition is listed on that page! Sorry for the redundant post.|||u mean the Developer Edition can be downloaded from MSDN?|||If you have an MSDN subscription...

http://msdn.microsoft.com/subscriptions/

Cheers
Ken|||thanks

MS SQL Port

How can I determine which port MS SQL is running.
I known that this can be set at SQL Server Network Utility, but I
wonder why my ASP.NET script can connect to SQL Server even I change
its running port.

Another thing I want to know... actually it doesn't concern with SQL
that is,
how can I watch all the enviroment variable in Windows XP, such as
%systemroot% and how can I set that one"=aKe=" <darkgod1987@.hotmail.com> wrote in message
news:36e545c4.0404140338.6b6e5440@.posting.google.c om...
> How can I determine which port MS SQL is running.
> I known that this can be set at SQL Server Network Utility, but I
> wonder why my ASP.NET script can connect to SQL Server even I change
> its running port.
> Another thing I want to know... actually it doesn't concern with SQL
> that is,
> how can I watch all the enviroment variable in Windows XP, such as
> %systemroot% and how can I set that one

You can look in the SQL Server Log for the port, in the registry, or use the
SQLDMO Registry2 object's TcpPort property.

SQL Server listens on two ports - 1433 and 1434 (by default). A client can
connect to port 1434 to get connection information about all the instances
on the server, inclluding their ports. See "Controlling Net-Libraries and
Communications Addresses" in Books Online.

As for environment variables, you can use SET in the Windows shell, or use
an interface such as WMI which returns all the current variables. WSH has a
built-in object for this, I believe, perhaps the Network object but I'm not
entirely sure.

Simon

MS SQL performance from 10" to 3 minutes

Hello all !

I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

with exactly the same datas in database

what can be the reasons ?

thank youcheck whether the session is getting expired or not if not kill it.|||other reason could be lock put on the table during the transaction which may keep the DB server busy.
Also check if some other query seeks a large resultset from DB.|||ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

wash : is there a way to unlock ? ot what can I do ?

on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

thank you|||the reasons for this can vary widely.

things to check...

1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

3. Open up the performance monitor and make sure you disk que length is under 3.

4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

That should keep you busy.|||RECOMPILE ? i didn't know it was even possible
how do yo do it ?|||recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

see sp_recompile in Books Online.|||Sean,

I believe you assume That this is a sproc

I got Money that it's not|||oh probably not. dude can probably use a little BOL reading anyways.

MS SQL MSDE Quick Question About Databases

Hey,
Im using MSSQL MSDE with the ASP.NET WebMatrix Project to build databases, but i cant find where the database is stored on my hard drive. I am about to format and reinstall Windows and would like to backup these databases so that i can continue to work with them after i reinstall. Can anyone help me?

Thanks in advance, all help is appreciated :)

E.The quick and dirty answer is to look at C:\Inetpubs\wwwroot. That is where mine were stored. A safer, more complete approach is to simply use a Windows Explorer seach for "My Computer".|||I believe that the default installation location for msde data files is:

\\[yourserver]\c$\Program Files\Microsoft SQL Server\MSSQL$[yourserver]\Data

-Sam

ms sql image + vb.net

Hi,

could anyone help me how to get the images from ms sql database with asp.net (visual basic)?

I have a field in the database with datatype image, and there are a couple of images in this table.

Now I am wondering how to get this images and view them on my .aspx page?

Thank you.I've have solved it.

<%@. Page Language="vb" debug="true" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script runat="server">

Dim u As String

Sub Page_load
u = Request.QueryString("u")
getImage(u)
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<script runat=server
Public Sub getImage(ByVal u)

' Create Instance of Connection and Command Object

Dim myConnection As New SqlConnection("Server=localhost;uid=xx;pwd=xx;database=xx")

Dim myCommand As New SqlCommand("Select avatarImage from yaf_user where Name = @.u", myConnection)
myCommand.Parameters.AddWithValue("@.u", u)

Try

myConnection.Open()

Dim myDataReader As SqlDataReader

myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Do While (myDataReader.Read())

'Response.ContentType = myDataReader.Item("avatar")
Response.ContentType = "image/gif"
Response.BinaryWrite(myDataReader.Item("avatarImage"))

Loop

myConnection.Close()

Response.Write("Person info successfully retrieved!")

Catch SQLexc As SqlException

Response.Write("Read Failed : " & SQLexc.ToString())

End Try

End Sub

</script>
</body>
</html

MS SQL and ASP.NET connection problem

Hi,

Im trying to set up a ASP.NET webform to a MS SQL database. Im currently databinding to a datagrid. My code seems to compile correctly, no errors, however the data does not display.

I have tried to connect to the database server using the data adapter preview and works perfectly. But once i get the webpage running nothing displays from my VB code.

Does anyone have any clue to a resolution? THANK YOU ALL!I don't know if this is the problem, but I think after setting the datagrid's datasource property to the dataset, you have to type this:
"datagrid1.bind" or maybe "datagrid1.databind" (one of them: I can't quite remember)

Hope this helps.
Wes

MS SQL 2005: Performance – normal CPU vs CPU DUO/4 CORE

Hello,Is performance of web application (ASP.NET + SQL Server 2005 Wrg edition + Win Server 2003 Web edition) running on server with one core duo/4 CPU generally comparable to the performance of the same application running on the same server with 2/4 physical CPU's?Thank you for your ideas!Jan

Hi Jan,

Of cource on a multiple core CPU, the ASP.NET and SQL Server performance will be better than a single core CPU.

Because the IIS and SQL Server will optimize the performance according to the core units. In this case, better performance is gain.

MS SQL 2005, .NET, logins/sec and page faults

Hi, all.

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.

Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.

We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.

We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

One thought I had was that the Page Faults reported for sqlsrv.exe is
related to memory mapped IO and therefore can be ignored. Right or
wrong?

Any thoughs/pointers/ideas, even wild guesses, are most welcome.

Bjrn

PS: The server memory is fixed at 1.5GB out of 2GB physical ram,
clients run on the same machine and use TCP/IP comm.(I know...) The
host itself is not paging.bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.
>
Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.
>
We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.
>
We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

I would use Profiler to see what these clients are up to. If they are
generating tons of Audit:Login events, there is something fishy. Either
they don't use pooling, or misbehave so that pooling is cannot be used.
I think a common error is to fail to close the connections.

If on the other hand they generate a lot of sp_reset_connection, then
at least connection pooling is in order. (sp_reset_connection is
executed when a connection is reused from the pool.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.

Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.

We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.

We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

>
I would use Profiler to see what these clients are up to. If they are
generating tons of Audit:Login events, there is something fishy. Either
they don't use pooling, or misbehave so that pooling is cannot be used.
I think a common error is to fail to close the connections.
>
If on the other hand they generate a lot of sp_reset_connection, then
at least connection pooling is in order. (sp_reset_connection is
executed when a connection is reused from the pool.)
>

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

I guess we'll continue to rewrite the clients to use just one
connection wherever possible. The changes made so far has improved the
situation a lot. :-)

Thanks.
Bjrn|||bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

Al least connection pooling is working!

Quote:

Originally Posted by

I guess we'll continue to rewrite the clients to use just one
connection wherever possible. The changes made so far has improved the
situation a lot. :-)

Yes, with that connection rate, he disconnected model is not very good.
Connection pooling is particular useful in things like web applications,
when you have many users that connect through the same middleware (the
web server), and that run queries very infrequently.

The model is usually fine for Windows applications too. But if you have some
service-type of thing which does not have to wait for a user that goes for a
coffee, it may be better to stay connected.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

>
Al least connection pooling is working!

More or less.
We also have a tiny ASP app that writes one row to the db for each HTTP
request it receives. The ASP app used to use connection pools with
default settings. After changing Min and Max pool size to 300,
everything is suddenly more normal, now the server is so fast my
profiler traces has to be changed to make them report rows at all :-)

Stuff that used to take between 1200 and 7000ms now finishes in 1ms, so
I guess the problem has been solved.

Have a great weekend.
Bjrn

[snip]

MS RS Reportviewer, sparklines and bullet graphs in the report.

Hello All,

For a new reporting project compact we are using MS Reporting Services and hosting the report in an ASP.NET reportviewer.

"Tufte-like" and very clean, simple data visualizations as sparklines and bullet graphs have become a requirement.

We've had some success redering these in ASP but it's become necessary to render them in the reports themselves and we have not found a good way to do it.
Even third party charting products do not seem to support RS very much, one that does requires it on top of analysis services and their sparklines are actually defined in the KPI-s, but we are not using AS.

Have any of you had reports inspired by Tufte or Few and implemented it,
how did you manage it?

as always, your help is greatly appreciated!

Edward Tufte reports!!! Great way to go!

I did some a while back - basically, take a regular column chart and strip away everything from it:

Chart Area - no border/line, no fill
Plot Area - no border/line, no fill
Axis - no labels, no scales, no tickmarks
No Legend
The Palette doesn't have enough options, but the default is best

If you are doing comparisons against, say regions or products, create a separate column chart for each. Keep them the same scale, and lined up (stacked).

You really can do a lot of nice Sparklines using SSRS.

Good luck.