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
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 5 Replies View RelatedHi 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.
Hi, I'm having issues trying to get a query working the way I want, it maybe
that i'm overly complicating things though.
What I have done so far is have 2 seperate tables one holding details about
the item to be booked out with an ID the second linking to the Item via the
ID and also having the startdate and the enddate of the booking, thus an item
will have multiple rows in the bookings table for multiple bookings.
What I want to have is a "quick" booking method where a user enters the
startdate they would like and the enddate, a drop down is then filtered (via
a query) returning only the items that are avalible.
The issue i'm having is that because my bookings have multiple rows for each
item, for what maybe true in the rules for an item in 1 row maybe false
alittle later - i which case the returned data i am getting is incorrect!
Hopefully I have made sense, and maybe someone can help?
my querry for the filter so far: -
SELECT DISTINCT DeviceDetails.Device_ID, DeviceDetails.Device_Name
FROM DeviceDetails LEFT OUTER JOIN
BookingDetails ON BookingDetails.Device_ID =
DeviceDetails.Device_ID
WHERE (BookingDetails.Bookout_Date IS NULL) OR
(BookingDetails.Bookout_Date >= GETDATE()) AND
(@Dateout <= BookingDetails.Bookin_Date) AND (BookingDetails.Bookout_Date >=
@Datein) OR
(BookingDetails.Bookout_Date >= GETDATE()) AND
(@Dateout >= BookingDetails.Bookin_Date) AND (BookingDetails.Bookout_Date <=
@Datein)
Is this possible?
here is the situation. I have a DB on one system. I back it up and then restore it to a second system. This second system I run reports off of and I want to create custom views that do not exist on the original system. Can I restore the
backup DB from the remote system without wiping out the custom views on the local system?
I have to do this this way as they won't let us create the views we want on the remote system so the only way we have access to run the reports is by restoring the backup locally.
TIA!
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work?
All help is greatly appreciated!
Lynnette
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?
Thank You!!
hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.
eg
Table MemberDetail
MemberID WorkingDay
M1234 12
M1234 10
M1555 02
M1555 15
Hi,
I am using custom dll in script component in SSIS package. This dll is looking for some configuration settings and dsplays the message as "Configuration section could not be found in the configuration source" . Please tell me the configuration source it looks for.
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double
if pFields !FA00902_AMOUNT.Value > 0 and
pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
Thanks in advance,
Buster
I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).
I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
View 3 Replies View Relatedim creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View 3 Replies View RelatedI've gone through all the steps, and when I finally drag the custom task I've made into the control flow, I get the error
"Failed to create the task. ... Cannot create a task with the name "SSISExportToExcel.ExcelExport.ExcelExport, SSISExcelExport, ... Verify the name is correct"
Here's the beginning of my class... any help would be appreciated...
Namespace ExcelExport
<DtsTask(DisplayName:="Excel Export Task", _
Description:="Exports a SQL query results to an Excel Document")> _
Public Class ExcelExport
Inherits Task
....
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
Hello,
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Can anyone help me out? I am not that familiar with VB.
Thanks,
Deb
I created a SSIS log provider derived from LogProviderBase, it's appear in my VS2005 and work fine, the only one problem is, there is not a dropdown list of connections in the SSIS Log Configuration column, how can I add it?
http://technet.microsoft.com/en-us/library/ms403361.aspx
thanks
I'm building a custom component and UI and am a bit confused on where I
need to create and/or set custom column
properties?
My UI will have a datagrid with three
columns: 1) a check box to select a column for use by the component, 2)
the input column name, and 3) a "differentiator" checkbox that indicates
an extra property about some of the columns that have the first column
checkbox checked (For example, my component may be using five input columns, but
three of those need to be used in a slightly different
way.)
The problem is, I don't understand when or
where I'm supposed to create the custom property for the input
column. SetUsageType is where I've been thinking, but I don't
know if I'm supposed to be creating it for an input column or a virtual
input column. I'd appreciate any guidance.
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar
(
wk_Date INT IDENTITY NOT NULL,
FullDate DATETIME NOT NULL,
WeekDayName VARCHAR(9) NOT NULL,
IsWorkday varchar(20) NOT NULL
)
[Code]....
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
In SQL 2000 for Procedures
View 5 Replies View RelatedWe're building a simple logging facility and would like to systematically determine the name of the current T-SQL object(i.e., the procedure name or function name) to provide this as a logging parameter. For example, in procedure FooBar, I'd like to be able to call a system function that will return 'FooBar', the name of the current object. Does such a feature exist?
Thanks - Dana
Hi,
I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.
It's been a long question but I desperately need the answer. Any thanks will be appreciated.
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
Below is code I am using to create a heatmap in SSRS. THe code seems to be working well except for one glitch. Sometimes numbers in the "middle of the pack" are white. Obviously they should be one the of colors listed below. Why numbers would be white when I declared only white for IsNothing.
Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
Dim colours As String() = New String() {"White","DarkGreen","Green","ForestGreen","LimeGreen","YellowGreen","Yellow","Gold","Orange","DarkOrange","OrangeRed","Red"}
If IsNothing(textBoxValue) = True Then
Return colours(0)
[Code] ....
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies View RelatedI created a clr proc that gets the most recent file within a directory based on the creation time property, see code below. I have attempted to replicate this within a clr scalar valued function in order to assign the resulting value to a variable within SQL server. I am getting the error message:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_fn_recentfile":
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at clr_fn_recentfile.clr_fn_recentfile.clr_fn_recentfile(SqlString Filepath)
After trying to troubleshoot this I am aware that this error is rather generic and have not been able to find any specific documenation regardint the use of file system objects with clr functions. I am at a loss. Any help would be appreciated!
STORED PROC CODE WORKS
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_recentfile
_
Public Shared Sub clr_recentfile(ByVal Filepath As SqlString)
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Return
End If
If fil <> Nothing Then
qry.CommandText = " SELECT '" & fil & "'"
'Execute the query and pass the result set back to SQL
sp.ExecuteAndSend(qry)
sp.Send(qry.CommandText.ToString)
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Sub
End Class
FUNCTION CODE DOES NOT WORK WITH ABOVE ERROR
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_fn_recentfile
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function clr_fn_recentfile(ByVal Filepath As SqlString) As SqlString
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Exit Function
End If
If fil <> Nothing Then
Return fil
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Function
End Class
My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?
Thanks
AdminAnup
Hi everyone, this is is my first post, so please reply and help.
I'm working on a project right now that uses asp 2.0 and SQL server 2005 express edition. This is a general idea of the project. In our company some of us receive ECO notifications (engineering change orders) for our products and we need to implement these to the test scripts that are on the production floor. So the project is about entering the new ECO into a database which will send an automatic notification to our test team. When they receive the notification they will have to sign in to the website and introduce their login and password to sign off the ECO (Following some checkpoints already defined by me, for example, Area ready, Test script modification necessary, new firmware introduction, comments, etc...) but I also need to record WHO and WHEN sign that ECO. We have 3 different test areas in our factory: Electrical, Functional and Systems, so all THREE areas must be signed off in order to the ECO go to a IMPLEMENTED state (at this point i need to send a new email saying that the eco has been implemented in all three areas).
So far I've completed the following things:
-users validation (logins, areas)
-New custom entry form for the ECOs and automatic email notification (part of what I did is described below). Dim ECODataSource As New SqlDataSource()ECODataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ECO_ICSConnectionString1").ToString()
ECODataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
ECODataSource.InsertCommand = "EcoNew"
ECODataSource.InsertParameters.Add("EcoNumber", EcoNumberTextBox.Text)
ECODataSource.InsertParameters.Add("EcoDescription", EcoDescriptionTextBox.Text)
ECODataSource.InsertParameters.Add("EcoMandatory", EcoMandatoryDropDownList.Text)
-Depending on which test area is the the engineering from, I can filter the ECOs and just shows the ones that their test area is pending. (using GridView)
But I'm stuck right now when the engineers have to sign the ECO for their test areas. I was able to use the Gridview and DetailsView to EDIT most of the things that I need. But there are somethings that I don't like:
1. When using the EDIT option on Gridview or Detailsview, all fields can be edited including ECO number, description and mandatory, which I don't want them to change. If I set those columns to read only, when editing that row again. It gives me an error that says that the ECOnumber can't be NULL, but if I remove these 3 columns the Engineer will not know which ECO they have sign. They are only going to be able to see the EcoId, which doesn't say much.
2. Also I saw that I wasn't able to do is to enter the USER login and CURRENT system date and time automatically. I don't want them to manually enter the date and their login manually.
3. Finally, when the last area signs the ECO, I want to update that record and set a flag that tells me that the ECO has been completed.
So what I really want is to create some sort of form (textboxes, labels, checkboxes, etc.) that will UPDATE the selected ECO from the gridview for instance. So when I select the row from the GridView, It will show the data (Econumber, description and mandatory as READ ONLY) and use the rest of the things as INPUT for the engineer to complete. At the end an "update button" and when I click it, It will enter/update the data on that specific row, but including the time and user login as well.
Also to check if the other 2 areas have signed and if so, change the ECOReadiness flag to 1 and send the email.
Is there a code like the one I used above to do this ? Or if you think there a better way to do this, I'll be very glad to hear it.
I'm new using sql and asp, so If i'm asking some dumb questions please forgive me. .
Here's my table definition for your reference:
EcoId - primary key.
EcoNumber
EcoDescription
EcoMandatory
EcoReadiness <- Flag for the entire ECO, when ALL 3 areas have signed, this will be 1.
ATE < - Flag for Electrical area.
ATEscripts < - Just a Yes/no input.
ATEengineer <- user login
ATEdatetimestamp <- Date.Now()
FAT < - Flag for functional.
FATscripts
FATengineer
FATdatetimestamp
SYSTEMS < - Flag for systems.
SYSTEMSscripts
SYSTEMSengineer
SYSTEMSdatetimestamp
THANKS IN ADVANCE,
Regards,
Jesus
Hi,
I imagine this will be done through the use of expressions.
What I would like to do is this. When my package fails, it's set up to send an email using the Send Mail Task.
I would like to create a custom subject line that contains:
1.) Name of the package
2.) Name of the task that failed (if possible)
I am seriously wondering how I can do this.
Thanks much
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
Thanks.