There must be a way to convert the binarydata field in the SQL trace output
to text data when the event type is a show plan. SQL Profiler does it but
how can it be done from an imported table?
hello all, I am reading a file that contains textual data (formatted,e.g font=bold,font size=10 etc). After reading the file, i store the data read from the file to a table in Sql server 2005. But in the table, this data is stored like this:
daohORIGINAL TEXTm............
how can i just store the original text of the file in the table and get rid of these boxes? Or when i read this data from table, how to remove these boxes and get only the original text? Thanks in advance. I am using C# FileStream to read the file from disk. VS 2005
How to convert SQL trace files into excel files without doing any work on SQL Profiler / SQL server using any scripting code. Consider that we only have SQL Trace files. What are the steps involved in converting into .CSV format using single "CLICK"
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 Everyone - iam facing a small problem i want to convert an output from the context_info() which is binary to string (or int)
example:
SET CONTEXT_INFO 3
select CONTEXT_INFO() --- it will return 0x0000000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
I have a Data Control Task that has a OLE DB Source and corresponding OLE DB Destination Data Flow Task. In the referenced source table there exists a field defined as a varchar(12), in the corresponding destination table it is defined as a binary(12). How do I perform this conversion?
I tried inserting a Data Conversion Task and assigning the new data type as byte stream[DT_BYTES] and a Length = 12, but this was a bust. Output test is as follows:
Error: 0xC020901C at DFT - Windows, OLE_SRC - Windows [1]: There was an error with output column "Payload" (40) on output "OLE DB Source Output" (12). The column status returned was: "The value could not be converted because of a potential loss of data.".
Hello,I'm trying to decifer the data in the table that stores the data in thebinary format. All numbers are placed in varbinary fields. All I know is theMS SQL 2000 database useing collation SQL_Latin1_General_CP1_CI_AS(default).For example the content of the field is:(0xB4F5000000000000) in unicode and defined as varbinary(8).Are there any procedures that convert the unicode binary (or hexa) numbersback to ascii or readable form?I tried as following but it didn't work.select cast(0xB4F5000000000000 as decimal(8,2))Any help is appreciated,Adam
Hi all,how r u everyone, guys i need a help, i have a interface in my application, adding attachments to issues, ie users can attach a file for a particular issue and if anyone have to view they ve click a link and it will download.iam using filestream and binaryreader concept in that, ie read the file using filestream and then use binaryreader.readbytes method to convert it into byte array and store it in the database in a column with a image datatype as a binary content. FileStream oImg; BinaryReader oBinaryReader; byte[] oImgByteArray; oImg = new FileStream(sFilePath,FileMode.Open,FileAccess.Read); oBinaryReader = new BinaryReader(oImg); oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length); oBinaryReader.Close(); oImg.Close(); this is the code iam using.when i use this its taking so much of time to get uploaded. so what i thought of doing is save the file in a specific folder using some postedfile.saveas(not sure of syntax) and when the user wants to view the file they can just download the file. i can do this by googling but what i want is wat abt the existing attachments in the database. so i need a help to do this........ is it possible to create a query to read the files and convert it into original file and save it in the specific folder or do i ve to create a simple interface to create do it manually , pls someone help me what to do ......thanks in advance.waiting for a reply soon Note: any unclear statement in my question kindly reply meWishes n RegardsVenkat.
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT, @BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125, @BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original], CAST(@SomeNumber AS BINARY(8)) AS [Binary], dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted], @SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float ( @BinaryFloat BINARY(8) ) RETURNS FLOAT AS BEGIN DECLARE@Part TINYINT, @PartValue TINYINT, @Mask TINYINT, @Mantissa FLOAT, @Exponent SMALLINT, @Bit TINYINT, @Ln2 FLOAT, @BigValue BIGINT
WHILE @Part <= 8 BEGIN SELECT@Part = @Part + 1, @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT), @Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0 BEGIN IF @PartValue & @Mask > 0 SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1, @Mask = @Mask / 2 END END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023) END Thanks again Jeff!
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.
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)
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,,,
Hello Dears, I was Maked Pictures table in sql server 2005 with Two Col pic_Id (int) and picture(binary) i need now ro insert image into this table by Asp.net under VB.net Code i have in my form FileUpload and button control and this is my CodeProtected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click Dim content() As Byte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType) StoreBlob2DataBase(content) End SubFunction ReadBitmap2ByteArray(ByVal FileName As String) As Byte() Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName)Dim stream As IO.MemoryStream = New IO.MemoryStream() image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)Return stream.ToArray End Function Sub StoreBlob2DataBase(ByVal content As Byte()) con.Open()Dim cm As New SqlCommand("Insert into Pictures(Pic_Id,picture) values(@id,@pic)", con) cm.Parameters.AddWithValue("@ID", 1)Dim p1 As New SqlParameter p1.ParameterName = "@pic" p1.SqlDbType = Data.SqlDbType.Binary p1.Value = content p1.Size = content.Length cm.Parameters.Add(p1) cm.ExecuteNonQuery() End Sub
when i make run to my website it give me an Exeption in this statement Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName) it tell me that Parameter is not valid i need help about this please with my Best regard khalil
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 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!
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.
Hi Folks, After I inserted a row in my Database (row 27) I started getting this error when I try to insert, update or delete the record in the database. I've searched about the error on google and it says that I should have a field that crossed the limit of characters. I have only the autoincrement field, two varchar fields and a text field, neither one of the varchar fields crossed the limit, they arent even close. I found in google that one solution would be turn the field that is having problems in a text field, but the only field that actually can be causing the problem already is of the text type. The exact error I get on VS 2005 when trying to change something in the row 27 is: "No row was updated. The data in row 27 was not committed.Error Source: .Net SqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated. Corret the errors and retry or press ESC to cancel the change(s)." I need some help guyz, see ya, hugs.
Hi Guys, I'm trying to save the data into 2 table when i click the button. But it pops out this error: String or binary data would be truncated. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.Source Error:
Line 116:Line 117: Sqlinsert.Connection.Open()Line 118: Sqlinsert.ExecuteNonQuery()Line 119:Line 120: Sqlinsert.Connection.Close() I Don't know what it means so i paste my codes regarding the button & the redline Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click 'add the data into shopper
Dim strsqlcmd As String
Dim strName, straddress As String Dim strContact, strEmail, strPw As String Dim strIC As String
' go the add item Response.Redirect("NewPrescItem.aspx") why my sqlinsert.excutenonquery will have this error? and what is this error really means? Thanks in advance