Calling Methods, Functions Threw Extended SP In A DLL
Jul 20, 2005
Hi,
I alredy tried to search this problem in last posts, but I couldn't
find the answer.
I try to access via Extended SP the method in a dll.
I registered the dll as a ExSP, with a name of method. But after
calling it in T-SQL, I became such a error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the
function SendGeneralNotify_FromA in the library [LibraryName.dll].
Reason: 127(error not found).
In this dll I have only one class an it has events, properities and
methods.
I will to call one of these methods.
aha ... very importand. Please don't say that this dll should be
written in c++ because it is made like that (no VB).
Maybe somebody of you have an example how I should call it, to became
an access on this dll?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
I have a dll which is developed in C#. I have made a reference to it from Reporting Services under Report Properties.
My problem is, that the methods in this dll are not static. Therefore under the Classes in Report Properties I have written the class-name and the instance-names of the instances that I need to use from this class (as I understand it, this is what you have to do if using non-static methods). In a text-box, I call one of the instances in the class, but I get this error:
[rsInvalidName] 'MyMethod()' is not a valid code class name. Names of objects must be CLS-compliant identifiers.
I am looking for a list of either generally used or a full list of overridable methods/functions for a script transformation?
I tried looking in the Visual Studio behind the script component but it only gives one method namely, Input0_ProcessInputRow, or if you make it asynchronous then it gives you another method CreateNewOutputRows.
I am basically looking for all the available overridable methods behind a script component in various situations.
Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e. DECLARE @sCellName VARCHAR(8) DECLARE @dDate DATETIME SET @sCellName = "CELL1M_1" SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL, SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate) FROM GSM.GSMCellDaily_vw WHERE CSSR BETWEEN 0 AND 85 AND FULLDATE = @dDate AND CM_SERV > 30 AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e. Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function. Does anyone have any idea on this, and what's the workaround for this?
Hi, all I'm using Sql server 2000 I want to make select statement dynamically and return table using function. in sp, I've done this but, in function I don't know how to do so. (I have to create as function since our existing API..)
Following is my tials... 1. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec spTest @from, @to GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: " ..... * EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec master..xp_msver GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.
The following line of code will not work in SQL Server. How should I achieve this?
I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?
Hey guys, I recently found out u can actaully JOIN tables that are on different dbs as long as they are on the same SQL Server by specifying [DATABASE].[DATABASEOWNER].[TABLE]. I now have a question tho. Can you access functions the same way? i have tried using the same syntax, but I get an error saying 'Invalid object name'
Hi, I am writing a stored proc that will so a very simple search for users in the database. In my database I am storing a birthday as a datetime column called 'bday'. Users can search for people between a certain age range; say 23 - 30.
Here is my search query:
alter procedure sp_wm_quickSearch @lookingFor int, @mySex int, @country varchar(500), @ageTo int, @ageFrom int, @state varchar(10), @userid int
as
select * from wm_user a inner join wm_user_details b on a.userid=b.userid where a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington'
I found a simple function that looks like it can do what i need it to do:
CREATE FUNCTION dbo.fnYearsApart ( @FromDate DATETIME, @ToDate DATETIME ) RETURNS INT AS BEGIN RETURN CASE WHEN @FromDate > @ToDate THEN NULL WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END / 12 END
MY QUESTIONS IS THIS:
How do I call this function in my query and pass it the age, and use the age as a search param. I imagine it woudl look something like this:
select * from wm_user a inner join wm_user_details b on a.userid=b.userid where a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington' and fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30
but this line doesn't work: (obviously i need to replace datetime.now
fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30
Hi , I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo . Can I do that ? Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
I have a number of databases that require a set of common functions. I'd like to place all those functions in a central DB. I'm having trouble calling them using the syntax FunctionDB.GetParamLength() for example, which works within the FunctionDB database.
Any ideas/suggestions? I really don't want to maintain seperate copies of the functions across 5+ databases.
We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.
The following was inserted for the SQLCommand property:
However, when the Refresh button is pressed we are presented with the error below:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".
If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@dt_required = ?, @dt_invoice = ?, @dt_despatch = ?, @ch_status = ?, @si_suffix = ?, @re_quantity = ?, @vc_invoice_id = ?, @vc_order_id = ?)) the following error is produced:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
I have a very simple SSIS package which I execute from VB.NET using the ManagedDTS namespace objects. I am getting a very sporadic error saying "The script threw an exception. Object reference not set to an instance of an object"
All the package does is insert rows from a filename that I pass into the package from code, and then rename the file in a script task. The script task is what is causing this error. If I remove/disable the script task the package executes everytime without error. I have even commented out all the code that might throw an exception and it still fails atleast 3 out of 10 times. Here is my code in the script task:
'CreateControlFile(strFileName, strDestination, e) End Sub
NOTE: I never get a package FAILURE as I would if it got into the CATCH block. I believe this is an error being generated from the Package processing engine.
Here is some output from the VB.NET application debug window:
This output's once for every "exception" thrown by each package execution.
A first chance exception of type 'System.NullReferenceException' occurred in VBAssembly
A first chance exception of type 'System.NullReferenceException' occurred in VBAssembly
A first chance exception of type 'System.IO.IOException' occurred in mscorlib.dll
Here is the code I use to execute the package:
Public Function ConsumePackage(ByVal strPath As String, ByVal strPackage As String) As Boolean
Dim bReturn As Boolean = False
Dim pkg As New Package
Dim app As New Application
Dim result As DTSExecResult = DTSExecResult.Failure
Try
pkg = app.LoadPackage(strPackage, Nothing)
Debug.Print("Executing package on file: " + strPath)
pkg.Variables("FileName").Value = strPath
result = pkg.Execute()
If result = DTSExecResult.Failure Or result = DTSExecResult.Canceled Then
My SqlConnection is causing an error "sqlConn.ServerVersion threw an exception of type System.InvalidOperationException". Everything I have read seems to suggest that there is a problem with my SQLConnection parameters but as you can see from the commeted code below I have tried three different servers with four different configurations. I am out of ideas on what is causing the error. Thanks - Amy Here is a section of my code: String sqlStmt = "Select [UserName], [Password], [Last], [First] from Contacts Where Login=@Uid AND Password=@Pwd"; SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI;");//SqlConnection sqlConn = new SqlConnection("Data Source=pe2800;Initial Catalog=test;Integrated Security=SSPI;");//SqlConnection sqlConn = new SqlConnection("Data Source=HorizonDC02;Initial Catalog=test;Integrated Security=SSPI;");//SqlConnection sqlConn = new SqlConnection("Data Source=HorizonDC02;Initial Catalog=test;User ID=username;Password=password;"); SqlCommand sqlCmd = new SqlCommand(sqlStmt, sqlConn);sqlCmd.Parameters.Add("@Uid", SqlDbType.VarChar, 30).Value = uid;sqlCmd.Parameters.Add("@Pwd", SqlDbType.VarChar, 30).Value = pwd;sqlCmd.Connection.Open();
I need to refresh my cube daily and once this processed is done, i will update in ETL table with lastcubeprocessedtime in my timestamp column. Actually i am using IBM db2 provider since my ETL table resides in IBM DB2 source.
In my first task i am using script task where i will check whether my timestamp column value is NULL or not.
I am getting error like "The script threw an exception : keyword not supported: 'network port'." when this script task executes.
Moreover i am getting another error like "The execution succeeded, but the number of error raised(1) reached the maximum allowed(1); resulting in failure. This occurs when the number of errors reaches the number of specified in MaximumErrorCount. Change the MaximumErrorCount or fix this errors." For this error i have changed MaximumErrorCount value from 1 to 100 in dataflow task.
Can anyone tell me what might be the problem for these two errors?
I have VS2003 and VS2005 installed as well as SQL Server 2000 and SQL Server 2005 Tools. I have RS2000 and RS2005 installed. RS2000 works fine in VS2003. When using VS2005 and opening a Microsoft sample project for 2005, I get the error listed in the subject line.
When trying to create a new project and connecting to an Oracle database or a SQL Server 2005 database, I get the following error: "A connection cannot be made to the database. Set and check the connection string." The connection works fine when the test button is clicked, but fails when continuing in the wizard.
Any suggestions. We are trying to migrate from RS2000 to RS2005 and nothing in 2005 works.
I am using sql reporting service 2005 with .NET 2.0. I have created a custom dll file for report and put this dll in appropriate folder. Report is running fine in designer project. but when I am trying to view this report after uploading to report manager it give me an error like
Failed to load expression host assembly. Details: The type initializer for 'CableReporting.Utilities' threw an exception. (rsErrorLoadingExprHostAssembly)Is there any solution for that?
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
I'm a newbee to replication and wanted to know about the whole mechanism of replication and how is two way replication implemnted on 2 diffrent servers located at 2 different remote locations????
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.What would be the more efficienct method to use?'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReaderDim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnectionmyItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"myItemCommand.Connection.Open()myItemReader = myItemCommand.ExecuteReader()Dim myfileout As StreamWritermyfileout = File.CreateText(Server.MapPath("out.txt"))myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")While myItemReader.ReadIf myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then'what method to to look up for example the category name using the category_quid referenced in the item master'Write out record for parts file
I'm fiddling around with my sql server in my home computer. I would like to store a different image in each row of the product table (for example). All the pics are now in my hard disk. I gather from various discussion boards that there are several ways of saving images -
1. INTERNALLY
a)Use VB. Run in ISQL/w b) Use insert statement. INSERT INTO EMP(empno,empname ,Picture_Id) VALUES('E1235','Anthony','c:PhotosAnthony.jpg') c) Use isql/w & TSQL statement d) Use sqlgetdata and sqlputdatn e) Use textcopy in binn directory. f) Use VB & ADO
2. EXTERNALLY
a)store the image externally and simply store the path to it in a plain varchar variable.
My questions are :- (i) Can I save images simply using 1b)? (ii)Is 2a) correct ? Do I just type the name of the image file in my harddisk as the value in the table ? (iii) If I have the database linked to the internet and have it hosted by a commercial server in the future, will I have a nightmare reentering all the path names when I upload the database into the commercial server? (iv) What are the pros and cons of the various methods listed in 1.
