How to insert picture into Image column?

I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql

How to insert BLOB data Using Store Procedure

Hi,

Can we insert BLOB data using store procedure using Oracle and ASP.Net.

I have inserted BLOB data using insert command, now i want to insert that BLOB via store procedure...

any links/tips will be helpful...

Hi,

Of course, you can use stored procedure to insert BLOB data. Use some data type that takes byte array as parameter, and you can insert into the table. You can just use the INSERT command as the content of the stored procedure.

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
Olav
Olav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>
|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>
|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.

> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
OlavOlav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
OlavOlav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.

> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>sql

how to identify the fixpack level?

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?
tnksSET NOCOUNT ON
SELECT CONVERT(CHAR(25),@.@.SERVERNAME) AS 'SQL SERVER',
SUBSTRING(@.@.VERSION,23,4) AS 'PRODUCT VERSION',
SUBSTRING(@.@.VERSION,35,3) AS 'BUILD NUMBER',
CASE SUBSTRING(@.@.VERSION,35, 3)
WHEN '194' THEN 'NO SP'
WHEN '384' THEN 'SP1'
WHEN '534' THEN 'SP2'
WHEN '760' THEN 'SP3'

ELSE 'Unknown - may be a Hot-Fix version or script out of date'
END AS 'SERVICE PACK'
set nocount off|||thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??|||SP3 and SP3a have identical build numbers (760).

Also, you can use this:

SELECT
cast(@.@.microsoftversion / power(2, 24) as varchar(2)) + '.00.' + cast(@.@.microsoftversion & 0xffff as varchar(4)) as VERSION

To derive version info...

hmscott
thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??