Rather Tricky Question - How Throw Information From .....
Nov 17, 2000
Hi everybody,
I have a good question. If anybody know help me.
Every body know that SQL Server has SQL SERVER LOG 1- Current and 6 - Archives
I'm shure every DBA look inside every day. And we can note inside a lot of many adds looks like this one:
2000-09-28 10:23:42.85 spid10 Starting up database 'pubs'.
So, we can see that 'spid10' had some activity at that day, but
HOW TO GET INFORMATION about WHO IS IT - Login name or Username at least.
Any idea will be appreciated
I am using SQL Server 2005 Express and one of the fields in my table has the UNIQUE constraint. When I try inserting a duplicate nothing gets inserted in the table, but no error is thrown. Where can I do a check for this? I would like to popup a message to the user indicating that the insert failed to the the unique constraint. I have configured the SQLDataSource with Insert/Update/Delete so the actual insert is done behind the scenes. Thanks in advanceEric
I'm looking for help on how to throw a page so that when printing duplex (both sides) a group will start on an odd page number eg 1, 3, 7, 9 etc. My large report needs to be split up into departments. I don't want a department starting on the reverse side of a sheet of paper.
When I execute the SQL Script in SQL Server Management Studio, everything is just fine. Â I have included this SP in a VS 2012 Database Project, and when I build, I get the error 'Incorrect Syntax near Throw'. Â The Database project is set to SQL 2012. Â I did try putting a ';' in front of the THROW as has been suggested on other threads. is this a case where we will have to abandon use of VS 2012 Database integration?
I'm trying to run a DTS package, and cant find an easy direct way to run it. It seems like the easiest solution would be to throw a custom error- then the server notices the error and runs a custom job, and the custom job runs the DTS. This is a roundabout way, but seems like it would be the simplest solution.Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement? Also maybe this is a very bad way to do this- any suggestions?
I'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure. But, I receive this stack trace exception: System.OverflowException: Conversion overflows. at System.Data.SqlTypes.SqlDecimal.ToDecimal() at System.Data.SqlTypes.SqlDecimal.get_Value() at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) 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() I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?
This is my code connect to SQL Server SqlConnection con = new SqlConnection("Data Source=OIT;Initial Catalog=big_db;User ID=sa; Password="); SqlDataAdapter cmd = new SqlDataAdapter("select * from myDB", con); SqlCommand sqlCmd = new SqlCommand(); DataTable dt = new DataTable(); cmd.Fill(dt); // It throw exception When myDB table have a lot of data, it throw exception like this : "It reached the time-out. Did the time-out period pass before completing the operation or the server doesn't respond. ". I config TCP/IP for SQL Server is Enable, but it throws SqlException too. How can I do? Help me please!!!
I have a report runs without any problem (Url access) and export to other format also without any problem. Only when I export the same report to Excel, the following error occurs in the reportserver log and its throws an error on screen.
I have found out it is down to a table grouping on a data field.
The report conatins a list and the list has a details group, which grouping the data by week. A table is sit inside the list to report the data. Within the table, there is a group list which is grouped by a field from the dataset.
I have the actual RDL and RDL.data file if anyone like the challenge.
Here is the error from the report server log:
--------------------------------------------------------------------------------------------------------------------------------------------- aspnet_wp!library!1!01/11/2008-14:54:49:: i INFO: Call to RenderNext('/ExportExcelProblemReport' ) aspnet_wp!reportrendering!1!01/11/2008-14:54:50:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ; Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. at Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32 index) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows header, TableHeaderFooterRows footer, TableGroupCollection tableGroups, TableRowsCollection detailRows, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList indexPath, PageReportItems& pageRI) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout, PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable& rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates, OutlineRenderStates[]& horizontalOutlineStates) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32 currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack& renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout& lastPageLayout, Action action) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) --- End of inner exception stack trace --- aspnet_wp!webserver!1!01/11/2008-14:54:50:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. at Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32 index) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows header, TableHeaderFooterRows footer, TableGroupCollection tableGroups, TableRowsCollection detailRows, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList indexPath, PageReportItems& pageRI) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout, PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable& rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates, OutlineRenderStates[]& horizontalOutlineStates) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32 currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack& renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout& lastPageLayout, Action action) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) --- End of inner exception stack trace --- at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) at Microsoft.ReportingServices.Library.RenderSnapshotAction.Render() at Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext reportContext, ClientRequest session, RenderingResult& result) at Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effecectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() at Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent() at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Maybe I am mistaken ( most likely ). But I am missing a dataflow task, which would do something similar like Throw Execption.
The project I am currently working on needs to validate a lot of different data. And sometimes incorrect data ( corrupted, incomplete or unexepected ) is coming from the source system. In case of this I need to trigger an error and discard the complete row or batch depending on the situation.
For example: In our source system we have some flag fields. And certain combinations of flags are not logical (business rules) but the system allows data to be inputted in this unlogical ways. And because we are creating the system I am expecting some combinations flags which are allowed but not properly defined in the specification.
So when I use a conditional split, I want the default output to trigger an error instead of an output.
I am using Data flow object in that I take OleDb as Source and Sql Server As destination. Without enabling Package configuration everything is working fine data is moving from Source to destination.
But When i apply Package Confiiguration It throw Error And My OleDb Source object Convert in to RED Color. I cant able to track This errror
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
I have a dataset that is between 40-50K records that has to go through a process that is pre-defined. SSIS works just fine with the smaller sets even up to 20K but this job keeps blowing up saying something along the lines of cannot write to recordset destination. Does this make sense to anyone? The sever is a 2 processor with 2GB of ram. Physical memory usage spikes to about 1.6GB during the run but the processor never really gets above 30% usage. Does this product just not scale yet?
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g. java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Hello, I have four diffrent tabels: bo_ Class, bo_Competition, bo_Result, bo_Licence And list all Licence where bo_Class.classRankingNbr >0
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale ------------ --------- --------------- ------------- ----------- --------------- ---------- M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H M180360GER01 Gert Lindholm Herrar klass A 1 2 H M041062MIC01 Micael Hamberg Herrar klass A 2 2 H K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D
If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition, and if an ClassRowNbr is 2 get out top2 from that competition. And so on.
From that list i want an SubQuery list where all licens order by where bo_Result.ResultPlace = bo_Class.ClassRowNbr.
In text form:
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale ------------ --------- --------------- ------------- ----------- --------------- ---------------------- M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H M180360GER01 Gert Lindholm Herrar klass A 1 2 H M041062MIC01 Micael Hamberg Herrar klass A 2 2 H K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta FROM Resultat r INNER JOIN Medlemmar m ON r.Spelare = m.ID WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang FROM Resultat ORDER BY Omgang DESC)) GROUP BY m.Namn + ' ' + m.ENamn ORDER BY 4 DESC
What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL
Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat Resultat r INNER JOIN medlemmar m ON r.Spelare = m.ID" Get lowest result same on all players.
Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny
I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database. Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.
Now, the problem is that there are some views that pull this numbers directly from the database and display it.
My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it. I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
I have a stored procedure called TC3_GetAllJobOrders which takes 8 parameters as filter values and dynamically builds a statement to filter the data. If you pass in null values for the filters, then the data is not filtered.
I want to write another SP called TC3_GetNewestJobOrders which takes 9 parameters. The first 8 parameters are the same as TC3_GetAllJobOrders and the 9th parameter is numRecs which defines the number of records to return. The procedure should call TC3_GetAllJobOrders, sort the data by date and then return the top numRecs. However, I can't figure out the best way to write this stored procedure because it references another one.
I am trying to stay away from dynamic SQL if at all possible. But I am thinking I will have to use dynamic SQL because I don't think the number of records to be returned (as defined using the TOP keyword) can be parameterized. However, I was trying to write a dynamic SQL statement so that the end statement looked something like:
SELECT TOP 10 * FROM ( EXECUTE TC3_GetAllJobOrders ... ) ORDER BY createdOn DESC
However, I guess having the EXECUTE in parens like that is no good and SQL Server doesn't like it. What is the best/correct way to do it?
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
I need to have a third selector, I have used the following code :
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod3)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
This is giving me to many answers, does anyone have any good suggestions to improve it?
I have 2 tables which are related to each other, each having a Foreign Keys of the other table . When I delete company table, it gives me an error that I'm violating a FK constraint of the table owner. When I try to delete employee, it gives the same error.
CREATE TABLE OWNER { employee_id PK company_id - this is a FK of COMPANY }
CREATE TABLE COMPANY{ company_id PK owner_id - this is a FK of OWNER }
Must I drop the constraints before I can delete? I don't want to do that because I don't want so many other tables are dependent on those tables.
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.
I have a feeling it is very easy to do what I want. But I don't know how. I have 2 queries that return 2 results sets. I'd like to have just 1 query that returns 1 result set that contains all the data of the 2 results sets.
Query 2 returns Item Actual ------------------- Lion 1 Bear 1
What I want is 1 query that will combine the results Item Expected Actual ------------------------------- Lion 2 1 Tiger 2 0 Bear 2 1
I tried using a unions between the 2 queries but that doesn't work. I am pulling my hair out. I have been struggling with this for several days now. Any help would be greatly appreciated.
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I need to return a number of records at specifik days, i do it with this query;
SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In FROM tb_SecurityLog WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (SL_PolicyName LIKE N'%') GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?
One of the reports the user needs to see looks like this:
ProductNumber of MachinesTotal Sales Magazine/Drink2$120.00 Drink2$70.00 Magazine1$30.00 Food/Drink1$70.00
To clarify:
There are two magazine/drink machines (ID 1 and 3) There are two drink only machines (ID 2 and 6) There is one magazine only machine (ID 4) There is one food and drink machine (ID 5)
How do I do this query?
Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.
I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.
