I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.
> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.
|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A
|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J
|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
sql
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Friday, March 30, 2012
Is using Max in varchar a bad thing?
I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
Monday, March 26, 2012
Is this possible?
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:
>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:
> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:
>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.
Wednesday, March 21, 2012
is this enough to copy user's permissions to another user's from sp_helpprotect
Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:
Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @.newLoginParam
My question: is there any big business scenario in which my script might fail or miss some permissions for any user?
here is the main part of my script:
declare @.objj as varchar(8000)
declare @.grantee as varchar(8000)
declare @.action as varchar(8000)
declare @.col as varchar(8000)
declare @.sqlGrant as varchar(8000)
declare @.ProtectType as varchar(8000)
declare @.count as int
set @.count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @.objj, @.grantee, @.ProtectType, @.action,@.col
while @.@.fetch_status=0
begin
if (@.objj!='.' and (@.col = '.' OR @.col = '(All+New)'))
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + ' to ' + @.newLoginParam
end
if (@.objj!='.' and @.col != '.' and @.col != '(All+New)')
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + '(' + @.col + ') to ' + @.newLoginParam
end
if (@.objj='.')
begin
set @.sqlGrant = @.ProtectType +@.action + ' to ' + @.newLoginParam
end
exec(@.sqlGrant)
fetch next from........
...etc
P.S: The main reason is that I can t test my script now on the production platform on which the script will be run
Thanks for your advice .
I think you nead a space between @.ProtectType and action.
If you need to grant the permission of an existing user to many new users consider using a database role. You can grant the permissions to the role and then just add the new users to the role.
|||yeah i will also add the new user to the roles that the old user was part of as well. But i guess the old user might also have permissions on databse objects without necessarily belonging to a specific role?do you agree?
Thanks
Subscribe to:
Posts (Atom)