Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Get Binary Data From Image Column




Hi I have an image column(Spectrum)in a Table (ParticleEDS)which is populated with an array of abunch of INT32's (4 bytes each)

UsingTSQL is there any way that I can read each 4 bytes (convert this to anINT)and return this data for a givenrecord (based on ParticleEDSID).

I know that there are 2048 x 4 bytes that make up the image column.

I would like the output of thequery/storedprocedureto be:

Value
------------
12342
22334
3 3343
.....
20481001


thanks!


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
OPENXML And Binary/Image Data
I seem to be getting very annoying behaviour from SQL Server and was wondering what the correct method should be...I want to store some binary data in the database and I want to use a stored procedure.  I also want to pass some XML to the stored procedure where this XML contains the binary data. I will then use OPENXML to grab the binary data and stick in the table. In order to ensure the safe transmission of the binary through XML I Base64 encode it. Ok so far. The problem is that although OPENXML is supposed to assume that anything marked as Image/Binary will be Base64 encoded it then proceeds to Base64 encode it again and put that result into the database! So what am I missing, am I supposed use a CDATA section or something rather than Base64 encode my XML?

View Replies !   View Related
Uploading An Image And Using An ASP.NET 2.0 Data Source Control Code To Store The Binary Data PROBLEM
I want to use a Stored Procedure (and add Parameters) instead of ADO.NET Code from the following article, but I'm not seeing something; could someone help me out?
Note: It's Visual Basic 2005 code, and that's what I want to use.
 http://aspnet.4guysfromrolla.com/articles/120606-1.aspx And code for article: http://aspnet.4guysfromrolla.com/code/BLOBsInDB.zip
If you look at this article, under the section: Uploading an Image and Using an ASP.NET 2.0 Data Source Control Code to Store the Binary DataIt has the following Code for the DetailsView UploadPictureUI_ItemInserting:
<asp:SqlDataSource ID="UploadPictureDataSource" runat="server"       ConnectionString="..."      InsertCommand="INSERT INTO [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)">  <InsertParameters>    <asp:Parameter Name="Title" Type="String" />    <asp:Parameter Name="MIMEType" Type="String" />    <asp:Parameter Name="ImageData" />  </InsertParameters></asp:SqlDataSource>
I tried to use the following, but It's not correct:
<asp:SqlDataSource ID="UploadPictureDataSource" runat="server"       ConnectionString="..."      InsertCommand="InsertPicture" InsertCommandType="StoredProcedure">  <InsertParameters>    <asp:Parameter Name="Title" Type="String" />    <asp:Parameter Name="MIMEType" Type="String" />    <asp:Parameter Name="ImageData" />  </InsertParameters></asp:SqlDataSource>
How do I simply replace the INSERT INTO Statement with a StoredProcedure, and set the parameters? Currently I have the Insert working, but the image shows up as an image placeholder and that's it.
ps: The stored procedure I use is below, and you might want to download the VB Code at the bottom of the article.ALTER PROCEDURE dbo.InsertPicture
 
(@PictureID int,
@Title varchar (50),@MIMEType varchar(50),
@ImageData varbinary
)
 
ASINSERT [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)
 
RETURN

View Replies !   View Related
Help In Storing Binary To Image Data Type
Hi. how can i store binary data to another field with image data types.


here is my sample code


--note: [CUD_DOCUMENT] and [RES_DOCUMENT] are image data type

DECLARE @CUD_DOCUMENT binary

SELECT @CUD_DOCUMENT = CUD_DOCUMENT FROM CUD_CONTINUOUS_UPLOAD_DOCUMENTS

INSERT INTO [RES_RESUMES](
[RES_DOCUMENT] -- image data type
) VALUES (
@CUD_DOCUMENT)

thnx for the help.

View Replies !   View Related
Accessing Image Files Stored As Binary Data
Hi
When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed.
When I open the images table in VWD  and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table.
Any ideas?
Thanks

View Replies !   View Related
Problem In Converting MS Access OLE Object[Image] Column To BLOB (binary Large Object Bitmap)
Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
 
 
 

View Replies !   View Related
How Can I Copy Data From A Table Containing A Binary Column?
I have a table called 'Articles' whose columns are articleId (int), authorName (varchar) and article (binary). The primary key is articleId. I tried using DTS wizard available in Enterprise Manager 2000,  to import the data from this table into a remote database table, but the data copy failed. I am thinking the binary column is causing the DTS data copy to fail.
If someone could tell me what else  I could do to transfer binary data  to remote database(like an example of a data transfer query),  then that would help me greatly.

View Replies !   View Related
Create A Column With Binary Data Type
Hi, in reference to the article:

http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

I would like to know how to create a 'password' column where the data type is a binary type of length 16
This is my table:

CREATE TABLE Staff
(
Namevarchar (50)PRIMARYKEY,
Password ???? NOT NULL,
Role varchar (50)NOT NULL
)

Please review my code. Thanks in advance for helping me out here!
-Gabian-

View Replies !   View Related
Trouble Loading Binary Data Using DT_BYTES Column
I am using SSISto load a flat file into a SQL Server 2005 database. One of the columns in the flat file data is a 50-byte binary string defined as DT_BYTES which needs to be loaded into a database column defined as binary(50). I am reading the flat file in fixed-width mode, so as to avoid the possibility of the binary data introducing additional unexpected column or line delimiters.

I am developing the package on a Windows XP Pro SP2 machine. The SQL Server 2005 instance is located on a server running Windows Server 2003 Standard SP1.

I am able to run the package successfully from my local machine, importing all columns and records into the database. I can run the packagedirectly from within Business Intelligence Development Studio. I can also deploy the package to the SQL Server machine and run the package that way from my local machine. If, however, I attempt to run the same package from the server, either interactively or using SQL Agent, it chokes specifically on the binary column.

Here is the error message generated when the package is run from the server:


Data conversion failed. The data conversion for column "COL_BIN_50" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

The package successfuly runs from the server if I ignore the binary column in the SSIS package.

Has anyone else encountered this behaviour? Does it seem to beoperating systemrelated?



View Replies !   View Related
Retrieving Binary Image From SQL
i am trying to retrieve an image blob stored in SQL to a web matrix form (using VB.NET).  I am totaly new to this and i got the code below from a site but it doesn't see to work.....
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Drawing.Image" %>
<%@ import Namespace="System.Drawing.Bitmap" %>
<%@ import Namespace="System.Drawing.Graphics" %>
<%@ import Namespace="System.Web.UI.WebControls" %>
<%@ import Namespace="System.Data.Odbc" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Private Function GetImageFromDB(ByVal ImageID As Integer) As System.Drawing.Image

' Get the Connection string
Dim strConnection As String
strConnection = "server='(local)'; trusted_connection=true; database='mydatabasename'"

Dim conn As New SqlConnection(strConnection)
Dim sqlCommand As String
Dim strImage As String
Dim image As System.Drawing.Image

Try
sqlCommand = "SELECT ImageField FROM ImageTable WHERE MyImageId = " + ImageID.ToString

Dim cmd As New SqlCommand(sqlCommand)
cmd.Connection = conn
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While (dr.Read())

Dim byt As Byte()
byt = dr.Item(strImage)
Dim bmp As New System.Drawing.Bitmap(New System.IO.MemoryStream(byt))
image = bmp

End While
Catch ex As Exception
' Log Error
Response.Write(ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try

Return image

End Function




Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs)
If Not IsPostBack Then
GetImageFromDB(2111)
End If
End Sub
</script>
<html>
<head>
</head>
<body>
<form enctype="multipart/form-data" runat="server">
<asp:Image id="BrandSignImg" runat="server"></asp:Image>
</form></body></html>

View Replies !   View Related
Profile Image Binary Blob Retrieval
 
Hi, All
Can you add a group of images into the aspnet_profile table as a serialized binary blob?
If so how do you retrieve the image from the blob?

View Replies !   View Related
Converting Binary Image To Readable Text
I have a table that contains the following two columns:BITS (image(16))BIT_LENGTH (int(4))When I look at the table, I see "OLE Object" in the BITS column. Whatsyntax should I use in a SELECT statement to convert the binary imageinfo contained in "BITS" into simple text that I can read? What roledoes the BIT_LENGTH field play?

View Replies !   View Related
Storing File As Binary Image Into SQL Server
I am using FileUpload control in ASP.net 2.0 to upload files and store them into SQL server database as an image. I am fine with MS office files, image files and etc. We have Product Center (Proe) engineering software to configure parts and the files generated through this software have PLT extension when I store these files, the file type is Plian/text. I am using Fileupload.PostedFile.Content to get the file type. How can I store PLT, TIF files in SQL server? Please help.

View Replies !   View Related
Insert Or Update Into A Image Column Causes Data To Double In Size
We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated

View Replies !   View Related
Query Image Data Type Column Using Linked Server
Hi all,
When running a query like "select * from test.dev.dbo.table_name" using linked server I have got the following error:
OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetNextRows returned 0x80004005: ]
Table contains image column. When quering the same with no image column works fine.
Steps already done:
Reinstalled MDAC 2.7 SP1
Recreated linked server

Thanks

View Replies !   View Related
&&"String Or Binary Data Would Be Truncated&&" When Using Default Column Definition


When I set a column to have a default definition that uses a UDF, I am receiving the "String or binary data would be truncated" error.

The UDF:




Code BlockALTER FUNCTION GetDefaultClientTier
(
@ClientAssets decimal(15,2)
)
RETURNS char(1)
AS
BEGIN
DECLARE @Result char(1)
-- Get the first result in case of overlaps.
SET @Result = CAST((SELECT TOP 1 ClientTier FROM ClientTiers WHERE @ClientAssets BETWEEN ClientAssetsFloor AND ClientAssetsCeiling)AS char(1))
RETURN @Result
END





ClientTier is defined as char(1) in the table. I simply have (isnull([dbo].[GetDefaultClientTier]([ClientAssets])),(null))) as the definition. I can't use a computed column because I the values need to be editable. When inserting with SSIS, the insert works fine but the column has a value of null for each row.

When putting a character as the default (like 'A') the insert works fine.

The cast is there only because I have tried everything I can think of to get around this.

Is there something simple I am overlooking?

(SQL2005 SP2)

Thanks!

View Replies !   View Related
Inserted The Image In A Column----how Can I View The Image
hi,i have inserted the image present in mydocuments using alter commandcreate table aa(a int, d image)insert into aa values (1,'F:prudhviaba 002.jpg')when i doselect * from aai am getting the result in the column d as0x463A5C707275646876695C70727564687669203030322E6A 7067how i can i view the image?pls clarify my doubtsatish

View Replies !   View Related
Inserting Image File Into Image Column In DB
I have inherited a VS 2005 database with a table that has a column of type IMAGE. I need to change the image for one of the rows in the table. I have the new image in a *.PNG file on my C: drive. What is the correct method for inserting this file into the IMAGE column.

Many thanks!

View Replies !   View Related
Retrieve And Display Image Inside An Html File (stored In Database) In Binary Format
Hi All,
I am not sure whether this is the right place to post this question. But I am unable to figure out what is the best solution to retrieve and display an image in a html file(stored in varbinary(max) column). I have a list of images in the file and I am supposed to display them. Can anybody please let me know what is the best way to do this?
Thanks a lot!!

View Replies !   View Related
Sending Uploaded Image To Data Access Class When Storing Image In SQL Server 2005
I am using the 3-tiered architecture design (presentation, business laws, and data acess layers). I am stuck on how to send the image the user selects in the upload file control to the BLL and then to the DAL because the DAL does all the inserts into the database. I would like to be able to check the file type in the BLL to make sure the file being uploaded is indeed a picture. Is there a way I can send the location of the file to the BLL, check the filetype, then upload the file and have the DAL insert the image into the database? I have seen examples where people use streams to upload the file directly from their presentation layer, but I would like to keep everything seperated in the three classes if possible. I also wasn't sure what variable type the image would be in the function in the BLL that receive the image from the PL. If there are any examples or tips anyone can give me that would be appreciated.

View Replies !   View Related
Binary Column Always Returns 0 For ISNULL
Why don't i ever get return value of 1 when the following binary column (profSignature) is null? 
RETURN SELECT ISNULL(profSignature, profSignature) FROM mpProfiles WHERE ApplicantID = CAST(@CID AS INT) AND ProfileID = CAST(@PID AS INT)

View Replies !   View Related
Binary Column Or Flat File Best Practices?
I have a design oriented question for a system I am developing.Because of various business concerns and issues we have been moving towards as desing thatbrings files into the SQL 2005 system as binary columns in a database. These files will then be processed at a later time using SSIS into relation model tables.



Normally I would just have the process be files are placed on a FTP location (or other drive path) and a location is stored in the database versus the storing of them as binary rows in the database. Then later the SSIS package runs using the path information for the conneciton manager.



Based on the proposed binary design I have two questions.

1. Can anyone speak to the advantages, disadvantages, issues they have had, etc to the binary storage method?

2. Can anyone make a suggestion on how they would handle the pulling of the file out of SQL when the file is ready to process? Do you stream it to a file and rebuild it on the physical disk, to then just import it with a connection manager for the flat file structure.... or can you stream it directly into a conneciton manager that reads it like a flat file and parses the file without ever going to disk? Any information on suggested implementations would be helpful.



Thanks.



View Replies !   View Related
Ntext Over 4000 Chars Causes 'Data In Row (n) Was Not Update... String Or Binary Data Would Be Truncated...'
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...

View Replies !   View Related
String Or Binary Data Would Be Truncated. I Get This Error When Entering Data Using Sql Server Management Studio Express.
String or binary data would be truncated. I get this error when entering data using sql server management studio express.

I am not running a sql to insert or update the table. This is through the EDI.

The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.

Any ideas?





View Replies !   View Related
Image Column In Sql 7.0
Dear all,

In SQL server 6.5/7.0

I have bitmap file called pic123.bmp. it is available in c:pic123.bmp.

I have a table called pictb -- columns picid int, picval image.

How i insert the c:pic123.bmp file into picval column of pictb table.

Image columns only takes value of varbinary.....


How can i do that....? Please give your suggestions.....

pl give me exact syntax...


Wincy

View Replies !   View Related
Image Column
Hi

I have a table in SQL SERVER with this structure...

(Empid Datatype is Char
Last_Name Datatype is Char
''
emp_Picture Datatype is Image)

in which emp_picture column has null values.

I copied the table into access and inserted the Images(jpegs) into the table.
After that I used DTS to copy the whole table back to SQL SERVER and I had
no
problems in doing that.
But when I try to see the images from crystal reports it gives me a blank
picture .. Can anyone tell me what could be the reason and how to work around
this problem..Is the problem at the database level or th problem is with the
ADO's

Thanks in advance
VENU

View Replies !   View Related
How To See Contents Of Image Column
Hello,In my MS SQL SERVER database i have an column with datatype image. Somerecords do have data in this column, some others don't. When i look at thistable in Enterprise Manager then i see in every field '<image>' so no way tosee directly wheter a filed has data or not. Is there a way to changedisplay of this column so i can see the binairy data in it?Marcel

View Replies !   View Related
Default Value For Image Column
HI All,

Does someone knows if it is possible to get a default value on a image column type!

Cheers Wim

View Replies !   View Related
URGENT (Image Column)
Hi

I have a table in SQL SERVER with this structure...

(Empid Datatype is Char
Last_Name Datatype is Char
''
emp_Picture Datatype is Image)

in which emp_picture column has null values.

I copied the table into access and inserted the Images(jpegs) into the table.
After that I used DTS to copy the whole table back to SQL SERVER and I had no
problems in doing that.
But when I try to see the images from crystal reports it gives me a blank
picture .. Can anyone tell me what could be the reason and how to work around
this problem..Is the problem at the database level or th problem is with the ADO's

Thanks in advance
VENU

View Replies !   View Related
Binary Data
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>?

View Replies !   View Related
Binary Data Into SQL 6.5
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.

I just do not have a clue how this works.

Bob

View Replies !   View Related
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:

"declare @MyHAX varchar(32)
select @MyHAX='0x3236374535454337363145313430463742394545 413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"

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."

Does anyone know how can I insert my binary data?

View Replies !   View Related
Binary Data
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,,,

View Replies !   View Related
Inserting In An Image Type Column
Hello, I was wondering, how do I insert a file in an Image type column from the SQL server 2000 corporative administrator? I have a table that contains information about all the programs we run here, and I have a column that I set to image type, I want to put the icon of my programs there..

View Replies !   View Related
Upload Files To Image Column
Anyone have any luck using "Upload multiple files to SQL Server Image column"
www.databasejournal.com/features/mssql/article.php/3444771

View Replies !   View Related
How To Insert A Value In Image Column Of A Table ?
Dear all,

In SQL server 6.5/7.0

I have bitmap file called pic123.bmp. it is available in c:pic123.bmp.

I have a table called pictb -- columns picid int, picval image.

How i insert the c:pic123.bmp file into picval column of pictb table.

Image columns only takes value of varbinary.....


How can i do that....? Please give your suggestions.....

Wincy

View Replies !   View Related
How To Insert A Value Into A Image Column Of A Table ?
Dear all,

In SQL server 6.5/7.0

I have bitmap file called pic123.bmp. it is available in c:pic123.bmp.

I have a table called pictb -- columns picid int, picval image.

How i insert the c:pic123.bmp file into picval column of pictb table.

Image columns only takes value of varbinary.....


How can i do that....? Please give your suggestions.....

pl give me exact syntax...


Wincy

View Replies !   View Related
Update Column With Image File
I created a table with image datatype on one of the columns. Inserted records in all the columns, but image column. Need to update the image column with .gif file. How could i insert .gif file in the column in SQL Server 7.0?
Thanks
Hemant Trivedi

View Replies !   View Related
Convert Image Column's Bytes To Int's
Hi I have an image column (Spectrum) in a Table (ParticleEDS) which is populated with an array of a bunch of INT32's (4 bytes each)

Using TSQL is there any way that I can read each 4 bytes (convert this to an INT) and return this data for a given record (based on ParticleEDSID).

I know that there are 8192 byes (2048x4 bytes) that make up the image column.

I would like the output of the query/stored procedure to be:

Value
------------
1 2342
2 2334
3 3343
.....
2048 1001

I am thinking that the way to do this would be to convert every 4 bytes into an int and create a temporaty table with an integer column which I populate with int and then run a select * on this temporary table.

Does anybody have any pointers on how I can start to do this?

View Replies !   View Related
How To Compare Image/varbinary Column
what is the best way of comparing image/varbinary and nullable column in sql server?
this is what i do to find out the different in image column in 2 tables sharing the same structure:
Select *
From TblA s
Left Join TblB c On

s.Id = c.Id And Coalesce(Convert(VARBINARY(MAX), c.Image),'') <> Coalesce(Convert(VARBINARY(MAX), s.Image),'')
Where c.Id IS NULL

alternatively, i was thinking to compare the column with the size, but worry about the accuracy:
Select *
From TblA s
Left Join TblB c On

s.Id = c.Id And ISNULL(Datalength(c.Image),0) <> ISNULL(Datalength(s.Image),0) Where c.Id IS NULL

any suggestion/advise will be appreciated ~

View Replies !   View Related
Replication On Table With Image Column.
I got a problem when replicating a table with an image field.

First I started replication and it seemed to work. Then after a day or so I noticed that the replication had stopped and it had a few errors like this:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000182EB0000025E001100000000, Command ID: 1)

Error messages:


NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133

NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133


I tried adding afilter to prevent rows which are NULL from being replicated with the filter "[content] is not null ", but I got the error message:


Cannot save properties for article "xxxxxxxxx"
Aditional Information

An Exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SQLServer.Rmo)

Replication filter procedures may not contain columns of large object, large value, XML or UDT type.
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the were clause of a select statement to produce a valid query.
Changed Database context to 'xxxxxxxxxx'. (Microsoft SQL server, Error: 341).


Now Im left wondering how to get the replication to work?




View Replies !   View Related
Retrieiving Binary Data
 
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
 
--Variable declarations
DECLARE @InsertStmt varchar(max)
DECLARE @Fields varchar(8000)
DECLARE @SelList varchar(max)
DECLARE @ColName varchar(128)DECLARE @IsChar tinyint
DECLARE @tableData varchar(8000)
DECLARE @strImageSQL varchar(200)
DECLARE @strTextSQL varchar(200)
DECLARE @ImageData varbinary(8000)
DECLARE @TextData varchar(max)
DECLARE @bitIdentity BIT
 
SET NOCOUNT OFF
 
SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_Name =@strTableName
 
PRINT '---- ** Start of Inserts ** ----'
PRINT ''
 
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName +'] ON '
END
 
--initialize variables
SELECT @InsertStmt = 'INSERT INTO [' + @strTableName + '] (',
@Fields = '',
@SelList = 'SELECT '
 
 
--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
 
CLOSE CR_TableDEALLOCATE CR_Table
 
SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @strTableNameSELECT @InsertStmt = @InsertStmt + @Fields + ')'
 
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.

View Replies !   View Related
Binary Data And Sql Server
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

View Replies !   View Related
Binary Data Type
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
 

View Replies !   View Related
Binary Data Type
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!

View Replies !   View Related
How To Script Binary Data
Hallo!!I have a table with varbinary and image type coumns.Is it possible to export that table data to SQL script ??I need to do this and run the script on the other DB.Is it possible to export binary data as a text ??Maybe some tools ??I'm using MSSQL Server 2005--Regards, Wojtashttp://www.e-krug.com/

View Replies !   View Related
CHECKSUM() Of Binary Data
Hello,

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.

How can I generate checksums of such datatype.

Karam

View Replies !   View Related
Insert Binary Data
Hi my first post so be gentle :D

I have a table which has a column of type binary(200)

I want to put some test data into this table just using sql server managment studio express

how do i do this i keep getting message which says

"the changed value in this cell was not recognized as valid .NET framework data type: Byte[]"

Thanks,

Adam

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved