Run SQL Server 2005 Stored Procedure From DTS

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)

Run SQL Server 2005 Stored Procedure From DTS

Author

Topic

jaji03
Starting Member

5 Posts

Posted - 2008-02-26 : 12:26:50

Hello,

I'm trying to execute a SQL Server 2005 Stored Procedure from DTS via "Execute SQL Task Properties" (EXEC dbo.s_Test ?,?,?,?,?,?,?). Once I click the "Parameters" button, I get this error "Error Source: Microsoft OLE DB Provider for SQL Server. Error Description: Incorrect Syntax new 'OPTIMIZE'.

Is anybody familiar with this error? Any suggestions would be greatly appreciated :-)

P.S.
At first I wondered if we could run Sprocs from SQL Server 2005 in DTS, but then I created an ActiveX script and it worked:

Option Explicit

Function Main()
Dim Conn
Set Conn=CreateObject("ADODB.Connection")

'*************************************
DTSGlobalVariables("gblstrDBConn").Value="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MS_TestDB;Data Source=SQLTest\SQLTest"
'*****************************************************

Conn.Open=DTSGlobalVariables("gblstrDBConn")

conn.execute "dbo.s_Test 1,'01/01/2008','01/01/2008','F','TestDesc',1,'test'"

msgbox("ok")

Set Conn=nothing

End Function

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-26 : 15:26:37

Have you checked the sp from query analyzer and is it running OK?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

jaji03
Starting Member

5 Posts

Posted - 2008-02-26 : 15:37:04

Yes. The SP is executing with no issues.

quote:
Originally posted by jackv

Have you checked the sp from query analyzer and is it running OK?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

jaji03
Starting Member

5 Posts

Posted - 2008-02-26 : 16:39:46

Actually, I found out what the problem was...it's the company's security setting. The connection was for a QA database. Once I changed the connection to point to the Dev database, I was able to set the parameters...then I was able to change my connection back to QA and was able to execute the DTS package successfully...I believe I have execute rights, but no edit rights...WEW!!!! That took me all day to figure out. Hopefully this will help somebody else :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources