Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...Try this:
http://www.sophos.com/virusinfo/ana...2tilebotav.html|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
David Portas
SQL Server MVP
--

Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...
Try this:
http://www.sophos.com/virusinfo/anal...tilebotav.html
|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
David Portas
SQL Server MVP

Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...Try this:
http://www.sophos.com/virusinfo/analyses/w32tilebotav.html|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
--
David Portas
SQL Server MVP
--

Am I really using the full 2GB of Ram?

We have a machine Server2000 / SQL Standard 2000.
This machine has 2.5GB of RAM, but when we go into Task Manager we see
the SqlServr.exe process is only using 1,740,000 KB of memory.
This comes out to about 1.65 GB.
I have heard that this might actually be the max amount of memory that
will be consumed. In other words, have we really reached our max
memory allowed by SQL Server 2000 Standard?
Also, we are thinking about what we can do to allow us to take
advantage of over 2GB. Is it correct that we a) Must have SQL Server
Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
Datacenter edition?
THANKS!
cmay,
SQL Server will only take as much RAM as it needs, and generally will
not release it unless asked for by the OS.
If you want to use more than 2Gb RAM for SQL Server you will need
Enterprise Edition.
Check out:
http://www.microsoft.com/sql/techinf...sKChooseEd.asp
and
http://www.microsoft.com/sql/evaluat...ew/default.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
cmay wrote:
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed. In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
>
> THANKS!
>
|||Mark,
Thanks for the reply.
I basically have 2 follow up questions:
1) If a computer running 2000 Standard has maxed out its 2GB of
memory, will it actually show up as 2GB of memory under the
SQLServr.exe process in task manager? Some have suggested that our
1,740,000 KB (about) is really the max that SQL Server Standard will
consume, even though it only adds up to about 1.65GB of RAM.
2) Can Enterprise edition make use of over 2GB of Ram while running on
Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
edition? I have read lots of stuff that says you *can* install SQL
Enterprise on W2k Server, but I also read that in order to use over
2GB, it must use AWE, which is only available on Advanced Server /
Datacenter editions of windows.
Can you clarify this?
|||"cmay" <cmay@.walshgroup.com> wrote in message
news:1118848547.194627.53370@.o13g2000cwo.googlegro ups.com...
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed.
This is likely. Depending on your configuration.

> In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
Try pinning this memory if you want the process to take the full 2GBs.
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'max server memory', '2048'
RECONFIGURE
EXEC sp_configure 'min server memory', '2048'
RECONFIGURE
EXEC sp_configure 'set working set size', '1'
RECONFIGURE

> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
You will need SQL Server and Windows Server editions that are beyond the
"Standard" editions.
http://support.microsoft.com/default...b;en-us;274750
...
|||Hi
SQL Server needs to leave about 256Mb or RAM in the MemToLeave area. This
allows for SQL Agent, XP's etc to run. With 2GB, the maximum SQL Server will
show is 1.65Gb.
Standard Windows 2000 supports up to 4GB or RAM. If you were to install SQL
server Enterprise Edition, it could use 3GB as the other 1GB is reserved for
the OS under Windows 32 bit.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmay" <cmay@.walshgroup.com> wrote in message
news:1118854442.903913.82200@.g47g2000cwa.googlegro ups.com...
> Mark,
>
> Thanks for the reply.
> I basically have 2 follow up questions:
> 1) If a computer running 2000 Standard has maxed out its 2GB of
> memory, will it actually show up as 2GB of memory under the
> SQLServr.exe process in task manager? Some have suggested that our
> 1,740,000 KB (about) is really the max that SQL Server Standard will
> consume, even though it only adds up to about 1.65GB of RAM.
> 2) Can Enterprise edition make use of over 2GB of Ram while running on
> Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
> edition? I have read lots of stuff that says you *can* install SQL
> Enterprise on W2k Server, but I also read that in order to use over
> 2GB, it must use AWE, which is only available on Advanced Server /
> Datacenter editions of windows.
> Can you clarify this?
>
|||Thanks Mike

Am I really using the full 2GB of Ram?

We have a machine Server2000 / SQL Standard 2000.
This machine has 2.5GB of RAM, but when we go into Task Manager we see
the SqlServr.exe process is only using 1,740,000 KB of memory.
This comes out to about 1.65 GB.
I have heard that this might actually be the max amount of memory that
will be consumed. In other words, have we really reached our max
memory allowed by SQL Server 2000 Standard?
Also, we are thinking about what we can do to allow us to take
advantage of over 2GB. Is it correct that we a) Must have SQL Server
Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
Datacenter edition?
THANKS!cmay,
SQL Server will only take as much RAM as it needs, and generally will
not release it unless asked for by the OS.
If you want to use more than 2Gb RAM for SQL Server you will need
Enterprise Edition.
Check out:
http://www.microsoft.com/sql/techinfo/planning/SQLResKChooseEd.asp
and
http://www.microsoft.com/sql/evaluation/overview/default.asp
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
cmay wrote:
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed. In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
>
> THANKS!
>|||Mark,
Thanks for the reply.
I basically have 2 follow up questions:
1) If a computer running 2000 Standard has maxed out its 2GB of
memory, will it actually show up as 2GB of memory under the
SQLServr.exe process in task manager? Some have suggested that our
1,740,000 KB (about) is really the max that SQL Server Standard will
consume, even though it only adds up to about 1.65GB of RAM.
2) Can Enterprise edition make use of over 2GB of Ram while running on
Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
edition? I have read lots of stuff that says you *can* install SQL
Enterprise on W2k Server, but I also read that in order to use over
2GB, it must use AWE, which is only available on Advanced Server /
Datacenter editions of windows.
Can you clarify this?|||"cmay" <cmay@.walshgroup.com> wrote in message
news:1118848547.194627.53370@.o13g2000cwo.googlegroups.com...
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed.
This is likely. Depending on your configuration.
> In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
Try pinning this memory if you want the process to take the full 2GBs.
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'max server memory', '2048'
RECONFIGURE
EXEC sp_configure 'min server memory', '2048'
RECONFIGURE
EXEC sp_configure 'set working set size', '1'
RECONFIGURE
> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
You will need SQL Server and Windows Server editions that are beyond the
"Standard" editions.
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
...|||Hi
SQL Server needs to leave about 256Mb or RAM in the MemToLeave area. This
allows for SQL Agent, XP's etc to run. With 2GB, the maximum SQL Server will
show is 1.65Gb.
Standard Windows 2000 supports up to 4GB or RAM. If you were to install SQL
server Enterprise Edition, it could use 3GB as the other 1GB is reserved for
the OS under Windows 32 bit.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmay" <cmay@.walshgroup.com> wrote in message
news:1118854442.903913.82200@.g47g2000cwa.googlegroups.com...
> Mark,
>
> Thanks for the reply.
> I basically have 2 follow up questions:
> 1) If a computer running 2000 Standard has maxed out its 2GB of
> memory, will it actually show up as 2GB of memory under the
> SQLServr.exe process in task manager? Some have suggested that our
> 1,740,000 KB (about) is really the max that SQL Server Standard will
> consume, even though it only adds up to about 1.65GB of RAM.
> 2) Can Enterprise edition make use of over 2GB of Ram while running on
> Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
> edition? I have read lots of stuff that says you *can* install SQL
> Enterprise on W2k Server, but I also read that in order to use over
> 2GB, it must use AWE, which is only available on Advanced Server /
> Datacenter editions of windows.
> Can you clarify this?
>|||Thanks Mike

Am I really using the full 2GB of Ram?

We have a machine Server2000 / SQL Standard 2000.
This machine has 2.5GB of RAM, but when we go into Task Manager we see
the SqlServr.exe process is only using 1,740,000 KB of memory.
This comes out to about 1.65 GB.
I have heard that this might actually be the max amount of memory that
will be consumed. In other words, have we really reached our max
memory allowed by SQL Server 2000 Standard?
Also, we are thinking about what we can do to allow us to take
advantage of over 2GB. Is it correct that we a) Must have SQL Server
Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
Datacenter edition?
THANKS!cmay,
SQL Server will only take as much RAM as it needs, and generally will
not release it unless asked for by the OS.
If you want to use more than 2Gb RAM for SQL Server you will need
Enterprise Edition.
Check out:
http://www.microsoft.com/sql/techin...esKChooseEd.asp
and
http://www.microsoft.com/sql/evalua...iew/default.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
cmay wrote:
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed. In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
>
> THANKS!
>|||Mark,
Thanks for the reply.
I basically have 2 follow up questions:
1) If a computer running 2000 Standard has maxed out its 2GB of
memory, will it actually show up as 2GB of memory under the
SQLServr.exe process in task manager? Some have suggested that our
1,740,000 KB (about) is really the max that SQL Server Standard will
consume, even though it only adds up to about 1.65GB of RAM.
2) Can Enterprise edition make use of over 2GB of Ram while running on
Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
edition? I have read lots of stuff that says you *can* install SQL
Enterprise on W2k Server, but I also read that in order to use over
2GB, it must use AWE, which is only available on Advanced Server /
Datacenter editions of windows.
Can you clarify this?|||"cmay" <cmay@.walshgroup.com> wrote in message
news:1118848547.194627.53370@.o13g2000cwo.googlegroups.com...
> We have a machine Server2000 / SQL Standard 2000.
> This machine has 2.5GB of RAM, but when we go into Task Manager we see
> the SqlServr.exe process is only using 1,740,000 KB of memory.
> This comes out to about 1.65 GB.
> I have heard that this might actually be the max amount of memory that
> will be consumed.
This is likely. Depending on your configuration.

> In other words, have we really reached our max
> memory allowed by SQL Server 2000 Standard?
Try pinning this memory if you want the process to take the full 2GBs.
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'max server memory', '2048'
RECONFIGURE
EXEC sp_configure 'min server memory', '2048'
RECONFIGURE
EXEC sp_configure 'set working set size', '1'
RECONFIGURE

> Also, we are thinking about what we can do to allow us to take
> advantage of over 2GB. Is it correct that we a) Must have SQL Server
> Enterprise 2000 and b) must run on Windows 2000 Advanced Server or 2003
> Datacenter edition?
You will need SQL Server and Windows Server editions that are beyond the
"Standard" editions.
http://support.microsoft.com/defaul...kb;en-us;274750
...|||Hi
SQL Server needs to leave about 256Mb or RAM in the MemToLeave area. This
allows for SQL Agent, XP's etc to run. With 2GB, the maximum SQL Server will
show is 1.65Gb.
Standard Windows 2000 supports up to 4GB or RAM. If you were to install SQL
server Enterprise Edition, it could use 3GB as the other 1GB is reserved for
the OS under Windows 32 bit.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmay" <cmay@.walshgroup.com> wrote in message
news:1118854442.903913.82200@.g47g2000cwa.googlegroups.com...
> Mark,
>
> Thanks for the reply.
> I basically have 2 follow up questions:
> 1) If a computer running 2000 Standard has maxed out its 2GB of
> memory, will it actually show up as 2GB of memory under the
> SQLServr.exe process in task manager? Some have suggested that our
> 1,740,000 KB (about) is really the max that SQL Server Standard will
> consume, even though it only adds up to about 1.65GB of RAM.
> 2) Can Enterprise edition make use of over 2GB of Ram while running on
> Windows 2000 Server, or must it run on W2k Advanced Server / Datacenter
> edition? I have read lots of stuff that says you *can* install SQL
> Enterprise on W2k Server, but I also read that in order to use over
> 2GB, it must use AWE, which is only available on Advanced Server /
> Datacenter editions of windows.
> Can you clarify this?
>|||Thanks Mike

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.
One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.
|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:
> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> > We have been using the textcopy.exe tool since SQL7 (we were able to still
> > use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> > can
> > use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> > record
> > in our database. Basically, the VB6 code calls a stored proc and passes
> > it
> > the name and location of the file. The stored proc will then use the
> > textcopy.exe to upload this file into an image column in a database table:
> >
> > SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> > password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> >
> > EXEC Master..xp_cmdShell @.cmd
> >
> > where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> > test.pdf).
> >
> > When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> > this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> > to
> > work in 2005.
> >
> > What is the best way to insert a file into an image column in the database
> > in 2005? I'd like to not have to re-architect this piece of our
> > application,
> > but I may have to...is there an alternative similar to textcopy.exe for
> > SQL
> > 2005? Indexing the file is not important - we just need to save the file
> > in
> > the database so that it can be viewed from a web application.
> >
> > Thanks!
> > Jeff M.
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or recor
d
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe t
o
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application
,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from you
r
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>