say, i have a column in the database that has number values. I want to display these number with comma separators (Eg: if the column values is 1654, then i want to display it as 1,654).
How can i achieve this in my sQL query
I have a table filled with phone numbers entered in every different way possible
(5551212, 1115551212, 111-555-1212, 111 555 1212, 111-555-1212 ex 1234, 5551212 x1234)
And I would like to pull them from the table using sql 2008 and have the formatting look like this:
(111)555-1212 x1234.
How can I make this happen? Where numbers are missing, I would prefer to leave the area blank, ie 5551212 becomes 555-1212, and 1115551212 becomes (111)555-1212, extension numbers only where they are currently included.
If I have a string of 10 numbers (with no other characters), what is the best way to format them into a standard phone format: xxx-xxx-xxxx?
I tried numerous variations in the format code and edit expression areas, but no luck.
Here is what I have
CDec(Sum(Val(Fields!Test1.Value))/(Val(CountRows("Group1"))*SomeNumber)*100%)
This works fine
but the problem is the number can be 25.325641. I only want to get 25 so I did this
Int(Sum(Val(Fields!Test1.Value))/(Val(CountRows("Group1"))*SomeNumber)*100%)
This did the trick but if the number is .255667 it will display 0.
here is what i want:
1-if the number is bigger than 0 i don't want to display the decimals for example:
25.01245 = 25
2-if the number is less than 0. I only want to display 2 decimal places
.256415 = .25
Thanks
Hi
I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).
I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out.
Anyone out there able to help me please?
Thanks,Keith.
I have a column of phone numbers that have formatting such as (xxx)xxx-xxxx,
or xxx.xxx.xxxx or xxx.xxx-xxxx; however, not all phone numbers are formatted in this fashion. I need to remove all characters and only leave behind numeric digits to look like xxxxxxxxxx. I have tried select replace(colname,'-','') from table. This does the trick but does not actually change the values in the column. Any help in accomplishing this would be helpful. Thanks in advance.
Paul
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
01 Jan 1980
01 Jan 2000
SQL query: I am importing data from one source to another. The phone number has 3 variations and I want to standardize the information. I need to remove the "+1" or "1" from the leading characters of the phone number.
example:
13035554444
+13035554444
I am trying to get my SQL create table to work for my phone number formatting and it is not. When I create the below code, the default is set to 3 numbers only.
CREATE SET TABLE dl_qpt_cqe.contacts, NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL
(contact_id integer not null ,contact varchar(50) , jobtitle varchar(50), dept varchar(50), phone integer format '999-999-9999', phone_ext varchar(10), email varchar(50), constraint pk primary key (contact_id));
Is there some other way I need to format the phone portion so the default is 999-999-9999?
Hi all,
Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).
I
have a relatively simple task - create an excel spreadsheet with 3
columns of data - Id, Description and Sales. ID and Description are
text, sales is int.
So my SP aggregates and creates my resultset
in my OLE DB Source in the Data Flow. It proceeds to the Excel
destination, and that all seems fine. My issue is that the data is
being written as text. Looking at the excel destination in Advanced
editor:
the Excel Destination Input, Input columns are formatted as
I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and
DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.
The
target excel spreadsheet is being created from a template file. That
template file has header columns. The target column for the Sales has
the entire column formatted to NUMBER (0 decimals). Yet to now avail.
When
I check the spreadsheet, the column has retained the cell formatting,
and I have a 'I' pop-up to inform me that 'someone' has inserted text
data into the number column (even though the data IS number).
Since
the SP spits out INT, it isn't a case of receiving a text value, imho.
While trying to change the external column data type in the advanced
editor, SSIS is quite happy to let me change the value for the Sales
output to DT_I4, apply, and ok. Then, when I open it immedaitely
aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't
handle it, at least tell me when I try and change it. don't let me
change it, and then revert back without telling me! Grumble grumble...
So - anyone know a way around this?
Hi,
when i try to export reports to excel number fileds has exported as text !!
I use SQL server with database in US codepage, Reporting Services in english version but excel with Italian codepage settings.
So i must convert the defaul decimal separator from "." to "," within the report generation. This cause that i can't use te cdbl() conversion directly in the report field.
Anyone have sugestion ??
P.S. I can't change the database and excel codepage settings
I know this is very simple, and I've done it before, but now I can't remember how to do it and I keep failing to do it correctly. I want to format a 6 digit number like this: 00-00-00. The number is returned from the database as 6 digits, and I just want to add the dashes.
I tried putting ##-##-## in the Format cell in the properties of the textbox (which I thought worked before) but it's not doing the trick. What am I doing wrong here?
i am trying to build a database that contains a coloumn of phone number but i reallly dont know where and how i will be able to give a specific format for phone numbers.. or anything else any common type of information that may have a format..
Will i use Visual Web Developer or SQL Server Management or whatever else to arrange my phone number coloumn?
We are looking at developing a new app with a SQL Server db. As such, we're all newbies.
What is the maximum number of columns I can put in a SQL Server table?
Thanks in advance.
Bob Anderson
Summary: Maximum number of records per second that can be inserted intoSQLServer 2000.I am trying to insert hundreds (preferably even thousands) of recordsper second in to SQLServer table (see below) but I am getting thefollowing error in the Windows Event Viewer Application log file:"Insufficent Memory......"And very few records were inserted and no errors where sent back viathe JDBC.By removing the indexes on the table we have stopped getting the errormessage and have managed to load the table at 300 records per second.However I have couple of questions:1) Are the indexes definitely to blame for this error and is thereanyway of getting around this problem i.e. keeping the indexes in placewhen inserting?2) How should I configure SQLServer to maximise the speed ofinserts?3) What is the limiting factor for inserting into SQLServer?4) Does anyone know of any metrics for inserting records? At wantpoint should we consider load balancing across DBs.I currently populate 1.6 million records into this table. Once againthanks for the help!!CREATE TABLE [result] ([id] numeric(20,0) NOT NULL,[iid] numeric(20,0) NOT NULL,[sid] numeric(20,0) NOT NULL,[pn] varchar(30) NOT NULL,[tid] numeric(20,0) NOT NULL,[stid] numeric(6,0) NOT NULL,[cid] numeric(20,0) NOT NULL,[start] datetime NOT NULL,[ec] numeric(5,0) NOT NULL,)GOCREATE INDEX [ix_resultstart]ON [dbo].[result]([start])GOCREATE INDEX [indx_result_1]ON [dbo].[result]([id], [sid], [start], [ec])GOCREATE INDEX [indx_result_3]ON [dbo].[result]([id], [sid], [stid], [start])GOCREATE INDEX [indx_result_2]ON [dbo].[result]([id], [sid], [start])GO
View 5 Replies View RelatedNow, I checked and verified that my backup version of SQL Server is the same as the version installed on the computer I'm restoring too.
I have SQL Server on a production machine that I backed up and want to test a full restore on a dev machine to make sure it will work when I need it to.
Now that I've run the restore command on my tape backup and go to restart the SQL server service I receive:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
I'm afraid I don't understand why this is happening. If the builds are the same, then shouldn't restoring the MASTER database have worked normally and I'd be able to restart the service now?
Any thoughts or suggestions?
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('’')
with 2 Unicode characters
= 0x2300
’ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?’"
’ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'’')
and select again voila i see
"’"
Does anyone have an idea?
Thanks
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies View RelatedI have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
Hello Expert!
I have 2 Database Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer using DTS Ive done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableΒ
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies View Related