I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.
The number will have 8 digits.
first 2 digits correspond to year. ex:04 or 03
the next 6 digits start with 000001 and it should get added for each new entry of data.
i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.
A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
I want to generate a resultset that is just a series of numbers inascending order or perhaps a series of dates.. What I mean is, isthere a way to generate a temporary table of dates given an input of astart date and an end date.. This table would then contain an entryfor each date in ascending order from the start date to and includingthe end date..Or perhaps with numbers, given a start of 5 and and end of 7the resulting table would be567Would appreciate any help with this one.. ThanksChris
Can you help me with SQL issue I€™m stuck on? I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
I have generated an SQL script such that when it is run, it will automatically create tables if not existing or drop the existing tables. The problem I have is that, is there any way I can also make the script create not only the tables but also transfer the data from the old database to the new database?
I would really appreciate if any of you could point me to any existing programs, scripts etc that can do that. Thanks in advance
Is there a way to generate SQL Scripts from a stored procedure?
For disaster recovery and documenation reasons, we'd like to create scripts of our databases periodically. I, being somewhat lazy, would like to automate this so I really don't have to remember to run this every so often.
I am unable to see how to generate a leading zero.
Table A
declare @TableA table ( ID numeric , Fruits varchar(10) ) insert @TableA select 1,'Oranges' union all select 2,'Mangoes' union all select 3,'Apricots'
ID Table A 1 Apples 2 Oranges 3 Grapes 4 Apricots
declare @TableB table ( seed numeric , ) insert @TableB select 080513000448 union all select 080513000449 union all select 080513000450
Table B seed 080513000448 080513000449 080513000450
I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case
SELECTconvert(varchar(10), getdate(), 12) + (SELECT CASE WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12) THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12) ELSE '000000' END AS SEED FROM B) + (row_number() over (order by Id)) as SEED FROM A
Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.
Help again please,I need to insert rows into a table from another table. The tables areidentical column wise except the table im inserting from does not havea primary key value. On insert I need to generate a primary key thatis consecutive based on the table im inserting into. Also the table iminserting into does not have a identity column. Much appreciated.
we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?
At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.
I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?
SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.
I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.
I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.
I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.
What are my other options? How can I generate a primary key for every new row?
Hello all, I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction. 1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database. The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet. Is there a setting available to create the script in a specific order to prevent these errors? 2) This is somewhat related to the first question. I need to drop all the tables/procedures/functions before I re-create them. Can I set all the DROPs at the beginning of the script, and then run all the CREATEs? 3) Eventually the database will be ready for production and in use. And eventually I'll be asked to make changes, without erasing existing data. Can I accomplish this using the generate scripts feature? What topics should I be investigating when looking into conducting these types of updates? "Updating database" is too general and I'm not sure the technical term. I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself. Basically, I don't know what I don't know. Any help is appreciated. Thanks.
hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip( id int IDENTITY NOT NULL, ip varchar(15) NOT NULL, hostname varchar(128) NOT NULL, primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code thankssimo stored as a new query
I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?
Hi all, I have a question about generating membership numbers on the fly when someone registers to my website.
Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...
SR357324J
This will then stay with them for the lifetime of their membership and be on their printed loyalty card.
My questions are... 1) Is there a 'good practice' for membership number format and generation?
2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.
I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.
Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/
(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)
But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#
Hi All, I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am) Is there anyway to achive this via SQL 05 Express? Thanks, Mal.
