Changing the default port number and still connecting

Hello,
I have SQL Server 2000 Enterprise Edition, and I'd like to change the
default port to something else (we'll use 2033 for this example). I've
tried to connect from another computer on the same subnet using
"ServerName:2033" as the server, but it doesn't connect. It will connect if
I change the Server to use the default port and change the server to connect
to on the client to "ServerName", but either I"m not referencing the new
port number properly or ?
Any help you could provide would be great.
Thanks In Advance!
Rick
Hi,
Stop all applications and Third party software services in the server.
After the applications and services are stopped, change the port and start
SQL Server
service. This should enable SQL Server to take control of new port. Open the
sql server error log and see if the new port is used.
See additional details from old post.
http://groups.google.co.in/groups?q=...ft.com&rnum=10
Thanks
Hari
SQL Server MVP
"RWC" <rcollens_SPAMONATOR_@.hemmingway.com> wrote in message
news:kUAde.1198452$8l.948157@.pd7tw1no...
> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to
connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ?
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>
|||Hi
"ServerName, 2033" is the correct format.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RWC" wrote:

> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect if
> I change the Server to use the default port and change the server to connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ?
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>
>
|||You're a GENIUS man! ;)
Thanks Mike, that worked like a charm. (I figured it would be the
connection syntax).
Thanks again!
Rick
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C7FAFA8A-7B27-4E48-88CB-90E00FF63573@.microsoft.com...[vbcol=seagreen]
> Hi
> "ServerName, 2033" is the correct format.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "RWC" wrote:

Changing the default port number and still connecting

Hello,

I have SQL Server 2000 Enterprise Edition, and I'd like to change the
default port to something else (we'll use 2033 for this example). I've
tried to connect from another computer on the same subnet using
"ServerName:2033" as the server, but it doesn't connect. It will connect if
I change the Server to use the default port and change the server to connect
to on the client to "ServerName", but either I"m not referencing the new
port number properly or ??

Any help you could provide would be great.

Thanks In Advance!
RickHi,

Stop all applications and Third party software services in the server.
After the applications and services are stopped, change the port and start
SQL Server
service. This should enable SQL Server to take control of new port. Open the
sql server error log and see if the new port is used.

See additional details from old post.

http://groups.google.co.in/groups?q...oft.com&rnum=10

Thanks
Hari
SQL Server MVP

"RWC" <rcollens_SPAMONATOR_@.hemmingway.com> wrote in message
news:kUAde.1198452$8l.948157@.pd7tw1no...
> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to
connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ??
> Any help you could provide would be great.
> Thanks In Advance!
> Ricksql

Changing text appearance on a MouseOver event.

Hi,

Is it possible in SSRS to change the appearance of a Hyperlink based on a MouseOver event? For example can I show the text as say, underlined, only when the Mouse pointer hovers over it.. and normal otherwise?

http://technet.microsoft.com/en-us/library/ms345247(SQL.90).aspx

What's the point of a hyperlink if it is not underlined? Bad practice. Jakob Neilson wouldn't like it. I'm telling.

Changing SSIS property defaults

To make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with Checkpoint\Restart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.

Is there a way to control the defaults that the SSIS IDE uses?

I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.

No worries. The same thing exists for SSIS:

SSIS: Location for your package templates
(http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx)

It won't allow you to set default properties of tasks though - which I think is what you are after.

-Jamie

|||

Yes, I was hoping to set defaults for packages and tasks. Any recommendations? Our DTS developers are balking at the multitude of properties needed to be set to implement certain features.

What do you name the templates that you put in the templates folder so that they are used in the IDE? Package1.dtsx?

Thanks.

|||

Chopaka wrote:

Yes, I was hoping to set defaults for packages and tasks. Any recommendations? Our DTS developers are balking at the multitude of properties needed to be set to implement certain features.

Sorry, that's not possible. It would be nice if it were though - perhaps you could suggest it at Microsoft Connect.

Whenever they build the ability to reuse pre-configured tasks then you will be able to do it. I wouldn't expect that until v3 at the earliest though.

Chopaka wrote:

What do you name the templates that you put in the templates folder so that they are used in the IDE? Package1.dtsx?

Whatever you like!

-Jamie

changing precision in a trigger

I am trying to change a quantity defined as float to a decimal with 2 decima
l
places.
example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
Is it possiable for this trigger to fire on only a certain storeId? Thanks
for any advice!
alter TRIGGER tr_InsertUpdateTransEntry
on TransactionEntry
FOR Insert,Update
AS
declare @.Id int
declare @.Qty decimal
declare @.StoreId int
select @.Id = (select [Id] from inserted)
--select @.Qty = (select cast(quantity as decimal(10,2))from inserted)
select @.Qty = (select quantity from inserted)
select @.StoreId = (select storeId from inserted)
update transactionEntry
--set quantity = @.Qty
set quantity = cast(@.Qty as decimal(10,2))
where [Id] = @.Id
and StoreId = @.StoreId"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> I am trying to change a quantity defined as float to a decimal with 2
decimal
> places.
> example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
I'm a bit -- why do you feel that 2.759 rounds to 2.78 ? Where
does the extra 0.02 come from?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||typo.. i want it to update 25.7599999 to 25.76
"Adam Machanic" wrote:

> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> decimal
>
> I'm a bit -- why do you feel that 2.759 rounds to 2.78 ? Where
> does the extra 0.02 come from?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||Three things:
1) Updating a floating point value to a rounded value won't get you much --
in a lot of cases you'll still see a non-rounded value when you query it
back again. Floating point is an inprecise datatype. Your best bets are
either to change the datatype itself to numeric or to handle the rounding at
display time.
2) Remember that triggers need to be able to handle multiple rows. This
trigger can only handle a single row as-is. What if an update affects more
than one?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> I am trying to change a quantity defined as float to a decimal with 2
decimal
> places.
> example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
> Is it possiable for this trigger to fire on only a certain storeId?
Thanks
> for any advice!
> alter TRIGGER tr_InsertUpdateTransEntry
> on TransactionEntry
> FOR Insert,Update
> AS
> declare @.Id int
> declare @.Qty decimal
> declare @.StoreId int
> select @.Id = (select [Id] from inserted)
> --select @.Qty = (select cast(quantity as decimal(10,2))from inserted)
> select @.Qty = (select quantity from inserted)
> select @.StoreId = (select storeId from inserted)
> update transactionEntry
> --set quantity = @.Qty
> set quantity = cast(@.Qty as decimal(10,2))
> where [Id] = @.Id
> and StoreId = @.StoreId|||this is my first trigger, so what do i need to do to handle sets. i thought
this would fire on each insert. I think i will code around the rounding
issues in reports.
thanks Adam.
"Adam Machanic" wrote:

> Three things:
> 1) Updating a floating point value to a rounded value won't get you much -
-
> in a lot of cases you'll still see a non-rounded value when you query it
> back again. Floating point is an inprecise datatype. Your best bets are
> either to change the datatype itself to numeric or to handle the rounding
at
> display time.
> 2) Remember that triggers need to be able to handle multiple rows. This
> trigger can only handle a single row as-is. What if an update affects mor
e
> than one?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> decimal
> Thanks
>
>|||It fires once per insert -- but an insert can have multiple rows; for
instance:
INSERT Tbl (Col)
SELECT 1
UNION ALL
SELECT 2
This would insert two rows into Tbl...
To handle multiple rows in a trigger, you could do something like:
UPDATE Tbl
SET Col = 'x'
WHERE EXISTS
(SELECT *
FROM INSERTED
WHERE INSERTED.PK = Tbl.PK)
-- Assuming that PK is the table's primary key
This would handle all of the rows in the INSERTED table in one shot.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:6E4A2C5F-FBD9-4472-8FF0-00FDEA88E935@.microsoft.com...
> this is my first trigger, so what do i need to do to handle sets. i
thought
> this would fire on each insert. I think i will code around the rounding
> issues in reports.
> thanks Adam.
> "Adam Machanic" wrote:
>
much --
are
rounding at
more
26.|||Thanks Adam, I will spend some time on reading up.
"Adam Machanic" wrote:

> It fires once per insert -- but an insert can have multiple rows; for
> instance:
> INSERT Tbl (Col)
> SELECT 1
> UNION ALL
> SELECT 2
> This would insert two rows into Tbl...
> To handle multiple rows in a trigger, you could do something like:
> UPDATE Tbl
> SET Col = 'x'
> WHERE EXISTS
> (SELECT *
> FROM INSERTED
> WHERE INSERTED.PK = Tbl.PK)
>
> -- Assuming that PK is the table's primary key
>
> This would handle all of the rows in the INSERTED table in one shot.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:6E4A2C5F-FBD9-4472-8FF0-00FDEA88E935@.microsoft.com...
> thought
> much --
> are
> rounding at
> more
> 26.
>
>

Changing Password

How can I assign a user the ability to change/reset password for users
assigned to one particular database only in EM. Example, Northwind is one of
the databases in my system; I want the user john to be responsible for
changing/resetting password for users with access to Northwind database only
and nothing else. Is this possible? If so, how do I do it?
Thanks
EmmaPasswords are assigned to logins which are at the server
level. And only sysadmins and securityadmins can execute
sp_password. But that's at a server level, not the database
level. So no, not if you want to restrict it to just one
database.
-Sue
On Tue, 27 Sep 2005 06:52:25 -0700, "Emma"
<Emma@.discussions.microsoft.com> wrote:

>How can I assign a user the ability to change/reset password for users
>assigned to one particular database only in EM. Example, Northwind is one o
f
>the databases in my system; I want the user john to be responsible for
>changing/resetting password for users with access to Northwind database onl
y
>and nothing else. Is this possible? If so, how do I do it?
>Thanks
>Emma

Changing Parameters Dynamically

Hello All,
How do I change paramters dynamically based on user selection. For
example if I let the user select country in a drop down, and based on
the country, I need to load the states parameter, and based on the
states, the counties parameter etc...Is such a dynamic layout possible
using reporting services and VS.NET? If so how? Any links or code
samples would be really helpful.
Thanks,
ChrisYes you can do this. Reference the data set in the report parameter. In the
code snippet below, I have 2 parameters. The first report Parameter is
passed into the query parameter. The resulting data set is then use in the
second parameter and the report filters the data set based on that
parameter. Play with the .rdl a bit and you will get it to work.
<ReportParameters>
<ReportParameter Name="FirstName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Brad</Value>
</Values>
</DefaultValue>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Brad</Value>
</ParameterValue>
<ParameterValue>
<Value>Jon</Value>
</ParameterValue>
<ParameterValue>
<Value>Steve</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<Prompt>FirstName</Prompt>
</ReportParameter>
<ReportParameter Name="LastName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Syputa</Value>
</Values>
</DefaultValue>
<Prompt>LastName</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Northwind</DataSetName>
<ValueField>EmployeeLastName</ValueField> --Field(column) in the
dataset.
<LabelField>EmployeeLastName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
--
| From: chrispragash@.hotmail.com (CPragash)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Changing Parameters Dynamically
| Date: 14 Sep 2004 10:57:39 -0700
| Organization: http://groups.google.com
| Lines: 11
| Message-ID: <79c9faae.0409140957.43a028f6@.posting.google.com>
| NNTP-Posting-Host: 167.7.17.3
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1095184659 14011 127.0.0.1 (14 Sep 2004
17:57:39 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 14 Sep 2004 17:57:39 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews2.google.com!no
t-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29153
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello All,
|
| How do I change paramters dynamically based on user selection. For
| example if I let the user select country in a drop down, and based on
| the country, I need to load the states parameter, and based on the
| states, the counties parameter etc...Is such a dynamic layout possible
| using reporting services and VS.NET? If so how? Any links or code
| samples would be really helpful.
|
| Thanks,
| Chris
|

Changing Parameter in SQLDataSource for Multiple DropDowns

I have one page, one connection, and three dropdowns.
The connection looks like (as an example):

<asp:SqlDataSource ID="DropDownConn" runat="server" ConnectionString="<%$ ConnectionStrings:aousConnectionString %>"
SelectCommand="SELECT [Value], [Text] FROM [DropDown] WHERE (([Group] = @.Group) AND ([Viewable] = @.Viewable))">
<SelectParameters>
<asp:Parameter Name="Group" Type="String" />
<asp:Parameter DefaultValue="True" Name="Viewable" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>

The DropDowns Look like this:

<asp:DropDownList ID="DropDown1" runat="server"></asp:DropDownList>
<asp:DropDownList ID="DropDown2" runat="server"></asp:DropDownList>
<asp:DropDownList ID="DropDown3" runat="server"></asp:DropDownList
The C# Code I am trying is like this:
DropDownConn.SelectParameters["Group"].Equals("DropDown1");
DropDownConn.SelectParameters["Viewable"].Equals(true);
DropDown1.DataSourceID = "DropDownConn";
DropDown1.DataTextField = "Text";
DropDown1.DataValueField = "Value";
DropDown1.DataBind();

As an example. I can not get it done so that I don't have to create 3 dataconnections. Any help, PLEASE?

It should be done this way..

DropDownConn.SelectParameters["Group"].DefaultValue = "some group value"; // change it accordingly on what data should be displayed in the dropdownlists
DropDownConn.SelectParameters["Viewable"].DefaultValue = "True";

DropDown1.DataSource = DropDownConn;
DropDown1.DataTextField = "Text";
DropDown1.DataValueField = "Value";
DropDown1.DataBind();

// for second

DropDownConn.SelectParameters["Group"].DefaultValue = "some group value for second dropdown"; // change it accordingly on what data should be displayed in the dropdownlists
DropDownConn.SelectParameters["Viewable"].DefaultValue = "True";

DropDown2.DataSource = DropDownConn;
DropDown2.DataTextField = "Text";
DropDown2.DataValueField = "Value";
DropDown2.DataBind();

-----

You can also, refactor the code to set the datasource for the dropdownlist.

Thanks

-Mark post(s) as "Answer" that helped you

|||

Thank you for the answer, and that method does work. Is that really the way we are suppose to do it. The default value is such a poor naming scheme and Microsoft had done such a good job with most other things. Thanks for the the help.

Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not[vbcol=seagreen]
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
Leila
Do you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there[vbcol=seagreen]
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable[vbcol=seagreen]
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?
|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...[vbcol=seagreen]
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
would´t got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
would´t worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,
>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> would´t got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> would´t worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> > - I do agree, but indexes and tuning the queries can help to limited
> > extent.
> > If they could be ultimate solution for all situations, Indexed Views
> > wouldn't be invented! What if your query needs a covering index with
more
> > that 16 columns? What if the index length exceeds 900 bytes? You will
not
> > be
> > able to create useful indexes.
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing Index Question

Hi All,
I'm trying to desgin a table that will reset the index depending on field
values. Example:
INDX Date1 NETID JunkField
-- -- -- --
1 05/05/05 USER1 1
2 05/05/05 USER1 1
3 05/05/05 USER1 1
1 05/05/05 USER2 1
1 05/06/05 USER2 1
Every time there is a new user NETID or the DATE1 values changes, the INDX
value should reset back to 1. Could someone provide me with a simple SQL
script. I'm new to this.
Thanks.Triggers may help you acheive this. Have a look at :-
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_create2_7eeq.htm
--
HTH
Ryan Waight, MCDBA, MCSE
"JackV" <vituja@.consumer.org> wrote in message
news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I'm trying to desgin a table that will reset the index depending on field
> values. Example:
> INDX Date1 NETID JunkField
> -- -- -- --
> 1 05/05/05 USER1 1
> 2 05/05/05 USER1 1
> 3 05/05/05 USER1 1
> 1 05/05/05 USER2 1
> 1 05/06/05 USER2 1
> Every time there is a new user NETID or the DATE1 values changes, the INDX
> value should reset back to 1. Could someone provide me with a simple SQL
> script. I'm new to this.
> Thanks.
>|||Hi Ryan,
The link doesn't seem to work. Can you resend it. Thanks.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eO0gI9RgDHA.2292@.TK2MSFTNGP10.phx.gbl...
> Triggers may help you acheive this. Have a look at :-
>
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
> lref.chm::/ts_create2_7eeq.htm
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "JackV" <vituja@.consumer.org> wrote in message
> news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > I'm trying to desgin a table that will reset the index depending on
field
> > values. Example:
> >
> > INDX Date1 NETID JunkField
> > -- -- -- --
> > 1 05/05/05 USER1 1
> > 2 05/05/05 USER1 1
> > 3 05/05/05 USER1 1
> > 1 05/05/05 USER2 1
> > 1 05/06/05 USER2 1
> >
> > Every time there is a new user NETID or the DATE1 values changes, the
INDX
> > value should reset back to 1. Could someone provide me with a simple
SQL
> > script. I'm new to this.
> >
> > Thanks.
> >
> >
>

changing English Date to Iranian date

Hi Everyone,

I want to change the form of Date in SQL Server 2005,from English Date,to Iranian Date.

For example, instead of inserting 2007/1/1 write 85/10/11.

Thanks,

Nassa

I am not familiar with the iranian date, is the first date you mentioend correlated to the second one ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

FROM SQL Server 2005 Books Online topic 'CAST and CONVERT (Transact-SQL)'.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.

See for custom code:
http://www.codeproject.com/useritems/Hijri_Shamsi_Date.asp?df=100&forumid=254936&exp=0&select=1371550

General international resource:
http://www.microsoft.com/globaldev/DrIntl/columns/002/default.mspx