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
Dmitri (DBA)
View 2 Replies
ADVERTISEMENT
Feb 5, 2008
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
View 2 Replies
View Related
Mar 30, 2007
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.
Your help would be appreciated.
View 1 Replies
View Related
Aug 12, 2013
I have what looks to be a very simple Stored Procedure as follows:
CREATE PROCEDURE SOE_OFMUpdateStatus_v1 Â
@sOrderNumber
numeric(7,0),
@sOrderType
char,
@sSupCode
char,
@sOrderStatus
varchar(15),
[code]...
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?
View 9 Replies
View Related
Jun 26, 2006
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?
View 2 Replies
View Related
Apr 19, 2007
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?
View 2 Replies
View Related
Jun 5, 2007
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!!!
View 2 Replies
View Related
Jan 23, 2008
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()
---------------------------------------------------------------------------------------------------------------------------------------------
Thanks in advance !
Regards,
pysw
View 11 Replies
View Related
Aug 14, 2015
Is it possible to capture the contents of THROW within a CATCH into a variable so that it can be written to a logging table?
I can capture ERROR_MESSAGE() to a variable but unfortunately it only writes the last error rather than the previous one that is informational.
I realize that THROW can be multiple lines so it may not be possible to do through TSQL but thought I should ask.
View 2 Replies
View Related
Dec 27, 2007
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.
View 10 Replies
View Related
Apr 4, 2007
Hi,
I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well
Code Snippet
CREATE PROCEDURE [dbo].[UpdateUser]
@CardNumber nvarchar(max),
@UserID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SecretData varbinary(max)
OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY
DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')
SET @SecretData = encryptbykey( @KeyGuid, @CardNumber)
UPDATE User
SET
CardNumber=@SecretData
Where UserID=@UserID
CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY
END
but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.
Code Snippet
CREATE PROCEDURE [dbo].[UpdateUser]
@CardNumber nvarchar(max),
@UserID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SecretData varbinary(max)
exec EncryptData @CardNumber, @SecretData output
UPDATE User
SET
CardNumber=@SecretData
Where UserID=@UserID
END
Code Snippet
CREATE PROCEDURE [EncryptData]
@ClearData varchar(max),
@SecretData varbinary(max) output
WITH EXECUTE AS 'DBO'
AS
BEGIN
OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY
DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')
SET @SecretData = encryptbykey( @KeyGuid, @ClearData)
CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY
END
Any Idea how to fix this issue
Thanks in advance.
View 3 Replies
View Related
Nov 15, 2007
Hi please Help Me Out.
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
Please Help me Out .
Thanks
Sandeep
View 3 Replies
View Related
Apr 20, 2015
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?
View 2 Replies
View Related
Jun 7, 2006
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?
View 1 Replies
View Related
May 23, 2007
Hello,
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.
Can anyone help me with this problem?
Simon
View 4 Replies
View Related
Nov 8, 2004
I have 2 tables joined together by the IDs, People and the pets they
own
PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe
PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish
I have create another where the PETS are in one column separated by
semi-colons and removing the dups
NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish
What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists
THanks!
View 1 Replies
View Related
Mar 18, 2008
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
Hope someone can take the time and help me out.
Best regards
Gert Lindholm
View 6 Replies
View Related
Oct 18, 2005
One statistic questiong from an db Resultat.
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.
Best reg
Gerten
View 2 Replies
View Related
Feb 7, 2006
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
View 5 Replies
View Related
Jan 28, 2008
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?
Thanks.
View 4 Replies
View Related
Jul 18, 2007
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.
View 14 Replies
View Related
Dec 13, 2007
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).
View 2 Replies
View Related
Mar 21, 2004
I have a table as follows:
Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)
Each team plays alternately at home then away throughout the course of a season.
I want perform 2 seperate queries on this table.
Query 1:
I want to select a particular teams opposition for the entire season.
Query 2:
I want to select a particular teams opposition for a particular weekNumber.
Thanks
View 3 Replies
View Related
Oct 6, 2004
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?
View 4 Replies
View Related
Mar 10, 2005
Hello,
I have a SQL statement which is working OK:
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?
View 1 Replies
View Related
Mar 7, 2000
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.
Joyce
View 2 Replies
View Related
Feb 9, 2001
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
please help...
View 3 Replies
View Related
May 13, 2003
Hi, I'm tring to break my head finding a solution of how to return the following result:
I have the source table -> S_TAB with some data like
COL1 COL2 COL3
ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......
what I need is come up with a single select statement (a view) to get the following output:
select <something> as RID, COL1, COL2, COL3 from S_TAB
RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....
Any Idea will be appreciated
Dim
View 2 Replies
View Related
Aug 31, 1999
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.
View 1 Replies
View Related
Aug 29, 2001
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.
Example.
Query 1 returns
Item Expected
--------------------
Lion 2
Tiger 2
Bear 2
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.
Thanks
Josh
View 3 Replies
View Related
Feb 9, 2001
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
please help...
View 2 Replies
View Related
Nov 16, 2005
Hi,
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?
//Mr
View 6 Replies
View Related
Feb 22, 2007
I have a data table like this:
MachineIDProductSales
-------------------------------
1Magazine$20.00
1Drink$30.00
2Drink$30.00
3Magazine$30.00
3Drink$40.00
4Magazine$30.00
5Food$40.00
5Drink$30.00
6Drink$40.00
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.
Here is some setup T-SQL code:
CREATE TABLE SalesData
(
MachineIDINTEGER,
ProductNameVARCHAR(50),
SalesMONEY
)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)
Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!
View 5 Replies
View Related