Frenz,
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
Your response is highly appreciated.
-Cheeku
Hi,
I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.
I follow the following steps to achieve this:
01. Created a new SSIS package
02. Create a connection string to "(local)/." server, "tempdb" database
03. Set the "RetainSameConnection" property value to "TRUE"
04. Set the "DelayValidation" to "TRUE", where ever I found this property
04. In Control Flow I added to items
a. Execute SQL Task
b. Data Flow Task
05. For "Execute SQL task" I set the connection to "tempdb"
06. I written the following query
Create table #transfer_CompaniesToProcess_tbl
(
companyID int not null
)
GO
07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination"
08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command"
09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl"
10. When I clicked on the "OK" button; I ended with following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
I gone through the following article and it seems I missed some thing.
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Can any one have any idea where I am doing wrong?
Thanks
Sreekanth
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
View 2 Replies View RelatedHi All,
"Happy New Year!"
I need to append data to the existing Table1 from a .txt file stored like this: "http://xx0opt02.corpuk.net/ABC_REPORTS/DATA/Table1.csv"
(Columns are identical)
This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted.
I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.
I have a few questions:
*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?
**) I heard I need an SQL agent. If so why and what is it?
***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?
****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?
Thanks for your help,
Gezza
I am getting frustrated with the accounting database table structure. I am really struggling to get the ‘par hours’. This is the amount of time that someone is assigned to work based on their workgroup in the workhour table.
Table 1 –Workhour table
Hours Resourceid workhourgroupcode dayid
NULL TJOHNSO TJOHNSO 1
8.0 TJOHNSO TJOHNSO 2
NULL TJOHNSO TJOHNSO 3
8.0 TJOHNSO TJOHNSO 4
NULL TJOHNSO TJOHNSO 5
8.0 TJOHNSO TJOHNSO 6
NULL TJOHNSO TJOHNSO 7
Table 2 - Time
Work DayHours
WorkGroup Resourceid DayID Non TimeDayWorked Hours worked
NULL SMB ABOOKWALTER 1 NULL 2007-12-26 00:00:00.000 7.50
NULL SMB ABOOKWALTER 1 NULL 2007-12-27 00:00:00.000 7.50
NULL SMB ABOOKWALTER 1 NULL 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-28 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-26 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-27 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-28 00:00:00.000 7.50
When I link these together by the resourceid dates worked, but since there is no date in the workhour table I cannot even do an outer join. The only think I could think of was creating a temp table (or a view) to return a line item for every day during the timeframe given. I created a dayID for they second table, I am not sure how to proceed. I need a line item to return for every day of the month so it will look at the workhours table to see how many hours someone is supposed to work.That will allow me to bring into Crystal, group and total.
8.30 SMB ABOOKW 6 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKW 6 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKW 6 1.00 2007-12-28 00:00:00.000 7.50
NULL SMB ABOOK 7 NULL 2007-12-29 00:00:00.000 0
NULL SMB ABOOKW 7 NULL 2007-12-30 00:00:00.000 7.50
NULL SMB ABOOKW 7 NULL 2007-12-31 00:00:00.000 0
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go
Alter Procedure spReport_SomeFooReport
@SearchFromThisDate datetime = null, @SearchToThisDate datetime = null
As
Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)
set @TableUniqueIdentifier = newid()
set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
exec @SQLString
Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go
-------------------------------------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.
So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
View 9 Replies View Relatedhello friends!!
i am trying to create stored procedure but i am getting error
create proc t
@i int
as
if @i = 1
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'SS' s) p
end
if @i = 2
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'S' s) p
end
Server: Msg 2714, Level 16, State 1, Procedure t, Line 15
There is already an object named '#T' in the database.
Server: Msg 170, Level 15, State 1, Procedure t, Line 17
Line 17: Incorrect syntax near 'p'.
T.I.A
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
View 5 Replies View RelatedI am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
domainName=CONCAT('domain',User Name);
I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
View 16 Replies View RelatedI could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'
Is there a way to make a DTS package call a stored procedure that uses temp tables?
Thanks.
Hi,
I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?
Seoras
I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.
CREATE PROCEDURE usp_CreateTakeoff
@iEstimate int,
AS
CREATE TABLE ##Temp_Takeoff
(
Field1 ......
Field2 ......
)
-- Add Structural data
usp_AddStructural @iEstimateID, 1, 'Structural'
usp_AddForming @iEstimateID, 2, 'Forming'
...
...
...
GO
Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".
I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.
Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.
Any ideas?
Mike B
Hey,
I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?
Thanks
Hi,
I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting
invalid object errors on the temporary tables:
Invalid object name '#temp_table1'
The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.
Thanks,
Jeff
hi All,
I am using a temp table creating it as
create table #process
(
tons of coomuns in here
)
then
insert into #process(collumns)
select from peon
where etc....
Can i use the same temp table definition , but insert into another tempTable.Does alias help me accomplish this task.
Thanks for your input
In these two tables im just to bring the data back where the two DesignID's dont match. Im gettin an error
Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#ttTopSellers' does not match with a table name or alias name used in the query.
Declare @CustomerID as VARCHAR(25)
Set @CustomerID = 'DELCOZ01-10'
/*Figure the designs that stores carry*/
Select Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID] as RegionID, Region.Name
Into #ttDesign
From Mas.dbo.Item Item
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Inner Join Mas.dbo.DesignRegionIndex DRI
on Design.[ID] = DRI.DesignID
Inner Join MAS.dbo.Region Region
on DRI.RegionID = Region.[ID]
Inner Join MAS.dbo.CustomerClassificationRegionIndex CRI
on Region.[ID] = CRI.RegionID
Inner Join MAS.dbo.CustomerClassification CC
on CRI.CustomerClassificationID = CC.[ID]
Where @CustomerID = CustomerID
Group By Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID], Region.Name
/*This finds the top retail sales globally*/
Select Top 10 Sum(Sales) as Sales, DesignID, Design.[Description]
Into #ttTopSellers
From Reporting.dbo.RetailSales_ByStore_ByCustomer_ByDay_ByItem DI
Inner Join Mas.dbo.Item Item
on DI.ItemNumber = Item.ItemNumber
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Where [Date] >= Month(getdate())-12
and DesignID <> 0
Group By DesignID, Design.[Description]
Order by Sum(Sales) Desc
Select *
From #ttDesign
Where #ttDesign.DesignID <> #ttTopSellers.DesignID
--Drop Table #ttDesign
--Drop Table #ttTopSellers
Why cant I use the same temptable name i a stored procedure after i have droped it?I use the Pubs database for the test case.CREATE PROCEDURE spFulltUttrekk ASSELECT *INTO #tempFROM JobsSELECT *FROM #tempDROP TABLE #tempSELECT *INTO #tempFROM EmployeeSELECT *FROM #temp
View 1 Replies View Related
I am having problem with my temp table.
I cannot get the syntax correct.
I need to do the following, If @mybit & br > 0 then
insert childid 'condidtion case when br & @mybit > then 0'
into the temp table.
CREATE TABLE #tmp_table(
childid integer null
)
IF @mybit & br > 0 THEN
INSERT INTO #tmp_table
SELECT c.childid
VALUES (childid,
CASE WHEN br & @mybit > 0 THEN 1 ELSE 0
END)
FROM
child c
I'm trying to create an sql server login, which should have access to and may see only one database. So what I do, is create a login and set the db's owner to this login. I also give it the appropriate rights like public, datareader, datawriter etc. on this db.
Furthermore, the login has no rights on other databases on the sql server. However, the login can see the other databases if he registers the sql server with the provided login data. This is what we would like to prevent: he may only see his own database.
How could I tackle this problem? Any suggestions are welcome.
I have a stored procedure which contains a temp table called #NamesThis has n rows of values which are peoples names (they are varchars)i.e#NamesRickRobFrankI have a table called tblPeople.tblPeopleid Name Telephone1 Ric 012334213452 Robert 0321120931233 Paul 123 123 123 123 I want to find all the people in tblPeople whose names are like those in the temp table #NamesHow do I do this?
View 1 Replies View Related Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists.
It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong.
This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists.
CREATE PROCEDURE dbo.repTest_TempASBEGIN
CREATE TABLE #Temp( [iTestID] uniqueidentifier, [bTest] [bit], [cTest] [varchar] )
INSERT INTO #TempSELECT *FROM tTest
SELECT *FROM #Temp
END
I have been researching some performance problems in a very large
application and I have a couple of questions about temp tables. (SQL 7.0
SP2)
I have one large procedure that I have been using as a test case.
Originally this procedure was a cursor with lots of processing steps
involving writing to, reading from and deleting in temp tables inside the
cursor. I remember reading that temp tables inside a cursor were a
potential performance problem, so I rewrote the procedure, replacing the
cursor with a While Loop.
Doing this showed no increase in performance. Since Profiler was showing .5
second duration times on statements in the procedure accessing the temp
tables I tested some more. I moved all the create statements to the top of
the procedure, as I know these statements after processing steps can cause
recompiles to happen. Still no performance increase.
Finally I replaced all the temp tables with actual tables, just to see what
would happen. With no other changes the performance increased by more than
500%.
Can someone give me some clues as to what is happening here, because if this
is a symptom of something I don't understand, the potential performance
problems from other places where temp tables are similarly used in the
application are enormous.
Thanks.
The problem is we need local tables in Access for SubReports (can't use store procedures as record source for this), therefore trying to create temp table and link to Access for each report instance.
When we create the ## type table in tempdb this cannot be linked from Access (cannot be seen via DSN).
The only other option we can find is doing
CREATE TABLE tempdb.dbo.[tablename]
but this requires the user to have admin permissions and therefore be 'dbo' on tempdb and not 'guest'.
Any clues?
Palmy
Is there any way to test if a table exists in the temp dB. In a procedure I create table called ##Test but I want to test if it exists before it's created and if it does I want to delete it. (It has to be a ## table because it's in a procedure being called in another procedure depending on an IF statement). The reason I want to do this is that I have multiple users and Occasionally they get the ... There is already an object named '##Test' in the database. error
Many Thanks
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
Sql 2000
Hi All,
I am creating a dts package which copies data via transform data task from a sql sever connection to an excel spreadsheet connection.
The transform data task uses a query that goes like this.
create table #temp1(CName varchar(10))
Insert #temp1
select CName from tbl1
--tbl1 is in a user DB called SALES
create table #temp2(VName varchar(10))
Insert #temp2
select CName from #temp1
select * from #temp2
--I need the resultset of the last select statement into an excel spreadsheet.
The problem is I can't even create the dts package..it shows up with an error saying that #temp1 is an invalid object....I am guessing that since the default database for the transform data task has been selected as SALES it is trying to locate the the #temp1 or #temp2 table in that DB instead of looking in the TEMPDB....My question is..is there any workaround for this problem.
Sample code welcome!!!!
Any help appreciated !
TIA
Kinnu