I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:
My dll name is 'Crystal_Reports_Test_01' In the DLL, my class is named 'Class01'. In the DLL, my procedure is named 'TestMe'
I've managed to integrate the DLL into SQL using the following statement:
CREATE ASSEMBLY TEST_ERIC_01 AUTHORIZATION dbo FROM 'c:mssqlassembliescrystalreports.dll' WITH PERMISSION_SET = UNSAFE
I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:
create procedure dbo.Crystal_Reports_Test_01( @Parm1 nvarchar(255) ) as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe
But I get the following error:
Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.
I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.
Any help would be appreciated ! I'll post the Delphi code (DLL) below.
Thanks,
Eric Gooden
library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0
-- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
SELECT emp_id AS emp_num, fname AS first, minit AS middle, lname AS last, IDENTITY(int, @number, 1) AS job_num, job_lvl AS job_level, pub_id, hire_date INTO employees FROM employee
Is there any way i can use variable inside the identity function like in the above example?.
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar) RETURNS numeric AS BEGIN declare @aantal numeric
if @proces = 'Inhuizen' begin if @categorie = 'open_op_tijd' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
if @categorie = 'open_te_laat' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date FROM d_Time_School_Year WHERE (Year_Key = ?)
This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date FROM fn_TimeDimension (?)
But SSIS doesn't like this. When I click on parameters command button I get and error like this
"Parameters cannot be extracted from the SQL command. The provider might not help.........
Syntax error, Permission Violation, or the non-specific error(Microsoft SQL native Client)"
Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine
now i want to get the min of each column name like so
select @columnname,min(mtr_5120),0,0,0 from temp_prt ------> this works for min(mtr_5120) mtr_5120,34.5,0,0,0
now I want to generalize so I try to pass in the variable name and I do the following
select @columnname,min(@columnname),0,0,0 from temp_prt (the columname (@columnname) exists in the table temp_prt)
but now i get an error Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:
select @columnname,'min(' + @columnname + ')',0,0,0 from temp_prt
but i get the same error Msg 8114, Level 16, State 5, Line 29 Error converting data type varchar to decimal.
I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable. I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable. Anyone knows why this isn't working? This is how I have the code:
SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)
the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:
SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.
Hello to all, I hope that somebody can help me. I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table. Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088. declare @IDM int; declare @IDO char(100); set @IDM = 3418; set @IDO = '4088'; select B.IDMember from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0 and charindex(@IDO,B.RelationshipIDs) > 0 Using this query i get nothing. I try to use constant in charindex and i get result. declare @IDM int; declare @IDO char(100); set @IDM = 3418; set @IDO = '4088'; select B.IDMember from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0 and charindex('4088',B.RelationshipIDs) > 0 So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ? Thanks Best Regards Pinsha
Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far <script runat="server""> Sub Page_Load dim --all the variables for my sql connections-- dim ff1 As Date then my sql connection and queries sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table) dim ff1 As string ff1="30/01/2006" but I need ff1 to be the current date and is I use ff1 As date it returns time and date Is there any way to set ff1 to the current date in this format "dd/mm/yyyy"
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int) RETURNS VARCHAR(100) AS BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week GROUP BY quantity ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table: ItemNo int identity(1,0), ItemType tinyint
There is a WETPAINT table: ItemNo int, Color varchar(20)
There is a DRYPAINT table: ItemNo int, Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable] ( @ItemType int = 1 ) RETURNS @thistable TABLE ( Color varchar(20)
) AS BEGIN if @ItemType = 1 insert into @thistable (color) select color from WETPAINT if @ItemType = 2 insert into @thistable (color) select color from DRYPAINT RETURN END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color FROM MAIN INNER JOIN gettable(Main.ItemNo) as a ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.
The syntax is as follows:
select car.id, car.licenceNumber, car.brand, car.model, (select driverName from getCurrentDriver(car.id)) as driverName from car
When I try to compile I get following error on the line of the function: Incorrect syntax near '.'
The database version is SQL Server 2000 SP3.
What am I doing wrong? Is there a workaround for this error?
Hi, I'm having trouble with this multi-statement table-valued function:
ALTER FUNCTION MakeArDetail ( -- Add the parameters for the function here @dateStart DATETIME, @dateEnd DATETIME ) RETURNS @arDetail TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) AS BEGIN DECLARE @arTemp TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 )
INSERT INTO @arTemp SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims; UPDATE @arTemp SET Claim = (SELECT SUM(Pims.AmtReq) FROM Pims WHERE Pims.Insurer = @arTemp.Insurer AND Pims.NABP = @arTemp.NABP AND Pims.NumRx = @arTemp.NumRx );
INSERT INTO @arDetail SELECT * FROM @arTemp RETURN END GO
I get Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43 Must declare the scalar variable "@arTemp".
I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared. If I don't include the UPDATE command the thing works.
i have a column with mulitple ids stored with commas . i want to pass ids and get data along with name from the table..how to get multiselect value in a variable in sql server functionÂ
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100) RETURNS int AS BEGIN DECLARE @emp_count AS int declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3 Incorrect syntax near 'RETURNS'. Msg 102, Level 15, State 1, Procedure count_rows, Line 10 Incorrect syntax near '@declaration'. Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal;
I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. Â For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence ( @param1 int, @param2 int ) RETURNS @Result TABLE (resultcol1 int, resultcol2 int) AS BEGIN
I have the following stored procedure... CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet. Obviously, I need to wrap this in an IF...EXISTS IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID)) (the SELECT above, since that's what needs to come back if the data exists) ELSE Do the same select but put in a static value for BudgetCode, like '0000' GO How could I do the part where the IF...EXISTS fails? I'm... not sure I can use RETURNS, since it feeds into this recordset: rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID") and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>
I'm attemping to deploy a crypto class that we use on a desktop app to SQL Server. I'll use the encryptstring/decryptstring found in this class as User Defined Functions on SQL Server. When I try to deploy the class I'm getting this error:
Error 1 Method, property or field 'EncryptString' of class 'EncryptingDecryptingOASISPWDS.Crypto' in assembly 'EncryptingDecryptingOASISPWDS' is not static. EncryptingDecryptingOASISPWDS
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.
Code Snippet
declare @IdTable table ( EmployeeId nvarchar( 16 ) not null ) insert into @IdTable select EmployeeNumber from Employees
/* I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set. */ select * from fn_GetEmployeeById( EmployeeId )
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?
ALTER PROCEDURE [dbo].[rpt]
(
@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'
) AS
--split CourtIDs into a table DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY) INSERT INTO @tbCourtIDs select * from dbo.Split(@CourtIDs, ',')
Should a static connection be used in application. Is it advisable to use a static connection, i mean everytime should i create a new instance of sqlonnection object and then dispose it after use or should i use a static connection plz advise