Script that will send the data results to a file

Question

Can we code this up as a script that will send the data results to a file? 

select A.last_name, A.first_name, A.disqual_num, A.decision_date, A.ssn

from State_Disqual_Data A

Cross join State_Disqual_Data B

where A.state = B.state

and (

(A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn <> b.SSN)

or

(A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date <> B.decision_date and A.ssn = b.SSN)

or

(A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num <> B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)

or

(A.last_name = B.last_name and A.first_name <> B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)

or

(A.last_name <> B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN))

order by a.ssn,a.last_name,a.first_name

Answer 1

u mean to say csv files?

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 2

CSV or txt, I tried using BCP got error message

SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
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
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

Answer 3

Hi Naveen,

Below is the code you required:

USE [Your Database]
GO
DECLARE @SQLCommand		[varchar](max)
	   ,@Query			[varchar](max)

SET @Query = 'select A.last_name, A.first_name, A.disqual_num, A.decision_date, A.ssn
				from State_Disqual_Data A
				Cross join State_Disqual_Data B
				where A.state = B.state
				and A.state=''''OR''''
				and ((A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn <> b.SSN)
				or (A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date <> B.decision_date and A.ssn = b.SSN)
				or (A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num <> B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)
				or (A.last_name = B.last_name and A.first_name <> B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)
				or (A.last_name <> B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN))
				order by a.ssn,a.last_name,a.first_name'

SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "' + @Query + '" queryout "C:\Output.txt" -T -c -t,''' 

PRINT @SQLCommand

EXEC (@SQLCommand)

Change the location of the output file appropriately

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

Thanks Basit, got below message and I don`t see the file on my local drive. I was running this script on server. Any helps will be appreciate.

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]
NULL

Answer 5

Sorry Naveen 

There is a space just before bcp with is truncated by the post editior. Try the following it will work 100 %

USE [Your Database]
GO
DECLARE @SQLCommand		[varchar](max)
	   ,@Query			[varchar](max)

SET @Query = 'select A.last_name, A.first_name, A.disqual_num, A.decision_date, A.ssn
				from State_Disqual_Data A
				Cross join State_Disqual_Data B
				where A.state = B.state
				and A.state=''''OR''''
				and ((A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn <> b.SSN)
				or (A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date <> B.decision_date and A.ssn = b.SSN)
				or (A.last_name = B.last_name and A.first_name = B.first_name and A.disqual_num <> B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)
				or (A.last_name = B.last_name and A.first_name <> B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN)
				or (A.last_name <> B.last_name and A.first_name = B.first_name and A.disqual_num = B.disqual_num and A.decision_date = B.decision_date and A.ssn = b.SSN))
				order by a.ssn,a.last_name,a.first_name'

SET @SQLCommand = 'EXEC xp_cmdshell '' bcp "' + @Query + '" queryout "C:\Output.txt" -T -c -t,''' 

PRINT @SQLCommand

EXEC (@SQLCommand)

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 6

No Luck, same message.

Answer 7

Basit,

Problem is not with the code, I assume its issue with BCP or xp_cmdshell.

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

Answer 8

This error occurs when you have not got the access to the proposed location for bcp file. For example, the script I posted, I specified the location for file as C:\OutPut.txt. I ensured before executing the script that SQL Service account got access to this location. Please check your location. 

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.