"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?
Here's an example...
The table contains this data, all items are varchar
32611317
3261Non-Client
32612
3261mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz
|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
David Portas
SQL Server MVP
|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegro ups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>
|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
David Portas
SQL Server MVP
|||"Aaron [SQL Server MVP]" wrote:

> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?Here's an example...
The table contains this data, all items are varchar
3261 1317
3261 Non-Client
3261 2
3261 mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
--
David Portas
SQL Server MVP
--|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegroups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
--
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" wrote:
> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"Multiple IP address in one SQL server" and "authentication problem with AD"

Never use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio
Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
|||Hi
Which domain is machine that you are connecting from in and have you tried
to change it?
Are you using SQL Authentication?
John
"Sergio Garcia" wrote:

> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>
|||Hi
Who are you logging into the machine as?
John
"Sergio Garcia" wrote:

> That machine is the same SQL Server. I can connect even to "localhost".
> If I use SQL Authentication it works, but with Windows authentication
> doesn't.
> Thanks very much.
> John Bell wrote:
>
|||Hi
If you can connect to host.pub.dom.com as a SQL User it is not the
resolution of the machine that has cause the problem.
If you can connect to host.dom.com as a Windows User host.dom.com\user it is
not the permissions for host.dom.com\user that is the issue.
If you can connect to host.pub.dom.com as a Windows User
host.pub.dom.com\user it is not the permissions for host.dom.com\user that is
the issue.
If you can connect to host.pub.dom.com as a Windows User host.dom.com\user I
suspect that it is something like the trust relationship between the two
domains that is the issue.
John
"Sergio Garcia" wrote:

> Ok, I will try to give you more information
> I am using a user who has permissions enough. It is a domain user, not a
> SQL user.
> Thanks for your help...
>
> John Bell wrote:
>
|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...[vbcol=seagreen]
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:

"Multiple IP address in one SQL server" and "authentication problem

Hi all.
I really don't known where to post because I don't known what is my
problem, so let me explain.
I have a SQL Server with two ethernet cards, one used for me, IT
Department, and other used by normal users.
Every card is in a separated network (obviously):
192.168.1.100 with "db.domain.com" A record in DNS
172.16.10.100 with "db.public.domain.com" A record in DNS
This server is an AD domain computer with "db.domain.com" name, and the
DC is in 192.168.1.0/24 network.
I am in troubles when I try to connect with MSrSQL Management Studio
using db.public.domain.com... I doesn't works, I get an error similar to
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 172.16.10.101]"
If I change db.public.domain.com to a CNAME record pointing to
db.domain.com it works, but this is not desired because I want users to
use their network, 172.16.10.0/24.
Any issues? Any suggestions? Any idea? Please help me if you can :) I
tried to play with Service Principal Names (SPN's), but I can't manage
to connect with an A record.
Thanks in advance,
SergioNever use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can :) I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio|||Sorry, I didn't explained well... it is not a DC, it is only a domain
member.
Paul Bergson [MVP-DS] wrote:
> Never use a multihomed server as a DC. So you need to either remove AD off
> of this machine or remove one of the NIC's.
> The link below has some info on this and I couldn't find the exact article
> but I can guarantee you are going to have constant problems with this setup.
> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
>|||Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
>> Never use a multihomed server as a DC. So you need to either remove AD
>> off of this machine or remove one of the NIC's.
>> The link below has some info on this and I couldn't find the exact
>> article but I can guarantee you are going to have constant problems with
>> this setup.
>> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>> Sorry for the misunderstanding. I don't specifically see what the
>> problem is, but does this sql server allow SQL Server and AD
>> authentication? If so have you tried both methods? What about the Event
>> Log what is the error you are receiving?

"cycles or multiple cascade paths" Error

Hi there.
I've been searching for this error specifically but I haven't found anything
yet.
I have these two tables (USERS and REQUESTS):
USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)
where LOGIN is the primary key.
The problem comes when I try to create the "REQUESTS" table.
In these requests there's one user who types the request. After one or two
days, there's other user who aproves the request. The problem is that I need
two foreign keys referencing the table "USERS", one for the user who types
and one for the user who aproves.
Note the "APROVED_BY" foreign key can be null because when a request is
saved the user who aproves doesn't exist yet.
CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
[TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE ,
CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY (
[APROVED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE
And SQL returns:
Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table
'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO
ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
Ok, after that, I tried creating a new table to store aprovals (Table with
two fields: "REQUEST_ID" and "APROVED_BY").
So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.
The same error comes up.
I don't think this structure goes into "cycles" or "multiple cascades".
Could someone tell me why SQL Server returns this error?
Thanks in advance
RegardsCREATE TABLE USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)
ALTER TABLE USERS ADD
CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
[LOGIN]
) ON [PRIMARY]
GO
CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
[TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE
The above one is working, you can only have one Cascade to one Table.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi Jens, thanks for your reply.
Does it mean that I'm not able to have "APROVED_BY" foreign key in table
REQUESTS?
"Jens" escribió:

> CREATE TABLE USERS (
> [LOGIN] [varchar] (10) NOT NULL ,
> [NAME] [varchar] (20) NOT NULL
> )
> ALTER TABLE USERS ADD
> CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
> [LOGIN]
> ) ON [PRIMARY]
> GO
> CREATE TABLE REQUESTS (
> [ID] [numeric](5, 0) NOT NULL ,
> [DATE] [datetime] NOT NULL ,
> [NOTES] [varchar] (100) NOT NULL ,
> [TYPED_BY] [varchar] (10) NOT NULL ,
> [APROVED BY] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE REQUESTS ADD
> CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE REQUESTS ADD
> CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
> [TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCA
DE
>
> The above one is working, you can only have one Cascade to one Table.
>
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||<DIV>"Rolandpish" <Rolandpish@.discussions.microsoft.com>
wrote in message
news:9B951B40-585A-444A-A125-2A87FF0FB748@.microsoft.com...</DIV>> Hi Jens,
thanks for your reply.
> Does it mean that I'm not able to have "APROVED_BY" foreign key in table
> REQUESTS?
>
No it means you can only have one cascading foreign key. However that's
fine since you should have ZERO cascading foreign key. ON UPDATE CASCADE
should be used very rarely, and probably not here. It is only used for
cascading updates the the primary key, which you generally shouldn't be
doing.
Here's all you need:
CREATE TABLE USERS
(
[LOGIN] [varchar] (10) NOT NULL PRIMARY KEY ,
[NAME] [varchar] (20) NOT NULL
)
CREATE TABLE REQUESTS
(
[ID] [numeric](5, 0) NOT NULL PRIMARY KEY,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL REFERENCES USERS,
[APROVED_BY] [varchar] (10) NULL REFERENCES USERS
)
David

"cycles or multiple cascade paths" Error

Hi there.
I've been searching for this error specifically but I haven't found anything
yet.
I have these two tables (USERS and REQUESTS):
USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)
where LOGIN is the primary key.
The problem comes when I try to create the "REQUESTS" table.
In these requests there's one user who types the request. After one or two
days, there's other user who aproves the request. The problem is that I need
two foreign keys referencing the table "USERS", one for the user who types
and one for the user who aproves.
Note the "APROVED_BY" foreign key can be null because when a request is
saved the user who aproves doesn't exist yet.
CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
[TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE ,
CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY (
[APROVED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE
And SQL returns:
Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table
'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO
ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
Ok, after that, I tried creating a new table to store aprovals (Table with
two fields: "REQUEST_ID" and "APROVED_BY").
So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.
The same error comes up.
I don't think this structure goes into "cycles" or "multiple cascades".
Could someone tell me why SQL Server returns this error?
Thanks in advance
RegardsCREATE TABLE USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)
ALTER TABLE USERS ADD
CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
[LOGIN]
) ON [PRIMARY]
GO
CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
[TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE
The above one is working, you can only have one Cascade to one Table.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi Jens, thanks for your reply.
Does it mean that I'm not able to have "APROVED_BY" foreign key in table
REQUESTS?
"Jens" escribió:
> CREATE TABLE USERS (
> [LOGIN] [varchar] (10) NOT NULL ,
> [NAME] [varchar] (20) NOT NULL
> )
> ALTER TABLE USERS ADD
> CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
> [LOGIN]
> ) ON [PRIMARY]
> GO
> CREATE TABLE REQUESTS (
> [ID] [numeric](5, 0) NOT NULL ,
> [DATE] [datetime] NOT NULL ,
> [NOTES] [varchar] (100) NOT NULL ,
> [TYPED_BY] [varchar] (10) NOT NULL ,
> [APROVED BY] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE REQUESTS ADD
> CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE REQUESTS ADD
> CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY (
> [TYPED_BY] ) REFERENCES [USERS] ( [LOGIN] ) ON UPDATE CASCADE
>
> The above one is working, you can only have one Cascade to one Table.
>
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||<DIV>"Rolandpish" <Rolandpish@.discussions.microsoft.com>
wrote in message
news:9B951B40-585A-444A-A125-2A87FF0FB748@.microsoft.com...</DIV>> Hi Jens,
thanks for your reply.
> Does it mean that I'm not able to have "APROVED_BY" foreign key in table
> REQUESTS?
>
No it means you can only have one cascading foreign key. However that's
fine since you should have ZERO cascading foreign key. ON UPDATE CASCADE
should be used very rarely, and probably not here. It is only used for
cascading updates the the primary key, which you generally shouldn't be
doing.
Here's all you need:
CREATE TABLE USERS
(
[LOGIN] [varchar] (10) NOT NULL PRIMARY KEY ,
[NAME] [varchar] (20) NOT NULL
)
CREATE TABLE REQUESTS
(
[ID] [numeric](5, 0) NOT NULL PRIMARY KEY,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL REFERENCES USERS,
[APROVED_BY] [varchar] (10) NULL REFERENCES USERS
)
David

"craxdrt9.dll" version "9.2.3.1368" errors at runtime with multiple threads STA

"craxdrt9.dll" version "9.2.3.1368" errors at runtime with multiple threads with ApartmentState as STA or MTA

I would appreciate any help on the following:

Keep getting the the following error from "Crystal Reports":

- Using "C:\Program Files\Crystal Decisions\Report Designer Component\craxdrt9.dll" version "9.2.3.1368"

Error on using multiple threads with thread.ApartmentState = ApartmentState.STA:

Object reference not set to an instance of an object.
at CRAXDRT.DatabaseTableClass.SetPrivateData(Int32 dataTag, Object data)
at TestCrystalReports1.QCrystalReports.QCrystalReportObject.processReport(IReport& iReport, Hashtable collReportDataRs, Boolean& m_btnCancelPressed) in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\QCrystalReports\QCrystalReportObject.cs:line 127
at TestCrystalReports1.QCrystalReports.QCrystalReportObject.processSubReport(IReport& iReport, Hashtable collReportDataRs, Boolean& m_btnCancelPressed) in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\QCrystalReports\QCrystalReportObject.cs:line 179
at TestCrystalReports1.QCrystalReports.QCrystalReportObject.runPrintJob(String rptFilePathStr, Hashtable recordsetHashTable) in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\QCrystalReports\QCrystalReportObject.cs:line 81
at TestCrystalReports1.PrintRequest.runPrintRequest() in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\PrintRequest.cs:line 79

Error on using multiple threads with thread.ApartmentState = ApartmentState.STA or ApartmentState.MTA:

Invalid directory.
at CRAXDRT.ApplicationClass.OpenReport(String pFileName, Object OpenMethod)
at TestCrystalReports1.QCrystalReports.QCrystalReportObject.runPrintJob(String rptFilePathStr, Hashtable recordsetHashTable) in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\QCrystalReports\QCrystalReportObject.cs:line 77
at TestCrystalReports1.PrintRequest.runPrintRequest() in E:\QECM\Tools\ReportingTools\TestApps\TestCrystalReports1\PrintRequest.cs:line 79

Code extract - C# / .net application:

References: Interop.CRAXDRT.dll

- Thread running the "processReport" method - ApartmentState - tried both STA & MTA
- Problem usually occurs in case of multiple threads or multiple calls within a thread

private static int DATA_INPUT_TYPE_ADO = 3;

private void processReport (
ref IReport iReport, // Report instance
System.Collections.Hashtable collReportDataRs, // Report data
ref bool m_btnCancelPressed // Cancel button
)
{
// Get the database
IDatabase iDatabase = (IDatabase) iReport.Database;

// Get the database tables
IDatabaseTables iDatabaseTables = (IDatabaseTables) iDatabase.Tables;

// Loop through the tables
int iTotalTables = iDatabaseTables.Count;
for ( int iTblCount = 1; iTblCount <= iTotalTables; ++iTblCount )
{
if ( m_btnCancelPressed == true )
return;

// Get table name
IDatabaseTable iDatabaseTable = (IDatabaseTable) iDatabaseTables[iTblCount];

// Get the relevant recordset from the collection passed
System.String bsTableName = iDatabaseTable.Name;

ADODB._Recordset rs = (ADODB._Recordset) collReportDataRs [ bsTableName ];
if ( rs == null )
{
throw new Exception( "Data not found: " + bsTableName );
}

// Set data for this table
iDatabaseTable.SetPrivateData( DATA_INPUT_TYPE_ADO, rs);
}
}Are any of Dlls missing?
See if you find asnwer here
http://support.businessobjects.com/

"case when" vs multiple update

Which one is faster and cost less for tables with a lot of rows:
1. update atable
set acol = case when cond1 then 1 when cond2 then 2 ..... end
2. update atable set acol = 1 where cond1
update atable set acol = 2 where cond2
update atable set acol = 3 where cond3
....>> Which one is faster and cost less for tables with a lot of rows:
Test it. One UPDATE statement with a CASE tends to be faster than multiple
ones.
Anith|||1 is probably cheaper. But it depends on whether you have a WHERE clause and
the selectivity, how
efficient indexes you have to drive that where clause and also how many rows
you modify.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
> Which one is faster and cost less for tables with a lot of rows:
> 1. update atable
> set acol = case when cond1 then 1 when cond2 then 2 ..... end
> 2. update atable set acol = 1 where cond1
> update atable set acol = 2 where cond2
> update atable set acol = 3 where cond3
> .....
>|||it depends, as usual.
look at the execution plans. If at least one of those updates scans the
whole tables, combine them into one update.
On the other hand, if every simple update accesses the table via a
highly selective index and touches just a handful of rows, it could be
better leave them as is, because the combined statement might be
executed as an expensive table scan|||The table may have up to 10 million rows. And the conditions may be only up
to 30s. So it sounds a low selective.
"Tibor Karaszi" wrote:

> 1 is probably cheaper. But it depends on whether you have a WHERE clause a
nd the selectivity, how
> efficient indexes you have to drive that where clause and also how many ro
ws you modify.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
>|||I'm not sure what you mean by "And the conditions may be only up to 30s". Ar
e you saying that the
SELECT statement will typically return about 30 rows from a table with 10,00
0,000 rows? If so, the
query has very high selectivity (it returns relatively few rows). In that ca
se, I'd go with my
original recommendation. If you cannot create that index as a clustered inde
x, a non-clustered index
should be efficient as well (thanks to the high selectivity).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:5D4A2719-76F6-46BD-9249-94CFA8BE8656@.microsoft.com...
> The table may have up to 10 million rows. And the conditions may be only u
p
> to 30s. So it sounds a low selective.
> "Tibor Karaszi" wrote:
>

multiple schema report

tnx in advance for any help.
I have a multiple enterprise system in which every enterprise is being
modeled as a schema in the DB. This means that schemas are identical in
structure and when you connect to one schema, you are connecting to one
logical enterprise.
Now, I need to write a bunch of reports with the ability to connect
indiferently to on Enterprise or another (the user selects against which
Enterprise he wants to run the report to).
RS allows to define one or more Datasets for every report. But that has to
be an SQL instruction with schema qualifying "hardcoded" for every table you
use.
I've thought about dynamic SQL inside a stored procedure, and the enterprise
(schema) being passed as a parameter, but that's not such an elegant
solution.
So any suggestion will be gratefully welcome...
regardsIn RS 2005 you can base a data source on an expression. This sounds like
your scenario is exactly what this feature was put in for.
From Books Online:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> tnx in advance for any help.
> I have a multiple enterprise system in which every enterprise is being
> modeled as a schema in the DB. This means that schemas are identical in
> structure and when you connect to one schema, you are connecting to one
> logical enterprise.
> Now, I need to write a bunch of reports with the ability to connect
> indiferently to on Enterprise or another (the user selects against which
> Enterprise he wants to run the report to).
> RS allows to define one or more Datasets for every report. But that has to
> be an SQL instruction with schema qualifying "hardcoded" for every table
> you
> use.
> I've thought about dynamic SQL inside a stored procedure, and the
> enterprise
> (schema) being passed as a parameter, but that's not such an elegant
> solution.
> So any suggestion will be gratefully welcome...
> regards
>
begin 666 copycode.gif
M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
MT?_______P``````````````````````````````````````````````````
M`````````````````````````````````````````````````"'Y! $``&8`
M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
`
end|||Hello Edmundo,
Have you got the the problem sorted? I am having the same trouble. In my
case, I need to select a db schema at the run time. Here are methods I've
tried:
1. create a parameter to contain the db name. Add in the ODBC connection
string "database=Parameters!db_name.Value", this doesnt work. The alert
"database-Parameters!db_name.Value doesn't exist" turns up.
2. delete the "database = XXX" part from the connection string, so no
specific database in the DB server will be connected, a connection to the DB
server itself is established. create a parameter to contain the db name. in
the query use ?.attr_name=XXX. By doing this, I expect the parameter can
specify the DB name at the run time. this doesnt work either.
So if you have the solution, could you please post it up. It'll be really
appreciated!
cheers,
P
"Bruce L-C [MVP]" wrote:
> In RS 2005 you can base a data source on an expression. This sounds like
> your scenario is exactly what this feature was put in for.
> From Books Online:
> Data Source Expressions
> You can put an expression into a connection string to allow users to select
> the data source at run time. For example, suppose a multinational firm has
> data servers in several countries. With an expression-based connection
> string, a user who is running a sales report can select a data source for a
> particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a report
> parameter named ServerName:
> Copy Code
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
>
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
>
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
>
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
>
> e.. Be sure that the list of data sources shares the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
>
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Edmundo J. Davila" <edavila@.monisa.com> wrote in message
> news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> > tnx in advance for any help.
> >
> > I have a multiple enterprise system in which every enterprise is being
> > modeled as a schema in the DB. This means that schemas are identical in
> > structure and when you connect to one schema, you are connecting to one
> > logical enterprise.
> >
> > Now, I need to write a bunch of reports with the ability to connect
> > indiferently to on Enterprise or another (the user selects against which
> > Enterprise he wants to run the report to).
> >
> > RS allows to define one or more Datasets for every report. But that has to
> > be an SQL instruction with schema qualifying "hardcoded" for every table
> > you
> > use.
> >
> > I've thought about dynamic SQL inside a stored procedure, and the
> > enterprise
> > (schema) being passed as a parameter, but that's not such an elegant
> > solution.
> >
> > So any suggestion will be gratefully welcome...
> >
> > regards
> >
>
>