Does DTS Mean "Do This Someotherway?"

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Does DTS Mean "Do This Someotherway?"

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-24 : 13:54:04

I have created a DTS package that pulls the data from a dBase5 file. I have never had any problem with this before. However, when setting up the process, I perform a bit of 'housekeeping': I check to see if the file exists, and if so then I drop it, either way, I create it.

Here's the code:

if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[RXCLAIMS]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[RXCLAIMS]

CREATE TABLE [Fowler].[dbo].[RXCLAIMS] (
[CLAIMNUM] nvarchar (15) NULL,
[PAYOR] nvarchar (5) NULL,
[MEMBERNUM] nvarchar (15) NULL,
[IDXMEMNUM] nvarchar (15) NULL,
[NAME] nvarchar (23) NULL,
[EMPNO] nvarchar (12) NULL,
[NETWORK] nvarchar (5) NULL,
[CLUSTER] nvarchar (5) NULL,
[CCOUNCIL] nvarchar (5) NULL,
[PRACTICE] nvarchar (5) NULL,
[PCP] nvarchar (8) NULL,
[RX_DEA] nvarchar (12) NULL,
[DT_WRITTEN] smalldatetime NULL,
[DT_FILLED] smalldatetime NULL,
[REFILL_NO] float NULL,
[NDC] nvarchar (13) NULL,
[FORMULARY] nvarchar (1) NULL,
[RX_COUNT] float NULL,
[MET_QUANT] float NULL,
[DAY_SUPPLY] float NULL,
[GENERIC] nvarchar (1) NULL,
[ING_COST] float NULL,
[DISP_FEE] float NULL,
[MEMPAYDIFF] float NULL,
[COPAY] float NULL,
[PAY] float NULL,
[PHARM_ID] nvarchar (15) NULL,
[AWP] float NULL,
[RX_NUMBER] nvarchar (15) NULL,
[LOAD_DATE] smalldatetime NULL
)

But every time I parse the query, I get this message:
"Error source: Microsoft JET Database Engine".
"Error description: Invalid SQL Statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE"

But a cut & paste to the SQA shows no problem. I don't get it. Anyone got any ideas on this?

Thanks...

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 15:16:18

I would guess that You don't have an SQL Server Datasource for Your SQL task.
Love the title of the thread Xerxes, well done!

rockmoose

jason
Posting Yak Master

164 Posts

Posted - 2005-03-24 : 15:26:29

If you haven't changed the package, then something else has changed. In recent updates to your computer?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-24 : 15:27:05

Thanks, 'Moose, I wish I was just as clever as figuring this out as I am coming up with nifty titles. (My fave is still that Outer Limits episode!)
I'll checkout my Data SOURCE and let you know if that was it.

I hope you'll like my comments regarding your preaching about NULLS.

And thanks for responding!

Semper fi, XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-24 : 15:47:55

Nope, haven't changed the package....and the dBase5 file is EXACTLY where it's supposed to be.

I even removed the existence check and drop statement and retried the Parsing of the Query (sounds like "the Gleaning of the Cube" or "the Weevils of Promiscuity" or something equally exotic).

Reshuffled Chickentracks read:

CREATE TABLE [Fowler].[dbo].[RXCLAIMS] (
[CLAIMNUM] nvarchar (15) NULL,
[PAYOR] nvarchar (5) NULL,
[MEMBERNUM] nvarchar (15) NULL,
[IDXMEMNUM] nvarchar (15) NULL,
[NAME] nvarchar (23) NULL,
[EMPNO] nvarchar (12) NULL,
[NETWORK] nvarchar (5) NULL,
[CLUSTER] nvarchar (5) NULL,
[CCOUNCIL] nvarchar (5) NULL,
[PRACTICE] nvarchar (5) NULL,
[PCP] nvarchar (8) NULL,
[RX_DEA] nvarchar (12) NULL,
[DT_WRITTEN] smalldatetime,
[DT_FILLED] smalldatetime,
[REFILL_NO] float,
[NDC] nvarchar (13) NULL,
[FORMULARY] nvarchar (1) NULL,
[RX_COUNT] float,
[MET_QUANT] float,
[DAY_SUPPLY] float,
[GENERIC] nvarchar (1) NULL,
[ING_COST] float,
[DISP_FEE] float,
[MEMPAYDIFF] float,
[COPAY] float,
[PAY] float,
[PHARM_ID] nvarchar (15) NULL,
[AWP] float,
[RX_NUMBER] nvarchar (15) NULL,
[LOAD_DATE] smalldatetime
)

Query parsing stabs result in the following message:

Error source: Microsoft JET Database Engine
Error description: Syntax error in CREATE TABLE statement

Ok....I'm blind (well, I sport lineless bifocals).

What is it that I don't see?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

jason
Posting Yak Master

164 Posts

Posted - 2005-03-24 : 15:54:42

Not sure, but I think the engine is the problem. You should be using a SQL OLEDB provider, right?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-24 : 15:59:36

quote:
Originally posted by jason

Not sure, but I think the engine is the problem. You should be using a SQL OLEDB provider, right?

Yes.

Semper fi, XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources