Fail To Create CLR Function In SQL 2005
Can anyone help me to create a URL decode user defined function in SQL Server 2005?
I want to use the method [System.Web.HttpUtility.UrlDecode] in .net framework, and I try to add it as a CLR function to SQL Server but always fail. It depends on [System.Web.dll], and when I try to create assembly [System.Web] using following scripts, it will fail:
CREATE ASSEMBLY [System.Web] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE
Error:
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true:
the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
I have searched the MSDN and then add following scripts before mine:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
CREATE ASYMMETRIC KEY SystemWebKey FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY SystemWebKey
GRANT UNSAFE ASSEMBLY TO CLRLogin
But unfortunately it fails again with same error.
View Complete Forum Thread with Replies
Related Forum Messages:
Create A TO_DATE Function For Use In SQLServer 2005
Hi , I 'm working with visual studio 2005 and I have created an SQLServer Project. I'm using the CLR functionality which comes with SQLserver 2005. This means that I can write VB.nEt code and use it inside Sqlserver 2005.So far so good. I am now inside the .NET I have created a Function(must remind you that I have created an SQLserver Project) which takes two string arguments. The date value in a string format and the string format. In Our case the function returns a string.It will return a datetime although. So we have Dim Datetime_Val As DateTime = Nothing Dim Date_Val As Date = Nothing Dim StrTemp As String = "" Dim StrDateTemp As String = Nothing Dim StrTimeTemp As String = Nothing Dim ls_return As String = Nothing Dim lindexof As Integer Dim Counter As Integer = 0 lindexof = 0 Select Case StrFormat Case "DD-MM-YYYY HH24:MIS" For Counter = 1 To 2 lindexof = StrDate.IndexOf("-", lindexof + 1) Next lindexof += 5 StrDateTemp = StrDate.Substring(0, lindexof).Trim StrTimeTemp = StrDate.Substring(StrDateTemp.Length, StrDate.Length - StrDateTemp.Length).Trim ls_return = StrDateTemp & " " & StrTimeTemp End Select The above is a simple code. As you can see I'm trying to convert the TO_DATE function ,which work with ORACLE, to make it work with SQLServer 2005. I've been trying unsuccessfully to combine the variables StrDateTemp and StrTimeTemp into a datetime value. I used the following code but nothing Datetime_Val = CDate(StrDateTemp & " " & StrTimeTemp) Didn't work Datetime_Val = Convert.ToDateTime(StrDateTemp & " " & StrTimeTemp) Didn't work Datetime_Val = DateTime.Parse(StrDateTemp & " " & StrTimeTemp) Didn't work Inside SQlServer I used this SQL statement Select dbo.TO_DATE('31-12-1990 00:26:46','DD-MM-YYYY HH24:MIS') But I am receiveing an error. I want to avoid changing all of my applications with a specific format.This sql statement without the dbo prefix I'm using in Oracle. I want to keep the format of the SQL and let VB.NET do the parsing for me. It is easier for me to put in my SQLs the dbo infront rather changing the complete SQL. I have two questions . How am I going to create a TO_DATE function which Oracle uses and write something similar in SQLserver ? And If I cannot do that how am I going to get the database 's datetime format and create with VB.NET the Datetime value from the two variables ? My problem I believe is quite complex. I would be mostly appreciated if you could help me on this. Thank you
View Replies !
How To Create Assembly Function Using Dll Files In SQL Server 2005???
Hiiiiiiii all I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration I have tried like this CREATE ASSEMBLY abc FROM 'C:abc.dll' WITH PERMISSION_SET = SAFE but it gives me incorrect syntax error so plzzzzz anyone help me wht to do in my probbbbbbbbb??????? Pratik Kansara
View Replies !
Fail In Sql 2005 Setup
Dears, i have a problem when i setup workstation componant in sql 2005 it fail when the setup remove the backup file in the end of setup and give me this if anyone no what id the problem solve it to me thanks
View Replies !
SQL 2005 Express Continues To Fail
I have been attempting to install Backup Exec 11D on a Win2K3 std server running Exchange 2003 Ent. Used to have 10D installed and did an uninstall from add remove programs then tried to install 11D When the setup runs it attempts to install the SQL 2005 Express and then fails with the dialog that pops up SQL Server Setup unexpectedly Failed. For more information, review the setup summary log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt I have review the summary .TXT which contains Microsoft SQL Server 2005 9.00.2047.00 ============================== OS Version : Microsoft Windows Server 2003 family, Standard Edition Service Pack 2 (Build 3790) Time : Sun Feb 10 18:05:02 2008 That is all it has in it. Can anyone out here help me with this issue. Thank You in advance Jeff jprewitt@ucsd.edu
View Replies !
Many Attempts, All Fail To Instasll SQL 2005 SP2
I have made numberous attempts to install SQL 2005 SP2 on my Developer version. When using windows update, it runs for about 30 minutes then fails. When I click the Failed Icon in WU panel, it returns a blank window. I have googled this but found no pertinent solution. Thanks!
View Replies !
Fail To Uninstall SQLServer 2005
I can't uninstall a default instance of SQLServer 2005. i have instaled a MSDE2000 then i uninstall it and when a try to uninstall the default instance of SQLServer2005 a have a error the log following: ----------------------------------------------------------------------------------- Instance Name = MSSQLSERVER Trying to find install through Instance Name Install Type = 1 If possible, determine action Failed to determine installSkuId due to property load failure.SKU is set to SKU_EXPRESS. Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Wed Oct 26 16:37:20 2005 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "InstallSku" {"SetupStateScope", "", ""} in cache Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Oct 26 16:37:21 2005 Function Name: SetupStateScope.InstallSku Source Line Number: 44 ---------------------------------------------------------- No collector registered for scope: "SetupStateScope" Machine = EMILIO, Article = WMIServiceWin32OSWorking, Result = 0 (0x0) Machine = EMILIO, Article = WMIServiceWin32CompSystemWorking, Result = 0 (0x0) Machine = EMILIO, Article = WMIServiceWin32ProcessorWorking, Result = 0 (0x0) Machine = EMILIO, Article = WMIServiceReadRegWorking, Result = 0 (0x0) Machine = EMILIO, Article = WMIServiceWin32DirectoryWorking, Result = 0 (0x0) Machine = EMILIO, Article = WMIServiceCIMDataWorking, Result = 0 (0x0) Machine = EMILIO, Article = XMLDomDocument, Result = 0 (0x0) Failed to determine installSkuId due to property load failure.SKU is set to SKU_EXPRESS. Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Wed Oct 26 16:37:20 2005 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "InstallSku" {"SetupStateScope", "", ""} in cache Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Oct 26 16:37:21 2005 Function Name: SetupStateScope.InstallSku Source Line Number: 44 ---------------------------------------------------------- No collector registered for scope: "SetupStateScope" Machine = EMILIO, Article = Processor, Result = 0 (0x0) Machine = EMILIO, Article = PhysicalMemory, Result = 0 (0x0) Machine = EMILIO, Article = DiskFreeSpace, Result = 0 (0x0) Machine = EMILIO, Article = OSVersion, Result = 0 (0x0) Machine = EMILIO, Article = OSServicePack, Result = 0 (0x0) Machine = EMILIO, Article = OSType, Result = 0 (0x0) Machine = EMILIO, Article = AdminShare, Result = 0 (0x0) Machine = EMILIO, Article = PendingReboot, Result = 0 (0x0) Machine = EMILIO, Article = IEVersion, Result = 0 (0x0) Machine = EMILIO, Article = DriveWriteAccess, Result = 0 (0x0) Machine = EMILIO, Article = COMPlus, Result = 0 (0x0) Machine = EMILIO, Article = ASPNETVersionRegistration, Result = 0 (0x0) Machine = EMILIO, Article = MDAC25Version, Result = 0 (0x0) Machine = EMILIO, Article = SKUUpgrade, Result = 0 (0x0) HOW I CAN RESOLVED THE PROBLEM THKS, Emilio Ferreira
View Replies !
SQL SERVER 2005 FAIL OVER CLUSTER
Greetings, First I am fairly new to SQL Server 2005 Clustering so this is why I was to see if any of you might be able to help me. Are current setup is as follows: CRM1 SERVER 2K3 R2 xxx.xxx.xxx.74 CRM2 SERVER 2K3 R2 xxx.xxx.xxx.75 HP MSA1000 xxx.xxx.xxx.75 MSDTC IP xxx.xxx.xxx.77 SQL INSTANCE xxx.xxx.xxx.78 Now are CRM guys are trying to connect to xxx.xxx.xxx.78 for there SQL instance or RDP into this ip address and they can’t…Now the question is should they be able yto logon to the SQL Instance via RDP? Thanks, CujoX
View Replies !
Restore Fail In Sql Server 2005
Posted - 09/07/2005 : 15:32:52 -------------------------------------------------------------------------------- Hi, i need help about restore a DB I did a backup of a database using SQL Server Management Studio, but when i try to restore my database now, i get this error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'Athenas'. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo) What do i doing so bad?? Thanks for ur opinions and help. Regards ------------------------------------------ seyha moth
View Replies !
SQL Server 2005 Fail Installation On Cluster
Hi, A few months ago, I was trying to install SQL Server 2005 SP2 on a cluster (Active-Active), the Cluster has 3 instances, 2 lives in one node and the other one in the other node. When I try to make the deployment an error message appear, I do not have the error right now but it€™s something like €śCould not connect to the passive node, installation failed€?. The worst part of the error was all instances shut down and I couldn€™t bring on line and I have to reinstall all instances. I need to install SP2 but I€™m a little afraid fail again. Do you know the best way to deploy the SP2 on a cluster? Maybe move all instances in one node before install SP2? Can you help me with your comments. Do you think I have problems with the windows cluster? Thanks and Regards,
View Replies !
Mails I Try To Send Whereby Sql Server 2005 And Fail To Do So
Hi everyone i get an error messgae running the following code DECLARE @mailist VARCHAR(max) SET @mailist='' SELECT TOP 1 @mailist=@mailist + email +';' FROM email.dbo.mytable SET @mailist=STUFF(@mailist,LEN(@mailist),1,'') SET @mailist='''' + @mailist + '''' EXEC msdb.dbo.sp_send_dbmail @profile_name='my_mail_profile', @recipients=@mailist, @subject='mysubject', @body_format='html', @body= ' <html> <head> <title> problems with send_dbmail </title> </head> <body> this is not working </body> </html> ' The error says: quote: Syntax error in parameters or arguments. The server response was: 5.5.4 Invalid Address) At the other hand, if i assign a straightforward @recipints address such as EXEC msdb.dbo.sp_send_dbmail @profile_name='my_mail_profile', @recipients=my@mail.com It works Anybody know why ? Thanks
View Replies !
Database Connection With Sql Server 2005 Fail With DSN
hello everybody I have a problem in connecting to my SQL Server 2005 With the help of DSN i.e. Data Source i have tried with both SQL Client and ODBC Connection RasConnection.Conn= new OdbcConnection("DSN=CallingServer"); but it is throwing the error the user is not associated with trusted SQL Server connection Please tell me how to solve the problem I n the serve side i had made the setting of the select "SQL Server and Windows authentication mode" Please give me some solution for both VB and C# .net Thanks in advanc3e
View Replies !
SQL Server 2005 Express Connection Fail. Please Help Me
Hi all, When i attempted to connected to Microsoft SQL Server 2005 Express via ASP.NET 2.0 application, it seems to throw the following error. I had set up the Protocols for Express (TCP/IP and Named Pipes are both enabled) to allow remote connections using both TCP/IP and named pipes. Login failed for user ''. The user is not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Source Error: The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL: 1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %> or: 2) Add the following section to the configuration file of your application: <configuration> <system.web> <compilation debug="true"/> </system.web> </configuration> Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode. Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace: [SqlException (0x80131904): Login failed for user ''. The user is not associated with a trusted SQL Server connection.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734883 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.sqlquerytool_aspx.Button_Click(Object s, EventArgs e) +49 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 I have been battling with error for 5 solid hours. Please help me thank you in advance
View Replies !
How To Uninstall SQL Server 2005 When Manual Attempts Fail.
I cannot uninstall SQL Server 2005 (x64) developer edition from my Vista (x64) Enterprise machine. Since I was unsuccessful with convention (several times) I have resorted to manual uninstall using instructions at http://support.microsoft.com/kb/909967 Because I encountered further diffuculties after meticulously following the instructions I have had to use the "If you experience problems" section. I have been able to uninstalled all portion except the four shown below (selected data shown in the registry); 1.) Display Name: Microsoft SQL Server 2005 Reporting Services (64-bit) GUID: {BEE3EC3D-0C91-4A3E-A42C-7634D32968F4} Uninstall string: MsiExec.exe /I{BEE3EC3D-0C91-4A3E-A42C-7634D32968F4} 2.) Display Name: Microsoft SQL Server 2005 (64-bit) GUID: {F14F2E25-99AF-42A9-977C-F6D0352DC59F} Uninstall string MsiExec.exe /I{F14F2E25-99AF-42A9-977C-F6D0352DC59F} 3.) Display Name: GDR 3054 for SQL Server Reporting Services 2005 (64-bit) ENU (KB934458) GUID: none Uninstall string: none 4.) Display Name: Microsoft SQL Server 2005 (64-bit) GUID: none Uninstall string: none Attempts to uninstall 1 and 2 above result in errors like: O:Watson 1:1317 2treamSupportFiles 3qlCreateDirectory 4:5 5:t:youkonsqlsetupdarwinsqlcastubcatarget.cpp 6:36 7qlcashtub.dll 8qlrun.msi And: An error occurred whil attempting to creat the directory: C:Program FilesMicrosoft SQL Server90Setup Bootstrapin ===== I am not sure what action to take next for the first two and also for the last two. I am inclinded to; #1 Remove all SQL Server files on the machine (note security groups are removed a services stopped) #2 Remove the four entries in the registry #3 Run a tool such as CleanMyPC registry cleaner Is there any reson not to take that action? And what is preferrable way to remove these when all other attempts fail? Thank you. dbuchanan
View Replies !
Scale Out Architecture With High Availability (fail Over) - SQL 2005
I am in the process of designing a database infrasture layout that can virtually scale to an very large number of servers in efforts to improve performance. The Scale-out architecture vs. grid computing (something like Oracle RAC) seems to be the way to go. It may take a lot more work up front, but it seems very flexible in the long run. One of the issues that I am trying to tackle is how should I grow this thing. Right now, I have one single 4 way server running SQL 2005 Ent. edt. We are planning on getting a second server as well as a Enterprise level San solution. With my 2 goals in mind (Scale out architecture and High Avail) should I bring this second server online as a passive cluster node, or should I partition out the data across both nodes. Will clustering even be part of my fault tolerence plan or should I use replication? Its hard to find a good answer as what is the *best* way to make this happen. Any insight will be greatful... Thanks! Eric Elliston http://www.rbdstudios.com
View Replies !
Email Tasks Fail After An Unsuccessful SQL 2005 Installation
I tried installing SQL 2005 Dev Edition on my Win 2000 Professional machine. That failed. But now I cannot send out emails or even open a simple Email task from DTS. This is something that worked perfectly fine before I attempted the SQL 2005 installation. It seems that the SQL 2005 installation somehow messed up the the MAPI profile. The exact error message that I get when trying to execute or open a Email task in DTS is: CAnnot load MAPI Interface layer for DTS. Please make sure that semmap90.dll is installed.
View Replies !
CREATE FUNCTION??
Hello!How can I make this function in MS SQL:CREATE FUNCTION id_name() RETURNS INTEGER AS 'SELECT MAX(ID)+1 FROM Test;'Thanks!
View Replies !
Create Function Help
I have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated. USE database1 GO CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Output VARCHAR(8000) SET @Output = '' SELECT @Output =CASE @Output WHEN '' THEN dwg_Seq ELSE @Output + ', ' + dwg_Seq END FROM dbo.Un_Combined WHERE prt_Mark = @prt_Mark ORDER BY dwg_Seq RETURN @Output END GO prt_Mark | dwg_Seq 12 | 12a,23b 25c | 1b,5e,8d,100as I get the following errors Msg 325, Level 15, State 1, Line 2 Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. Msg 137, Level 15, State 2, Line 14 Must declare the scalar variable "@prt_Mark". Msg 178, Level 15, State 1, Line 17 A RETURN statement with a return value cannot be used in this context. Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'To'.
View Replies !
Help Create Function Day_week_month
I want to divide day of month as example: month is July week1: 02-07 week2: 09-14 week3: 16-21 week4: 23-28 week5: 30-31 --========Sunday is not including example: month is August: week1: 01-04 week2: 06-11 week3: 13-18 week4: 20-25 week5: 27-31 but if December:week1: 01 week2: 03-08 week3: 10-15 week4: 17-22 week5: 24-29week6:31 please help me!thanks so much!
View Replies !
Create Function Permission...
How do I give a Windows group complete rights (including create) to allstored procedures and user defined functions without giving them dbo accessin SQL Server 2005? If I have to I can do it from the Management Console,but I would also like to know the commands.ThanksMatthew WellsJoin Bytes!
View Replies !
Unable To Create Function
Hi All, I'm running SQL Server 2002 and trying to create a User Defined Function. However, everytime I try to save the script I get Error 170 Incorrect Syntax near 'FUNCTION'. This happens if I create the Function from Code or use the Enterprise Manager. I'm logged in with 'sa' privs, so I don't think it's a privilege issue. I'm well confused. Anyone help? TIA William.
View Replies !
How CREATE FUNCTION This Query
Code Snippet Declare @DBName as varchar(100) Declare @Query as varchar(8000) SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue Set @Query =' SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1 FROM ReciptItems LEFT OUTER JOIN ' + @DBName + '.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1' Exec (@Query)
View Replies !
Can't Create Recursive Function
Greetings. I'm having trouble creating a recursive function in T-SQL (SQL Server 2000). I've got a table that has an ID column and a ParentID column. Each row can have a value in the ParentID column that references the ID column of another record - I'll call such rows "child records". I'll cal the row referenced by the ParentID the "parent record". Each child record can itself have another child record. I need a function that will take an ID column value as a parameter, and walk up the chain of parent records until I get the first record in the series and return that record's ID value. I'll call that record the "UrParent record". I'm trygin to create a recursive function called ufunc_ST_GetUrParentCertNum. In the function, there is of course a recursive call to itself - GetUrParentCertNum. However, when I try to run the CREATE FUNCTION script, I get the error: Server: Msg 195, Level 15, State 10, Procedure ufunc_ST_GetUrParentCertNum, Line 26 'ufunc_ST_GetUrParentCertNum' is not a recognized function name. I tried the same thing with a Stored Procedure, and that worked fine. However, I really want this to work as a function. Does anyone have advice on how I can achieve this? Thanks in advance. - will f
View Replies !
Is There Anyway To Create A View Within A Function
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said 'CREATE VIEW' must be the first statement in a query batch. I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece. CREATE Function GetCommonFailurs() AS BEGIN IF OBJECT_ID(N'CommonFailures') IS NOT NULL DROP VIEW CommonFailures DECLARE @Run1Result as char(4), @Run2Result as char(4); SET @Run1Result='Fail'; SET @Run2Result='Fail'; CREATE VIEW CommonFailures AS SELECT Run1Failures.RunID as Run1ID, Run2Failures.RunID as Run2ID, @Run1Result as 'Run1Result', @Run2Result as 'Run2Result', Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath, Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath, Run1Failures.SDET as SDET, Run1Failures.CommandLine as CommandLine, Run1Failures.OutputFilePath as OutputFilePath FROM Run1Failures INNER JOIN Run2Failures ON Run1Failures.TestID = Run2Failures.TestID END
View Replies !
What Function Can Create A Record Automatically
In the table, there is a record which has several field. every month, the function will create a same record. that means, the first month, one record. the secord month, two reocrds, ..... for a years. will have same 12 record. so what function can do this? Thanks.
View Replies !
Create User Function W/Case
I keep getting an error message "incorrect syntax near keyword case"when trying to run this:USE DEDUPEGOCREATE FUNCTION fnCleanString(@mString varchar (255))RETURNS varchar(255)ASBEGINDECLARE@mChar char(1),@msTemp varchar(255),@miLen int,@i int,@iAsc intBEGINset @mChar = ''set @msTemp = ''set @miLen = Len(@mString)set @i = 1while @i <= @miLenbeginset @mChar = substring(@mString,@i,1)set @iAsc = Ascii(@mChar)casewhen @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mCharwhen iAsc >= 65 And iAsc <= 90 Then set @mChar = @mCharwhen iAsc >= 49 And iAsc <= 57 Then set @mChar = @mCharelse @mChar = ""endset @msTemp = @msTemp & @mCharset @i = @i + 1endENDRETURN @msTempENDCan anybody point out what I'm doing wrong?Thanks.Randy
View Replies !
Getting Errors I Don't Understand Within Create Function
Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue -- This function will get the numbers from the front of a field -- and return the value of those numbers in a numeric data type (@mNumInput as charvar(100)) RETURNSnumeric AS BEGIN declare @x as tinyint declare @x1 as tinyint SET @x = 1 WHILE IsNumeric(SubString(@mNumInput, @x, 1)) BEGIN SET @x1 = @x SET @x = @x + 1 CONTINUE END If @x1 > 0 BEGIN RETURN CAST(LEFT(@mNumInput, @x1), Numeric END END Here are the two error messages I'm getting from this function.Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12 Incorrect syntax near the keyword 'BEGIN'. Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20 Incorrect syntax near the keyword 'END'. I have no idea what these two error messages mean. TIA,
View Replies !
How To Create A Measure With Count Function
Hi, i created a cube that has 2 measures. I created the measures by selecting the columns from my fact table, but the function that applied in the measures was the sum function. I need to apply the count function in my measure. How can i do that? Thanks in advance.
View Replies !
How To Use The Create Relationship Function At MS SQL Server
hi all, For those who will celebrate the chinese new year, Gong Xi Fa Cai!!! I now using microsoft SQL server to manage my database. To manage the database, I have go through the SQL Server Enterprise Manager To create my database. Now I am the stage create the relationship for my relations. So when I drag the foreign key from one relation to primary key at another relation, vice versa, it will pop up the 'Create Relationship' form. What I can saw is there are three check boxes. one check boxes have 2 sub check boxes. Quote: Checkbox 1 - [Check existing data on creation] Checkbox 2 - [Enforce relationship for replication] Checkbox 3 - [Enforce relationship for INSERTs and UPDATEs] Checkbox 3.1 - [Cascade Update Related Fields] Checkbox 3.2 - [Cascade Delete Related Records] Usually the Checkbox 1, 2, 3 had been checked. but the check box 3.1 and 3.2 is display as uncheck by default. Another question is when I linked up the relationship between two of the relations will appear a asterisk(*) beside the relation's name. Why? But I not very understand to the check boxes means and the asterisk. Can someone give me some guidelines!!Thanks Thanks in billions.... Best regards John Ang
View Replies !
SQL Query - Using Result Of Create Function
I created a function that will return from OpenDataSource('.....') tablename where ... is fully populated. However, I can't figure out how to use it? For example select functiona (parameter) as data_src this returns the "from" statement above I then try to run select * data_src So how do I reference the contents of data_src in the select? Thanks for any help
View Replies !
How To Create Custom System Function
Greetings, I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server. Is there a way to create such a function? Where is it stored? Rob
View Replies !
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 !
Beginner Question: Find Or Create Function
I have a table with two columns: siteID (int primary key) and siteName(varchar(50) unique constraint).I am completely new to databases and UDFs and would like to write afunction that looks for a particular siteName and returns the siteID.If the siteName is not found then it would create a record and returnthat record's siteID.I am pretty sure there is a standard way of doing it and have beenlooking for examples, but have yet to find anything on Google.If anyone could point me in the right direction I would be verygrateful - I am still looking and will reply if I find anything.Many thanksJon
View Replies !
Granting Create Function Post Restore Of 7 Db To Sql 2k
Hi, I've noticed that if I restore a 7.0 db to sql 2k server, I can't grant create function (new to sql2k) to a user until I shutdown and restart my sql server instance. However, If I use sp_attach_db, I don't have to. sp_attach_db does an upgrade to my 7.0 database to sql 2k. Any thoughts on this?
View Replies !
Issues With SQL 2005 SP's. Select/Updates/Remote EXEC Calls With Parameters Fail With No Error. HELP???
I have finally decided to write up my issue because searching forums and Google has been a long waste of time. Has anyone else had a issue where Parms will work in sql script under the SQL server management studio but fail in a stored procedure. I have tried multiple ways for the stored procedure to utilize the parameters by compiling on the fly changing it to dynamic (that seems to work, just does not seem right to add parameters to a string, Adding quotes to execute a external T-SQL command to get results. *** Any thought or resolutions would be greatly appreciated. First Issue regarding a stored procedure select statement within a stored procedure. SELECT *,'Unsolicited' as ttype from ( SELECT Web_Outbound_Uns.ID, Web_Outbound_Uns.PIDID, Web_Outbound_Uns.Port, Web_Uns_Messages.Data, Web_Outbound_Uns.Status, Web_Outbound_Uns.Source, Web_Outbound_Uns.RcvTime FROM Web_Outbound_Uns INNER JOIN Web_Uns_Messages ON Web_Outbound_Uns.Seq_ID = Web_Uns_Messages.Seq_ID WHERE (Web_Outbound_Uns.PIDID = @ID) ) z WHERE STATUS='1' ORDER BY RCVTIME Full stored procedure on bottom of page. I have a variable @ID that, I want to use as a my search parameter. The funny thing is this will fail to work within my stored procedure. If, I set this parameter as hard coded '99 99 99 79' boom it works with out a hitch. Yes, I have tried using exec, and sqlcmd but this is the tip of the ice berg from hundreds of SP's. If, I take this script into a query editor and execute by hard coding the expected stored procedure input values, boom it works. Issue two remote SQL 2000 stored procedure call. Set @query = N'[APSQL].[Alerts].[dbo].[ProcessForcedByeCommand] ' Set @query = @query + '''' + @PID + '''' exec (@query) Again the above coded worked to accept parameters and execute the remote stored procedure. But the orginal code statement was; EXEC [APSQL].[Alerts].[dbo].[ProcessForcedByeCommand] @PID This Exec does not return a error. It just does nothing during a trace from both severs nothing is sent to remote sql2000. Change this exec where the @PID parameter is hardcoded. and it will work in a stored procedure. I have a few more dealing with Update with a index not executiong with a parameter also. Hard code the param and it works. No errors once again with any code. ALTER PROCEDURE [dbo].[Alecs_Get_All_Messages] @DeptID varchar(6), @UserName varchar(30) AS -- New System will use @UserName with '~' at begin to denote a true pidid passed in DECLARE @ID varchar(15) DECLARE @ViewCount int BEGIN TRY if substring(@UserName, 1, 1) = '~' begin set @ID = replace(@UserName, '~', '') end else begin SELECT TOP 1 @ID = [PIDID] from APSQL.Alerts.dbo.session WHERE DeptIDNow = @DeptID AND UserName = @UserName AND len(PIDID) < 8 end select *, 'Alecs/Leads' as ttype from Alecs_Web_Messages.dbo.[web_outbound] where pidid = @ID UNION ALL select *, 'Local Message' as ttype from Alecs_Web_Messages.dbo.[Web_Outbound_LocalMessages] where pidid = @ID UNION ALL SELECT *,'Unsolicited' as ttype from ( SELECT Web_Outbound_Uns.ID, Web_Outbound_Uns.PIDID, Web_Outbound_Uns.Port, Web_Uns_Messages.Data, Web_Outbound_Uns.Status, Web_Outbound_Uns.Source, Web_Outbound_Uns.RcvTime FROM Web_Outbound_Uns INNER JOIN Web_Uns_Messages ON Web_Outbound_Uns.Seq_ID = Web_Uns_Messages.Seq_ID WHERE (Web_Outbound_Uns.PIDID = @ID) ) z WHERE STATUS='1' ORDER BY RCVTIME END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage END CATCH
View Replies !
Need To Create Custom Function For Enterting Automated Database Records
Hi All,I need help in creating a function in VB for my ASP.NET application where I want to add records to database on the first day of every month.I have got no idea about what I have to do for achieving this goal.Its basically for a customer based application where Interest will be paid into customers' account and I need to implement this for every customer on 1st day of every monthThe thing I am not sure about is how can I get the application to add a record for each customer on the first day of each month, i.e. how can I get the application to check that its 1st day of month and then the application adds records automatically for each customer based on my specified rules.If any of you could help me with this, I'll really appreciate it.Thank you.
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 !
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 !
Extend GetUserID Function Or Create Additional Custom Information Funcitons
Hi there I'm relatively new to Reporting Services and .Net development so bear with me if I've missed something obvious. I've set up Reporting Services to run under forms authentication successfully. I want to filter the data (both within Report Builder Models and .rdl files) to the company the user belongs to. To avoid joining the users table to all other tables on the companyID I'm wondering if it's possible to create a custom funciton which could use the UserID to lookup the companyID and then pass this through as a parameter, and have this "GetCompanyID" function available when building Models and/or reports...??? When building a model the Information Functions available are GETUSERID and GETUSERCULTURE. Is it possible to modify how these work and/or create additional functions here??? Thanks in advance for your help. Cheers, Todd.
View Replies !
Create A Compatible Function That Can Invoke A Stored Procedure Without Writing The ParameterName And Remembing The Type And Size....
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text? public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
View Replies !
Fail To Install SQL Server 2005 (Clustering): SQL Server Setup Was Unable Add User
Hello all, I have tried to install SQL Server 2005 Standard edition with CLUSTERING. I faced a problem and everything rolls back. TITLE: Microsoft SQL Server 2005 Setup ------------------------------ SQL Server Setup was unable add user domain1xyz to local group domain1IT Security Admin-Group. For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29512&EvtType=sqlca%5csqlsecurityca.cpp%40Do_sqlGroupMember%40Do_sqlGroupMember%40x6ba I have refered to PostI=1659185 posted by Fly and it still won't be able to fix my problem. I have added LOCAL SERVICE into the local group (SQLServer2005MSFTEUser$AAA$MSSQLSERVER), but it still can't work. Please can someone help me on this. Thank a lot....
View Replies !
SQL Server 2005 (x64) Fail To Install On Windows Server 2003 R2 (x64).
I tried to install SQL Server 2005 (x64) Standard Edition on Windows Server 2003 R2 (x64) Standard Edition. My box meet the requirement. But I got the following error when the installing progress was just about to begin: Error 1016:Could not access network location k.3643236F_FC70_11D3_A536_0090278A1BB8]URTVersio. (this is exactly what i saw in the system log) what does it mean?? i am totally confused...what the hell is the k.3643236F_FC70_11D3_A536_0090278A1BB8]URTVersio???? Can anybody give me a hand? thanks...
View Replies !
No TRIM Function In 2005 - Are You Serious!?
I cannot believe that there is yet another version of SQL Server without a TRIM function. So for SQL 2005 (like I've done in 7.0 and 2000) my T-SQL will look like dog poo because I will have to write LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends of character values instead of just writing TRIM(MyValue).
View Replies !
|
TRACKER
