September 9, 2002 at 12:17 pm
I am having a problem with one of my packages. I have a package that runs fine on my machine, but fails on the second task on the sever. Let me explain what the package does.
SQL Task 1
Drops existing table.
Creates new table.
SET NOCOUNT ON
DROP TABLE cub_bu_summary
DECLARE @code varchar(6),@CreateStr NVARCHAR(2000)
SET @CreateStr = 'CREATE TABLE [cub_bu_summary] (
[consmonth] datetime NULL,
[billcode] char (6) NULL,'
DECLARE stat_cursor CURSOR FOR
SELECT code FROM cub_stat_codes Where bu_summary = 1 ORDER BY code
OPEN stat_cursor
FETCH NEXT FROM stat_cursor
INTO @code
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateStr = @CreateStr + '[' + @code + '] decimal (16,2) NULL,'
SELECT @CreateStr
FETCH NEXT FROM stat_cursor
INTO @code
END
CLOSE stat_cursor
DEALLOCATE stat_cursor
DECLARE stat_cursor CURSOR FOR
SELECT code FROM cub_stat_codes Where bu_summary_ct = 1 ORDER BY code
OPEN stat_cursor
FETCH NEXT FROM stat_cursor
INTO @code
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateStr = @CreateStr + '[' + @code + '_ct] decimal (16) NULL,'
SELECT @CreateStr
FETCH NEXT FROM stat_cursor
INTO @code
END
SET @CreateStr = LEFT(LTRIM(RTRIM(@CreateStr)),LEN(LTRIM(RTRIM(@CreateStr)))-1)
SET @CreateStr = @CreateStr + ')'
SELECT @CreateStr
EXEC sp_executesql @CreateStr
CLOSE stat_cursor
DEALLOCATE stat_cursor
GO
Data Pump Task 1
Loads Data.
SQL Task 2
Updates existing columns.
The above package runs fine when being executed from a client machine, but fails on the Data pump task when exectued from the server. I get the following error when executing the package from the server.
Invalid object name 'Table I create in the first SQL task'
I look in to the database and the table didn't get created! But the task said it completed successfully! I run the same SQL that is in task 1 and it works fine from SQL Server Query Analyzer. I have also recreated the package on the server and the same thing happens.
Any feedback will be appreciated.
Ken
September 9, 2002 at 4:04 pm
Give the client, that activates the DTS Pkg, system administrator permission on your SQL server then give it a try. It sounds like security.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
September 9, 2002 at 4:08 pm
also the SQLAgentCmdExec user may need to be a member of the sysadmin group on your sql server...
Michael Weiss
Michael Weiss
September 10, 2002 at 4:14 am
SQLAgentCmdExec has nothing to do with a called package that is under the right of the user.
Depending on the users rights they may have created the table but under there user name instead of dbo. Make sure when the table is created it is qualified as dbo.tablename. Also have the user try creating the table logged in as themselves with QA to see what happens. You should in no way need to give them admin rights or even db_owner rights to the server just create table permissions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 12, 2002 at 9:22 am
Well I got it working! I don't know what did it. Things like this tend to frustate the hell outta me. Fixing something and not knowing what exactly fixed it. Heres what I did. Everything I did centered around my first task. I figured that something inside the the task was not working and error was not being reported. So I changed the Create to:
SET NOCOUNT ON
DROP TABLE cub_bu_summary
DECLARE @code varchar(6),@CreateStr NVARCHAR(2000)
SET @CreateStr = 'CREATE TABLE [WPPI_HDWH].[dbo].[cub_bu_summary] (
[consmonth] datetime NULL,
[billcode] char (6) NULL,'
didn't work!
So I figured something was happening in my @CreateStr. So I made it simpler.
DROP TABLE cub_bu_summary
DECLARE @code varchar(6),@CreateStr NVARCHAR(3000)
SET @CreateStr = 'CREATE TABLE [WPPI_HDWH].[dbo].[cub_bu_summary] (
[consmonth] datetime NULL,
[billcode] char (6) NULL)'
EXEC sp_executesql @CreateStr
DECLARE stat_cursor CURSOR FOR
SELECT code FROM cub_stat_codes Where bu_summary = 1 ORDER BY code
OPEN stat_cursor
FETCH NEXT FROM stat_cursor
INTO @code
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateStr = 'ALTER TABLE [WPPI_HDWH].[dbo].[cub_bu_summary] ADD [' + @code + '] decimal (16,2) NULL'
EXEC sp_executesql @CreateStr
FETCH NEXT FROM stat_cursor
INTO @code
END
CLOSE stat_cursor
DEALLOCATE stat_cursor
DECLARE stat_cursor CURSOR FOR
SELECT code FROM cub_stat_codes Where bu_summary_ct = 1 ORDER BY code
OPEN stat_cursor
FETCH NEXT FROM stat_cursor
INTO @code
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CreateStr = 'ALTER TABLE [WPPI_HDWH].[dbo].[cub_bu_summary] ADD [' + @code + '_ct] decimal (16) NULL'
EXEC sp_executesql @CreateStr
FETCH NEXT FROM stat_cursor
INTO @code
END
CLOSE stat_cursor
DEALLOCATE stat_cursor
GO
It works fine now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply