March 1, 2011 at 12:19 pm
Running SQL 2008 R2 and getting the following error:
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL
Named Pipes is enabled.
Remote connection enabled.
Executing as myself which has full dbo rights.
any suggestions?
This was running on my local dev machine (SQL 2008 *NOT R2)
I have hunted for advice all over.. Tried everything I can think of. Really need this going today so I must digress to asking 🙁
Any help would be much appreciated.
JW
** I DID NOTICE... In SQL config. if I change the order of named pipes and TCP making named pipes first the error changes to ref. tcp. Not sure if that is a clue; means nothing to me but maybe it means something.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 1, 2011 at 1:08 pm
What are you executing in the call to xp_cmdshell...are you running something that connects back to the SQL Server, like bcp? If so, please provide the command line (with any sensitive info scrubbed of course).
Is the login that is executing the xp_cmdshell command part of the sysadmin Server Role on the instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 1, 2011 at 1:15 pm
Thank you for your reply.. Here is the SPROC with some data altered of course.
(
@dbName varchar(100) = '[eDirect]',
@sql varchar(8000) = 'select LastName, FirstName, PreTitle, SufTitle as Suffix, OldPriNumb as OldStreetAddress
, OldPreDirc as OldPreDirectional, OldPriName as OldStreetName, OldSuffix as OldStreetSuffix
, OldPSTDirc as OldPostDirectional, NewPreDirc as NewPreDirectional, NewPriName as NewStreetName
, NewSuff as NewSuffix, NewPostDir as NewPostalDirectional, NewCityNME as NewCityName, NewST as NewState
, NewZip, NewPL4Code as NewPlus4
from FFUpdate WHERE CustomerID = 80002 AND Transmitted = 0'
)
as
PRINT 'Environment Set'
PRINT ''
PRINT 'DBName: ' + cast(@dbName as varchar (50))
PRINT ''
PRINT ' @sql: ' + cast(@sql as varchar (8000))
PRINT ''
PRINT ''
PRINT 'Begin processing'
PRINT''
if @sql = '' -- or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
SELECT 0 as ReturnValue -- failure
RETURN
END
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
PRINT 'Query data set built. Now processing Export SQL Statement '
PRINT ''
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand + 'f:\FFMun\someclient\Output\someclientreport.xls -c -w -T -U sa -P sa","-CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 1, 2011 at 1:39 pm
Executing as myself which has full dbo rights.
Is your login in the sysadmin Server Role? Is it a domain login, a local Windows login or a SQL Login?
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand + 'f:\FFMun\someclient\Output\someclientreport.xls -c -w -T -U sa -P sa","-CRAW'
Why are you providing -T and -U/P?
Also, you are not providing -S meaning bcp will attempt to connect to the default SQL Server instance. Is your server the default instance (running on port 1433)? I would recommend providing -S in all cases for clarity and not rely on the implicit behavior of bcp.
Have a look at the BOL article for bcp:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 1, 2011 at 2:25 pm
Still no go :(.
Changed to:
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand + 'f:\FFMun\FirstMerrit\Output\FirstMerritFFMun.xls -S -C -U sa -P mypassword -CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
Also tried -T no User / Pass specified, same result.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 1, 2011 at 3:28 pm
You'll want to provide the "server\instance" name after -S.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 1, 2011 at 5:11 pm
I have tried that but I could not get the syntax correct I guess.... Here is where I am calling the DB
EXEC eDirect..xp_cmdshell @bcpCommand
the instance and server are both the same name
EZCOMPLIANT\EZCOMPLIANT
So... could you please show me how to call that in the EXEC above???
Calling as EZCOMPLIANT.eDirect does not throw an error but does not fix the problem... I can not call the server / instance as I have not been able to figure the synatx for that.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 1, 2011 at 9:27 pm
I am very very close now.. thank you so much for your help..
I have one more issue. Now I create an export that is 0 bytes with an error of
Enter the file storage type of field LastName [nvarchar]:
I am going to work on figuring that out now however if you have any recommendations that would be GREAT.
thanks again for your help you got me past a big road block.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 1, 2011 at 10:16 pm
Jeffery Williams (3/1/2011)
I am very very close now.. thank you so much for your help..I have one more issue. Now I create an export that is 0 bytes with an error of
Enter the file storage type of field LastName [nvarchar]:
I am going to work on figuring that out now however if you have any recommendations that would be GREAT.
thanks again for your help you got me past a big road block.
Sure, I am happy to help.
Submit a -c switch on your bcp command. This will instruct the app to retrieve the data in "character" format which should do away with that prompt and retrieve all data in standard text format.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 2, 2011 at 7:09 am
Thats did the trick. You have no idea how much of a help you have been. Thank you so much!
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 2, 2011 at 7:46 am
You're very welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 1:15 pm
Ok Now I am having a problem with a similar procedure as posted previously. I can not figure out why this will not run, can someone help please?
The error result;
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
The procedure I am running:
DECLARE @InvoiceNumber as bigint
,@InvoiceCount as int
,@FileName as varchar(1000)
,@ProcessionCenter as int
,@BatchID as int
,@Count as int
DECLARE @InvoicesToExport TABLE
(InvoiceNumber int )
INSERT INTO @InvoicesToExport
(InvoiceNumber )
SELECT InvoiceNumber
FROM InvoiceHeader
WHERE MovedToPrintStream=0
SELECT @Count = count(*)
FROM @InvoicesToExport
SET @FileName = 'Applied032011.txt'
DECLARE @bcpCommand varchar(8000)
SET @bcpCommand =
'bcp " SELECT ih.InvoiceNumber, ih.CustomerID, ih.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), id.Quantity, 0, 0, 0, 0, 0, 0, 0, ih.InvoiceTotal, ih.InvoiceTotal, 0, 0, 0, '' '',c.CustomerName, coalesce(ca.Address1, c.CustomerContact), ca.City, ca.State, ca.ZipCode, 0, 0, ih.ProcessingCenterID, ih.ProcessingCenterID from eDirect..InvoiceHeader as ih JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1 WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber FROM eDirect..InvoiceHeader WHERE MovedToPrintStream = 0) ORDER BY id.Quantity DESC" queryout'
SET @bcpCommand = @bcpCommand + ' d:\ffmun\PrintStream\' + 'sampleexportheader.txt' + ' -w -T -U sa -P sa","-CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
-- PS_InvHeader.txt
SET @bcpCommand =
'bcp " SELECT RIGHT((''00000'') + cast(''00000'' + ih.InvoiceNumber as varchar), 6), RIGHT(('' '') + cast('' '' + ih.BatchID as varchar), 6), RIGHT((''00000'') + cast(''00000'' + id.DetailID as varchar), 6), RIGHT((''00000'') + cast(''00000'' + ih.CustomerID as varchar), 6), id.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 1, TransactionType, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), RIGHT(('' '') + cast('' '' + '' '' as varchar), 6), RIGHT(('''') + cast('' '' + id.Quantity as varchar), 12), RIGHT(('' '') + cast('' '' + ''000-'' as varchar), 3), (''000'' + id.Quantity), 0,0,0,0,0,0, RIGHT(coalesce(cast(id.Quantity as varchar), ''0000''), 4), 0,0,0,0,0,0,0,0,0,0,0,0,0,0, id.LineDescription,(id.UnitPrice * id.Quantity), 0, 0,0,0,0,0, ((id.UnitPrice *id.Quantity) - (id.UnitPrice *id.Quantity * 2)), cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 0, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 0, ''q'',0,0,0,0,0,0 from eDirect..InvoiceHeader as ih JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1 WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber FROM eDirect..InvoiceHeader WHERE MovedToPrintStream = 0) ORDER BY id.Quantity DESC" queryout'
SET @bcpCommand = @bcpCommand + ' d:\ffmun\PrintStream\PS_InvDetail.txt -w -T -U sa -P sa","-CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 26, 2011 at 1:21 pm
Hey Jeffery, I am not seeing a -S on your new bcp command-line. If it is not provided bcp will try to connect to the local default instance. Is it deliberate?
If that is not the issue or you're having trouble with a new issue please post to a new topic.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 1:38 pm
Ok I saw that after posting... sorry. So I made a couple changes and now I don't get a specific error but I don't get a file generated either.
The result window just shows all the switches available for the bcp command
The messages window shows 6 rows affected, 13 rows affected, 8 rows affected.
The sql code I am running is now the following:
DECLARE @InvoiceNumber as bigint
,@InvoiceCount as int
,@FileName as varchar(1000)
,@ProcessionCenter as int
,@BatchID as int
,@Count as int
DECLARE @InvoicesToExport TABLE
(InvoiceNumber int )
INSERT INTO @InvoicesToExport
(InvoiceNumber )
SELECT InvoiceNumber
FROM InvoiceHeader
WHERE MovedToPrintStream=0
SELECT @Count = count(*)
FROM @InvoicesToExport
SET @FileName = 'Applied032011.txt'
DECLARE @bcpCommand varchar(8000)
--SET @bcpCommand = @bcpCommand + ' ' + @OutputPath + @ReportName + '.xls -S EZCOMPLIANT\EZCOMPLIANT -c -T -CRAW' ---U sa -P Brittany5 -CRAW'
-- -S EZCOMPLIANT\EZCOMPLIANT -c -T -CRAW' ---U sa -P Brittany5 -CRAW'
SET @bcpCommand =
/*
'bcp " SELECT ih.InvoiceNumber, ih.CustomerID, ih.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), id.Quantity, 0, 0, 0, 0, 0, 0, 0, ih.InvoiceTotal, ih.InvoiceTotal, 0, 0, 0, '' '',c.CustomerName, coalesce(ca.Address1, c.CustomerContact), ca.City, ca.State, ca.ZipCode, 0, 0, ih.ProcessingCenterID, ih.ProcessingCenterID
from eDirect..InvoiceHeader as ih
JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber
JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID
JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1
WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber
FROM eDirect..InvoiceHeader
WHERE MovedToPrintStream = 0)
ORDER BY id.Quantity DESC" queryout'
*/
'bcp " SELECT ih.InvoiceNumber, ih.CustomerID, ih.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), id.Quantity, 0, 0, 0, 0, 0, 0, 0, ih.InvoiceTotal, ih.InvoiceTotal, 0, 0, 0, '' '',c.CustomerName, coalesce(ca.Address1, c.CustomerContact), ca.City, ca.State, ca.ZipCode, 0, 0, ih.ProcessingCenterID, ih.ProcessingCenterID
from eDirect..InvoiceHeader as ih
JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber
JOIN eDirect..ClientCompany c on c.CustomerID = ih.CustomerID
JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1
WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber
FROM eDirect..InvoiceHeader
WHERE MovedToPrintStream = 0)
ORDER BY id.Quantity DESC" queryout'
--SET @bcpCommand = @bcpCommand + ' d:\ffmun\PrintStream\' + 'sampleexportheader.txt' + ' -c -T -U sa -P sa","-CRAW'
SET @bcpCommand = @bcpCommand + ' d:\ffmun\PrintStream\' + @FileName + ' -S EZCOMPLIANT\EZCOMPLIANT -c -T -CRAW' ---U sa -P Brittany5 -CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
-- PS_InvHeader.txt
SET @bcpCommand =
'bcp " SELECT RIGHT((''00000'') + cast(''00000'' + ih.InvoiceNumber as varchar), 6), RIGHT(('' '') + cast('' '' + ih.BatchID as varchar), 6), RIGHT((''00000'') + cast(''00000'' + id.DetailID as varchar), 6), RIGHT((''00000'') + cast(''00000'' + ih.CustomerID as varchar), 6), id.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 1, TransactionType, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), RIGHT(('' '') + cast('' '' + '' '' as varchar), 6), RIGHT(('''') + cast('' '' + id.Quantity as varchar), 12), RIGHT(('' '') + cast('' '' + ''000-'' as varchar), 3), (''000'' + id.Quantity), 0,0,0,0,0,0, RIGHT(coalesce(cast(id.Quantity as varchar), ''0000''), 4), 0,0,0,0,0,0,0,0,0,0,0,0,0,0, id.LineDescription,(id.UnitPrice * id.Quantity), 0, 0,0,0,0,0, ((id.UnitPrice *id.Quantity) - (id.UnitPrice *id.Quantity * 2)), cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 0, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 0, ''q'',0,0,0,0,0,0 from eDirect..InvoiceHeader as ih JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1 WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber FROM eDirect..InvoiceHeader WHERE MovedToPrintStream = 0) ORDER BY id.Quantity DESC" queryout'
SET @bcpCommand = @bcpCommand + ' d:\ffmun\PrintStream\PS_InvDetail.txt -w -T -U sa -P sa","-CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
/*
UPDATE InvoiceHeader
SET MovedToPrintStream = 1
WHERE InvoiceNumber IN (SELECT TOP 1 InvoiceNumber FROM InvoiceHeader WHERE MovedToPrintStream = 0
ORDER BY Quantity DESC)
*/
--InvoiceNumber, CustomerID, ProcessingCenterID, EntityID, BatchID, CompanyID, InvoiceDate, InvoiceTotal, StatusID, MovedToPrintStream, Emailed, Printed,RecordDate, InvoiceNumber, DetailID, LineDescription, ProcessingCenterID, ItemID, UnitPrice, UnitSize, Quantity, Discount, Void, InvoiceDate
--JOIN eDirect..InvoiceDetail id on id.InvoiceNumber = ih.InvoiceNumber
--JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 26, 2011 at 1:42 pm
Keep at it...personally I never use bcp from a stored proc because it means I have to enable xp_CmdShell which is a security risk and so I choose not to use it. That said, one alternative would be to write a PowerShell script that called bcp for you.
If you want to or must stay within a stored proc setting then the best way to debug this stuff is to print bcp statement instead of executing it, and then try executing it yourself from the command line to see that it works.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply