about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

sql

Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,
Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thank
s,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

A->B, A->C relationships without using subreports

Hi all:

This is simplified version of my problem:

There are 3 tables A, B, and C. The relationships are: A(one)->B(many), A(one)->C(many). (there is no direct relationship between B and C)

I want to create a report to list each record in A, followed by records associated to the A’s record in B, then records associated to the A’s record in C. Both B, C records should be in a table format. Can I do it without using subreports? I have performance problems with subreports in a large report(thousand records in table A). RS documentation suggests replacing subreport with data region will help the performance.

Thanks in advance!

Here is an example:

Table definitions: (Pet table and Dependant table have not direct relationship)

Employee Table has column: EmpNo

Dependant Table has 2 columns:

EmpNo

DependantName

Pet Table has 2 columns:

EmpNo

PetName

Relationships: Employee(one) -> Dependant(Many)

Employee(one) -> Pet (Many)

Report Format:

EmpNo: ###

(this is a Reporting Service table)

Dependant Name 1 for EmpNo ###

Dependant Name 2 for EmpNo ###

Dependant Name 3 for EmpNo ###

Dependant Name 4 for EmpNo ###

(this is a Reporting Service table)

Pet Name 1 for EmpNo ###

Pet Name 2 for EmpNo ###

Not sure I completely understand your situation, but in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport.

If you have performance problems using the dataset region method because of a large volume of records, then programming that can be a little tricky. I had a similar situation where, for performance reasons, I HAD to filter/parameterize the dataset before joining it because of the number of records (300,000+) in one of the tables. To pull this off I used a (sql server 2000) table type user-defined function (kinda a view that allows parameters) and joined that in the report. You will also have to rewrite your query to bring all the data together in one dataset.

A Wizard to create Triggers

Hi Guys,

Is there any Wizard or software or tools that I can use to create Triggers for my tables ... !??

It is for MSSQL 2005 database

Thanks for your help,
Mehdi

What kind of triggers do you want ?|||

In Managenent studio try View->template explorer and next select Trigger from templates and select trigger you wont.

a way to export data from table to flat file

Hi there!
I know bulk insert for importing data into tables and bcp for both
importing, exporting data between tables..from flat files...ok..
But is there any way to export data using a SQL statement and a format
file (need to export in kind of csv) just like bcp ? a kind of *bulk
output*...:)
any idea'
thanks a lot
++
Vincelook at DTS...|||Vince
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\FolderName;','select * from Text1.txt')
"Vince .>" <vincent@.<remove> wrote in message
news:ro7751hjpvc9l0lks58pds2dda1prok8lu@.
4ax.com...
> Hi there!
> I know bulk insert for importing data into tables and bcp for both
> importing, exporting data between tables..from flat files...ok..
> But is there any way to export data using a SQL statement and a format
> file (need to export in kind of csv) just like bcp ? a kind of *bulk
> output*...:)
> any idea'
> thanks a lot
> ++
> Vince
>

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it??
Thx in Adv.
Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>sql

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
BalaHave a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
--
Regards
Barry McAuslin
----
--
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
--
David Gugick
Imceda Software
www.imceda.com

a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
Bala
Have a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
Regards
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>
|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
David Gugick
Imceda Software
www.imceda.com

A trigger that backups data from several tables...?

Hi,
I have a problem... I want to backup data to a backuptable from a table
that has a relation to an another table. And the backup should be done
with a trigger when an update or delete occurs... What I mean is that
if I have a table A with columns of NameID, Name and LinkID... then the
LinkID column has a reference to another table B, which has columns of
LinkID, LinkName. I know how to get a backup from table A or Table B...
but I don't know how to get a backup from both of the tables at the
same time to a single table that would consist of the following
information: NameID, Name, LinkID and LinkName. (Should there be a use
of joins in the trigger or how do I get all the required info?)
Table A
======
_____________________
| NameID | Name | LinkID |
---
| 1 | Billy | 1 |
Table B
======
_________________
| LinkID | LinkName |
--
| 1 | blaa blaa |
.... and what I want in the backuptable:
_______________________________
| NameID | Name | LinkID | LinkName |
----
| 1 | Billy | 1 | blaa blaa |
Thanx in advance!Here is an example of an audit trigger I use:
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
You can also join the [deleted] or [inserted] tables with other tables like
so:
insert into EmployeeAudit
select
deleted.a,
deleted.b,
TableB.c,
TableB.d
from deleted
join TableB
on TableB.EmployeeID = deleted.EmployeeID
However, if TableB is a code table and you just want to include descriptions
in the audit, then perhaps you could implement the audit table with the same
structure as the updated table, and then implement a view that joins the
audit table with the related table.
"patte" <fipatte@.luukku.com> wrote in message
news:1138044677.419112.215820@.g44g2000cwa.googlegroups.com...
> Hi,
> I have a problem... I want to backup data to a backuptable from a table
> that has a relation to an another table. And the backup should be done
> with a trigger when an update or delete occurs... What I mean is that
> if I have a table A with columns of NameID, Name and LinkID... then the
> LinkID column has a reference to another table B, which has columns of
> LinkID, LinkName. I know how to get a backup from table A or Table B...
> but I don't know how to get a backup from both of the tables at the
> same time to a single table that would consist of the following
> information: NameID, Name, LinkID and LinkName. (Should there be a use
> of joins in the trigger or how do I get all the required info?)
> Table A
> ======
> _____________________
> | NameID | Name | LinkID |
> ---
> | 1 | Billy | 1 |
> Table B
> ======
> _________________
> | LinkID | LinkName |
> --
> | 1 | blaa blaa |
>
> .... and what I want in the backuptable:
> _______________________________
> | NameID | Name | LinkID | LinkName |
> ----
> | 1 | Billy | 1 | blaa blaa |
>
> Thanx in advance!
>|||thanks!!! :)
...I got it to work just as I wanted to.|||Keep in mind NOT to use the asteriks if you are goind in productional
code. Especially in triggers that can cause a tremendous trouble. If
the schema is modified in some way (lets say at the source table of the
trigger and you didn=B4t extend the schema at the backup table, every
transaction made on the source table will fail. better use named
columns in here.
HTH, Jens Suessmeyer.

A Trigger Question

hello there, i am trying to write a swl trigger for update in my database
application...
there are 2 tables like this:
Table1: Stock
Column1: StockCode
Column2: Stockquantity
Table2: Group
Column1: GroupCode
Column2: StockCode
Column3: StockQuantity
Column4: GroupQuantity
Column5: OrderAmount
now i wrote a sql trigger like this:
----
--
create trigger StockUpdate
on [Group]
for update
as
declare @.Code char(10)
declare @.SCode char(10)
declare @.sq decimal(9)
declare @.Ch decimal(9)
set @.Code = (select GroupCode from Inserted)
set @.SCode = (Select StockCode from Inserted)
set @.sq = (select StockQuantity from Inserted)
set @.ch = (select OrderAmount from Inserted)
begin
update Stock
set StockQuantity = STockQuantity - @.sq*@.ch
from Stock
where Stock.Stockcode = @.Scode
end
----
--
now ets say i have tables filles like this:
Table1: Stock
StockCode: StockQuantity:
Stock001 10000
Stock002 8908
Stock003 20000
Table2:
GroupCode: StockCode: StockQuantity:
GroupQuantity: OrderAmount:
Group001 Stock001 20
1 0
Group001 Stock002 20
1 0
Group001 Stock003 1
1 0
Group002 Stock001 5
2 0
now when i write this command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
The trigger will increase the GroupQuantity Value by 2;
and also decrease the StockQuantity by (2*5 = 10) so the stockquantity will
be 9990. this trigger works quite well if theres only
one 'Group002' in the second table. but i want this trigger to work for all
columns when i write the command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
i get this error:
"Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
..
so what can i do to make this trigger for when theres more then 1 querry
results'
THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
EFFOERTS...
have a nice day!Serdar C.,
A trigger is not fired by each row affected, instead it is fired by each dml
operation (insert, update, delete), so you have to take in mind that
multirows can be affected bby the operation. Supposing that column
[StockCode] is the pk of table [Stock], then try:
update Stock
set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmount
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Serdar C." wrote:

> hello there, i am trying to write a swl trigger for update in my database
> application...
> there are 2 tables like this:
> Table1: Stock
> Column1: StockCode
> Column2: Stockquantity
> Table2: Group
> Column1: GroupCode
> Column2: StockCode
> Column3: StockQuantity
> Column4: GroupQuantity
> Column5: OrderAmount
> now i wrote a sql trigger like this:
> ----
--
> create trigger StockUpdate
> on [Group]
> for update
> as
> declare @.Code char(10)
> declare @.SCode char(10)
> declare @.sq decimal(9)
> declare @.Ch decimal(9)
> set @.Code = (select GroupCode from Inserted)
> set @.SCode = (Select StockCode from Inserted)
> set @.sq = (select StockQuantity from Inserted)
> set @.ch = (select OrderAmount from Inserted)
> begin
> update Stock
> set StockQuantity = STockQuantity - @.sq*@.ch
> from Stock
> where Stock.Stockcode = @.Scode
> end
> ----
--
>
>
> now ets say i have tables filles like this:
> Table1: Stock
> StockCode: StockQuantity:
> Stock001 10000
> Stock002 8908
> Stock003 20000
> Table2:
> GroupCode: StockCode: StockQuantity:
> GroupQuantity: OrderAmount:
>
> Group001 Stock001 20
> 1 0
> Group001 Stock002 20
> 1 0
> Group001 Stock003 1
> 1 0
> Group002 Stock001 5
> 2 0
>
> now when i write this command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
> The trigger will increase the GroupQuantity Value by 2;
> and also decrease the StockQuantity by (2*5 = 10) so the stockquantity wil
l
> be 9990. this trigger works quite well if theres only
> one 'Group002' in the second table. but i want this trigger to work for al
l
> columns when i write the command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
> i get this error:
> "Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated."
> ...
> so what can i do to make this trigger for when theres more then 1 querry
> results'
>
> THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
> EFFOERTS...
> have a nice day!
>
>
>|||Correction,
Sorry, I am making same mistake. We have to use an aggregate function here.
update Stock
set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
i.OrderAmount)
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Alejandro Mesa" wrote:
> Serdar C.,
> A trigger is not fired by each row affected, instead it is fired by each d
ml
> operation (insert, update, delete), so you have to take in mind that
> multirows can be affected bby the operation. Supposing that column
> [StockCode] is the pk of table [Stock], then try:
> update Stock
> set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmou
nt
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode = stock.Stockco
de)
> go
>
> AMB
> "Serdar C." wrote:
>|||Thanx so much... it is working now...
god bless you :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:563B331D-1FE1-43F5-B00C-2AA7E1EC9750@.microsoft.com...
> Correction,
> Sorry, I am making same mistake. We have to use an aggregate function
> here.
> update Stock
> set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
> i.OrderAmount)
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode =
> stock.Stockcode)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>

A transport-level error has occurred when receiving results from S

I am setting up a replication of a database which has 364 tables. The size of
this database is close to 150 GB.
I have one server as publisher.
I have another server which i am using for distributor as well as
subscriber. Both servers are seperate physical servers.
I tried replicating 2 tables, I don;' see any issue. They replicate with no
issue. However, when I try to replicate all tables I get this error on
snapshot agent.
Please advise if any one has already seen this problem. My both servers are
on SQL 2005 SP2.
Error messages:
Message: A transport-level error has occurred when receiving results from
the server. (provider: TCP Provider, error: 0 - The specified network name is
no longer available.)
Command Text:
set nocount on
declare @.source_object_id int
declare @.sync_object_id int
set @.source_object_id = object_id(quotename(@.source_object_schema) + N'.' +
quotename(@.source_object_name))
set @.sync_object_id = object_id(quotename(@.sync_object_schema) + N'.' +
quotename(@.sync_object_name))
declare @.partitioning_column sysname
declare @.partitioning_column_type sysname
declare @.qualified_source_object_name nvarchar(600)
declare @.partitioning_column_collation sysname
declare @.partitioning_index_name sysname
set @.qualified_source_object_name = quotename(@.source_object_schema) + N'.'
+ quotename(@.source_object_name)
set @.partitioning_column = null
set @.partitioning_column_type = null
set @.partitioning_index_name = null
select @.partitioning_column = sc.name,
@.partitioning_column_type = st.name,
@.partitioning_column_collation = sc.collation,
@.partitioning_index_name = si.name
from sysindexes si
inner join syscolumns sc
on index_col(@.qualified_source_object_name, si.indid, 1) = sc.name
inner join systypes st
on sc.xtype = st.xusertype
where si.id = @.source_object_id
and sc.id = @.sync_object_id
and si.indid = 1
and st.name in (N'uniqueidentifier', N'bit', N'tinyint', N'smallint',
N'int', N'smalldatetime', N'real', N'money', N'datetime', N'float', N'bit',
N'decimal', N'numeric', N'smallmoney', N'bigint', N'varbinary', N'varchar',
N'binary', N'char', N'timestamp', N'nvarchar', N'nchar')
and (@.use_primary_key_only = 0 or si.status & 2048 = 2048)
if @.partitioning_column is not null
begin
select @.partitioning_index_name, @.partitioning_column,
@.partitioning_column_type, @.partitioning_column_collation
dbcc show_statistics(@.qualified_source_object_name,
@.partitioning_index_name)
end
Parameters: @.source_object_name = E_QRTZ_TRIGGER_LISTENERS
@.source_object_schema = dbo
@.sync_object_name = syncobj_0x3238314643373630
@.sync_object_schema = dbo
@.use_primary_key_only = 1
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException e,
SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteReader(SqlCommand
command, Int32 queryTimeout, CommandBehavior commandBehavior)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32
queryTimeout, CommandBehavior commandBehavior)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.GatherBaseTableSta tistics()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioningUsingKeyDistributionHistogram(BaseArtic leWrapper article, Boolean usePrimaryKeyOnly)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioning(BaseArticleWrapper
article, Boolean usePrimaryKeyOnly)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .BcpLoadHintAndPartitioningResolutionWorkerThreadP rovider.DoWork(WorkItem workItem)
at
Microsoft.SqlServer.Replication.WorkerThread.NonEx ceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.Agent ThreadProc()
at
Microsoft.SqlServer.Replication.AgentCore.BaseAgen tThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 64)
Get help: http://help/64
Server NTFWEPSQLT1, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.) (Source: MSSQLServer, Error number: 64)
Get help: http://help/64
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
You had a network failure in the middle of your snapshot distribution. This
should clear the next time it runs. You might want to run ping -t to watch
to see how lossy your link is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B877BD9F-EE3C-456E-87A7-64BB17FC5C11@.microsoft.com...
>I am setting up a replication of a database which has 364 tables. The size
>of
> this database is close to 150 GB.
> I have one server as publisher.
> I have another server which i am using for distributor as well as
> subscriber. Both servers are seperate physical servers.
> I tried replicating 2 tables, I don;' see any issue. They replicate with
> no
> issue. However, when I try to replicate all tables I get this error on
> snapshot agent.
> Please advise if any one has already seen this problem. My both servers
> are
> on SQL 2005 SP2.
> --
> Error messages:
> Message: A transport-level error has occurred when receiving results from
> the server. (provider: TCP Provider, error: 0 - The specified network name
> is
> no longer available.)
> Command Text:
> set nocount on
> declare @.source_object_id int
> declare @.sync_object_id int
> set @.source_object_id = object_id(quotename(@.source_object_schema) + N'.'
> +
> quotename(@.source_object_name))
> set @.sync_object_id = object_id(quotename(@.sync_object_schema) + N'.' +
> quotename(@.sync_object_name))
> declare @.partitioning_column sysname
> declare @.partitioning_column_type sysname
> declare @.qualified_source_object_name nvarchar(600)
> declare @.partitioning_column_collation sysname
> declare @.partitioning_index_name sysname
> set @.qualified_source_object_name = quotename(@.source_object_schema) +
> N'.'
> + quotename(@.source_object_name)
> set @.partitioning_column = null
> set @.partitioning_column_type = null
> set @.partitioning_index_name = null
> select @.partitioning_column = sc.name,
> @.partitioning_column_type = st.name,
> @.partitioning_column_collation = sc.collation,
> @.partitioning_index_name = si.name
> from sysindexes si
> inner join syscolumns sc
> on index_col(@.qualified_source_object_name, si.indid, 1) = sc.name
> inner join systypes st
> on sc.xtype = st.xusertype
> where si.id = @.source_object_id
> and sc.id = @.sync_object_id
> and si.indid = 1
> and st.name in (N'uniqueidentifier', N'bit', N'tinyint',
> N'smallint',
> N'int', N'smalldatetime', N'real', N'money', N'datetime', N'float',
> N'bit',
> N'decimal', N'numeric', N'smallmoney', N'bigint', N'varbinary',
> N'varchar',
> N'binary', N'char', N'timestamp', N'nvarchar', N'nchar')
> and (@.use_primary_key_only = 0 or si.status & 2048 = 2048)
> if @.partitioning_column is not null
> begin
> select @.partitioning_index_name, @.partitioning_column,
> @.partitioning_column_type, @.partitioning_column_collation
> dbcc show_statistics(@.qualified_source_object_name,
> @.partitioning_index_name)
> end
> Parameters: @.source_object_name = E_QRTZ_TRIGGER_LISTENERS
> @.source_object_schema = dbo
> @.sync_object_name = syncobj_0x3238314643373630
> @.sync_object_schema = dbo
> @.use_primary_key_only = 1
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e,
> SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteReader(SqlCommand
> command, Int32 queryTimeout, CommandBehavior commandBehavior)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
> commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32
> queryTimeout, CommandBehavior commandBehavior)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
> commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.GatherBaseTableSta tistics()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioningUsingKeyDistributionHistogram(BaseArtic leWrapper
> article, Boolean usePrimaryKeyOnly)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioning(BaseArticleWrapper
> article, Boolean usePrimaryKeyOnly)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .BcpLoadHintAndPartitioningResolutionWorkerThreadP rovider.DoWork(WorkItem
> workItem)
> at
> Microsoft.SqlServer.Replication.WorkerThread.NonEx ceptionBasedAgentThreadProc()
> at Microsoft.SqlServer.Replication.WorkerThread.Agent ThreadProc()
> at
> Microsoft.SqlServer.Replication.AgentCore.BaseAgen tThread.AgentThreadProcWrapper()
> (Source: MSSQLServer, Error number: 64)
> Get help: http://help/64
> Server NTFWEPSQLT1, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server.
> (provider: TCP Provider, error: 0 - The specified network name is no
> longer
> available.) (Source: MSSQLServer, Error number: 64)
> Get help: http://help/64
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
|||Hilary - Why then it always works when I replicate any small database or same
data base with few articles. I see no time outs when ping during replication.
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Hilary Cotter" wrote:

> You had a network failure in the middle of your snapshot distribution. This
> should clear the next time it runs. You might want to run ping -t to watch
> to see how lossy your link is.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B877BD9F-EE3C-456E-87A7-64BB17FC5C11@.microsoft.com...
>
>
|||It really looks like a network failure somewhere. The ping-t command should
reveal network hiccups occurring during the snapshot application.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:C3B863FA-723D-4781-9A99-99FCA2DDB364@.microsoft.com...[vbcol=seagreen]
> Hilary - Why then it always works when I replicate any small database or
> same
> data base with few articles. I see no time outs when ping during
> replication.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Hilary Cotter" wrote:
|||Actually, the SQL Server could think it's under a DOS attack and start
killing off connections.
Check out section 5.1 of
http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm#sse_dbengine
for more information.
On Mar 19, 1:02 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It really looks like a network failure somewhere. The ping-t command should
> reveal network hiccups occurring during the snapshot application.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Moh" <mab...@.Pier1.com> wrote in message
> news:C3B863FA-723D-4781-9A99-99FCA2DDB364@.microsoft.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

a too long query MAX(CASE WHEN

Hello

I am using an allready Full database MS SQL 2000

my 3 tables -->

Report :
ReportID (PK)
RName
RValue

Product :
PName
Category
ReportID (FK)

Infos :
IComments
IVaLue

my query (to get a new table with only columns, or a .NETcollection) -->

SELECT

Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,

Infos.Commentar AS IC,

MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50,
MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54,
MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78,
MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540,
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421

FROM

Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue

WHERE (Report.ReportID = 10)

GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.IComments

Report.ReportID = Product.ReportID --> Primary Key to Foreign Key
Report.RValue = Infos.IValue --> only on full text (100 char)

they are not indexed

in Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infos

it can be very long
how can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)

thank youHi

This is some sort of odd pivot but I don't understand what your problem is or what you would like to accomplish. Please could you elaborate?|||on 3 tables i have columns or rows , i want to get only columns
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421 makes a column from a row|||It does indeed. So what is the problem? :)

EDIT - oh hang on - do you mean you want zero rows??|||i want to find a better way if exists|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH|||you think a union will be really faster ?|||you think a union will be really faster ?I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.|||ok i try it

thank you|||I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.

Just FYI,
Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries.
The data types must be compatible|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH

UNION ALL if no dupes.|||As mentioned in my next post :)