Implementing Oracle's GREATEST And LEAST Functions In T-SQL
Sep 15, 2006Has anybody been successful in doing this? I've seen a few articles on the web, but none for free....
View 4 Replies
ADVERTISEMENT
Greatest And Least Functions
Mar 2, 20011.I am trying to write following query in SQLserver(this is oracle query),
IS their any equivalent for GREATEST and LEAST functions in SQLServer ???
Update mwebtemp
Set Temp_amount1 = (SELECT SUM(AuthAttr_Amount*(num_Business_Days
(GREATEST(AuthAttr_Start_Date, @dtstartdate),
LEAST(AuthAttr_Finish_Date, @dtenddate))))
.....where...
etc...
.....
Note: this is part of a stored procedure...
2. Also how do you write and call a function in SQLServer (syntax ??), eg.num_Business_Days
is a function returning number of business days between the 2 dates....
thanks a lot
Very Urgent Greatest And Least Functions
Apr 6, 2001I have to to find the greatest and least values from the set of values which are from the different columns.
Eg. Select greatest(tab1.date1, tab2.date2), least(tab1.date3, tab2.date4)
from tab1, tab2
where....
tab1 and tab2 are the tables and
date1, date3 are the columns from tab1
date2, date4 are the columns from tab2
Is there any easy way to do this in SQL server ?
MAX and MIN will not work here as these are not the max and min from the same column they are from the different tables and columns.
Thanks
Need Help--implementing Functions In C#???
May 5, 2008Hi y'all I think i have some problem in here perhaps you guys can help.I have this code: 1 public void AddQstrWhere(ref string TmpStr,string Parameter)
2 {
3 if(TmpStr=="")
4 {
5 TmpStr +="WHERE"+Parameter;
6 }
7 else
8 {
9 TmpStr +="AND"+Parameter;
10 }
11 }
12
13
14 public string querySlsPerson;
15 //public string queryLastSls;
16 public ArrayList CLSGetSalesman(string szSalesmanID)
17 {
18
19
20 // TODO: Add CLSReportManager.CLSGetSalesman implementation
21 BOSTx Tex = GetTx(CLSReportConstants.TrnMasterID.FQN2,"QueryRptSales");
22 try
23 {
24 string querySalesPerson = "SELECT dbo.BOS_GL_Workplace.WpszState, BOS_PI_Employee_1.szName, dbo.BOS_PI_Employee.szName AS Expr1, dbo.BOS_PI_Employee.szEmployeeId, "+
25 " dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription "+
26 " FROM dbo.BOS_PI_Employee LEFT OUTER JOIN "+
27 " dbo.BOS_GL_Workplace ON dbo.BOS_PI_Employee.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN "+
28 " dbo.BOS_PI_Division ON dbo.BOS_PI_Employee.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN "+
29 " dbo.BOS_PI_Team ON dbo.BOS_PI_Employee.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN "+
30 " dbo.BOS_PI_Employee BOS_PI_Employee_1 ON dbo.BOS_PI_Employee.szSupervisorId = BOS_PI_Employee_1.szEmployeeId ";
31 //"WHERE dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
32 //" WHERE BOS_GL_Workplace.WpszState = '" + szProvince + "' AND BOS_PI_Employee.szName = '" + szsalesPrsn + "' AND BOS_PI_Employee_1.szName = '" + szSupervisor + "'";
33
34 string wheretext = " dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
35 string queryLastSls;
36 //AddQstrWhere(szSalesmanID,wheretext);
37 if(szSalesmanID==string.Empty)
38 {
39 queryLastSls = querySalesPerson;
40 }
41 else
42 {
43 //string querySlsPerson;
44 AddQstrWhere(ref queryLastSls,wheretext);
45
46 queryLastSls = querySalesPerson + querySlsPerson;
47 }
48
49 OleDbCommand comd = Tex.GetTextCommand();
50 comd.CommandText = queryLastSls;
51
52 DataTable slTable = new DataTable();
53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd);
54 adapt.Fill(slTable);
55
56
57 ArrayList DataSalesArr = new ArrayList();
58 foreach(DataRow row in slTable.Rows)
59 {
60 CSDatasales sls = new CSDatasales();
61 sls.sz_province = row["WpszState"].ToString();
62 sls.sz_supervisorname = row["szName"].ToString();
63 sls.sz_salemanname = row["Expr1"].ToString();
64 sls.sz_salesmancode = row["szEmployeeId"].ToString();
65 sls.sz_Divisi = row["Divisi"].ToString();
66 sls.sz_typeOp = row["szDescription"].ToString();
67
68 DataSalesArr.Add(sls);
69 }
70 Tex.CloseConnection();
71 //AddQstrWhere("",wheretext);
72
73 return (DataSalesArr);
74
75 }
76 catch(Exception ex)
77 {
78 throw ex;
79 }
80
81 }
82 You see I want to make the query more dynamic. So instead of making the SQL parameter permanent I wanna make it dynamic, so the usercan choose whether they want to use the parameter or not. I already declare the parameter which is szSalesmanID. There's a function called AddQStrWhere which can make the query more dynamic. But the thing is when I wanna use the parameter it seems the code doesn't take it, it just use the regular query I dont know why.I already use the function but it just wont work, can you guys tell me what's wrong, Please I really do need some help from you guys, feelin; a lil' bit desperate in here.I appreciate any kinds of help, thanks. FYI: I use Visual Studio.NET 2003, SQL Server 2000.Best Regards.
Greatest Value
Mar 15, 2006Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.
View 7 Replies View RelatedGREATEST AND LEAST
Apr 24, 2007Hi All,
Is there any equivalent of GREATEST(something) and LEAST(something)
in SQL server 2000
Thanks in advance.
vishu
Bangalore
Greatest Value
Mar 14, 2006Hi all. I'm looking for assistance to get the greatest value from 3 ormore different columns. I'm assuming that the best way is to put thecolumns into a temp table and use 'max' function to return the greatestvalue but don't know how to code it. Thanks in advance.*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedSQL With Greatest Date
Nov 7, 2006Here is my SQL statementSELECT a.name, b.meterno, c.meterstatus, d.accountno, d.creation_dateFROM fmdata.location a, cisdata.service_meters b, cisdata.meter_master c,cisdata.Account_Master dWHERE a.location_id = b.location_id AND b.meterno IS NOT NULL AND b.meterno= c.meterno AND a.location_id = d.location_idORDER by meterstatus, nameHere is my resultsNAME METERNO METERSTATUS ACCOUNTNO CREATION_DATE01010004 20512944 Active 101000402 7-Feb-200201010004 20512944 Active 101000401 8-May-199701010005 25917180 Active 101000501 27-May-200301011001 13646231 Active 101100102 17-Mar-199901011002 18389246 Active 101100201 29-Apr-199401011003 84473845 Active 101100301 24-Apr-199701012002 47511850 Active 101200202 26-Jan-199601013001 35653963 Active 101300101 28-Feb-1979If you notice I'm getting two 01010004 under the NAME column. I would likethe SQL statement to select the greatest CREATION_DATE if there areduplicate NAME'S. So in this case, it would select the top 01010004 since ithas the greatest CREATION_DATE.thanks in advancebart
View 2 Replies View RelatedGreatest Function In TSQL
Aug 20, 2007Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error
GREATEST is not a recognized built-in function name.
all I have is
declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)
Can somebody point me in the right direction.
Thanks so much
Summing Only The Greatest 5 Values In Each Group
Jan 10, 2008Hello,
Here's one way to sum only the top 5 (greatest 5) values per group.
I assume there is a table called IdValues that contains two columns: id int, value int.
declare @lastId int
declare @value int
declare @count int
declare @idList varchar(5000)
declare @valuelist varchar(5000)
set @count=0
set @lastId = -1
set @value = 0
set @idList=''
set @valuelist=''
select
@count=(case when @lastId<>id then 1 else @count+1 end),
@value=(case when @lastId<>id then value when @count<=5 then @value+value else @value end),
@idList=(case when @lastId<>id then cast(id as varchar)+','+@idList else @idList end),
@valuelist=(case when @lastId<>id then cast(@value as varchar)+','+@valuelist else cast(@value as varchar)+','+right(@valuelist,len(@valuelist)-charindex(',',@valuelist)) end),
@lastId=id
from IdValues
order by id desc, value desc
select @idList,@valuelist
It's a funny approach. I'd be interested to see a better method. In MySQL it is possible to do this much better and have it produce an actual resultset (since MySQL allows you to assign variables and product a resultset in the same query).
I also noticed something interesting. If you do any operation on the order-by columns, the query doesn't work. For example, if I do:
order by id+0 desc, value desc
something funny happens and you only get one id and one value in the list variables. Maybe someone else who actually some idea of how SQL Server works can explain this.
Thanks,
Thomas
Selecting One Greatest Number From Two Columns?
Jul 20, 2005I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
View 1 Replies View RelatedFind The Greatest Of Three Columns Per Each Row And Display
Nov 15, 2006Hi,
i have a problem where in i have to display the greatest marks scored by each student.
How can i do this?? Is there any built in TSQL function.
Rgds..,
Aazad
Any Function In T-SQL Thats Similar To GREATEST() IN Orcl.
Dec 6, 2007
Is there any Function in T-SQL thats similar to GREATEST() IN Orcl?
Iam trying to find the greatest of two DATETIME datatype Fields in my 'select's.
If there is no in built function, Whats the best approach?
What Happens When Autoincrement (auto Increment) Reaches Its Highest (greatest, Last) Value?
Aug 19, 2007What happens when autoincrement reaches its last value? Does my application crash? Will autoincrement wrap?
Let us assume I figure that my database table will be much smaller than 2^16 records. Since it will be nowhere near the limit, I select smallint (2 bytes wide) as my ID field. I set the seed value to -32,767. I set the increment value to 1. Over the course of actual useage, many records are added and deleted many times. Although I correctly anticipated that the number of records in my database is far less than 65,535, autoincrement has reached 65,535. What will happen next? Ideally I would like autoincrement to wrap and take the next unused value. But does it do that? Or does it freeze at the highest value? What happens?
SQL Server 2005: CLR Functions Vs SQL Functions
May 26, 2006I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies View RelatedImplementing SCD Type 1 && 2 Using DTS
Jul 22, 2003Hi!
How to Implement scd type 1 and 2 functionality using DTS.
Implemented the functionality using T-SQLS, but want to make use of DTS. Can we do this using DTS without using T-SQLs.
Thanks
Implementing A Search
Mar 28, 2008Here's my table:
----------------------------
News
----------------------------
ID | Headline | Article
----------------------------
I want the user to be able to search for keywords in articles. Here's my stored procedure:
CREATE PROCEDURE sproc_GetNewsSearch
(
@keyword varchar(50)
)
AS
SELECT Headline, Article
FROM News
WHERE Article LIKE '%' + @keyword + '%'
This will work fine if I search with one word. If I enter "zebra", it will find the articles containing "zebra".
The problem is when I search for more than one word. If I enter "monkey zebra", it won't search for articles containing "monkey" and "zebra", it will look for "monkey zebra".
How can I fix that?
Implementing Preconditions
Oct 23, 2006I have about a half-dozen insertion tasks that depend on a condition: SQLServerProcessDate == OLEDBprocessDate. If the task executes early, old data gets inserted and needs to be backed out later on. (This happens about two-three times a month.)
The jobs start at 8:00 PM, but on occasion processing runs late. In order to make the task intelligent enough to not insert old data, I've thought of a couple options:
1.) Separate scheduled job runs continuously, comparing process dates. When the dates match, it sends a message to the other jobs. Pro: less repetitive. Con: another scheduled job?!? I'll have to learn the message queue task; a little bit complex; flood of database activity on message reception may slow system down
2.) Compare the dates in each individual task; if they're not the same, sleep for a while (15 min.) Pro: simpler. Con: I haven't found any way to pause/sleep/wait a task based on a condition; repetitive functionality in multiple tasks
Any opinions on which is the better choice?
Implementing IDTSLogging
Oct 12, 2006I am writing my own SSIS package host and I want to create custom logging and event trapping in the host. My goal is to reroute logging calls to the patterns and practices logging block and control the logging destinations using the config file.
Here is an excerpt of code from my main method of the host:
// recurse through all DtsContainers in package
//and enable logging, set filter kind to Exclusive, and clear filter list
ConfigureLogging(package);
// LoggingEventHandler inherits from Microsoft.SqlServer.Dts.Runtime.DefaultEvents
LoggingEventHandler eventHandler = new LoggingEventHandler();
// LoggingBlockLogger implements IDTSLogging
LoggingBlockLogger logger = new LoggingBlockLogger();
DTSResult result = package.Execute(null, null, eventHandler, logger, null);
The behavior I am looking for is that regardless of what logging providers are configured in the package, all the providers will be bypassed and all logging will be passed through my custom LoggingBlockLogger class. However, I find that if there were any providers configured in the package, they are being passed logging events. I tried returning 'false' from LoggingBlockLogger.Enabled, and I'm returning an empty array from LoggingBlockLogger.GetFilterStatus().
I followed the stack trace from the point of entry into the package log providers and found that they were being called directly from ManagedWrapper.Log. Why are the package providers still being called? What is the purpose of the IDTSLogging interface if every container simply passes log entries to the providers in its LoggingOptions.SelectedProviders collection?
Thanks!!!
Implementing An Audit Mechanism
Jun 19, 2006We're trying to implement an audit mechanism to track all changes made to our database and would appreciate any suggestions or alternate ways to do this, we're using the regular trigger based approach.
We also need to capture the user name of the current logged in user - this is NOT the sql server database user, it is at the application level, the user who logs into our site. We were planning to pass this username using context_info in SQL SERVER through triggers as described here - http://www.sqlservercentral.com/columnists/spustovit/easyauditingasharedaccount.asp
Alternatively I could add a userId field to every BC and store the logged in user's name there but I'd rather avoid that.
Implementing A Simple Search
Mar 26, 2008I'm not trying to do anything too fancy; given a string, I just wanna see if anything in the column of my database matches the string. I'm using an SQL query that takes a string, then selects the data using LIKE %searchword%.This works fine when the user enters only one word. But I guess you can see that a problem arises when they enter more than one word.So how can I implement a very simple search that will take more than one word?
View 5 Replies View RelatedImplementing A Customer System
Mar 4, 2006Hi there, I am a little confused with some data ideas in .NET 2.0.
First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions?
Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself.
If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say?
Thank you very much!
Tristan
Implementing Standby SQL Server
Feb 15, 2000Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.
Many Thanks in advance!
Help Implementing Service Broker
Feb 26, 2008
I am working on a project where I want to implement service broker. A lot of the examples that I have seen are all based on T-SQL. Here are my questions.
1. I have a web page that allows a user to type in an order. How do I put a message on the queue using a .NET assembly? Is there some sort of API set? Do I just pass the message to a stored procedure that puts the message on a queue?
2. Are there any samples that illustrate how to read messages off a queue using a console application or windows service? Is it possible to read the messages off of the queue with a console application or do you have to subscribe to the queue activation event and write a service program that runs under the control of service broker? All of the data that I need to fill the order has to come from a DB2 database on the mainframe. Therefore, I am stuck with creating an external application that processes the messages.
3. In order to use Service Broker, do I have to install Notification Services as well?
Implementing Ranking Position
Jan 17, 2007Hello,
I have a group that is custom made on the table's grouping expression, something like
=SWITCH(Fields!Country.Value="Portugal",IIF(Fields!Storetype.Value="Frs","Portugal Frs", "Portugal"),
Fields!Country.Value="Italy","Italy",Fields!Country.Value="Spain","Spain",Fields!Country.Value="United States","United States",
Fields!Country.Value="Internet","Internet",Fields!Country.Value<>"","Other")
The thing is that i order this by my value, and then i use a column that is the rowcount, but sadly i got
11 Portugal
19 United States
30 Spain
How can i do this like
1 Portugal
2 US
3 Spain
Is there something like in C ++1 or something that increments a number depending on the line of the table that is in?
Thank you
Implementing Incremental Database Backup
Apr 11, 2007Hi,
I am working on one application, which retrieves data from multiple tables in the database and all the fields retrieved, are exported as an excel sheet. All the export functionality is done through DTS. And the data is retrieved using an SP.
I am supposed to use an "incremental backup" approach here. Means, for the 1st scheduling of the package, all the database dump will be there in excel file. But, after that only fields that are updated/inserted are to be filled in the excel file. Each time, when the dump of the database is taken, the excel file is stored in an archive folder with Date and tiemstamp(e.g. TEST_11_04_2007_15_00_00.xls)
e.g. For the first dump, I get 100 records from the database. Before next execution of the package, 10 rows get inserted and 1 row gets updated. So, on the second time execution of the package, I should populate the excel sheet with those 11 rows only, and not 110 records.
I am not authorised to change the database schema.
So, is there any approach to try out this?
Implementing DB Security In Existing Server
Mar 25, 2008Dear Colleagues,
How do I install a Microsoft SQL Server 2005 database application in an existing server in an existing database server and still have the control over it and also restrict the new server Admin user from editing or even opening my DB. Any modification of my database or code should be implemented only by me. Is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.
Thanks and best regards,
Peter
Implementing Read-write Locks
Jul 23, 2005Hi,I would like to use database locking mechanism to control access to anexternal resource (like file system).What I need is1. an exclusive (write) lock conflicting with any access to theresource (both for read and write)2. non-exlusive (read) lock conflicting with writes onlyHow this could be done?I'd appreciate any reply.Vadim
View 1 Replies View RelatedGet Error Implementing Linked Servers
Apr 29, 2008Hi Everyone.
I am trying to setup a linked server on 2 machines. Both machines have identical SA login/pwd. Also, both machines have a database called Federated_Bridge.
The setup I have is as follows:
Machine 1: (local) - As it appears in Enterprise Manager
Machine 2: (RealIBM2) - As it appears in Enterprise Manager
Earlier I executed a query to successfully link machine 1 to machine 2 as follows:
-------
EXEC master.dbo.sp_addlinkedserver @server = 'TEST3', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='REALIBM2', @catalog='Federated_Bridge'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST3',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
-----
The above query works. However, when I try to do the same thing from Machine 2, it's not working. It doesn't seem to like the (local) name for the @datasrc field. Here is my new query .. which is failing:
-----
EXEC master.dbo.sp_addlinkedserver @server = 'TEST2', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='local', @catalog='Federated_Bridge'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST2',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
----
The question I have is, how do I reference the SQL instance that is saying (local) ?
Thanks!
Implementing A Custom Task UI Problem...
Jan 23, 2008
Hello,
Anyone experienced in creating custom tasks?
I am currently having problems while trying to use a UI form within my task.
This is the way i am coding it:
In the same project i have two classes and one Form: A CustomTaskExample, a CustomTaskExampleUI and a CustomTaskForm
Class: CustomTaskExample
Code Snippet
< FONT>"CustomTaskExample", _
UITypeName:="CustomTaskExampleUI," & _
"Version=1.0.0.0,Culture=Neutral," & _
"PublicKeyToken=b853fe59589b971f", _
TaskType:="PackageControl", _
TaskContact:="CustomTaskExample; Testing", _
RequiredProductLevel:=DTSProductLevel.Enterprise)> _
....
Class: CustomTaskExampleUI
Code Snippet
Public Function GetView() As ContainerControl _
Implements IDtsTaskUI.GetView
Return New CustomTaskForm(Me.taskHost, Me.connections)
End Function
When i try to drag the task to a new Package i get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Failed to create the task.
------------------------------
ADDITIONAL INFORMATION:
The task user interface specified by type name 'CustomTaskExampleUI,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=b853fe59589b971f' could not be loaded. (Microsoft.DataTransformationServices.Design)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=CouldNotLoadTaskUIForType&LinkId=20476
------------------------------
The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
What am i doing wrong?
Regards
<>
Ask: Implementing Stored Procedure In SSIS
Jan 8, 2008Hi all,
I need to know whether changing/migrating process in SP to SSIS is a good idea to enhance performance in datawarehouse.
My client want to do this because they have problems in performance while executing SP from dts 2000 packages.
So they don't want to use SP anymore. They want to use all SSIS features.
Since I'm newby with SSIS, I don't have any idea how to implement SPs in SSIS, especially while working with temporary
tables.
Btw, this is snippet of the SP I want to change :
ALTER PROCEDURE [dbo].[DBAS_BBCBG_DataDaily]
@CustomDate datetime = null
AS
BEGIN -- Begin Procedure-
--15 Minute
DECLARE @date datetime
SET @date = isnull(@CustomDate,
(SELECT top 1 Reporting_Date From DBDS..DataDate))
SELECT * INTO #TbDataDaily FROM DBABC.dbo.TbDataDaily WHERE 1=2
BEGIN
INSERT INTO #TbDataDaily (
Col1, Col2, Col3, Col4, ValDate
)
SELECT CD1,CD2,CD3, CD4, @date
FROM DBAS..HDKFS
CREATE INDEX IDX_ABC_DataDaily ON
#ABC_DataDaily (Col3,Col4) ON [PRIMARY]
END
BEGIN
UPDATE #TbDataDaily
SET Col2 = B.CABLVT
FROM #TbDataDaily A, DBAS..COLACBFP B
WHERE A.Col3 = B.CAKNTN
END
... and so on...
all other processes in the SP are just about to SELECT/INSERT/UPDATE temporary table, and finally
fill the data to physical table.
So how can I change this SP to SSIS package? (of course if this is the best way to improve performance)
Thank You in advance.
Best Regards,
Ricky Lie
Implementing Paging Using Store Procedure
Mar 19, 2007Hi All,
I am using store procedure to which we pass ...
1.Sql query (String)
2.Starting index (Integer)
3.Page size (Integer)
4.total records out (Integer)
It works fine for the simple queries like "select * from tblcountry" but give error in case when we have sort of inline query or order clause or some complex query etc.
Despite of what is happing in the procedure can any help me out with a simple store procedure to which i give any query , start index and page size it should give me record set according to the given page.
For example query could be 'Select * from tblcountry where countryid in (select * from tblteachers) order by countryname desc', start index=0 , page size is 100 and total records are 500.
Note:
When i pass this query to sql server directly using command object its exectued successfully (this mean that it will also run fine in query analyzer), but i want to use paging in sql server so that is why i need a procedure to implement paging and query could be any its fully dynamic but i will be a valid query.
Your help in this regard is realy apperciated...
DB Design :: Implementing Hierarchies In Server
Jun 12, 2015I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager, Delivery Manager).
One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child has more than 1 parent).
Are there other more efficient ways? What is the best way to implement this?