MS SQL, Using SP in Select

Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
PillaiOriginally posted by mbpilla
Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
Pillai

You can not call a stored procedure in a select query but insted if u just call the stored proc as
exec mtest instead of the select query and u get the same output.|||Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai|||Originally posted by mbpilla
Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai
for more information refer to BOL under trusted connections.
From BOL:
A login ID only enables you to connect to an instance of SQL Server. Permissions within specific databases are controlled by user accounts. The database administrator maps your login account to a user account in any database you are authorized to access.
Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each has a different class of login ID.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows NT and Windows 2000 group and user accounts of the client that opened the connection. SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.That is a trusted connection.|||yup it is possible to use a sproc in a select statement using open query ... or opendatasource ...
------------------------
SELECT *
FROM OPENQUERY(SvrName, 'exec sproc')
------------------------

but i believe you will have to add a linked server to your own server.

MS SQL This feature has not been implemented yet

Hi, I'm a Delphi programmer, and using an ADO Dataset, I'm trying to select some data from a database. However, when I set it up using parameters, I get an error in Delphi that this Feature has not been implemented yet. Can anyone help me configure the MS SQL database, so I can run SQL queries with parameters?which version of Delphi and SQL are you using. I use D6 with SQL 2k and have no problems (well, almost none) using an ADO dataset. What is your sql statement and how did you define your parameters?|||I am using Delphi version 7 and MS SQL 2000. Here is the Commandtext from my ADODataset:

select count(*) from CallDetail calldetail where i3timestampgmt between :BegTime and :EndTime and initiateddate between :BegTime2 and :EndTime2 and LocalUserId = :User

If I plug in actual values in the SQL instead of parameters, the script runs just fine. Then here is how I defined the parameters:

dmReports.ADODataSet1.Parameters.ParamByName('BegT ime').Value := FormatDateTime('mm/dd/yyyy hh:nn', BegDate);
dmReports.ADODataSet1.Parameters.ParamByName('EndT ime').Value := FormatDateTime('mm/dd/yyyy hh:nn', EndDate + 1);
dmReports.ADODataSet1.Parameters.ParamByName('BegT ime2').Value := FormatDateTime('mm/dd/yyyy hh:nn', BegDate);
dmReports.ADODataSet1.Parameters.ParamByName('EndT ime2').Value := FormatDateTime('mm/dd/yyyy hh:nn', EndDate + 1);
dmReports.ADODataSet1.Parameters.ParamByName('User ').Value := workgroupmembers[i];
dmReports.ADODataSet1.Active := True;
ShowMessage(dmReports.ADODataSet1COLUMN1.AsString) ;

At the beginning of the procedure I defined them like this:

BegTime, EndTime, BegTime2, EndTime2, User: Variant;|||your declarations seem ok at first sight. My guess would be to change the parameter named 'user' to another name because this is keyword used by sql server.|||I tried changing User to Operator, and it is still giving the exact same error. I am guessing that the MS SQL is not configured correctly, and maybe under the current configuration it can't handle parameters.|||I don't see how it could be configured to not accept parametrized queries from a client application. What is the exact error message you get? If you click on the parameters property in the object inspector, does it display all your parameters correctly?|||In the object inspector all the parameters are displayed correctly. Here is the message that I receive

--------
Debugger Exception Notification
--------
Project report.exe raised exception class EOleException with message '[Microsoft][ODBC SQL Server Driver]Optional feature not implemented'. Process stopped. Use Step or Run to continue.
--------
OK Help
--------|||There is nothing to configure in sql server to allow it to accept queries.
Depends on what is being sent to the server by the app as to whether it will work.
Try using the sql server profiler to trace what is being sent.

Check the delphi documentation and the provider you are using as the error is comming probably comming from one of these|||see http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q214/4/59.asp&NoWebContent=1 for more info.|||I just ran the SQL Server Profiler from the Server, and that query is not even showing up there. I know that it connects however, because when I run a query without the parameters, it does show up.

Also, I beleive that the Microsoft website is talking about Visual Basic, instead of Delphi.|||Thanks for all your help jora and nigellrivett. I couldn't get it working, so I decided that instead of using parameters, I'd set the commandtext property directly with the right values in a procedure in Delphi.sql

MS SQL Server XML Query Help

Hi all--

I am trying to execute the following query in MS SQL Server 2000.

SELECT 1 AS Tag, NULL as parent,
'x' AS [A!1!B],
NULL AS [C!48],
NULL AS [RepurchaseDetails!49]

UNION ALL

SELECT 48, 1,
NULL, 'y', NULL

UNION ALL

SELECT 49, 48,
NULL, NULL, 'z'

ORDER BY
TAG ASC

FOR XML EXPLICIT

I get the error:

Server: Msg 6806, Level 16, State 2, Line 1
Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.

However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
the query works just fine. It however still fails for
RepurchaseDetailz, or RepurchaseDetailzz

Any ideas?"Jay" <xml_@.hotmail.com> wrote in message
news:4e7ac2ff.0402260651.7c8b888b@.posting.google.c om...
> Hi all--
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1,
> NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48,
> NULL, NULL, 'z'
> ORDER BY
> TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz
> Any ideas?

You may want to post this in microsoft.public.sqlserver.xml, as you'll
probably get a better response there.

Simon|||Jay (xml_@.hotmail.com) writes:
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1, NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48, NULL, NULL, 'z'
> ORDER BY TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz

Not only that. Shortening it to RepurchaseDetail also works.

I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
bug long way.

The hour is a little late for me now, but I will look into this
again tomorrow. I guess that you need to use a workaround.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I posted this same question on microsoft.public.sqlserver.xml
and they are stumped. I have tried this on different versions of SQL
server 2000 with the same results.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949C6D4ED1DBYazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > I am trying to execute the following query in MS SQL Server 2000.
> > SELECT 1 AS Tag, NULL as parent,
> > 'x' AS [A!1!B],
> > NULL AS [C!48],
> > NULL AS [RepurchaseDetails!49]
> > UNION ALL
> > SELECT 48, 1, NULL, 'y', NULL
> > UNION ALL
> > SELECT 49, 48, NULL, NULL, 'z'
> > ORDER BY TAG ASC
> > FOR XML EXPLICIT
> > I get the error:
> > Server: Msg 6806, Level 16, State 2, Line 1
> > Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> > However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> > the query works just fine. It however still fails for
> > RepurchaseDetailz, or RepurchaseDetailzz
> Not only that. Shortening it to RepurchaseDetail also works.
> I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
> bug long way.
> The hour is a little late for me now, but I will look into this
> again tomorrow. I guess that you need to use a workaround.|||Jay (xml_@.hotmail.com) writes:
> Thanks, I posted this same question on microsoft.public.sqlserver.xml
> and they are stumped. I have tried this on different versions of SQL
> server 2000 with the same results.

I've now tested the query on Yukon Beta 1, where it works as it should.

I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
I get the same error.

I will report this to Microsoft, and I would expect them to file a bug
unless this is a known problem.

However, if you are in need of a fix, and cannot use the workaround of
shortening the element name, or making it longer, then need to open a
case with Microsoft to pursue this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all your help, Microsoft has now confirmed to me that this
is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
and is now being considered for a future service pack for 2000. I'll
just have to work around it for now.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949CEECB08C61Yazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > Thanks, I posted this same question on microsoft.public.sqlserver.xml
> > and they are stumped. I have tried this on different versions of SQL
> > server 2000 with the same results.
> I've now tested the query on Yukon Beta 1, where it works as it should.
> I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
> I get the same error.
> I will report this to Microsoft, and I would expect them to file a bug
> unless this is a known problem.
> However, if you are in need of a fix, and cannot use the workaround of
> shortening the element name, or making it longer, then need to open a
> case with Microsoft to pursue this.|||Jay (xml_@.hotmail.com) writes:
> Thanks for all your help, Microsoft has now confirmed to me that this
> is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
> and is now being considered for a future service pack for 2000. I'll
> just have to work around it for now.

Thanks for reporting back! If this is known bug, then I don't need to
bug (sic!) my contacts at MS with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

MS SQL Server equivalent query

I have this query I use on MySql, and I'm trying to translate that to make it work on MS SQL Server.

INSERT INTO totable (col1,col2,col3)

(SELECT col1,col2,col3 FROM fromtable AS x)

ON DUPLICATE KEY UPDATE col1=x.col1,col2=x.col2,col3=x.col3;

Basically it inserts all rows from one table into another, and if you get a unique key constraint, it updates that rows instead. So far I haven't found any equivalent for MS SQL Server Anyone have a suggestion?

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

WesleyB wrote:

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Well...I don't have that amount of time Anyway...to elaborate a little bit...I have a working solution, but I'm trying to make it better. Currently this is done from a c++ program in a cursor loop. I send a select, insert and update statement to a function, selects a dataset and for each row in that dataset I'll first try to update, and if the update fails (returns no affected rows), I execute the insert statement. Needless to say that it isn't very efficient, but I can be flexible in terms of prepare temporary tables, send in help queries etc. I just can't figure out a better way than the current scenario...I just think that it really have to be a better way to do this.

|||

Rather than use a CURSOR, I would load the data in question into a staging table, using a table variable or #temp table, then with a single update statement, update all pertinent rows in the production table, a second query to delete those rows from the staging table, and then a third query to add the remainder to the production table.

Depending upon the number of rows, it is likely to be quite a bit more efficient and 'faster'.

|||

Why not doing first the update then the insert ?

Update SomeTable
SET
col1=x.col1
col2=x.col2,
col3=x.col3
From SomeTable
INNER JOIN fromtable x
ON --Place your join conditions here

INSERT INTO totable (col1,col2,col3)

(
SELECT col1,col2,col3
FROM fromtable AS x
WHERE NOT EXISTS
(
Select * from totable T
Where t.SomeColumn = x.SomeColumn --These should be your join conditions
)
)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!Hi
"prudon@.inbox.ru" wrote:
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegroups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback

MS SQL query, whats the default order the rows returned are sorted by?

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's inIf you want an order, specify the order with the ORDER BY clause. If you are willing to take whatever order the optimizer decides on, omit the ORDER BY clause.

-PatP|||hmm this is weird.. in the access database if i select it, they're returned in the order the rows were inserted.. but after importing that database into ms sql, and selecting that table, the order isn't the same row i got when i ran the query in the access db|||Jet, the default database engine used by MS-Access is rather "simple-minded" when it comes to query optimization. MS-SQL has a much more powerful optimizer, which is a two-edged sword... The MS-SQL optimizer is able to easily process queries that Jet would never complete, but it does that processing in a very different way. As an interesting side effect, it also means that unless you specify an order in your query, there is no guarantee that running the exact same query on the same box will ever return the rows in the same order, even though it often will return them in a consistant order.

-PatP|||Ahhh ok i see what you're saying.

In my query, i had a left join statement in there.. i took that out and used a subquery instead of the join and it returns the rows fine now in the order they were inserted. Looks like the join caused the problem.|||No, the JOIN did not cause the problem. The absence of an ORDER BY statement caused the problem.|||The problem is actually a lot simpler than "the join caused the problem". If you want an order, specify it with the ORDER BY clause. If you don't care about an order and are willing to accept the order determined by the optimizer at the moment, omit the ORDER BY clause.

-PatP

MS SQL Query statement.

Hi All,
Problem statement:
Group 2 tables.
B(B_ID,B_DES)
C(C_ID,B_ID,C_BY)
select B.*,C.*
from b bx inner join cx on bx.b_id=cx.b_id
group by B column,,C column
Output:
B_ID column | C_BY
1 we
1 xy
2 DF
Above result is not what i desired, instead, i needs:
1 we
xy
2 DF
How to solve it'
thank you in advance..Help will be appreciated.
Best regards,
Gin Lye KhorRepresent the results in a report engine using Group & Detail bands?
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9B5D30B5-ADFB-404D-97D1-5CC5DB252BC4@.microsoft.com...
> Hi All,
> Problem statement:
> Group 2 tables.
> B(B_ID,B_DES)
> C(C_ID,B_ID,C_BY)
> select B.*,C.*
> from b bx inner join cx on bx.b_id=cx.b_id
> group by B column,,C column
> Output:
> B_ID column | C_BY
> 1 we
> 1 xy
> 2 DF
>
> Above result is not what i desired, instead, i needs:
> 1 we
> xy
> 2 DF
> How to solve it'
> thank you in advance..Help will be appreciated.
> Best regards,
> Gin Lye Khor
>
>

MS SQL passing in more than 8000 characters

if a user chooses to request a lot of customers to report on say from a multi select listbox - what is the best way to pass this list to my stored proc? Looking for suggestions.

thanks,

Can you post some more details of what you want to do and how you have you are trying to achieve it right now? From the question you've posted it looks like you want to send so much details to your sproc.

|||

hi,

you can use the ntext as a datatype for storing huge values in the sql server 2005.

The value changes dynamically ,so we can use

sqlcmd.paramters.add("paramter Name").value= @.value

By using the above syntax we can give input whatever the value is. but do remeber make the datatype of the field as ntext.

cheeers mate...

VIjay

|||

i apologize for being to vague, it's a sql 2000 db and if all options were selected and thrown into a column the field would exceed the 8000 characters, this has to be saved so the user could re-run the same row if they choose to, so as the users build these requests - they are saved to a db, now maybe i could write the selected lists to an xml file and then pass that into my stored proc paramaters, but i've never done anything like that, possibly save the xml file with a relation to the row and then when the users calls that tracking again - it knows to grab that xml file and feed it to the stored proc? possibly you have seen or done this before.

thanks,

Jeff

MS SQL Joins

Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

Store Procedure:

--Create Procedure dbo.IMS_Donation

--AS

Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO

SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO

SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

GO

SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE

Error Message:

(845 row(s) affected)

(844 row(s) affected)

(396 row(s) affected)

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.

First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).

If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.

If that doesn't help, buy more disk!

-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.

MS SQL Deadlock but no SQL transactions exists - Help

The deadlock victim occurred executing the line

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)
NotesDisplay is the .aspx page

You can clearly see that no transaction was in use, so the deadlock should not have occurred as far as I am concerned (3rd time I seen something like this).

This happened on MS SQL 2005 SP1.

The other part of this deadlock was caused by a delete statement (which did have a transaction) deleting rows that may have formed part of this view but that should only resulted in a normal lock, not a deadlock.

I have included the complete code that was executed along with the error message for reference.
Can anyone explain this?

PartialClass NotesDisplay

Inherits System.Web.UI.Page

Private mUserInfoAs Database.UserInfo

Private mDbAs Database

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim RegOccIDAsInteger =CInt(Request.QueryString(("RegOccID")))

Dim DT3As Data.DataTable

Main.GetSession(Me.Page, mUserInfo, mDb)

DT3 = mDb.MatchingRegs(RegOccID)

PublicFunction MatchingRegs(ByVal RegOccIDAsInteger)As DataTable

Dim DT3As Data.DataTable

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked where RegOccID = " & RegOccID)

If DT3.Rows.Count = 0Then

ThrowNew Exception("Reg occ not found")

EndIf

Dim StartsAsDate

Dim EndsAsDate

Dim WhereAsString

With DT3.Rows(0)

Starts = .Item("Starts")

Ends = .Item("Ends")

If .Item("Ends")Is DBNull.Value =FalseAnd .Item("RoomID")Is DBNull.Value =FalseAnd .Item("RegStaff")Is DBNull.Value =FalseThen

' Posible merge with other regs

Dim cmdAsNew Data.SqlClient.SqlCommand

cmd.Parameters.AddWithValue("@.Starts", Starts)

cmd.Parameters.AddWithValue("@.Ends", Ends)

Where =" Where RoomID = " &CInt(.Item("RoomID")) &" AND Starts = @.Starts AND Ends = @.Ends AND RegStaff = " &CInt(.Item("RegStaff")) &" AND RoomID2 "

If .Item("RoomID2")Is DBNull.ValueThen

Where &=" is null"

Else

Where &=" = " &CInt(.Item("RoomID2"))

EndIf

DT3.Rows.Clear()

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)

EndIf

Return DT3

EndWith

EndFunction

PublicFunction GetSQLTable(ByVal SQLStringAsString,OptionalByRef CmdAs SqlCommand =Nothing)As DataTable

Dim myConnAs SqlConnection

Dim LocalConAsBoolean =False

If CmdIsNotNothingAndAlso Cmd.ConnectionIsNotNothingThen

myConn = Cmd.Connection

If SQLString =""Then

SQLString = Cmd.CommandText

EndIf

Else

myConn = DBOpenSQLConnection(SqlConEnum.Timetables)

LocalCon =True

EndIf

Try

GetSQLTable = GetSQLTableInternal(SQLString, myConn, Cmd)

Catch exAs Exception

Throw

Finally

If LocalConThen

DBCloseSQLConnection(myConn)

EndIf

EndTry

EndFunction

PrivateFunction GetSQLTableInternal(ByVal SQLStringAsString,ByRef myConnAs SqlConnection,OptionalByRef CmdAs SqlCommand =Nothing,OptionalByRef TransAs SqlTransaction =Nothing)As DataTable

Dim mySQLCommandAs SqlCommand

Dim mySQLAdaptorAs SqlDataAdapter

If CmdIsNothingThen

mySQLCommand =New SqlCommand

Else

mySQLCommand = Cmd

EndIf

mySQLCommand.Transaction = Trans

mySQLAdaptor =New SqlDataAdapter(mySQLCommand)

mySQLCommand.Connection = myConn

mySQLCommand.CommandText = SQLString

GetSQLTableInternal =New DataTable

mySQLAdaptor.Fill(GetSQLTableInternal)

EndFunction

Screen shot of SQL profiler deadlock information (showing lock types etc)
http://img299.imageshack.us/img299/9625/deadlockmb9.jpg

System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.HasMoreRows()

at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

at System.Data.SqlClient.SqlDataReader.Read()

at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)

at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)

at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at Database.GetSQLTableInternal(String SQLString, SqlConnection& myConn, SqlCommand& Cmd, SqlTransaction& Trans)

at Database.GetSQLTable(String SQLString, SqlCommand& Cmd)

at Database.MatchingRegs(Int32 RegOccID)

at NotesDisplay.Page_Load(Object sender, EventArgs e)

at System.Web.UI.Control.OnLoad(EventArgs e)

at System.Web.UI.Control.LoadRecursive()

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

********************

at System.Web.UI.Page.HandleError(Exception e)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

at System.Web.UI.Page.ProcessRequest()

at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)

at System.Web.UI.Page.ProcessRequest(HttpContext context)

at ASP.notesdisplay_aspx.ProcessRequest(HttpContext context)

at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The answer can be found here

http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/a05e7d7f4eabf61e/337c7c6d75c38452?lnk=st&q=ms+sql+server+2005+deadlock+no+transaction&rnum=25&hl=en#337c7c6d75c38452

MS SQL Backup Schedule

When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
and schedule. Then I select DAILY and a Time, click apply, and then OK. Whe
n
I go back in to review the setting it is back the way it was with no
schedule.
What am I doing wrong ?I'm unable to reproduce this on my sql2k+sp3a. Perhaps, you want to update
to the latest service pack.
http://microsoft.com/sql
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite
box
> and schedule. Then I select DAILY and a Time, click apply, and then OK.
When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||That dialog only creates an SQL Server agent job. Check in SQL Server Agent,
Jobs and you will see
you job(s) there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite b
ox
> and schedule. Then I select DAILY and a Time, click apply, and then OK. W
hen
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||Thank you Tibor, where can I see the scheduled jobs ?
gerrym
"Tibor Karaszi" wrote:

> That dialog only creates an SQL Server agent job. Check in SQL Server Agen
t, Jobs and you will see
> you job(s) there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
> news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
>
>|||Enterprise Manager, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:7DC355DB-A511-4B91-A46D-17B309D4879C@.microsoft.com...[vbcol=seagreen]
> Thank you Tibor, where can I see the scheduled jobs ?
> gerrym
> "Tibor Karaszi" wrote:
>
see[vbcol=seagreen]

MS SQL Backup Schedule

When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
and schedule. Then I select DAILY and a Time, click apply, and then OK. When
I go back in to review the setting it is back the way it was with no
schedule.
What am I doing wrong ?
I'm unable to reproduce this on my sql2k+sp3a. Perhaps, you want to update
to the latest service pack.
http://microsoft.com/sql
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite
box
> and schedule. Then I select DAILY and a Time, click apply, and then OK.
When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?
|||That dialog only creates an SQL Server agent job. Check in SQL Server Agent, Jobs and you will see
you job(s) there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
> and schedule. Then I select DAILY and a Time, click apply, and then OK. When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?
|||Thank you Tibor, where can I see the scheduled jobs ?
gerrym
"Tibor Karaszi" wrote:

> That dialog only creates an SQL Server agent job. Check in SQL Server Agent, Jobs and you will see
> you job(s) there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
> news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
>
>
|||Enterprise Manager, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:7DC355DB-A511-4B91-A46D-17B309D4879C@.microsoft.com...[vbcol=seagreen]
> Thank you Tibor, where can I see the scheduled jobs ?
> gerrym
> "Tibor Karaszi" wrote:
see[vbcol=seagreen]

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
--
Customer_tbl
--
CustomerID
AccountNo
Name
EmailAddress
--
Order_tbl
--
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
--
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 2000 string.replace

Hello
with the following query :
SELECT words FROM T1
i get :
A,B,C
how can i get
A > B > C
something like String.Replace(words , ',' , ' > ')
thank youHi,

you can replace SELECT words by SELECT replace(words,',','>')
your query become:

SELECT replace(words,',','>') words FROM T1

goodluck!|||wonderfull, and so easy :-)

thanks a lot|||hi`, sometime everything become simple if we think it simple :)

MS SQL 2000 - SQL Select with XPATH Where Clause

In MS SQL Server 2000, is there an easy way to use an XPATH statement in a
WHERE clause? I have a table with a TEXT column that contains XML. I would
like to query this table and select any rows where the XML data field meets
my XPATH criteria.
I'm attempting to avoid using cursors and sp_xml_preparedocument on each row
of data in the table, but with SQL Server 2000, that may be my only option.
Any help would be appreciated!Bryan (Bryan@.discussions.microsoft.com) writes:
> In MS SQL Server 2000, is there an easy way to use an XPATH statement in
> a WHERE clause?
Yeah, upgrade the instance to SQL 2005. :-)

> I have a table with a TEXT column that contains XML. I
> would like to query this table and select any rows where the XML data
> field meets my XPATH criteria.
> I'm attempting to avoid using cursors and sp_xml_preparedocument on each
> row of data in the table, but with SQL Server 2000, that may be my only
> option.
OPENXML is on the only XML support that SQL 2000 offers. I agree that a
cursor and preparedocument for each is not appealing. The other alternative
would be to bring the data to the client, but is probably even less
appealing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

MS SQL (min function To min2ndlowest)

Hi,
Below stated is my original query from my database but for the sake ofsimplicity , I have used the pubs database to convey my thoughts.

select p.pub_name,min(t.price),max(t.price) from publishers p,titles t
where
p.pub_id=t.pub_id
group by p.pub_name

Instead of the min(Price), I would like to get the min2ndLowest(Price).
Is there away to manipulate the Min function so as to change it . Or is itsomehow possible to rewrite another function like Min2ndLowest() toeasily solve this situation.I will be more happy to solve via thisroute as I later have to solve other queries like Max2ndHighest() andso forth.
Or is it only possible thru some serious query design

Thanks for help guys..

My Original Query

select b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported,min(t.begintime),max(t.begintime)
from
batches b, queues q,tasks t
where
b.queueid=q.queueid
and
b.batchid = t.batchid
group by b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported

--SELECT MIN(b.PRICE1) AS Max2ndHighest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1 DESC) AS b

--SELECT MAX(b.PRICE1) As MIN2ndLowest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1) AS b

--table is from this thread with more data:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619492&SiteID=1

create

table #x

(

ProductID

int,

MarketID

int,

Date

datetime,

PRICE1

decimal(9,2),

PRICE2

decimal(9,2),

PRICE3

decimal(9,2)

)

insert

#x

select

1, 2,'1/01/2006', 2.78, 3.4, 2.97unionall

select

1, 2,'2/01/2006', 2.51, 3.5,NULLunionall

select

1, 2,'3/01/2006',NULL, 3.6,NULLunionall

select

1, 2,'4/01/2006',NULL, 3.55,NULLunionall

select

2, 4,'1/01/2006', 3.44, 1.23, 4.33unionall

select

2, 4,'2/01/2006', 3.55, 1.22, 4.22unionall

select

2, 4,'3/01/2006', 3.13, 1.51, 4.54unionall

select

2, 4,'4/01/2006',NULL, 1.50, 4.25

SELECT b.ProductID, b.MarketID,

(

SELECTMIN(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDORDERBY a.PRICE2DESC)AS c)AS Max2ndHighest,

(

SELECTMAX(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDorderby a.PRICE2)AS c)AS MIN2ndLowest

FROM

#xAS b

GROUP

BY b.ProductID, b.MarketID

drop

table #x|||

Insert this into the joins area of your original query:

LEFT JOIN ({a complete copy of your original query}) t1 ON ({field1 from original query}=t1.{field1 from original query} AND {field2}=t1.{field2} ... AND {field to be min-ed from original query}={min-ed field result from subquery})

Then add

WHERE t1.{field1} IS NULL to the where clause of your original query.

For example:

SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1

becomes

SELECT field1,MIN(field2) AS MinField2
FROM table1
LEFT JOIN (
SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1) t1 ON (table1.field1=t1.field1 AND table1.field2=t1.MinField2)
WHERE field3='something'AND t1.field1 IS NULL
GROUP BY field1