I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours @DayName varchar,@DayIDName varchar AS BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID END RETURN ...is this possible?? if so how? Thanks
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
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
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 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.
Hi there gurus, can you please add your 2 cents on this design? We'rehaving trouble relating these tables in a diagram because of the keys.Is it necesary to have the references setup? I would assume yes so theforign keys can be setup.If you look at this link, you'll see our diagram. In Red are therelationships that we would like to make for referential integrity, butcannot because of the keys.http://rullo.ca/linktome/QuestionsDB.jpgOur goal in all of this is to have a facility wherin we can store aquestion, that has multiple names over multiple Languages. Forinstance:-Q1| QNameID = 1 | "Do you have a dog in your appartment?" | LangID =1(eng)-Q1| QNameID = 2 | "Do you have a dog in your house?" | LangID =1(eng)-Q1| QNameID = 1 | "-French - Do you have a chien in your appartment?"| LangID = 2(fr)-Q1| QNameID = 2 | "-French - Do you have a chien in your house?" |LangID = 2(fr)The difficulty is when we try and put this in the group details table.We don't want to outline the Language, we'd just pass the language intoa proc to retreive a specific group with a specific language. If youfolks would be so kind as to add your comments to the design I would betruely grateful.CREATE TABLE [Question] ([QuestionID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED([QuestionID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionAnswer] ([QuestionID] [int] NOT NULL ,[QuestionAnswerID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY CLUSTERED([QuestionID],[QuestionAnswerID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GOCREATE TABLE [QuestionAnswerName] ([QuestionAnswerID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,CONSTRAINT [PK_QuestionAnswerName] PRIMARY KEY CLUSTERED([QuestionAnswerID],[QuestionAnswerNameID],[LanguageID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroup] ([QuestionGroupID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,CONSTRAINT [PK_QuestionGroup] PRIMARY KEY CLUSTERED([QuestionGroupID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroupDetails] ([QuestionGroupID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[QuestionSortOrder] [int] NULL ,[AnswerSortOrder] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[DisplayLevel] [int] NULL ,CONSTRAINT [PK_QuestionGroupDetails] PRIMARY KEY CLUSTERED([QuestionGroupID],[QuestionNameID],[QuestionAnswerNameID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionGroupDetails_QuestionGroup1] FOREIGN KEY([QuestionGroupID]) REFERENCES [QuestionGroup] ([QuestionGroupID])) ON [PRIMARY]GOCREATE TABLE [QuestionNames] ([QuestionID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Desciption] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ControlTypeID] [uniqueidentifier] NOT NULL ,CONSTRAINT [PK_QuestionNames] PRIMARY KEY CLUSTERED([QuestionID],[QuestionNameID],[LanguageID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionNames_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GO
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 have a query to insert records into a table based on a request but the query can only read one record at a time. How do i change the query such that it is able to read multiple records. In the below query i was able to input only 1 request which is 149906.
Query
declare @num_of_times int declare @Count INT DECLARE @newrequestid varchar(50) DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INT SET @RequestId = 149906 SET @x = 1
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.
I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?
Code Snippet
-- Bug report
-- 2007/07/19
-- Alasdair Cunningham-Smith
-- alasdair at acs-solutions dot co dot uk
set nocount on
go
-- example date in in British date format
set dateformat dmy
go
use tempdb
go
create table foo( bar varchar( 30 ) not null )
go
insert into foo( bar ) values ( 'fishy' )
insert into foo( bar ) values ( '19/07/2007' )
go
-- this works fine in all versions - only valid dates are passed to the convert function
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
go
-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):
-- Msg 295, Level 16, State 3, Line 2
-- Conversion failed when converting character string to smalldatetime data type.
--
-- I believe the query is rewritten as if the derived table query contained
-- "and convert( smalldatetime, bar, 103 ) < getdate()"
-- which would expose the convert to the invalid data
select
*
from
(
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
-- Workaround:
-- Use a case statement to protect the convert operator from the invalid data
select
*
from
(
select
case when bar like '__/__/____' then
convert( smalldatetime, bar, 103 )
else
null
end as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
drop table foo
go
The workaround I discovered is simple but ugly. I invite your comments...
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.
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.
1. We have user defined type (Sql type = bit ) with rule setting it to 0 2. New column(isVIP) was created using this user defined type 3. sp_unbindrule was used to unbind rule from new column (isVIP) 4. Alter table Client drop Column isVIP !!!! (invalid syntax )
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have 2 database table3 as follows: eg 1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2 2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls
Using the myGameRecencyAllBalls table --- I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).
I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.
I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1
however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?
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.
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
I'm importing data from Navision 3.70A Database (not MS SQL Server) with SSIS and data reader via odbc.
Works perfect until I try to import a table which has a column including cells with | (pipe symbol) and .. (dots) between numbers.
8420|8421|8430|8431
8900..8944
the error message from data reader: [get sachkonto [5165]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "get sachkonto" (5165)" failed because error code 0x80004003 occurred, and the error row disposition on "output column "Zusammenzählung" (8265)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
No error message exist before this message.
Is there an option to allow or a workaround? Maybe a replace function in the sql command can help but I have no sql manual for the native database of navision. Can somebody help with an example of a query?
I can read this table with Excel via ODBC without problems ...
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr] for insert as begin declare @liter as decimal(28,6)
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
Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases. I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value: Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = ['+@idpartition+'] AND StoreIdentifier IS NULL') Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'. If I change the last line to: WHERE [IdPartition] = 2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated. Best regards, Tim
