0

I'm having an issue with a stored procedure which SQL Server being executed as a scheduled task through Task Manager. I have a batch file containing the EXECUTE statement which is called by Task Scheduler. Platforms is SQL Server 2008 R2 on Windows Server 2008 R2.

The batch file code 

@echo off 
SQLCmd -S  lccc-cpc-sql -E -d NTSR -Q "Execute update_vw_NTSR_Base_AllRecords_Labels_new_proc"

This SP does the following:

  1. Drops a table
  2. Recreates it with updated data using a SELECT INTO statement

Problem: It's running the DROP statement, but failing on the SELECT INTO. Here's what's weird though:

If I execute the sp through SSMS (right click the sp, choose Execute) OR, view a query editor, run the code to drop the table and the SELECT INTO statement, it finishes correctly. It's a very large SELECT INTO statement - hundreds of columns and about 100 joins. The purpose is to join a lot of lookup tables to values so I have one place for my users to go for labeled data and some variables computed for user friendliness. It's messy, but it's what I have to work with.

Query timeout is set to 0 (no limit). This only happened recently as I added more columns and variables but it seems it'd fail called through any method, not just through the batch file. Any thoughts on how to make this work as-is (ie without breaking this up into multiple SELECT INTO statements)?

Thanks.

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
Clint Finch
  • 101
  • 1
  • 2
  • 8
  • 1
    When you say it's `not completing`, do you mean it's running for a long time so you are cancelling it? – DavidG Sep 22 '14 at 15:03
  • 1
    What actually happens? "*It Fails*" tells us nothing. – RBarryYoung Sep 22 '14 at 15:03
  • http://dba.stackexchange.com/questions/22020/running-queries-with-sqlcmd-vs-running-queries-with-ssms – DavidG Sep 22 '14 at 15:07
  • @DavidG, it runs a long time (typically 3 mins or so) then stops. I am not cancelling it. – Clint Finch Sep 22 '14 at 16:11
  • @RBarryYoung, I mean that the batch completes and the table is dropped, but it is not created again with the select into statement. The INTO statement fails to create a table. I do not receive an error – Clint Finch Sep 22 '14 at 16:11

0 Answers0