Sql Server 2005 Query Help.

Jun 4, 2008

Hi,

I'm having a problem with the below query. I'm trying to show the total amount of placements(sales) group by each month. I also want to show how much of the placements(sales) have been invoiced again groped by each month. The problem I'm having is some placements(sales) have more two invoices linked to them so the placementfee for that placement is being doubled.

How do I write the below code so that it only sums placementfee based on a distinct placementid?

is it possible to but something like this in the where clause -
where p.placementid = distinct/unique

Thanks in advance

Dave,

select
DATENAME(MONTH, p.createdon) AS theMonth,
sum(p.placementfee) as 'Amount Boarded',
count(distinct p.placementid) 'Number of Deals'
,SUM(i.netsum) as 'Amount Invoiced',
SUM(p.placementfee)- SUM(i.netsum)as 'Waiting to invoice'

FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid
left join invoices i on i.invoiceid = pp.invoiceid

WHEREp.createdon >= '20080101'
AND p.createdon < '20150101'

GROUP BY DATENAME(MONTH, p.createdon),
DATEPART(MONTH, p.createdon)

ORDER BY DATEPART(MONTH, p.createdon)

View 4 Replies

Running A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported

Aug 27, 2007

I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)

However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======

On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO

On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO

On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.

On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.

On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.

=======
My resolution that generates the second error message
=======

On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.

View 1 Replies View Related

Can Distributed Query Read Sql Server 2000 From Sql Server 2005?

Nov 29, 2007

if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..

For reference, here's an example of a distrib query that reads excel...

ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]

View 1 Replies View Related

SQL Server 2000 To SQL 2005 Linked Server - Query Problem

Jan 16, 2008

Hello

SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram

Symptoms

Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.

At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.

Please, any ideas?

Working

SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'

Not Working (correctly anyway!)

DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value

View 5 Replies View Related

Error Query Data Through Linked Server , SQL Server 2005

May 27, 2008

Hello,

I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used

The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.

Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?

Thanks,
Simba

View 1 Replies View Related

SQL Server 2000 Query Slowing Down In SQL Server 2005

Aug 14, 2006

Hi everybody,

I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.

Here is the sample:

Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))

Thank you

Steve Gadoury

View 13 Replies View Related

Sql Server 2005 Query Questions

Apr 4, 2006

I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC

View 4 Replies View Related

Complex Sql Server 2005 Query

Nov 8, 2007

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

IDSourceIDSourceCodeSecurityBpriceBpriceSizeApriceApriceSizeImportDate

11sourceASecA100.2299.1212007-11-07 16:24:31.297
22sourceWSecH95.789.432007-11-07 16:24:31.297
33SourceXSecS50.56176.4442007-11-07 16:24:31.297
44SourceQSecZ87.982007-11-07 16:24:31.297
55SourceJSecH100.299.1222007-11-07 16:24:31.297
66SourceKSecU2007-11-07 16:24:31.297
77SourceTSecA50.56387.112007-11-07 16:24:31.297

81sourceASecA100.2699.1222007-11-07 16:26:15.123
92sourceWSecH99.54489.432007-11-07 16:26:15.123
103SourceXSecS50.56219.332007-11-07 16:26:15.123
114SourceQSecZ16.9887.982007-11-07 16:26:15.123
125SourceJSecH100.2199.1222007-11-07 16:26:15.123
136SourceKSecU2007-11-07 16:26:15.123
147SourceTSecA50.56287.1112007-11-07 16:26:15.123

151sourceASecA100.2187.1112007-11-07 16:26:15.123
162sourceWSecH99.6689.4322007-11-07 16:26:15.123
173SourceXSecS50.56219.332007-11-07 16:26:15.123
184SourceQSecZ16.98387.9832007-11-07 16:26:15.123
195SourceJSecH100.2399.1232007-11-07 16:26:15.123
206SourceKSecU2007-11-07 16:26:15.123
217SourceTSecA101.32587.1132007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

SecurityMax_BpriceBprice_SizeBprice_SourceCodeMin_ApriceAprice_SizeAprice_SourceCode

SecA101.325SourceT87.114SourceA, SourceT
SecH100.23SourceJ89.432SourceW
SecS50.562SourceX19.33SourceX
SecZ16.983SourceQ87.983SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCode

View 6 Replies View Related

How To Scheduling A Query In Sql Server 2005

Apr 16, 2008

I want to schedule a query that ll insert a record in a table of the database at every 02:00 am. How should i do that. i am uisng sql server 2005?

Muhammad Saifullah

View 7 Replies View Related

Query Designer In SQL Server 2005

May 9, 2007

I would like to be able to use the Query Designer without having to close it out to test the query.

I seem to recall using Query Designer in 2000 as a stand-alone app outside of Enterprise Manager.

How can I use Query Designer AND have the results show up in the same window?

View 2 Replies View Related

SQL Server 2005 - SELECT Query

Oct 18, 2006

HiI currently have a select query with "Description = 'input from userhere'" which basically returns the associated row containing theDescription field exactly as typed by the user, however, would it bepossible to, if not found, return the closest match? I am usingMicrosoft Visual Studio 2005 with C# as language and Microsoft SQLServer 2005 as database.Regards,Lionel Pinkhard---avast! Antivirus: Outbound message clean.Virus Database (VPS): 0642-0, 2006/10/17Tested on: 2006/10/18 12:46:07 PMavast! - copyright (c) 1988-2006 ALWIL Software.http://www.avast.com

View 4 Replies View Related

Query RowVersion In SQL Server 2005

Feb 6, 2007

Hello all,I am new at SQL Server 2005 and have been reading everything I canfindabout the new optimistic concurrency control mechanisms. As far as Ican tell, the Snapshot Isolation Level is based avoids the use ofshared locks using rowversioning instead.To control rowversions in SQL Server 2000 I was using an extra columnin each table containing a rowversion datatype. What i do with this isfind out if a row was modified. Is it possible in SQL Server 2005 toavoid the use of this extra column, using the new versioning features?What I mean is, if the Snapshot Isolation already manages rowversionsin TempDB, is it possible for me to query those versions using somestored procedures or functions given by SQL Server 2005 system? Whatfunctionalities does SQL Server 2005 gives me for that (anydocumentation pointer would help)?This would allow me to do the exact same processing that i was doingwith the rowversion column in SQL Server 2000 without the need forthat extra column.For all of you my thanks and best wishesSusana Guedes

View 1 Replies View Related

Query Performance - SQL Server 2005

Oct 17, 2007

Hi,

I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).

Can you please suggest me in which way I can increase the performance.

Note: I am using only one table with Primary Key.

Thanks
Dinesh

View 7 Replies View Related

Query Plan In SQL Server 2005 SP2

Aug 14, 2007

Hello,

The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms
CPU = 111141 ms
Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms
CPU = 687 ms
Reads = 5558

Statistics are updated every night (w/Fullscan)
Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?
Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,
Smitha
------------------------------------------------------------------------

QUERY:
select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)

INDEX Definition:
CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

View 10 Replies View Related

Sql Server 2005 Query Analyser

Nov 29, 2006

is there a query analyzer for sql server 2005 as in sql server 2000?

bcoz i downloaded the 180 days evaluation version & installed it .

i have worked with sql server 2000. but there is no query analyzer for 2005 how can i write queries & execute?

of course there is vs 2005 which has come with sql server 2005 evaluation copy.

plz help!

bcoz i really want to work with sql server 2005

thanks in advance for ur replies

View 8 Replies View Related

Help Required Please + SQL Server 2005 + LIKE Query

Jan 10, 2008

Hi Guys

I am in need of some assistance please.

I am using SQL Server 2005 and C#(winforms).

For example if I have a winform with a textbox and search button. If I type in ABCD1234. I won't know what the data is coming into the X Table. So there will be hundreds of description data in the description field in X table.

So in the X Table, there are ABCD1234,ABCD_1234,ABCD/1234,ABCD 1234

But I will only pick this 'ABCD1234' record up, and the rest won't be picked. They are variations of the same description. It needs to pick also ABCD_1234,ABCD/1234,ABCD 1234

If also type in 5678, in another column like code in X Table. It would have 005678,05678,56780. But I will only pick this '5678' record up, and the rest won't be picked. They are variations of the same code.
It needs to pick also 005678,05678,56780

I have tried LIKE, FREETEXT and CONTAINS.

But another idea is a table of alternate descriptions for those that you know about and look those up and search for them all. But how can that be done?

Any ideas guys please? with examples if possible.

Thanks Newbie.....

View 4 Replies View Related

SQL Server 2005 And Query Notification

Jun 9, 2006

I have been testing SQLDependency and I have a two questions;

1) When using a stored procedure to run the dependency query, using a "clean" procedure (containing nothing but a select statement), works fine. Adding try..catch (which is a part of our coding standards), results in the subscription firing immediately because of an invalid query. Is this by design? If so, how can I implement error handling.

2) How does SQLDependency handle SQL Server restarts?
I see two options:
a) The subscription is fired listing a server restart message in the related service broker queue, but as far as I can tell, SQLDependency has no way of handling these messages given the connection has been broken. (Establishing a new connection and dependency won��t read the said messages).
b) The SQLDependency listener on the client raises an error for the connection being terminated. Can I relay on this event to recreate a connection and dependency?

Help will be appriciated
Boaz

View 3 Replies View Related

A Query In SQL Server 2000 And Sql Server 2005

May 5, 2008

Hi all,

I need your help.
I have 2 different databases. One of them is in the SQL Server 2000 and the other on is in the SQL Server 2005. I need to make a query out of these 2 databases.
Is there any way that I can do this?

Thanks,

View 12 Replies View Related

SQL Server 2005 Query/trigger/function (whatever It Is That I Need)

Dec 8, 2006

Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
table 1 = stock table
table 2 = Purchase order table
in table 2 there are line items that have ordered quantities for parts that we have ordered
in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1

quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this

table 2
poNumber             partNumber                 quantity
1                             516                             1
2                             516                             12
3                             516                             4

table 1
partNumber            inStock              onOrder
516                          0                        17(this is what i am trying to figure out how to compute)

any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.

View 3 Replies View Related

How To Break Insert Query In Sql Server 2005 In To Two...

Jan 15, 2008

I am inserting a lengthy query through the source code here it contains one big record called description which has 3 paragraph length.....its showing error also.Is there any way to break the query in to two and execute?

View 6 Replies View Related

SQL Server 2005 Query With Date Function

Feb 5, 2008

I wrote a function and a SQL to get the 3 columns Date,Total Orders
& Amount, for dates between Date Started and Date Completed if I
pass different Dates in the SQL I get the correct result but if I pass
same dates then I don't get the result I am looking for .For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.But I give same dates for Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.Some how I could not trace what could be the error in my SQL /Function.I appreciate if I could get some work around for this.Thanks! Function:create function dbo.CreateDateList(@start datetime, @end datetime)returns @t table ( [date] datetime )asbegin if @start is null or @end is null return if @start > @end return set @start = convert(datetime, convert(varchar(10), @start, 120), 120) set @end = convert(datetime, convert(varchar(10), @end, 120), 120) while @start < @end begin insert into @t ( [date] ) values (@start) set @start = dateadd(day, 1, @start) end returnend ---------SELECT qUERY---------- SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101) Union SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID Order by Date

View 6 Replies View Related

Online Query Tool SQL Server 2005

Feb 18, 2008

Hi,I was wondering if someone could help advise me.Basically i have a client who wants to upgrade from a desktop msaccess solution to a web based system using MS SQL Server 2005.Basically their main concern is in losing the ability to write ad-hocqueries using the MS Access query manager. I can obviously writestatic reports etc but whats the best way to give them someflexibility ??Can anyone advise me if there is any product that can do this for SQLServer 2005 (which im sure there is) but will work in a webenvironment.Or does anyone have any helpful ideas ???all help / advice greatly appreciatedCG

View 1 Replies View Related

Incorrect Results With T-sql Query In SQL Server 2005

Apr 18, 2006

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
------------
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
    inner join #projects on #projects.projectid = tblprojects.projectid
     Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
          and tblProjects.projectID in (
               select projectid
               from tblMilestones
               where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
                    and enabled = 1 )
------------
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
    and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
    and enabled = 1 "

2) Changing " Select distinct tblProjects.ProjectID"
To   " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |    |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
            |    |         |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
            |    |         |    |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
            |    |         |    |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
            |    |         |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
            |    |         |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
            |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
                      |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)

View 6 Replies View Related

Problem With Query Using SQL Server 2005 September CTP

Oct 27, 2005

To SQL Server 2005 Team: This query works fine on SQL Server 2000: select top 10 aename as EscrowOfficer,aomon,aoday,aoyeary2k, aemon,aeday,aeyeary2k,aprice,aescr#,aprpt#,abra#,arecmn, arecdy,arecyry2k,Source,absct,asrep1,asrep2,asrep3,asrep4, asrep5,arepc1,arepc2,arepc3,arepc4,arepc5,atname as TitleOfficer, acust#,aagnts,aagntb,aempl#,titccd,titord,atiu,atmon,atday,atyeary2k, atoff#,acnty#,areals,arealb from SVRVMLAPORTALDB.RPStaging.dbo.inform where aescr# in ( select aescr# from SVRVMLAPORTALDB.RPStaging.dbo.inform where aoyeary2k >= 2004 and aomon between 1 and 12 and aoday between 1 and 31 and abra# <> 99) or aescr# in ( select inform.aescr# from SVRVMLAPORTALDB.RPStaging.dbo.inform as inform inner join SVRVMLAPORTALDB.RPStaging.dbo.invoicehdr as invoicehdr on inform.aescr# = invoicehdr.aescr# where invoicehdr.ipostdatey2k >= 20040101 and inform.aoyeary2k < 2004 and inform.abra# <> 99) The result set I get back (abbreviated for clarity) on SQL Server 2000 is: 2 13 2004 3 15 �� MARGIE LLARINAS 9 10 2002 9 11 �� Leslie Azevedo 10 28 2004 12 2 �� Nesha Castelo 5 23 2005 8 22 �� Nesha Castelo 5 23 2005 7 29 �� Jill Stonebraker 9 21 2001 12 10 �� Jill Stonebraker 9 11 2002 10 31 �� Jill Stonebraker 10 31 2002 1 2 �� Jill Stonebraker 11 22 2002 1 29 �� Jill Stonebraker 12 19 2002 2 6 ��

View 9 Replies View Related

Upgrade Query From Access To Sql Server 2005

Apr 6, 2006

I'm currently upgrading from Access to Sql Server, and I have a broken query that I cannot find a fix for.

The original query:

SELECT DISTINCT DownloadedNames.*FROM DownloadedSkims INNER JOIN (DownloadedNames INNER JOIN DownloadedInfo ON DownloadedNames.DNID = DownloadedInfo.DIDnID) ON DownloadedSkims.DM_ID = DownloadedInfo.DI_DM_IDWHERE DM_ID=23 AND (dWeek BETWEEN '6/26/2006' AND '9/18/2006')ORDER BY DIID, dWeek

I get the error "Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified" when I test the query.

If anyone knows what changes I can make to the query, you'd really be helping me out.

Chris

View 1 Replies View Related

SQL Server 2005 - How To Convert A Query Into A Table?

Sep 11, 2007

I'm going through and restructuring a database scheme and was wondering if, after I make a query (view), how can I convert that into a table?

Thanks

View 4 Replies View Related

How To Replace Sysindexes And Sysobjects In The Query For Sql Server 2005?

Feb 26, 2008

hi all,i was using the system tables namely sysindexes and sysobjects in sql server 2000. But now sql server 2005 is using instead of sql server 2000. Since am using sysindexes and sysobjects, too much time is taking for the execution in sql server 2005. So I need to change the query suitable for sql server 2005.I have read in msdn that the system tables are replaced with corresponding catalog views in sql server 2005. The catelog view for sysobjects is sys.objects. plz check the link http://technet.microsoft.com/en-us/library/ms187997.aspxCould anyone please tell me how to replace the sysindexes and sysobjects in the query without rewriting the query too much. If I can replacesysobjects with just sys.objects, then it will be very helpful. Is it possible? And what about sysindexes. Any help anyone could give would be greatly appreciated!

View 8 Replies View Related

Scheduling A Query To Be Executed At Given Time In SQL Server 2005

Apr 4, 2007

Hi friend!

I want to schedule a query or procedure running at given time regularly eg: at 12AM daily. Please tell me how to do that in sql server 2005.

We use SQL server 2005 developer edition.

Thanks in advance.

View 1 Replies View Related

SQL Server 2005: Full Text Query Failed

Sep 3, 2007

Hi guys,

I am getting a really weird error message when executing a full-text query on SQL server 2005:

-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider."
-------------------------

Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.

The query is not particular complex:

SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID

It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.

Has anybody come across this error before? Any ideas on how I could fix it?

View 14 Replies View Related

Query Tracer Tool For SQL Server Express 2005

Dec 3, 2007

Hi guys,
I am using DLinq with SQL Server Express 2005 and I need to install some kind of user interface which shows me a trace of the SQL statements sent. I would like to see them in real time (I won't like to inspect a log after :-).
I remember that there was something similar in SQL Server 2000 full edition, but I requiere the same functionality in the Express version now.

Could you tell me if there is some tool to do that?
Thanks in advance,
Erich.

View 1 Replies View Related

Query Performance Difference Between Sql Server 2005 And 2000

Aug 1, 2007

Hi,

I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.

The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).

I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.

Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.

Cheers,

Chris

View 4 Replies View Related

Preventing [sa ] To Login Via Query Analyzer [SQL Server 2005]

May 11, 2007

I am implementing the security and add different windows domain group and also assign then appropriate rights and permission.

No i want that if some knows the "sa" password and want to connect to sql server 2005 via query analyzer then message should display to user that u can not login with sa login while using SQL Server Query analyzer.

How i can identify and can display the message?

Thanks a lot in advance.

View 1 Replies View Related

Transact SQL :: Create PIVOT Query In Server 2005

Jul 24, 2015

I have a query which I want to convert It PIVOT query

SELECT     Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROM         CashSalesDetail INNER JOIN                      CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOIN                      Parties ON CashSales.PartyID = Parties.PartyIDWHERE     (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName

following is my requirement after summing up qty of each area

ProductName      area a         area b       area c
abc                          10                0
20
def                           1
4               2
ghi                           5
3               10
jkl                             7
15              3

Note: numeric values are Quantity of each product in each area

View 15 Replies View Related