Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;public static DataSet SQLExecute(string strSQL, string strError)
{
DataSet dsetResponse = new DataSet();
try
{using (SqlConnection conn = new SqlConnection(DHOC.clsDHOC.GetConnString()))
{SqlCommand cmd = new SqlCommand();cmd.CommandType = CommandType.Text;
HiI a am executing a stored procedure from C code using SQLExecute . Ittakes some time to Execute the procedure. However when I see theprocessor usage during that time it shows 100% cpu usage.Can anyone tell me why SQLExecute uses 100% CPU when running thestored proceudre.Thanksvishal
Am abit new to SQL Server Programming and I have an issue which I need Assistance.
I have a table with the following Colunms Inv_Number ItemCode1 ItemCode2 ItemCode3 ItemCode4 ItemCode5 this table as data which i want to put to another table with the following Columns Invoice_Number ItemCode Amount
What i mean here is that Invoice Items were static and were about five and the new programs am designing will have more item codes.
Create a Function call InitCap accepts 1 parameter of type varchar returns a varchar It should only capitalize the first letter of the string passed to it. Items to watch out for. a-z not starting the string The 1st letter is already capitalized
This problems deals with ASCII characters, just wanted to get some insight here.
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
Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.
Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.
declare @Radius float(10), @BaseDiameter float(10), @BaseCircumference float(10), @BaseArea float(10), @Height float(10), @SurfaceArea float(10), @TotalArea float(10), @Volume float(10) set @Radius = 19.1 set @BaseDiameter = @Radius * 2 set @BaseCircumference = @BaseDiameter * PI() set @BaseArea = power(@Radius,2) * PI() set @Height = 16.27 set @SurfaceArea = 2 * (pi() * @Radius * @Height) set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI()) set @Volume = (power(@Radius,2) * pi()) * @Height select @Radius as [Base Radius], @BaseDiameter as [Base Diameter], @BaseArea as [Base Area], @Height as Height, @SurfaceArea as [Side Area], @TotalArea as [Total Area], @Volume as Volume go
So how do I make a function that does the same thing?
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:
Code Snippet CREATE FUNCTION f_GetGroupMembership(@providerKey nvarchar(200), @connection nvarchar(1000)) RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25)) AS EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser
This returns an error:
Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1
The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.
Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:
Code Snippet using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.DirectoryServices; using System.Collections; public class GroupMembership {
public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type) { //cast the enumerator passed in group grp = (group)obj; //create output parameters guid = new SqlGuid(grp.guid); name = new SqlString(grp.Name); type = new SqlString(grp.applicationData); } }
Can anyone tell me why this is happening? How can I overcome this problem?
Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes
Public Class SICTrans
Public Shared Function TransSIC(ByVal inpSIC As String) As String 'Dim conn As SqlConnection = New SqlConnection("context connection=true") Dim NewSIC, TempSIC, tempFSIC As String
If Len(NZ(inpSIC)) > 0 Then TempSIC = NZ(inpSIC)
If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))
tempFSIC = tempFSIC + "/" + Right(TempSIC, 1) End If End If
NewSIC = tempFSIC
TransSIC = NewSIC
End Function Public Shared Function NZ(ByVal input As String) As String If Not (input Is Nothing) Then Return input Exit Function End If Return String.Empty
End Function End Class
Which compiles fine...
i then use the following code to create the assembly in SQL which is fine:
USE NARD GO CREATE ASSEMBLY SICCodeTrans FROM 'c:SICCodeTrans.dll' WITH PERMISSION_SET = SAFE GO
but when i goto create the function with the following code it wont have it!
CREATE FUNCTION TransSICCode(@inpSIC varchar) RETURNS varchar AS EXTERNAL NAME SICCodeTrans.SICTrans.TransSIC GO
It gives me the following error message
Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1 Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.
I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.
When I hard code the script to use the name of my schema, wcadmin, it works OK.
CREATE FUNCTION wcadmin.dectohex(@a numeric) RETURNS varchar(8) BEGIN DECLARE @x varchar(8) DECLARE @y varchar(1) DECLARE @z numeric DECLARE @w numeric
SET @w=@a SET @x=''
WHILE @w > 0 BEGIN SET @z = @w % 16;
SET @y= CASE @z WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' ELSE CAST(@z AS varchar) END
SET @w = ROUND(@w/16,0,1) SET @x = @y + @x END
-- Pads the number with 0s on the left SET @x = RIGHT(REPLICATE('0',8) + @x ,8) RETURN @x END; GO
select 'WTDOCUMENT' HOLDER, dm.WTDocumentNumber ITEMNUMBER, dm.name ITEMNAME, ad.fileName ContentFilename, fh.hostName VaultHost, wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile, fm.path VaultPath from WTDocument di, WTDocumentMaster dm, HolderToContent hc, ApplicationData ad, FvItem fi, FvFolder ff, FvMount fm, FvHost fh where di.idA3masterReference = dm.idA2A2 and fm.idA3A5 = ff.idA2A2 and fm.idA3B5 = fh.idA2A2 and fi.idA3A4 = ff.idA2A2 and ad.idA3A5 = fi.idA2A2 and hc.idA3B5 = ad.idA2A2 and hc.idA3A5 = di.idA2A2
DROP FUNCTION wcadmin.dectohex; GO
But when I remove my schema name I get the error
'dectohex' is not a recognized built-in function name.
In this case I'm just using :-
CREATE FUNCTION dectohex(@a numeric) . . . . select 'WTDOCUMENT' HOLDER, dm.WTDocumentNumber ITEMNUMBER, dm.name ITEMNAME, ad.fileName ContentFilename, fh.hostName VaultHost, dectohex(fi.uniqueSequenceNumber) VaultFile, . . . . DROP FUNCTION dectohex;
Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.
I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.
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!!!
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
hi to all, i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void) i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error. there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error....................... Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
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
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.
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage() ------------------------------------ RETURN( SELECT ut.LastName, ut.FirstName, CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)], Max(hu.time_stamp) AS [Last Log Date], pct.Title, cat.topic_name FROM ZSRIVENDEL.dbo.UserTable ut, ZSRIVENDEL.dbo.history_usage hu, ZSRIVENDEL.dbo.pc_CourseTitles pct, ZSRIVENDEL.dbo.cam_topics cat WHERE ut.student_id = hu.student_id AND hu.course_id = pct.CourseID AND hu.topic_id = cat.topic_id AND ((ut.ClientID=@ClientID) AND (pct.ClientID=@ClientID) AND (ut.GroupID=3400) AND (hu.time_stamp>= @StartDate And hu.time_stamp< @EndDate) AND (hu.session_time<21600000)) GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name )
and will call this function UserSummary(): ----------------------------------------- RETURN ( SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] FROM ZSRIVENDEL.dbo.UserTable AS ut INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID WHERE ut.ClientID = @ClientID AND ut.GroupID = 3400 AND hu.Time_Stamp >= @StartDate AND hu.Time_Stamp < @EndDate AND hu.Session_Time < 21600000 GROUP BY ut.LastName, ut.FirstName )
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG
Hi,I have a User Defined Datatype, which I want to use to define anotherdata-type. I tried the obvious:EXEC sp_addtype@typename = UDT_OBJECT_ID,@phystype = 'NUMERIC (5)',@nulltype = 'NULL'GOEXEC sp_addtype@typename = UDT_TRACKING_NUM,@phystype = UDT_OBJECT_ID,@nulltype = 'NOT NULL'GObut that didn't work :Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line 186The data type UDT_OBJECT_ID does not exist.Has anyone done this before,Thanks,Rowland.
1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:
Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated. If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action Delete the rows that were queried and updated from the 'Scheduling' table
I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?
2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?
I am using VB .NET 2003 with SQL Server Express 2005. I am writing a program that creates a new database each time it is ran (through "Test_My_Connection" Sub below). On my development PC I am able to create a database on the fly using the code below without any issue:
Once I deploy to any computer I get the following error on the Create_DB_File below: "An attempt to attach an auto-named database for file c:Program FilesCommon FilesIdeasmyTestDB.mdf failed. As database with that name exists, or specified file cannot be opened, or it is located on UNC share."
I can create any database file I want with this code on my design PC, as long as it is in the "c:program filescommon filesideas" directory, if I move the directory I get the error on my design PC also. Do I need to register the directory I am using as a data directory to allow my PC or deployment computers to create databases? I don't know how I got it to work in the "common files" directory for my development PC but it does. Any thoughts? Code is below, with connection string...
Thanks,
Brian
'This will be run each time a new project is started Private Sub Test_My_Connection
Dim myProjName as String ="myTestDB" myOutDB_cls = New OutputDBCls(retMsg) If retMsg <> "SUCCESS" then
'Deal with it End If
'Database files to be created by "Create_DB_File" Sub
