Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 8th, 2007, 11:29 AM
hai hai is offline
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default table variable query

I have a Stored procedure that use a temporary table. I am able to run the SP from query analyzer.
But if I run through Execute SQL Task Properties, I am getting error
"Invalid object name '#Codelist'"
I checked one topic here and I found that table variable will solve the issue.
I tried using the table variable but i am getting error.
Can someone say in detail how to use the table variable?
Can table variable be used in Stored procedure?

  #2 (permalink)  
Old June 8th, 2007, 01:23 PM
hai hai is offline
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can somebody help me out I tried creating a table variable but its throwing error as follows:
Must declare the variable '@CodeList'.
But I have declared the table variable in the top of code as
Declare @CodeList TABLE

The code I used is for update is
update @CodeList
    set enttype = (select a.description
            from @CodeList as c
                inner join dparty_anal as p
                    on p.code=c.Ccode
                inner join danal as a
                    on a.code = p.anal_code and a.sequence=p.sequence
            and @CodeList.Ccode = c.Ccode
            where p.sequence=24)

The error is happening at the following line
@CodeList.Ccode = c.Ccode
If I use the temporary table this logic works fine

  #3 (permalink)  
Old June 8th, 2007, 02:02 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

When you declare a table variable you need to define the structure of the table as you would when creating a normal table:
Code:
Declare @CodeList TABLE
(
  EntType NVARCHAR(100),
  OtherField INT
)
--

Joe (Microsoft MVP - XML)
  #4 (permalink)  
Old June 9th, 2007, 06:32 AM
hai hai is offline
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
I have created the structure like what you have mentioned.. I havent mentioned it but I had already done it..



  #5 (permalink)  
Old June 9th, 2007, 07:23 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

The syntax for the update looks strange. Can you break it down so that you can test the inner query to see if it works as a standalone? The standard SQL Server syntax for updates is:
Code:
UPDATE @CodeList
  SET field1 = alias1.field2
    FROM @CodeList alias1 <JOIN> Table2 alias2 ON alias1.field2 = alias2.field2 [more joins]
--

Joe (Microsoft MVP - XML)
  #6 (permalink)  
Old June 11th, 2007, 06:19 AM
hai hai is offline
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have used the syntax because I have several update statement. In that one some are using aggregate functions. Aggregate functions cannot be assigned to a column.
e.g
     accdate = (select convert(char, max(ddate), 103)
        from @CodeListas s
            inner join ddate as r on
            r.code = s.Ccode
            and @CodeList.Ccode = s.Ccode
        where
            r.date_type = 'ACF')
It will be great if you can tell me how to modify the update statement with the same logic you mentioned earlier?

 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using variable for table name phishcoder SQL Language 0 September 26th, 2006 11:54 PM
Can a Make Table Query produce a Linked table? kronik Access 5 May 16th, 2006 07:17 AM
Sql Query With Variable jittendersaini Beginning PHP 1 April 4th, 2006 08:33 AM
Make Table query : table in Another Database marnik Access 1 March 19th, 2005 12:39 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 08:42 PM



All times are GMT -4. The time now is 07:41 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.