BCP Code not behaving same on diff servers...

Question

BCP Code not behaving same on diff servers...

sindhigrl

Default port

Points: 1448
More actions
October 18, 2011 at 4:14 pm

#261554

We have two SQL Server instances, ‘dev’ (for database development) and ‘prod’ (for production). The instances are on separate physical servers.
One of our developers wrote a sproc which receives an XML string from the calling command file; stores the XML as a row in a global temporary table; then uses the bcp utility to dynamically create a folder and write the row out as an .rdl file within the folder, on a network file share.
On the dev server, the sproc runs flawlessly, producing the .rdl file in its folder, in just moments.
On the prod server, the sproc creates the folder, but then hits this statement:
EXEC master..xp_cmdshell @bcpString3
and then ‘spins’ endlessly. At that point it also produces these error messages:
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
Permissions are the same on both servers. Configuration also looks the same, but maybe there’s something I’m missing? Remote connections are enabled on both servers as well. The code is listed below. Many thanks in advance for your help!
ALTER PROCEDURE [dbo].[WriteStringToFile_sp]
(
@stringXML,
@pathVARCHAR(500),
@nameVARCHAR(500)
)
AS
DECLARE @fileAndPathVARCHAR(1000),
@bcpString1VARCHAR(1000),
@bcpString2VARCHAR(1000),
@bcpString3VARCHAR(2000),
@mkdirCmdVARCHAR(1000)
SET NOCOUNT ON
SELECT @fileAndPath=
'"'
+ @path
+ '\'
+ @name
+ '"'
-- Note: bcp doesn't support local temporary tables. Use a global temporary table instead.
CREATE TABLE ##atemptbl(string XML)
INSERT ##atemptbl (string)
VALUES (@string)
-- See http://msdn.microsoft.com/en-us/library/ms162802.aspx for bcp documentation.
-- Note: the 'mkdir' command is issued for each instance of @path - no checking is done within
-- this sproc to determine if the folder already exists.
-- If the folder already exists, a warning will be issued, 'A subdirectory or file xxx already exists.',
-- where xxx is the subdirectory.
SET @bcpString1 = 'bcp ##atemptbl out '
SET @bcpString2 = ' -c -T'
SET @bcpString3 = @bcpString1 + @fileAndPath + @bcpString2
SET @mkdirCmd = 'mkdir ' + '"' + @path + '"'
EXEC master..xp_cmdshell @mkdirCmd
EXEC master..xp_cmdshell @bcpString3
DROP TABLE ##atemptbl
Thanks Anita
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Has a proxy been created for the use of xp_CmdShell? Has xp_CmdShell been enabled? Can the production server "see" the file path you're trying to use?

Try running the following on the dev and the production server...

EXEC xp_CmdShell 'Dir whateverpathyoure using';

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

p.s. The "Underground Tour" with you guys was an absolute blast!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sindhigrl Default port Points: 1448 More actions · Answer 3

Thanks for the reply Jeff!!

Yes, I can get a dir listing from both servers - they are able to see the network file server where we are writing the files out to. Both have xp_cmdshell enabled.

Like I said, the folders get created, but that's where it stops on the prod server. I'm running the sproc under myself(sysadmin) or I run it under SQL Server agent within a job which is using a service acct that has sysadmin permissions. We keep going back to some configuration difference, since it works on one server but not the other, but I'm just not seeing it.

The underground tour was a blast! The whole week was great and I really enjoyed the conversations over dinner with you and Roy. Can't wait till we do it again next year!

Thanks!

Anita

Thanks!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Both the test server and the production server is on the same version? Check if you can set the query timeout to some higher value.

BTW, looking forward to next year at PASS

-Roy

sindhigrl Default port Points: 1448 More actions · Answer 5

Hi Roy!

We took a look at the remote query timeout and it’s set to 600 seconds which is the default on both servers, not sure if this is what you meant?

Thanks!

Anita

Thanks!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

Yes, that is what I meant. It is set pretty high.

Just one stupid question, this is not a named instance, right?

-Roy

sindhigrl Default port Points: 1448 More actions · Answer 7

You are a genius!! That was it. After I read your question I googled it and found that he needed to add the instance name because by BCP looks for the default instance. These set of servers are our older set, thankfully on our new test, dev and prod environments they are all named instances(which will make things more consistant). We added it and it works. Neither one of us have worked much with BCP so we learned something new. Thank you so much for your help!

SET @bcpString1 = 'bcp ##atemptbl out '

SET @bcpString2 = ' -c -S server\instance -T'

SET @bcpString3 = @bcpString1 + @fileAndPath + @bcpString2

SET @mkdirCmd = 'mkdir ' + '"' + @path + '"'

Thanks

Anita

Thanks!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Ah, So not a stupid question. I am glad that you got it fixed.

-Roy

sindhigrl Default port Points: 1448 More actions · Answer 9

My dad always told me... "There are never any stupid questions!" 😉

Thanks!