I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1
EXEC sp2
EXEC sp3
Job2
EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5
Job3
EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?
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"?
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing: SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn); However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say: SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn); The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :). Appreciate if someone could help,
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
I have two tables that contain product SKUs (12-character strings): Table 1Product IdSKU... Table 2ProductVariantIdSKU... I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers. Thanks! Duncan
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:
SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost, dbo.table2.dialed_digits FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a join table2 as b on a.file_auth_nbr = b.file_auth_nbr join table3 as c on a.file_auth_nbr = c.file_auth_nbr join table4 as d on a.file_auth_nbr = d.file_auth_nbr join table5 as e on a.file_auth_nbr = e.file_auth_nbr ......etc., etc.
This is feeling very hard for me, but is surely very easy for many of you. I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Can any one please tell me where i am going wrong..
Code Snippet create proc SP_PercentageRMU as SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty' ,RAW_MATERIAL into #TZMelt_Pound FROM [LatrobeOCT].[dbo].[ZMelt_Pound]
group by RAW_MATERIAL GO select [Usage Qty],(case when r.raw_material = z.raw_material then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8)) else 0 end) as '%UsageQty' ,r.[PRODL]
,r.RAW_MATERIAL ,r.[GRADECODE] into #PZMelt_Pound FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r inner join #TZMelt_Pound z on r.raw_material = z.raw_material
now i want to relate and join the table so that i can create a view with the following coloumns such as
start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
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)
CREATE TABLE [Table 2] ( Id varchar, MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
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?
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?
-- 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.
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?
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.
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
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
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
