Temp, Variable, Derived Tables

Feb 26, 2008

Two point to discuss

1) Temp table, variable table and derived tables,

they all seem same to me. Is there any big difference among them.

Also, if I do have the tables avaiable, so now I don't think I have to create any of the temerory tables,
since I can use the

WITh mystatement ( )
Select * from mystatmment

I think if I have to use any of the temperory tables , only time when I have to just create some tables to test some values on in it rather then using the real table in the database.

2) And not only that I use the CTE for that but for any sub query whereever apply, i would like to forget about the IN, Exit or = in the correlated queries but would use whereever I can use the "Joins"

I would like other point of view on that.
Thanks

View 1 Replies


ADVERTISEMENT

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

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

Passing Variable To Derived Column Transformation

May 11, 2007

I am trying to pass a variable set by a Row count transformation to a derived column transformation. There are actually three separate Row Count transformations storing to different variables. In the derived column trans. the expression i am using is @[User::ClientCount] and the variable type is Int32 and I am passing it to an I4 database column. After the derived column trans. all rows just come out with 0s in the row count columns. Does anyone know why this would be? Please let me know if you need more information.

View 3 Replies View Related

Variable Expressions In Derived Columns - Evaluate ColumnNames??

Jan 24, 2008

Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.

Example:
On an ETL of Products I want a new derived column that uses two other columns.

I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.

So I make that expression = @[User::Variable]
and stuff into @variable ( a string param) : ProductID + " - " + ProductName
My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names

I've tried with/without brackets, quotes and braces but no change.


Any way I can get the pieces in that variable expression to evaluate as column names?

View 6 Replies View Related

How To Assign User Variable Value To The Derived Column, In Data Flow Task

Dec 19, 2006

Hi:

In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.

MA2005

View 1 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

Temp. Variable (TSQL)

Sep 8, 2001

Friends,

I am trying to extract value from temp.variable and create new column in temp.table using that value for .eg

@name = 'abc'

What command should I use in my S.P to extract abc from @name, so I can create new column called abc in my temp. table.If there are any other ways of doing thinsg let me know.

Any help on this one would be appreciated.

Thanks in advance,

Sam

View 1 Replies View Related

Temp Variable As Column Name ??

Jul 21, 2004

Hi ,
Can anyone guide me to resolve my problem . I need to write a procedure which first looks for the Worker names from WORKER table who satisfies certain criterias , and then Find from another table how many jobs each one has done on each day of a month . I have written a function which will return all days of a particular month, which can be used for the above procedure .If the wrokers are John , Alex and Martin ,( which may vary according to the branch parameter) The report should look like

Day John Alex Martin
1/5/2004 4 8 NULL
2/5/2004 5 9 12
------------------------
etc

Thanks in advance
Regards
Praveen ( praveenvc@rediffmail.com)

View 2 Replies View Related

Derived Tables

Feb 2, 2004

OK....I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?


Thanks,
Trey

View 1 Replies View Related

Derived Tables

Dec 12, 2006

Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.

I presently have the following code

SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,

ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,

ProSolution.dbo.Offering.Code

FROM ProSolution.dbo.StudentDetail INNER JOIN

ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN

ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID

WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND

(ProSolution.dbo.Enrolment.CompletionStatusID = '1')




The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.

567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS

Can anyone give us any guidance please?

Thanks
Chip

View 16 Replies View Related

Advantage Of Derived Table Over Temp Table

Jan 22, 2008

Hi, I wanna know is there any advantage of perf gain when using Derived Tables over Temp Tables, advice me which one is better to use. Can I create Indexes and Insert/Update records into Derived Tables.


-Senthil

View 8 Replies View Related

Using A Temp Table And Variable With Gridview

Jun 16, 2008

Hi all, Im trying to use a temporary table along with a gridview.  But i keep getting an issue that says that I need to declare the variable name.  
 Right now, I have it set up that I have a permanent table in the database that is serving as my temporary table, however Im running into issues if multiple users access at once... So I would like to dynamically create a table and use that for my temporary table and then drop the table when the user is done with it...
 heres my code so far..Private Sub AddRecords(ByVal prodins As String, ByVal releaseins As String, ByVal opsysins As String, ByVal currelins As String, ByVal schemains As String, ByVal commentins As String, ByVal freeformins As String)
Dim tablevar As String = lblRequestor.TextDim tablename As String = "##" + tablevar Dim myConnection As New SqlClient.SqlConnection
Dim query As String = "CREATE TABLE " + tablename + "(UniqueID int Identity(1,1), Product varchar(50), OperatingSystem varchar(50), CurrentRelease varchar(50), Release varchar(50), SchemaSize varchar(50), Comments varchar(50), TempRelease varchar(50), FreeFormFlag varchar(50), sourcereleasedir varchar(125))"
Dim query2 As String = "INSERT INTO " + tablename + "(Product, Release, OperatingSystem, CurrentRelease, SchemaSize, Comments, TempRelease, FreeFormFlag) VALUES ('" & prodins & "','" & releaseins & "', '" & opsysins & "', '" & currelins & "', '" & schemains & "', '" & commentins & "', '" & currelins & "', '" & freeformins & "')"Dim myCommand As SqlClient.SqlCommandmyConnection = New SqlClient.SqlConnection("Data Source=mydatasource")
myConnection.Open()myCommand = New SqlClient.SqlCommand(query, myConnection)
myCommand.ExecuteNonQuery()myCommand = New SqlClient.SqlCommand(query2, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
 
And here is my query for the gridview....
 SelectCommand="SELECT * FROM @tablename where FreeFormFlag <> 'Y'"
and
<SelectParameters>
<asp:ControlParameter ControlID="tablename" Name="tablename" PropertyName="Text" DefaultValue="##default" />
</SelectParameters>
so @tablename is set to the value of a textbox i have hidden which the textbox gets the username of the user and adds ## to it..so if john doe comes on... the texbox reads ##jdoe... and thats what im trying to name the temp table.    SO the syntax is all there.... But i keep getting an error saying i need to declare @tablename... which is puzzling to me.. I tried doing
declare @tablename varchar (50)
select.....etc.  
 
but then it brings up an error saying... @tablename is already declared, each variable must be unique..., You must declare @tablename...   It tells me that its already declared then tells me i must declare it???  it makes no sense..
 
Anyone have any ideas?
Thanks,

View 4 Replies View Related

Using A Variable To Create Temp Table

Mar 3, 2003

Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.

Thanks,
Dianne

View 2 Replies View Related

Scope In Derived Tables

Jun 29, 2007

This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!

View 1 Replies View Related

Derived Tables Are UPDATABLE Or NOT ????

Sep 28, 2007

Hi,
In the SQL92 Specifications i read the foloowingf statement...
"All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are
permitted for updatable tables, subject to constraining Access
Rules. The operations of insert, update, and delete are not allowed
for read-only tables."
But i am concentrating on the below line from the above written lines,
"Derived tables are either updatable or read-only."
I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...





Code Block

Update (select * from test1) AS de SET id=0
????

View 10 Replies View Related

Integration Services :: How To Use Temp Table / Variable

Aug 8, 2014

It's SQL 2008 R2. I need to bring data from Oracle using .Net Providers/ODBC Data Provider to MS SQL table converting Oracle UTC dates to PST.  The source connection type cannot be changed as it's given. For the Destination I'm using the OLE DB.

As the truncate all and load could take time I'm trying to use a temp table or a variable to use it further with t-sql merge or not exists to bring/add the only new records to the destination table.

I'm trying different scenarios that is all failed.

Scenario A:

1. In DTF after OLE DB Source I'm using the Derived Colum to convert dates. It's working well.

2. Then use Recordset Destination with an object variable User::obj_TableACD. It's also working well.

3. Then I created a string variable with a simple query that I could modify later "select * from " + (DT_WSTR,10)@[User::obj_TableACD] trying to get data from the recordset object variable but it's not working.

Scenario B:

1. Created a store procedure to create a temp table.

2. Created a string variable to execute SP str_CreateTempTable: "EXEC dbo.TempTable". It's working well with the SQL Task with SQLSourceType as Variable.

3. Then how to populate the temp table from the Oracle source to bring data into the Destination?

View 6 Replies View Related

Derived Tables From Multiple Resultsets

Oct 19, 2005

Hi!I want to return a derived table along with 4 simple tables in a storedprocedure as follows:Input parameter: @FtNum (==Order Number, selects one Order and allassociated data)Table 1: OrdersTable 2: ItemsTable 3: InstancesTable 4: StockDetailsDerived Table: for each Item that requires stock items, 1st columnshould receive the ItemNo (from Items), subsequent columns should receive thedetails from StockDetails via the common key field 'StockCode'.I have so far used a 'Fetch' cursor to find all occurrences of a StockCodewithin the Items table, but have been unable to figure out how to first addthe ItemNo into the temporary table.Code is as follows:... build #tmp_StockDECLARE stock_cursor CURSOR FORSELECT StockCode, ItemNoFROM ItemsWHERE FtNum = @FtNumORDER BY ItemNoOPEN stock_cursorFETCH NEXT FROM stock_cursorINTO @StockCode, @ItemNoWHILE @@FETCH_STATUS = 0BEGININSERT INTO #tmp_Stock-- wish to insert ItemNo = @ItemNo here --SELECT *FROM ControlledStockWHERE StockCode = @StockCodeFETCH NEXT FROM stock_cursorINTO @Stockcode, @ItemNoENDOf course there may be a much simpler way to do this!Your help would be greatly appreciated either way.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1

View 2 Replies View Related

I Think I Found A BUG With Either Newid() Or Derived Tables

Apr 3, 2008

Hello.So the scenario is a little complicated.I am joining two tables.Table 1 is derived; it has one row; it has a column based from newid()Table 2 joins to table 1 and reuses the newid() value from table 1 in table 2's rowsBecause there is only one row in Table 1, the value of newid() REPEATS in Table 2The bug is that the NewId() value from Table1 is REGENERATED with every Table 2 record.I created a blog about this because it takes a code sample to demonstrate:http://jerrytech.blogspot.com/2008/04/sql-2005-tsql-bug-with-newid-in-derived.html

Have a nice day;
Jerry

View 2 Replies View Related

Create Multiple INNER JOIN On Derived Tables

Mar 10, 2014

create multiple INNER JOIN on derived tables as I have written below or use a #temp table for all derived tables and use them into JOIN. This below query is also very hard to understand what is going on .

CREATE TABLE #Temp
(
NumPlayers INT,
ModuleID INT,
ClientId INT,
ASF_Version VARCHAR(10),
ASF_VersionHead INT

[code]....

View 1 Replies View Related

Searching A List Of Tables, Derived From Another Table

Sep 21, 2005

Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan

View 6 Replies View Related

Paging Records On SQL Server Using Derived Tables

Jan 24, 2007

I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...

View 1 Replies View Related

Cant Find TEMP Environmental Variable While Installing Analysis Services

Dec 30, 2003

Hi guys

What's this nonsense all about?

Im attempting to install the Analysis Services on NT 4 and I get this strange error message (about not finding the TEMP environmental variable).

I checked the TEMP environmental variables they are just fine thank you very much.

Did anyone encounter this thing?

Thanks.

View 1 Replies View Related

SQL Server 2008 :: Temp Table - Must Declare The Scalar Variable

Mar 25, 2015

I can create a temp table like this, and immediately read the data:

declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

Paging Records On SQL Server Using Derived Tables : More Question

Jan 25, 2007

I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...

View 8 Replies View Related

Compare Data-type Xml In A Temp/variable/physical Table In MSSQL 2000

May 22, 2008

Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?

I tried this works in MSSQL 2005,



Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2


but not MSSQL 2000.

Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.


Is this true (from BOL)?




Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.

View 1 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







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