I have a Transaction replication running between 2 SQl 2000 Servers. The distributor is on seperate machine.
Server1 is an online Db which is sending replicated trn to Server2 which is a reporting Server. Because of space issues, I have to remove about 100 Million Rows from Server1 Table without affectiing Server2 Table (Server 2 Retains those rows)
Is there any Stored Proc available to disable the replication only during that time ?
Thanks
Hi, all..
I by mistake Overwrites distribution DB.
Server1 and Server2 replicates each other.
Applications are using Server1, and Server2 is back up purpose.
When server1 fails application redirected SErver2, using same data since there is replication.
Server2 had distribution DB.
Now there is no distribution DB.. it's messed up...
I want to restore replication between Server1 and Server2.
I tried to make replication from Server1, I got errors..
How can I do this???
Hey all
I've got a DTS package with a script that does this:
Set xlApp = CreateObject("Excel.Application")
This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??
Thanks! I gotta get this solved within 5 hrs :(
Hi,
I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?
Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007
Any help will be appreciated.
Hi everyone -
I am relatively new to this and trying to make my database as efficient as possible.
Here is the situation. In my database, there are multiple "stocks" that each have a "pricing history". When something happens that changes the price of the stock, an entry containing information about the time the change occured, who changed it, etc. is added.
I have many stocks. The information of these stocks is independant in the sense that I never really need to grab/compare information about two stocks at once. I only look at one stock at a time.
So, do I set up a "pricing history" table for every single stock (pricinghist1,pricinghist2,etc.)? Or do I set up one "pricing history" table it and index the table by stock number?
Are these equivilant? Is one more efficient than the other?
Thanks!
N
Here is the basic situation.
(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.
The way I wanted to approach this situation is as follows.
Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.
The concept is simple, but I'm not sure how to implement this.
For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.
Lastly, If you have better approach, I would like to hear about it.
Thank you.
Hello All
I have a table called Tax Act
In that I have Tax Act ID. Which is supposed to be NOT NULL.
I have to create IDs by my self using some MAX and MAX+1 incrementing function.
Please guide me on how to
Hello everyone,
I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.
The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.
To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:
Person.Base
Id (uniqueidentifier)
UniqueIdTypeId (uniqueidentifier)
UniqueId (uniqueidentifier)
Password (char(88))
PasswordSalt (char(10))
PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]
PasswordRecoveryAnswer (char(88)) [NULLABLE]
CreationDate (datetime)
AuthenticationWindowStartDate (datetime)
AuthenticationWindowAttemptCount (tinyint)
IsActive (bit)
IsBanned (bit)
IsLocked (bit)
Status (bit)
Person.Emails
PersonId (uniqueidentifier)
EmailAddress (nvarchar(256))
CreationDate (datetime)
ValidationCode (char(10))
ValidationDate (datetime) [NULLABLE]
IsValid (bit)
IsPrimary (bit)
IsRollbackTarget (bit)
Status (bit)
So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.
In order to meet this business requierment I would create some addicional tables. Let's get started:
Person.CustomFields
Id (uniqueidentifier)
Name (nvarchar(50))
Description (nvarchar(3000)) [NULLABLE]
Status (bit)
Person.CustomField_Value
FieldId (uniqueidentifier)
Value (nvarchar(450))
This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.
How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.
Best regards and thanks in advance.
Hi, I have the following tables:
Categories {Category_ID, Column2, ...}
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.
Regards,
I need help on reproducing a deadlock. The sample that I have so far is only creating blocking and it never deadlocks. It's because I'm not sure what order the system tables are being help when creating temp tables in a transaction, therefore, I can't tell which pages need to be requested from another transaction to cause the deadlock. Any help will be much appreciated.
Thanks,
Mike
We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.
The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.
So... here's my question:
After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!
Thanks!
whill96205 the Noob :confused:
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table
Cananyone help me with this query please
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
View 2 Replies View RelatedHello,I have a rather large table in MS SQL 2000 that I'm writing reports inCrystal from, but I'm unsure how to get the various data files (fromdifferent departments) into the table. Below is a small chunk of thetable:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefitsI'm getting data from multiple places, but as I import the data, Iwant it to populate the table with DeptCode and Type being the primarykeys.If someone sends me the following file:DeptCode;Type;AveMonthVolume;WorkedFTE1000;Budget;100;2001010;Target;233;433And I get the following file from another source:DeptCode;Type;TotHours;Benefits1000;Budget;433;4001010;Target;33;43I want a simple way to import all this into the table so it looks likethis:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits1000;Budget;100;200;433;4001010;Target;233;433;33;43The data will be coming in delimited text and in Excel format. Iassume I can import his into MS SQL Enterprise Manager directly, but Ican't find any simple way of running in update query to import mydata. Also, I'd hate to use MS Access as the middle-man if at allpossible... but if this is the only option, I'll do it.Does anyone have other suggestions? Above is only a small example...the table has about 25 columns with various data I'll be collectingfrom 3-4 departments, so it's a hodge podge of data to combine intoone report.Thanks for any suggestions or ideas in solving this. Thanks inadvance...Alex.
View 2 Replies View RelatedI am here in a peculiar situtation i am not very sure the problem which i am facing as of know is a Database or Network.
One of our organisation application is connected to SQLServer 2005 (clustered) database was working fine before few days back we gone though a windows 2003 server patch update activity and server was restarted and a failover occured.
The day patch activity is done we are facing data loss problem over the network.
Checked the SQLServer2005 ErrorLog but no error related to database.
Few user are facing problem as mentioned below
Error : Checknetwork Documentation
Search the net but no satisfactory answer.
Please help me with a resolution or work around.
One more Issue :
While pinging the server some time we get request Time out.This Request time out is for 1 ms.
Do this also effects the thick client application connectivity with the Database.
regards
Sufian
So we have Jobs that are created via web portal and stored in the database.
There is a "Job Processor" webservice that currently polls the database to find out if there are any new jobs to process.
Ideally I would like to place these Jobs in a queue and have the queue fire a message to the Job Processor indicating that there is work to be done.
Thoughts?
hello,
I recently transfered date into my sql server source table from Access data base but at that time one column was blank. ID and mainID so mainID was missing
Now i received and excell sheet. in which again i have ID and mainID both provided .
please let me knwo how do i update those mainID into the sqlserver cource table column using available ID.
thank youi
Is there a way to connect to two servers located at remote location in ssis.
Here is what i want to achieve..
I Have created a package which will load data from a text file on one sql server1.
What i want to do is after data is loaded i want to connect to different server on the remote location and then i want to check to compare the data from server 1 with the existing data in server 2.If the data in server 1 has new records then it should insert that data in to table in server 2.else update.
Servers are located remote and are under two different networks.
Can this be achieved using ssis or how can i implement this.
In SQL 2005 I have a stored procedure as below:@sub_no smallint OUTPUTBEGINBEGIN TRANSACTIONINSERT...INTOSET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)INSERT...INTOEXE another_stored_procedure (it includes also BEGIN...COMMIT)EXE another_stored_procedure (it includes also BEGIN...COMMIT)SET @sub_no = .......COMMIT TRANSACTIONWhen Visual Studio (ASP.NET 2005) is open and I run the program,procedure is executed once without any problem. If I publish theproject and put files on another server (or even use the publishedfiles from my machine) I have an error because stored procedure isexecuted twice. @sub_no is used as input/output parameter.I followed/trace the steps in procedure and it seems that procedure isexecuted once with correct value of @sub_no. The second time procedureis executed, the value that it was assigned before COMMIT is used,which gives an error because the INSERT values have NULL values.In ASP.NET I call the store procedure once.What could be the reason ?Thanks a lot for any help.
View 2 Replies View Relatedhello,
I have to write a batch file program that will just copy the names of the files from a particular folder.
Which means some command that could go into that folder through a path provided and get/copy just the name of the file.
Regards
Thank You
Yesterday I had a nasty mirroring problem.
The principal and the mirror server are both running SQL Server 2005 64-bit Enterprise Edition. Witness is running Workgroup Edition. We are running in high availability mode (SAFETY ON).
3 nights before something strange happened and 1 database failed over (out of the six being mirrored) for some unknown reason. The other 5 didn't. We failed that one back and all seemed ok over the weekend.
We came in Monday and found that the main live OLTP database was showing as the Principal on BOTH servers (in SMO/Management Studio and also in sys.database_mirroring). This is the "split brain" scenario that the presence of the witness is supposed to prevent. Both databases were accessible with a USE statement - clearly not right.
I pondered what to do, eventually I decided to remove mirroring from this database. That was ok until suddenly a few minutes later we realised the (original) Principal was in recovery! uisers of course were kicked out/unable to connect. I tried to force recovery with RESTORE DATABASE dbname WITH RECOVERY but it complained users were connected!
I had to KILL the users then recovery proceeded and the database became available again. I forced the mirror offline to prevent accidental usage.
This is obviously a nasty situation where mirroriing - which is supposed to prevent downtime - actually caused it instead.
I intend to log a call with CSS but I wanted to warn other users if they encounter something similar - it has shaken my confidence in mirroring quite severely.
Hi guys,
Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-
((All names and values had been changed to protect confidentiality))
region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3
A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.
Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.
So the "new" Report would have to look something like this.
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0
--------------- break page ---------------------------
Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0
---------- break Page -------------------- and so on
I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is
=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.
Grouping1: Group by Region, insert page after each group.
What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0
--------------- break page ---------------------------
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0
--------------- break page ---------------------------
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0
--------------- break page ---------------------------
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)
The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.
Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !
Bernard Ong
SQL Server 2000
Windows 2000 Advanced Server
We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)
Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute
SELECT GET_SID('U<valid user>', NULL)
it returns NULL, however, if I run
SELECT SUSER_SID(<valid user>)
then I get the Windows SID of whatever valid user name I supply.
We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.
NOW THE PLOT THICKENS. If I run this query on the dev box....
SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)
...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.
Why doesn't GET_SID() work outside of SP_GRANTDBACCESS?
Why did recompiling SP_GRANTDBACCESS break it permanently?
It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.
Hi all. I have ran into a annoying situatin on sql cluster. Config is:
w2k3 EE x64 r2 - two nodes
sql 2005 x64 standart v2153
2instances, active-active cluster.
While changing tcp port configuration of one instance (using configuration manager) I have acidentaly created that setting:
- All IP dynamic port and All IP port was set to same number while separate IPs have enabled dynamic ports and does not have static port set.
This situation leads to unability of starting the instance anymore. There is no need wide explanation, its quite clear - while starting instace, it was configured with static port and with dynamic port with the same number, so port conflict error takes place. Problem was how to escape from this situation. On non clustered sql is quite easy, because these settings are stored in registry and also can be changed thorough Configuration manager, easy solution. Nor on the cluster.. Modifying the registry even on both nodes was unefective, every try to start instance fails with port conflict error and bad config was saved to registry again. I wasnt able to find where this config resides and change it to corect values. I have revealed only that it is not stored in instance master DB (restoring it from backup). Looks like its held somehow by cluster service itself, but I didnt find where it should be and how to edit it. So the last possible option take place (time was against me) - reinstalation of whole clustered instance. Nice example of how one click can create you a 2 days of work... Iam still interested if there is a smooth and easy way out of this situatin:-) So if anyone knows..... If there is not, I realy warn everybody.
DAvid
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!
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
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
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 RelatedI 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.
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 RelatedThis 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).