devin.sills (2/14/2012)

---

Elliot --- thanks for your reply. The problem is that once the variable is added, it cannot be changed at run time b/c the new behavior is such that...

Just been playing with this and I think that Eugene's solution may have a slight flaw. SET NOEXEC OFF needs to be executed between creates to ensure that the subsequent...

That's ugly, but also taught me something, thanks! 🙂

scottichrosaviakosmos (2/14/2012)

---

Actually I am doing this in a script and i am doing this for 50 procedures and each procedure is more then 50 lines. So, dynamic query will not...

Ignore this - Adi has already spotted the issue.

Can you post the text of the error you are receiving?

Try using the inserted table.

I do not think that Ian was taking the **** with his 'strange requirements' comment, just helpful.

ram_kamboz2k7 (2/14/2012)

---

EXEC xp_cmdshell 'bcp "SELECT * FROM TM37_1_2_ONLINE.TM37_1_2_ONLINE.dbo.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'

Well I have changed it around and seems to come up with the same errors.

The users table...

ram_kamboz2k7 (2/14/2012)

---

declare @path varchar(2000)

set @path = 'C:\'

EXEC master.dbo.xp_create_subdir @Path

I have created a directory first and foremost.

EXEC xp_cmdshell 'bcp "SELECT * FROM USERS" queryout "C:\gradfive.txt" -T -c -t,'

I want the...

SQL_Surfer (2/13/2012)

---

Another thing to note is that DTS uses a sql login but SSSI uses a service account. But that still doesn't explain why it wouldn't error out (can't find...

Don't know (been years since I last used DTS and I've forgotten how it works!)

SQL_Surfer (2/13/2012)

---

Connection is pointing to remote server, which is configured to connect through service account.

Stored proc on remote server references a linked server to the calling server (where the package...

The ExecuteSQL task requires a connection. Where is that connection pointing?

Can you also post the full text of the error message which you are getting?

So, in SSIS, you are using an ExecuteSQL task to initiate a stored proc on a different server, is that correct?

Have you configured a connection to the other server?

If not,...

vinodoss57 (2/13/2012)

---

Hello sir,

this is vinoth. i have faced the same issue you have mentioned.I ran the query INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\resource_add.xls;','SELECT...