DTS parameter problem... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

DTS parameter problem...

Author

Topic

mrpush
Starting Member

14 Posts

Posted - 2013-03-14 : 10:13:24

Hi,

I have a DTS package that creates a CSV file.

In it, I have an Execute SQL task with the following code:

drop table mytabletemp

declare @wkenddate datetime

SELECT *
into mytabletemp
FROM othertable 
where this=that
and wkending = @wkenddate
order by 


update mytabletemp
set amount = amount * 100


ALTER TABLE mytabletemp ALTER COLUMN amount numeric (19,0) NOT NULL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytabletemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[othertable]


CREATE TABLE [dbo].[othertable] 

w all my table settings

When I click the PARAMETERS button, to define my wkenddate parameter, it says "the SQL statement does not contain any parameters".

Um, yes, it does.

This code works fine in Query analyzer.

Whats wrong?

Thanks much,

Mark

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 10:19:16

use a question mark for your parameter.

and wkending = ?

mrpush
Starting Member

14 Posts

Posted - 2013-03-14 : 10:35:13

Hi Russell,

Ok, I tried that.

Now I get

"ERROR SOURCE : Microsoft OLE DB Provider for SQL server
Error Description: Syntax error or access violation"

When I click OK it says:

"An error occurred while parsing the SQL statement for parameters. Please check the syntax of the SQL statement and verify that is is valid for the connection selected"

Any ideas?

Thanks,

Mark

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 10:58:54

Try adding a semi-colon after the select into query.

Other than that...not sure...been a long time since I've used SQL 2000.

Might have a look at this: http://msdn.microsoft.com/en-us/library/aa933469(v=sql.80).aspx

mrpush
Starting Member

14 Posts

Posted - 2013-03-14 : 11:27:45

Russell,

Nope, that did not do it either. I already read that link, it did not help either.

Not really sure, these are supposed to allow parameters, but its just not working.

Thanks for your help,

Mark

mrpush
Starting Member

14 Posts

Posted - 2013-03-14 : 11:37:17

Russell,

Well would you look at that, I put the ";" right after the DECLARE line and used "and wkending = ?" and it worked!

Thanks for the leads!

Much obliged!

Mark

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 12:14:31

Awesome! Glad you got it worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources