Count Function: SQL Server 2005
is it possible to do a count on the same column but under different circumstances posting the results in two different result columns?
View Complete Forum Thread with Replies
Related Forum Messages:
Calculation Using Count Function In SQL Server Business Intelligence Development Studio
Hello All, I am working on a report using SQL server Business Intelligence development studio in which I need to count the total number of saleslines that either has a status : Delivered or Invoiced. I am using the inbuilt count function under the calcuations, as: Count(Fields!SalesStatus.Value, scope). I am not sure what scope means here however, I read that scope refers to some dataset, dataregion or grouping. I dont know what that means. I shall be really thankful if someone can help me with this. Regards, Rashi
View Replies !
SUM A Count Function
I need to perform a summation on the results of a COUNT but I'm not sure what's the best way to do it. Example: TableA --------------- CaseID (int) FollowupCorrespondence (int) (not guaranteed to be sequential) CustomerName (varchar) /**Get the total number of followups for each customer case, and display the customer's name.*/ select T.caseid, sum(T.cnt), T.CustomerName, T.FollowupCorrespondence from (select CaseID, count(FollowupCorrespondence) as cnt, CustomerName, FollowupCorrespondence from tableA group by caseID, CustomerName, FollowupCorrespondence; ) as T group by caseid, customername, followupcorrespondence I get the following output, but what I need is to get '4' in each row of the SumCases column. CASEID SUMCases CUSTOMERNAME FOLLOWUPCORRESPONDENCE ---------------------------------------------------------------------------------------------------------------- 1.................1.............John Doe................1 1.................1.............John Doe................2 1.................1.............John Doe................3 1.................1.............John Doe................4
View Replies !
Need Help With Count Function
Hi, I am new to sql language so bear with me. Suppose I have a relation table Student(department, name, gender): department, name, gender Bio Elisa F Physics Jeanne F Physics Rocky M Math Andy M Math John M and I want to find the number of male students in each department, how should I do it? this is what I got so far: select department, count(*) from student where gender like "M" group by department; but the problem with this query is I will get this table Department Count(*) Physics 1 Math 2 What I want is Department Count(*) Bio 0 Physics 1 Math 2 Can someone help with this? Thank you
View Replies !
How To Use Count() Function?
Dear all, I have a table with the the following columns: ProductCode varchar(50) PK, ProductType varchar(50), ProductCategory varchar(50), InStock (bit), Locked (bit) What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked. i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked. How do I do this in a query? Any help is very much appreciated. Eugene
View Replies !
Multiple Count Function?
OK I am having problems trying to figure out or if it is even possible to do this in MSSQL Here is my records 1=New 2=Open 3=Closed Table stats ID, Status 1 1 2 1 3 1 4 2 5 2 6 3 So I am trying to count each of the status So Total records should be 6, New 3, Open 2, Closed 1 records. SELECT COUNT(*) FROM Stats Thats about all I know on this, will give me the total. but is there a way to count the total and count individuals.
View Replies !
Can't Divide A Count Function?
I am trying to divide 2 funcitons by each other and I am coming back with 0 as my result in one of them while I am getting the correct answer in another. The two columns I am trying to pull with functions are: SELECT sum(D.PrincipalBal)/sum(L.PrincipalBal)*100 as DelqRatioByBal , count(D.LoanID)/count(L.LoanID)*100 as DelqRatioByCnt FROM Loan L INNER JOIN DelqINFO D on D.LoanID = L.LoanID I get the correct result back for 'DelqRatioByBal' but I get 0 back for 'DelqRatioByCnt' Any suggestons?
View Replies !
COunt Function In Report
I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far. I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial. So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field I also want to print the the same fields in the report total area. Any ideas ?? =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID") =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID") =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")
View Replies !
Add Count Function To My Query
Hello, I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated. SELECT A.ParentSubjectName, A.ParentSubject, A.SubjectId, B.CreatedOn FROM dbo.Subject A INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId WHERE A.ParentSubjectName LIKE 'ACDelco Products%' AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30') AND A.SubjectId IN ( 'C44ADE3E-527B-DC11-8A2D-00170857BDE7', 'F8758E52-527B-DC11-8A2D-00170857BDE7', '7E65F458-527B-DC11-8A2D-00170857BDE7', '7F65F458-527B-DC11-8A2D-00170857BDE7', '2BE35262-527B-DC11-8A2D-00170857BDE7', '2AE35262-527B-DC11-8A2D-00170857BDE7', 'A2002127-527B-DC11-8A2D-00170857BDE7', '41A8A66F-527B-DC11-8A2D-00170857BDE7', 'A3002127-527B-DC11-8A2D-00170857BDE7', 'D6C08B45-527B-DC11-8A2D-00170857BDE7', 'C439FB4B-527B-DC11-8A2D-00170857BDE7' ) ORDER BY B.CreatedOn[/blue]
View Replies !
Count Function To Filter
Guys, I'm using the following code and I just want display all glcodes that have a count less than 2: select glcode, sum(abs(sysvalue)) 'Movement', count(glcode) 'Occurances' from jet group by glcode order by occurances The following sytax works i know i need to use the where function. Does anyone know the syntax? Cheers Michael
View Replies !
Count/sum Function Query
create function mytotalcount (@audit varchar(50), @startdate datetime, @enddate datetime) returns table as return ( select t.value,sum(t.countvalue) as totalcount from ( select sm.value,count(sm.value) as countvalue from subjectbase s join stringmap sm on s.organizationid = sm.organizationid inner join audit a on s.subjectid=a.subjectid inner join incidentbase i on i.subjectid=s.subjectid where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and sm.attributename = 'contractservicelevelcode' and sm.ObjectTypeCode = 112 group by sm.value ) t group by t.value ) value totalcount ------------------ NHLBI Employee329 NIH Employee329 Public329 VIP329 instead of different values i m getting same... there is something wrong in joins..can anyone help me? thanks.
View Replies !
Need Help With Count Function And Temporary Tables
I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle
View Replies !
COUNT FUNCTION ON MULTIPLE COLUMNS
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
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 !
Need Function To Count Business Days
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future. Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well. TIA Phil
View Replies !
Count Function Grouping And Subtotals
Here is what I am trying to accomplish. I need to list the number of Sales lines for a customer per order they they place as MTD values, YTD values and 12 months rolling values. here is an example. order No. Number of lines MTD Number of Lines YTD 1001 3 1002 3 1003 5 Customer totals: 8 3 How do I get the total count in the group footer. I am using the following calculation for the Number of lines MTD on the Report Layout area =iif(Fields!Posting_Date.Value>=Fields!monthstartdate.Value and Fields!Posting_Date.Value <= Fields!monthenddate.Value,COUNTdistinct(Fields!Document_No_.Value),0) where monthstartdate and monthenddate are values defined in my dataset. I guess my question is: is there any way to sum up values in the report layout in the group footer section based off of another calculated field on the report layout. THanks
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
Including Text And Count Function In The Same Table Footer
Hi I know this is going to sound embarassingly obvious, but i cannot find a quick solution. I have some data, I display that data in a table. Simple so far. I have a table footer, which I want to display the total number of rows returned. For example "Total Rows Returned =" Now I know how to get the total rows returned: CountRows("Dataset1") However when I try to put the two together like this: = "Total Rows Returned =" + CountRows("Dataset1") I just get the whole thing outputted, and no total for totalrows... Anyone know what I am doing wrong?
View Replies !
Better Method To Count Records In Custom Paging For SQL Server 2005
heres my problem, since I migrated to SQL-Server 2005, I was able to use the Row_Number() Over Method to make my Custom Paging Stored Procedure better. But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure. What I want to know is: Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction??? heres my stored procedure: SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum, morerecords = (Select Count(Ad_Id) From Ads) FROM Ads) as testWHERE RowNum Between 11 AND 20 The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me. Thank for your time.
View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly.
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition. I have two SQL Server instances installed. 1- UserLT (this is sql 2000)2- UserLTSQL2005 (this is SQL 2005 named instance) But when i try to add a database to my VS website project I get the following error: Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925 I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default) and changed the "SQLEXPRESS" to "USERLTSQL2005". But I still get the same error message. Any ideas how i can resolve this issue?
View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly
hello, i've installed SQL server 2005 express and Visual web developper 2005 express. when i whant to create a database in VWD the following error occures: connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251 i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays. please help!
View Replies !
VS2005 Error: Connection To SQL Server Files (*.mdf) Requires SQL Server Express 2005 To Function Properly.
Good Evening All, I've serached this forum and Google'd for a resolution to this issue, to no avail. Here's the scenario: I'm running VS 2005 on Windows Media Center laptop and need to create ASP.net membership for my web application built using VB. I have SQL Server Developer installed with instance name MSSQLSERVER. Previously, I uninstalled SQL Express and installed Developer edition and can now open and utilize Management Studio. Now, when I try to create a new SQL Server database in my solution's App_Data directory, I receive the above error. I already changed instance name in VS2005 per Tools-Options-Database Tools-Data Connections to MSSQLSERVER. Could someone provide me with a list of procedures to ensure proper setup of VS2005 with SQL Server Developer Edition? Thanks much for your help.
View Replies !
Sql Server 2005 GETDATE() Function
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector. When I use the SQL Mangment Studio Express to execute this query: SELECT GETDATE() it returns 2008-04-22 16:25:03.690 which is OK !! I want to get that same value using a Java program so I do that query but when I display it it shows as: 2008-04-22 and there is no time there ! I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM So maybe the time isn´t there ? This is the code i am using: Statement st = con.createStatement(); String sql = "SELECT GETDATE()"; ResultSet result1 = st.executeQuery(sql); result1.next(); Date today = result1.getDate(1); Any help would be appreciated.
View Replies !
SQL Server 2005 Query With Date Function
I wrote a function and a SQL to get the 3 columns Date,Total Orders & Amount, for dates between Date Started and Date Completed if I pass different Dates in the SQL I get the correct result but if I pass same dates then I don't get the result I am looking for .For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.But I give same dates for Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.Some how I could not trace what could be the error in my SQL /Function.I appreciate if I could get some work around for this.Thanks! Function:create function dbo.CreateDateList(@start datetime, @end datetime)returns @t table ( [date] datetime )asbegin if @start is null or @end is null return if @start > @end return set @start = convert(datetime, convert(varchar(10), @start, 120), 120) set @end = convert(datetime, convert(varchar(10), @end, 120), 120) while @start < @end begin insert into @t ( [date] ) values (@start) set @start = dateadd(day, 1, @start) end returnend ---------SELECT qUERY---------- SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101) Union SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID Order by Date
View Replies !
How To Run User Defined Function On Sql Server 2005
Dear all I wants to run sql server user defined function when linked two server. I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me? Regards Jerminxxx
View Replies !
SQL Server 2005 Query/trigger/function (whatever It Is That I Need)
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario table 1 = stock table table 2 = Purchase order table in table 2 there are line items that have ordered quantities for parts that we have ordered in table 1 under each part number is a field for "quantity on order" I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1 quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match so for part number 516 i have this table 2 poNumber partNumber quantity 1 516 1 2 516 12 3 516 4 table 1 partNumber inStock onOrder 516 0 17(this is what i am trying to figure out how to compute) any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.
View Replies !
Does SQL Server 2005 Has Similar Function Like Mysql_fetch_row ?
I just began to use SQL Server 2005 as database programming and found out that I have to translate mysql_fetch_row into SQL Server 2005 but I cannot find some related functions/api, and I was wondering Does SQL Server 2005 has similar function like mysql_fetch_row ? Or if not, any advice how I can program to acheive similar functions ? thank you in advance.
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 !
SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record
Hello, I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column. Any help you can provide will be greatly appreciated! Thank you, Nathan
View Replies !
ODBC Link From Access To SQL Server 2005 Stored Function
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC? I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access. I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time. What am I missing? Suggestions?
View Replies !
Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View Replies !
Problem With (SQL Server 2005) Reporting Services Drill Through Using SWITCH Function In Sharepoint Integration Mode
When deploying reports to a report server in Sharepoint Integration mode (post SQL 2005 SP2), when using the SWITCH function when drilling through to another report ('jump to report'), you have to append the ".rdl" entension to the report name. For example, on a report server in native mode, the following will work: =SWITCH(Fields!Region.Value="US","USRegionDrillDownReport", Fields!Region.Value="Europe","EURegionDrillDownReport") When deploying to a report server in Sharepoint Integration mode, you must do the following: =SWITCH(Fields!Region.Value="US","USRegionDrillDownReport.rdl", Fields!Region.Value="Europe","EURegionDrillDownReport.rdl") When doing this, the deployed report will work, but the link in the Visual Studio developer environment will no longer work (and vice versa).
View Replies !
Count(name) &> 1 [MS SQL 2005]
for MS SQL 2005 I want to find all users with same sName CREATE TABLE [Users] ( [id_Users] [int] NOT NULL PRIMARY KEY CLUSTERED , [fName] [varchar] (100), [sName] [varchar] (100) ) I must get all the rows with count(sName) > 1 20 , Aldous Leonard, Huxley 26 , Bob, Huxley ....... thank you for helping
View Replies !
SQL Server 2005 &&"lag&&" Function
Hello, Does SQL Server 2005 have anything like a "lag" function that, in a sigle query allow you to retrive a previous record? The syntax would be like: SELECT transactionid ,transactiondatetime ,amount ,amount + LAG(amount) OVER(ORDER BY transactiondatetime) balance FROM bankaccount Thanks for any advice
View Replies !
Altering Table Issues (total Row Count Vs Exported Row Count)
Please help me... I had a table with x number of fields... I had this data source view and model ... if I selected some information about the table everything was ok... yesterday I altered the table adding two more fields. I create a new data source, data source view and model to get the new database fields included... now I filter using those fields... and the report is telling me... 81 records... but I exported the data to csv... and I only see 58 records... I do a manual query thru query analyzer... and got the 81 records... some records did not appear... I have no filters... no relations, just one table just one field selected in the report I'm building. any ideas ??? kindest regards. elias.
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 !
Can A Sql 2005 Function Return More Than A Variable
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
View Replies !
Calling VB.NET Function From Trigger In SQL 2005
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running. I need help on how can I capture insert trigger event VS 2003 console application?
View Replies !
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 Replies !
|
TRACKER
