textcopy.exe failure

[Connie]

I am running the following script:
begin
set quoted_identifier off

declare @pk int

declare @where_clause varchar(100)

declare @file_name varchar (50)

declare @debug varchar (50)

Declare @cmd varchar (50)

--debug

/*if @Debug = 1
print @cmd
exec Master..xp_cmdShell @cmd */
-- begin cursor

DECLARE LOOKUP CURSOR FOR select pr.[id]
from plan_report pr, plan_version pv
where pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time

>= pv.update_time

OPEN LOOKUP

FETCH NEXT FROM LOOKUP INTO @pk
-- Loop through the list

WHILE @@FETCH_STATUS = 0

BEGIN
SET @where_clause = 'Where' + '[ID]' + '=' + cast(@pk as
varchar(10))
SET @file_name = 'C:\'+cast(@pk as varchar(10))+'.pdf'

exec sp_textcopy @srvname = 'MILNPPRODSQL',

@login = 'sa',

@password = 'W3Ot-@PirI#a',

@dbname = 'Naviplan',

@tbname = 'Plan_Report',

@colname = 'document',

@filename = @file_name,

@whereclause = @where_clause,

@direction = 'o' -- 'o' for output, 'i' for input

-- loop cursor

SET @pk = NULL

SET @where_clause = NULL

SET @file_name = NULL

FETCH NEXT FROM LOOKUP INTO @pk
END
-- cleanup

CLOSE LOOKUP

DEALLOCATE LOOKUP

end

This script runs with no issue in our Dev environment (which has a
restore of our production db) but when I run in production I get the
following error:

The system cannot find the path specified.

I can't figure out what path it is erroring on?? Both servers are
Windows 2003. The environmental variables on each server is
identical. Only difference I can think of is that our production
server is a clustered sql server and our dev server is not....

Any help would be greatly appreciated...

Thanks,

Connie

[Erland Sommarskog]

Connie (cs*****@rwbaird.com) writes:

exec sp_textcopy @srvname = 'MILNPPRODSQL',
...
This script runs with no issue in our Dev environment (which has a
restore of our production db) but when I run in production I get the
following error:

The system cannot find the path specified.

I can't figure out what path it is erroring on?? Both servers are
Windows 2003. The environmental variables on each server is
identical. Only difference I can think of is that our production
server is a clustered sql server and our dev server is not....

We don't know what is in that sp_textcopy, but apparently textcopy is
on in the path on the production server. You may have to modify this
sp_textcopy, so that it does not assume that textcopy is in the path.

And, of course, if the production server is SQL 2005, then there is
no textcopy available.

Overall, I am not very fond of the solution of calling textcopy from
xp_cmdshell. If this is for an Agent job, I think it would be better
to do with an Active-X task instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Connie]

On Mar 7, 4:24 pm, Erland Sommarskog <[e-mail]:

Connie ([e-mail]) writes:

exec sp_textcopy @srvname = 'MILNPPRODSQL',
...
This script runs with no issue in our Dev environment (which has a
restore of our production db) but when I run in production I get the
following error:

The system cannot find the path specified.

I can't figure out what path it is erroring on?? Both servers are
Windows 2003. The environmental variables on each server is
identical. Only difference I can think of is that our production
server is a clustered sql server and our dev server is not....

We don't know what is in that sp_textcopy, but apparently textcopy is
on in the path on the production server. You may have to modify this
sp_textcopy, so that it does not assume that textcopy is in the path.

And, of course, if the production server is SQL 2005, then there is
no textcopy available.

Overall, I am not very fond of the solution of calling textcopy from
xp_cmdshell. If this is for an Agent job, I think it would be better
to do with an Active-X task instead.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, The reason I am doing this is to extract a blob from a sql
server table and store it into a location on our server. Here is what
is in the sp_textcopy:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S ' + @srvname
+
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

I believe that I must be having some sort of right issue on the
Production server where I am now trying to run this. It works
perfectly in Development.

[Erland Sommarskog]

Connie (cs*****@rwbaird.com) writes:

Erland, The reason I am doing this is to extract a blob from a sql
server table and store it into a location on our server. Here is what
is in the sp_textcopy:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S ' + @srvname
+
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

I believe that I must be having some sort of right issue on the
Production server where I am now trying to run this. It works
perfectly in Development.

No, it's not a permissions issue. But it would be as simple that on
the production box, SQL Server is not installed on the C disk. Or
that the 8.3 name for C:\Program Files\Microsoft SQL Server is
different. The first thing to try is replace the 8.3 parts with the
long names. Next is to check where textcopy is located on the
production server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Connie]

On Mar 7, 4:46 pm, Erland Sommarskog <[e-mail]:

Connie ([e-mail]) writes:

Erland, The reason I am doing this is to extract a blob from a sql
server table and store it into a location on our server. Here is what
is in the sp_textcopy:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S ' + @srvname
+
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

I believe that I must be having some sort of right issue on the
Production server where I am now trying to run this. It works
perfectly in Development.

No, it's not a permissions issue. But it would be as simple that on
the production box, SQL Server is not installed on the C disk. Or
that the 8.3 name for C:\Program Files\Microsoft SQL Server is
different. The first thing to try is replace the 8.3 parts with the
long names. Next is to check where textcopy is located on the
production server.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Did all of that already, checked the location of the textcopy.exe and
it does exist in the same location on dev as it does on production. I
tried using the long names and the short name both, still same
results... I have basically gone through and verified everything and
compared production to development, I am just frustrated right
now....Thanks for all the ideas and help though I am open for all and
any suggestions....

[Connie]

On Mar 8, 12:23 pm, "Connie" <[e-mail]:

On Mar 7, 4:46 pm, Erland Sommarskog <[e-mail]:

Connie ([e-mail]) writes:

Erland, The reason I am doing this is to extract a blob from a sql
server table and store it into a location on our server. Here is what
is in the sp_textcopy:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S ' + @srvname
+
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

I believe that I must be having some sort of right issue on the
Production server where I am now trying to run this. It works
perfectly in Development.

No, it's not a permissions issue. But it would be as simple that on
the production box, SQL Server is not installed on the C disk. Or
that the 8.3 name for C:\Program Files\Microsoft SQL Server is
different. The first thing to try is replace the 8.3 parts with the
long names. Next is to check where textcopy is located on the
production server.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-Hide quoted text -

- Show quoted text -

Did all of that already, checked the location of the textcopy.exe and
it does exist in the same location on dev as it does on production. I
tried using the long names and the short name both, still same
results... I have basically gone through and verified everything and
compared production to development, I am just frustrated right
now....Thanks for all the ideas and help though I am open for all and
any suggestions....- Hide quoted text -

- Show quoted text -

Well I got past that error, I took the textcopy.exe file and put it in
the root of C:\ and changed the stored proc to reflect that change,
and now it runs with no issues....It just seems that it cannot find it
under the sql server installation location for some reason.....I
double checked the path several times??? Go figure.. Thanks for the
help though your suggestions about the path led me to try this out :)