Lottery / Allocate Number
Oct 25, 2005
I’m hoping someone can help me create a ‘lottery’ for records in a table.
I have a table called tblPoints with a number of fields including Application_ID, Total_Points and Place_No. The following is a sample of the data
Application_ID Total_PointsPlace_No
87 105
68 95
49 75
96 75
155 75
156 70
144 70
146 70
At the moment there is no data in the Place_No field.
I want to assign a Place_No to all records based on the number of Points (Total_Points). The highest Points value should have a place Number of 1 and so on.
Application_IDTotal_PointsPlace_No
871051
68952
4975
9675
15575
15670
14470
14670
However, where a number of applications have the same points I want to randomly allocate a place number for them. Application_ID 49, 96 and 155 all have 75 points so each of the 3 applications should be randomly allocated one of the following place numbers, 3, 4 and 5. I can not allocate them based on their order in the table as it has to be seen as a ‘lottery’ and each time it is run they would expect to get a different result.
The same thing then has to happen with the last 3 records in this sample allocating place numbers 6, 7 and 8
I was hoping to create a stored procedure to do this, but I’ve no idea where to begin. I would appreciate any help you could give. Thank you.
C
View 6 Replies
ADVERTISEMENT
May 6, 2015
I'm developing a Lottery system. Here are some background information:
- Total 48 numbers
- Draw 6 numbers + 1 extra number
- each Bet select 6 numbers
And the Prize:
Prize
Selected Matched
Extra number Matched
1st
6
No
2nd
5
Yes
[Code] ...
Below is my proposed table design (simplified):
Draw table
Column
Datatype
DrawDate
date
Primary Key
ResultNumber1
tinyint
[Code] ....
There will be millions of Bet for a Draw. I need to write a stored procedure to check which bets won the 1st ~ 7th prizes. How to write a query to match the bets with the draw result? The query should be run within 1 minute. And should I change my table design?
View 5 Replies
View Related
Feb 5, 2015
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
View 8 Replies
View Related
Aug 6, 1999
Hi,
I've got a table with 65'000 records and when I do a SELECT * FROM tablename ORDER BY Name I receive this error message:
Msg 1105, Level 17, State 1
Can't allocate space for object '-513' in database 'tempdb' because the 'system' segment is full.
If you ran out of space in Syslogs, dump the transaction log.
Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
Msg 1510, Level 17, State 2
Sort failed: Out of space or locks in database 'tempdb'
So I've dumped the transaction with no_log and also I've extended the segement from the master database (because tempdb is in it bydefault):
sp_extendsegment system, master
But I've still got the error message. Is there anybody who can advice me?
Thank you
View 2 Replies
View Related
Oct 25, 2004
Hi,
Why we allocate .mdf and .ldf on seperate drives?
Please tell me a proper logical reason behind it.
View 2 Replies
View Related
Jan 9, 2006
Hi,
Sorry for placing it in SQL Server Forum.But I couldn't find a appropiate forum for my question.Can anybody help me?
My question is -
How to allocate expended memory in windows XP?
Thanks!!
Joydeep
View 1 Replies
View Related
Jul 20, 2005
I am getting a "Could not allocate space for object 'temp_trc' in database'Test' because the 'PRIMARY' filegroup is full"The database test has unrestricted growth (All the defaults). It resides ondrive c which has 4Gigs free. I added new data and log files on drive dwhich is about 30G free. I know that my insert doesn't take even 1G diskspace.Why is the database complaining about a full filegroup when I just expandedit?J.
View 1 Replies
View Related
Aug 16, 2000
Could anyone please help me in fixing this error asap...
Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '(SYSTEM table id: -334560816)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
View 7 Replies
View Related
Dec 8, 2005
Any help would be appreciated.
This message is received on the client. Client pc has Intel 2Ghz or better processor, 512MB Ram, sufficient hard drive space, connecting to MS SQLServer 2000 thru TCP/IP.
Database server is running Windows 2000 Advanced Server w/ SP3 and MS SQLServer 2000 Enterprise Edition w/ SP3, with 4 way 700Mhz PIII Xeon processors and 4GB Ram (I'm not certain about network connectivity, but it's at least 100MB Ethernet). The database is approximately 87GB, with an average of 250 to 300 connections.
The application is vendor supplied, written in Visual Basic 3.0. Therefore I am using 16 bit SQL drivers, the latest I am aware of, dated 6/15/1997.
This database was previously running on SQLServer 7, and this error did not occur. It started after the upgrade to SQLServer 2000. We discovered this error in testing the upgrade and found that by decreasing the "Network Packet Size" setting on SQLServer w/ sp_configure, we were able to make the message go away. However now that we are in a production environment, the message seems to be coming back randomly. We have the NPS set to 1024 (default is 4096). I'm worried about performance if it is dropped much farther.
Any ideas? Thanks again for any help!
View 8 Replies
View Related
May 9, 2004
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."
I get this error when running a query on another database. But why?
Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?
View 7 Replies
View Related
May 22, 2004
I get the following error when doing a variety of basic queries on other databases:
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."
This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.
Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%
Both tempdb data file and transaction file are on D: but all drives have ample space:
c:25 GB free
D:69 GB free
E:175 GB free
sp_spaceused returns the following for tempdb:
namesizeunallocated space
tempdb4.00 MB1.45 MB
reserveddataindex_sizeunused
568 KB176 KB304 KB88 KB
sp_helpdb returned for tempdb:
tempdb
4.00 MBsa2Apr 23 2004Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics80
This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)
Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?
View 2 Replies
View Related
Dec 30, 2007
I have received below alert:
Could not allocate space for objects in database 'abc'
And I have added 1 GB(1024 MB) of free space to primary file system of 'abc'. However now the primary file system of 'abc' database is 120 GB and the file properties are : Automatically grow file is checked, By percent 1 and restrict file growth: 121024 MB
Still the database is showing as space avialable is 0.00, the total size is : 132186 MB
As of now I have't got any other alert, Please let me know if I get in the near future how to proceed??
One DTS package is running contunuously on this DB
View 2 Replies
View Related
Oct 18, 2007
Hello, I have an issue with a process that blows up because of the following error.
Executed as user: batchloader. Updated 0 existing Company records [SQLSTATE 01000] (Message 0) Inserted 0 new Company records [SQLSTATE 01000] (Message 0) Inserted 0 new EntityIdXref records [SQLSTATE 01000] (Message 0) Updated 977 existing CompanyCustomerAttr records [SQLSTATE 01000] (Message 0) Inserted 0 new CompanyCustomerAttr records [SQLSTATE 01000] (Message 0) Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth. [SQLSTATE 42000] (Error 1101). The step failed.
Ok I am going to be typeing some really bad practices(I just started here 3 weeks ago.)
There is 23.6 gig free on my log drive. The disk is not running out of space and there are no disk errors in event viewer. The process in question calles 2 procs. These 2 proces load files from the filesystem and bulk load them into #temp tables. Then select's from these tables are issued using criteria from a static table. There are around 700000 rows being inserted into the #temp tables and no indexes are being created so very large table scans going on. Also there are some cursors being called to row by row minipulate the records and in the cursor it calles fucntions using cursors. There are thousands of files being processes everyday by several different jobs. All of the processes are written the same way. We have the tempdb set to auto grow by 10 % and the initial size is 3.5 gig. There are 3 to 4000 tables in the database and 90 % of them are being created on the fly to be used by this process and yes once again there are no indexes created on the on the fly tables. We have only one Filegroup on the server default.
I believe that takeing some of the objects and moving them to there own filegroup will help this issue. Every month we take on up to 800000 new records to process on top of what we allready do. So we use cursors cursors cursors temp objects with no indexes and massive recordsets and doing sorts on massive records sets. I am working with development to show them how and where to index but this will take time. I need a quick solution. Any thoughts any questions? The box has 4 gig of ram.
View 3 Replies
View Related
Dec 31, 2007
I have received below alert:
Could not allocate space for objects in database 'abc'
And I have added 1 GB(1024 MB) of free space to primary file system of 'abc'. However now the primary file system of 'abc' database is 120 GB and the file properties are : Automatically grow file is checked, By percent 1 and restrict file growth: 121024 MB
Still the database is showing as space avialable is 0.00, the total size is : 132186 MB
As of now I have't got any other alert, Please let me know if I get in the near future how to proceed??
One DTS package is running contunuously on this DB
View 4 Replies
View Related
Oct 26, 2007
running Sql Server 2005 SP2 on Windows 2003 Server SP1 with 2GB RAM. After start-up, the sqlservr.exe does only take up around 100 MB of RAM, and it stays roughly there even if the DB is used heavily. This leads to very poor performance, even timeouts on simple querys.
In the task manager, I see that of the 2 GB of RAM, more than 1 GB is still available. I don't understand why SQL Server won't take it?
As a test, I configured the min and max amount of RAM SQL Server should used both to 1024 MB and restarted the service - but it is still the same picture. It won't take more than around 100 MB.
The server has just been restarted, but the problem remains.
BTW there is also an instance of SQL 2000 on the same machine. It shows the same behaviour - I even checked the "reserve phyiscal memory" checkbox there, but it stays on a very low number (50 MB) and doesn't adhere to the supposed size.
thanks and best regards,
- Urs
View 3 Replies
View Related
Mar 24, 2008
do all sql data types consume whole numbers of bytes? We have an app that might be best suited to bit manipulation at the nibble rather than byte level.
View 3 Replies
View Related
Aug 5, 2015
We got an error "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE"
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
Dec 10 2010 10:56:29
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
We had 24CPU intell cores and 64 gb RAM on server.
Sql service starts with enabled AWE + LPM and "-g512" option.
Why we still use such relic configuration it is not my fault. i'm investigate log and find that memorystatus "Optimization Queue" section has really huge values.
Optimization Queue
Overall Memory = 1321132032Target Memory = 1158799360 Last Notification = GROW Timeout = 6 Early Termination Factor = 5Small Gateway Configured Units = 32 Available Units = 26 Acquires = 6 Waiters = 0 Threshold Factor = 250000 Threshold = 250000Medium Gateway Configured Units = 8 Available Units = 7 Acquires = 1 Waiters = 0 Threshold Factor = 12 Threshold = 16094435Big Gateway Configured Units = 1 Available Units = 1 Acquires = 0 Waiters = 0 Threshold Factor = 8 Threshold = 144849920
[code]...
What forced bPOOL to allocate pages in MTL? How to determine that queries? Same about SQL OS.As workaround i increase -g startup option to "-g2048", and "max server memory" was decreased to 54Gb.
View 10 Replies
View Related
Nov 20, 2000
Hi all,
I need to load a table with 820,000 records from a Sybase db via DTS. It always fail with the error:
"Error at destination for row number 820000. Could not allocate space for object in tablespace tempdb
. The default filegroup is full.".
There is only the primary filegroup defined in the db. I've increased the size from 1.5GB to 2GB,
and specify that it shd grow automatically by 10% and there's no limit to the size.
There is still some 28GB in the server, so it should be fine.
It still fail so I added another file to the primary filegroup with size 100MB. Again, it failed with the same error msg.
Where or how else can I increase the tempdb size?
Any help is appreciated.
Thanx,
Jes
View 1 Replies
View Related
Jan 31, 2008
I have an error on the phpbb message board I am using. I have a MS SQL database and cannot figure out what I get this error. How could I fix this?
The full error is:
Could not allocate space for object 'phpbb_topics' in database 'adultsiblinggrief' because the 'PRIMARY' filegroup is full.
View 1 Replies
View Related
Feb 21, 2008
Hello.
I have a db dal containing the following code:
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SOME STORED PROCEDURE";
.
.
.
declare some SqlParameters
.
.
.
dbc.Open();
cmd.ExecuteNonQuery();
dbc.Close();
This code, when concurrently running via several threads, yields the following exception:
"The connection was not closed. The connection's current state is open."
My questions are:
1. Why don't .Net allocate another connection from the pool (I try to only concurrently run 2 threads while there are 25 connections in the connection pool) ?
2. How can one explicitly allocate a connection?
3. How do you suggest to solve this problem without a mutex/monitor etc' on the 3 bold lines above and without BeginExequteNonQuery()?
Thank you !
View 2 Replies
View Related
Apr 25, 2006
Hi All,
The following is the report from the SQL Server Mobile Subscription wizzard, Any Ideas?
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit
HRESULT 0x80045647 (0)
Invalid parameter @subid specified for sys.sp_MSmerge_log_idrange_alloc_on_distributor.
HRESULT 0x0000523F (0)
The operation could not be completed.
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Initially i thought it might be that some of the articles had primary keys that were of type nvarchar rather then Int thus resulting in no identity range being able to be assigned to those articles.
Test 1: I tried removing all articles that had nvarchar primary keys and left only one table that had an identity Int primary key colum. I then ran the snapshot agent. I then run through the Subscription Wizzard again and the error was the same.
Test 2: Then reading the error message again i tried those tables that didnt use identity columns and the wizzard completed successfully. Any idea what would be wrong with my articles that have identity columns. The article properties for the identity columns use the Identity Range Management defaults.
Any help would be appreciated.
View 18 Replies
View Related
Jul 20, 2005
is there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
View 1 Replies
View Related
Aug 8, 2007
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
View 4 Replies
View Related
Jan 17, 2007
Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
Thanks.
David.
View 7 Replies
View Related
Mar 21, 2015
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
View 7 Replies
View Related
Jun 29, 2015
I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
View 3 Replies
View Related
Feb 21, 2007
hi i'm having this error on my application"cannot allocate more connection.connect pool is at maximum increase max pool size" the proble is when i do testing this error does not apply it only Appears when the application is been used by many people
How can I resolve this?
Thanks
View 1 Replies
View Related
Oct 15, 2007
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
View 2 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableÂ
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Oct 8, 2006
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
View 4 Replies
View Related
Jun 5, 2007
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
View 1 Replies
View Related
Feb 19, 2013
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies
View Related
