MSDN Magazine November 2003
View Complete Post
MSDN Magazine February 2004
I'm using varchar(Max) to store a text file content. It's not taking the newline character. I tried \n and char(13) etc.. whatever solution posted in the net.
ex : update test set f='This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.'
after updating it doesn't have any newline or linebreak. Can anyone help me in this how to have linebreaks in the varchar(max) field.
Thanks a lot.
We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all
One of article says that SQL Server 2005/SQL Server 2008 not support
"Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing.
it is going to be big deal.
our client Decided to stop migration, Please some help is there any other alternative.
I'm attempting to use a SQLDataSource to only pull those fields that are "different". For example: I have two tables; Artist & ArtistPendingChanges. When an Artists updates their online profile, it's saved to the ArtistPendingChanges table, which is almost an exact copy of the Artist table. I want the Artist to be able to go to a page where they can see what changes are pending. Ideally I'd like to be able to build my SELECT statement to do something like this (in plain English):
SELECT ArtistPendingChanges.FirstName if it's different from the Artist.FirstName
Right now this is my query and it's displaying all fields which isn't that helpful if the user only updated one column:
SELECT ArtistPendingChanges.LastName, ArtistPendingChanges.FirstName, ArtistPendingChanges.HomeAddress, ArtistPendingChanges.HomeAddressPublic,
ArtistPendingChanges.MailAddress, ArtistPendingChanges.MailAddressPublic, ArtistPendingChanges.PostalCode,
ArtistPendingChanges.PostalCodePublic, ArtistPendingChanges.HomePhone, ArtistPendingChanges.CommunityID,
ArtistPendingChanges.HomePhonePublic, ArtistPendingChanges.AltPhone, ArtistPendingChanges.AltPhonePublic, ArtistPendingChanges.PhoneTypeID,
I am facing problem in sql server 2008, my requirement is to sum the values in each columns in table and displays in total.
I used stored procedure to get 5 rows with values, but i need to total value for each column in bottom row. Like
COL COL1 COL2 COL3 COL4 AA 150 100 50 4HHH 161 125 36 4PPPP 160 85 75 4JJJJJ 120 56 64 2GGGG 40 31 9 2TOTAL ?? ?? ?? ??
I need to get the total for
I was thinking about the fastest way to retrieve a "set" of objects related to a specific user. For instance, if I was to create a website similar to Flickr where people upload lots and lots of photos(jpegs) what would be the fastest way to be able to retrieve those photos from the SQL Server database. Scenario 1: You store metadata about each file they upload to the website as a single row in a database table. Then when you want to retrieve a specific users uploaded files you simply scan the database file for every row matching the UserID and store them in memory.
SELECT PictureId, UserId, PathToFile, Caption, (etc.)
This, of course, gets all the information about all the photos a single user has uploaded. My concern with this approach is when the user activity goes from a few thousand to being in excess of a million.
This approach seems fine for a small website but does this method work for a million plus user website like Flicker or MySpace? When you have a million users and millions more photos does this approach still perform at acceptable speeds?
My idea was to actually attempt to create a collection in memory of "ALL" the PictureIds' for a single user and store them in-memory, in a collection and then save this collection as a single VARBINARY(MAX) field in the SQL
Have spent few hours to get this working. I like to get help now. Please note MONTH AND DATES stored in single or double digits as they occur.
Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010, 22/10/2010 etc. Following UK Date here.
It is required to pull the data to COUNT No. of Expired. Obviously below one fails.
--AS Date AND Month stored in Single digits AND double digits , below statement
--thorws Arthmetic overflow exception
SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND
CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired
ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.
NOW IT IS REQUIERD TO RETRIEVE ALL RECORDS WHERE EXPIRYDATE BETWEEN USER PASSED START MONTH,START YEAR AND END MONTH, END YEAR.
USING SQL SERVER 2000
SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND
CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired
Your help is greatly appreciated. Spent hours to get this resolved.
sorry if the following appears like a total newbie question, but I'm taking my first college course using MS SQL Server & SSIS, so please bear with me :)
I'm currently working on an assignment and face a couple (maybe mundane) problems:
I have a task to move table having "text" data type for a column from SQL Server to CSV file so while transfering the data using SSIS I want to perform Cleanup on this column such as removing HTML tags, White spaces etc and then transfer it. Note: I dont have any option to store the table in the staging database as I will be running it on the production server directly.
Can any one help me accomplish this task???
Hi all ...I need to add values like this ... I have a table like this...
EmpID Basic PF DA HRA MA
1 500 200 100 100 100 =
2 100 100 100 100 100 =
I need to add these values and store it in column through query ...Like this
The Total Salary Would be like this.... BY Adding First row of EmpId:1
Total Salary : 1000( 500+200+100+100+100)...
How to Write query for this...how to do this...kindly help.....