How to save images to SQL Server?

Hi, All
I have a question?
I have scanned images (file format .tif) i need to store them in SQL Server.
And after all how fast is the search for those images (search on 200
thousands images and up)
Thank you,
Are you going to put some other fields for searching purpose e.g. file
name, date created, creator etc? Since they are image files, what kind
of search you want to put it on?
Mel
|||Hi,
Thank you for replay,
It will be one field "Ticket No", and i need to search by this field.
So it is possible, where i can get some more references? and SQL Server 2005
will be the answer
"MSLam" wrote:

> Are you going to put some other fields for searching purpose e.g. file
> name, date created, creator etc? Since they are image files, what kind
> of search you want to put it on?
> Mel
>

How to save images to SQL Server?

Hi, All
I have a question?
I have scanned images (file format .tif) i need to store them in SQL Server.
And after all how fast is the search for those images (search on 200
thousands images and up)
Thank you,Are you going to put some other fields for searching purpose e.g. file
name, date created, creator etc? Since they are image files, what kind
of search you want to put it on?
Mel|||Hi,
Thank you for replay,
It will be one field "Ticket No", and i need to search by this field.
So it is possible, where i can get some more references? and SQL Server 2005
will be the answer
"MSLam" wrote:

> Are you going to put some other fields for searching purpose e.g. file
> name, date created, creator etc? Since they are image files, what kind
> of search you want to put it on?
> Mel
>|||... still don't qutie get your question. I shall take it as follow:
You have a table that has at least two fields: Ticket No and Image.
The Ticket No will be using INT as the data type and the Image will be
using text/image data type.
You will be using Ticket No for users to search on OR
You will be using Image for users to search on
If ticket no will be the field users need to search on, it should not
take long to retrieve records (assuming the Ticket no here is data type
int). You can put a normal index on the field to speed up the search
(either clustered or non-clustered depending your situation).
If image will be the field users need to search on (based on text value
in the image file), you use FULL-TEXT index for this purpose.
Check BOL for more information about indexing.
Hope I got your question right.
Mel|||Thank you, Mel
I need perform search on "ticket No" field and if found ti retrive the image
.
Thanks again,
"MSLam" wrote:

> .... still don't qutie get your question. I shall take it as follow:
> You have a table that has at least two fields: Ticket No and Image.
> The Ticket No will be using INT as the data type and the Image will be
> using text/image data type.
> You will be using Ticket No for users to search on OR
> You will be using Image for users to search on
> If ticket no will be the field users need to search on, it should not
> take long to retrieve records (assuming the Ticket no here is data type
> int). You can put a normal index on the field to speed up the search
> (either clustered or non-clustered depending your situation).
> If image will be the field users need to search on (based on text value
> in the image file), you use FULL-TEXT index for this purpose.
> Check BOL for more information about indexing.
> Hope I got your question right.
> Mel
>

How to save images to SQL Server?

Hi, All
I have a question?
I have scanned images (file format .tif) i need to store them in SQL Server.
And after all how fast is the search for those images (search on 200
thousands images and up)
Thank you,Are you going to put some other fields for searching purpose e.g. file
name, date created, creator etc? Since they are image files, what kind
of search you want to put it on?
Mel|||Hi,
Thank you for replay,
It will be one field "Ticket No", and i need to search by this field.
So it is possible, where i can get some more references? and SQL Server 2005
will be the answer
"MSLam" wrote:
> Are you going to put some other fields for searching purpose e.g. file
> name, date created, creator etc? Since they are image files, what kind
> of search you want to put it on?
> Mel
>|||... still don't qutie get your question. I shall take it as follow:
You have a table that has at least two fields: Ticket No and Image.
The Ticket No will be using INT as the data type and the Image will be
using text/image data type.
You will be using Ticket No for users to search on OR
You will be using Image for users to search on
If ticket no will be the field users need to search on, it should not
take long to retrieve records (assuming the Ticket no here is data type
int). You can put a normal index on the field to speed up the search
(either clustered or non-clustered depending your situation).
If image will be the field users need to search on (based on text value
in the image file), you use FULL-TEXT index for this purpose.
Check BOL for more information about indexing.
Hope I got your question right.
Mel|||Thank you, Mel
I need perform search on "ticket No" field and if found ti retrive the image.
Thanks again,
"MSLam" wrote:
> .... still don't qutie get your question. I shall take it as follow:
> You have a table that has at least two fields: Ticket No and Image.
> The Ticket No will be using INT as the data type and the Image will be
> using text/image data type.
> You will be using Ticket No for users to search on OR
> You will be using Image for users to search on
> If ticket no will be the field users need to search on, it should not
> take long to retrieve records (assuming the Ticket no here is data type
> int). You can put a normal index on the field to speed up the search
> (either clustered or non-clustered depending your situation).
> If image will be the field users need to search on (based on text value
> in the image file), you use FULL-TEXT index for this purpose.
> Check BOL for more information about indexing.
> Hope I got your question right.
> Mel
>sql

How to save image to SQL Server 2000

Hi,

I have to store images in database. I have a table which contains field picture which is an image.

How can I do this using C# ?

In Visual Studio .NET i found a code how to obtain BLOB values from the database but I do not know how to do upload an image to the database.

Thanks in advance for your help.

RafiIf you go to theData Access forum and search for BLOB you should be able to find the information you need.

Terri|||Thank you for information. I think I found there everything I needed

How to run multiple store procedures in SQL Reporting Service

Can we run two store procedures in the Query String under the Dataset section
of SQL Reporting Service 2005? It seems like the Query String only allow one
store procedure. Ww want to use a separate store procedure to open a
symmetric key before we run the second store procedure to retrieve the
sensitive data.What about writing a thrid procedure calling the two others ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--sql

How to ROLLBACK TRANSACTION on client level

Suppose i connect to db (SQL Server) from the client (.NET) and call to
some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
and before COMMIT or RALLBACK an error happen that imidietly stop the
execution of stored procedure, In the client i cacth this error but what
about an open transaction ?
HOW to rollback in the client?
Message posted via http://www.webservertalk.comYou can wrap your sp inside another one, get @.@.trancount before calling
second sp and compare after the call.
create procedure dbo.proc1
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.error int
begin transaction
insert into t1 values(@.p1, @.p2)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 1.', 16, 1)
return 1
end
insert into t2 values(@.p1)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 2.', 16, 1)
return 1
end
else
commit transaction
return @.@.error
go
create procedure dbo.proc2
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.tc int
declare @.rv int
declare @.error int
set @.tc = @.@.transcount
exec @.rv = dbo.proc1 @.p1, @.p2
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.tc != @.@.trancount
rollback transaction
return @.error
go
Call proc2 from your client app, instead calling proc1.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"E B via webservertalk.com" wrote:

> Suppose i connect to db (SQL Server) from the client (.NET) and call to
> some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
> and before COMMIT or RALLBACK an error happen that imidietly stop the
> execution of stored procedure, In the client i cacth this error but what
> about an open transaction ?
> HOW to rollback in the client?
> --
> Message posted via http://www.webservertalk.com
>|||thanks. However i find somthing more intresting, in my app i'm using
ADO.NET so when i close a connection (conection to db) with ADO.NET method
close() it rolls back any pending transactions.
Message posted via http://www.webservertalk.com

How to Reuse functions in script tasks ?

Hello

I have some common functions that i use in several script tasks. How du i store a function globaly so that i can use it from different projects and still only have to edit it one place ?

I think one way is to create a dll of the function and import in each of your script tasks. To Import the dll into each of the script you need to place the dll into <root>\Windows\Microsoft .Net\Framework\<Latest Version> and gac it.

I think this will help...

Thanks

Dharmbir

|||But when i import it, it will become "local" ? So if i want to change something in the function i'll have to import it again in all those scripts ?|||

Correct.

I am not able to think of any other way to do the same.

Because each script task is different Identity(different project), if you create a function into one ...the other script task will not be aware of it.

How do you share a global function b/w two different projects?

Thanks

Dharmbir

|||

You don't have to import in each of your script...

once u do changes copy to the <root>\microsoft.net\.... folder again and just gac it...

It should work

How to return an entire table using store procedure

hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

jOriginally posted by nano_electronix
hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

j

Hi, maybe I did not understand your problem entirely, but a stored procedure returning a recordset is as simple as

create procedure P as select * from sales
go

exec p
go

Hope, this helps.|||but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp

besides given the store procedure you shown there, how would you get it into a recordset/dataset using either asp or asp.net.

for example, in asp.net i make use of dataadapter to retrieve a table into a dataset (which can store multiple table) but if i use store procedure i am not sure how that can be done.

cheers

j|||i think i might know why you don't understand my problem

the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.

wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)

when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.

if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.

Please help

J|||Hi

Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.

Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.

Regards

Richard...

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@.CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();|||I had the same problem about a month ago. I think what you're looking for is the datareader. Here's an example of how it works:

Dim connection As New SqlConnection()
Dim cmdSelect As SqlCommand
Dim reader As SqlDataReader
Dim Num As Integer

Num = 12
connection = SqlConnection1

cmdSelect = New SqlCommand("exec sp_getNum @.date = " & Num), connection)
If connection.State <> ConnectionState.Open Then connection.Open()
reader = cmdSelect.ExecuteReader
datalist.DataSource = reader
datalist.DataBind()
reader.Close()
connection.Close()

If you need more tables, just created more readers. Is that what you're looking for?|||Originally posted by nano_electronix
but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
j

I would guess you would want to have one select statement per sproc. and then create a recordset for each executed sproc?|||thanx guys

I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.

Originally posted by nano_electronix

i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

J|||hi guys

first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.

i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.

i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.

cheers :)

j

how to return all store procedure names from a databases?

Hi all,

I require a script to get all store procedure names from a database. I managed to find a script on how to return all the tables names from a db . I was thinking there could be script that could do the same thing but instead it returns the sp names

Thanks

Matthew

One way would be to search the sysobjects table:

select name
from sysobjects
where type = 'P'
order by name

Another (better) alternative is:

select routine_name
from information_schema.routines
where routine_type = 'PROCEDURE'
order by routine_name

|||

With SQL 2005, use:

SELECT Name
FROM sys.procedures

|||

The examples below will return all non-system stored procedures.

Chris

--SQL Server 2000
SELECT [name]
FROM dbo.sysobjects
WHERE OBJECTPROPERTY([id], 'IsProcedure') = 1
AND OBJECTPROPERTY([id], 'IsMSShipped') = 0

--SQL Server 2005
SELECT [name]
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsMSShipped') = 0

How to return a value from SP

Hi all,

How to return a value from a store procedure?

I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

Here is an VBA example:

Dim GetDestinationID As Long

Dim conConnection As ADODB.Connection
Dim StrSQL As String

Set conConnection = CurrentProject.Connection

StrSQL = "usp_GetDestinationID " & 2 & ", " & _
GetDestinationID

conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

I would like to return GetDestinationID.

Here is the SP:

CREATE PROCEDURE [dbo].[usp_GetDestinationID]

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT

)

AS

BEGIN

set @.intDestinationID=(SELECT lv.DestinationID
FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID)

END
GO

What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

Regardshttp://dbforums.com/t916861.html|||HI all

I found a solution to my question about VBA calling a Stored Procedure and returning a value

VBA:
'-------------------
Private Function GetDestinationID(OrderID As Long) As Long
On Error GoTo GetDestinationID_Err

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = CurrentProject.Connection
.CommandText = "usp_GetDestinationID"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.intOrderID", adInteger, adParamInput, , OrderID)
.Parameters.Append .CreateParameter("@.intDestinationID", adInteger, adParamOutput)
.Execute
GetDestinationID = .Parameters("@.intDestinationID").Value
End With

WrapUp:


Exit_GetDestinationID:
Set cmd = Nothing
Exit Function

GetDestinationID_Err:
Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
Resume Exit_GetDestinationID
End Function

'----------------
T-SQL:

CREATE PROCEDURE dbo.usp_GetDestinationID

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

SELECT @.intDestinationID=lv.DestinationID
FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID

END
GO
'-----------

Thanks to Igor for suggestions.

Dani

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?
Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to retrieve all information abt all constraints inorder to drop and recreate

Hello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!First step - dropping:

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'|||i first need to store the information about the constraints before dropping them, so that they can be recreated later after the necessary changes have been made..i have 200+ tables...any tips?|||select *
INTO TempConstraintStore
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

INSERT INTO INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM TempConstraintStore

or something like that|||INSERT into system tables...hmmmmmmm

No thanks...

Just go in and Script the contraints and save the script

Do the drops, necessary changes have been made..then rerun the script

What are the changes?

If RI is out of wack the constraints will fail....|||i'm aware that scripting the constraints using EM would make life easier,but no such luck for me..it has to be done using TSQL..i'm using the information schemas to retreive info,like referential_constraints, columns,constraint_column_usage..is that enough??|||try to use SQL-DMO with something like that (VBS):

Set dmoSQLServer = CreateObject("SQLDMO.SQLServer")
dmoSQLServer.Connect "MyServer", "MyLogin", "MyPSWD"

For Each dmoObj In dmoSQLServer.Databases("MyDBName").Tables
If dmoObj.SystemObject = False Then
dmoObj.Script 134348800, dmoObj.Name & ".fky"
End If
Next

dmoSQLServer.Disconnect
Set dmoSQLServer = Nothing

One file storing all FK creates for each table (TableName.fky). If table have no FK this file will be empty|||Similar problem?

I simply want to add a foreign-key constraint to an existing column.

This is what I have but no luck so far.

alter table table_name with check
alter column column_name
add constraint foreign_key_name
references Reference_Table (Reference_column)

I know it can't be far from this - I think I've even done it before!

John|||I'd use something like:ALTER TABLE myTable
ADD CONSTRAINT myConstraint
FOREIGN KEY (myColumn)
REFERENCES anotherTable (differentColumn)-PatP