Looking for some fundamental help automating a query

Question

Looking for some fundamental help automating a query

n.heyen

SSCrazy

Points: 2849
More actions
December 17, 2009 at 1:12 pm

#218752

I have a query that runs fine in the query window but I don't understand how to get it to work in DTS. This is a SQL Server 2000 database, so limited to DTS for now. I've done some simple packages but have not dealt with creating a temp table, loading data form the database into the table, then using that with a join.
Please be patient if it appears that I don't know what I'm doing, 'cause that is pretty much the case.
In the end, I need to generate a pipe (vertical bar) delimited text file to send to a 3rd party. The working code is below. I guess I need to understand which connections, tasks, etc. and how to connect them so I can get a text file out.
Thanks in advance,
Norman
SELECT clarity_ser.PROV_ID, clarity_ser.PROV_NAME
INTO #clarity_ser
FROM CLARITY_SER
WHERE clarity_ser.prov_name NOT LIKE 'RADIO%'
AND clarity_ser.prov_name NOT LIKE 'zzz%'
AND clarity_ser.prov_name NOT LIKE 'test%'
AND clarity_ser.prov_name NOT LIKE 'PRENATAL%'
AND clarity_ser.active_status = 'Active'
AND clarity_ser.prov_type IN ('Physician')
--AND clarity_ser.prov_type IN ('Physician','Optometrist','Dentist','Audiologist','Nutritionist','Ophthalmologist','Psychologist','Physical Therapist','Psychiatrist')
ALTER TABLE #clarity_ser
ADD TL AS DATALENGTH(REPLACE(#clarity_ser.PROV_NAME,', ',',')), -- Length of the original Name
LN AS CHARINDEX(',',#clarity_ser.PROV_NAME)-1, -- Length of the Last Name
TrimLN AS
DATALENGTH(
ISNULL(
REPLACE(
STUFF(#clarity_ser.PROV_NAME,
CHARINDEX('"',#clarity_ser.PROV_NAME), -- First "
CHARINDEX('"',#clarity_ser.PROV_NAME,CHARINDEX('"',#clarity_ser.PROV_NAME)) -- Second "
,'')
,', ',',')
,REPLACE(#clarity_ser.PROV_NAME,', ',','))
),
TrimName AS
ISNULL(
REPLACE(
STUFF(#clarity_ser.PROV_NAME,
CHARINDEX('"',#clarity_ser.PROV_NAME), -- First "
CHARINDEX('"',#clarity_ser.PROV_NAME,CHARINDEX('"',#clarity_ser.PROV_NAME)) -- Second "
,'')
,', ',',')
,REPLACE(#clarity_ser.PROV_NAME,', ',','))
GO
DECLARE @client VARCHAR(255)
DECLARE @source VARCHAR(255)
SET @client = 'Monroe Clinic'
SET @source = 'Epic'
SELECT @source AS [Source], ser.prov_id
--, spec.line
, LEFT(#clarity_ser.PROV_NAME,LN) LastName,
CASE WHEN CHARINDEX(' ',RIGHT(TrimName, (TrimLN-LN)-1),1) > 0
THEN LEFT(RIGHT(TrimName, (TrimLN-LN)-1),CHARINDEX(' ',RIGHT(TrimName, (TrimLN-LN)-1),1))
ELSE RIGHT(TrimName, (TrimLN-LN)-1)
END FirstName,
CASE WHEN CHARINDEX(' ',LTRIM(RTRIM(RIGHT(TrimName, TrimLN-LN-1)))) = 0 THEN NULL
WHEN REPLACE(RIGHT(TrimName, TrimLN - CHARINDEX(' ',TrimName)),'.','') IN ('Jr','Sr') THEN NULL
ELSE LEFT(RIGHT(TrimName, TrimLN - CHARINDEX(' ',TrimName)),1)
END MiddleInitial
,ser.doctors_degree AS [Suffix], ser.external_name
,addr.addr_line_1, addr.addr_line_2, addr.city, state.abbr AS [State], addr.zip
,ser.sex, ser.birth_date, addr.email, addr.phone, addr.fax
,zc_specialty.abbr AS [Speciality], zc_specialty.name AS [Speciality Description]
,ser.active_status AS [Status]
,prov_NPI.identity_new_id AS [NPI]
FROM clarity_ser ser
JOIN #clarity_ser ON ser.prov_id = #clarity_ser.prov_id
JOIN clarity_ser_addr addr ON addr.prov_id = ser.prov_id
JOIN zc_state state ON state.state_c = addr.state_c
JOIN clarity_ser_spec spec ON ser.prov_id = spec.prov_id
JOIN zc_specialty ON zc_specialty.specialty_c = spec.specialty_c
LEFT OUTER JOIN (SELECT I.IDENTITY_NEW_ID, I.PROV_ID, I.LINE --alias for provider NPI
from IDENTITY_SER_ID_HX I
where I.LINE=(select max(I2.LINE)
from IDENTITY_SER_ID_HX I2
where I.PROV_ID = I2.PROV_ID)) prov_NPI
on prov_NPI.prov_id = ser.prov_id
WHERE ser.prov_id IS NOT NULL
AND ser.STAFF_RESOURCE = 'person'
AND ser.active_status= 'active'
AND #clarity_ser.LN > -1
AND ser.user_id IS NOT NULL
AND spec.line = 1
ORDER BY ser.prov_name
GO
DROP TABLE #clarity_ser

Viewing 12 posts - 1 through 11 (of 11 total)

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

as long as your code works, you should just be able to drop a SQL connection and an execute SQL task in a DTS package and execute it. If you are simply looking for a way to run this on a regular basis, you could turn it into a stored procedure and execute that from a SQL Agent job.

n.heyen SSCrazy Points: 2849 More actions · Answer 2

Thanks Adam,

I've tried that and it chokes on the "GO" statements. It doesn't run without them.

What I've tried is a simple OLE DB connection and a Text File (Destination) connected with a Transform Data Task.

When I start to define the columns in the Destination tab, it complains about the column names in the temp table. Do I need to create the temp table and populate that as a separate SQL task?

When I paste the SQL into the Transform Data Task and parse it, the GO statements generate errors about Incorrect syntax near 'GO'.

And I need to generate a pipe delimited text file. I can try making this a SP but I don't see a way to build the output text file. I'd love to know how to do that too!

Any suggestions or pointers to an example?

Thanks again,

Norman

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 3

you should be able to strip out the go statements. I missed the part where you need to create a txt file. sorry about that. DTS is the way to go then. you will want to probably use permanent tables to make the DTS work easier if it's possible.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Heh... I notice you have a "name splitter" in the code. Let me ask, what are you going to do with a name like Dr. Robert Douglas van Guttenburg III, PHD...;-)

--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)
Intro to Tally Tables and Functions

n.heyen SSCrazy Points: 2849 More actions · Answer 5

Hi Jeff,

This is basically the code you gave me earlier, glad I didn't muck it up so badly as to be unrecognizable. 😛

As to the good Doctor, business development has decided that we don't want to project that level of pretentiousness to potential patients and we will just call him Dr. Robert

Seriously, the data set is small enough that I don't have to worry about this now. For what we need, just using the name in the current form is enough. All they really want are first and last names; middle initials are a bonus but not an issue one way or the other. I suspect the third party we send this data to has the AMA lists and the NPI is a unique identifier. But that is another subject.

I just need to get this into a DTS so it can be run automagically without my input. I might just change the database structure to include the first and last names and MI and do away with building the temp table. Kind of hate to do that since it requires another level of approval and adds to the general level of maintenance that has to be done for upgrades.

Norman

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

If you don't know me by now, then let me tell you that I always have to ask "Are you sure?" when I see something like that. Same thoughts went through my head on the previous post where we did the splitting. 😉

So, are you all set with the DTS stuff or are you still stuck somewhere on this?

--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)
Intro to Tally Tables and Functions

n.heyen SSCrazy Points: 2849 More actions · Answer 7

Yes, I am still in need of getting this into DTS. The database is vendor provided and changes I make are problematic and make me responsible for any problems, real or imagined, caused by my changes. Hence the hesitancy in making a change to the table.

Norman

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Ok... so let's peel one potato at a time. My first question would be.... did you try just removing the "GO"'s?

--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)
Intro to Tally Tables and Functions

n.heyen SSCrazy Points: 2849 More actions · Answer 9

Hi Jeff,

Yes, I tried to remove the GO statements. I tried to replace them with ; as well.

With the GO statements in the query, the message is:

Error Source : Microsoft OLE DB Provider for SQL SErver

Error Description : Deferred prepare could not be completed.

Statement(s) could not be prepared.

Line 95: Incorrect syntax near 'GO'

Line 47: Incorrect syntax near 'GO'

Without the GO statements, there is a long list of messages about Invalid Column Name - looks like one for every time they are called. This list is LN, TrimName and TrimLN.

Replacing the GO with a ; gives the same messages as without the GO statements, missing column names.

What I have is the Microsoft OLE DB for SQL Server connection and a Text File (Destination) connected with a Transform Data Task. I get these messages when I click on teh Preview button on the Source tab.

Thanks again,

Norman

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Ah... I see the problem. The alter table does not happen at compile or run time. It happens when it is actually executed. The only way that this will work is if you use dynamic SQL for everything that refers to the altered version of the table.

--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)
Intro to Tally Tables and Functions

n.heyen SSCrazy Points: 2849 More actions · Answer 11

Thank you Jeff,

That explains the problem and suggests a solution. But I took the easy way out and violated a couple of Dr. Codd's rules and created a table in the database to replace the temp table. I need to build something to re-populate this table before the DTS package is executed. But for now, the table is small enough to not cause problems. At least that is the plan at the moment.

I appreciate your time and effort giving me a hand.

Norman