Hi,
Can someone tell me how to draw a circle other than using an image
Thanks,
PonnurangamSQL Server 2000 Reporting Services does not support circles.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:eVkifAwmEHA.2708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone tell me how to draw a circle other than using an image
> Thanks,
> Ponnurangam
>
Showing posts with label image. Show all posts
Showing posts with label image. Show all posts
Friday, March 9, 2012
how to download the image or file from the [Blobcontent] as file in sql server 2005?
hi,
i plan to use the database mail to send the mail. i created stored procedure to send the mail to all receipents ,.
Table fields like, profile,Toaddress,Bcc,Ccc,Blbcontents ,filename..subj,msgbody ,etc...,
download the blb & attach with the mail for the particular recepent
.
so., is it possible to download the blobcontent using Query? or any Other function available in Sqlserver 2005?
In order to attach files to an email in Database Mail on SQL Server 2005, the files must be on disk. If you are storing files at blobs (VarBinary or image) in your SQL Server database, you must extract them to disk before attaching them to your mail.
Does that answer your question?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||thanks Mr.Paul A. Mestemaker,
my question is, Is any system procedure available to download the blob-content in sql server 2005 database itself?
Please let me know ..
any way, myself found the solution, please let me know the procedure is correct
i wrote the clr integrated procedure using c# application
the below steps i have done
1. created the ExportBlob.dll file
2. Created assembly with ExportBlob.dll.
3. Created Procedure to call the Dll file with param.
4. Created ownProcedure to send mail
5. if you want to send mail as Automatically, create new job & set the schedule time to run in sql server agents.
this code is working fine.
Create assembly
==============
USE [master]
Go
/* register the assembly in a SQL Server database using the CREATE ASSEMBLY statement */
CREATE ASSEMBLY ExportBlob
FROM 'D:\Extended_Proc\sp_exportBlob.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
GO
/****** Object: StoredProcedure [dbo].[xp_ExportBlob]
CREATE PROCEDURE [dbo].[xp_ExportBlob]
@.ServerName [nvarchar](25),
@.UID [nvarchar](50),
@.Pwd [nvarchar](25),
@.DBName [nvarchar](25),
@.Tbname [nvarchar](50),
@.FldName [nvarchar](100),
@.cndt [nvarchar](max),
@.Exportpath [nvarchar](100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ExportBlob].[sp_exportBlob].[exportBlob] //
mail send
=========
SET @.DBName=db_name() /* Get & Assign the Current Database Name*/
SET @.Server=@.@.servername
Select @.ListOfAttachFiles='', @.Delimiter=';'
/*Create a Local Dir to save the File*/
set @.ExtractPath='C:\SQLdbmail_attched_files\
DECLARE CursorMailList CURSOR FOR
SELECT [MailID],
[Profile],
left(Subject,255),
[importance],
[MsgTo],
[CopyTo],
[BCCTo],
[Contents],
isnull(HasAttachment,0)
FROM MailTb Where Processed=0 Order by MailID
OPEN CursorMailList
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
Select @.ListOfAttachFiles=@.ListOfAttachFiles + @.ExtractPath+Filename+@.Delimiter from Attachments where MailID=@.MailID
SET @.ListOfAttachFiles=left(@.ListOfAttachFiles,len(@.ListOfAttachFiles)-1)
SET @.cndtn='where Datalength(Blobcontents)>0 and MailID='+ convert(VARCHAR,@.MailID,25)
select @.FldNames='Blobcontents,Filename'
/*Download File in the server Dir */
/*passing param to dll , param=servername,UID,PWD,DatabaseName,Tablename,Feildname,wherecndtn,extractDir */
EXEC master..xp_ExportBlob @.Server, 'sa', 'pass', @.DBName, 'Attachments', @.FldNames, @.cndtn, @.ExtractPath
/*Get Missing Attchment Files */
BEGIN TRY
EXECUTE msdb..sp_send_dbmail
@.profile_name = @.Profile
,@.recipients = @.m_Toaddress
,@.copy_recipients = @.m_ccaddress
,@.blind_copy_recipients = @.m_Bccaddress
,@.body = @.m_body
,@.subject = @.m_subject
,@.importance = @.m_importance
,@.file_attachments = @.ListOfAttachFiles /* Eg. D:\mailDownload\filename.ext;c D:\mailDownload\filename2.ext */
,@.mailitem_id = @.mailitemid output
Update MailTb set Processed=1 where MailID=@.MailID /*Update back Mail sent scessfully*/
END TRY /*TRY*/
BEGIN CATCH
SET @.err=1
set @.ErrMsg = @.ErrMsg +'Failed to send Mail with Attachments. SDS MailID ='+convert(varchar,@.MailId,25)
END CATCH /*Catch*/
END /*
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
END /*WHILE*/
-- clean up; close & Deallocate the cursor
CLOSE CursorMailList
DEALLOCATE CursorMailList
c# application {Create library}
====================
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class sp_exportBlob
{
// Identify that this is a SQL Stored Procedure
[SqlProcedure]
//summary
//FldNames should be BlobContents and Filename with Extension using comma Sepearator.
// [i.e., Fldnames="Blobcontents,Filename+Extension" ]
//
public static void exportBlob(string ServerName, string UID, string Pwd, string DBName, string TbName, string Fldnames, string Cndt, string ExtractPath)
{
SqlConnection sqlconn = new SqlConnection();
SqlDataReader sqlDr;
SqlCommand cmd;
FileStream fs;
BinaryWriter bw;
int Blobsize;
long blob, startIndex;
byte[] outBuffer;
string ExtractFileName = "";
try
{
int IndexBlobContents = 0;
sqlconn.ConnectionString = "Server=" + ServerName + ";UID=" + UID + ";Pwd=" + Pwd + ";Database=" + DBName;
sqlconn.Open();
Blobsize = 1024;// Initalize the BlobSize
cmd = new SqlCommand("SELECT " + Fldnames + " FROM " + TbName + " " + Cndt, sqlconn);
sqlDr = cmd.ExecuteReader();
while (sqlDr.Read())
{
ExtractFileName = ExtractPath + sqlDr[1].ToString();//dirname + filename
startIndex = 0; // Reset the starting byte for the new BLOB.
outBuffer = new byte[Blobsize];
if (File.Exists(@.ExtractFileName))
{
File.Delete(@.ExtractFileName);
}
fs = new FileStream(@.ExtractFileName, FileMode.OpenOrCreate, FileAccess.Write); // Create a file to hold the output.
bw = new BinaryWriter(fs);
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize); // Read bytes into outByte[] and retain the number of bytes returned.
while (blob == Blobsize) // Continue while there are bytes beyond the size of the buffer.
{
bw.Write(outBuffer);
bw.Flush();
startIndex += Blobsize;
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize);
}
bw.Write(outBuffer, 0, (int)blob);// Write the remaining buffer.
bw.Flush();
bw.Close();
fs.Close();
SqlContext.Pipe.Send("Downloaded successfully in " + ExtractFileName);
}
sqlDr.Close();
}
catch (Exception er)
{
SqlContext.Pipe.Send(er.Message);
}
finally
{
if (sqlconn.State == System.Data.ConnectionState.Open)
{
sqlconn.Close();
}
fs = null;
bw = null;
cmd = null;
sqlDr = null;
sqlconn = null;
}
}
}
|||Looks pretty cool. I generally don't like to see dynamic SQL, but it seems like it's working for your architecture. Thanks for posting this so the community can benefit :-)
People ask about this quite a bit at conferences... now I have a place to direct them.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||Hi Raj,
This solution is great. However, I am using a different environment :-
SQL 2005 and Java for this application.
So, i will not be able to use the c# piece of code.
c# application {Create library}
Thanks,
how to download the image or file from the [Blobcontent] as file in sql server 2005?
hi,
i plan to use the database mail to send the mail. i created stored procedure to send the mail to all receipents ,.
Table fields like, profile,Toaddress,Bcc,Ccc,Blbcontents ,filename..subj,msgbody ,etc...,
download the blb & attach with the mail for the particular recepent
.
so., is it possible to download the blobcontent using Query? or any Other function available in Sqlserver 2005?
In order to attach files to an email in Database Mail on SQL Server 2005, the files must be on disk. If you are storing files at blobs (VarBinary or image) in your SQL Server database, you must extract them to disk before attaching them to your mail.
Does that answer your question?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||thanks Mr.Paul A. Mestemaker,
my question is, Is any system procedure available to download the blob-content in sql server 2005 database itself?
Please let me know ..
any way, myself found the solution, please let me know the procedure is correct
i wrote the clr integrated procedure using c# application
the below steps i have done
1. created the ExportBlob.dll file
2. Created assembly with ExportBlob.dll.
3. Created Procedure to call the Dll file with param.
4. Created ownProcedure to send mail
5. if you want to send mail as Automatically, create new job & set the schedule time to run in sql server agents.
this code is working fine.
Create assembly
==============
USE [master]
Go
/* register the assembly in a SQL Server database using the CREATE ASSEMBLY statement */
CREATE ASSEMBLY ExportBlob
FROM 'D:\Extended_Proc\sp_exportBlob.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
GO
/****** Object: StoredProcedure [dbo].[xp_ExportBlob]
CREATE PROCEDURE [dbo].[xp_ExportBlob]
@.ServerName [nvarchar](25),
@.UID [nvarchar](50),
@.Pwd [nvarchar](25),
@.DBName [nvarchar](25),
@.Tbname [nvarchar](50),
@.FldName [nvarchar](100),
@.cndt [nvarchar](max),
@.Exportpath [nvarchar](100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ExportBlob].[sp_exportBlob].[exportBlob] //
mail send
=========
SET @.DBName=db_name() /* Get & Assign the Current Database Name*/
SET @.Server=@.@.servername
Select @.ListOfAttachFiles='', @.Delimiter=';'
/*Create a Local Dir to save the File*/
set @.ExtractPath='C:\SQLdbmail_attched_files\
DECLARE CursorMailList CURSOR FOR
SELECT [MailID],
[Profile],
left(Subject,255),
[importance],
[MsgTo],
[CopyTo],
[BCCTo],
[Contents],
isnull(HasAttachment,0)
FROM MailTb Where Processed=0 Order by MailID
OPEN CursorMailList
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
Select @.ListOfAttachFiles=@.ListOfAttachFiles + @.ExtractPath+Filename+@.Delimiter from Attachments where MailID=@.MailID
SET @.ListOfAttachFiles=left(@.ListOfAttachFiles,len(@.ListOfAttachFiles)-1)
SET @.cndtn='where Datalength(Blobcontents)>0 and MailID='+ convert(VARCHAR,@.MailID,25)
select @.FldNames='Blobcontents,Filename'
/*Download File in the server Dir */
/*passing param to dll , param=servername,UID,PWD,DatabaseName,Tablename,Feildname,wherecndtn,extractDir */
EXEC master..xp_ExportBlob @.Server, 'sa', 'pass', @.DBName, 'Attachments', @.FldNames, @.cndtn, @.ExtractPath
/*Get Missing Attchment Files */
BEGIN TRY
EXECUTE msdb..sp_send_dbmail
@.profile_name = @.Profile
,@.recipients = @.m_Toaddress
,@.copy_recipients = @.m_ccaddress
,@.blind_copy_recipients = @.m_Bccaddress
,@.body = @.m_body
,@.subject = @.m_subject
,@.importance = @.m_importance
,@.file_attachments = @.ListOfAttachFiles /* Eg. D:\mailDownload\filename.ext;c D:\mailDownload\filename2.ext */
,@.mailitem_id = @.mailitemid output
Update MailTb set Processed=1 where MailID=@.MailID /*Update back Mail sent scessfully*/
END TRY /*TRY*/
BEGIN CATCH
SET @.err=1
set @.ErrMsg = @.ErrMsg +'Failed to send Mail with Attachments. SDS MailID ='+convert(varchar,@.MailId,25)
END CATCH /*Catch*/
END /*
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
END /*WHILE*/
-- clean up; close & Deallocate the cursor
CLOSE CursorMailList
DEALLOCATE CursorMailList
c# application {Create library}
====================
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class sp_exportBlob
{
// Identify that this is a SQL Stored Procedure
[SqlProcedure]
//summary
//FldNames should be BlobContents and Filename with Extension using comma Sepearator.
// [i.e., Fldnames="Blobcontents,Filename+Extension" ]
//
public static void exportBlob(string ServerName, string UID, string Pwd, string DBName, string TbName, string Fldnames, string Cndt, string ExtractPath)
{
SqlConnection sqlconn = new SqlConnection();
SqlDataReader sqlDr;
SqlCommand cmd;
FileStream fs;
BinaryWriter bw;
int Blobsize;
long blob, startIndex;
byte[] outBuffer;
string ExtractFileName = "";
try
{
int IndexBlobContents = 0;
sqlconn.ConnectionString = "Server=" + ServerName + ";UID=" + UID + ";Pwd=" + Pwd + ";Database=" + DBName;
sqlconn.Open();
Blobsize = 1024;// Initalize the BlobSize
cmd = new SqlCommand("SELECT " + Fldnames + " FROM " + TbName + " " + Cndt, sqlconn);
sqlDr = cmd.ExecuteReader();
while (sqlDr.Read())
{
ExtractFileName = ExtractPath + sqlDr[1].ToString();//dirname + filename
startIndex = 0; // Reset the starting byte for the new BLOB.
outBuffer = new byte[Blobsize];
if (File.Exists(@.ExtractFileName))
{
File.Delete(@.ExtractFileName);
}
fs = new FileStream(@.ExtractFileName, FileMode.OpenOrCreate, FileAccess.Write); // Create a file to hold the output.
bw = new BinaryWriter(fs);
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize); // Read bytes into outByte[] and retain the number of bytes returned.
while (blob == Blobsize) // Continue while there are bytes beyond the size of the buffer.
{
bw.Write(outBuffer);
bw.Flush();
startIndex += Blobsize;
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize);
}
bw.Write(outBuffer, 0, (int)blob);// Write the remaining buffer.
bw.Flush();
bw.Close();
fs.Close();
SqlContext.Pipe.Send("Downloaded successfully in " + ExtractFileName);
}
sqlDr.Close();
}
catch (Exception er)
{
SqlContext.Pipe.Send(er.Message);
}
finally
{
if (sqlconn.State == System.Data.ConnectionState.Open)
{
sqlconn.Close();
}
fs = null;
bw = null;
cmd = null;
sqlDr = null;
sqlconn = null;
}
}
}
|||Looks pretty cool. I generally don't like to see dynamic SQL, but it seems like it's working for your architecture. Thanks for posting this so the community can benefit :-)
People ask about this quite a bit at conferences... now I have a place to direct them.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||Hi Raj,
This solution is great. However, I am using a different environment :-
SQL 2005 and Java for this application.
So, i will not be able to use the c# piece of code.
c# application {Create library}
Thanks,
how to download the image or file from the [Blobcontent] as file in sql server 2005?
hi,
i plan to use the database mail to send the mail. i created stored procedure to send the mail to all receipents ,.
Table fields like, profile,Toaddress,Bcc,Ccc,Blbcontents ,filename..subj,msgbody ,etc...,
download the blb & attach with the mail for the particular recepent
.
so., is it possible to download the blobcontent using Query? or any Other function available in Sqlserver 2005?
In order to attach files to an email in Database Mail on SQL Server 2005, the files must be on disk. If you are storing files at blobs (VarBinary or image) in your SQL Server database, you must extract them to disk before attaching them to your mail.
Does that answer your question?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||thanks Mr.Paul A. Mestemaker,
my question is, Is any system procedure available to download the blob-content in sql server 2005 database itself?
Please let me know ..
any way, myself found the solution, please let me know the procedure is correct
i wrote the clr integrated procedure using c# application
the below steps i have done
1. created the ExportBlob.dll file
2. Created assembly with ExportBlob.dll.
3. Created Procedure to call the Dll file with param.
4. Created ownProcedure to send mail
5. if you want to send mail as Automatically, create new job & set the schedule time to run in sql server agents.
this code is working fine.
Create assembly
==============
USE [master]
Go
/* register the assembly in a SQL Server database using the CREATE ASSEMBLY statement */
CREATE ASSEMBLY ExportBlob
FROM 'D:\Extended_Proc\sp_exportBlob.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
GO
/****** Object: StoredProcedure [dbo].[xp_ExportBlob]
CREATE PROCEDURE [dbo].[xp_ExportBlob]
@.ServerName [nvarchar](25),
@.UID [nvarchar](50),
@.Pwd [nvarchar](25),
@.DBName [nvarchar](25),
@.Tbname [nvarchar](50),
@.FldName [nvarchar](100),
@.cndt [nvarchar](max),
@.Exportpath [nvarchar](100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ExportBlob].[sp_exportBlob].[exportBlob] //
mail send
=========
SET @.DBName=db_name() /* Get & Assign the Current Database Name*/
SET @.Server=@.@.servername
Select @.ListOfAttachFiles='', @.Delimiter=';'
/*Create a Local Dir to save the File*/
set @.ExtractPath='C:\SQLdbmail_attched_files\
DECLARE CursorMailList CURSOR FOR
SELECT [MailID],
[Profile],
left(Subject,255),
[importance],
[MsgTo],
[CopyTo],
[BCCTo],
[Contents],
isnull(HasAttachment,0)
FROM MailTb Where Processed=0 Order by MailID
OPEN CursorMailList
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
Select @.ListOfAttachFiles=@.ListOfAttachFiles + @.ExtractPath+Filename+@.Delimiter from Attachments where MailID=@.MailID
SET @.ListOfAttachFiles=left(@.ListOfAttachFiles,len(@.ListOfAttachFiles)-1)
SET @.cndtn='where Datalength(Blobcontents)>0 and MailID='+ convert(VARCHAR,@.MailID,25)
select @.FldNames='Blobcontents,Filename'
/*Download File in the server Dir */
/*passing param to dll , param=servername,UID,PWD,DatabaseName,Tablename,Feildname,wherecndtn,extractDir */
EXEC master..xp_ExportBlob @.Server, 'sa', 'pass', @.DBName, 'Attachments', @.FldNames, @.cndtn, @.ExtractPath
/*Get Missing Attchment Files */
BEGIN TRY
EXECUTE msdb..sp_send_dbmail
@.profile_name = @.Profile
,@.recipients = @.m_Toaddress
,@.copy_recipients = @.m_ccaddress
,@.blind_copy_recipients = @.m_Bccaddress
,@.body = @.m_body
,@.subject = @.m_subject
,@.importance = @.m_importance
,@.file_attachments = @.ListOfAttachFiles /* Eg. D:\mailDownload\filename.ext;c D:\mailDownload\filename2.ext */
,@.mailitem_id = @.mailitemid output
Update MailTb set Processed=1 where MailID=@.MailID /*Update back Mail sent scessfully*/
END TRY /*TRY*/
BEGIN CATCH
SET @.err=1
set @.ErrMsg = @.ErrMsg +'Failed to send Mail with Attachments. SDS MailID ='+convert(varchar,@.MailId,25)
END CATCH /*Catch*/
END /*
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
END /*WHILE*/
-- clean up; close & Deallocate the cursor
CLOSE CursorMailList
DEALLOCATE CursorMailList
c# application {Create library}
====================
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class sp_exportBlob
{
// Identify that this is a SQL Stored Procedure
[SqlProcedure]
//summary
//FldNames should be BlobContents and Filename with Extension using comma Sepearator.
// [i.e., Fldnames="Blobcontents,Filename+Extension" ]
//
public static void exportBlob(string ServerName, string UID, string Pwd, string DBName, string TbName, string Fldnames, string Cndt, string ExtractPath)
{
SqlConnection sqlconn = new SqlConnection();
SqlDataReader sqlDr;
SqlCommand cmd;
FileStream fs;
BinaryWriter bw;
int Blobsize;
long blob, startIndex;
byte[] outBuffer;
string ExtractFileName = "";
try
{
int IndexBlobContents = 0;
sqlconn.ConnectionString = "Server=" + ServerName + ";UID=" + UID + ";Pwd=" + Pwd + ";Database=" + DBName;
sqlconn.Open();
Blobsize = 1024;// Initalize the BlobSize
cmd = new SqlCommand("SELECT " + Fldnames + " FROM " + TbName + " " + Cndt, sqlconn);
sqlDr = cmd.ExecuteReader();
while (sqlDr.Read())
{
ExtractFileName = ExtractPath + sqlDr[1].ToString();//dirname + filename
startIndex = 0; // Reset the starting byte for the new BLOB.
outBuffer = new byte[Blobsize];
if (File.Exists(@.ExtractFileName))
{
File.Delete(@.ExtractFileName);
}
fs = new FileStream(@.ExtractFileName, FileMode.OpenOrCreate, FileAccess.Write); // Create a file to hold the output.
bw = new BinaryWriter(fs);
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize); // Read bytes into outByte[] and retain the number of bytes returned.
while (blob == Blobsize) // Continue while there are bytes beyond the size of the buffer.
{
bw.Write(outBuffer);
bw.Flush();
startIndex += Blobsize;
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize);
}
bw.Write(outBuffer, 0, (int)blob);// Write the remaining buffer.
bw.Flush();
bw.Close();
fs.Close();
SqlContext.Pipe.Send("Downloaded successfully in " + ExtractFileName);
}
sqlDr.Close();
}
catch (Exception er)
{
SqlContext.Pipe.Send(er.Message);
}
finally
{
if (sqlconn.State == System.Data.ConnectionState.Open)
{
sqlconn.Close();
}
fs = null;
bw = null;
cmd = null;
sqlDr = null;
sqlconn = null;
}
}
}
|||Looks pretty cool. I generally don't like to see dynamic SQL, but it seems like it's working for your architecture. Thanks for posting this so the community can benefit :-)
People ask about this quite a bit at conferences... now I have a place to direct them.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||Hi Raj,
This solution is great. However, I am using a different environment :-
SQL 2005 and Java for this application.
So, i will not be able to use the c# piece of code.
c# application {Create library}
Thanks,
Friday, February 24, 2012
How to do REPLACE in SQL Queries
Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg
Project/NEWUSERID/ProjectImages/702d_2.jpg
How can I accomplish this in SQL?
thanks in Advance
Dollarjunkie
http://msdn2.microsoft.com/en-us/library/ms186862.aspx
http://www.sqlteam.com/article/using-replace-in-an-update-statement
Update table set IMAGESRC = Replace(IMAGESRC, 'aa11be5d-dd9e-48c8-9d8c-6a972e996b28', 'NEWUSERID')
|||Hello Dollarjunkie,
In SQL you have two functions you can use:
- CharIndex to locate the position of the first and second '/'
- Substring to split your string into parts that you can concatenate to a new string.
In your case it will be something like:
... Substring(IMAGESRC, 1, CharIndex(IMAGESRC, '/', 1) + 1) + NEWUSRID +
Substring(IMAGESRC, CharIndex(IMAGESRC, '/', CharIndex(IMAGESRC, '/', 1) + 1))
The first substring results in: Project/
The second substring results in: /ProjectImages/702d_2.jpg
Hope this helps.
Jeroen Molenaar.
Sunday, February 19, 2012
How to do field content search in sql
Hi everyone
I have a silly question about SQL, I wonder if it is possible using
existing technique to accomplish it.
I have a binary field(e.g. Image) in SQL, I need to store image
file(scanned from original document) in that field. I don't think it is
possible but my boss want me to give him at least an alternative
solution: Can I search the text in this field?
To make it clear:
I scan a paper document, I get a jpeg file, the original file contains:
text object, handwritting object. There is a word "Bush" in the
document. I store this jpeg file in a field (type of Image) in SQL
database, Now I want to do a search for "Bush" in that field in the
database.
Can I do that?
I told him it is impossible to do that but you know he is looking for
kind of alternative solution, he doesn't care money.
I told him I can search for text object, if you put some description on
that image field, then I can search those description. But he want to
search the whole document.
Can I do sort of recognization and extract content from the scanned
jpeg, and then store these extracted content in sql so that I can do
some search?
Thanks.You can't do that out-of-the-box with SQL Server. However, there are
plenty of document imaging systems that will scan and index documents
into a database for you. You should probably take a look at some of
those packages.
David Portas
SQL Server MVP
--|||Raymond,
David, this can actually be done with a slight and very easy modification to
Raymond's procedures...
Raymond, when you scan a paper document can your scanner or scanner software
save the file as a Tagged Image File Format (TIFF) file? Most scanners can
store files as TIFF as well do OCR too... If you can then you can use the
TIFF IFilter (mspfilt.dll) as it filters files with the TIFF extension. This
filter gets installed by MS Office 2003 & MS Office XP. If you cannot, you
could possibly use one of several JPEG IFilters, for example the XMP IFilter
lets you index JPEG, GIF, TIFF, PNG, PS, EPS, PSD, AI and SVG files (see
http://www.ifiltershop.com/xmpfilter.html) or JPEG IFilter - JPEG Content
Filter for Microsoft Indexing Service (see
http://www.aimingtech.com/jpeg_ifilter/). While I've not tested these JPEG
IFilters with SQL Server 2000 FTS, but as they say an ifilter, is an
ifilter, is an ifilter...
These IFilters work the same way and use the same technology as Adobe's PDF
IFilter that can used with SQL Server 2000 Full-text Search (FTS). You store
the binary file in a column defined with the IMAGE datatype and then alter
your table and add a new column for "file extension" and define this column
as char(3), varchar(4) or sysname and populate it with "pdf", ".pdf"
respectively.
Below are a couple KB articles related to the TIFF IFilter:
Q321820 FIX: Non-OCR/Non-Display TIFF Data Indexed by SQL Server Full-Text
http://support.microsoft.com/defaul...b;en-us;Q321820
Q283950 SPS: Some Character Sets Are Not Supported for OCR by the TIFF Index
Filter
http://support.microsoft.com/defaul...b;en-us;Q283950
Q291835 SPS: TIFF Filter Stops Working After You Start the Windows
Components Wizard
http://support.microsoft.com/defaul...b;en-us;q291835
Q294303 SPS: TIFF Filter Does Not Perform OCR on Indexed Files
http://support.microsoft.com/defaul...b;en-us;Q294303
Q. Can I do sort of recognition and extract content from the scanned jpeg,
and then store these extracted content in sql so that I can do some search?
A. Yes, if your scanner &/or scanner software supports OCR or Optical
Character Recognition, then you will be able to extract the content.
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125682987.259473.144690@.g44g2000cwa.googlegroups.com...
> You can't do that out-of-the-box with SQL Server. However, there are
> plenty of document imaging systems that will scan and index documents
> into a database for you. You should probably take a look at some of
> those packages.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi John and David,
Thank you for your reply.
John, I tried your script on your blog to set up a full text search, I
stall couldn't get any matching file when I do my search. For example, I
inserted a txt file containing "John" into the image column of table
FTSTable, and then I search it in that table, no luck.
Did I miss anything? I followed every step on the script on your blog.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Raymond,
Could you review your server's Application event log for any "Microsoft
Search" or MssCi source events. Specifically, for MssCi (informational,
warnings & errors) as this may indicate why the Full Population did not
succeed for the file type. Can I assume you are using English as the
"Language for Word Breaker" and that English is the language in the docs?
Additionally, could you confirm that you have properly associated the
correct file type MS Word with "doc" in the file extension column defined as
char(3)?
Feel free to email me directly, at jt-kane at comcast dot net
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Raymond Xie" <halifaxdal@.yahoo.ca> wrote in message
news:%23M9k48xsFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Hi John and David,
> Thank you for your reply.
> John, I tried your script on your blog to set up a full text search, I
> stall couldn't get any matching file when I do my search. For example, I
> inserted a txt file containing "John" into the image column of table
> FTSTable, and then I search it in that table, no luck.
> Did I miss anything? I followed every step on the script on your blog.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Raymond,
A follow-up... Did you install Adobe's PDF IFilter? If not, you can download
the PDF Ifilter from the following blog entry:
"IFilters or Indexing Filters used with SQL FTS..." at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!374.e
ntry
Click on PDF - Adobe and then save or install the ifilter60.exe file on
your server where SQL Server 2000 is installed. Then re-run a Full
Population and check the server's application event log for a successful
Full Population!
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uqVHf8CtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Raymond,
> Could you review your server's Application event log for any "Microsoft
> Search" or MssCi source events. Specifically, for MssCi (informational,
> warnings & errors) as this may indicate why the Full Population did not
> succeed for the file type. Can I assume you are using English as the
> "Language for Word Breaker" and that English is the language in the docs?
> Additionally, could you confirm that you have properly associated the
> correct file type MS Word with "doc" in the file extension column defined
> as char(3)?
> Feel free to email me directly, at jt-kane at comcast dot net
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Raymond Xie" <halifaxdal@.yahoo.ca> wrote in message
> news:%23M9k48xsFHA.3236@.TK2MSFTNGP09.phx.gbl...
>
I have a silly question about SQL, I wonder if it is possible using
existing technique to accomplish it.
I have a binary field(e.g. Image) in SQL, I need to store image
file(scanned from original document) in that field. I don't think it is
possible but my boss want me to give him at least an alternative
solution: Can I search the text in this field?
To make it clear:
I scan a paper document, I get a jpeg file, the original file contains:
text object, handwritting object. There is a word "Bush" in the
document. I store this jpeg file in a field (type of Image) in SQL
database, Now I want to do a search for "Bush" in that field in the
database.
Can I do that?
I told him it is impossible to do that but you know he is looking for
kind of alternative solution, he doesn't care money.
I told him I can search for text object, if you put some description on
that image field, then I can search those description. But he want to
search the whole document.
Can I do sort of recognization and extract content from the scanned
jpeg, and then store these extracted content in sql so that I can do
some search?
Thanks.You can't do that out-of-the-box with SQL Server. However, there are
plenty of document imaging systems that will scan and index documents
into a database for you. You should probably take a look at some of
those packages.
David Portas
SQL Server MVP
--|||Raymond,
David, this can actually be done with a slight and very easy modification to
Raymond's procedures...
Raymond, when you scan a paper document can your scanner or scanner software
save the file as a Tagged Image File Format (TIFF) file? Most scanners can
store files as TIFF as well do OCR too... If you can then you can use the
TIFF IFilter (mspfilt.dll) as it filters files with the TIFF extension. This
filter gets installed by MS Office 2003 & MS Office XP. If you cannot, you
could possibly use one of several JPEG IFilters, for example the XMP IFilter
lets you index JPEG, GIF, TIFF, PNG, PS, EPS, PSD, AI and SVG files (see
http://www.ifiltershop.com/xmpfilter.html) or JPEG IFilter - JPEG Content
Filter for Microsoft Indexing Service (see
http://www.aimingtech.com/jpeg_ifilter/). While I've not tested these JPEG
IFilters with SQL Server 2000 FTS, but as they say an ifilter, is an
ifilter, is an ifilter...
These IFilters work the same way and use the same technology as Adobe's PDF
IFilter that can used with SQL Server 2000 Full-text Search (FTS). You store
the binary file in a column defined with the IMAGE datatype and then alter
your table and add a new column for "file extension" and define this column
as char(3), varchar(4) or sysname and populate it with "pdf", ".pdf"
respectively.
Below are a couple KB articles related to the TIFF IFilter:
Q321820 FIX: Non-OCR/Non-Display TIFF Data Indexed by SQL Server Full-Text
http://support.microsoft.com/defaul...b;en-us;Q321820
Q283950 SPS: Some Character Sets Are Not Supported for OCR by the TIFF Index
Filter
http://support.microsoft.com/defaul...b;en-us;Q283950
Q291835 SPS: TIFF Filter Stops Working After You Start the Windows
Components Wizard
http://support.microsoft.com/defaul...b;en-us;q291835
Q294303 SPS: TIFF Filter Does Not Perform OCR on Indexed Files
http://support.microsoft.com/defaul...b;en-us;Q294303
Q. Can I do sort of recognition and extract content from the scanned jpeg,
and then store these extracted content in sql so that I can do some search?
A. Yes, if your scanner &/or scanner software supports OCR or Optical
Character Recognition, then you will be able to extract the content.
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125682987.259473.144690@.g44g2000cwa.googlegroups.com...
> You can't do that out-of-the-box with SQL Server. However, there are
> plenty of document imaging systems that will scan and index documents
> into a database for you. You should probably take a look at some of
> those packages.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi John and David,
Thank you for your reply.
John, I tried your script on your blog to set up a full text search, I
stall couldn't get any matching file when I do my search. For example, I
inserted a txt file containing "John" into the image column of table
FTSTable, and then I search it in that table, no luck.
Did I miss anything? I followed every step on the script on your blog.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Raymond,
Could you review your server's Application event log for any "Microsoft
Search" or MssCi source events. Specifically, for MssCi (informational,
warnings & errors) as this may indicate why the Full Population did not
succeed for the file type. Can I assume you are using English as the
"Language for Word Breaker" and that English is the language in the docs?
Additionally, could you confirm that you have properly associated the
correct file type MS Word with "doc" in the file extension column defined as
char(3)?
Feel free to email me directly, at jt-kane at comcast dot net
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Raymond Xie" <halifaxdal@.yahoo.ca> wrote in message
news:%23M9k48xsFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Hi John and David,
> Thank you for your reply.
> John, I tried your script on your blog to set up a full text search, I
> stall couldn't get any matching file when I do my search. For example, I
> inserted a txt file containing "John" into the image column of table
> FTSTable, and then I search it in that table, no luck.
> Did I miss anything? I followed every step on the script on your blog.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Raymond,
A follow-up... Did you install Adobe's PDF IFilter? If not, you can download
the PDF Ifilter from the following blog entry:
"IFilters or Indexing Filters used with SQL FTS..." at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!374.e
ntry
Click on PDF - Adobe and then save or install the ifilter60.exe file on
your server where SQL Server 2000 is installed. Then re-run a Full
Population and check the server's application event log for a successful
Full Population!
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uqVHf8CtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Raymond,
> Could you review your server's Application event log for any "Microsoft
> Search" or MssCi source events. Specifically, for MssCi (informational,
> warnings & errors) as this may indicate why the Full Population did not
> succeed for the file type. Can I assume you are using English as the
> "Language for Word Breaker" and that English is the language in the docs?
> Additionally, could you confirm that you have properly associated the
> correct file type MS Word with "doc" in the file extension column defined
> as char(3)?
> Feel free to email me directly, at jt-kane at comcast dot net
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Raymond Xie" <halifaxdal@.yahoo.ca> wrote in message
> news:%23M9k48xsFHA.3236@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)