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 ?
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?
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.
Quick question here. I am working with a database which uses Binary(8) as an id(ie, contact_id is binary(8), and connects 2 tables). I can already extract the binary data (in the form 00-00-00-00-00-00-ab-cd). I know that there exists a contact_id 0x0000000000001989 (hex) however, when I attempt this query...
"SELECT * FROM Support_Incident where Contact_Id = '0x0000000000001989'". I get no results. What have I done wrong?
Hi all, There are several columns called enabled with a char datatype in my database. One enabled column per table. These columns either have a value of T or F (true or false), depending on whether they're enabled or not. I want to change these columns to a bit datatype and insert the relevant value of true or false... I guess the best way to do this is to add a new column to a table with a bit datatype, and based on the value in the current enabled column, insert TRUE or FALSE. Anyone ideas on the best way to accomplish this? Thanks.
I'm designing a database that will not be that large (I would be surprised if it ever surpassed 50,000 rows across all tables), but will be accessed quite often, so I am doing my best to optimize its structure, such as doing 3NF, selecting appropriate data types, etc.
I have a few columns that will contain numeric data (such as an invoice number (from an external source) or location ID). However, one of my classes in college was about database design, and we were taught that if you won't be doing mathmatic computation on a field (such as the invoice or location fields I mentioned earlier), then you should use a string literal type (char, varchar, etc.)
Unfortunatly, the professor did not explain much as to why this should be done. From the standpoint of semantic analysis, these types of fields are probably more labels than they are numbers. However, I don't find that very convincing (or even helpful).
In short, my question is that given a column holding numeric data that isn't worked on in a mathematical sense, is it really better to mark it as a string literal than a number? Any articles or studies I can read relating to this?
I would think that comparisons would be faster with int, as well as data storage (though, as mentioned, that's not as big of a concern). Sadly, Google doesn't have many helpful resources. (Lots of stuff on char vs varchar, though.)
I read the topic from JROdden and this case is similiar but...
I got several varchar fields with values like 1.2 1.3 ... these I can covert with select CONVERT(dec(5,2), fieldname) as fieldname
In fact I also solved undefined- and NULL-values with. CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,
But now there are values like 1,4 and these ones neither CONVERT nor CAST will handle.
I tried the SELECT DISTINCT KMPAUSCHALE FROM extr_INTFIRMA WHERE (isnumeric(KMPAUSCHALE) = 1)
and get 0,40 0.25 0.30 and so on...
The error is: [Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))
I think the easiest way would be to insist on higher data quality but I also would like to solve this interesting challenge.
Thanks for any hints
By the way, I followed rudys link to http://rudy.ca/afdb.html and now I know how I could protect myself !!!!
There must be a voice in my head saying: Try the db-forum, try it and stay happy... ;-)
I am trying to run the following query:ALTER TABLE dnb_profileALTER COLUMN [family update date] datetimeand I keep getting the following 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.The statement has been terminated.Can anyone tell me how I can do this successfully??Thanks,Connie SawyerFoley & LardnerJoin Bytes!
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 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.
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
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
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!
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.
Trying to get this query to work, converting a binary version string to human readable output but somehow it doesn't work?
/* Version number binary from daily registy */ DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B; /* Inline Tally for parsing the binary string */ ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) ,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
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
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.
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 ?
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 ,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 using DTS to import a DB2 table from the mainframe and export the table in text format to a shared folder. I want to convert two fields to a date format yyyy-mm-dd. RELSE_Date is in this format and Updtts is a timestamp coming from the mainframe. The text file is all vchar. In dts here is my query pulling from the mainframe SELECT A.PROD_ORDER_NUMBER, A.DYE_SEQUENCE, A.STYLE, A.COLOR, A.SIZE, A.STATUS_IND, A.STATUS_CODE, A.QUANTITY_REC_DC, B.SHIP_OTFQ_QTY, A.MRP_PACK_CODE, A.LABELS_PRINTED, date(A.RELSE_DATE) as RELSE_DATE, date(A.UPDTTS) as UPDtts, A.LOCATION FROM DB2.WP1_PO_CUST_REQ A, DB2.WP1_DYE_LOT_REQ B WHERE A.PROD_ORDER_NUMBER = B.PROD_ORDER_NUMBER AND A.DYE_SEQUENCE = B.DYE_SEQUENCE AND A.STYLE = B.STYLE AND A.COLOR = B.COLOR AND A.SIZE = B.SIZE AND (A.PROD_ORDER_NUMBER LIKE '__K____') When I parse the query it accepts it. When I run the DTS I get an error stating (SQL STATE 220077 SQLCODE -180) The sting representation of a datetime value has invalid syntax. Does anyone have a suggestion on how to convert these fields before the text file is exported or another output format that is similar to .dat in comma delimited format? Thank you.
Will it be possible to convert a varchar data type column (having values that are numbers or null only) to a numeric data type column having the number values and the null values not being replaced by 0.
Col A Col A (varchar) (int or numeric) 123 123 124 124 NULL 0 <expected blank> 125 125
I tried using CAST(Col A as int) and it converts the NULL to 0. Will CAST(ISNULL(ColA,'') as int) work? The reason for this requirement is the value 0 will give different meaning to the data. Any insights will be appreciated.
I have a table named 'Table1' which contains a column 'Name'. The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
I have a table named 'Table1' which contains a column 'Name'. The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
Can anyone tell me how to calculate datatype conversion times in SQL Server 7? I have a varchar (15) field that I tried to convert to integer using the table design GUI in Enterprise Manager. The table holds about 72,000,000 records about 1k apiece in size.
It's been running for about an hour now with no seeable results. In Performance Monitor I don't see any page reads happening, so is this indicative that the process died? Enterprise Manager is no longer responsive - even if I open another session.
I'd like to know how long I can reasonably expect this conversion to take. Also, how can I abort this request safely if I want to?
