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 run user defined function on sql server 2005

Dear all

I wants to run sql server user defined function when linked two server.

I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?

Regards

Jerminxxx

You can call the function with [ServerName] .[DatabaseName].[User/Schema Name].[FunctionName].

|||

Just use the select ServerName.DatabaseName.OwnerName.Functionname

i.e Like select IFoundd10.Employee.dbo.getenc()

__________________________________________________________

Don't forget to click "Mark as Answer" on the post that helped you.

|||

Thanks for ur reply.

when i try to use this technique i got some error like .

"contains more than the maximum number of prefixes."

Can u help me

|||

Which version of SQL Server are you using ? Can you post some of the details of the 2 servers that you've linked, the function and from where are you trying to execute the function ?

|||

I'm using sql server 2005.

For Example:

We have two sql server named as server1 and server2. These two servers are already linked.

On server1 we have some user defined function.Amoung the function one function called getencf().

What i want .I wants to run function getencf() on server 2 environment.

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

CAn you help me

Regards

Jerminxxx

|||

jerminxxx:

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

Are you getting the same error ("contains more than the maximum number of prefixes.") which you posted in one of your posts ?

Are you able to run the function from server1 ?

|||

it will e hard due to prefixes number, if your function returns single value try to replace it( or add extra ) stored procedure on the server which will call you function. If your function returns results set try to use OPENQuery to execute it on remote server.

How to run TSQL from vb net 2003

Hi All
I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
In vb6 I called a function...
retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
which ran the TSQL script against OSQL. The script basically created the
Database in MSDE then imports a heap of tables from Access 2000.
Private Function ExecCmd(cmdline$)
'used for MSDE to launch osql
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret&
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
'Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
What is the preferred method of running a TSQL script from VB Net 2003 using
sqlclient class?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
> In vb6 I called a function...
> retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
> which ran the TSQL script against OSQL. The script basically created
> the Database in MSDE then imports a heap of tables from Access 2000.
> Private Function ExecCmd(cmdline$)
> 'used for MSDE to launch osql
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ret&
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
> ' Start the shelled application:
> ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
> 'Wait for the shelled application to finish:
> ret& = WaitForSingleObject(proc.hProcess, INFINITE)
> Call GetExitCodeProcess(proc.hProcess, ret&)
> Call CloseHandle(proc.hThread)
> Call CloseHandle(proc.hProcess)
> ExecCmd = ret&
> End Function
> What is the preferred method of running a TSQL script from VB Net
> 2003 using sqlclient class?
http://www.absistemi.it/permalink=tn169.ashx
this will use the very same feature with .Net..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how to retun results from Stored Proc

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

try a function instead|||

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

I think if you take out the Return at the end then stored procedure will return the result of the query.

How to restore SQL 7 DB to SQL 2005

Hi, All
I am trying to restore SQL 2000 DB into SQL 2005 Database.
I backup the database from SQL 2000.

From Management Suit, I try to restore database but I can not see network drive from there even though SQL serveris running under network account.
I could see network drive from SQL 2000 or at least I can type path to find backup file. However I can not do this..

My Q is:
1. How can I restore this SQL 2000 db to SQL 2005 using network path?
2. Since backup is SQL 2000 database, when I restore into SQL 2005, does restore upgrade system tables and other schema to SQL 2005 as well?
If not, what is the best practice to upgrade this database into SQL 2005?
Upgrading SQL 2000 current server to SQL 2005 is not an option at this point.
Eventually porduction server will be scrup and install SQL 2005 and then restore DBs into production machine...

Thx in advance
Jay
1. We support restoring from network paths. You need to ensure the sql service has access to the path. The GUI would also need access to the network path. You might try using an explicit UNC rather than drive letters.

2. We automatically upgrade the system tables as part of the restore (or attach).|||

Steve Schmidt

Thanks for the post here. But can you lay it down in plain english. What I mean is I need a step by step. What I have done is I have the backup for my SQL Server 2000 databases. I have created the tables in SQL Server 2005. Then I go to restore the files using the Restore Database in SQL Server 2005. I select the newly created database in the To Database on the general tab. Select From File on the general tab and browse for the backup file which I find. Check the restore check box then hit OK.

I get the following error.

// Start Error Message //

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'VM-WIN03SERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'shoplist' database. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
// End Error Message //

If I do an import it works just fine but you loss all your key info and other important elements just like when you do a import from SQL Server 2000 to SQL Server 2000.

|||

Your procedure is correct.

There is an "overwrite existing database" option on the "options" page of the restore dialog. You need to check it to avoid the error.

The error indicates that the backup set was not created by backing up the current database you plan to overwrite. So if you are sure that you have the correct backup set, check that box and the restore will procede.

Hope that helps.

|||

Hi,

I was having a similar problem. I am trying to restore a .bak file from another sql server 2000. We are looking at moving all the DB's to the new server running sql 2005.

I have tried a restore using the managment studion via right click on the databases and also tried making a new data base that I may restore into like the example above. I both cases I get an error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'SQLSERVPRO2'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

What the problem was here was the paths where the origional data files are not same in sql 2005 x64. Under the options section there is a place to redirect the files to restore.

|||

I am getting the same error msg. We did backup in SQL 2005 itself and wanted to try the restore operation but throws the same error. I did choose "force overwrite existing db" but still the same.

Any idea?

|||Anyone had solution to this? I'm experiencing the same error.|||

This error:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

indicates that restore can not find the path:

C:\Program Files\Microsoft SQL Server\MSSQL\Data

You either need to create the path yourself, or:

1. use "WITH MOVE" in the T-SQL

or

2. use the GUI to specify an alternate location for each database file

|||

To restore a SQL 2000 database on SQL 2005

create a new database in 2005 with the same name and compatibility set for SQL 2000 or the version you want to restore from.

restore this database with options 'overwrite existing database'

i did this from one server to another, but it is also possible from a .bak file ofcourse.

gtx. YAK

|||I have done this with about 30+ DB. The fastest way and most reliable was to back up the DB from the SQL 2000 machine on to the SQL 2005 machine via EM. Then just login to the sql 2005 machine and use the restore database from SSMS. This way you can insure all of your tables will stay exactly the way that you had them ie identities. I have tried many different ways and this is by far the most straight forward. Of course you could script the backup and the restore if you are going to be doing this often or have more than one DB to convert/move.|||

I am trying to restore a database from SQL7 to SQL2005 but get a message:

System.data.sqlclient.sqlerror: The backup set holds a backup of a database other than the existing database.

I have tried the restore and the inport option for the sql2005 database.

****Update

I used the overwrite option with a complete backup file. The procesws seamed to work but now I have a (6.5 Compatible) next to the dadabase name and there is nothing (tables, views, security, etc..) associated with the database. Now I can not delete or rename the database. Those options are not supported: 6.5 database compatibility level is not supported.

Any ideas?

|||

Okay since this forum has failed to answer the question, here's the process in SSMS.

1) Go to restore database

2) Select the database that you want to back up to

3) Locate the backup file on disk. You may have to put it into the MSSQL Server -> MSSQL.1-> MSSQL -> Backup Folder. It must be a .bak file.

4) Select the back that want to restore from the available backups.

5) Go to the top left "options" property and when you do that select "overrite existing database".

6) Now make sure that the path to the files on database to be restored are correct in this same dialog view. Look at the paths to the database file and the log file and make damn sure that they are the correct ones for the database to be restored. The problem here is that those paths are going to be for the filesystem that the backup came from, not the one you are goning to put the restore onto. That's the big problem here.

|||

it working with my good

just go options then chang the path for .mdf and ldf file

like : C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.ldf

choose new name for the database

clear all check box overwrite options

|||

Guys,

I have experienced challenges that only Microsoft can supply,

When all looks fine and it still doesn't work then it is time to copy and paste. I experienced similar problems and found when I copied the file path from windows explorer directly into the "options" -> "restore database files" ->"Restore As", it worked. I did not know that it was case sensitive, if not my DB was just tempremental.

If you follow the previous steps and copy and paste then it will hopefully work.

|||

Hey "Reformed ", yes it worked the way you told us. :) Thanks it was a great help!

The only thing that was not correct was the path ... which you highlighted.

Thanks again!