Script error when executing script with temp tables in Sql Server

Toad Data Point
1

Hi,

I’m having a problem executing a script…actually I think I’m having two.

  1. I have an automation script where I select ID’s from an Access DB in order to create a series of insert statements and save them to a file. I then take that script and merge it to another script and execute the merged script in SQL Server. I am getting an error on the script even though I can manually open the script and execute it with no problems against my Sql Server DB. I think there’s an issue with TDA using ODBC as the connection type for the SQL Server connection, and ODBC does not support some of the T-SQL syntax like 'IF OBJECT_ID(‘tempdb…#BASE_POP’) IS NOT NULL BEGIN
    DROP TABLE #BASE_POP
    END

  2. If I create a job by itself with only a SQL server connection in it…It doesn’t seem to like it when I create a temp table by using select * into #temp from t1.

Are these known issues?

2

Why are you using an ODBC connection to SQL Server? It should be using the native connection type along with the Script execute activity. This activity should execute your block of code with the same components that the editor uses. Check your connection and activity type. If this turns out to be ok, then run the script again and send me the log file as well as the script and all supporting files.

Debbie

3

I’m not using ODBC. When I create an automation script I use the Databases Connection Activity and choose access. I then execute the script.

Next I create another Database Connection Activity and choose SQL Server, and execute my script.

It’s breaking when trying to run my script against Sql Server in the automation script…yet I can compile and run the script directly against the Sql Server just fine.

4

Okay. So the issue isn’t the connection type. Can you send me your log file and all script files? Send to dpeabody@quest.com