i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily.
I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column.
Will appreciate help on this
In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated. I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.
I have a windows forms app and I'm trying to add functionality that would allow the user to enter a number into a textbox control on a setup form and then that number would autoincrement by one each time a new record on another form is inserted into the DB .
I've searched high and low and can't seem to find much information on how I can put this together. I'm sure I'll need a "numbers" table in the DB that stores the numbers and is joined with the parent table where the records are being inserted into. After that, I'm pretty much lost on how to generate the numbers in the "Numbers" table.
If anyone could assist or point me in the right direction of a resource, I would really appreciate it!!
This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)
How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?
CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS DECLARE @NewOrderId int, @nSQLError int, @nRowCount int BEGIN TRAN UPDATE CUSTOMERS SET ORDER_NUMBER=ORDER_NUMBER + 1 WHERE COMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -999 End SELECTORDER_NUMBER FROMCUSTOMERS WHERECOMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -998 End COMMIT TRAN
In SSIS, I created a flow and within that flow i need to create an automatic number generator. The flow is simple: Ole db source part, a multicast part and a flat file destination and a ole db destination. In the Ole db source there is no field that is the starting point for this generator. This automatic nr should start with 10000 and each time a row is found it should be increased by 1.
First question: is it possible to create a column in SSIS flow which is the starting point for the generator (in this case 10000) or should it be created in the source table?
Second question: where in SSIS and with what component can i create such a generator??
I need to update an integer column in my table with a random number. Here's what I've done so far (with the table name and column names changed):
UPDATE dbo.MyTable SET Column1 = RAND() * 1000000
My question is, how come the values in my column all have the same value? I thought the RAND function gives out a random number? How can I update my table to generate random numbers?
How to create random number for the value in other colum. Please help and urgent
I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)
If Ori_quantity < 5000 then Rand_quanty as qty = I want Random number within 100 Else If Ori_quantity = 0 or < 10 then Rand_quanty as qty = I want Random number within 7 End if
From tblname where Location = 'DAS'
I have around 2500 field. So when I run the query I expect the result should be like below
I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?
If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.
ID  Item           RandomNumber ------------------------------------------- 1   JEANS                   1234567 2   SHIRT                 72813550 3   HOOD                           Null 4   TROUSER             72191839 5   BLAZER                            0
I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query
select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1)) when RandomNumber is null then (select floor(rand()*100000000-1)) else RandomNumber end from tblProducts
So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.
Hey Guys, I have a quick stored procedure question for you. I created a procedure that returns a randomly generated number.
I know there is a built in Rand() function but I need to be able to specify the range. Anyways, it€™s returning multiple numbers (1 for each row in the tempTable) But I just want one number returned. I tried using MAX( ) but that€™s not a good way of doing it.
My question is, how do I return just one record or number?
Thanks Fellas,
Chris
ALTER PROCEDURE dbo.spx_GetRandomNum
AS
DECLARE @Start_Num int
DECLARE @End_Num int
DECLARE @ReturnValue int DECLARE @RecNum int DECLARE @CurrentRec int DECLARE @MyCount int
SET @Start_Num=1
SET @End_Num=100
CREATE TABLE #tblTemp (CurrentRecNo int IDENTITY(1,1), RandomNumber int null)
SET @MyCount = ( @End_Num - @Start_Num ) + 1 SET @CurrentRec = 1
WHILE @CurrentRec <= @MyCount
BEGIN SET @RecNum = CAST((RAND() * @MyCount) AS int) + @Start_Num
INSERT INTO #tblTemp (RandomNumber) VALUES (@RecNum) SET @CurrentRec = @CurrentRec + 1
END
SET @ReturnValue = (SELECT RandomNumber FROM #tblTemp)
I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it? thanks!
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp. Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
The VB code was ok if you wanted it to be basic but when I was using it and trying to get it to work into my project it was quite painful. So in retrospect I have gone back to C# ... so I can have operator overloading plus smaller code anyhow ... will post here new routine ... working on one that will convert a stored procedure to a function as well... ... I still have the old code if anyone is interested, but like I said ... it's not very robust and getting it there in VB.NET just was a bigger pain than I need at the moment ...
Hi,This may be a little OT, but I don't know exactly where else to postit. I am writing a little parser that generates valid SQL using"English like" text. I want to use this to allow non-technical users tobe able to quickly write their own queries, to search through adatabase that stores information about the sales of differentcompanies. I can provide more information for anyone who wants to help.Currently, the syntax is :Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon.... and ITEM_DATE date_conditionWhere:product_filter specifies the product type to be included in the searchcompany specifies the company whose data is to be searchedfuncname is an aggregate functioncondition specifies the criteria for the aggregate function (i.e. aHAVING clause)date_condition specifies the criteria for the dates to be used in thesearchNote: there can be more than 1 aggregate functionA typical query then may look like this:Select all 'toys' from 'Walmart' where average_sales(100) 100000 andavaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and"01-Jan-06"I would like to know what the underlying SQL statement will look like,so that I can use this knowlege to build a generic parser that createsSQL statements from the "English like" text, using the syntax Idescribed above.
HI all im new to sql reporting services, im in need of a rdl (report definition language)generator are there any resouirces out there on the net ? if so , can u suggest some links for the same? thanks in advance !
Hello, I am new to MS SQL. I primarily used MySql, but my employer has MS SQL so i am trying to learn this. I kind of assumed that this was going to be similar to MSACCESS. My question is this. In access its easy to make forms and generate reports. Is there an "add-on" that makes it just as easy in MS SQL?
I see Crystal reports mentioned, but don't think my company would spend more money on this project.
I can't seem to find it and the link is no longer valid. Does anyone happen to have a copy of this script? I'd like to check it out again (finally starting to do regular C# programming here at work).
Thanks!
Kyle Heon PixelMEDIA, Inc. Senior Application Programmer, MCP kheon@pixelmedia.com
Hello,Since SQL Server has no sequence generator, I wrote my own. (I claimno ownership of it as it is closely modeled after earlier discussionson this topic.) I have included the sql statements below.While it works independently on its own, It seems to lock in multi-userenvironments or in nested-transactions. It s funny really: I have mymain transaction, but the sequence generator below forces anothertransaction, which I do not really care for. I cannot remove the extratransaction from the sequence generator because I would like to discardany values it retrieved, regardless of whether the main transactionsucceeded or failed.Any suggestions?--------------------------------------------------------------------------------create table my_sequence (name varchar(10), seq int identity (1, 1))godeclare @next intbegin transactionupdate my_sequence set seq = seq + 1 where name = 'abc';select @next = seq from my_sequence where name = 'abc';commit transaction---------------------------------------------------------
In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)
So I want to know, is there any automatic query generator ? that could work like: 1) We enter all control names 1 by 1 2) we enter postfix text ( like .Trim(), .ToString() ) etc 3) Result should be automatically generated query
Is there any software/program for that ???
Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists
Hello,- Is any body aware of a random workload/query generator such as theTPC-H query generator (QGEN) ? I am looking for a query generator thattakes a schema as an input, and produces several queries.I need it mainly for performance evaluation. the generator I am lookingfor should produce SELECT queries, including joins, and not only INSERTINTO, DELETE, UPDATE queries such as the "SqlQueryGenerator" athttp://www.tucows.com/preview/297930ThanksRegards,Abdur-Rahman
I have only been into SQL Server for a few months, and written severalstored procedures. Is there a good software product available that willgenerate the SQL code for a stored procedure based on a visual interface?Any help is appreciated.
I need a solution ( if this is possible) to automatically generate create scripts for my replications on our live server and put that into a file. I dont have permission to scrip that from the Enterprise Managger, but the support have and can make a job in the live server for this. So that would be enough for me to put a job to the live server what puts the scripts to a file. I want to use the same replications in my test environmnet, and they are always changing and I dont want to wait for the support.
I have an idea, but I still dont try it: If I run a trace, when i push the generate sql script button, maybe I can get the code for the generating the create script. :confused:
Is there a simple solution for this problem, or I have to get it from the trace ? Thanx!
