I have a huge question haha. I just simply want to make a number display differently before I insert it into the table. for instance in Access, you can simply say FORMAT(c,format) ex: FORMAT(@CHARMINS, 00000) and it should work that way.
How would I accomplish this in SQL server? Right now I have the number 30 displaying as 30.00 in the variable it is set to.... I want it to be shown as 00030.... and any other number that is like 30.00, I want it shown the same way. Like 100.00 would be 00100. Thank you.
Presently I have a column which represents the amount of money someone spent and for some reason, the fields are being outputted like this 29.8600.
This field is infact suppose to 29.86. Now initially this file was an excel spread sheet and I imported it into sql server 2000 and the datatype is money. What would i use to get the desired field.
Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
I have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
I have two questions : 1) Linking SQL Server Databases 2) Data Formatting like InputMask
1) I want to know if there is a way to link two seperate SQL server databases (it's tables) within the same server (local server). I created one Global database - with clients, etc. tables. The reason I did it like this is that I have to handle three seperate invoices databases, but using the same clients, etc. tables to get their information from. I used MS Access like this, linking Clients, etc tables from the Clients Database with the three other Databases. It worked fine but now I need some new features like setting the InvoiceNumber to something different, eg . 04/0001 for this year and then 05/0001 for next year. My Access databases can't change this numbers - it's just a autonumber I used. I know some Visual Basic code can make a change like perhaps adding a new column like InvYear and then adding it together with InvoiceNumber to get the Invoice Number, but I want to change it to SQL Server because I want to learn how SQL Server works. I am just starting out and not doing courses.
2) The other thing is I want to know if there is some feature in SQL Server to make data input easier, like a InputMask in Access where you can let a text column be formatted as you want. Eg. for a telephone number, it can store it in the format you made; like (011) 123 4567, not like you typed it : 0111234567.
I need to export some Database data into a text file. My Query looks like this:
SELECT Category1, Category2, Category3 FROM dbo.tbl1 WHERE Category1 = 'JP-4' AND Category2> 4;
This works fine to get the data, however there is some html formatting in the table entries such as
`<p>,</p>, ,</br>` etc.
So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work. I've also got an issue with line splits and would need to remove the ( ).
The Data format is something like this:
Category1: JP-4 Category2: 4 Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p> <p>amet, consectetur, adipisci velit</p> Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>
I got it to work like this with the replace function:
SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','') FROM dbo.khia_tbl WHERE Category1= 'JP-4' AND Category2> 4;
But the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so , </br>,
as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it...
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie. Code Description Brand ABC1 BLANK DVD SONY ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows FOR XML RAW ('node'), root('nodes'), ELEMENTS
I'm looking for a new way to publish reports based on OLAP data and was very disappointed to find that Reporting Services does not natively support displaying MDX query results in a matrix. While it is possible to assign an MDX query to the matrix control in Reporting Services, the two main problems are that the columns of the query must be measures (not dimensions), and it does not support display of server based formatting (fore color, back color, and font flags).
Does anyone know of any custom control that properly deals with Analysis Services data?
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL as select UserName , UserPassword , UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
where as I prefer this format:
create procedure getUser ( @UserId int = NULL ) as select UserName, UserPassword, UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Hello - Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size. Thanks Adam
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible? SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV I tried: SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV But this does not format it as currency. Any input would be helpful.
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005. How could I write a sql statment to put make this show up as 07/29/05?
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible) Using this SQL statement: select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc the resulting output displays this in the email body "Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 " but what I want is the text to look more like this: Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092 Is there a way to do this in the SQL statement (without HTML tags)?
