.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL 2005 Format Question

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

Hi Guys I have a fully functioning query, but need to do a little formatting. One of my fields is called a route name. An example of the data in that field is "PRN L5 L7 S LAM C"

Now what I need to do is firstly remove the PRN, secondly split the route into seperate columns, so column 1 would have L5, column 2 would have L7 ect....

Now, the route operations (L5, L7, LAM) would have either 1,2 or 3 characters in no paticular order. Any body got any ideas?

Regards Richard Baker

View Complete Post

More Related Resource Links

Question regarding IIF statement and Grouping in SSRS 2005

Hi Guys, I am using SSRS 2005. I am facing small prob in using IIF statement to hide a column. I am having a Grouping in my table. (Emp_Group and the values are 'Yes' and 'No') Now, I want to hide a column of the table if Grouping is Yes and I want to show the table if Grouping is No. (When exported to excel it will appear in two different sheets). Any suggestions please............

Question regarding SSRS 2005

Hi Guys, I am using SSRS 2005. I am using a parameter named Employee (and it has the ability to accept multiple values like this : '100,200,300' or '100', or '100,'1453' etc. I made this possible in the Stored Proc using some Split function) Now What I want is when we enter values manually in the Employee parameter, I want to display their respective names in the Header part of the report. For example, if we enter values '100,'1453'  and view the report, I want the Header information to be displayed as 'James,Steve'. And I am having fields named Employee_Code and Emplyee_Name in my report. And the report designed so that user should manually enter the values of employee codes. ANy ideas??? Thanks, Bablu.

problem in date format in sql 2005

I am new to sql2005  My problem is,  I send a date parameter (@fdt  and @Tdt)   like "DD/MM/YYYY" format through Sql reporting services My query is select  * from deposits where Depdate >= @fdt and Depdate <=  @tdt in the aove query there is no record has been fetched   But i give like this,  Inthis case i get a resultselect  * from deposits where Depdate >= '2010/04/01' and Depdate <= '2010/08/31' pls tell me how can i convert the date to "yyyy/mm/dd"    in where condition   Thanks Gk  

SSRS 2005 Render Format Excel Issue with Time Field


I have a report that uses a datetime field but gets populated only with the time. So, in SQL db, it gets stored with a default date value of 1899-12-30 12:01:01 with the correct time filled in. Report in SSRS Preview mode displays the value just fine when I use the Time format in Layout mode. I build and deploy and able to view the report in a web browser with no problem displaying the time field. But when I subscribe to the report and choose Excel render format, when I open up the Excel file from the email that was sent, I get negative decimal values. I have tried every possible Time format in SSRS but I get the same result. I also tried just the default and still gives me negative decimal values that show up as ####### in Excel.

Any help will be greatly appreciated. Thanks

date format examples: Dumb question


I can't find a table that shows what different SSIS data flow date formats look like.

What IS a dt_dbdate, vs. a dt_dbtime or a dt_dbtimestamp?

I know these must be right under my nose on MSDN somewhere. 


All I can find is http://msdn.microsoft.com/en-us/library/ms141704.aspx.

If there's "Business Intelligence", there's bound to be "Business Stupidity".

SSRS 2005 export to csv has question mark in place of sterling currency symbol



I've upgraded reports from SSRS 2000 to 2005, and since then when I export to csv I get a question mark wherever there ought to be a sterling currency symbol. The table cell in the report is formatted correctly and when exporting to xls the currency symbol is preserved. One of my users requires the data as csv.

Can anyone help with this?



SSRS 2005 Subtotal Question


I am using a matrix to display some numbers and in my data column, I am displaying average values.  More specifically (# of actions/# of opportunties).  This gives average # of actions performed per opportunity.  And it's being displayed per sales person.   The columns are grouped by certain type of actions (phone call, email, voicemail).  What I want to capture in the subtotal area is sum of the average, not the total average.  

In otherwords, if 3.5 phone calls, 2 emails, and 2.5 voicemails, I want to display 8.  Not 2.6.  How is that done?  Thanks.

A strange question about maintenance plan in sqlserver 2005


This is SQLServer 2005EE.

I created one maintenance plan some time ago.

But today i found i can't execute it and drop it.

the error message is:"exception has been thorwn by the target of an invocation.(mscorlib)"

The strange thing is,now i can create a new MP and drop it.

I have not found any valuable clues on the web sites.

could you please help me?


If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

SQL 2005 Backup question - Differentials seem to big right after a full backup has occurred.

i have a sql2005 database in Full recovery model.
(Microsoft SQL Server 2005 - 9.00.3042.00 (X64)   Feb 10 2007 00:59:02   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) )

MDF is 12GB & LDF is currently 6.4GB.
I do weekly fulls on Friday at 10pm.
I also do daily Diffs at 8am, 12pm, 4pm.
We also do hourly Transaction log backups during biz hours since this is an ERP system.

The hourly transactional logs seem to vary predictably given how much work is being done on the database.

But the diffs just seem to large in proportion to the size of the db, EVEN AFTER A FULL BACKUP WAS JUST DONE.

For instance, i just ran a full back up which was 18.7GB. 
Then i IMMEDIATELY ran a diff and it's 6.4GB.

Shouldn't a diff immediately following a full be almost nothing?
Am i missing anything here?

my backup syntax that i just ran was as simple as:
BACKUP DATABASE coll18_production
TO DISK = 'X:\SQL Backups\Weekly\coll18_production\coll18_production_FULL__20100201_1425.bak'

BACKUP DATABASE coll18_production TO DISK = 'X:\SQL Backups\Daily\coll18_production\coll18_production_DIFF__20100201_1430.bak'


SQL 2005, mainteance clean up task didn't delete a manual bak file with different format name, is i

I have a manual full backup, its name is in different format than scheduled dbname_date....bak, the clean up didn't delete it, is it normal?

Question regarding Subscriptions in SSRS 2005


I have a question regarding subscriptions in SSRS 2005.

We have development (reports_dev)  and Production (reports_prod) environments .

I created a subscription in the Production environment for a report, and ticked the Include Link so that I can get the link of the Production environment reporting services of the report.

I am getting the email but in the email its showing the link of Development environment. (http://reports_dev/reportserver................)

So my question is where can I find this information and how to change it to the correct Production environment link http://reports_prod/reportserver ..................

Any ideas?



Question on Reporting Services 2005 Date from iseries to ssrs

I was wondering I am using SQL server reporting services 2005 and I-series to gather my data.
I need to convert a date from yymmdd to mmddyyyy to use the calendar in a parameter. Is there anyway to do this? I have tried many different ways with no success. basically because the date in the i-series is 110414 (today's date) The datetime option on parameter will not work. I can use it as a string and type it in. I would really like to convert it to 04/14/2011 so that I can let the user select from a calendar. Let me know if you know anyway or anywhere I can post it to get an answer to this question.
Thank you

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


How to Encrypt and Decrypt a Password using SQLSERVER 2005?(Video)

Encypt and Decrypt a Password using SQLSERVER 2005(Video)

SQL Server 2005 Try and Catch Exception Handling

I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend