Need Help--implementing Functions In C#???

May 5, 2008

Hi 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 }
14 public string querySlsPerson;
15 //public string queryLastSls;
16 public ArrayList CLSGetSalesman(string szSalesmanID)
17 {
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 + "'";
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);
46 queryLastSls = querySalesPerson + querySlsPerson;
47 }
49 OleDbCommand comd = Tex.GetTextCommand();
50 comd.CommandText = queryLastSls;
52 DataTable slTable = new DataTable();
53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd);
54 adapt.Fill(slTable);
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();
68 DataSalesArr.Add(sls);
69 }
70 Tex.CloseConnection();
71 //AddQstrWhere("",wheretext);
73 return (DataSalesArr);
75 }
76 catch(Exception ex)
77 {
78 throw ex;
79 }
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.   

View 2 Replies


Implementing Oracle's GREATEST And LEAST Functions In T-SQL

Sep 15, 2006

Has anybody been successful in doing this? I've seen a few articles on the web, but none for free....

View 4 Replies View Related

SQL Server 2005: CLR Functions Vs SQL Functions

May 26, 2006

I 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 Related

Implementing SCD Type 1 && 2 Using DTS

Jul 22, 2003


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.


View 5 Replies View Related

Implementing A Search

Mar 28, 2008

Here's my table:

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)
SELECT Headline, Article
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?

View 6 Replies View Related

Implementing Preconditions

Oct 23, 2006

I 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?

View 7 Replies View Related

Implementing IDTSLogging

Oct 12, 2006

I 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

// 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?


View 2 Replies View Related

Implementing An Audit Mechanism

Jun 19, 2006

We'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 -
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. 

View 2 Replies View Related

Implementing A Simple Search

Mar 26, 2008

I'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 Related

Implementing A Customer System

Mar 4, 2006

Hi 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!

View 3 Replies View Related

Implementing Standby SQL Server

Feb 15, 2000

Can 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!

View 1 Replies View Related

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?

View 3 Replies View Related

Implementing Ranking Position

Jan 17, 2007


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",


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

View 1 Replies View Related

Implementing Incremental Database Backup

Apr 11, 2007


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?

View 1 Replies View Related

Implementing DB Security In Existing Server

Mar 25, 2008

Dear 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,


View 3 Replies View Related

Implementing Read-write Locks

Jul 23, 2005

Hi,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 Related

Get Error Implementing Linked Servers

Apr 29, 2008

Hi 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) ?


View 1 Replies View Related

Implementing A Custom Task UI Problem...

Jan 23, 2008


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=,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.
The task user interface specified by type name 'CustomTaskExampleUI,Version=,Culture=Neutral,PublicKeyToken=b853fe59589b971f' could not be loaded. (Microsoft.DataTransformationServices.Design)
For help, click:
The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) (mscorlib)

What am i doing wrong?



View 6 Replies View Related

Ask: Implementing Stored Procedure In SSIS

Jan 8, 2008

Hi 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
Btw, this is snippet of the SP I want to change :

@CustomDate datetime = null
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

INSERT INTO #TbDataDaily (
Col1, Col2, Col3, Col4, ValDate
SELECT CD1,CD2,CD3, CD4, @date
#ABC_DataDaily (Col3,Col4) ON [PRIMARY]

UPDATE #TbDataDaily

... 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

View 5 Replies View Related

Implementing Paging Using Store Procedure

Mar 19, 2007

Hi All,

I am using store procedure to which we pass ...

1.Sql query (String)
2.Starting index (Integer)
3.Page size (Integer) 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.

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...


View 10 Replies View Related

DB Design :: Implementing Hierarchies In Server

Jun 12, 2015

I 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?

View 9 Replies View Related

Implementing Inheritance For Hierarchical Data

Oct 12, 2007

in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data and auditing and reporting on individual nodes. what is the best way to store this type of data? my current approach is to have an attribute table having nodeid as a foreign key and only store the direct and NOT the inherited attributes of the node in the table, but this means to find all the attributes for the node, I need to gather the attributes of all the parents until the root node. I can't see any easy way out for this.

View 4 Replies View Related

Need Help Implementing Weka Into SQL Server 2005

Jul 29, 2007


I'm a student of computer science, and for one of my projects I would need Weka plug-in for SQL Server 2005. I don't know much about plug-ins, so if I'm asking stupid questions, I hope you will forgive me. It was easy to get a library from weka that I can use in Visual Studio, but I just can't figure out, how to get Weka data mining algorithms into plug-ins for MSSQL 2005.

I would appreciate any help given...


View 8 Replies View Related

Transact SQL :: Implementing CDC For History Tracking

Mar 1, 2012

This question is based on both T-SQL as well as SSIS. We have around 120 history tables which would be loaded with the history records from the Main tables. Following is an example of a Main table and its corresponding history table

Main table - Patient
Columns - PatientID (Identity), PatientFirstName, PatientLastName, Suffix, DOB, etc...
History table - PatientHistory
Columns - PatientHistoryID(IDentity), PatientID , PatientFirstName, PatientLastName, Suffix, DOB, etc...

So almost all of the History tables would be having the same columns from the Main table along with the identity field for the history table.

At present we have After triggers for Update which pulls the records from the deleted table and inserts these records into to history table. I have been asked to implement it without triggers.

My question is - Can we implement CDC for this and is it the best option performance wise as well

View 4 Replies View Related

Implementing Interactive Sorting Without The Botton.

Jun 8, 2007


I want to implement interactive sorting on few columns of my report. That is working fine but the the button coming for interactive sort is taking extra space.

I have some space constrains due to too many columns in the report and so i want that implementation without the button(its taking extra space and also ruining the alignment) , may be with a underline in the column header
like hyperlink or something.

Is it possible.

Thanks in advance.


Priyank Pandey.

View 4 Replies View Related

Implementing Incremental Database Backup

Apr 11, 2007

Hi,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?

View 2 Replies View Related

Implementing Drop Down Box In Reporting Services

Aug 14, 2007


I'm creating a report in reporting services wherein i would like to give a drop down box for selecting the respective database for which a report is to generated. Is there a way of goin about this...Pls help.

View 14 Replies View Related

Syntax Error While Implementing Forms Auth

Aug 9, 2007

Hi All:
Need quick help guyz. I am implementing Forms Authentication on my website below is the code for the same... However  I am getting the following error ....
Line 1: Incorrect syntax near 'SQLSTR'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at GreyHounds.Login.btnLogin_Click(Object sender, EventArgs e) in c:inetpubwwwrootGreyHoundsLogin.aspx.vb:line 49  Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Dim con As New SqlConnection("server=localhost; initial catalog=Grey Hounds; integrated security=SSPI")Dim SQLSTR As String = "Select * from Emp_Login"
Dim cmd As New SqlCommand("SQLSTR", con)Dim p1 As New SqlParameter("@EmpId", SqlDbType.VarChar)Dim p2 As New SqlParameter("@Password", SqlDbType.VarChar)
p1.SqlDbType = SqlDbType.VarChar
p1.Value = txtEmpID.Text
p2.SqlDbType = SqlDbType.VarChar
p2.Value = txtPass.Text
cmd.CommandType = CommandType.Text
con.Open()Dim dr As SqlDataReader = cmd.ExecuteReader()
If Not txtEmpID.Text.Equals("") Or txtPass.Text.Equals("") Then
If dr.Item("Emp_Login") = p1.Value And dr.Item("Password") = p2.Value ThenFormsAuthentication.RedirectFromLoginPage(txtEmpID.Text, True)
Session("Emp_ID") = p1.Value
End If
lblMessage.Text = "Invalid Credentials!Please try again"
End If
lblMessage.Text = "Cannot leave the boxes empty! Please enter the values"Catch ex As SqlException
Response.Write("<b>" & ex.Message & ex.HelpLink & "</b>")
Response.Write(ex.StackTrace)Catch ex1 As Exception
Response.Write(ex1.Message & ex1.Source & ex1.HelpLink)
con.Close()End Try
End Sub
Your quick help is highly appreciated...

View 2 Replies View Related

Implementing Security In Totally Open Environment

Aug 4, 2015

I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....

(where's the 'Puking' icon??)

They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.

View 0 Replies View Related

Access To Several SQL Servers Implementing Trusted Connection

Jan 16, 2007

Being an IT consultant, I have to visit a number of customers having applications running on SQL Servers that implement trusted connection security model. In addition, all of these companies have their own DOMAIN settings, and security implementation.

How can I access to databases without changing the DOMAIN settings of MY notebook? I do not want them to switch to SQL security model, and I do not want to create any security hole in those companies.

What shall I want from the system administrator so that I can login into their domain without changing my domain settings?



View 5 Replies View Related

Shrinking Mdf File After Implementing New Filegroups - SS2005

Feb 18, 2008

I have a database where I add some new Filegroups and moved some Gb's of
tables and indexes to new file groups.
Now I would like to shrink the source of these tables/indexes: the mdf file.
This means there should be much empty space now in the mdf file.

I tried to use dbcc shrinkfile, but it run for more than one day.
Are there some good strategies about shrinking the mdf file?
I had to restart the instanse because got problem using the db while dbcc runned...

Thank you for help

View 2 Replies View Related

Implementing Transaction In SSIS Package Using SQL Task

Dec 16, 2007

Hi all,

I am having a simple ssis package. In that i have an sql task that will insert a record in to the database. i find that if i use a simple OLEDB connection it works fine. but the problem comes here when i change the connection string with an valiable. Once i assign the same connection string to a valiable and assign the variable as expression, there after i encounter the following error.

[Execute SQL Task] Error: Failed to acquire connection "DB connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

I am struggling with this for a long time.

Any of you plz suggest me a solution for this problem


View 2 Replies View Related

Reporting Services :: Implementing SSRS In A New Environment

Nov 12, 2015

I've been charged with architecting a new SSRS solution for our environment. We're mainly a .NET shop which works with an Oracle Database Server. Our developers want to start using SSRS for report creation and I'm not sure what is the best approach when it comes to this. Normally most of our environments have 3 separate servers, each one is DEV, QA and Prod. With SSRS however, i know that you can create multiple folders each with its own datasource connection, so i wonder if it would be better to just have one server were the developers develop their reports and then move them into a 'production' folder. (Any particular downsides to this approach?)

Also, are there considerations to be taken say, to tune Production SSRS vs a development SSRS or is that not really an issue? Besides that any other resources or tips for implementing SSRS for the first time, we're not going to do much with the actual databases to start we're just going to connect to our main Oracle database. 

View 2 Replies View Related

Copyrights 2005-15, All rights reserved