|
Select @total=total+@total,@month=@month + [month] + ' ' + Cast(@total as varchar(6)) + ',' from CummSum
Then you have to split the value with comma and spaces
Best Regards,
Chetan Patel
|
|
|
|
|
Hi Friends,
I am having problem with extracting data from a field which has datatype text in sql server 2005. Following is the two sample rows which contains the field.
1.
TrackID14910417OrderID1019RefReason1019Src21244700309600019SID13640214PayCh13MJA210PurchaseID10218
2.
TrackID145493211Description235USE+IT+UP+AND+WEAR+IT+OUT+-+ODYSSEY17OrderID10210PurchaseID1019RefReason10214PayCh13MJA16UserID23642B942B8-05D0-4447-BEEC-7EF11419368B217CVFDate1019SID13640213TID1019Dst21244700059600
I want to extract TrackID value from above two rows, the length of the values may vary, it might be 8,9 or 10 digits long.
Shahzad Aslam
Software Engineer
Softech Systems Limited
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I guess you need to pull out ten characters, and then parse them. In future, perhaps you can think of a more sensible format 
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
yes 10 characters needed and then need to parse them, it could be a sensible format in the future but currently we have to continue with this..
Shahzad Aslam
Software Engineer
Softech Systems Limited
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Hi,
I am planning to develop a web application by ASP.NET on document management for commercial distribution. The target customers are middle size companies
and they will host their own applicaton servers.
I am now considering using which backend server database for development
and deployment. The candidates are MySQL, Interbase and Firebird.
My questions are as follow,
1. Are MySQL and Interbase are free for commercial distribution ?
(I only know that firebird is free, and MySQL and Interbase are open-source)
2. Coz firebird is free for commerical distribution, is it good enough as backend server for mid-sized company usage ?
3. From view of performance only, which one is the best ?
4. From view of performance combined with cost, which one is the best ?
Thanks
|
|
|
|
|
MySQL is not free for commercial use, AFAIK
I would say write standard SQL and test with all three to see which performs best for you.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
A couple things I'd LOVE to see Microsoft, if not ANSI, add to SQL.
#1
Select As Elements<br />
*<br />
From<br />
Elements;
Since SQL Server and VS.NET 2005 were released basically as sister projects, this would have been wonderful for queries that returned more than one table. The tables in a DataSet get called, by default, Table, Table2, Table3, etc. You can override this using the DataTableMapping class, but having it named in the query could also be useful.
#2
Select<br />
*, -ID, -Name<br />
From<br />
SomeTable
This would select all columns, except for ID and Name. Normally, it is bad practice, as most developers know, to use Select *. However, if you're writing a proc that must work on a table that is passed in by name, for instance, you either have to use Select *, or query the INFORMATION_SCHEMA for the necessary columns. The above syntax, I think, is much nicer.
Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan
Portland, Oregon, USA
|
|
|
|
|
I'm trying to write datetime's to an access DB. I can read them using an oleDbDataAdaptor and a dataset created from it, but when I write new values back to the DB only the date value is sent and the time is set to midnight. When debugging I can see the dataset is storing both the date and time values within my C# application, but only the former is being written into the DB itself.
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Solved by changing the type of the fields in the parameter collection for each query from OleDbType.DBDate to OleDbType.Date
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Hi,
Please look at the following piece of code from a strored procedure:
CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
@UniqueEmail int = 0
Does this mean that if @UniqueEmail is not supplied it will add a 0 to that table field?
Thanks
Brendan
|
|
|
|
|
.NET Enthusiast wrote: CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
@UniqueEmail int = 0
Does this mean that if @UniqueEmail is not supplied it will add a 0 to that table field?
I think you have your terminology wrong. This code has nothing to do with fields (columns) on tables.
If you call the stored procedure and you do not supply a value for the @UniqueEmail parameter then it will be replaced with 0. If you supply NULL as the parameter value it remains null.
|
|
|
|
|
Hy,
I hope this is the place where to post my problem. I didn't see a specialized forum for Sql Reporting Services. So here goes.
I have to create a report, rdl file. The report shows a few statistics for a period of time. I passed, the parameters, showed the data, everything. Now the only thing left is to modify the report to show the statistics for one month on a page. To give an example: I want to see the statistics from 1st of march to the 21st of april. The report should have 2 pages: a page for the month of march and one page with statistics from 1st of april to the 21st.
I tried google, MSDN, and stil couldn't find a solution to this problem.
Any hint is welcomed.
Thanks
PS: I'm using SQL Server 2005 and VS 2003
I will use Google before asking dumb questions
modified on Monday, March 10, 2008 8:59 AM
|
|
|
|
|
Have you tried using a datalist to repeat your data based on the month?
|
|
|
|
|
Hey,
Thanks for the reply. I have tried a data list, but to be sincerely I don't know how to filter the data based on the month. Can you give me a small example? Thanks
I will use Google before asking dumb questions
|
|
|
|
|
Thanks a lot. Cheers. It works just fine. 5 from me
I will use Google before asking dumb questions
|
|
|
|
|
I'm currently creating a data import routine for a project I'm working on that involves populating a database with information retrieved from an Excel spreadsheet.
I've found a problem with the way that column data types are discovered; certain columns are sparsely populated (e.g. one column has null / empty values for the first 50 rows and then numerical data after that) and it appears that this prevents data in that column from being loaded.
Apart from changing the sorting of the sheet, does anyone have any suggestions about how I could get around this? I believe that Excel uses a registry key to determine how many rows to look at when determining the data type but I'm really looking for a different solution.
Thanks in advance
It definitely isn't definatley
|
|
|
|
|
hi
I need a query for copying a database table to another database
I have done this before unfortunately forgotten it
if anyone knows please let me know
Thank u
*** Why EXPERTS behave like a fool ***
|
|
|
|
|
check this link[^]
I Love SQL
|
|
|
|
|
I have 3 Tables (Person-1------1->Employee-1------*>Career)
an employee can have multiple Career. I created the sql statement below
to return some information on about 200 employees
##################################################
select p.FamilyName,
p.AddressLine1,
Career.CareerDescription
from Person as p
inner join Employee on Employee.EmployeeID =p.PersonID
inner join Career on Employee.UniqueIdentifier = Career.ParentIdentifier and Career.PrimaryPostYN = 'Y'
###############################################
The "Career.PrimaryPostYN = Y" indicates which Post is active for an employee with multiple Career.
So only one post should be active at any one time. All other should be set to "Career.PrimaryPostYN = N"
QUESTION:
If an employee has multipe Career and all his career entries is set to "Career.PrimaryPostYN = Y". In such a case i want to return the first case where "Career.PrimaryPostYN = Y"
How do i alter the above statement to do this while returning information for all other employee. I tried "top 1" but that only returned 1 record out of the 200?
|
|
|
|
|
ONeil Tomlinson wrote: If an employee has multipe Career and all his career entries is set to "Career.PrimaryPostYN = Y". In such a case i want to return the first case where "Career.PrimaryPostYN = Y"
The first thing you need to answer is: If the person has multiple rows where PrimaryPost is 'Y' and you want the "first", how do you define "first"?
Databases are set based systems. There is no concept of first. It may appear that way, but that is just a manifestation of the algorithms used to store and retrieve the data and the result order may change if the database engine decides on a different optimisation for a particular query.
|
|
|
|
|
Somthing like this is stored in the tables
Name : PrimaryPostYN : Post
-------------------------------------
john: Y : Electrical Engineer
John: Y : Engineer
Ben : N : Editor
Ben : Y : Senior Copywriter
Tom : Y : Developer
In this case there are two valid entries for "John". i want to display
the first entry when "John" is an "Electrical Engineer" or "Enginner" (Whichever one is return first in the record set) plus all the other Employees as well. How can i alter the Statement in my first post to do this?
|
|
|
|
|
ONeil Tomlinson wrote: In this case there are two valid entries for "John". i want to display
the first entry when "John" is an "Electrical Engineer" or "Enginner" (Whichever one is return first in the record set)
As I've mentioned before, there is no "first". What you see is an illusion. The key is the term "record set". A "set" has no order.
If you want "first" you have to artificially create it. How do you define first? You have to define an order for the result set.
|
|
|
|
|
Sorry my fault i thought Record set had an indexed order. Ok basically what I want is one entry (for employee john) to be returned (doesnt matter which one) plus all the other employees
|
|
|
|
|
If i want to display message when user enters NULL for some field ,when I call stored procedure from the database?
|
|
|
|
|
Master Mind'z wrote: If i want to display message when user enters NULL for some field ,when I call stored procedure from the database?
Are you asking if a stored procedure can open up a message box to display a message to the user?
If so, the answer is no. Well, I suppose you could if you hacked around a bit. However, it is not a good idea. SQL Server is a database engine, often it will reside on another machine and if you were to get it to open a message box, it would only do so on the machine in which the SQL Server was running - it would be absolutely useless to the person using your application as they wouldn't see it.
Finally, the suggestion that SQL Server generates the message box is betrays somewhat a poor design. You are attempting to put presentation layer functionality in the database layer.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
