Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View Complete Forum Thread with Replies
Related Forum Messages:
Create View Of Inline Function
Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC. I'm having trouble accessing functions through access. I've built the following function: CREATE FUNCTION fnSTR_LEASESTATUS(@TRS nvarchar(12)) RETURNS TABLE AS RETURN ( SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name] FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR) WHERE (((dbo.tblTRACT.STR)=@TRS)) ) GO I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me? Thanks for your time.
View Replies !
Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions
Hi! I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error: Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34 Incorrect syntax near the keyword 'option'. Here is the function: create FUNCTION [dbo].[ExpandedCTE] ( @p_id int ) RETURNS TABLE AS RETURN ( with tbl_cte (id, tbl_id, lvl) as ( select id, tbl_id, 0 lvl from tbl where id = @p_id union all select t.id, t.tbl_id, lvl + 1 from tbl_cte inner join tbl t on rnr.tbl_id = tbl_cte.id ) select id, tbl_id, lvl from tbl_cte option (maxrecursion 0) ) Please help! Alexander. P.S. I'm really sorry if it is about syntax, but I could not find it in the documentation.
View Replies !
Function To Create Comma Separated List From Any Given Column/table.
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View Replies !
Query To Find A Value In Column B Based On An Aggregate Function On Column A?
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View Replies !
Using A Function As A Column
I finally got my UDF working, but now I'm faced with another problem. I can't seem to use it for anything![:I First, here is the function itself: CREATE FUNCTION dbo.fnDueDate ( @DOP AS DATETIME, @INTERVAL AS TINYINT, @ThisDate DATETIME ) RETURNS DATETIME BEGIN DECLARE@Temp DATETIME IF @dop <= @ThisDate +(DATEADD(month,1,@dop)) SELECT@Temp = DATEADD(month, @Interval, @dop) ELSE SELECT@Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop) RETURN @Temp END It is called using something like this SELECT dbo.fnDueDate('20060315', 3, GETDATE()) This function executes(correct term?) with three parameters, DOP, Interval, and ThisDate, which correspond to a column from a table (mytable.dop), an interval in months, and ThisDate (GETDATE()) Now, I have determinied that the function works great, after several posts and a lot of help from our SQLteam gurus/yaks. However, I can't seem to use it in a stored procedure. Here is some sample data from myTable ResidentsID Last First DOP 1 Jones Bridget 09/08/2004 2 Longstocking Pippi 08/01/2006 etc. What I would like to do is generate this: Last First DOP Next3month Next6Month Jones Bridget 09/08/2004 12/08/2006 3/08/2006 Longstocking Pippi 08/01/2006 11/01/2006 5/01/2006 Using the data from each row for DOP Help. Please! Stephen
View Replies !
Inline Table-valued Function With Multi-value Parameter
Hello everybody, I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution? Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"? Thanks in advance for your answers.
View Replies !
SQL AVG Function Of A Decimal Column
Hi, Trying to get a dataset. My select statement is "SELECT Com_year,Avg(GPA) FROM Pilot_Stats WHERE com_source = 0 GROUP BY Pilot_Stats.com_year ORDER BY Pilot_Stats.com_year" Where Com_Year is an integer (ie 1984, 1986, etc) Where GPA is a decimal (ie 3.4, 3.0, etc) Where com_source is an integer This returns this error when I try to fill the dataset: Decimal byte array constructor requires an array of length four containing valid decimal bytes. For some reason it's hanging up on Averaging this Decimal column for some reason... Anyone have an idea why? Thanks for any help!
View Replies !
Column Update Function
This is probably a common problem with a standard design pattern, butI'm having trouble finding the solution.I have a table with a lot of columns, for this example I'll just usethree but in reality its more like 20.Create Table myTable (int col_one primary key, int col_two,varchar(20) col_three) etc...I want to write a sproc that allows updating of this column. Say Ihave a sproccreate sproc myUpdate int @col_one, int @col_two, varchar(20)col_threeasupdate myTable col_two = @col_two, col_three = @col_threewhere col_one = @col_onethen if I only want to update col_two I have to pass in the currentvalue of col_three so that it remains the same, which seems prettyinefficient. so I could change it to:asupdate myTable col_two = coalasce(@col_two, col_two), col_three = coalasce(@col_three, col_three)where col_one = @col_oneand then if I wanted to leave col_three the way it is then I couldjust doexec myUpdate 1, 2, NULLthe only problem here is that what if the value of col_three iscurrently 3, and I want to set it to NULL? Under the current method,setting someting to NULL is impossiblefinally, I'd like to use parameter naming in my exec calls. that wayI can just say someting likeexec myUpdate 1, col_three=3this would update col_three to 3 and leave the rest of the fieldsuntouched. you can see how handy this would be if you just want tochange a few of the fields in a table with a large number of columns.I'm sure this has been done before, can somebody point me in the rightdirection?Thanks,Ben
View Replies !
Error Creating CLR Function
Hi, When I try to create a CLR function in SQL 2005 (June CTP) I get the following error: Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'. The assembly registers successfully, with no errors. I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO The function's code is the following: using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient; namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) { //Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return ""; //Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . } return cFinal; } }} Thanks in advance, Anderson
View Replies !
New Column From User Defined Function
I have a user defined function (UDF) that takes 2 parameters. These parameters will be mapped from OLE DB source column values. I want the function result to be a new column that will be mapped to an OLE DB destination. Should this be done using a lookup task, OLE DB command task, or...? If this is possible I would appreciate a very specific example of how it's done. BTW I tried using a lookup task with a SQL command before the UDF idea occurred to me and ran into problems using parameters in the SQL query: Parameter Information cannot be derived from SQL statments. Set parameter information before preparing command. I've seen the posts about going to the advanced tab and messing with the parameters there but I was hoping using a UDF would be easier, if it can be done. Thanks.
View Replies !
AVG Function On An Integer Column- Truncation
When I use the AVG Function on an integer column, the result is truncated Example: Select AVG(field1) from table1 Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114. I can get the correct result by using the following SELECT: SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1 Am I missing something here? Is there an simpler way to do this? Any help will be appreciated. Steve D.
View Replies !
Changed A Computer Column Function
I had an issue that I ran across that I got solved but it was very monotonous. Here is my issue: I have an SQL function that takes in 2 parameters (lookup category and lookup code) and with those parameters is goes out and find the lookup code description. I have this function in about 40 computed columns. It isn€™t the best way to do things but we need it for some legacy stuff. The problem occurs when I need to make a change to that function. I can€™t because it is being reference by all of the tables. So what I have to do is go in to each of the tables and remove the function from the column formula. Then change the function and then finally add that function back to the column. Here is what I did. I went to the syscolumn table to find all of the computer columns. I went to each of those columns and removed the formula and added it to a text document. Then I changed the function. I then wrote a stored procedure that would add the column back. ( I couldn't find a way to use the ALTER COLUMN to add the function so I had to drop and then add the column ) Here are the possible scenarios that I want to see if they are possible. 1) Somehow €œturn off€? the dependency between the function and the column. That way I can just update the function. OR 2) Programmatically grab the data from the column formula and write that some place and clear out the formula. Then update the function. Then finally take all of the data that was cleared out and write it back to the column. Does anyone know how to make this easier?
View Replies !
Column Label Using Dateadd Function
I currently have this statement: CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven, I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate(). I've tried the month(dateadd("m',-11, getdate()) but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks.
View Replies !
Partition Function - Unknow Range Column
Hi,I need to create a partition table but the column on which I need tocreate a partition may not have any logical ranges. So while creatingor defining partition function I can not use any range.likeCREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FORVALUES (1,100,1000);Is there any way to define partition function in SQL Server somethinglike Oracle HASH partitions where logical range is unkown?ThanksSameer
View Replies !
How To Call A Function From A Column Formula In My MS SQL Table
Good day! What is the syntax on calling a function from a column formula in an MS SQL table. I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct? SELECT dbo.FunctionName([Parameter1, Parameter2]) But i can't save the table, "Error validating the formula". Pls. help Thanks a lot.
View Replies !
How To Recall Column Name Defined In CASE Function
Hello all, I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA' If anyone knows a solution, please kindly let me know. Thank you! Here is the sample code: ALTER PROCEDURE [dbo].[sp_premium_register] @PolicyType AS VARCHAR(10), @ReportFrom AS DATETIME, @ReportTo AS DATETIME, @Business AS VARCHAR(1), @Term AS VARCHAR(1) SELECT ColumnA, ColumnB, SLA = CASE WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L' WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S' END FROM DNIssue D LEFT OUTER JOIN KILRIShare S ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo) LEFT OUTER JOIN KILPolicy P ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo) LEFT OUTER JOIN v_report_KILDNFund F ON (D.DebitNote = F.DebitNote) LEFT OUTER JOIN PolicyProfile R ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType WHERE SLA = @Term Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate
View Replies !
Dynamic Order Status Column Function
I am looking for assistance coming up with a function (or maybe not a function if there is a better way) to make the Status column in my order table dynamic. The default value for new records will always be "1" to designate a quote. At this point the field is not dynamic. Once the customer confirms the order, the user needs to execute a command to change the status to "3" to designate a Confirmed order. At this point the field needs to be dynamic based on the shipping records. There are two order details tables. One for sales items and one for rental items. Each of these details tables has their own shipping record. the CheckInOut Tables are for rental while the Ship tables are for sales. So, if some (but not all) of the items in either of these order details tables has a shipping record associated with it, then the status should be changed to "5". If everything has been shipping, the status is changed to "4". If everything has been shipping but some items have been returned, the status is "6" if everything has been shipping and all of the RentalDetail items have been returned then the status is "7" and if there is any other combination of a variety of ships and returns, the status is "8". Also, at any time, the user needs to be able to execute a command to change the value to "2". once the value is changed to "2" the field stops being dynamic again. Below are my tables creation commands. CREATE TABLE OrderHeader ( OrderID int identity primary key, Status int, StartDate datetime, EndDate datetime )--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed" CREATE TABLE OrderRentalDetail ( OrderRentalDetailID int identity primary key, OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID), ItemName varchar(30), Qty int, SiteID int, ) CREATE TABLE CheckInOutHeader ( CheckInOutID int identity primary key, Type int, SiteID int, ActionDate datetime )--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost" CREATE TABLE CheckInOutDetail ( CheckInOutDetailID int identity primary key, CheckInOutID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID), OrderRentalDetailID int, Qty int ) CREATE TABLE OrderSalesDetail ( OrderSalesDetailID int identity primary key, OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID), ItemName varchar(30), Qty int, SiteID int, ) CREATE TABLE ShipHeader ( ShippingID int identity primary key, Type int, SiteID int, ActionDate datetime )--Use Type 1 = "Ship" Type 2 = "Return" CREATE TABLE ShipDetail ( ShipDetailID int identity primary key, ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID), OrderSalesDetailID int, Qty int )
View Replies !
How To Use Coalesce Function In Derived Column Component?
Hi all, You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example? Thanks! Any reply or comment is highly appriciated. Robert
View Replies !
Changing Column Values Usin SQL Function
Hi... I am having a very difficult time figuring this out, and hope somebody can help me out. I am importing data from sybase to a SQL 2005 server. This data is somewhat sensitive, and I need to perform a SQL function on each row to encrypt the value prior to importing it. I would like to change the value of some columns using a SQL function: "EncryptByPassPhrase" which is great in my opinion. The only problem is I can't figure out how to change a value of a column using a SQL function. I tryed a derived Column transformation but it only takes the functions provided. And doing an OLE DB Command seems like the right way of doing it, but I can't figure it out. All I need is something like this: newvalue = Select EncryptByPhrase('encphrase', OldValue) Thanks
View Replies !
Round Function In Derived Column Transformation
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point. Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help Value x Excel SSIS 627900 16200 16221 187000 4800 4831 277760 7200 7175 763000 19700 19711 1387500 35800 35844 1465200 37900 37851 2725000 70400 70396 292800 7600 7564 317200 8200 8194 The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View Replies !
Sending Column Name As A Parameter To Aggregate Function
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function . my procedure like this: ALTER PROCEDURE [dbo].[ORNEK10] @YIL VARCHAR(4), @TEKLIF_TURU VARCHAR(255) AS BEGIN DECLARE @N1 FLOAT DECLARE @N2 FLOAT SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1') SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2') SET NOCOUNT ON; --I want to avoid using if statements for Sum() function IF(@TEKLIF_TURU='BASKAN_TEKLIF') SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA, SUM(BASKAN_TEKLIF) AS YILI, ((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1, ((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2 FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP ORDER BY KOD1, KOD2, KOD3, KOD4 IF(@TEKLIF_TURU='ENCUMEN_TEKLIF') SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA, SUM(ENCUMEN_TEKLIF) AS YILI, ((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1, ((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2 FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP ORDER BY KOD1, KOD2, KOD3, KOD4 IF(@TEKLIF_TURU='MECLIS_TEKLIF') SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA, SUM(MECLIS_TEKLIF) AS YILI, ((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1, ((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2 FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP ORDER BY KOD1, KOD2, KOD3, KOD4 END
View Replies !
Find A Function That Returns The Column Width
Dear all hi, I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column. I know that If I have the table name I can find the column width through the system tables. I don€™t want this. Sincerely, Hellen
View Replies !
ContainsTable Function Searching Only One Column Per Record
Hello all, I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column. What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located. Can anybody help me achieve this? My code is: Code Block SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK FROM Sources AS FT_TBL INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC; What am I doing wrong? Thanks in advance, Chris
View Replies !
User Defined Function In Computed Column
I am trying to key a Personnel Table on a person's initials. Obviously there may be more than one person with the same initials so I am trying to use a User Defined Function to see how many of a certain set of initials are already there, and add 1 and add the number to the Key to make it unique - DW1, DW2, DW3 etc. But I cannot get the Computed Column Specification to accept a user Defined Function. any thoughts? help will be appreciated
View Replies !
Round Function In Derived Column Transformation
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point. Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help Value x Excel SSIS 627900 16200 16221 187000 4800 4831 277760 7200 7175 763000 19700 19711 1387500 35800 35844 1465200 37900 37851 2725000 70400 70396 292800 7600 7564 317200 8200 8194 The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View Replies !
Error While Creating Function On Remote Server
i have a function Create Function ReturnAmountB(@CMID int) Returns Decimal as BEGIN declare @Return decimal select @Return =sum(PD_PaymentAmount) from Payment_Details where PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID return @Return END when i run this on server(remote) this error shows... Incorrect syntex near 'Function' Must declare the variable '@CMID' A RETURN statement with a return value cannot be used in this context i am not able to understand why this error shows... Any solution!!! spandey
View Replies !
SUBSTRING In User Defined Function - Invalid Column
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
View Replies !
Sample Problem : Function Replace() In Derived Column
Hello All. Hopefully someone out there will have an idea as this isdriving me nuts. Ihave some sample problem. I want to use function replace() on Derived Column. For example. when strDate = 2007/03/22 I used ==> replace(strDate, "/", "") ==> 20060322 But If strTest = "123.10" ====>> 123.10 How can i do to replace ( " )double qoute ? by function replace() what is a statement for replace (") in Derived Column ? please tell me for this event. any suggesstion appreciated Thank you very much. Chonnathan
View Replies !
Derived Column Returning No Data On GetDate() Function
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View Replies !
Setting The Column Property Description With A SQL Function Call
I am trying to figure out how to set the Description of a Column in my database table by making a SQL function call. I know that I can go into Microsoft Studio Express and type in each desciption for each column. I just have about 1000 variables and each variable's description is in an Excel spreadsheet. I want to be able to build SQL code that will set each of the 1000 variables own description. Thanks for any help. Wesley Marshall
View Replies !
Joining On And Grouping By CASE Function Column Alias (URGENT)
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet: SELECT NewColumn= CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END, Table2.SelectCol2 FROM Table1 JOIN Table2 ON NewColumn = Table2.ColumnName GROUP BY NewColumn, Table2.SelectCol2 ORDER BY Table2.SelectCol2 I really appreciate any help anyone can provide. Thanks, DC Ross
View Replies !
Extending Derived Column Transform With Custom Function Library
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages. The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select. Is this possible ? What other approaches could I take to building about 30 re-usable functions?
View Replies !
How To Retrieve The Char(1) Column From SQL Server With Dbbind() Function In Windows C Programming?
I have used the following Windows C codes to retrieve records from the bus_newjob table in SQL server: ========================================================== // construct command buffer to be sent to the SQL server dbcmd( dbproc, ( char * )"select job_number, job_type," ); dbcmd( dbproc, ( char * )" disp_type, disp_status," ); dbcmd( dbproc, ( char * )" start_time, end_time," ); dbcmd( dbproc, ( char * )" pickup_point, destination," ); dbcmd( dbproc, ( char * )" veh_plate, remark," ); dbcmd( dbproc, ( char * )" customer, cust_contact_person," ); dbcmd( dbproc, ( char * )" cust_contact_number, cust_details" ); dbcmd( dbproc, ( char * )" from bus_newjob" ); dbcmd( dbproc, ( char * )" where disp_status = 0" ); dbcmd( dbproc, ( char * )" order by job_number asc" ); result_code = dbsqlexec( dbproc ); // send command buffer to SQL server // now check the results from the SQL server while( ( result_code = dbresults( dbproc ) ) != NO_MORE_RESULTS ) { if( result_code == SUCCEED ) { memset( ( char * )&disp_job, 0, sizeof( DISPATCH_NEWJOB ) ); dbbind( dbproc, 1, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_number ); dbbind( dbproc, 2, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_type ); dbbind( dbproc, 3, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_type ); dbbind( dbproc, 4, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_status ); dbbind( dbproc, 5, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.start_time ); dbbind( dbproc, 6, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.end_time ); dbbind( dbproc, 7, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.pickup_point ); dbbind( dbproc, 8, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.destination ); dbbind( dbproc, 9, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.veh_plate ); dbbind( dbproc, 10, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.remark ); dbbind( dbproc, 11, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.customer ); dbbind( dbproc, 12, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_person ); dbbind( dbproc, 13, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_number ); dbbind( dbproc, 14, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_details ); // now process the rows while( dbnextrow( dbproc ) != NO_MORE_ROWS ) { new_job = malloc( sizeof( DISPATCH_NEWJOB ) ); if( !new_job ) return( 0 ); memcpy( ( char * )new_job, ( char * )&disp_job, sizeof( DISPATCH_NEWJOB ) ); append_to_list( &Read_Job_List, new_job ); } } else { sprintf( str, "Results Failed, result_code = %d", result_code ); log_str( str ); break; } ========================================================== where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function. However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same. Who can tell me the tricks to retrieve a char(1) column from SQL server?
View Replies !
Function That Works In Sql Server Management Studio Does Not Work In Derived Column Transformation Editor
Hi I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime. I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor. Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher? Thanks for the look see... ****************************** ALTER FUNCTION [dbo].[convdate] ( @indate nvarchar(10) ) RETURNS nvarchar(10) AS BEGIN -- Declare the return variable here DECLARE @outdate nvarchar(10) set @outdate = substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+ substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+ substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2) RETURN @outdate END ******************************** And the error... expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified. Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error. Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid. Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)". (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
View Replies !
Calling VB Based SQLCLR Function Failed With Error Can't Load System.Web Assembly
I created a CLR function based on following VB code: Imports Microsoft.SqlServer.Server Public Partial Class SqlClrVB <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function GetTotalPhysicalMemory() As Integer GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory End Function End Class The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function: create assembly totalmem from '!WORKINGDIR! otalmem.dll' WITH PERMISSION_SET=UNSAFE go create function fnGetTotalMem() returns int as external name totalmem.SqlClrVB.GetTotalPhysicalMemory go When I call this function, it returned following error: select dbo.fnGetTotalMem() Msg 6522, Level 16, State 2, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem': System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. System.IO.FileNotFoundException: at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value() at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance() at SqlClrVB.GetTotalPhysicalMemory() . Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005. Thanks much, Zhiqiang
View Replies !
ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
View Replies !
SSIS Undouble - Inline Double Quote And Column Delimeter Problem
We have problems with SSIS flat file adapter. We try to import file which contains next sort of data: "1";"ABC";5 "2";"A""BC""";5 "3";"A""B;C""";5 The resulting table must be: f1 f2 f3 1 ABC 5 2 A"BC" 5 3 A"B;C" 5 How can we get this result? p.s. We tried to use "SQL Server SSIS Sample Component: UnDouble" but the result was unsuccesfull.
View Replies !
Create Multi Column View From Single Column Data
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field. data_table product_code month value 350 1 10 350 2 20 350 3 30 product_table product_code profit_center 350 4520 result_view product_code profit_center mon1 mon2 mon3 350 4520 10 20 30 My current query gives the following result result_view product_code profit_center mon1 mon2 mon3 350 4520 10 0 0 350 4520 0 20 0 350 4520 0 0 30 Any direction toward a solution would be appreciated. Am using SS2005.
View Replies !
Cannot Find Either Column &"dbo&" Or The User-defined Function
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
View Replies !
How To Create A New Column And Insert Values Into The New Column
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do. Column from Parts Table Column from MiniParts New Column in (Table 1 ) (Table 2 ) MiniParts (Table2) PartsNum MiniPartsCL NewMiniPartsCL 1 K DK 1 K K 1 Q Q 0 L L 0 L LC 0 D G 0 S S I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns. The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain. For Example Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above). Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
View Replies !
SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With &&"sys&&"?
Hi all, I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got: Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9 System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled. I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K? Thanks, Jos Here's a test script: /* ---------------------------------------------------------------------------------------------------- T-SQL code to test creation of three types of function where the function name begins with "sys_". Jos Potts, 02-Nov-2006 ---------------------------------------------------------------------------------------------------- */ PRINT @@VERSION go PRINT 'Scalar function with name "sys_" creates ok...' go CREATE FUNCTION sys_test () RETURNS INT AS BEGIN RETURN 1 END go DROP FUNCTION sys_test go PRINT '' go PRINT 'In-line table-valued function with name "sys_" creates ok...' go CREATE FUNCTION sys_test () RETURNS TABLE AS RETURN SELECT 1 c go DROP FUNCTION sys_test go PRINT '' go PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...' go CREATE FUNCTION sys_tmp () RETURNS @t TABLE (c INT) AS BEGIN INSERT INTO @t VALUES (1) RETURN END go DROP FUNCTION sys_test go PRINT '' go /* ---------------------------------------------------------------------------------------------------- */ And here€™s the output from running the test script in Query Analyser on our server: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Scalar function with name "sys_" creates ok... In-line table-valued function with name "sys_" creates ok... Multi-statement table-valued function with name "sys_" generates error 1706... Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11 System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled. Server: Msg 3701, Level 11, State 5, Line 2 Cannot drop the function 'sys_test', because it does not exist in the system catalog.
View Replies !
|
TRACKER
