I am trying to insert a unique code, like a voucher code, into a table in MSSQL, 10 numbers (0-9) which obviously needs to be unique.
I can code this in ASP, but I think the process could be a bit long winded, i.e. create a key - run a stored procedure to see if it is free if not loop and check again. Surely there would be a way to do this in a stored procedure in one?
So the code would look like 3928492834.
1. Generate Key in SQL
2. Check if not used in column 'kcode' if it is, regenerate and check again.
Any advice would be greatly appreciated, I know how to do this in ASP but bit of a SQL newbie!
Is there any way to auto generate the unique primary key when inserting data in MS SQL? there is a way to create the increasing integer as primary key, but is there any ways other than that?
I'm working on a final project for school. It is a warehouse application. For this application I will need to generate a barcode. I have read some articles online and found out that I could use the code 39 barcode font.
I just don't fully understand how to generate the unique barcode. Could someone please help me? I also would like to store the barcodes in a sql server table. Could I use reports to print the barcode?
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.
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Requirements • ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99 Last 8 bytes will alternate between 2 byte alpha/2 byte numeric • Generate from Alphabets – A through Z Numbers -0 to 9 • Generate Unique Sequence (No Duplicates). • Must Eliminate letters I and O
Output Expected • AA00AA00………..ZZ99ZZ99 • Using 24 alphabets & 10 digits , 24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW] ( @Id BIGINT ) Returns char(8)
I am using Visual Studio 2005 and SQL Server mobile edition. From Visual Studio I went to Data->Add New Data Source. From there I created and added an SQL server Mobile database. I want to use result sets instead of data sets. A file was added to my profect called: "databaseNameDataSet.xsd". In the properties for that file, when I change the Custom Tool property to MSResultSetGenerator, I get the following error:
Failed to generate ResultSet code. Typed ResultSets are only valid for Microsoft SQL Server Mobile Edition databases.
But I am using a SQL Server Mobile Edition database. Any help will be appreciated.
Is there an easy way to generate the parameters code for calling stored procedures?
Is there an easy way to generate stored procedure code without having to manually type it in? I've seen 3rd party tools, but I was wondering if there's anything within Sql Server to do it.
I'm using c#, visual studio 2005, sql server 2005.
I am working on SSAS project and now i am looking for a way to generate some documentation from XMLA code ?I have seen that it's possible to do it with XML, so i think it is possible too in XMLA !
I am looking for some examples of code for an appliction which will let run RS reports from a custom application. The user will choose the reports from a menu structure
We have an exiting application which we use to run crystal reports which we are going to be migrating to RS. We use custom web controls to capture the required parameter values for the reports, and hope to reuse the same controls (with minimal rework) to capture the parameters values for the RS reports.
What I am after is some code which will
a) let me run the report and display it. I don't want to display the report parameters in the URL etc as we generate some report parameter values based upon the user's ID and do NOT want them displayed back to the users
b) export the report to PDF, CSV etc depending upon the option chosen by the user.
The application will be developed in VS 2005 - VB. Does any one know of some links to some sample code
select * from bigtable where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
Using VS 2008 I got a Windows Smart Device project targeting WM6 Standard and using .NET CF 3.5. I add a new database file = creating an empty Compact 3.5 database (creates an sdf-file in my project). Then create an empty dataset in the wizard (creates an xsd-file in my project). Then I add a couple of tables in the database. After that I want to use my earlier created dataset. I mark the xsd-file and looks in Poperties. I get the error when I try to change Custom Tool value from 'MSDataSetGenerator' to 'MSResultSetGenerator'. Why????
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.