I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @x int, @y int)
RETURNS INT AS
BEGIN
DECLARE @z INT
SET @z = @x + @y
RETURN @z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):
SELECT fieldlist INTO #temp1 FROM table
SELECT fieldlist INTO #temp2 FROM table
SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2
DROP TABLE #temp1; DROP TABLE #temp2
Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?
Is it possible to get a Computed column in one table to carry out a SELECT & SUM from a column in another table? I have tried a SELECT / FROM / WHERE construct but SQL complains about that. Regards Clive
I am working with data that has a lot of records that get updated and inserted frequently, and to avoid having to create formulas through code all over the place, I am experimenting with computed column formulas. I have a question though.. It works well for any addition or subtraction (columnA+columnB), however, when I try to use division (columnA/columnB), it only returns integers, no decimals. I would like to have decimals, particularly with a specific scale and precision and I would really like to attempt this without any coding. Any suggestions?
I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname)
Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000?
Dear all, Pls help me with this I have 2 tables Trip (TripID, Duration) Reserve(ReserveID, TripID, StartDay, EndDate) in which EndDate = StartDay + Trip.Duration How can I do this?
I have a table with fields called fname (First Name) and lname (LastName). I need the user´s email thai is compose from lname and fname:LOWER(LEFT (fname,1) + lname)Is there any difference between creatig this computed column ia a tableor in a view in SQL Server 2000?I can do:1. CREATE TABLE Users(fname varchar(20),lname varchar(20),email as LOWER(LEFT (fname,1) + lname) )Or2. CREATE TABLE Users (fname varchar(20),lname varchar(20))CREATE VIEW Vw_users (fname, Lname ,email)ASSELECT fname, Lname ,LOWER(LEFT (fname,1) + lname) )Is one of them is better?Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column
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"?
What is the difference between a computed column and a UDF? Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager? Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?
Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?
Hi, I have a table with 4 columns let us say A,B,C,D. column D is computed column with formula A + '-' + B Now, i want to add one more condition to the formula which looks like "A + '-' + B + '-' + C".
Please let me know how to do this using T-SQL as i cannot open the table in design mode in production server.
i have a table containing the column "current month" and "current day" as smallint which contains the number of months since 1900-01-01 and the day of this month. now i want to trnslate this column in a smalldatetime ( not datetime !) value using a computed column and then create an index on that column.
the formula should be: dateadd(d,[current day]-1, dateadd(m,[current month],convert(smalldatetime,'1900-01-01')) )
trying to create an index on this column results in an error message saying that the formula is nondeterministic or imprecise
removing the convert statement leaving only the date results in a column of type datetime and creating the index works fine
replacing convert(smalldatetime,'1900-01-01') with a column name which has the type smalldatetime also allows to create an index but thats not what i want to do.
it seems that sql2000 thinks a convert from a string to a date is nondeterministic. Is there any possibility to create a const of type smalldatetime without using convert?
Any idea?
(besides this, datediff(d,'yyyy-mm-dd',anydate) is nondeterministic but datediff(d,dateadd(d,0,'yyyy-mm-dd'),anydate) is deterministic. strange...)
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted from dbo.fielddata GO
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
In SQL Sever, do the size of computed columns gets added to the totalsize of the tables? Does SQL server stores the actual values incomputed columns?Thanks_GJK
I need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
Hello, does anyone know a website, where I can read something about the syntax of Computed Columns? I don't know how to enter the following expression in the computed columns field of MS SQL Server:
Instead of using Full-Text indices, which I don't like to manage, we've tried to use seperate tables that contain recordID, the word, a count of the word in the parent field and computed column which is the CHECKSUM() of the word column. I indexed the checksum column with a clustered index.
Works great in Query Analyser. But when the ASP page calls it, I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14) INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Same for updates and deletes. The question is how should these SET settings be done? Any ideas would be greatly welcomed.
Using SS2K, I'm getting the following error while bulk inserting:Column 'warranty_expiration_date' cannot be modified because it is acomputed column.Here is my bulk insert statement:BULK INSERT dbo.TestDataFROM 'TestData.dat'WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='|',MAXERRORS = 1,FORMATFILE='TestData.fmt')The computed column is not referenced in the format file and the data filedoes not contain the computed data.Thanks
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 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)
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
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
