How To Change The Sql Queries Generated Internally By Functions Supported By MSADO Dll For MS SQL Server2005

May 3, 2007

We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary).
When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.

We are using MSADO15.DLL for database connectivity.
We have a table where ID column is set to primary key with Auto Increment true.
We use AddNew() function of this library to insert a new record.
We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0.
When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.

We tried to trace this problem using SQL Server Profiler Tool of SQL Server.
AddNew() function performs following operations in back end
1. Inserts the record to the table using INSERT
2. Calculates the auto increment field ID using SELECT @@IDENTITY
3. Fills this ID in the structure passed to AddNew() and returns it.
But ID returned by SELECT @@IDENTITY query is wrong.
There are other ways also to retrieve the last ID inserted in table by
IDENT_CURRENT(€˜table_name€™) which return right ID.

Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL?
Or there is another way of retrieving right ID?

View 3 Replies


ADVERTISEMENT

How To Change The Sql Queries Generated Internally

May 3, 2007

We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary).

When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.



We are using MSADO15.DLL for database connectivity.

We have a table where ID column is set to primary key with Auto Increment true.

We use AddNew() function of this library to insert a new record.

We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0.

When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.



We tried to trace this problem using SQL Server Profiler Tool of SQL Server.

AddNew() function performs following operations in back end

1. Inserts the record to the table using INSERT

2. Calculates the auto increment field ID using SELECT @@IDENTITY

3. Fills this ID in the structure passed to AddNew() and returns it.

But ID returned by SELECT @@IDENTITY query is wrong.

There are other ways also to retrieve the last ID inserted in table by

IDENT_CURRENT(‘table_name’) which return right ID.



Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL?

Or there is another way of retrieving right ID

View 1 Replies View Related

How To Change The Sql Queries Generated Internally

May 3, 2007

We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary).

When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.



We are using MSADO15.DLL for database connectivity.

We have a table where ID column is set to primary key with Auto Increment true.

We use AddNew() function of this library to insert a new record.

We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0.

When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.



We tried to trace this problem using SQL Server Profiler Tool of SQL Server.

AddNew() function performs following operations in back end

1. Inserts the record to the table using INSERT

2. Calculates the auto increment field ID using SELECT @@IDENTITY

3. Fills this ID in the structure passed to AddNew() and returns it.

But ID returned by SELECT @@IDENTITY query is wrong.

There are other ways also to retrieve the last ID inserted in table by

IDENT_CURRENT(‘table_name’) which return right ID.



Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL?

Or there is another way of retrieving right ID?

View 1 Replies View Related

Install SQL Server2005 Error:[Native Client]Encryption Not Supported On The Client

May 10, 2006

Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

View 78 Replies View Related

SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions

May 12, 2015

Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data

View 1 Replies View Related

Optimizing Queries Generated At Runtime

Sep 5, 2007

Hi

We are using SQL SERVER 2005, enterprise/standard edition for an application that generates queries at runtime and normally having many joins in it. But these queries are taking lot of time when they are executed. After setting the database parameter PARAMETERIZATION to FORCED, performance of the queries have improved a lot but still we want to improve them further. Is there any other parameter that we can set for improving the performance of the queries. We have created the indexes on the basic columns that will be used while querying but it may happen that some other columns may also be used for searching in which case queries become quiet slow. Is there anything like skip-scan indexes(of oracle) in sql server 2005.

Regards
Salil

View 4 Replies View Related

Change Report Is Being Generated

Feb 20, 2006

Hello,

I develop some application using SQL Server Reporting Services 2005 to Russian customers. They usually don't know English well. That's why I really want to change phrase "Report is being generated" to Russian words.
How can I do it? I tried to fing this phrase in configuration files or registry but there wasn't success.

Thank you,

View 17 Replies View Related

Different Queries In Different Sheets Of The Same Excel Work Book For A Report Generated In SSRS 2005

Apr 23, 2008

i need to get the result of two different queries into 2 different sheets of the same excel file while exporting a report to excel using reporting services..

somebody please help

View 1 Replies View Related

Sub Queries, Aggregate Functions && Group By Clause

Feb 17, 2008

Hi Guys,

I am having trouble with a particular query that is beyond my scope of understanding.

Basically I need to pull sales records based on the following criteria:

I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..

Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.

For example, CustomerNumber 123 has recontracted 3 times..

once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..

So they have a 12 month contract each time.. then come in to recontract it.

So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.

Currently to pull ALL sales records for all customers, my query is this:


Code:


SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber

FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice

WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL



We want to get a list of customers that we can call to recontract, based on the ContractEnd field.

However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.

So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:


Code:


SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB



Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)

BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..

Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.

Can anyone please help me!

Thanks guys,

Mick

View 1 Replies View Related

Saving Tables That Are Generated By Queries As HTML File Or Sub-tables

Oct 17, 2006

I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.

The table has columns like Commodity, Unit, Quantity, Value, Month, Country

A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"

The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.

It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.

I wanted to know if there is an alternate way to pull the data from server ?

I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??

OR

Can I write a script that creates a html files for each table for all input combinations save them ?

OR

Is there exists any other solution ?

View 1 Replies View Related

Dynamically Change The DataFlow Queries

Mar 20, 2006

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

View 2 Replies View Related

Will Change In Index Slows Down Queries In SQL Server2000 Sp4.

May 12, 2008



Hi

Will change in index slows down queries in SQL Server2000 sp4.

Please help me

View 4 Replies View Related

Two Count Queries - Calculate % Change In Number Of Orders

Mar 12, 2015

I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.

I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.

View 2 Replies View Related

Change Connected User To Avoid Owner Prefix In Queries

Mar 16, 2007

Hi everybody,I've five instances of SQL Server 2000 with the SAME database with aDIFFERENT owner in each server. I, as the administrator, have a lot ofqueries that I have to execute in some or all servers. The problem isthat I have to connect to all servers with MY user, not each of the dbowners...So I have queries this way:select * from mike.table1 t1 join mike.table2 t2 on...And when I connect to another server I have to change mike for jeremyin all the SQLs...And when I connect to another server I have to change jeremy for ninain all the SQLs...I know that there was an old, v7, deprecated way to change the"schema", something likechange current user to kimberlygoselect * from table1 t1 join table2 t2 on...This way, I'll change ONLY once the connected user. I could even do atthe beginning of the script an IF, to change the connected userdepending on @@SERVERNAME !!!Can someone remember this instruction???Thanks in advance for your help !!!

View 1 Replies View Related

Internally Convert To GMT?

Sep 25, 2006

Hi there,Can SQL Server 2005 be made to report the date as GMT using the getDate() function? (without changing the local time on the box that it's installed on?)If I could get the current offset value within a Stored Procedure that would help.. Thank you! 

View 1 Replies View Related

Entity Framework: No Support For Server-generated Keys And Server-generated Values

May 23, 2008

Hello

I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".

How can I add entities to my Sqlce- database?

I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.

What do I have to change to make successfully a SaveChanges()?

Thanks for your help,
Gerald

View 21 Replies View Related

How Data Is Inserted Into Table Internally

Nov 2, 2015

I would like to know how data is inserted and selected from table internally. Like how buffers are created and how they are executed.

Eg. Table1 
col1 col2 col3
1 a 1
2 b 2

I want to know how this data is inserted into tables. How many buffers are created ? Does it depends on the columns? How buffers are executed? 

View 7 Replies View Related

Clean Text Files Externally Or Internally

May 17, 2008

Quick history, I have substantial experience using MS Access, VBA, and Regular Expressions to clean up messy text files for use in MS Access.

I want to upgrade an MS Access back end to SQL Server (2008 to be specific). I need to upload several delimited files on a daily basis. The files are full of formatting that I strip out using VBA and Regular Expressions. I've poked around on the site and found some UDFs that implement the VbScript Regular Expressions object as a stored procedure in Transact SQL.

My questions is, am I better off processing the files externally using a procedural language like VB or can SQL Server handle the processing as gracefully as MS Access/VBA/RegEx does?

Thanks for letting me tap into your experience.

I could simply puzzle it out myself by implementing it in SQL Server but I'd rather not waste the time as a newb to transact SQL and then find out that I'd be better off doing it externally.

J--

View 1 Replies View Related

Working With SQL 2005 Interface But Internally It Works As SQL 2000

Aug 27, 2007

Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.� So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)� So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?

View 2 Replies View Related

How To Get The Output Parameter From An Internally Called Stored Procedure???

May 29, 2005

Hi all,i have a problem and couldnt find anything even close  to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane

View 8 Replies View Related

Working With SQL 2005 Interface But Internally It Works As SQL 2000

Aug 27, 2007

Hi!

I have installed SQL 2000 and SQL 2005 in my computer. But none of 2005 feature is working. So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000:
€œMicrosoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)€?

So, how can I change it for working with SQL 2005?. It€™s like I€™m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?

View 1 Replies View Related

BUg In JDBC Driver 1.2 - Fails To Call Stored Procedures Which Use Ap_name() Internally.

Nov 9, 2007

Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .


This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver (2006), suceeds in both cases.

Here is a test case which demonstrates this problem.

create table ICS_ConsraintTest (PrimaryIdentifier varchar(64),
ServiceProvider varchar(64),
SecondaryID varchar(64),
SecondaryServiceProvider varchar(64),
Description varchar(64),
PanoramaObject varchar(64),
Operation int,
ScalingFactor int,
DisplayAs float,
FeedType int,
InputValue int,
OutputValue float,
salary float,
birthdate datetime,

CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0
and len(ltrim([ServiceProvider])) > 0
and len(ltrim([SecondaryID])) > 0
and len(ltrim([PanoramaObject])) > 0
and [salary] <> 0.0)))
Stored procedure id defined as follows:


CREATE PROCEDURE SP_ICS_TestWithConstraints1(@PrimaryIdentifier varchar(64),
@PrimaryServiceProvider varchar(64),
@ServiceProvider varchar(64),
@SecondaryID varchar(64),
@SecondaryServiceProvider varchar(64),
@Description varchar(64),
@PanoramaObject varchar(64),
@Operation int,
@ScalingFactor int,
@DisplayAs float,
@FeedType int,
@InputValue int,
@OutputValue float,
@salary float,
@birthdate datetime) AS
BEGIN

BEGIN TRANSACTION

BEGIN
/* Insert */
INSERT INTO ICS_ConsraintTest ( PrimaryIdentifier,
ServiceProvider,
SecondaryID,
SecondaryServiceProvider,
Description,
PanoramaObject,
Operation,
ScalingFactor,
DisplayAs,
FeedType,
InputValue,
OutputValue,
salary,
birthdate)

VALUES ( @PrimaryIdentifier,
@ServiceProvider,
app_name(),
@SecondaryServiceProvider,
@Description,
@PanoramaObject,
@Operation,
@ScalingFactor,
@DisplayAs,
@FeedType,
@InputValue,
@OutputValue,
@salary,
@birthdate)
END
COMMIT TRANSACTION

END
Check out the app_name() is passed as the SecondaryID which causes the failure.

View 6 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

Sql Server2005

May 12, 2008

 
hi I have developed one tool which will show the realtime data of memory,processor, disk and Network...these data will change foe every second...
i want to store that data into the SQL server...is there any script to do this?...whether i need to do some scheduling?.....can anyone help me out please?...
Thnks in advance...
 

View 10 Replies View Related

SQL Server2005

May 12, 2008

Hi,
  I have developed one tool in ASP.NET(C#) which will show the realtime data of memory,processor,disk and memory which will refresh for every second...
 i want to store this data into the sql server...is it possible to store the dynamic data?...if not how can i achieve this?...
 evev i want to store the performance of all the remote servers which are in the intranet....is it possible to do?...how to achieve this?.. can anyone help me out...?
 
 
 

View 4 Replies View Related

New At Sql Server2005

Apr 28, 2006

Hello
When i execute the Management Studio Express how can i verify (or define or ...) my server name? (If i choose Browse for servers i can´t find any of them local or network servers)
I have to choose windows authentication or sql server authentication?
Sorry about my basic questions :)

View 1 Replies View Related

DTs In Sql Server2005

Mar 5, 2007

Hello evrybody..........
hope u all r doing fine ...well i hve a little problem that i hve installed sql server2005 express edition on my machine and now i just want to import my clients database which is on Acess but m not getting DTS wizard anywhere in 2005 edition so what should i do....infact i found the exe of dtswizrd in program files microsoft sql server folder but when i hit that exe it throws error that one of the file is could not be loaded it is SQLSVC.RLL file...so what should i do shall i reinstall my package ???and even m not confirmed that the free packageof 2005 which i hve downloaded by download center..wheather microsoft gives the whole package or not???like because here in my package i don't find DTS wizard anywhere like we have in 2000 package....
So does anybody help me Plz?????
hope for a positive reply from all u experts .........
thnks

View 7 Replies View Related

Sql Server2005 Err

Jun 7, 2008

We have SQL Server 2005 on windows 2003 server +SP2. In the application log I noticed the following errors during .

Event Type:Error
Event Source:MSSQLSERVER
Event Category:(3)
Event ID:19019
Date:6/6/2008
Time:10:28:13 PM
User:N/A
Computer:COMP NAME
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4b 4a 00 40 01 00 00 00 KJ.@....
0008: 08 00 00 00 53 00 51 00 ....S.Q.
0010: 4c 00 43 00 4c 00 55 00 L.C.L.U.
0018: 53 00 00 00 00 00 00 00 S.......

When error occur at this time excute a procedure to export table in csv file use bcp and copy this file in Network folder
so what should be error?

View 1 Replies View Related

I Need SQL Server2005 Materials

Oct 18, 2006

hi every body ; Ineed sql server 2005 material 

View 5 Replies View Related

What Is Catalog Name In Sql Server2005?

Apr 22, 2008

Hi,
When i execute this query

SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'a424465';
I can see the..

CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER ...
schema1 schema2


I wanna change the SCHEMA_OWNER to be schema1...

How can be done???
Plz Help me ASAP..

View 1 Replies View Related

Sql Server2005 -Alerts

May 29, 2008

hi
how are the use alerts in sql server2005,
and which alert type used in maximum and how to performaing our database

I would like to get user experiences

View 3 Replies View Related

Replication In SQL Server2005

Aug 20, 2006

Hi there i need an advice on replication in sql2005

i've heared that i can't make replication between two cluster servers installed on them sql2005 and they are in different domains so please can you tell me is that true and if is what can we do to solve this because they are in different places and we can't give them the same domains of IPs

thanks

View 3 Replies View Related

Sql Server2005 And Sql Mobile DB (.sdf)

Feb 21, 2006

hi.

I am too green in sql2005.

so the problem is with data import from csv or txt or access files to sql mobile database.

at internet i fount that tis is wery simple but i can not find how?

so dts wizzard have not this function.

coul someone provide directly steps which i shoud do to import/export data from sdf db

i have vs2005 and sql2005

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved