SQL2005 Reporting Service - Using Temp Tables?

Oct 31, 2007

Hello everyeone,

I'm very new to this reporting services and am trying to see whether this service can be utilized in our company's sales system.

I've searched quite a few documents about Reporting Service tutorials and it only shows how to make very basic reports, using simple query and one dataset.

Is there any document or manual that shows how to generate a report using a stored procedure or complex queries with temp tables.

If above is not possible, ca I join multiple datasets then?

Please help~...

Thank you in advance!!

View 5 Replies


ADVERTISEMENT

SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

Feb 8, 2008

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.

Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.



----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.

If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.

View 3 Replies View Related

Temp Tables In Integration Service

Aug 29, 2006

I am trying to move a flat file into a temporary table but the pre-execute phase looks for the table and fails the package. Is there away around this? I have set the connection RetainSameConnection to true but that doesn't appear to help in the dataflow.

View 6 Replies View Related

Getting Rid Of #temp Tables Without Restarting SQL Service

Aug 29, 2006

If you create a #temp table on stored procedure  #1
and then call another stored procedure #2  from #1; if stored procedure #2 has an error in it, the #temp table will not release even though stored procedure #1 has a drop table statement in it.

View 5 Replies View Related

SQL Server 2008 :: Service Broker And Temp Tables

Feb 12, 2015

Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?

View 5 Replies View Related

X-ref Documentation For DTS-package,views,tables,Reporting Service,Analyze Service

Mar 27, 2006

We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.

It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.

Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.

While everything exists in different SQL databases it should possible to do.

Has anyone any comments or the same needs ?

View 1 Replies View Related

Reporting Services - Use Of Temp Tables

Nov 12, 2006

Hello all,

I need to create a report based on a stored procedure. This stored procedure contains temp tables. At the time of creating the report, I receive a message mentioning - There is an error in the query. Invalid object name '<temp table name'>

Doesn't Reporting Services support temp tables?

Thanks all,
Saurav

View 2 Replies View Related

Working Around Temp Tables In Reporting Services.

Jun 15, 2006

I have been working for days to translate a report out of an old system and in SQL reporting services.

Getting the basic code down to get the required data was easy, however getting it to work in reporting services has turned into a nightmare.

Why, because I have been told that SQL reporting services does not allow temporary tables...HUH!

Ok, so how am I supposed to take three data queries and munge them together into a report.

Here is the query that does work, can anyone give me an idea of how to make this work given the limitations I have run up against.

I have already thought of using a store procedure, but we have ruled that out since would likely have to do it via linked servers, which would be expensive. We thought of having it just create and then link real tables and then delete them...not sure thats going to work, and again probably talking linked servers to get that to work.

Code:
select distinct al3.asg_location into ##tmp1
from dbo.probsummarym2 AL3
inner join dbo.probsummarym1 AL1 on AL3.number=AL1.number
where AL1.assignment='international client services'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
and al3.asg_location is not null




SELECT AL3.asg_location as asg_location, Count (AL3.resolve_met) as met_sla into ##tmp2
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
AND AL3.resolve_met='t'
AND AL1.assignment='international client services'

)
GROUP BY AL3.asg_location

SELECT AL3.asg_location as asg_location, Count (AL1.status) as sch_closed into ##tmp3
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code='Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}

)
GROUP BY AL3.asg_location

SELECT AL3.asg_location as asg_location, Count (AL1.status) as unsch_closed into ##tmp4
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}

)
GROUP BY AL3.asg_location



select ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
from ##tmp1 left outer join ##tmp2 on ##tmp1.asg_location = ##tmp2.asg_location
left outer join ##tmp3 on ##tmp1.asg_location = ##tmp3.asg_location
left outer join ##tmp4 on ##tmp1.asg_location = ##tmp4.asg_location
group by ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
order by ##tmp1.asg_location

drop table ##tmp1, ##tmp2, ##tmp3, ##tmp4

View 21 Replies View Related

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

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"?

View 2 Replies View Related

How To Avoid Space Of Text Box In Reporting Service If It Is Null Reporting Service

May 16, 2008



i have a list report
some values of this are blank or null
i want to avoid that spacing if it is null
like
name:jjj
lastname kkk
firstname
address uuuu

i want it should be
name jjj
lastname kkk
address uuuu

View 3 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

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 Related

Temp. Tables / Variables / Process Keyed Tables ?

Feb 22, 2008

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)

View 1 Replies View Related

How To Connect To Analysis Service Database Through Reporting Service?

Mar 31, 2006

Hello,

In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.

I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.

I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.

As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.

Thank you very much,

Annie

View 5 Replies View Related

Error Code 2259 Applying SQL2005 Service Pack

Jun 6, 2006

I have SQL 2005 Express installed. When I try to apply service pack 1 to this by going through the upgrade process, I get an error ' THe installer has encountered an unexpected error. The error code is 2259. Table(s) update failed'

Any ideas? Help!

Ranjit Charles

View 4 Replies View Related

SQL2005 Database Service Fails OWC11 Setup Failure --missing 4c483c.msi--HELP Please!!

Nov 8, 2006

Hi all,
I had to uninstall SQL2005 Service, client-tools & BI studio applications on my laptop running XP SP-2. Now, I'm trying a fresh install, a simple install of >>just<< the 2005 DB service is failing.
Snippet of log file-------------------------
MSI (s) (F8:80) [22:35:02:484]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (F8:80) [22:35:02:484]: Warning: Local cached package 'C:WINDOWSInstaller4c483c.msi' is missing.
Snippet of log file-------------------------


CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Failed to find help file for LCID :3081
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.1399.0
<EndFunc Name='DwLaunchMsiExec' Return='0' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2006/10/8 22:18:10, returned true
Running: SetPackageInstallStateAction at: 2006/10/8 22:18:10
Complete: SetPackageInstallStateAction at: 2006/10/8 22:18:12, returned true
Running: DeterminePackageTransformsAction at: 2006/10/8 22:18:12
Complete: DeterminePackageTransformsAction at: 2006/10/8 22:18:25, returned true
Running: ValidateSetupPropertiesAction at: 2006/10/8 22:18:25
Complete: ValidateSetupPropertiesAction at: 2006/10/8 22:18:25, returned true
Running: OpenPipeAction at: 2006/10/8 22:18:25
Complete: OpenPipeAction at: 2006/10/8 22:18:25, returned false
Error: Action "OpenPipeAction" failed during execution.
Running: CreatePipeAction at: 2006/10/8 22:18:25
Complete: CreatePipeAction at: 2006/10/8 22:18:25, returned false
Error: Action "CreatePipeAction" failed during execution.
Action "RunRemoteSetupAction" will be skipped due to the following restrictions:
Condition "Action: CreatePipeAction has finished and passed." returned false.

The OWC11 setup failure is triggerring SQL DB Service installation to fail as well.
Please help!!!

Regards,
Uday

View 7 Replies View Related

Reporting Services Menu Does Not Appear When Install Reporting Service Add-in For SharePoint

May 14, 2008

Hi all,

I have some problem about reporting service add-in.

After I install reporting service add-in for SharePoint, reporting service menu does not appear in Application Management Tab in SharePoint Central Administration.

I try to uninstall and re-install again, it remain not work.

How can I solve this problem?

Thank you very much.

View 4 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

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.

View 2 Replies View Related

Temp Tables

Jun 12, 2002

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

View 2 Replies View Related

##Temp Tables

Jun 16, 2004

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

View 4 Replies View Related

Temp Tables And UDF's

Dec 14, 2004

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

View 1 Replies View Related

Temp Tables

Apr 7, 2006

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

View 6 Replies View Related

Temp Tables

Sep 4, 2007

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

View 3 Replies View Related

Help With These Temp Tables

Dec 11, 2007

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

View 2 Replies View Related

#Temp Tables

Jul 20, 2005

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

Temp Tables

Nov 15, 2007



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

View 3 Replies View Related

SQL, Temp Tables And The Like Statement

Jul 23, 2005

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

No Temp Tables In SQLDatasource?????

Apr 12, 2006

   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
 

View 6 Replies View Related

Temp Tables And Performance

Mar 17, 2001

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.

View 1 Replies View Related

Seeing SQL 7.0 ##temp Tables In Access 97

May 2, 2001

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

View 4 Replies View Related

Testing For Temp Tables

May 11, 2004

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

View 3 Replies View Related

Global Temp Tables

Aug 11, 1998

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

View 1 Replies View Related

Temp Tables In Dts Package

Jul 18, 2002

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

View 1 Replies View Related

Help With A SQL Query Using Temp Tables

Aug 30, 2004

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved