Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
View 1 RepliesI am trying to do following thing:i got a table which contains some words....all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table... could anyone help me out here.....thanks a lot in advance.........
View 2 Replies View RelatedExperts: Please assist with coding a trigger for a SQL Server 2005 .NET application.
Here's the scenario:
Suppose there are tables MEMBERS, ACTIVITY, and HEADCOUNT that look like this:
MEMBER
member_id (int)
member_name (varchar(50))
...etc
ACTIVITY
activity_id (int)
activity_name (varchar(50))
...etc
HEADCOUNT
headcount_id (int)
member_id (int)
activity_id (int)
...etc
Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):
headcount_id member_id activity_id
31 10 1
32 10 2
33 10 3
I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.
Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)
Much obliged for your assistance.
-Kurt Euler
San Jose, CA
Hi,
I have two little identical tables.
Pdates - (here pid is foreign key field linked to diff table proposal)
pdate
pid
pkid-primary key
Cdates - (here cid is foreign key field linked to diff table confirm)
cdate
cid
tid
strttime
endtime
pkid-primary key
I wish to just copy the records from pdates to cdates selected based on pid value with diff cid. The other fields tid,strttime and endtime will be updated later.
what kind of query should I use?
as I am a newbie to SQL server can anybody help
thanks, regards.
http://forums.devshed.com/ms-sql-development-95/writing-a-simple-replace-query-335014.html
So, what if I wanted to do a search and replace for a single quotation? I can't use that in my search. How to get around that?
example:
Code:
update NamTable
set namstr=replace(namstr,''','')
Ok I have 2 batch files and I have rum them one after one another. I am using
call batch1.bat
call batch2.bat
It is running the 1st batch fil successfully but it is not running the second one. I used a pause to see th e error it says some internal and external batch error.
Ok batch1 is at the desktop. and batch2 is in one of the folders in the desktop.
Nature of batch1 is that it runs successfully.
Nature of batch2 is that it gives and error if I individually execute it. But when I am running together then its not showing the error.l
Please if u did not understand this situation atleast show me how to run two batch files using command lines.
Thanks
Hi,
I have Table called performance where i what to get the AsOfDate(char 10) from that table... and i want to get the most recent based on this function.
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS char(10)
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 4 AND 6 THEN 5
WHEN @month BETWEEN 7 AND 10 THEN 6
WHEN @month BETWEEN 11 AND 12 THEN 7
WHEN @month BETWEEN 1 AND 3 THEN 8
END
END
and i want this to return 3/31/2008 but i want to make it dynamic like if it is "3/31' and year can be depending on the year it passes
Any help will be appreciated.
Thanks
Karen
select atleast one record where the date different is > 5 or 10 years.
I could only figure out for 5 years. so this is what I have to do.
Select top 1 Claims# where DateDiff(yy,FromDate,ThroughDate) >5
get the CalimsId of the result and do a query on that id to select all the values of the outcome id.
how i do that.
thanks
I would like to write a user defined function that takes as an input a varchar and returns an xml. If the string is valid xml the function would just return it. If the string is not valid xml then the function would encode the value before returning it.
The function would look something like this:
Code Snippet
CREATE FUNCTION dbo.EncodeXMLIfNotValid (
@InputString varchar(max)
)
RETURNS xml
AS
BEGIN
DECLARE @xml xml
BEGIN TRY
SET @xml = CAST(@InputString as xml)
END TRY
BEGIN CATCH
SET @xml = '<![CDATA[' + @InputString + '>]]>'
END CATCH
RETURN( @xml )
END
However, after writting this I figured out that using BEGIN TRY and CATCH is not allowed in a UDF.
Any thoughts or ideas how I could write a function with this behavior?
Bob
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
DECLARE
@CustomerID int,
@CustomerName varchar(50),
@CustomerSurname varchar(50),
@CustomerAddress varchar(100)
SET @CustomerID =1
SET @CustomerName='JO'
SET @CustomerSurname='BLOGG'
SET @CustomerAddress='5 London Road'
Can you help in writing something that generates and populate parameters?
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(); }
Hello
I'm trying to do a simple =Sum([unitPrice] * [quantity]) but it won't doing anything more than stay on zero! I've put it in the properties but has anyone got any ideas to get the total of this???
I have a cube with 8 dimensions: time, organization, customer, item, region, market, salesperson, shipped_from
3 Measures: invoice_qty, invoice_price, sales_total
I want to get the average invoice_price for an item ...across any dimension or groups of dimensions... what is the proper syntax for creating a calculated member using the dims and measures above
Thanks in advance
Steve
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8
John Doe Cleaning 4
Bubba Smith Fork Lift 3
John Doe Welding 7
Steve Johnson Welding 5
Bubba Smith Fork Lift 6
Page output:
John Doe
Welding 15 hrs.
Cleaning 4 hrs.
Bubba Smith
Fork Lift 9 hrs.
Steve Johnson
Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask
Any suggestions would be greatly appreciated.
Thanks,
Parallon
Hey all,
Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Thanks!
Ron Moses
ConEst Software Systems
Hi All,
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Many thanks in advance!!
Brad
----------------------------------------------
declare @today DateTime
Select @today = GetDate()
print @today
Hi All!This is the first time i am tryin to write a sql server 2000 function.I need to clean up some of my stored procedures..Can any one please give me skeleton for a userdefined function orcorrect my function. I get the following error.Select statements included within a function cannot return data to aclient.This returns a bit and it does a query to the database to decide thevalue of the bitCREATE FUNCTION GoodAd(@adid int,@currentdate datetime)RETURNS bitASBEGINdeclare @Return bitselect @return = 1if exists (select null from adqas where adid=@adid)select @return 0return @returnend
View 2 Replies View RelatedI have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
Hi,
We are seeing memory issues with a simple C# based TVF that splits an input string into a table based a delimiter. Entries such as the following show up in the SQL log:
AppDomain 8 (DBName.dbo[runtime].7) is marked for unload due to memory pressure.
AppDomain 8 (DBName.dbo[runtime].7) unloaded.
These entries only show up after the TVF has been called many times (~ half million times or more).
We encountered the same memory issues with June CTP, but they appeared to be fixed in Sepetmber CTP. Somehow the issues come back for us in the SQL Server 2005 RTM version. With June CTP after these errors show up many many times, the SQL server had to be re-started.
I'd appreciate any comments/ideas regarding these error messages in the SQL log?
We are using the RTM version of SQL2005: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks,
Wenbin Zhang
I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed
View 6 Replies View RelatedHi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 Replies View Relatedfound it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
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
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!
I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.
USE Database
GO
SELECT MIN(Wage) AS "Min Salary", Status
FROM Employee, Job
WHERE Status = '1'
GROUP BY Wage, Status
GO
The result set brings back all not the "status" class that I want.
Again, I am relatively new so take it easy on me!
Thanks,
grinch
Hello,
I wrote a stored procedure in SQL 2K5, and I would like to write to the sql log without using a rasieerror function. I just would like to log the sucessfull steps without exiting the proc. I am not sure how to do this. Any help would be appreciated.
Thanks,
Dave
Hi,
I am new to this programming and SQL.
I am following the: Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database video series. I cannot get the following to write changes to the SQL Database:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
BindingSource1.EndEdit()
'CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
Dim rowsAffected As Integer = 0
rowsAffected = CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
MessageBox.Show(rowsAffected.ToString())
End Sub
End Class
I do get the message box to popup showing 1 row of changes when I make one change, however that change does not get written to the database. Is this enough info to have any idea why?
Thanks
I was hoping that someone might be able to help a SQL statement for this, if possible. I have two tables, InvoiceDetails and InvoiceLineItems. What I'd like to do is write a query that will return all invoices, but also return a value InvoiceTotal Based on SUM(InvoiceLineItems.qty * InvoiceLineItems.unitPrice). I've tried a few different methods of writing this, but haven't had much luck. Can anyone shed some light on this for me? Thanks.
View 7 Replies View Related