I have a C# application that connects to a SQL Server Express 2005 instance. One of the testers here shuts his machine down every night and first thing in the morning when he fires up the application it fails to connect. If he tries to open it again right after that it connects. What would the failed attempt do that would fix the instance?
View 6 RepliesI have a Local DTS Package which will execute fine...no problems. When I try to schedule it from my NT 4.0 Workstation to the NT 4.0 Server, I get the job scheduling dialog box...fill it out...click OK...and NOTHING.
No error message, nothing in the SQL error log, nothing in the NT logs, no job in the schedule! When I perform the same task at the server logged in as Administrator it schedules successfully.
Any feedback would be greatly appreciated.
Eric
Hi Guys,
I am working on MS Reporting Services...
This is the error I get
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
The request failed with HTTP status 404: Not Found.
The following is the code(Its an aspx code).
<rsweb:ReportViewer ID ="SummarReportViewer" runat="server" Height ="760px" Width="1076px" ProcessingMode="Remote" ShowParameterPrompts="False">
<ServerReport ReportPath="/Pages/Report.aspx?ItemPath=/TestForecastReports/SummaryReport"
ReportServerUrl="http://ricsqltest2/Reports
" />
</rsweb:ReportViewer >
I am able to access the report when I hit @ http://ricsqltest2/Reports /Pages/Report.aspx?ItemPath=/TestForecastReports/SummaryReport
I also changed the code in C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportManagerRSWebApplication.COnfig to
<Configuration>
<UI>
<ReportServerUrl>http://ricsqltest2/Reports</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
****
****
****
</Configuration>
Can any One plz help me out.
Thanks & Regards,
Mansoor.
I have RS running on Standard server 2003 I receive the follow when trying to access the main page.
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version
I can view reports from the location below just not view the main page.
servername/ReportServer
works fine
included error log file
01/02/07 18:10:34, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 2364
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x4747EC40
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
01/02/07 18:10:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: D:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesLogFilesSQLDmpr0001.mdmp
01/02/07 18:10:34, ACTION, ReportingServicesService.exe, Watson Invoke: No
Please help anyone.
Thanks Alan
.
I have a web page that contains a ReportViewer control. I am trying to display a report, which is an .rdl file located on the SSRS server, in this Report Viewer control. I have set the ReportPath and ReportServerUrl correctly. I am getting an error message.Am I suppose to use an .rdlc file rather than a .rdl file? Does the web server configuration need to use a certain account? I am getting the following error message:The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
View 4 Replies View Related
Hello,
I moved the web app. I'm working on to another machine. Everything seemed to go well. Installed Reporting Services and configuration worked well with green ticks everywhere and I'm running reports in Report manager and in Visual Studio. However running off the web app. gives the message:
"The attempt to connect to Report Server failed" - there's also a ref. to an HTTP 404 error.
In RSWebApplication.config I have :
<ReportServerUrl></ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
and in RSreportserver.config I have:
<UrlRoot><server IP>/Reportserver</UrlRoot>
The install might not have gone as well as I thought because there was a "ReportServer" website created in IIS at same level as default. (Also Reports and ReportServer as virtual directories under Default website). The rogue website is 'stopped' at present. Would be grateful for any help with this. One thing is that I'm now behind a proxy so I'm using localhost:8080
Hi , have a database on sqlexpress 2005 that I would like to use from vs 2008 c++. Using :
String ^ connectString = "Data Source=localhost;Database=sqlexpress\FAR_HISTORY;UID=eclipz;Password=litespeed";
SqlConnection^ sqlconn = gcnew SqlConnection(connectString);
sqlconn->Open();
and it fails: Additional information: Login failed for user 'eclipz'. I have the user id created under the "security tab" in SQL Server Management Studio express with the correct DB.
Next I tried Database="Trusted_Connection=yes............."
It fails again.
But If I try connecting to the Northwind DB created under MSDE 2000 using the "sa" account it logins ok !!!!!!
Help?
Hi all,
I have just installed Microsoft SQL server 2005.
From Management Studio I try to connect to server using as Server Name:computerNameSQLEXPRESS.
Unfortunately I am not able to connect, instead of this I receive an error message:
Cannot connect to MARIAPORTSQLEXPRESS
Additional Information:
An error has occured 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. (providerQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)
Please help me!!
Thanks in advance,
Maria
I get the following error when trying to create an Access Data Project:Login failed for user 'myname'. Reason: Not associated with a trustedSQL Server connection.I'm using a specific username and password. The sQL Server (actually, MSDE)is installed on \server1, and I'm working on my laptop.I get the error whenI try and click the dropdown for "Select the database on the server". Anyhints on what I've donw wrong?Thanks, in advance.
View 1 Replies View RelatedWe are running SQL 2005 Standard on Windows 2000 Server Standard. We are trying to connect via SSMS with the Object Explorer to the server and it is failing with an error message stating:
"An exception occurred while executing a Transact-SQL statement or batch.
Additional Information:
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)
Using profiler, I tracked down that it is a call to xp_instance_regread. This is something that SSMS is issuing - not me.
We've found in the past that rebooting the server fixes the problem. But it keeps cropping up. The only thing running on this server is SQL 2005 - so if something is monkeying with the registry - it's got to be SQL Server itself.
Querying works just fine - we can use the editor to perform queries without any issue - it's just explorer that goes kaboom.
We have SP2a installed on the server and on most (if not all) of the clients.
This is getting frustrating because it's a production server and we're having to reboot this to fix it.
I am trying to set-up membership options on my MS Server 2003.
I also have a Computer running MS XP Professional sp2.
With the Advanced in stall of SQLExpress - trying to establish cojnectivity on the server fails when using the ASP.NET Configuration button.
This works fine on the XP box.
On the security tab the following message apppears:
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.
The following message may help in diagnosing the problem: Unable to connect to SQL Server database.
I've compared all settings in ssemee, surface manager etc. - all identical.
run aspnet_regsql on both machines. Added the SERVERASPNET account to SQLExpress and to the aspnetdb database.
I've enabled TCP,remote connections, named pipes etc. to no avail.
Any suggestion as to what the error could be would be most appreciated - been trying now for 3 days to get this working without any success.
I'm attempting to connect to a database file through visual studio 2013 Ultimate. The .mdf file is located on my local drive inside the App_Data folder of the project. However when I try to connect to the file it fails and throws an error message (see below).
“The attempt to attach to the database failed with the following information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. The specified LocalDB instance does not exist.”
II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomainmyuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..
In my string below, my forum setup up file, setup.asp , when run delivers an error that it is unable to connect to the database. Error: 800004005
I am running this from within IIS 5.0 aon a Windows 2000 Server, by right clicking the file in IIS and choosing "browse" I am trying to set this up so user IUSR_SERVER_NAME can "read and Write" to the database or instance I have created called rpggamerpro. The authentication in the SSSAC is set to use both Windows and SQL Authentication for the "master". I have set permissions on the folder which contains the SQL 2005 Express installation and database for IUSR_SERVER_NAME to read/write. I have created a IUSR_SERVER_NAME user in the Security/User directory for the database or instance "rpggamerpro" and gave the user read/write permissions there. So I am still at a loss as to what permissions, strings, settings, configuration or whatever the heck else needs to be done in order to get this thing to work. Please anyone willing to help a realative new commer to the SQL and DB world would be greatly appreciated.
trConnString = "Provider=SQLOLEDB;Data Source=nssqlexpress;database=rpggamerpro;uid=IUSR_NS;pwd=;" '## MS SQL Server 6.x/7.x/2000 (OLEDB connection)
I just installed SQL Server 2005 CTP (9.00.1116) on Windows Server 2003 with SP1. Then I unzipped DotNetNuke portal site and tried to connect to it with various errors, generally relating to invalid login.
View 3 Replies View RelatedI am hosting my website with a web hosting company on the web. My web application readsdata from a SQL server 2005 database. So far I have been able to establish a connection to the database, but when I attempt to query data from a table, I a get an error message.I can't figure out what I am doing wrong here.basically I just want to read data from acolumn in the database and to store it into a multi-line textbox control. I am using someinline sql for the call. Can someone help me out here?
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { mySqlConnection = new SqlConnection(ConnectionStringConst); mySqlConnection.Open(); try { // Database is named "Torsion", Table I am querying is named "Content" SqlCommand cmd = new SqlCommand("SELECT * FROM [Torsion].[dbo].[Content] ORDER BY [Section] DESC", mySqlConnection); rdr = cmd.ExecuteReader(); ListBox1.DataSource = rdr[0]; ListBox1.DataTextField = "HLContent"; // HLContent is the first column in my database ListBox1.DataBind(); // I want to store HLContent into ListBox1 control } catch { throw;} finally { mySqlConnection.Close(); mySqlConnection.Dispose(); } }}-----------------------------------------ERROR_MESSAGE_BELOW----------------------------
Server Error in '/' Application.
Invalid attempt to read when no data is present.
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.InvalidOperationException: Invalid attempt to read when no data is present.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[InvalidOperationException: Invalid attempt to read when no data is present.]
System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) +137
System.Data.SqlClient.SqlDataReader.get_Item(Int32 i) +7
_Default.Button1_Click(Object sender, EventArgs e) +167
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
Hello,The code below is my first attempt at sql server 2005 and asp.net v2.0. Can anyone let me know what they think of it, ie things that are bad and should be changed?thanks.csusing System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Web.Configuration;using System.Net.Mail;public partial class register : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (Session["ID"] == null) { //notlogged in uxRegisterPanel.Visible = true; uxSuccessPanel.Visible = false; uxExistPanel.Visible = false; } else { //logged in uxRegisterPanel.Visible = false; uxSuccessPanel.Visible = false; uxExistPanel.Visible = true; } } protected void submit_Click(object sender, EventArgs e) { int anyerror = 0; int receiveUpdate = 0; if (uxNameText.Text.Length < 3) { uxNameLabel.Text = "*Must be 3 or more characters long"; anyerror = 1; } else { uxNameLabel.Text = ""; } if (uxPassText.Text.Length < 6) { uxPasswordLabel.Text = "*Must be at least 6 characters"; anyerror = 1; } else { uxPasswordLabel.Text = ""; } if (uxUpdateCheck.Checked) { receiveUpdate = 1; } if (anyerror == 0) //all fine submit to db to check that username/email does not exist { //new connection string connectionString = WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionString); try { myConnection.Open(); SqlCommand memberCheck = new SqlCommand("memberCheck", myConnection); memberCheck.CommandType = CommandType.StoredProcedure; memberCheck.Parameters.Add("@name", SqlDbType.VarChar); memberCheck.Parameters["@name"].Value = uxNameText.Text; SqlDataReader memberCheckReader; memberCheckReader = memberCheck.ExecuteReader(); if (memberCheckReader.HasRows) { //if username exists uxNameLabel.Text = "<br />" + uxNameText.Text + " already exists, choose another or if this is you <a href='/sendPassword.aspx'>send your password</a>."; anyerror = 1; } else { uxNameLabel.Text = ""; } memberCheckReader.Close(); SqlCommand memberECheck = new SqlCommand("memberECheck", myConnection); memberECheck.CommandType = CommandType.StoredProcedure; memberECheck.Parameters.Add("@email", SqlDbType.VarChar); memberECheck.Parameters["@email"].Value = uxEmailText.Text; SqlDataReader memberECheckReader; memberECheckReader = memberECheck.ExecuteReader(); if (memberECheckReader.HasRows) { //if email exists uxEmailLabel.Text = "<br />" + uxEmailText.Text + " already exists, have you <a href='/sendPassword.aspx'>forgoten your password</a>?"; anyerror = 1; } else { uxEmailLabel.Text = ""; } memberECheckReader.Close(); if (anyerror == 0) { //insert member details SqlCommand memberInsert = new SqlCommand("memberInsert", myConnection); memberInsert.CommandType = CommandType.StoredProcedure; memberInsert.Parameters.Add("@name", SqlDbType.VarChar); memberInsert.Parameters["@name"].Value = uxNameText.Text; memberInsert.Parameters.Add("@email", SqlDbType.VarChar); memberInsert.Parameters["@email"].Value = uxEmailText.Text; memberInsert.Parameters.Add("@password", SqlDbType.VarChar); memberInsert.Parameters["@password"].Value = uxPassText.Text; memberInsert.Parameters.Add("@receiveUpdate", SqlDbType.SmallInt); memberInsert.Parameters["@receiveUpdate"].Value = receiveUpdate; SqlDataReader memberInsertReader; memberInsertReader = memberInsert.ExecuteReader(); memberInsertReader.Read(); //store session stuff Session["Name"] = uxNameText.Text; Session["ID"] = memberInsertReader["ident"].ToString(); Session["Access"] = 0; //send activation email MailMessage email = new MailMessage("noreply@name.com", uxEmailText.Text); email.Subject = "Action needed to activate your account at cookbuzz.com"; email.Body = "Please follow the link below to activate your account" + memberInsertReader["ident"].ToString(); SmtpClient mailClient = new SmtpClient(); mailClient.Host="smtp.ntlworld.com"; mailClient.Send(email); memberInsertReader.Close(); uxRegisterPanel.Visible = false; uxSuccessPanel.Visible = true; uxSentTo.Text = uxEmailText.Text; } } catch (Exception err) { Response.Write("Error:" + err); } finally { myConnection.Close(); } } }}.aspx<%@ Page Language="C#" AutoEventWireup="true" CodeFile="register.aspx.cs" Inherits="register" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <link href="StyleSheet.css" rel="stylesheet" type="text/css" /> <title>Cook Buzz ::</title></head><body> <form id="form1" runat="server"> <h1>Register</h1> <asp:Panel ID="uxRegisterPanel" runat="server"> <table> <tr> <td>User name: </td> <td><asp:TextBox ID="uxNameText" runat="server" MaxLength="50"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="uxNameText" CssClass="error" Display="Dynamic" ErrorMessage="*Input a username"></asp:RequiredFieldValidator> <asp:Label ID="uxNameLabel" runat="server" CssClass="error"></asp:Label></td> </tr> <tr><td colspan="2"><div class="small">An activation code will be sent to the address below</div></td></tr> <tr> <td>Email address: </td> <td><asp:TextBox ID="uxEmailText" runat="server" MaxLength="100"></asp:TextBox> <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="uxEmailText" ErrorMessage="*Invalid email" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*" CssClass="error" Display="Dynamic"></asp:RegularExpressionValidator><asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="uxEmailText" CssClass="error" Display="Dynamic" ErrorMessage="*Input an email"></asp:RequiredFieldValidator> <asp:Label ID="uxEmailLabel" runat="server" CssClass="error"></asp:Label></td> </tr> <tr> <td>Confirm Email: </td> <td><asp:TextBox ID="uxCEmailText" runat="server" MaxLength="100"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="uxCEmailText" CssClass="error" Display="Dynamic" ErrorMessage="*Input an email"></asp:RequiredFieldValidator> <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToCompare="uxEmailText" ControlToValidate="uxCEmailText" ErrorMessage="*Emails dont match" CssClass="error"></asp:CompareValidator></td> </tr> <tr><td colspan="2"><div class="small">Passwords must be 6 or more characters in length</div></td></tr> <tr> <td>Password:</td> <td><asp:TextBox ID="uxPassText" runat="server" TextMode="Password" MaxLength="50"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="uxPassText" CssClass="error" Display="Dynamic" ErrorMessage="*Input a password"></asp:RequiredFieldValidator> <asp:Label ID="uxPasswordLabel" runat="server" CssClass="error"></asp:Label></td> </tr> <tr> <td>Confirm Password: </td> <td><asp:TextBox ID="uxCPassText" runat="server" TextMode="Password" MaxLength="50"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="uxCPassText" CssClass="error" Display="Dynamic" ErrorMessage="*Input a password"></asp:RequiredFieldValidator> <asp:CompareValidator ID="CompareValidator2" runat="server" ControlToCompare="uxPassText" ControlToValidate="uxCPassText" CssClass="error" ErrorMessage="*Passwords dont match" Type="String" ValueToCompare="Text"></asp:CompareValidator></td> </tr> <tr><td></td><td> <asp:CheckBox ID="uxUpdateCheck" runat="server" Checked="True" Text="Receive email updates" /></td></tr> <tr> <td></td> <td> <br /> <asp:Button ID="submit" runat="server" Text="Register" OnClick="submit_Click" /><br /> <div class="small">By clicking register you argree to the <a href="terms.aspx">Terms & Conditions</a>.<br /> We take privacy seriously, please read our <a href="privacy.aspx">Privacy Policy</a>.</div></td> </tr> </table> </asp:Panel> <asp:Panel ID="uxExistPanel" runat="server"> You are already registered and logged in.<br /> <br /> If you are waiting for an email to activate your account then you can <a href="resendactivation.aspx"> resend activation email</a>.</asp:Panel> <br /> <asp:Panel ID="uxSuccessPanel" runat="server"> You are now registered and logged in.<br /> <br /> Before you can begin to use your account you need to click the activation link sent to <asp:Label ID="uxSentTo" runat="server"></asp:Label>. Please be aware that it may be delivered to junk mail folders.<br /> <br /> If you do not receive an activation email you can <a href="#">resend the activation email</a> or <a href="#">change your email address</a>.</asp:Panel> </form></body></html>stored procsset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[memberInsert] @name varchar(50), @password varchar(50), @email varchar(100), @receiveUpdate smallintAS BEGIN SET NOCOUNT ON; Insert into Member(Name,Password,Email,JoinDate,LastActivity,groupID,receiveUpdate) Values(@name,@password,@email,getdate(),getdate(),0,@receiveUpdate) DECLARE @code varchar(4) SET @code = round(rand()*8999,0)+1000 select @@identity as ident, @code as code INSERT into activation(memberID,code) values(@@identity,@code)set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[memberCheck] ( @name varchar(50) )ASBEGIN /* SET NOCOUNT ON */ --check to see if username is in use--SELECT name from Member where Name = @nameENDRETURN
View 4 Replies View RelatedI have a standard Login ASP.NET 2.0 control on a login Page, a LoginName and LoginStatus controls on the member's page.
once the user login successfully I am redirecting the user to Member.aspx page. The following is my machine configuration
Windows XP Pro Service Pack2
IIS 5.1
SQL Server 2000
visual Studio 2005
DISABLE ANONMYOUS ACCESS IN IIS
ENABLE WINDOWS AUTHENTICATION
I am Using form authentication, and I am getting following error "Your login attempt was not successful. Please try again".
To debug,I am displaying the follwoing properties
Membership.ValidateUser(Login1.UserName, Login1.Password) : - True
HttpContext.Current.User.Identity.Name : - // is blank
FormsAuthentication.FormsCookieName : - SqlAuthCookie
FormsAuthentication.CookieDomain :- // is blank
FormsAuthentication.CookieMode :- UseCookies
FormsAuthentication.CookiesSupported :- True
FormsAuthentication.DefaultUrl :- /webIT/default.aspx
FormsAuthentication.EnableCrossAppRedirects :- False
FormsAuthentication.FormsCookieName :- SqlAuthCookie
FormsAuthentication.FormsCookiePath :- /
FormsAuthentication.LoginUrl :- /webIT/ASPX/Security/Login.aspx
FormsAuthentication.RequireSSL :- False
FormsAuthentication.SlidingExpiration :- True
User.Identity.IsAuthenticated :- False
User.Identity.Name :- // is blank
User.Identity.AuthenticationType :- // is blank
Is it something to do with the security of Windows XP Pro ? or a IIS 5.1? Any help will be highly appreciated.
Login.aspx file is
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:Login ID="Login1" runat="server" DestinationPageUrl="~/Member.aspx" RememberMeSet="True">
</asp:Login>
</form>
</body>
</html>
Member.aspx is
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Member.aspx.cs" Inherits="Member" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
You are back ........
<asp:LoginName ID="LoginName1" runat="server" />
<asp:LoginStatus ID="LoginStatus1" runat="server" LogoutAction="Redirect" LogoutPageUrl="~/Login.aspx" />
</div>
</form>
</body>
</html>
Member.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Member : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("<br/>Page.User.Identity.Name: " + Page.User.Identity.Name);
Response.Write("<br/>Page.User.Identity.IsAuthenticated: " + Page.User.Identity.IsAuthenticated);
Response.Write("<br/>Page.User.Identity.AuthenticationType: " + Page.User.Identity.AuthenticationType);
}//end
}
Web.config file is
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<clear />
<add name="SQL_CONNECTION_STRING"
connectionString="server=Sample;database=sample;user id=sa;password=temp;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
<system.web>
<authentication mode="Forms">
<forms name="SqlAuthCookie"
loginUrl="~/ASPX/Security/Login.aspx"
timeout="60"
domain=""
path="/"
protection="All"
requireSSL="false"
slidingExpiration="true"
defaultUrl="default.aspx"
cookieless="UseCookies"
enableCrossAppRedirects="false"
/>
</authentication>
<!-- Deny Access to anonymous user -->
<authorization>
<deny users="?" />
<allow users="*"/>
</authorization>
<!-- SQL Server Memebership Provider's-->
<membership defaultProvider="SqlProvider"
userIsOnlineTimeWindow="30">
<providers>
<clear />
<add connectionStringName="SQL_CONNECTION_STRING"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordFormat="Hashed"
applicationName="webIT"
name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>
<!-- SQL Server RoleManager Provider's-->
<roleManager enabled="true"
cacheRolesInCookie="true"
cookieName=".ASPXROLES"
defaultProvider="SqlRoleProvider">
<providers>
<clear />
<add connectionStringName="SQL_CONNECTION_STRING"
applicationName="webIT"
name="SqlRoleProvider"
type="System.Web.Security.SqlRoleProvider" />
</providers>
</roleManager>
</configuration>
NOTE : The applicationName is same in web.config as well as in the aspnet_Applications table in SQL Server 2000 membership database.
What if I attempt to creat a (merge) publication while the production sql server is online and active?
From another point of view, is there any drawback if I try to create (merge) publication while the tables are being used by users?
Thanks
Ekrem Önsoy
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
dtexec /f "C:Program FilesMicrosoft SQL Server90DTSPackagesIntegration Services Project estcom.dtsx"
pause
Why it's not running with SQL Server Agent???
New project and this one going with stored procedures.
Should have done it on my first project, but learn as you go...
I have the following code to insert info into the database....
SqlCommand myCommand = new SqlCommand("sp_AddOnlineUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@IP_Address", Request.ServerVariables["REMOTE_ADDR"]);
myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
myCommand.Parameters.Add("@user_Agent", Request.ServerVariables["HTTP_USER_AGENT"]);
myCommand.Parameters.Add("@Referer", Request.ServerVariables["HTTP_REFERER"]);
Now, I just want to be sure this stored procedure is correct and if not, suggestions....
create proc dbo.sp_AddOnlineUser
@IP_Address char(4),
@Session_ID nvarchar(100),
@user_Agent nvarchar(255),
@Referer nvarchar(255)
Thanks all,
Zath
Hello
This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
take care,
~Iris~
:)
Hi all,
I'm not too sure if this should be in this forum or in the admin one, but since DBCC is required to fix this error, so I might as well put it here. What I'm having is this error msg:
Attempt to fetch logical page in database 'DBName' belongs to object 'TableName', not to object 'TableName'.
Error: 605, Severity: 21, Stat: 1
I've checked BOL and it said that DBCC CheckTable on the 2nd table will fix this issue (or by using CheckDB), that is fine but what I want to know is why is this happening, and how to prevent it, I have a cluster server holding this DB, we have disabled write cache and we have tried to fail over to another node, but this issue does happen again (more often now), its running on SQL 2000 with sp4. Anyone got any ideas of why this is happening? and how to prevent it?
Thanks
I was hoping I could get this to work, so far I can access all the data from the database, but I also want to update it. I placed a button, updateBttn_Click event to update the database.It's not updating though and I am not gettting any errors. Could you please look at the update query in the update button and see if I have it right. Also, I think it might be because I have the first retrieval of the database tables set to ExecuteReader(). Do I need to redo this and use ExecuteNonQuery somewhere else?Thanks in advance. using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class userpage : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { usrnmeLbl.Text = PreviousPage.CurrentUser; hiddenpassLbl.Text = PreviousPage.CurrentPass; //Define database connection SqlConnection conn = new SqlConnection( "Server=localhost\SqlExpress;Database=MyDB;" + "Integrated Security=True"); //Create command //SqlCommand comm = new SqlCommand("SELECT UserName, Password, UserID FROM UsersTable WHERE UserName = '" + usrnmeLbl.Text + "'", conn); //SqlCommand comm = new SqlCommand("SELECT Father FROM FamHistTable WHERE UserID IN (SELECT UserID, UserName, Password FROM UsersTable WHERE UserName = '" + usrnmeLbl.Text + "')", conn); //SqlCommand comm = new SqlCommand("SELECT FamHistTable.Father FROM FamHistTable INNER JOIN UsersTable ON FamHistTable.UserID = UsersTable.UserID WHERE UsersTable.UserID = '" + usrnmeLbl.Text + "'", conn); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = '" + usrnmeLbl.Text + "'", conn); //Open connection conn.Open(); //Execute the command SqlDataReader reader = comm.ExecuteReader(); //Read and display the data while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { readDB_Lbl.Text += reader["username"] + "<br />"; //readDB_Lbl.Text += "Your User ID is: " + reader["UserID"] + "<br />"; readDB_Lbl.Text += "Your Father's name is: " + reader["Father"] + "<br />"; readDB_Lbl.Text += "Your Mother's name is: " + reader["Mother"] + "<br />"; } else { readDB_Lbl.Text = "User and Password mismatch."; } } } reader.Close(); conn.Close(); } } protected void updateBttn_Click(object sender, EventArgs e) { SqlCommand comm = new SqlCommand("UPDATE FatherHistTable SET " + "'famUpdteDDL.SelectedItem.Value'" + " = " + "'addPrsnTxtBx.Text'" + "WHERE UserName = " + "'usrnmeLbl.Text'"); }}
View 8 Replies View RelatedI try to run a webform with gridview which is using the sqldatasource to get the customer table from local database, AdventureWorks_Data.mdf, it throws the following error:
Error:
An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I'm writing my first .net app in VB.net. I can connect to my database, but get the following error when I try to set a label value. (My code is listed below) What am I missing?
Exception Details: System.InvalidOperationException: Invalid attempt to read when no data is present.Source Error:
Line 19:
Line 20: reader = comm.ExecuteReader()
Line 21: EmployeesLabel.Text = reader.Item("tkinit")
Line 22:
Line 23: reader.Close()
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim conn As SqlConnectionDim comm As SqlCommand
Dim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("IntegratedDataConnectionString").ConnectionString
conn = New SqlConnection(connectionString)
comm = New SqlCommand("Select top 1 tkinit from tEliteTimeKeep", conn)
conn.Open()
reader = comm.ExecuteReader()EmployeesLabel.Text = reader.Item("tkinit")
reader.Close()
conn.Close()
End Sub
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server"><title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="EmployeesLabel" runat="server" ></asp:Label>
</div></form>
</body>
</html>
I try to display normal gridview in a webform but it throws this error:
An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataCommerce.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Line 45: Dim myAdapter As New SqlDataAdapter(myCommand)Line 46: myAdapter.Fill(myDataSet)Line 47: Line 48: gv_CashStatementList.DataSource = myDataSet
Web.Config
<add name="CommerceConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataCommerce.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
My query actually returns rows but when I run, I get this error "Invalid attempt to read when no data is present". It says that the datareader does not return any rows.
Please help.
Thanks in advance
Sangita
Hi all,
I'm new to SSRS. I installed reporting services with my other services in SQL Server 2005. When I started Reporting Service Configuration tool,I see that my Reporting Server Status Stopped. When i am trying to start the service, i get the following error(exception)....also attached are the logfiles....
EXCEPTION DETAILS:
System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.
at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout)
at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)
LOGFILE1:
-----------------------
------------------------------
ReportingServicesService!library!8!3/6/2008-20:26:48:: e ERROR: Sql error:System.Data.SqlClient.SqlException: Could not find stored procedure 'GetDBVersion'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.ConnectionManager.EnsureCorrectDBVersion()
ReportingServicesService!library!8!3/6/2008-20:26:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights.
ReportingServicesService!library!8!3/6/2008-20:26:48:: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights.
at Microsoft.ReportingServices.Library.ConnectionManager.EnsureCorrectDBVersion()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread()
ReportingServicesService!library!8!3/6/2008-20:26:48:: Attempting to start service again...
LOGFILE2:
------------
--------------------
ReportingServicesService!library!4!3/6/2008-20:26:47:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'MACHINE_NAMEASPNET' is not recognized., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'MACHINE_NAMEASPNET' is not recognized.
ReportingServicesService!servicecontroller!9!3/6/2008-20:26:47:: Total Physical memory: 1063641088
ReportingServicesService!servicecontroller!4!3/6/2008-20:26:47:: e ERROR: Exception caught starting RPC server: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'MACHINE_NAMEASPNET' is not recognized.
at Microsoft.ReportingServices.Library.Native.NameToSid(String name)
at Microsoft.ReportingServices.Library.ServiceAppDomainController.StartRPCServer(Boolean firstTime)
The IIS Version I'm using is 5.1
Hi All,I think this is a thorny problem, and I'm hoping you can help. I've notfound this exact issue described anywhere yet.I have a stored procedure that calls BULK INSERT on a set of text files.These files are FTP'd from a legacy system (a mainframe running MVS).Sometimes, the process steps on iteslf, whereby the bulk insert is attemptedon a file whose FTP is still in progress; it's not fully written to disk onthe SQL box (it's a 100MB file that takes a long time to cross the networkto the share on the Windows box), and so the insert generates a fatal errorand the procedure stops.I wrote a proc that calls the sp_OA* procs to use theScripting.FileSystemObject to test for the file's readiness. It returns anerror for me if the file does not exist. It also returns an error if I tryto run the BULK INSERT against a file which is being copied via Windows tothe SQL box. This is working just fine under those conditions; thesp_OAMethod to call OpenTextFile bombs appropriately if the file write isstill in progress.That's great, but it doesn't do the same thing during an FTP in progress.It doesn't generate the same error (that is OpenTextFile has no problemopening a partially written FTP'd file). I can also open the file inNotepad, even though it's not fully written to disk ... something I did notexpect, but there we are. What is it about FTP that's different from aWindows file system copy operation that makes the file look available forreading?If BULK INSERT is capable of detecting that it cannot do its thing on a filewhose FTP is in progress, then what can I write or call to emulate thatdetection? I tried writing a COM object in VB.NET and calling that from mySQL stored proc instead of the Scripting Engine's FSO methods. My codesimlpy tries to run a FileOpen using an Exclusive read lock; however, thisdoesn't seem to work, and I'm shooting in the dark now.Can anyone tell me what kind of file i/o request BULK INSERT makes, suchthat it is capable of abending during a run against an incompletely writtenfile using FTP?Thanks!Tim
View 3 Replies View RelatedSQL SERVER 2000Hi allThis is my first attempt at writing a stored procedure. I have managed toget it working but its unlikely to be the best way of handling the problem.While writing it I found some things that I don't understand so if any onecould shed any light it would be much appreciated. I have posted these atthe end.Sorry about the length but I thought it might be worthwhile posting the codeThe purpose of the procedures is as follows : we have a view of lots of bitsof information that need automatically mailing to different people. eachelement of information has a name allocated against it. If we had 100 piecesof data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager3 etc...Both SP's look at the same viewThe first SP generates a distinct list of managers and for each managercalls the second SPThe second SP filters the view for the data belonging to the selectedmanager, and builds an HTML mail. It then sends all the bits of informationbelonging to that manager off in an EMAIL to him/her. ( It uses a brilliantbit of code from sqldev.net to handle the mail)the first mail then repeats for all the managers in the listCODE ---- SP 1ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2ASbeginSET NOCOUNT ONdeclare @no_of_managers as intdeclare @current_record as intdeclare @manager_name as varchar(100)-- count how many distinct managers we need to send the mail toselect @no_of_managers = COUNT(DISTINCT manager_name) FROMdbo.vw_client_notification_email_1-- open a cursor to the same distinct listdeclare email_list cursor for select distinct manager_name fromdbo.vw_client_notification_email_1 dscopen email_list-- for each distinct manager get the managers name and pass it to the storedprocedure that generates the mail.set @current_record = 0while (@current_record) < @no_of_managersbeginfetch next from email_list into @manager_nameEXECUTE dbo.pr_admin_client_weekly_notification @manager_nameset @current_record = @current_record+1end-- close the cursorclose email_listdeallocate email_listendCODE ---- SP2ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION(@current_manager_name as varchar(100))-- a unique managers name is passed from the calling procedureas beginSET NOCOUNT ON-- declarations for use in the stored procedureDECLARE @to as varchar(100)DECLARE @entry varchar(500)DECLARE @region as varchar(100)DECLARE @type as varchar(100)DECLARE @site_ref as varchar(100)DECLARE @aborted as varchar(100)DECLARE @weblink as varchar(1000)DECLARE @manager_name as varchar(100)DECLARE @manager_email as varchar(100)DECLARE @body VARCHAR(8000)DECLARE @link varchar(150)DECLARE @web_base VARCHAR(150)-- set up a connection to the view that contains the details for the mailDECLARE email_contents cursor for select region,type,site_ref,aborted_visit,link,manager_name,manager_e mail fromvw_client_notification_email_1 where manager_name = @current_manager_nameopen email_contents--some initial textset @body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to logon to xxxxx </a><br><br> '--fetch the first matching record from the table and build the body of themessagefetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailset @web_base = 'http://'set @weblink = @web_base + @linkif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'set @body = @body + '<font size="2"><b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ ' <b>Unique Report Reference </b>' + @link + '<br>'-- continue reading the records for this particular message and adding on tothe body of the textwhile(@@fetch_status = 0)beginfetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'if (@@fetch_status = 0) set @body = @body + '<b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ '<b>Unique Report Reference </b>' + @link + '<br>'end-- close the cursorset @body = @body + '</font>'close email_contentsdeallocate email_contents-- generate the mailDECLARE @rc int EXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N'FROM ME',@TO = @manager_email,@server = N'server',@subject = N'Weekly Import',@message = @body,@type = N'text/html'endQuestionsis the way I've done it OK. I thought I would be able to do it in a singleSP but I really struggled nesting the cursor things.@@fetchstatus seems to be global, so if your using nested cursors, how doyou know which one you are refering to. If you have multiple calls to thesame SP how does it know which instance of the SP it refers to.When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn'tget the while loop working - I have a feeling it was down to the @@fetchstatus in the 'calling' procedure being overwritten by the@@fetchstatus in the 'called' procedure.The whole @@fetchatus thing seems a bit odd. In the second procedure, I haveto fetch, then check, manipulate then fetch again, meaning that the samemanipulation code is written twice. thats why in the first procedure I usedthe select distint count to know how long the record set is so I only haveto run the manipulation code once. Is what I have done wrong?its possible that the body of the mail could be > 8K, is there anotherdatatype I can use to hold more than 8Kmany thanks for any help or adviceAndy
View 3 Replies View RelatedI have a stored procedure that inserts a record, and then either returns an error or, when successful, returns the entire row. This is a method I use on sites I have developed in other languages when inserting or updating data; I am new to c#.
With C# I have a datareader executing the sp, and the insert works fine, however I get the error "Invalid attempt to read when no data is present." when I try to read the data that should be coming back. I have researched and updated my code so that I no longer get the error, but I still cannot get the data back. I trap the sp in profiler, ran it in anaylzer, and it works fine - data is returned. But in the code, it does not see the data.
I have modified the return results so now I just have the ID coming back and that works fine. But I would rather just return the entire row. Can this be done, and if so, how?
Thanks.
I am using a standard dbreader type of loop in a query to retrieve data. I am running over what should be end of record set, every time.
I have altered my read procedures to use while dbreader.read() and if dbreader.read(), to attempt to avoid getting the error. Neither is stopping it.
While debugging it, as I get to the last item and actually get the error, if I check the dbreader status, it still indicates that it has rows.
Anyone have any ideas on how to get around this?
TIA, Tom