Question About Converting Bigint Field To Int Field
Jan 20, 2006
We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.
What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.
i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int i need to know about the performance and difference will it affect the performance much and second will i have to change any code like delete,update
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field. Shouldn't they be 0's and 1's instead???? What am I doing wrong? What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end. Thanks for any help.
I have been asked to investigate the feasibility of converting an Invoice Number field from 6 to 7 digits, and allowing alpha characters. This means CHAR. The Invoice Number is not used outside of the application, but changes would have to be made to the database, and VB code that runs the app. Any ideas on how to proceed?
Any responses would be greatly appreciated! Thanks
How can I create a field in a view that takes a date field andconverts it to a month?For instance, if I have a field called "CreatedOn" with a date of'2/22/2007', how can I create another field called "Month" that willsay "February"? Is there a built-in SQL function that does this (likeusing CONVERT) or do I need to write my own?Thanks!Lisa
I have a table with over a million rows and one of the fields containsamounts of money in text format.What is the most efficient way of converting this field to a numberformat that I can sum on?Regards,Ciarán
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50)) I would like to have it -> 11/29/00 (datetime)
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
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.
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!
This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]
If I run, SELECT Â * FROM Â Â dbo.tblIDNumber WHERE Â IDN_IDNumberTypeStaticValue = 33 Â Â Â Â AND IDN_Removed = 0 Â Â Â Â AND CAST(IDN_Number AS BIGINT) = 1
SQL Server give me the error: Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!
UPDATE dbo.tblIDNumber SET Temp = CAST(IDN_Number AS BIGINT) WHERE Â IDN_IDNumberTypeStaticValue = 33 Â Â Â Â AND IDN_Removed = 0
To workaround, I ran the query,
UPDATE dbo.tblIDNumber SET IDN_Number = '123' WHERE Â IDN_IDNumberTypeStaticValue = 33 Â Â Â Â AND IDN_Removed = 0
and then I ran the first query, and SQL Server does NOT give me the same error -Â Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????
finally, I found the root cause to be an index that the first query uses :
CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber] ( [IDN_Removed] ASC, [IDNumberCode] ASC ) INCLUDE ( [IDN_Number], [IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY] GO
If I remove the index, the first query works without the error.
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)
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 need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
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 am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine. CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red ELSE '3' --White END END else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8Â Â Â Â Â Â *Correct 1234567-9 -> 1234567-Â Â Â Â Â *Wrong 123456789 -> 123456789Â Â Â *Correct 1234567#9 -> 1234567#Â Â Â Â *Wrong 1234567a9 -> 1234567a9Â Â Â *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
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.
The strange in this problem is that i got this problem suddenly and before all was running without any error.
The script that cause the error 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:
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
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?