I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.
can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.
Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested? Thanks.
Hi I have a table, which contains Char and NChar NOT NULL columns Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used, ALTER TABLE <TableName> ALTER COLUMN <ColName> CHAR NULL ALTER TABLE <TableName> ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated.
But for the same table, the below command executes fine, ALTER TABLE <TableName> ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
Hi, We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.
After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.
Hi All: I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this : PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc but I don't how to select the datatype for them. should I select Char or VarChar ? which one is the best slection ? thans in advanced!
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):
I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).
I tried all the ways i can and still i get the same error..:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type nvarchar to numeric.".
Can we actually do it...?? any help would be appreciated.
I'm scrambling to complete a project and ran into an int to char problem. I'm sure, in my haste, I'm doing something stupid. Here is an example of what I'm trying to do but it doesn't convert as expected. I want the SEQ_NBR2 value to convert from 4 to 0000004.
select right('0000000' + convert(char(7), isnull(a.SEQ_NBR2, 0.0)), 7) fromTXN_HEAD_837 a where a.file_auth_nbr = '1084472388468' anda.seq_nbr2 = 4 and a.sgmt_id2 = 'BHT'
I have data tables that include ZIP code, as char(5). The values looklike integers, but they are padded with leading zeroes to fill out 5characters, '00234'.There are SPs to look up data, with @Zip char(5) among the parameters.Some users call these with integer values, @Zip = 234, and SQL makesthe conversion. Is it necesary to add the leading zeroes in the SP --@Zip=RIGHT('00000'+@Zip,5) -- or would SQL find this match? (234 ='00234'). It looks like the conversion is to '234' and the matchfails.Thanks,Jim GeissmanCountrywide
I'm trying to run the following statement. If the first row of invoie data has a number the invoice for the rest of the rows that contain text come through as null. If the first row of data is text i'm fine. How do I convert the invoice to text as it is being read from the below statement?
Set @tsql = 'insert into #Draw_File_Data (Folder, Vendor, Invoice, Dist_Seq, Draw) select Folder, Vendor, Invoice, [Dist_Seq], [Draw] from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @path + ';'',''select Folder, Vendor, cast(Invoice as varchar(50)), [Dist_Seq], [Draw] from [' + @draw_file + ']'')'
i have another problem.and it's now on converting a char(4) to datetimehere is the situationJ_TIM < F_TIMJ_TIM is datetime while F_TIM is char of 4exampleJ_TIM = 20:30F_TIM = 2030how can i convert F_TIM to datetime so that i can compare them.???thanks
here is my problem: i have a variable @sid_x as binary(16) = 0x4CF254AB0BA5D411AA3E00508BC5C413 and i want to use it as argument in sp_addlogin statement.
select @sqlcmd = 'sp_addlogin "test", @sid = ' + @sid_t /* this doesn't work, because @sid_t is binary... */
select @sqlcmd = 'sp_addlogin "test", @sid = ' + convert (char (20), @sid_x) /* this doesn't work either, because it doesn't convert to binary text */
my question, is there any way i can get @sid_x in follow text format 0x4CF254AB0BA5D411AA3E00508BC5C413 ?
Hi All, I am trying to convert date from a character string field 12 to another database which has smalldatetime. Is there a way to do this. Any help would be greatly appriciated.
on a column DateNew = DateTimei am trying :INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')and i get an error :conversion of char data type to datetime data type resulted in an out of range datetime valueI had never this error before , do you know why ?i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langagesthank you for helping
im trying to convert char (18) data type to decimal (18,6) but it wont let me do it. It gives an arithematic error. what would be other way to solve this problem. Can i do it with float if yes how so? or any other suggested way. Thanks
I have to import data from Excel file to an SQL Server Database. One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18. Can you give me some help for the DTS.
I got this erorr when trying to create my stored proc,
What do i need to fix, and how do i fix it?!!
Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13
Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author: <Zaccheus,Tenchy> -- Create date: <NOVEMEBER,12,2007> -- Description: <Reporting stored procedure,DAILY ACTIVITY,> -- ============================================= CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY] (@Region_Key int=null) AS BEGIN SELECT Region_Key, Null as Customer_Code, Non_Customer_Activities.Question_code, Non_Customer_Activities.Description, Region AS Region, Name AS Territory_Name, Non_Customer_Activities.Que_Desc AS Store_Name, Non_Customer_Activities.Logged_Time AS TheDate, Non_Customer_Activities.response AS Response, Null as is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select QH.[question_code] ,Question_Header.Description ,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time ,SUBSTRING([entity_code],1,5) SR_Code ,[response] ,Territory_Code SR_Territory_Code ,'Not Customer Related' Que_Desc From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code WHERE [entity_code] like '%.USER%' AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07') AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007' ) Non_Customer_Activities ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code UNION ALL SELECT Customer_Activities.Customer_Code, NULL, NULL, Region AS Region, Name AS Territory_Name, Customer_Activities.Customer_Name AS Store_Name, Customer_Activities.Logged_Time AS TheDate, NULL AS Response, is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select distinct time_log Logged_Time ,[entity_code] Customer_Code ,[name] Customer_Name ,Territory_Code Cust_Territory_Code ,MAX(is_Visit_Fg) Is_Visit_Fg From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code INNER JOIN [FSSRC].[dbo].[customer] ON Entity_Code = [customer_code] INNER JOIN [FSSRC].[dbo].[visit] V ON V.[customer_code] = QH.[entity_code] AND V.[period_code] = QH.[period_code] AND V.[cycle_day] = QH.[cycle_day] INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS WHERE [entity_code] NOT like '%.USER%' AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'
GROUP BY time_log ,[entity_code] ,[name] ,Territory_Code ) Customer_Activities ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code WHERE @Region_Key=Region_Key order by 4 END
Hi guys/ladies I'm still having some trouble formatting a select statement correctly. I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database. Here is my select statement cut down to the most basic elements. SELECT commentFROM informix.ipr_stucomWHERE (comment > 70) The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error "Character to numeric conversion error" How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values? I have tried case with cast and isnumeric... I don't think that I have the formating correct. I have also used: WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F')) This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration
Partial Class Admin_emag_insert Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click Me.VersionTextBox.Text = "" End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click Dim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim ConnectionString As String Dim record_date As Date Dim version As String Dim emagSQL As String
'save form values in variables record_date = Record_DateTextBox.Text version = VersionTextBox.Text
'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open() emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _ "values('" & record_date & "','" & version & "')"
'Create and execute the command objDataCommand = New SqlCommand(emagSQL, objConnection) objDataCommand.ExecuteNonQuery() objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @ItemID NVARCHAR(36), @OrderNo NVARCHAR(36), @LineNo NVARCHAR(36), @TAllotedQty Numeric, @EmployeeID NVARCHAR(36), @Trndate datetime ) AS BEGIN By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same) IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
