bcp [PLS_WORK].[dbo].[ACHLDR_Stage_X150753] in "C:\PLS\Data\PRU_ALLIANCE.txt" -SNJROS1BBLD0304\DEV2K08 -T -c

quote:

---

C:\>bcp [PLS_WORK].[dbo].[ACHLDR_Stage_X150753] out "C:\test.dat" -SNJROS1BBLD03
04\DEV2K08 -T -c
Password:
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: R
equested server name not found.
Establishing connection failed.

---

quote:

---

C:\>ping njros1bbld0304

Pinging njros1bbld0304.prudential.com [48.160.98.188] with 32 bytes of data:

Reply from 48.160.98.188: bytes=32 time=53ms TTL=120
Reply from 48.160.98.188: bytes=32 time=2ms TTL=120
Reply from 48.160.98.188: bytes=32 time=2ms TTL=120
Reply from 48.160.98.188: bytes=32 time=3ms TTL=120

Ping statistics for 48.160.98.188:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 2ms, Maximum = 53ms, Average = 15ms

---

quote:

---

Originally posted by Kristen

Haven't tried BCP in 2K8 ... but

BCP -?

should give you the switches? or BOL will. Maybe something has changed subtly?

Sorry, only 101-level-help, but just in case you haven't scratched-that-itch it might help?

---

bcp [PLS_WORK].[dbo].[ACHLDR_Stage_X150753] in "C:\PLS\Data\PRU_ALLIANCE.txt" -S"NJROS1BBLD0304\DEV2K08" -T -c

DECLARE @cmd varchar(8000)
SET @cmd = 'bcp [PLS_WORK].[dbo].[ACHLDR_Stage_X150753] out D:\Test.dat -SNJROS1BBLD0304\DEV2K08 -T -c'
exec master..xp_cmdshell @cmd

DECLARE @cmd varchar(8000)
SET @cmd = 'bcp [DIVERSITY].[dbo].[USERS] out D:\Test.dat -SPAERSCBVD0403\dev -T -c'
exec master..xp_cmdshell @cmd

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'PRUDENTIAL\NJROS1BBLD0304$'.
NULL

(3 row(s) affected)

quote:

---

Phil: I'm a god.
Rita: You're God?
Phil: I'm a god. I'm not the God... I don't think.

Rita: You're not a god. You can take my word for it; this is twelve years of Catholic school talking.

Phil: Well maybe the real God uses tricks, you know? Maybe he's not omnipotent. He's just been around so long he knows everything.

---

quote:

---

Originally posted by tkizer

I forgot to mention that once you fix the path, the SQL Server service will need to be restarted in order to see the change. The path gets loaded into its memory, so it won't see the new path until the next startup.

---

quote:

---

Originally posted by tkizer

Dude, so you work on Sybase, DB2, Oracle, and SQL Server? I sure hope they pay you tons!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" PLS_WORK.dbo.ACHLDR_Stage_X150753 in "C:\PLS\Data\PRU_ALLIANCE.txt" -SNJROS1BBLD0304\DEV2K08 -T -c

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000

2207 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 391    Average : (5644.50 rows per sec.)

C:\>
C:\>

quote:

---

Sajesh,

To make life easier...take your excel spreadsheets, and make sure the have data in a format that matches the columns in the database

create a staging table, the use bcp.

The do a join on these 2 columns...the staging table does not need an index because it will be scanned anyway (being the driver)

USE [PLS_Work]
GO

CREATE TABLE [dbo].[ACHLDR_Stage_X150753](
[acct_ledger] [char](3),
[acct_num] [char](5),

) ON [PRIMARY]
GO

Then do the bcp load from cmdshell (>>Run >> CMD). This creates a dos window

Then execute

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" PLS_WORK.dbo.ACHLDR_Stage_X150753 in "C:\PLS\Data\PRU_ALLIANCE.txt" -SNJROS1BBLD0304\DEV2K08 -T -c

You do this in a dos prompt so it makes sure it uses your credentials, and uses your drive mappings

Then, do your query

SELECT *
FROM PLS.dbo.ACHLDR a
INNER JOIN PLS_WORK.dbo.ACHLDR_Stage_X150753 xxx
ON a.acct_ledger = xxx.acct_ledger
AND a.acct_num = xxx.acct_num

The other way you were trying..

SELECT * FROM ACHLDR
WHERE acct_num+acct_ledger IN (
SELECT 'XYZ1123' UNION ALL
SELECT 'XYZ1123' UNION ALL
..
..
..
20,000 more times
)

Will only cause a scan...and I wonder about an upper limit on the size of a sql query

To get the data out, I can help you with that as well...

First things first

---

quote:

---

yeah got it..that was quick.I modified the query to include all the rows from the stage table..

---