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

INSERT Does Not Throw Error, What Do I Need To Do

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

Conditional Page Throw

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

Incorrect Syntax Near Throw

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

How Can I Throw A Custom Error Code?

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

Decimal.MinValue Throw OverflowException

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

SQL Server Throw Timeout Exception

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

Export To Excel Throw WrapperReportRenderingException

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

SQL Server 2014 :: Capture THROW For Logging

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

Missing Dataflow Task? ( Throw Execption)

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

Encrypt Dara Throw A Stored Procedure

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

Enable Package Configuration THrow Error

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

SQL Server 2012 :: Use Unique Custom Error Numbers For THROW Statements?

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

DTSX Package Continues To Throw Errors When Working With Large Dataset.

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

SecurityException Class SQLServerDatabaseMetaData's Signer Information Does Not Match Signer Information Of Other

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

Tricky SQL - Need Help

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

Tricky SQL

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

Tricky SQL!

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

Help With Tricky T-SQL

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

Here's A Tricky One

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

Tricky SELECT

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

Need To Do Some Tricky SQL Sorting

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

Tricky Query??

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

Tricky SQL Statement (for Me)

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

Tricky SQL Statement

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

Tricky Delete

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

Tricky Sql Query...

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

Help - With A Tricky Select, Pls

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

Tricky Query

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

Tricky Query Need Help

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

Tricky SQL Query

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

Tricky Query

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

Tricky Query

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







Copyrights 2005-15 www.BigResource.com, All rights reserved