I just wanted to write a simple WebService to return a dataset.
But when I am trying to debug it under Visual Studio, I get an exception
when calling conn.Open( ). The error message is "Failed to login user
myMachine\ASPNET". What is ASPNET? Why does Visual Studio invent that
account, if it fails to connect to my local MSDE? What can I do about it?
Please, I am not an administrator, so I need every detail.Make sure that you have a right Login as well as check if you have Windows
Authentication only. Change it to Mixed Authentication
"N Thorell" <NThorell@.discussions.microsoft.com> wrote in message
news:06CD83E4-9F83-4BE2-A695-652A7C4A7DF9@.microsoft.com...
> I just wanted to write a simple WebService to return a dataset.
> But when I am trying to debug it under Visual Studio, I get an exception
> when calling conn.Open( ). The error message is "Failed to login user
> myMachine\ASPNET". What is ASPNET? Why does Visual Studio invent
that
> account, if it fails to connect to my local MSDE? What can I do about
it?
> Please, I am not an administrator, so I need every detail.|||The local ASPNET Windows account is created automatically when you install
ASP.NET. This account is used by default as the security context for
ASP.NET applications.
You can either grant ASPNET access to SQL Server or reconfigure ASP.NET to
use a different account. See
http://support.microsoft.com/defaul...kb;en-us;316989 for details
on how to do this.
Hope this helps.
Dan Guzman
SQL Server MVP
"N Thorell" <NThorell@.discussions.microsoft.com> wrote in message
news:06CD83E4-9F83-4BE2-A695-652A7C4A7DF9@.microsoft.com...
>I just wanted to write a simple WebService to return a dataset.
> But when I am trying to debug it under Visual Studio, I get an exception
> when calling conn.Open( ). The error message is "Failed to login user
> myMachine\ASPNET". What is ASPNET? Why does Visual Studio invent
> that
> account, if it fails to connect to my local MSDE? What can I do about
> it?
> Please, I am not an administrator, so I need every detail.
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Fail to get Return_Value in stored procedure NullValueReference
I have a stored procedure that insert into a table and return a value indicating if the insert really occured.
If the a insert occurs then it returns the identity of that insert.
I have slimmed down my code here, so you can see.
My problem is that a NullValueReference exceptions occurs when trying to reference the returnvalue.
Is it not possible to get a return value along with a recordset ?
publicint RegisterCustomer(Customer customer)
{
SqlCommand myCommand =newSqlCommand("ins_RegistrerKunde", myConnection);
myCommand.CommandType =CommandType.StoredProcedure;
SqlParameter Epost =newSqlParameter("@.Epost",SqlDbType.VarChar, 25);
SqlParameter Passord =newSqlParameter("@.Passord",SqlDbType.VarChar, 25);
SqlParameter ReturnValue =newSqlParameter("@.RETURN_VALUE",SqlDbType.Int, 4);
ReturnValue.Direction =ParameterDirection.ReturnValue;
Epost.Value = customer.Email;
Passord.Value = customer.Password;
myCommand.Parameters.Add(ReturnValue);
myCommand.Parameters.Add(Epost);
myCommand.Parameters.Add(Passord);
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.SingleRow);
int isRegistered = (int)myCommand.Parameters["@.RETURN_VALUE"].Value;
myConnection.Close();
if (isRegistered == 1)
{
result.Read();
returnConvert.ToInt32(result["Kunde_ID"]);
result.Close();
}
returnConvert.ToInt32(isRegistered);
}
//Stored Procedure ins_RegistrerKunde
create procedure%PROC% (@.Epost varchar(25), @.Passord varchar(25))
as
begin
IF EXISTS( SELECT EpostFROM tbl_KundeWHERE Epost = @.Epost)
RETURN 0
ELSE
INSERT INTO tbl_Kunde( Epost, Passord)
VALUES (@.Epost, @.Passord)
SELECT @.@.IDENTITY AS Kunde_ID
RETURN 1
end
go
Also, instead of @.@.IDENTITY, use SCOPE_IDENTITY(), as this will returnthe the last Identity value in the current scope. SeeIdentity Crisis andUnderstanding Identity Columns for an explanation.|||
I see.
Thank you.![Embarrassed [:$]](http://pics.10026.com/?src=/emoticons/emotion-10.gif)
Friday, February 24, 2012
Extreamly slow performance on a view
Can anyone see anything that may be causing this view to return data
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>
Sunday, February 19, 2012
Extracting Month Name from Date
Is there a function for getting the Month name from a date string? For
example, '01/20/2004' would return 'January'I think it's MonthName()
MonthName(getdate()) would return October
"Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> Is there a function for getting the Month name from a date string? For
> example, '01/20/2004' would return 'January'
>|||DATENAME(MONTH, GATDATE())
"Kelly" <kelly.hauser@.amerock.com> wrote in message
news:ectKTi#qEHA.2764@.TK2MSFTNGP11.phx.gbl...
> I think it's MonthName()
> MonthName(getdate()) would return October
>
> "Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
> message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> > Is there a function for getting the Month name from a date string? For
> > example, '01/20/2004' would return 'January'
> >
> >
>
example, '01/20/2004' would return 'January'I think it's MonthName()
MonthName(getdate()) would return October
"Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> Is there a function for getting the Month name from a date string? For
> example, '01/20/2004' would return 'January'
>|||DATENAME(MONTH, GATDATE())
"Kelly" <kelly.hauser@.amerock.com> wrote in message
news:ectKTi#qEHA.2764@.TK2MSFTNGP11.phx.gbl...
> I think it's MonthName()
> MonthName(getdate()) would return October
>
> "Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
> message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> > Is there a function for getting the Month name from a date string? For
> > example, '01/20/2004' would return 'January'
> >
> >
>
Friday, February 17, 2012
Extracting Cube detailed info
Hello,
Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.
Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.
-Lawrence
If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.
If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.
Adrian Dumitrascu
|||Hello Adrian,
If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.
Sincerely,
-Lawrence
|||Hi,
here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
using Microsoft.AnalysisServices;
namespace GetCubeInfo
{
class Program
{
static void Main(string[] args)
{
string dateFormat = "yyyy-MM-dd HH:mm:ss";
try
{
FileStream stream;
stream = File.Create(@."GetCubeInfo.xml");
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
// Causes child elements to be indented
writer.Formatting = Formatting.Indented;
// Report element
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteStartElement("Server");
// Connect to the SSAS server
Server server = new Server();
server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");
writer.WriteAttributeString("Name", null, server.Name);
writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));
// Get the Adventure Works cube(s)
foreach (Database database in server.Databases)
{
writer.WriteStartElement("Database");
writer.WriteAttributeString("Name", null, database.Name);
writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));
writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));
foreach (Cube cube in database.Cubes)
{
writer.WriteStartElement("Cube");
writer.WriteAttributeString("Name", null, cube.Name);
writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Cube
}
foreach (Dimension dimension in database.Dimensions)
{
writer.WriteStartElement("Dimension");
writer.WriteAttributeString("Name", null, dimension.Name);
writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Dimension
}
writer.WriteEndElement(); // Database
writer.Flush();
}
writer.WriteEndElement(); // Server
writer.Flush();
}
catch (Exception exception)
{
// Uups
Console.WriteLine(exception.Message);
}
}
}
}
|||Hello,
I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.
BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.
Sincerely,
-Lawrence
|||Hi Lawrence,
I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.
Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).
Regards,
Bertil
|||Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.
Your help is appreciated!
-Lawrence
Extracting Cube detailed info
Hello,
Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.
Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.
-Lawrence
If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.
If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.
Adrian Dumitrascu
|||Hello Adrian,
If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.
Sincerely,
-Lawrence
|||Hi,
here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
using Microsoft.AnalysisServices;
namespace GetCubeInfo
{
class Program
{
static void Main(string[] args)
{
string dateFormat = "yyyy-MM-dd HH:mm:ss";
try
{
FileStream stream;
stream = File.Create(@."GetCubeInfo.xml");
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
// Causes child elements to be indented
writer.Formatting = Formatting.Indented;
// Report element
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteStartElement("Server");
// Connect to the SSAS server
Server server = new Server();
server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");
writer.WriteAttributeString("Name", null, server.Name);
writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));
// Get the Adventure Works cube(s)
foreach (Database database in server.Databases)
{
writer.WriteStartElement("Database");
writer.WriteAttributeString("Name", null, database.Name);
writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));
writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));
foreach (Cube cube in database.Cubes)
{
writer.WriteStartElement("Cube");
writer.WriteAttributeString("Name", null, cube.Name);
writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Cube
}
foreach (Dimension dimension in database.Dimensions)
{
writer.WriteStartElement("Dimension");
writer.WriteAttributeString("Name", null, dimension.Name);
writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Dimension
}
writer.WriteEndElement(); // Database
writer.Flush();
}
writer.WriteEndElement(); // Server
writer.Flush();
}
catch (Exception exception)
{
// Uups
Console.WriteLine(exception.Message);
}
}
}
}
|||Hello,
I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.
BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.
Sincerely,
-Lawrence
|||Hi Lawrence,
I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.
Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).
Regards,
Bertil
|||Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.
Your help is appreciated!
-Lawrence
Subscribe to:
Posts (Atom)