having trouble connecting to database

Hi I am new at this.

I have a little program written in C# in asp.net. The program basically accesses a database and stores new records. The database is supposedly already attached to MSDE so I am able to see the tables of the database inside asp.net. I can click on the individual slots of the table and modify the datas manually. However, I want to connect to the database from my C# program and be able to input data into the database via the website that the C# program produces. After I type in the data into the website and click the submit button on the website, I get an error page that says this:

Login failed for user 'sa'

the line of code thats causing this error is:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

Why is it not able to connect to the database?

YourChild:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

I am taking the liberty to presume that your database name is Friends.I just couldn't figure what the "\\NetSdk" means. Perhaps you can addsomething to my knowledge.

Most probably if you aren't using Integrated Security for accessing thedatabase, you'd need to specify the password in the connection stringas well. You can make it something like this:

con =new SqlConnection("data source=(local);initial catalog=Friends;user id=sa;pwd="yourpassword")

Remember,it is a bad habit to store your connection string in the code. Tryputting it in the web.config, but you might want to do this later, whenyou get the hang of things.

Smiles.|||

uXuf:

YourChild:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

I am taking the liberty to presume that your database name is Friends. I just couldn't figure what the "\\NetSdk" means. Perhaps you can add something to my knowledge.

Most probably if you aren't using Integrated Security for accessing the database, you'd need to specify the password in the connection string as well. You can make it something like this:

con =new SqlConnection("data source=(local);initial catalog=Friends;user id=sa;pwd="yourpassword")

Remember, it is a bad habit to store your connection string in the code. Try putting it in the web.config, but you might want to do this later, when you get the hang of things.

Smiles.

Thank you it works! I'm so glad you came by! now I am in the process of trying to stick the 'userName' and 'password' into the web.config file by using:

<

identityimpersonate="true"userName="sa"password="mypassword"/>

only now when I hit compile, Windows is giving me an error that says:

"Unable to start debugging on the web server. Server side-error occurred on sending debug HTTP request." Do I need to change something else to make this work?

There is another piece of code at the top of the web.config file that looks like this:

<authenticationmode="None"/>

Should "None" be set to "Windows"?

Then there is another username and password inside the "Directory Security" in IIS via 'property' for the virtual directory corresponding to the directory of the project. Is this username and password associated with the userName and password used to access the database?

Having some trouble connecting to a database....

I am doing one of the Microsoft virtual labs "Creating ASP.NET Web Applications with C# - Part 2" using Visual Web Developer Express. I am trying to fill a gridview with database information, but it gives me the error

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)".

This is on the local machine that I am executing the code.

public void BindGrid(string sortfield) {//Create DataAdapter to fetch data from Prodcuts table SqlDataAdapter myCommand =new SqlDataAdapter("select * from Products", myConnection);//Create dataset and fill it with Product data DataSet ds =new DataSet(); myCommand.Fill(ds,"Products");//Bind Product data to Datagrid DataView Source = ds.Tables["Products"].DefaultView; Source.Sort = sortfield; dgProducts.DataSource = Source; dgProducts.DataBind(); }

It stops on the line "myCommand.Fill(ds, "Products"); and points out the error mentioned earlier. I am not sure what to do...any help would be greatly appreciated, thanks in advance, and I apologize as well if I have not pointed out enough information.

What does your connection string look like?|||

check this link for help:

http://support.microsoft.com/kb/914277/en-us

Thanks

|||

First of all, thank you both greatly for responding.

Smalltalk, here is the connection string:

myConnection = new SqlConnection("server=localhost;database=northwind;Trusted_Connection=yes");

Jpazgier, my connections are set at local and remote connections, plus the server is on the same machine, I have no idea why it mentions that error for me.

Thanks again, guys.

|||

Hi GWShane,

Please make sure that the server is the default instance on your machine. If you're using SQL Express, the default name for this instance might be SQLExpress. In this case, you might need to add the instance name to the DataSource like:

myConnection = new SqlConnection("server=localhost\\SQLExpress;database=northwind;Trusted_Connection=yes");

Having problems using Breakpoint in Script Task.

I am still pretty new to SSIS, SQL, and DOT NET. I came from the UNIX world. I have a SSIS package. On the Control Flow one of the Items I have is a Script Task. I was able to successfully set breakpoints in the Script Task and they worked fine.I could step through the script and check values in variables.Life was good.

Now something happened.I set the breakpoint, from the menu I select “start with debugging” and I get the following window:

-- -

Visual Studio Just-In-Time Debugger

An unhandled exception (‘System.Runtime.InteropServices.COMException’) occurred in DTAttach.exe [3380].

Possible Debuggers:

New instance of Microsoft CLR Debugger 2003

New instance of Visual Studio .NET 2003

New instance of Visual Studio 2005

[_] set the currently selected debugger as the default

[_] Manually choose the debugging engines

Do you want to debug using the selected debugger?

--

I have tried selecting Yes, but that doesn’t work.If I delete all breakpoints the package runs fine.I greatly appreciate your help.

You are not alone.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1846879&SiteID=1|||Thanks Phil. I found a work around for my problems and posted it on the thread you provided the link to above.

HAVING PROBLEMS INSTALLING THE SQL 2005 EXPRESS

hello, I am having some problems installing the sql 2005 express that comes with the asp.net 2.0

It says that the installation failed thats all. Anyone with that kind of problem?

Installation failure is not so easy to troubleshoot... If you had installed beta version of VS2005, you'd better uninstall the existing components completely. Here is a uninstall tool for VS2005:

http://msdn.microsoft.com/vstudio/support/uninstall/default.aspx

If you want to troubleshoot the installation, you can take a look at these posts:

http://forums.asp.net/thread/1290342.aspx

http://forums.asp.net/thread/1306350.aspx

Having a problem with ReportViewer Drill-Throughs

I'm having a problem with the Report Viewer in ASP.NET. I have a report that drills-through to other reports using the Action->Jump to report setting. This works perfectly in the VS Report Builder Preview screen, as well as in the Report Manager when I upload it. However, on my aspx page, clicking any item in the viewer makes the page just refresh. I can't find any articles on this anywhere. Any Ideas?You guys are ridiculously helpful, by the way.Anything on this? I really don't understand what's going on.|||Finally figured this out.You need to add a handler for the Drillthrough event, and make sure you're not reloading your main report on Postback.sql

Having a hard time connecting to sample SQL database from ASP.Net Page

I am working through the book ASP.Net Unleashed Second Addition. I am trying to open a simple database connection. (using the sample "pubs" db from SQL Server)
I have copied the author's code exactly, but it just won't work. I keep getting this error:
What am I doing wrong???
Description:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message:BC30182: Type expected.
Source Error:

Line 5: Line 6: Sub Page_LoadLine 7: Dim conPubs As SqlConnectionLine 8: conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )Line 9: conPubs.Open()

What namespaces have you imported?|||Coding for my namespace is
<%@. Import Namespace="System.Data.SqlClient" %>
This is copied directly from the authors example.... so I don't know if it is right lol
Then under the page_load event
I typed:
<script Runat="server">
Sub Page_Load
Dim conPubs as SqlConnection
conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" )
conPubs. Open()
End Sub
</script>
The text "Connection Opened!" Is supposed to show up if the coding is correct...
Help hehe just a beginner :)|||Does anyone know some awesome websites that teach you Visual Studio.Net 2003 and database stuff? I have the hardest time finding things in the MSDN library....|||Try:
http://learnvisualstudio.net/|||

I don't know if VB.NEt support the continuation stuff.
If it does, may be u have to put a continuation sign at the end of line 7.
Cheers.

sql

Have a problem with UpdateCommand

Hello everyone,

I am a bit new to ASP .Net so forgive me, if I dont understand something right off.

I am writing a page that gets code from the SQL database and puts it into a GridView. To get the information I am using SqlDataSource. I cant post an error message because I am running this off remote server but though testing I figure that it crashes when I add

Analysis = @.Analysis

line into the UpdateCommand. First I thought that maybe my parameters were not read correcty but when I tried something like

Analysis = 6

it worked. Then I tried to replace CQNo=@.CQNo with

CQNo = @.Analysis

and it also worked.

I am very much puzzed at this. In SQL database CQNo is varchar, WorkDate is DateTime and Analysis is Money type.

Can someone please help, I am out of ideas. Thanks!

<asp:SqlDataSourceID="myEfforts"runat="server"SelectCommand="SELECT SNo, CQNo, WorkDate, Analysis, Design, Coding, Testing, DesRev, CodeRev, PeerRev, SysTest, PostInstall, Others, TotEff FROM Effort WHERE EmpId = @.EmpId ORDER BY WorkDate DESC"DeleteCommand="DELETE FROM Effort WHERE SNo=@.SNo"UpdateCommand="UPDATE Effort SET CQNo = @.CQNo, WorkDate=@.WorkDate Analysis=@.Analysis WHERE SNo=@.SNo"><SelectParameters><asp:SessionParameterDefaultValue=""Name="EmpId"SessionField="PR_EmpIDVal"Type="String"/></SelectParameters>Looks like you are missing a comma after @.workdate and before Analysis? Or was that a type during pasting the code here?|||That was a typo when I was pasting the code in here. Sorry|||

I tried this and it works.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SNo" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SNo" HeaderText="SNo" ReadOnly="True" SortExpression="SNo" />
<asp:BoundField DataField="CQNo" HeaderText="CQNo" SortExpression="CQNo" />
<asp:BoundField DataField="WorkDate" HeaderText="WorkDate" SortExpression="WorkDate" />
<asp:BoundField DataField="Analysis" HeaderText="Analysis" SortExpression="Analysis" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"
DeleteCommand="DELETE FROM [effort] WHERE [SNo] = @.SNo"
SelectCommand="SELECT [SNo], [CQNo], [WorkDate], [Analysis] FROM [effort]"
UpdateCommand="UPDATE [effort] SET [CQNo] = @.CQNo, [WorkDate] = @.WorkDate, [Analysis] = @.Analysis WHERE [SNo] = @.SNo">
<DeleteParameters>
<asp:Parameter Name="SNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CQNo" Type="String" />
<asp:Parameter Name="WorkDate" Type="DateTime" />
<asp:Parameter Name="Analysis" Type="Decimal" />
<asp:Parameter Name="SNo" Type="Int32" />
</UpdateParameters>

</asp:SqlDataSource>

|||Thanks a lot of taking the time, let me try this.|||

It worked! Thanks a lot!!!! Can you please explain what was causing the error (what I was doing wrong)?

Thanks a lot!

|||

I didn't see your whole page so my guess is the problem lies in the updateparameter part. A quick trick for this, you can always drag and drop a gridview to hook up with your table of interest in your database and ask for generating all commands (insert, update, delete). You will get a working copy to modify. Remeber assign a primary key for the table.

Glad you got it to work now.

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Hardware question

I'm building a database server that's going to be running MS SQL 2000 server
and .net application. Should I go with SATA RAID or is U320 SCSI RAID
recommended? The latter is much more expensive, but is the performance
worth it?

Also, should I go with dual Xeon or should I go with AMD's Opteron? I read
a review from anandtech that basically tore the Xeon up. What are your
thoughts?

http://www.anandtech.com/IT/showdoc.aspx?i=1935&p=9"Shabam" <blislecp@.hotmail.com> wrote in message
news:bq6dnZkI_NzWiGbdRVn-ug@.adelphia.com...
> I'm building a database server that's going to be running MS SQL 2000
server
> and .net application. Should I go with SATA RAID or is U320 SCSI RAID
> recommended? The latter is much more expensive, but is the performance
> worth it?

It really depends on what you're doing.

> Also, should I go with dual Xeon or should I go with AMD's Opteron? I
read
> a review from anandtech that basically tore the Xeon up. What are your
> thoughts?

Again.. it depends.

I've run SQL databases on a Single CPU 500Mhz machine and it was adequate.

I have a database on Quad Xeon 700Mhz 2MB Cache machine with U160 RAIDs and
that's barely fast enough for 42 million lookups a day and 17 million writes
a day.

So... again.. what will you be doing.

(and there are books that will give you numbers on various things like I/Os
per disk, etc that let you calculate what you need.)

> http://www.anandtech.com/IT/showdoc.aspx?i=1935&p=9|||The whole SCSI vs. SATA debate comes down to nothing more than the
external interfaces for the drives. You need to look beyond that and
compare the drives themselves and then take a look at what you want to
do with them.

In simple terms SATA drives are not yet designed for 24x7 use with the
same MTBF as SCSI drives, if you take a close look at the numbers you'll
find that the external interface, SCSI or SATA, isn't the deciding
factor in the drives performance. SCSI drives are just built better in
very simple terms and do perform better, remember that performance isn't
all about rpm. If you need the extra performance and reliability then go
for SCSI, if money is tight then go for SATA.

HTH,
g.

http://www.sqlskunkworks.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Handling world time with SQL and ASP.NET

Hi,

I would like to capture date time in my SQL during a certain operation. This piece of data will be displayed to an ASP.NET page.

When displaying the date time, I want it converted to the local time zone equivalent as set in the user's operating system.

What setup do I need on the SQL and the coding in the ASP.NET page?

Thanks.The following MSDN article may help you to understand date & time and also avoid inherant problems when dealing with different time zones.

Best Practices Using DateTime in the .NET

Hope this helps

Regards

Wayne Phipps|||Hi Wayne,

Thanks. That's a great article on issues related to saving and computing date time when daylight saving is relevant.

I'm also looking for saving date time in one time zone, and then display the date time relevant to the target user's system. I managed to find a web site that's dedicated to addressing this issue, SimpleTimeZone .NET Framework Class (http://www.michaelbrumm.com/simpletimezone.html). However I have not evaluated the .NET class concerned.

Thanks again for your reply.

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuing
multiple queries by breaking them up via WHERE clause filters isn’t the way I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
..NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
Scott
The XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommandforXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReadersr1 = new StreamReader(mQueryFileName);
stringquery = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuin
g
multiple queries by breaking them up via WHERE clause filters isn’t the wa
y I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.P() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
.NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
ScottThe XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommand forXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReader sr1 = new StreamReader(mQueryFileName);
string query = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.P() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Handling provider-level errors from .Net SqlClient Data Provider

Hi,

After reading this helpful blog entry:

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I think this might be a good place to ask the following question.

I am writing the error handling code for my data access layer for a web application. I am using the Enterprise Library Data Access Application Block. Although this supports generic database connections, I realized that I need to handle errors specific to each database type. Microsoft SQL is the only database type I am using for now, so I am using a try...catch (SqlException e).

In testing my code, I intentionally changed the instance name in web.config to a name that does not exist. I get the very popular error 26 - Error Locating Server/Instance Specified. This is returned as a SqlException, but the SqlError.Number property is set to -1.

Am I getting "-1" because the provider hasn't actually connected to SQL yet, so it doesn't have an actual SQL error number? Can I assume that (SqlError.Number == -1) is always a fatal, provider-level connection exception? Will the provider ever use another SqlError.Number of its own? Or do all numbers besides -1 come from the SQL sysmessages table?. Is there a comprehensive list of what exceptions might be raised by the SqlClient provider, including #26?

The reason for all the questions is that in a web application, I want to prevent the end-user from seeing the "real" exception if it has to do with configuration errors. However, maybe there are other errors that the user should see and handle? It's hard to know without a full list of SqlClient provider errors, along with the SqlError.Number that each error maps to.

Thanks and regards,

Mark

I can only answer 1. SqlError.Number corresponds to the SQL Server message id from sys.messages table on the server, so if it is not -1 it indicates an error message coming from the server.

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks

Waseem

|||

Waseem Basheer - MSFT wrote:

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks Waseem. I'm familiar with sysmessages. For messages coming directly from SQL server, sysmessages is all I need.

I am looking for a comprehensive list of SqlClient messages that are NOT in sysmessages, i.e. the errors that are raised directly by the .NET SqlClient provider. Hopefully this is a much more limited number of messages.

Regards,

Mark

|||

Hi, mcbsys

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

HTH

Ming.

|||

MING LV wrote:

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Good idea, I will try that.

Thanks,

Mark

Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.

Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.