SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 DTS parameter problem...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrpush
Starting Member

14 Posts

Posted - 03/14/2013 :  10:13:24  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 03/14/2013 :  10:19:16  Show Profile  Visit russell's Homepage  Reply with Quote
use a question mark for your parameter.

and wkending = ?

Edited by - russell on 03/14/2013 10:19:40
Go to Top of Page

mrpush
Starting Member

14 Posts

Posted - 03/14/2013 :  10:35:13  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 03/14/2013 :  10:58:54  Show Profile  Visit russell's Homepage  Reply with Quote
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 - 03/14/2013 :  11:27:45  Show Profile  Reply with Quote
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 - 03/14/2013 :  11:37:17  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 03/14/2013 :  12:14:31  Show Profile  Visit russell's Homepage  Reply with Quote
Awesome! Glad you got it worked out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000