osql text file output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

osql text file output

Author

Topic

jeanna09
Starting Member

5 Posts

Posted - 2010-08-16 : 14:42:25

I may be overlooking something simple but why would the osql text file results add 2 bytes to the end of data types ST char(2) and LicenseNumber varchar(22) in a sql stored procedure?

I am trying to create a data file in ASCII format.

OSQL -E -D ImportSQL -Q "sp_OD21_Explore_Download" -h -s -w28 -o D:\VisionReportTests\ExploreTest2.txt

ExploreTest1.txt results:

ST LicenseNumber
--------------------------
WI afseraera
WI 3333
WI 3232232
WI 11
WI aerwer
WI aerarw
WI adasdf
WI 12121212

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-16 : 14:44:48

well, I gotta tell you right up front, that naming your homegrown sprocs starting with sp_ is NOT a good idea

Post the sproc so we can see it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeanna09
Starting Member

5 Posts

Posted - 2010-08-16 : 16:33:13

Why is sp_ not a good idea? This is how my co-worker wanted me to name the stored procedures.

Here is the sql stored procedure? For testing purposes, I am only using the first two fields. Be aware, I am creating temp tables with data from an Oracle database.

ALTER PROCEDURE [dbo].[sp_OD21_Explore_Download]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--Variables
DECLARE @Reserve char(1)
SET @Reserve = null

DECLARE @Reserve2 char(3)
SET @Reserve2 = null

DECLARE @Reserve8 char(8)
SET @Reserve8 = null

DECLARE @Reserve23 varchar(23)
SET @Reserve23 = null

DECLARE @Rated char(1)
SET @Rated = 'R'

DECLARE @ExcludedDriver char(1)
SET @ExcludedDriver = 'N'

DECLARE @ACCT# varchar(9)
SET @ACCT# = '602426'

DECLARE @Quoteback varchar(80)
SET @Quoteback = null

-- optional field. Let explore now if there are any changes with our products
DECLARE @PRODUCTFLAG varchar(9)
SET @PRODUCTFLAG = null

--Create Temp table for Active Policy information
create table #ActiveExplore(
PolicyID int,
Programcode char(2),
Policynumber char(7),
Maxseq char(2),
Maxversion char(4),
Effectivedate datetime,
Expirationdate datetime,
Term int,
Termmonths int,
ActivityDate datetime,
Paymentmethodid int,
NLECI char(1),
SchoolDistrID int,
OriginalEffDate datetime,
Insuredid int,
InsuredCode varchar(10),
SSNTaxID int,
Individuallastname varchar(60),
Individualfirstname varchar(60),
Line1Address varchar(40), --address at time of trans. current address on nanda file.
Line2Address varchar(40),
City varchar(40),
StateCode char(2),
ZipCode varchar(9),
PhoneNo varchar(15),
DailyDate datetime,
PrevDate datetime)

--Insert sp_Dly3_ActivePolicy fields into temp table
Insert into #ActiveExplore
exec sp_Dly03_ActivePolicy

create table #POLICYDRIVERS(
PolicyID int,
DriverID int,
DriverFirstName varchar(60),
DriverLastName varchar(60),
DriverMiddleInitial varchar(1),
DateofBirth datetime,
DriverGender char(1),
LicenseNumber varchar(22),
LicenseState char(2),
ExcludedDriver char(1),
DriverAuditUser varchar(60))

insert into #POLICYDRIVERS
select PolicyID, DriverID, FirstName, LastName, Middleinintial,
DateofBirth, Gender, LicenseNumber, LicenseState, ExcludedDriver, AuditUser
from OPENQUERY (JESS, 'Select PolicyID, DriverID, FirstName, LastName, Middleinintial,
DateofBirth, Gender, LicenseNumber, LicenseState, ExcludedDriver, AuditUser
from lamda.POLICYDRIVERS')

--1. Auto drivers information excluding driver name with "Extra",
-- license = "NONE" and state = "ZZ"

CREATE TABLE #EXPLORE_1(
ST char(2),
LicenseNumber varchar(22),
DriverLastName varchar(60),
DriverFirstName varchar(60),
DriverMiddleInitial char(1),
DateofBirth datetime,
DriverGender char(1),
ExcludedDriver char(1),
DriverAuditUser varchar(60),
ZipCode varchar(15),
Line1Address varchar(40),
Line2Address varchar(40),
PolicyID int,
Programcode char(2),
Policynumber char(7),
Maxseq char(2),
Maxversion char(4),
Expirationdate datetime)

INSERT INTO #EXPLORE_1
Select LicenseState, LicenseNumber, DriverLastName, DriverFirstName,
DriverMiddleInitial, DateofBirth, DriverGender, ExcludedDriver, DriverAuditUser,
NandaAddress_1.ZipCode, NandaAddress_1.Line1Address, NandaAddress_1.Line2Address,
#ActiveExplore.PolicyID, Programcode, Policynumber, Maxseq, Maxversion,
Expirationdate
FROM #ActiveExplore LEFT JOIN #POLICYDRIVERS
ON #ActiveExplore.PolicyID = #POLICYDRIVERS.PolicyID
LEFT JOIN OPENQUERY (JESS, 'Select Nandaid, AddressType, Line1Address, Line2Address, City,
StateCode, ZipCode, AuditUser from lamda.NANDAADDRESS') NandaAddress_1
ON #ActiveExplore.InsuredID = NandaAddress_1.NandaID
WHERE Programcode = 'AU'
and DriverFirstName NOT LIKE '%EXTRA%'
and AddressType = 'B' and LicenseNumber NOT LIKE '%NONE%'
and LicenseState <> 'ZZ'

--2. MAIN: Create File with no excluded drivers
Create table #EXPLORE_2(
ST char(2),
LicenseNumber varchar(22),
LastName varchar(25),
FirstName varchar(20),
Initial varchar(20),
DOB varchar(8),
ZipCode varchar(9),
Address varchar(25),
G char(1),
R1 char(1),
PolicySeq varchar(32),
Expdate varchar(8),
R2 char(2),
Quoteback varchar(80),
I char(1),
R8 char(8),
Product varchar(9),
ACCT varchar(9),
Reserve23 varchar(23))

INSERT INTO #EXPLORE_2
SELECT ST, LicenseNumber,
LEFT(DriverLastName, 25) LastName, LEFT(DriverFirstName, 20) FirstName, DriverMiddleInitial,
CONVERT(varchar,DateofBirth, 112) DOB, LEFT(ZipCode, 9) ZipCode, LEFT((ISNULL(Line1Address,'')+ ' '+ ISNULL(Line2Address,'')),25) Address,
DriverGender, @RESERVE R1, Policynumber + Maxseq PolicySeq,
CONVERT(varchar, Expirationdate, 112) ExpirationDate,
@RESERVE2 R2, @Quoteback Quoteback, @Rated I, @RESERVE8 R8,
@PRODUCTFLAG Product, @ACCT# ACCT, @RESERVE23 Reserve23
FROM #EXPLORE_1
WHERE ExcludedDriver = 'N' -- Drivers not exlcuded

--3. ADD to Step #2: File with driver excluded.
INSERT INTO #EXPLORE_2
SELECT ST, LicenseNumber,
LEFT(DriverLastName, 25) LastName, LEFT(DriverFirstName, 20) FirstName, DriverMiddleInitial,
CONVERT(varchar,DateofBirth, 112) DOB, LEFT(ZipCode, 9) ZipCode, LEFT((ISNULL(Line1Address,'')+ ' '+ ISNULL(Line2Address,'')),25) Address,
DriverGender, @RESERVE R1, Policynumber + Maxseq PolicySeq,
CONVERT(varchar, Expirationdate, 112) ExpirationDate,
@RESERVE2 R2, @Quoteback Quoteback, @ExcludedDriver I, @RESERVE8 R8,
@PRODUCTFLAG Product, @ACCT# ACCT, @RESERVE23 Reserve23
FROM #EXPLORE_1
WHERE ExcludedDriver = 'Y' -- Excluded Drivers

--4. Create File for DOWNLOAD and file to be used with other Reports
Create table #EXPLORE_4(
ST char(2),
LicenseNumber varchar(22),
LastName varchar(25),
FirstName varchar(20),
Initial varchar(20),
DOB varchar(8),
ZipCode varchar(9),
Address varchar(25),
G char(1),
R1 char(1),
PolicySeq varchar(32),
Expdate varchar(8),
R2 char(2),
Quoteback varchar(80),
I char(1),
R8 char(8),
Product varchar(9),
ACCT varchar(9),
Reserve23 varchar(23))

INSERT INTO #EXPLORE_4
SELECT ST, LicenseNumber, LastName, FirstName, Initial,
DOB, ZipCode, Address,
G, R1, PolicySeq, ExpDate,
R2, Quoteback, I, R8,
Product, ACCT, Reserve23
FROM #EXPLORE_2

--5.
SELECT ST, LicenseNumber
FROM #EXPLORE_4

END

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 09:45:36

sp_ is not a good idea, beacause sql server will confuse it with a system stored procedure, and will look to the master database first, which will slow down (however so slightly) the execution. The othe reason is, if, by chance you name a sproc the same as a system sproc, unless it's qualified you will be executing the system sproc...

check that...you will never execute your sproc...



CREATE TABLE myTable99(Col1 int)
GO

EXEC sp_help myTable99
GO

CREATE PROC sp_help @param varchar(8000)
  AS
	PRINT 'Ha ha'
GO

EXEC sp_help myTable99
GO

EXEC dba.dbo.sp_help myTable99
GO


DROP PROC sp_help
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 10:19:38

Looking through the sproc...

Was the developer of the sproc an M$ Access "Genius"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 10:21:39

Where does...


from OPENQUERY (JESS, 'Select PolicyID, DriverID, FirstName, LastName, Middleinintial,
DateofBirth, Gender, LicenseNumber, LicenseState, ExcludedDriver, AuditUser
from lamda.POLICYDRIVERS'

Come from?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeanna09
Starting Member

5 Posts

Posted - 2010-08-17 : 14:49:45

This data is coming from the PolicyDrivers table in an Oracle test database called JESS.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 15:09:41

If it's a linked server, why do you need to use OPENQUERY and why do you need to stuff it into temp tables (which you only do sometimes)?

In any case

can you make a copy of the sproc and try this at the bottom

SELECT '"'+ST+'"', '"'+LicenseNumber+'"'
FROM #EXPLORE_4

SELECT '"'+RTRIM(ST)+'"', '"'+RTRIM(LicenseNumber)+'"'
FROM #EXPLORE_4

And tell us what you see?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 15:23:14

Isn't that entire mess...just


SELECT * 
	 FROM (exec sp_Dly03_ActivePolicy)							    a
LEFT JOIN OPENQUERY (JESS, 'Select * from lamda.POLICYDRIVERS')	p
	   ON #ActiveExplore.PolicyID = #POLICYDRIVERS.PolicyID
LEFT JOIN OPENQUERY (JESS, 'SELECT * from lamda.NANDAADDRESS') NandaAddress_1
ON #ActiveExplore.InsuredID = NandaAddress_1.NandaID
WHERE Programcode = 'AU'
and DriverFirstName NOT LIKE '%EXTRA%'
and AddressType = 'B' and LicenseNumber NOT LIKE '%NONE%'
and LicenseState <> 'ZZ'
WHERE ExcludedDriver IN('Y','N')

What's the difference between Explore 1,2 & 4????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeanna09
Starting Member

5 Posts

Posted - 2010-08-17 : 16:06:26

Here are the results.

--------------------------
--
"WI""afseraera"

"WI""3333"

"WI""3232232"

"WI""11"

"WI""aerwer"

"WI""aerarw"

"WI""adasdf"

"WI""12121212"

"WI""aeraer"

The mess in 2 & 3 is creating a new field where included drivers is R instead of Y and excluded driver stays as N. This is the format the file needs to be in.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 16:12:30

I don't see any extra spaces..do you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 16:17:08

so...@ExcludedDriver I, and @Rated I i sthe only difference?

Ever here of CASE?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeanna09
Starting Member

5 Posts

Posted - 2010-08-17 : 16:18:29

Thank you Brett!

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 16:27:19

Good luck...I'm assuming you inhereted this....

If so you must be doing the Madison Blues

http://blueslyrics.tripod.com/lyrics/george_thorogood/madison_blues.htm

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources