First of all, I must state that I'm new to SQL Server, but have been a
long-time software guy for quite some time. Please excuse the potential
simplicity of the solution to my problem.
I create a blank database (TestDB) under my server machine (SQL Server 2000
Standard)... I then create a new table (Run_Number) with only one record in
it.
Field(1): Next_Run_Number (bigint), identity w/ seed 1 and increment 1, no
nulls, PK
I can successfully save this new table to disk and everything seems fine...
problem comes up when I go back into the table and change the datatype from
(bigint) to (int). I try and save changes and I get the following error
prompt:
'Run_Number' table
- Unable to create index 'PK_Run_Number'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
I've checked and there's plenty of disk space available and the dbase is set
to grow by 10% as necessary. Any ideas what could be going wrong? I'm able
to successfully make this change on my other machine running SQL Server 2000
Personal. Thoughts?
Hello,How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV). I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data. EX: "$2,000" >> 2000The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?
I had created a database named "Company-Data" that contains a table named "tblStock". This table contains several columns such as ID, Product, Quantity. The datatype for the column "Quantity" is INT.
I had entered 100 records to the table, now I want to change the datatype for the column "Quantity" from INT to MONEY.
How can we do this without loosing the data that has been entered the the column previously.
Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner
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.
Hi, I have a excel file and i am trying to import zip codes to the database... but the some of the zip codes start with 06902 but the excel file treats them as float but i want to treat them as varchar...
i need to change the production database structures with multi language Support , datatype from VARCHAR to NVARCHAR in all SQL Objects SQL Objects: Tables, Functions, Stored Procedures...Some of production Tables with 15 Croces records with 10 indexes.how to change the entire databases through SQL scripting
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors).
I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message:
Property Value is not valid.
The details are:
Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
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:
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.
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?
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
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...
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script step # 2 - Actual DDL SQL statements step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
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.
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>.
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."
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
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.
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
