The dynamic SQL at the buttom causes a error:
Msg 402, Level 16, State 1, Line 9
The data types ntext and nvarchar are incompatible in the equal to operator.
I think it is due to the one of the where conditions [URL] = @old_URL ,both are set to NULL(@URL=NULL, @old_URL=NULL).
How do I make changes to the SQL statement to compare the conditions where NULL comparsion is inevitable.
exec sp_executesql N'UPDATE [Announcement] SET [Atype] = @Atype, [Title] = @Title, [Content] = @Content, [URL] = @URL WHERE [AnnouncementID] = @old_AnnouncementID AND [Atype] = @old_Atype AND [Title] = @old_Title AND [Content] = @old_Content AND [URL] = @old_URL',N'@Atype nvarchar(3),@Title nvarchar(3),@Content nvarchar(41),@URL nvarchar(4000),@old_AnnouncementID decimal(1,0),@old_Atype nvarchar(3),@old_Title nvarchar(3),@old_Content nvarchar(41),@old_URL nvarchar(4000)',@Atype=N'CMC',@Title=N'aaa',@Content=N'asdfg',@URL=NULL,@old_AnnouncementID=6,@old_Atype=N'CMC',@old_Title=N'aaa',@old_Content=N'asdfg',@old_URL=NULL
Any help would be apprecaied,
Ricky.
I have found that when I'm debugging a custom component in BIDS that I've created in another instance of Visual Studio, every time I rebuild the component I have to shutdown and restart BIDS and then reattach to the BIDS process. Which is pretty time consuming... And if I find a small error in my custom component when debugging then I don't seem to be allowed to make any changes to the code unless I stop debugging and go through the process above.
Am I missing something here? Or do I really have to manually go through these steps every time I want to change code in the component I'm debugging?
Can I automate the process with MSBuild or NAnt? If so, is there an example of this anywhere?
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Hi, (sorry for my english...)I have a probelm to build my Pivot table.Indeed, The goal is just to see an example of Table Pivot with our Database...For that, I use this link : http://www.csharphelp.com/archives4/archive623.htmlI try to adapt the code but some problems...I use visual developper, and i have installed OWC11 & .NET framework 2.0.using System;using System.Data;using System.Configuration;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 Microsoft.Office.Interop.Owc11;public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");PivotTableClass PTClass = new PivotTableClass();PTClass.ConnectionString = ConfigurationManager.ConnectionStrings["TimeTrackingDB"].ToString();PTClass.DataMember = "";PivotView pview = PTClass.ActiveView;Response.Write(pview.FieldSets.Count);}}}When i'm lauching there is an error : HRESULT E_FAIL http://fists148.free.fr/Error.JPGIf i put the last lines in commentary, public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");}}}The component is loading but it seems to be an issue with the connection database...In my web.config, I Have this : <connectionStrings><add name="TimeTrackingDB" connectionString="Data Source=PORT06139DEV_2000;Initial Catalog=TimeTracking;Persist Security Info=True;User ID=user_TTT;Password=user_TTT;pooling=false"/></connectionStrings>Error connection (http://fists148.free.fr/OWCscreen.JPG)If you have any idea, i take it...I'm deseperate :eek: Thanks By advance
I tried to confiure sql reporting service on vista box but I got the following page.
Server Error in '/Reports' Application.
Request is not available in this context 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.Web.HttpException: Request is not available in this context
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:
[HttpException (0x80004005): Request is not available in this context] System.Web.HttpContext.get_Request() +3465893 Microsoft.ReportingServices.UI.Global.get_ConfigurationManager() +47 Microsoft.ReportingServices.UI.GlobalApp.Application_Start(Object sender, EventArgs e) +32
[HttpException (0x80004005): Request is not available in this context] System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +3385130 System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +125 System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +182 System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +259 System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +245
[HttpException (0x80004005): Request is not available in this context] System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +3465475 System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +69 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +675
I tried a lot of searches online but could not find any solutions. I am running Vista Ultimate, SQL Server 2005 and IIS 7. I am sure I configure everything right under "Default Web Site". The same config is running perfectly under XP. Can anyone help please?
I have SSRS installed on a web server and points to another SQL Server with the ReportServer database. Every now and then I would get this error message on Report Manger:
Client found response content type of '', but expected 'text/xml'. The request failed with an empty response.
Here is a trace from Stack Trace from ReportServerWebApp log file: <Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product> <Locale>en-US</Locale> <TimeZone>Eastern Daylight Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesLogFilesReportServerWebApp__08_27_2007_00_09_07.log</Path> <SystemName>WATCOMWEB</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing ProcessRecycleOptions to default value of '0' because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing WatsonFlags to default value of '1064' because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing SecureConnectionLevel to default value of '1' because it was not specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!6!8/27/2007-00:09:07:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False' because it was not specified in Configuration file. w3wp!ui!6!8/27/2007-00:09:10:: e ERROR: Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. w3wp!ui!6!8/27/2007-00:09:10:: e ERROR: HTTP status code --> 500 -------Details-------- System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecureMethods() at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection() at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level) at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnInit(EventArgs e) at System.Web.UI.Page.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) w3wp!ui!6!8/27/2007-00:09:13:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
Other times I would get this error: The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href="/insightrptserver/logon.aspx?ReturnUrl=%2finsightrptserver%2fReportExe cution2005.asmx">here</a>.</h2> </body></html>
Stack Trace from ReportServerWebApp log: <Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product> <Locale>en-US</Locale> <TimeZone>Eastern Daylight Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesLogFilesReportServerWebApp__08_26_2007_22_03_51.log</Path> <SystemName>WATCOMWEB</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing ProcessRecycleOptions to default value of '0' because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing WatsonFlags to default value of '1064' because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing SecureConnectionLevel to default value of '1' because it was not specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!6!8/26/2007-22:03:51:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False' because it was not specified in Configuration file. w3wp!ui!6!8/26/2007-22:07:35:: Unhandled exception: System.Net.WebException: The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href="/insightrptserver/logon.aspx?ReturnUrl=%2finsightrptserver%2fReportExecution2005.asmx">here</a>.</h2> </body></html> --. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.GetExecutionInfo() at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetExecutionInfo() at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo() at Microsoft.Reporting.WebForms.ServerReport.SetExecutionId(String executionId, Boolean fullReportLoad) at Microsoft.Reporting.WebForms.ServerReport.LoadFromUrlQuery(NameValueCollection requestParameters, Boolean fullReportLoad) at Microsoft.Reporting.WebForms.ReportDataOperation..ctor() at Microsoft.Reporting.WebForms.HttpHandler.GetHandler() at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) w3wp!ui!5!8/26/2007-22:07:48:: Unhandled exception: System.Net.WebException: The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href="/insightrptserver/logon.aspx?ReturnUrl=%2finsightrptserver%2fReportExecution2005.asmx">here</a>.</h2> </body></html> --. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.GetRenderResource(String Format, String DeviceInfo, String& MimeType) at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetRenderResource(String Format, String DeviceInfo, String& MimeType) at Microsoft.Reporting.WebForms.ServerReport.GetRenderResource(String format, String streamId, String& mimeType) at Microsoft.Reporting.WebForms.ServerReportControlSource.GetRendererImage(String streamID, String& mimeType) at Microsoft.Reporting.WebForms.ReportImageOperation.GetRendererImage(String resourceID, HttpResponse response) at Microsoft.Reporting.WebForms.ReportImageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) w3wp!ui!7!8/26/2007-22:08:22:: e ERROR: Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. w3wp!ui!7!8/26/2007-22:08:22:: e ERROR: HTTP status code --> 500 -------Details-------- System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecureMethods() at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection() at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level) at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnInit(EventArgs e) at System.Web.UI.Page.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) w3wp!ui!7!8/26/2007-22:08:22:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
Does any know what causes these errors. They don't appear very often, but often enought for the users to complain.
I am trying to run a query where my Table and my request are variables(Will be used in Stored Procedure)Declare @EmailVARCHAR(100)Declare @TableVARCHAR(50)Declare @Count VARCHAR(8)DECLARE @cmd VARCHAR(500)set @Table = 'tblManager'set @Email = 'cauer@tampabay.rr.com'Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email= ' + @Emailexec(@cmd)The error I get isServer: Msg 207, Level 16, State 3, Line 7Invalid column name 'cauer@tampabay.rr.com'.I have worked on this for a while, any help would be greatlyappreciated.Chris Auer
Hi, i am using Vista and iis6. Evertything seems to be configured ok, but when i try to open this url: http://localhost/Reports/ i get the following error:
Server Error in '/Reports' Application.
Request is not available in this context this error is when Report directory is set for "DefaultAppPool", but in "ClassicAppPool" i get another error:
Error
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
I've installed Visual Studio 2005 Professional evaluation edition on both my desktop & on the server to try out the Debugger to be used with my Stored procedures
I got this error message every time i try to 'Step into Stored Procedure' from my desktop to connect to the stored procedure which resides on the Server.
Error message: Unable to Start T-SQL Debugging. Could not attach to SQL Server Process on 'Server1'.The RPC server is unavailable
The 'Step into Stored Procedure' works though if I open the stored procedure directly on the server.
Both my PC and Server has been installed with Remote Debugger and my windows login has been assigned permission to debug on the debugger that resides on the Server and has sysadmin server role on SQL 2005 both on PC and Server.
Can someone tell me what i miss out to debug Stored procedure in the SQL Server 2005 on the Server from my PC?
Just yesterday I implemented SqlCacheDependencies on my ASP.NET 2.0 web site for SQL Server 2005. My SQL Server 2005 database is in mode 90, mirrored with a second database server. Now I am getting Error 17310 while running very simple statements, but only on a specific table.
We have a table called "tblSchools" which, due to negligence and incompetence, has a maximum size of over 8,000. I plan on fixing that eventually, by splitting all the long varchar(2048) fields off into other tables.
I wanted to use SqlCacheDependencies on this table, for every single column. So I created a DateTime column called LastChanged and created a trigger. I am using LastChanged as a DateTime type instead of a TimeStamp/RowVersion type because I thought it would be more useful.
Here are the changes made: ---------------------------
-- Adding the column... ALTER TABLE tblSchools ADD LastChanged DateTime Null GO
-- The program uses this stored procedure to watch the "LastChanged" column. CREATE PROCEDURE [apCacheWatchSchool] @SchoolID int AS select LastChanged from dbo.tblSchools where SchoolID = @SchoolID GO
-- Any updates on the table cause the LastChanged column to be updated with getdate() CREATE TRIGGER [UpdateLastChanged_TS] ON [dbo].[tblSchools] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; update [dbo].[tblSchools] set LastChanged = getdate() where SchoolID in (select SchoolID from inserted); END GO
---------------------------
After adding this column and the trigger and s-proc, any update to any row in tblSchools fails with error 17310. It CANNOT set LastChanged to anything. It has to remain NULL. I was able to perform the following operation (as long as the tblSchools.LastChanged updating trigger [UpdateLastChanged_TS] was disabled): "update tblSchools set SchoolName = 'ZZZZ' + SchoolName where SchoolID = 185" -- so, it's not some kind of row length limit. I think it's a limit on # of columns that can be dealt with. I don't really know.
I don't know where the problem is occurring. I am just telling you why. I got around the problem (temporarily) by simply having the trigger update a DateTime column in another table, and having the SqlCacheDependency watch that column in the other table. No biggie.
As for moving some of the enormous varchar columns off of tblSchools... I don't really care enough. I guess that's why it's been like this for so long.
So, my problem is fixed, I just wanted to submit this bug report so you can at least fail gracefully in the next version of SQL Server.
I have the log/txt/mdmp files available, and can provide them upon request; they're too big for this form, sorry. Please contact me at plushpuffin AT wwddfd.com if you have any questions.
Hi, A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.
What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.
I am new to SQL 2005. I have one issue and it is as follows:
I write a simple query and run it; it works fine.
Now I break the network connection and run the query; in this case it runs fine.
But when I reconnect the network and run the query, then I get an error as follows:
"A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.) "
Every thing is local here. So how the network issue comes here?
I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.
I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.
What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.
An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.
No logs created, this is all I have: Â Unhandled Exception: System.Security.SecruityException: Request failed. Â Â Â Â Â Â Â Â at Microsoft.SqlServer.Chainer.Setup.Setup.DebugBreak() Â Â Â Â Â Â Â Â at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
Then the Managed SQL Server Installer box pops up with Managed SQL Server Installer has stopped working.
"Windows could not start the SQL Server (RETAIL) service on local computer."
"Error 1053: The service did not respond to the start or control request in a timely fashion."
I am trying to start my SQL Server service and I get the above error. I am using Windows 7 32 bit. SQL 2008 R2 32bit. SQL has ran successfully for 2 years on this machine until a power outage the other night. Now I have this happening on two computers.
I have read every event viewer message up and down and searched Google for hours trying to find a solution. I have done so many things from changing dll's to reinstalling sql 2008 to trying sql 2012.
I uninstalled SQL2008 and installed SQL2012 and it had the exact same error message. It wont start that one process. I have read this forum and I see many instances of this issue being reported but none of them have been recent. I attempted to do everything the previous posts said to try and nothing has worked. I have tried to roll back using windows restore point, I have tried to change the servicespipetimeline registry edit in the HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControl. I have changed permissions.
I am trying to deploy a shape file map of the world with 100 random data points from my dataset but get this error: "exception running the extensions specified in the config file. ---> Maximum request length exceeded". I can preview this fine in SSDT. I have read various solutions which involve editing the config file but I don't have permissions to get to these files. Is there any way to amend the maximum length either through SSMS or SSDT? If not how can I make my map size small enough to deploy? It is a very simple shape file with no colours etc and I am passing random 100 data points to it from my dataset.
I have smilar kind of an issue with replication. I had sucessfully done replication with Sqlce2.0 on sql server 2000. In development machine I had VS 2003, sqlce development tools.
Now I use the same code done in VS 2003 into VS2005, it sucessfully converted the code.
In server machine (win 2003) I installed sql server 2000, sql server 2000 service pack 4, SP4 merge replication components, sql server 2005 mobile server tools and .Net framework 2.0. Created pubication sucessfuly. In this machine I configured IIS as per the web synchronization wizard. Gave anyonymous permissions. Virual direcory is created sucessfully. I tested the server agent in IE http://myservername/aliasname/sqlcesa30.dll.
SQL Server Mobile Server Agent 3.0 is shown
My application opened successfuly in windows mobile 5.0 Pocket PC emulator. When try to sync its dislays error "A request to send data to the computer running IIS is failed, see Hrresult"
No firewall in both development and server machines.
The same code works if its sqlce20 perfectly with vs2003 code. Not in this
We have installed SQL Server 2005 with reporting services.When tried to open getting errors as below: how to avoid such errors See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. ---> System.Runtime.InteropServices.COMException (0x8000000A) Â at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) Â [code]...
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
I got this message from sql "Server was unable to process request. ---> A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) " Does anyone know what happen? Thanks
I need to step into a SP on my SQL server. However, I get "Security hasn't been setup correctly on server RADU. SQL debugging terminated."
I read the docs, and I did exactly this (as per the help files):
--------------------------------------------------------------------------------------------------- To configure DCOM on the server for SQL debugging
You may need to download and install DCOM first. If so, go to http://www.microsoft.com/com/resources/downloads.asp for the latest version.
You must have administrator privileges on the machine where you are configuring DCOM.
From the Start menu, choose Run. In the Open box, type dcomcnfg.exe. Click OK. The Component Services dialog box appears.
In the left pane of the Component Services dialog box, expand the Component Services node and find Computers. Expand the Computers node and find the computer for which you want to configure DCOM. Right-click the computer and choose Properties from the shortcut menu. In the Properties dialog box, select the Default COM Security tab. Under Access Permissions, click Edit Default. The Access Permission dialog box appears.
If the group or user who needs access does not appear in the list of permissions, click Add. The Users, Computers, or Groups dialog box appears.
In the Enter the object names to select box, type the name of the group or user who needs access. Click OK. The user is now added to the list.
If SYSTEM does not appear in the list of permissions, click Add. The Users, Computers, or Groups dialog box appears.
In the Enter the object names to select box, type: SYSTEM Click OK. SYSTEM is now added to the list.
In the Access Permission, select Allow for each user you added. Click OK to close the Properties dialog box. If you changed any settings in this procedure, restart SQL Server. ---------------------------------------------------------------------------------------------------
However, from inside VSnet IDE. I get the same message ("Security hasn't been setup correctly blah-blah-blah").
I have tried with users 'ASPNet' and 'VSDevelopers'. Which user should I choose ? Is there something else I should do ?
Sure, this is not the end of the world, I can still do the debug from within Query Analyzer, but still, it would be nice not to have to do this.
Does anyone use SQL debugging? I write pocedures in SQL server useing Developper studio, but i do not find any means to debug stored procedures. Though i can place a break point to source code of sp_procedure, code is runned a whole. Is this means only for C++? Can anyone help me? I have very large procedures and someteimes it is hard to find an error.
if(acr_application.appStatus = 'AOO' then acr_application.appDt, if(acr_application.appStatus = 'A01' then acr_application.recvdDt, if(acr_application.appStatus = 'A02' then (select A.payDt from acr_payment A, acr_reference_no B where A.refNo = B.acrReferenceNoId and B.refType = 'ACF' and B.appNo = acr_application.appNo), if(acr_application.appStatus = ('A03', 'A08'), (select C.updatedBy from acr_insp_sched C where C.appNo = acr_application.appNo and C.seqNo = (select max (D.seqNo) from acr_insp_sched D where D.appNo = C.appNo)), if (acr_application.appStatus = ('A04', 'A05', 'A06', 'A15'), (select E.updatedBy from acr_inspection E where E.appNo = acr_application.appNo and E.seqNo = (select max (F.seqNo) from acr_inspection F where F.appNo = E.appNo)), if(acr_application.appStatus = 'A07', (select G.payDt from acr_payment G, acr_reference_no H where G.refNo = H.refId and H.refType = 'REI' and H.appNo = acr_application.appNo), if(acr_application.appStatus = ('A10', 'A11'), acr_application.approvedDt, if(acr_application.appStatus = 'A13', acr_application.updatedBy, if(acr_application.appStatus = 'A14', (select H.payDt from acr_payment H, acr_reference_no J where H.refNo = J.refId and J.refType = 'CSB' and J.appNo = acr_application.appNo), null)))))))))
In SQLServer 2000 I could easily debug a stored procedure from QA. But I dont know how to debug a sp in SQLServer 2005. I could find nothing to deal with it in SQL Server Management Studio. Can anybody tell me how to do it?
select acr_application.appNo as 'applicationNo', acr_application.facilityName as 'nameOf DrugTesting', acr_application.adr as 'address', acr_reference_no.refNo as 'referenceNo', ref_charge.chargeDesc as 'charges/fee', acr_reference_no.amount as 'amount',
ref_settings.earlyFileDiscnt as 'pdiscount', ((ref_settings.earlyFileDiscnt / 100) * acr_reference_no.amount) as 'discount',
case when acr_reference_no.earlyFileDiscntDt is not null then acr_reference_no.earlyFileDiscntDt else case when '2007-09-29' <= ref_facility.validToDt then date_sub(ref_facility.validToDt, interval (ref_settings.earlyFileDiscntValid + 1) day) end end as 'discountValidity',
ref_settings.lateFileDiscnt as 'psurcharge', ((ref_settings.lateFileDiscnt / 100) * acr_reference_no.amount) as 'surcharge',
case when acr_reference_no.lateFileSurchargeDt is not null then acr_reference_no.lateFileSurchargeDt else case when '2007-09-29' <= ref_facility.validToDt then ref_facility.validToDt, select a.validTodt from acr_application a where a.appNo = (select max(b.appNo) from acr_application b where b.appNo < acr_application.appNo) end end end as 'surchargeValidity',
'0012 2222 20' as 'accountNumber', concat('DOH-',acr_reference_no.refNo, '','-85619') as 'accountName',
concat(if(acr_application.ownerTitle is null or acr_application.ownerTitle='','', concat(acr_application.ownerTitle, '')), acr_application.ownerFname, ' ', if(acr_application.ownerMname is null or acr_application.ownerMname='','', concat(substr(acr_application.ownerMname,1,1),'.', ' ')), acr_application.ownerLname, '', '', if(acr_application.ownerSuffix is null or acr_application.ownerSuffix='','', concat(' ', acr_application.ownerSuffix))) as 'nameOfRepresentative'
case when acr_application.appType = 'INA' then acr_reference_no.amount else case when acr_application.appType = 'RNW' then (if(acr_reference_no.earlyFileDiscnt is not null and acr_reference_no.earlyFileDiscnt <='2007-09-29', acr_reference_no.amount + (acr_reference_no.amount * (ref_settings.earlyFileDiscnt / 100)))) else case when acr_reference_no.earlyFileDiscntDt is not null and acr_reference.earlyFileDiscntDt > '2007-09-29' and '2007-09-29' <= ref_facility.validToFt then acr_reference_no.amount, acr_reference_no.amount + (acr_reference_no.amount * (ref_settings.earlyFileDiscnt / 100)) end end end as 'totalCashDeposit',
case when acr_application.appType = 'INA' then date_add(acr_applicationappDt, interval ref_settings.validityAfterAppDayy) else case when acr_application.appType = 'RNW' then acr_reference_no.earlyFileDiscntDt else case when acr_reference_no.earlyFileDiscntDt is not null and acr_reference_no.earlyFileDiscntDt > '2007-09-29' and '2007-09-29' <= ref_facility.validToDt then ref_facility.validToDt, date_add(ref_facility.validToDt, interval ref_settings.validityAfterAppDay) end end end as 'bankValidity'
from acr_application left join ref_facility on acr_application.appNo = ref_facility.appNo left join ref_settings on ref_settings.service = acr_application.service and ref_settings.ownType = acr_application.ownType and ref_settings.facilityType = acr_application.facilityType and ref_settings.institutChar = acr_application.institutChar left join (acr_reference_no left join ref_charge on acr_reference_no.chargeId = ref_charge.chargeId and acr_reference_no.paid <> 'Y') on acr_application.appNo = acr_reference_no.appNo
My colleagues and I have not been able to successfully set up SQL debugging on a SQL 2000 instance running on Windows 2000.Is there a checklist posted somewhere? We have verified that the SQL Server is not running with System Account Credentials. It runs as a Domain Administrator. We have also verified that everyone has permission to run sp_sdidebug. Does it matter that the Win2K machine is a Domain Controller? Any help would be appreciated.
I am new to SSIS but not new to ETL. Is there a way in debugger to debug but not actually update the target table ? In other tools I€™ve used, there was an option setting to not allow update/inserts into the actual table, but would provide a visual dataset so you could see & test with. I haven€™t been able to find this feature in SSIS & was wondering if there is a way to do this.