Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Converting INT Datatype To BIGINT Datatype


HI,

I have a table with IDENTITY column with the datatype as INTEGER. Now
this table record count is almost reaching its limt. that is total
record count is almost near to 2^31-1. It will reach the limit with in
another one or two months.

In order to avoid the arithmentic overflow error 8115, we would like
change the datatype from INT to BIGINT. we hope this will solve our
problem.

How do I approch this datatype conversion?. Since the data count is
huge, that leads to a long down time of database.

we need better approach or solution for this problem?. kindly give me
a better solution that will reduce the total downtime of the production
database.?.

Regards


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Bigint Datatype
I am using bigint as a primary key. What happens when bigint reaches the maximum number that it can hold?

The Yak Village Idiot

View Replies !   View Related
Change Datatype From Varchar To Bigint Not Working
Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.

View Replies !   View Related
Change Datatype From Varchar To Bigint Not Working...
Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

[This example is just for the columnname of 1 table, I am using multiples one on each line]

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.

View Replies !   View Related
Converting Datatype
I need to convert a text field (which contains only numbers) into an int field. What would be the easiest way to do so. SQL 6.5. There are about 100, 000 rows in the db.

Thanks.

View Replies !   View Related
Converting Datatype
Hello!
I have a column tData with char(10)datatype - dd/mm/yyyy.
I try to convert them to datetime, here is statement:

select convert (datetime(103),tData)
from Test1

As result I have got error message:
conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

What I'm doing wrong?

Thanks a lot.

View Replies !   View Related
Numeric Datatype To Ssis Variable Datatype Conversion Problem


Good afternoon,

I have an issue with an ssis variable datatype.

The scenario is as follows:

I havea stored procedure:


PROCEDURE [dbo].[sp_newTransaction]



@sourceSystem varchar(50),

@txOut NUMERIC(18,0) OUTPUT

AS

insert into scn_transaction (sourceSystemName) values(@sourceSystem);

SELECT @txOut = @@identity


Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).

I execute the stored proc with the following sql with an OLE DB connection manager:

exec sp_newTransaction ?, ?

The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:

User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1

The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.

At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.

I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.

Thanks much,

B

View Replies !   View Related
Converting Data From One Datatype To Another.
I am populating tables with Columns of fixed length (of a text format)
from a different table with some Columns of Money ( DataType).
It would not allow me to populate these Columns.

Can anyone please give me a hint?????

Thanks in advance.

M. Khan

View Replies !   View Related
Converting From Nvarchar To Money Datatype
Hi there,I have a table named Action. This table has a column InPrice with datatypenvarchar(12). I want to change its datatype from nvarchar(12) to money. Ibrowsed through the values and removed any dots. Th column now has onlynumeric values (and commas for decimal values such as 105,8). When I try tochange the datatype from nvarchar to money, following mesage is displayed:ADO error: Cannot convert a char value to money. The char value hasincorrect syntax.How can I solve this problem? I cannot figure out which values are causingthis error.Thanks in advance,Burak

View Replies !   View Related
Casting Or Converting Smallint Datatype To Datetime
A SQL Server 2005 db has three date related-columns (MonthGiven,
DayGiven, YearGiven) each as smallint datatype.  I would like to
create a DocDate column (datetime datatype) that combines the data from
the three existing date-related columns.  I have tried casting and
simple concatentation without success.  ALTER TABLE Details ADD DocDate DateTime NULL

UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting but
have been unable to implement info I have found in the SQL Server
Developer Center in my situation.

View Replies !   View Related
Trouble Converting Datatype For Database Insert
Hi,
I need to take a value from a textbox and insert it into a field in my database which takes decimals. My problem, no matter what I try I cannot convert the value so that the database will accept it. This all happens when the submit button is hit on my webpage. Here is the cmdSubmit_click sub code:

Dim surveyNum As Decimal = Decimal.Parse(txtSurveyNum.Text, Globalization.NumberStyles.Number)
myCmd.CommandText = "INSERT INTO survey(ID) VALUES('" & surveyNum & "')"
myCmd.Parameters.Add("surveyNum", SqlDbType.Decimal)
myCmd.Parameters("surveyNum").Value = System.Convert.ToDecimal(txtSurveyNum.Text)

myConn.Open()
Try
myCmd.ExecuteNonQuery()
lblMessage.Text = "Record successfully updated"
Catch
lblMessage.Text = "Query error: " & Err.Description
End Try
myConn.Close()

Thnx in advance, any help would be greatly appreciated.

View Replies !   View Related
Conversion Failed When Converting The Nvarchar Value To Datatype Int
Exception in one of the stored procs:

Conversion failed when converting the nvarchar value to datatype int.After moving database from 2000 to 2005.
Using backup and restore.

Note I had the same issue after restoring the production database on my local server SQL2000->SQL2000.

The problem was solved by restoring master and msdb from the production. But I can't do this on the SQL2005.

This is not a collation issue as I have ensured collation is the same on all databases including the system ones.

The database in question makes extensive use of user defined data types (Which I have recreated on the destination server).

Can anyone please help?

Raf




View Replies !   View Related
Converting/Casting Strings Into Datetime Datatype
Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?


Thanks!

View Replies !   View Related
Convert Char Datatype To Datetime Datatype
Database is SQL Server 2000

I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.

What is the syntax to convert char(6) to datetime?

Thank you in advance.

View Replies !   View Related
Modify Nvarchar Datatype To Datatime Datatype
Hi,

I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.

I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.

I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.

UPDATE users SET DATE2 = DATE.. But it also faild,..

How can I modify the column?

Thank you.

View Replies !   View Related
Oledbcommand Converting Date Field To Character Datatype
im usoin sqlbuklcopy class to migrate dbf files into sql server tables, but i got a proble and its because the format of a field named birthdate  its mm/dd/yyyy and i got this
Cadena = "select SITE_ID ,PATIENT_ID , LOCAL_ID, " & _                    " BIRTHDATE , GENDER  ,  LAST_NAME  , FIRST_NAME from patient "
then i open mi oledbcommand
        Dim cnSource As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Origen & ";Extended Properties=DBASE IV")        Dim cmdSource As New OleDbCommand(Campos, cnSource)
and throw me an exception referring the format of birthdate field...how can i convert in my select statement the field birthdate to a character datatype?
 
 

View Replies !   View Related
Syntax Error Converting Varchar Value '/' To A Column Of Datatype Int
Hi,
Can anyone please help me with the syntax for this query please. Error is "syntax error converting varchar value '/' to a column of datatype int"
Query:

Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label,((select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ) + ' /' + (
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0)) as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID


Thanks...

View Replies !   View Related
Error Converting DataType Forn Varchar To Smalldat
Getting error
Converting DataType forn Varchar to smalldatetime
when running a job (in SQL Server 2005 ) which calls a SP in the step...
this worked fine in SQL Server 7

the step is like this ....

exec spGet_Prism_Sales_History 'ABC', '2006-09-01', '2006-09-31', 1

View Replies !   View Related
Text Datatype Vs Nvarchar Datatype


Hi guys..

i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...

and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...

Thanks and looking forward.-MALIK

View Replies !   View Related
Error Converting Data Type Nvarchar To Bigint
I getting the above error can someone please help me solve it, here is the code:
public void InsertHost()    {        // TODO        // - Call stored procedure to write to a log file writeToLog        using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))        {            SqlCommand cmd = new SqlCommand("writeToLog", cn);            cmd.CommandType = CommandType.StoredProcedure;            cmd.Parameters.AddWithValue("@pAction", "action");            cmd.Parameters.AddWithValue("@pHostName", "Hostname");            cmd.Parameters.AddWithValue("@pUserNUm", "requestorID");            cn.Open();            cmd.ExecuteNonQuery();        }    }
Here is the storedprocedure:
ALTER PROCEDURE dbo.writeToLog(@pAction varchar(10), @pUserNUm bigint, @pHostName varchar(25))AS INSERT INTO dbo.hostNameLog                      (action, requestorID, HostName)VALUES     (@pAction, @pUserNUm, @pHostName)
Here is the table:
HostName - varchar, action - varchar, requestorID - bigint
I can't seem to find the error.

View Replies !   View Related
Error Converting Data Type Varchar To Bigint.
I have the following query which has been giving me headaches for days :  SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size],  Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified],   CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads],  UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At]  FROM #TempTable  WHERE id > @firstitem AND id < @lastitem  ORDER BY   CASE  WHEN @sortcolumn = 'Service Catalog Code' THEN SCCode    WHEN @sortcolumn = 'File Name' THEN FileName    WHEN @sortcolumn = 'Extension' THEN FileExtension    WHEN @sortcolumn = 'Size' THEN FileSize    WHEN @sortcolumn = 'Author' THEN Author    WHEN @sortcolumn = 'Last Modified' THEN CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120)    WHEN @sortcolumn = 'Last Accessed' THEN CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120)    WHEN @sortcolumn = 'Downloads' THEN TimesAccessed    WHEN @sortcolumn = 'Uploaded By' THEN UploadedBy    WHEN @sortcolumn = 'Uploaded At' THEN CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120)   END ASCWhen my @sortcolumn parameter is either FileSize (BIGINT) or TimesAccessed (BIGINT) then the query returns the data without any problem. However, if I use a different @sortcolumn value such as "Author" then I keep getting "Error converting data type varchar to bigint."A solution that seems to work is to drop CASE and use lots of IF statements instead. I don't prefer to do this because it requires me to repeat the same select statement for each and every possible @sortcolumn value!Does anyone know how to solve this? Help!

View Replies !   View Related
Error Converting Data Type Varchar To Bigint
I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ',' into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.



ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]

@NozzleID int,

@ParserString varchar(MAX)

AS

BEGIN



DECLARE @NextPos int

DECLARE @LoopCond tinyint

DECLARE @PreviousPos int

DECLARE @FlgFirst bit

DECLARE @QueryCondition varchar(MAX)

SET @LoopCond=1

SET @NextPos =0

SET @FlgFirst=0

SET @QueryCondition=''

WHILE (@LoopCond=1)



BEGIN

--Retrieving the Position of the delimiter

SET @NextPos =@NextPos + 1

SET @NextPos = CHARINDEX(',',@ParserString, @NextPos)



--Retreiving the last substring

IF(@NextPos=0)

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

BREAK

END



--Retrieving the individual substrings

If @FlgFirst=0

--Retreiving the first substring

BEGIN

SET @FlgFirst=1

PRINT SUBSTRING(@ParserString,1, @NextPos-1)

SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos-1) AS bigint)

SET @PreviousPos = @NextPos

END

ELSE

--Retreiving the internmediate substrings

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

END

END

print 'ErrorId <>' + @QueryCondition

UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails]

SET ErrorRectifyDateTime=GETDATE()

WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)

END

View Replies !   View Related
Arithmetic Overflow Error Converting Expression To Data Type Bigint
I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:

USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,

vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO


When the wizard gets to the step where it is creating the publication, I get the following error message:


Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).

I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.

Thanks in advance ...

David L. Collison

Any day above ground is a good day.

View Replies !   View Related
Strange Problem: Error Converting Data Type Varchar To Bigint.


The strange in this problem is that i got this problem suddenly and before all was running without any error.

The script that cause theerror is in the trigger for insertion on a table, there is somewhere where i convert from varchar to bigint, but all seems fine to me i printed the varchar variable and the conversion looks valid, also i included several print statement in the script for debugging purposes and i feel everything is right and there shouldn't be any error.

I don't think anything changed in my db, so i wonder where this problem is comming from ?
this is the code:



Code Block
-- Private Sending start
declare @tmpmsg nvarchar(200)
set @tmpmsg = ltrim(rtrim(@message)) -- Remove leading and trailing blanks
if (left(@tmpmsg,4))='خاص' and len(@tmpmsg)>6
begin
declare @msg_error nvarchar(70)
declare @msg_error1 nvarchar(70)
declare @msg_error2 nvarchar(70)
declare @msg_error3 nvarchar(70)
declare @sender_id bigint
set @msg_error1 = 'الرجاء إرسال كلمة خاص متبوعة برقم مل? الشخص المطلوب ثم الرسالة الشخصية'
set @msg_error2 = 'الرجاء تسجيل مل?ك قبل إستخدام خدمة الرسائل الخاصة'
set @msg_error3 = 'إن مل? الشخص المطلوب غير موجود'
-- The message should not go to chat filtration
update received_in set sent = 3 where reqid = @id
-- if the sender profile doesn't exist, send him a message indicating that.
SELECT @sender_id = profile_id FROM profiles where msisdn=@msisdn
if @sender_id is not null
begin
set @tmpmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-3))
-- Now the message looks like '12345 this is a test message'
declare @profileid bigint
declare @strprofileid nvarchar(50)

declare @posSpace int
set @posSpace = charindex(' ', @tmpmsg)

if @posSpace = 0 -- Not found (the message format is like this 'pri 3432434 ' or 'pri dfsdffsd '
begin
set @msg_error = @msg_error1
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
end
else
begin
set @strprofileid = left(@tmpmsg, @posSpace-1)
-- first char separating profileid and util message
if isnumeric(@strprofileid) = 1
begin
set @profileid = @strprofileid
declare @utilmsg nvarchar(200)
set @utilmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-@posSpace))

-- Right message format
print 'Profile id=' + ltrim(str(@profileid))
print 'Message=' + @utilmsg
declare @recmsisdn varchar(16)
select @recmsisdn = msisdn From profiles where profile_id=@profileid
-- if the receiver profile doens't exist, send him a message indicating that
if @recmsisdn is not null
begin
-- All fine: msg format, sender and receiver
if left(@recmsisdn,3)='000'
Insert into Inbox (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
else
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
end
else
begin
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
set @msg_error = @msg_error3
end
end
else
begin
set @msg_error = @msg_error1
end
end
end
else
-- The sender profile is not registered with us
set @msg_error = @msg_error2
if @msg_error is not null
begin
print @msg_error
if left(@msisdn, 3) = '966' -- We can only send replies to KSA mobiles
INSERT INTO cgi_sms_services.dbo.cgi_sms_in(msisdn, sms_code, message, reqport, smstype)
VALUES (@msisdn, '1000000002', @msg_error, 86367, 'wcsms')
end
end -- Private Sending End



View Replies !   View Related
XML Datatype
I'm using a while loop to build a SQL insert string based on values in nodes of an XML object. Here is an example of what I'm attempting to do:&nbsp;<pre class="coloredcode"><span class="sqlkwd">SET</span> @SQL = <span class="st">'insert into dbo.stuff select '</span>

<span class="sqlkwd">WHILE</span> @i &lt; 10
<span class="sqlkwd">BEGIN</span>
@i = @i + 1
@SQL = @SQL + @<span class="sqlkwd">xml</span>.value(<span class="st">'/stuff/item'</span> + @i ,<span class="st">'varchar(50)'</span>) + <span class="st">', '</span>
<span class="sqlkwd">END</span></pre>&nbsp;This however, is incorrect because the xml.value method only accepts string literals as parameters. Can any one think of a more clever way to accomplish this task? Thanks immeasurably.

View Replies !   View Related
Should I Use XML Datatype
Hello All,
My queries are as follows:
 
1)How much memory is consumed by the XML Dataype column when initially?
2)In what scenario should we XML Datatype?
                       





Personal Information


Key

Name

Address

FriendsList

BankAccounts

PhoneNo


 

 

 

Name

PhoneNo

BankName

AccountNo

 









 
If I have a scenario like above which option should I use?
 
Option 1: Make three different tables Named Main, FriendsList and BankAccounts
 
Header table named Main        





PersonalKey

Name

Address

PhoneNo


 

 

 

 
 
FriendList table





Key

PersonalKey

Name

Phone


 

 

 

 
 
BankAccounts table





Key

PersonalKey

BankName

AccountNo


 

 

 

 
 
Option 2: Since I know for a particular Person there may have very little Friends and bank accounts. Means for a particular row in Main table have corresponding 5-10 rows in FriendsList table and BankAccounts table. So, I am creating single table instead of three like below.
 





 

 


PersonalKey

Int


Name

Varchar(50)


Address

Varchar(250)


PhoneNo

Varchar(20)


FriendList

XML


BankAccounts

XML
 
3)In the nutshell I want to know is it advisable to replace the tables’ small in size with the XML datatype?
 
4)I also want to know what SQL Server does behind the scene while in doing operations (Insert/Update/Delete) with XML Dataype.
 
5)Will it degrade the performance? What other aspects are there related to XML datatype?
           

Best Regards
Kausar
 
 

View Replies !   View Related
What Datatype??
I have a message box or multiline textbox on my form and I am storing into an SQL DB. I currently have it as a VARCHAR, but that doesn't seem to store the carriage returns. Do I have to store them as a nvarchar, text, ntext or something else to make the returns show? I am pulling them out of the DB and showing them in a label where I replace the with a <br>.
 
tempMessage = Dr("Message")
message.Text = tempMessage.Replace("", "<br />")

View Replies !   View Related
Which Datatype Should I Use?
I have a USERS database table with a column called IsValidated.  It identifies if a user has been validated.
I'd like to know which datatype would be the most efficient for this column.
Our DBA made it a varchar(1) column.  Would using int be better?
Please advice.

View Replies !   View Related
What Datatype To Use For PK?
Over the years I have always used the decimal(18,0) as the datatype forprimary keys. Aside from the number of significant numbers involved,would BigInt type be better for performance or is decimal(18,0) still okay.--Don VaillancourtDirector of Software DevelopmentWEB IMPACT INC.phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.comWeb Impact Inc. <http://www.web-impact.com>This email message is intended only for the addressee(s) and containsinformation that may be confidential and/or copyright.If you are not the intended recipient please notify the sender by replyemail and immediately delete this email.Use, disclosure or reproduction of this email by anyone other than theintended recipient(s) is strictly prohibited. No representation is madethat this email or any attachments are free of viruses. Virus scanningis recommended and is the responsibility of the recipient.

View Replies !   View Related
Datatype
I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying toinsert the date and time into a SQL database by using hour(now). I amhaving a hard time trying to figure out which datatype to use in SQL tostore this value. I have tried using datetime, char, nchar, text andnothing seems to work. Anyone have any ideas? Thanks!Regards, :)Christopher Bowen

View Replies !   View Related
Sql Datatype Help
hello,i m having a problem with the sql database.it does not accept 0(zero)asthe first number in the integer field.whenever i insert 01 it takes itas 1.similarly for 02 and so on till 09.i want 01. can anyone suggesthow to get.i m doing this on sql server.does sql server support?is thisthe problem of sql server?

View Replies !   View Related
The Datatype
hello !I got some problems here. I have an attribute that determines the unitof something,e.g. the size of using "mm" , the length of using "seconds"and something others may using "n-page", so ,which data type should Iuse. the "nvarchar" or others?thank you very much!*** Sent via Developersdex http://www.developersdex.com ***

View Replies !   View Related
Datatype
I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible???

View Replies !   View Related
64 Bit Datatype
Is there a datatype that I can use to store a 64 bit unsigned int? I'm using SQL 6.5. Was thinking of using the standaed Interger datatype.

View Replies !   View Related
64 Bit Datatype
Is there a datatype that I can use to store a 64 bit unsigned int? I'm using SQL 6.5. Was thinking of using the standaed Interger datatype.

View Replies !   View Related
Bit Datatype
i have a field in table x withe datatype bit.
if i store say value 64. it will store it as 1. then if i store another no. say 43 and so on.
then how do i retireve those values from table. say i want ot retrieve 64 the how will sql recoginze i want to retireve 64 as it is stored as 1 in the datafield

View Replies !   View Related
Datatype
what date type is bet used when there is some occaions the user may enter 2.5 etc

View Replies !   View Related
About Datatype
fathima writes "what data type is used to store unlimited content in sql server.
Actually i need to store a text book
pls reply i need it for a proejct work."

View Replies !   View Related
Datatype
Hello Harish,

The data type is a varchar(10)

The Loan Number looks something like this:

254009411

Thanks,

Kurt

View Replies !   View Related
Datatype
lakshmi writes "i want to store more than 8000 chars in varchar field.what can i do?"

View Replies !   View Related
Sql Datatype
Hi,



Supposed the variable @number calculated and resulted as 0.75. I declared it as a FLOAT but it keeps giving me '0'.



Set @number = 6/8



I get the zero result. What datatype should I use for the correct result ?



Thanks,

Dror.

View Replies !   View Related
Datatype
Hi guys

What are alpha values and what datatype stores them.

View Replies !   View Related
SQL Datatype Issue
Hi,Sorry if this isn't the right forum...I'm getting a "System.ArgumentException: Column requires a valid DataType" on the following line of code: dcUserAnswer.DataType = System.Type.GetType("SqlTypes.SqlInt32") I'm using SQL Server 2003 & ASP.NET 1.1  Thanks,Jens 

View Replies !   View Related
'Image ' Datatype
Hi,I created a table with one column being that of an 'image' data type. The problem is I don't know  how to insert an image into that column. Pleas Help.

View Replies !   View Related
DataType Money
Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00
Thanx

View Replies !   View Related
Datatype Problem
Hi Everyone, In C# file: bool abc;  (it is working)But in sql file:  (bool is not working. What datatype should use for bool in the sql file?)CREATE TABLE sb_payment_history ( sb_dispensing bool NULL       <================ not working because bool is not known. )GOThanks,May

View Replies !   View Related
DataType Problem
   Hi,
I  want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?
Thanks in Advance.
 
 

View Replies !   View Related
OpenXML DataType
Hello, I am using OpenXML to do inserts/updates on Sql Server.
I have a problem with data type, for example, check this example:
DECLARE @record VARCHAR(1000)
SELECT @record = '<tests> <test>  <NumberofChildren></NumberofChildren>  <Name></Name>  <Active></Active> </test></tests>' DECLARE @IndexInXML INT         -- Create an internal representation of the XML document     EXEC sp_xml_preparedocument @IndexInXML OUTPUT, @record           INSERT INTO   [test] SELECT   [NumberofChildren], [Name], ISNULL([Active],null) FROM OPENXML   (@IndexInXML, '/tests/test',2) WITH  (  [NumberofChildren] INT, [Name] VARCHAR(50), [Active] BIT )
  -- Remove in-memory table from memory      EXEC sp_xml_removedocument @IndexInXML  I have a table called test, it has the followig fields:1- ID2- NumberofChildren3- Name4- ActiveAll fields but ID are asisgned "Allow Null"I want to add data to this table, but if the value coming from xml is empty, I want to have null, in NumberofChildren, Name, Active.When I try my script, if the data is null, NumberofChildren with 0 value, Name is nothing, Active is 0,How can I make the INT and BIT datatypes have NULL when the input is empty in the xml? Can you helpthank you

View Replies !   View Related
DataType Conversion
Dear All:
I am in the process of developing a code generation tool to generate automatically:
1- Business Layer objects
2- Object Layer objects
3- Data Layer objects

The code follows the same technique used in IssueTracker Starter Kit.

I faced somehting wierd today while trying to convert between SQL Data types to C# data types:

Check the image please, the problem is that, different value number is being given to each column type, by using both:
syscolumsn.type and syscolumns.xtype,
which one to use ? which is the best used to convert to C# ?
Are there any place where data types of SQL Server are being converted to C# data types ?

check the pic here please SQL DB

Thanks a lot

View Replies !   View Related
Datatype Performance
Hi, I'm a webmaster of  http://www.jivejewelry.com. Somehow the website seems slows. The developer told me that a datatype in the database design could be causing the problem. I don't believe it that is possible. Is this actually possible? Please help.
 

View Replies !   View Related
DataType Conversion Using WHERE IN ( )
I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@myList varchar(200)


SELECT column1
FROM table1
WHERE table1.ID IN (@myList)



When @myList is a single value, I get no errors. However, when @myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--Colonel

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved