I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512. I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters. How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently? The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated. I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used. How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large. Thanks!
There are two datatypes for storing binary data type in the SQL Server:1. binary - for fixed length binary data2. varbinary - for variable length dataMy question is: how is data inserted into them? Do they have anydelimiters that go into the insert statement like strings and datetimeshave? What format (hex/decimal?) do they accept data in? Can you pleasegive me an insert statement example?
I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
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!
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
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"
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible? I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.
SELECT rsbirthday CAST(rsbirthday AS smalldatetime)
FROM panelists
the error returned is "incorrect syntax near 'rsbirthday'.
I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
Create and populate table: create table maxtable ( tablename varchar (18) not null, [...] )
Try to insert into test3: insert into test3 (name, tbname) select i.name, o.name from dbo.sysindexes i, sysobjects o, maxtable m where i.indid > 0 and i.indid < 255 and i.id = o.id and i.indid = 1 and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
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?
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is
Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc;
try {
dc = dt.Columns.Add("Availability", typeof(decimal)); €¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
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..... ?
Hello, I have a table which uses binary data to store passwords. How do I view the contents of the "binary data" column, ie. the passwords? It just shows it as <binary data>?
We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.
Here is the SQL String that has been executed successfully in Query Analyzer:
When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."
How does one go about getting a graphic image into SQL Server 6.5. For example, let`s say I have a company logo that I want to include in a company profile table to be used on some reports. The graphic is now a .BMP or .GIF or .JPG file.
Hi there, Am working on an archiving system that stores files/images in a column of type Binary. we want to change the front end of this archiving system it was done using asp.net we dont have access to the source code what is the way to retrieve the files from the binary columns? how they store or retrieve the files? Thanx best site for sql,,,
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT (
SELECT MT.MessageId 'MessageId', MT.MessageType 'MessageType', FROM MessageTable MT ORDER BY MT.messageid desc FOR XML PATH('MessageStatus'), TYPE
) FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated. Thanks Jerry oSQLConn.Open()Dim myreader As SqlDataReader myreader = myCommand.ExecuteReader Response.Expires = 0 Response.Buffer = True Response.Clear() Do While (myreader.Read()) Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content")) Loop
Hello All, I am using a stored procedure. In that stored procedure I am passing the name of a table as an argument. and after executing that procedure, it gives me the 'Insert' query of each and every row. So if I have 10 records, then the procedure will give me 10 'Insert' queries with its table values. There is a image datatype in last column of my table. and i am not able to fetch that binary data of that table. Can anybody tell me what is the problem with my stored proce. given below :: ALTER PROCEDURE [dbo].[SP_Generate_Insert_Statements_Testing] @strTableName varchar(128), -- used to specify the table to generate data for @RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements AS
--create a cursor that loops through the fields in the table --and retrieves the column names and determines the delimiter type that the field needs DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME, 'IsChar' = CASEWHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0 WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2 WHEN DATA_TYPE in ('text', 'ntext') THEN 3WHEN DATA_TYPE in ('sql_variant') THEN 4 WHEN DATA_TYPE in ('image') THEN 5ELSE 6 END FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK) INNER JOIN syscolumns sc WITH (NOLOCK) ON c.COLUMN_NAME = sc.name INNER JOIN sysobjects so WITH (NOLOCK)ON sc.id = so.id AND so.name = c.TABLE_NAMEWHERE table_name = @strTableName AND DATA_TYPE <> 'timestamp'AND sc.IsComputed = 0 ORDER BY ORDINAL_POSITIONFOR READ ONLYOPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1) BEGIN IF @@fetch_status <> -1 BEGINIF @SelList = 'SELECT' BEGIN SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 2 THEN @SelList + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' --WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) ''' WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' WHEN 5 THEN @SelList + '''MyImageData''''' ELSE @SelList + ' ISNULL(CONVERT(VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END END ELSE BEGIN SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' --WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) ''' WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' WHEN 5 THEN @SelList + ''',''' + '''MyImageData''''' ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT(VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END ENDIF @IsChar = 5 SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar END
SET NOCOUNT ON --now we need to create and load the temp table that will hold the data --that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(MAX)) INSERT INTO #TheData (TableData) EXEC (@SelList) IF @strImageSQL <> '' BEGIN CREATE TABLE #ImageData (TableData image) INSERT INTO #ImageData (TableData) EXEC (@strImageSQL) END --Cursor through the data to generate the INSERT statement / VALUESDECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR READ ONLY OPEN CR_Data FETCH NEXT FROM CR_Data INTO @tableDataIF @strImageSQL <> '' BEGIN DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FORREAD ONLY OPEN CR_ImageDataFETCH NEXT FROM CR_ImageData INTO @ImageData END
WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2)BEGIN IF @strImageSQL <> '' BEGINPRINT @InsertStmt PRINT 'VALUES ' PRINT '(' PRINT SUBSTRING(@tableData,1,CHARINDEX('MyImageData',@tableData)-2)PRINT @ImageData PRINT SUBSTRING(@tableData,CHARINDEX('MyImageData',@tableData) + 12,LEN(@tableData)) PRINT ')' + CHAR(13) END ELSE PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)
IF @RequiredGo = 1 PRINT 'GO'
ENDFETCH NEXT FROM CR_Data INTO @tableData IF @ImageData <> '' FETCH NEXT FROM CR_ImageData INTO @ImageData
-- IF @TextData <> '' -- FETCH NEXT FROM CR_TextData INTO @TextData END CLOSE CR_Data DEALLOCATE CR_DataIF @ImageData <> '' BEGINCLOSE CR_ImageData DEALLOCATE CR_ImageDataEND
IF @bitIdentity = 1 BEGIN PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF ' END
PRINT '---- ** End of Inserts ** ----' RETURN (0)
=========== End of Procedure ======================= Any help will be appreciated.
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
