Northwind Pivot issue

I tried using some code for the new pivot function in the "Inside Microsoft SQL Server 2005 T-SQL Querying" book.

I was using the Northwind database but that was from a SQL 2000 database. There was an error that stated the database needed to be updated.

I have looked all over the web and have not seen where to download the SQL 2005 Northwind & Pubs database.

Anybody have the URL for this or is this not available?

Thanks,

Gene

You can download the scripts here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Denis the SQL Menace

http://sqlservercode.blogspot.com/

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR
Probably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR
|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely[vbcol=seagreen]
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
change
> code
> area
>
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Just as an FYI:

> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> another,
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Just as an FYI:
>
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
change[vbcol=seagreen]
> code
> area
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about :
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about :
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:

> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and
"something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8
or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they h
ave the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mil
l people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> another,
> I think we're talking about different things in regards to prefix. I'
m
> talking about :
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I thin
k
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Just as an FYI:
>
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Probably not. The phone number really is (in North America) 10 digits,
of
> > which the first three are the area code. Beyond N America, things
change
> > somewhat (I believe).
> >
> > That said, you can have a table of phone numbers where there is an area
> code
> > column and a number column, with a FK from the area code to a table of
> area
> > codes. This ensures that only known area codes are put into your phone
> > number table.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> >However, if you're only using them for display, they should definitely
> > be in the same column, IMO...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:
> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> > I'd be careful about prefix. Where I work right now, I use 9+1+area
> > code+number. In another place, it was 4+area code+number. In yet
> another,
> > it was 8+area code+number. Of course, at home, it's 1+area code+number.
> >
> > Sometimes, internationalization can be a right PITA...
> >
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:
> Just as an FYI:
>> For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
>> I'd be careful about prefix. Where I work right now, I use 9+1+area
>> code+number. In another place, it was 4+area code+number. In yet
>> another,
>> it was 8+area code+number. Of course, at home, it's 1+area
>> code+number.
>> Sometimes, internationalization can be a right PITA...
>>
>> I think we're talking about different things in regards to
>> prefix. I'm talking about:
>> (XXX) YYY - ZZZZ
>> Where XXX is the area code and YYY is the prefix. For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix,
>> although the number of digits are variable, but the something can be
>> totally different depending on country -- there may be more levels
>> of hierarchy, e.g. I think in some Asian countries they have 4 or 5.
>> The local phone system you're on will determine the prefix you're
>> talking about, 9 + 1, 4 + , etc.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.sqljunkies.com/weblog/amachanic
>> --|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:elA9%239V0EHA.3408@.tk2msftngp13.phx.gbl...
> And for further info, we don't have any prefix or area code in Denmark. We
> used to have a 2 digit area code in the past, but for many years we've
just
> had an 8 digit phonenumber.
> >
> > In Sweden, we have no prefix. We have CountryCode (obviously),
> > AreaCode and "something". :-)
Thanks for the info guys!
Both are countries I haven't worked with yet. I had a bear of a time w/
Asia (mostly due to lack of quality data, but it's been a few years since
that project so maybe things have improved) -- I can't wait until I get to
cover Europe :-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Non-US address

Someone told me that some non-US address do not have State/Province, and
some might not have Postal Code. Is this true?J wrote:
> Someone told me that some non-US address do not have State/Province, and
> some might not have Postal Code. Is this true?

Yes. And some might have things that aren't in a US address. For example:

3-1, Kudan-minami 1-chome
Chiyoda-ku,
Tokyo, Japan 102-8660

Chiyoda-ku has no US equivalent and not no equivalent for state.
Tokyo is in Kanto but you won't find that in the normal address
listing.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||Check out

http://www.upu.int/post_code/en/pos...countries.shtml|||J (jungnaja@.hotmail.com) writes:
> Someone told me that some non-US address do not have State/Province, and
> some might not have Postal Code. Is this true?

Yes, here in the outside world things are rough. You know, for our tiny
little country it would be preposterous to have a thing like states. Many
US states have a bigger population than we have. Besides, by tradition
this is a centralised country where the national government strongly
regulates what counties and municiplaties should do. In theory they are
free to set their on tax level, but the government meddles there as well.
On top of that, county and municiplatity borders changes sometimes, so
it would not be good for addresses.

We do have postal codes. And to make you feel a little more like home,
they are even five digit ones. However, there are really rough places
like the UK where the postal codes looks entirely different.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

non-typesafe data reporting

I have vb.net 2005 code that generates data that needs to be reported.
The results need to be reported in generic text fields in the rdlc
report. Sometimes I will report 2 text values while the next time I
may report 3 values in the report text box.
My problem is that I can't pass this program generated data to the text
fields on the report. I used to do this and can still do this in
crystal reports and active reports by generating text objects on the
report followed by creating an instance of the report. Next the
instance property (any of the text boxes) value is set by means of
setting the parameter. Lastly generate the report.
In summary, I need to be able to pass specific program generated text
values to text boxes to a Microsoft report by NOT using a dataset or
datatable.
Thanks all
(crystal example)
Dim i As New CrystalReport1
i.SetParameterValue("Parameter_X", "test value for text box on
report")Use hidden report parameters. Then hide or show the textbox based on whether
the parameter is filled in or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <Chris.Grimm@.ormet.com> wrote in message
news:1134161274.196063.122960@.g47g2000cwa.googlegroups.com...
>I have vb.net 2005 code that generates data that needs to be reported.
> The results need to be reported in generic text fields in the rdlc
> report. Sometimes I will report 2 text values while the next time I
> may report 3 values in the report text box.
> My problem is that I can't pass this program generated data to the text
> fields on the report. I used to do this and can still do this in
> crystal reports and active reports by generating text objects on the
> report followed by creating an instance of the report. Next the
> instance property (any of the text boxes) value is set by means of
> setting the parameter. Lastly generate the report.
> In summary, I need to be able to pass specific program generated text
> values to text boxes to a Microsoft report by NOT using a dataset or
> datatable.
> Thanks all
> (crystal example)
> Dim i As New CrystalReport1
> i.SetParameterValue("Parameter_X", "test value for text box on
> report")
>

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

NON EMPTY issue

Apologies if this is a bit simple but it's doing my head in a bit

Why would the following MDX return 4 correct records

Code Snippet

SELECT { [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

non empty{ DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time Calcs].[YTD],[Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Unit of Measure].&[2] )

however adding another measure returns 50 additional spurious measures, all with blank rows

Code Snippet

SELECT NON EMPTY { [Measures].[Signed Leases], [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Time Calcs].[YTD], [Unit of Measure].&[2] )

The "signed leases" measure was the field added

Presumably you mean that adding the Signed Leases measure returns another 50 spurious rows, not measures? Is Signed Leases a calculated measure, and if so what is its definition? I'm guessing [Time Calcs].[YTD] is another calculated measure (and is probably the culprit) - what is its definition? Have you been using the Non_Empty_Behavior property anywhere?

Chris

|||

Chris,

Yes, you were correct in your assumption. TimeCalcs was OK, it was actually the fact that signed leases was calculated. Adding an iif statement to create a null sorted it

No. of records returned by a query.

Dear all
i want to run a query on sql server based on whether the no. of records returned by another query is 0 or >0. what will be the sql code for this.Assume your SELECT statement is called Q. Than you can write

if (select count(*) from (Q) q)>0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END|||...or, if you have already run your select statement as part of your procedure, use this so you don't have to execute it a second time:

if @.@.ROWCOUNT > 0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END

@.@.ROWCOUNT stores the number of records returned by the last executed statement. If you don't use the value immediately, you will need to store it in a variable because it will change with your next statement.

blindman