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 = ?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 12:14:31
Awesome! Glad you got it worked out.
Go to Top of Page
   

- Advertisement -