Pulling Information About Stored Procedures Query
Apr 28, 2008
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id
AND procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id
View 7 Replies
ADVERTISEMENT
Jul 20, 2005
Hi ,I am looking for meta-information about the return recordset of astored-procedure. The procedure returns a resultset that contains columns ofmore tables joined together. In all tables, I use, there is aRecord-Creation-Timestamp-Attribute. When joining two or more tables theseattribute-names appear in ther resultset buti found no way to distinguish them.I there a way to retrieve meta-information about the result-recordset ofsuch a stored-procedure?here some details:the tables=======CREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[FK_Tab2ID] [int] NULL ,[CreatedAt] [datetime] NULL )CREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (35) NULL ,[CreatedAt] [datetime] NULL)the stored-procedure:===============CREATE PROCEDURE dbo.sp_Test_RetrieveData@ID intASSET NOCOUNT ONselect * from table1 inner join table2 on (FK_Tab2ID = Table2ID)where table1.ID = @IDGOthe resultset:==========Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt(the attribute CreatedAt appears twice.)-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
View 2 Replies
View Related
Dec 16, 2005
Hello,
I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers.
I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail").
I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
When I try to run my application I'm getting an error which reads:
Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated.
The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong!
The code I'm using for my stored procedure is as follows:
ALTER PROCEDURE AddCustomer
(
@CustomerID int,
@Email nvarchar(50),
@Password nvarchar(MAX),
@Name nvarchar(50),
@Address1 nvarchar(50),
@Address2 nvarchar(50),
@Address3 nvarchar(50),
@City nvarchar(50),
@County nvarchar(50),
@PostCode nvarchar(50)
)
AS
INSERT INTO Customers
(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode)
VALUES
(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)
I'd be really grateful if anyone could help me out with this.
Thanks in advance,
Luke
p.s. just incase it helps, here's my createaccount.aspx page:
<%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %>
<%-- Add content controls here --%>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<span style="text-decoration: underline"><strong>Create Account<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)">
<SelectParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text"
Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Password" Type="String" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Address1" Type="String" />
<asp:Parameter Name="Address2" Type="String" />
<asp:Parameter Name="Address3" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="County" Type="String" />
<asp:Parameter Name="PostCode" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</strong></span>
<table style="font-weight: bold; width: 394px; text-decoration: underline">
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Email:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Password:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Name:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Address 1:</td>
<td>
<asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Address 2:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Address 3:</td>
<td>
<asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
City:</td>
<td>
<asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
County:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Post Code:</td>
<td>
<asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td>
</tr>
</table>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
</asp:Content>
Thanks again
View 1 Replies
View Related
Apr 3, 2001
I am looking for something on pulling information from Exchange 5.5 Sp4 to SQL7.0. Anyone know where I can start?
-Stu
View 3 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Mar 9, 2005
We have a bunch of stored procedures already built. Is there any way of using them in a query? For example:
select authors from (exec sp_authordata)
This obviously doesn't work but it is what I want.
View 3 Replies
View Related
Aug 21, 2007
I have two table one is CustomerPurchaseOrder column as (CPONO,ItemName,CpoQuantity) another is SupplierPurchaseOrder column as (CPONO,SPONO,ItemName,SPOQuantity). There is Three Condition
Which is as :
Case1)
IF CustomerPurchaseOrder.CPONO not Exist in SupplierPurchaseOrder
then pick all CustomerPurchaseOrder.ItemName and CustomerPurchaseOrder.CpoQuantity.
Case2)
IF CustomerPurchaseOrder.CPONO Exist in SupplierPurchaseOrder
then pick CustomerPurchaseOrder.ItemName and CustomerPurchaseOrder.CpoQuantity only for CustomerPurchaseOrder.ItemName Which having CustomerPurchaseOrder .CpoQuantity > Sum(SupplierPurchaseOrder .SPOQuantity)
thanks in Advance
View 1 Replies
View Related
Nov 5, 2004
i want use stored procedures in select query.
for example :
select * from table where (... run a stored procedure ....)
or update from table set a=@a where (... run a stored procedure ....)
how can i do it ?
thx
View 3 Replies
View Related
Jan 14, 2000
Hello Everyone
Can someone please confirm, wether it is possible to execute a stored procedure in a sybase/or any other database from within an open query function in a SQL 7 database.
I know that we can execute a dynamic SQL statement, what about a stored procedure in the linked server. Can we execute that with parameters??
THANKS
View 1 Replies
View Related
Mar 24, 2008
Okay, so here's my dilemma: I'm trying to figure out a way I can get a list of drives, users, groups, etc. from computers on the network. There are a couple of caveats:
1) This has to be done entirely using T-SQL. There can be no external components that need to be installed.
2) xp_cmdshell can NOT be used, both for security reasons and because some of the computers being polled do not have SQL Server installed.
I would like to use the sp_OA* stored procedures. So far I have been able to connect to a remote server and find the running state of SQL Server; however, when it comes to enumerating collections I'm kinda lost.
This has also been posted in the MS-SQL general forum.
Any help would be greatly appreciated!
View 1 Replies
View Related
Oct 20, 2006
Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!
View 1 Replies
View Related
Oct 17, 2007
Hello,
I was in a confusion that is Stored Procedures are really fast ? I have a .NET application where I am using Stored Procedures. But recently I cam through this link http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx which describes Stored Procedures are bad and it won't give any performance difference. What is the truth ? Will it give good performance that passing query from the application ?
Please make it clear
View 8 Replies
View Related
Dec 12, 2007
I've been researching this problem for weeks and I haven't gotten very far with it so I was hoping to get some help here.
Here's the error information we get:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This particular error comes from a .net application, but we've seen similar "query timeout errors" from a vb6 application roughly 6 months ago.
Here are the facts we've narrowed down:
1) The timeout happens at seemingly random times (very sporadic).
2) It's currently only happening on a few stored procedures (if requested, I'll provide one of the stored procedures, but it's basically a complex search procedure).
3) To resolve the timeout error, we've found 2 temporary solutions:
A) have all clients exit the program thus closing all active connections (less than 10 connection in a 4-5 user setup)
B) I run the following script when the timeout occurs and then the stored procedure runs smoothly:
Code Block
sp_configure 'remote query timeout', 0
reconfigure with override
sp_configure 'remote query timeout', 600
reconfigure with override
4) Running the stored procedure from our application and from SQL Management studio express, is the same, except management studio doesn't time out and actually runs as long as it takes (roughly 1 minute 20 seconds or under 1 second after the script above runs).
We're pretty stumpted and it's happened at 5 different client sites with little in common. One of our sites is even running SQL express off the workstation with nothing else running and it still occurs.
I'm open to trying practically anything at this point, but unfortuntenly we have not been able to reproduce this behavior in our testing enviroment so I can't give much information for others to reproduce.
Thanks in advance!
View 1 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
May 21, 2004
Hi MAK,
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Thank you!
View 2 Replies
View Related
Jan 25, 2007
Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!
<cfquery name="formWait" datasource="#datasource#">
SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName,
formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus,
formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as
ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status
FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2 where teacherStatus = 2 and appraiserStatus = 1 and teacherID=#id#
</cfquery>
View 7 Replies
View Related
Feb 11, 2015
My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.
Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,
[Code] .....
View 9 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Aug 29, 2014
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
View 7 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Aug 8, 2005
Hi all,I'm trying to get information about a given Stored Procedure (SP) but am able (until now) to get to extract only the name and columns either using T-SQL or SQLDMO. I want to extract information like which Tables the SP accesses, and also which Fields. This however doesn't seem to be available through T-SQL nor SQLDMO.I haven't been able to find any information yet, and am thinking that my only way to work this out is to design my own parser, which I've done to some extent - it extracts the tables and fields of every query inside the SP, but it gets a bit messy when the SP gets complicated, even when using regular expressions.I'm sure SQL must know a way to find out about these objects, since it manages to compile SP - so where does it maintain or (even better) expose information about the tables and fields each SP uses?Any help or pointers would be very very helpful!Regards,Pieter
View 1 Replies
View Related
Apr 6, 2008
Hi,
I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.
Would you please give me a idea if you know how to do this with query or build-in stored procedure.
Thanks a lot
Mark
View 1 Replies
View Related
Oct 3, 2007
Hi
I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.But when executing it from ASP.NET code like this:
..... Of course more code is executed before this call .....int retVal = this.odbcCreateDataBaseCommand.ExecuteNonQuery();
retVal is -1. But -1 doesn't really tell me what the problem is?
Is there anyway to get extended error information so I can figure out whats going wrong?
(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)
Regards
Tomas
View 1 Replies
View Related
Feb 18, 2003
I want to write a sql query for information from a table in different database but on the same server.
what is the syntax.
select * from databse2.owner.table ??
i am in application1 database and i want to query from application2 database on the same server.
use application1
go
Select * from application2.dbo.tablename
go
Is this query correct?
View 1 Replies
View Related
Oct 11, 2001
Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Thanks,
Mary Elizabeth
NW Natural
View 1 Replies
View Related
Oct 6, 2005
does any one has a query which gives, how often the job runs in a day?
Thanks for your help.
View 1 Replies
View Related
Jun 5, 2015
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
View 4 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Mar 23, 2007
Hi all,
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode
1 999 Barry White BW13 8GS
2 999 <null> <null> BW13 9GS
3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
Kind regards,
macca
View 10 Replies
View Related
Oct 31, 2005
Hello All,Could someone help provide a query that I can run todetermine the primary key and unique columns for anygiven table ?Thanks in Advance,Akintoye
View 1 Replies
View Related
Jul 20, 2005
Hi,I was just helping a coworker optimize a query. He had two versions:one which used UNION for each value for which he was tallying resultsand another query which used GROUP BY. Here is an aproximation of whatthey were:Query #1:---------SELECT 12 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 5UNIONSELECT 13 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 6Query #2:---------SELECT R.row_num AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1INNER JOIN Report_Rows R ON R.col2 = T.col2WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateGROUP BY ALL R.row_numORDER BY R.row_numThe Report_Rows table in this case would have had two rows mapping row12 to a column value of 5 and row 13 to a column value of 6. Thesecond query was performing horribly until I noticed the ALL keywordin the GROUP BY, which I didn't think was necessary. When I removedthat it performed more like I expected it to perform.Before I had noticed that I was scouring over the query plans andcouldn't figure out why in one instance the query optimizer chose tojoin My_Table and Another_Table, yet when the ALL keyword was there itchose to return all of the records from Another_Table (a rather largetable) and join it to the Report_Rows table before then joining toMy_Table, which had the date criteria in the WHERE clause.So, if you've read this far without giving up...1. Why would the ALL keyword cause this? I understand thefunctionality of ALL, but I still don't see why that caused thereordering of the joins.2. (more importantly) Are there any good resources that you know ofthat explain how the query optimizer choices its query paths? Do the"Inside SQL Server" books go into that much detail? Any good onlineresources?Thanks!-Tom.
View 3 Replies
View Related
Mar 13, 2008
Hello.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
[result will be]
| id1 | id2 | col67 | .......................
and at the end of all I need something like :
id1-from t1.id; id2-from t2.id; col67-unknown;
thanks for help
View 3 Replies
View Related