Can not INSERT to the second table

Hi, everyone, I write a SP and the first table can be inserted succesfully, but the second fails.
However there are not any error message to occur, just passes it. Anyone can give me a tip?
I am a begineer of SQL.

CREATE PROCEDURE sp_FinalizePurchase
 @.Inv_shipAddress as varchar(50),
 @.Inv_shipCity as VarChar(50),
 @.Inv_shipProvince as VarChar(20),
 @.Inv_shipPostalCode as Char(6),
 @.Inv_shipPhone as VarChar(10),
 @.INV_billAddress as VarChar(50),
 @.INV_billCity as VarChar(50),
 @.INV_billProvince as VarChar(20),
 @.INV_billPostalCode as Char(6),
 @.INV_billPhone as Char(10),
 @.CUS_customerID as Int,
 @.Inv_invoiceID as Int Output
AS
-- STEP 1
--Do the Insert
 INSERT tblInvoice
 (Inv_date,
 Inv_shipAddress,
 Inv_shipCity,
 Inv_shipProvince,
 Inv_shipPostalCode,
 Inv_shipPhone,
 INV_billAddress,
 INV_billCity,
 INV_billProvince,
 INV_billPostalCode,
 INV_billPhone,
 INV_status,
 CUS_customerID
 )
 VALUES
 (getdate(),
 @.Inv_shipAddress,
 @.Inv_shipCity,
 @.Inv_shipProvince,
 @.Inv_shipPostalCode,
 @.Inv_shipPhone,
 @.INV_billAddress,
 @.INV_billCity,
 @.INV_billProvince,
 @.INV_billPostalCode,
 @.INV_billPhone,
 1,
 @.CUS_customerid)
--FIND OUT WHAT THEIR NEW ID IS
 SELECT @.Inv_invoiceid = @.@.identity
-- STEP 2:
--Do the Insert
 INSERT Into tblInvoiceLine
 (INV_invoiceID,
 PRD_productID,
 ILN_description,
 ILN_quantity,
 ILN_unitprice
 )
 SELECT DISTINCT
 @.Inv_invoiceid,
 tblBasket.productID,
 tblMovie.mov_description,
 tblBasket.quantity,
 tblProduct.PRD_Price
 FROM tblBasket, tblProduct, tblMovie
 WHERE tblBasket.productID=tblProduct.PRD_ProductID

I suspect it's this:

 SELECT DISTINCT
 @.Inv_invoiceid,
 tblBasket.productID,
 tblMovie.mov_description,
 tblBasket.quantity,
 tblProduct.PRD_Price
 FROM tblBasket, tblProduct, tblMovie
 WHERE tblBasket.productID=tblProduct.PRD_ProductID

You're not giving it any join criteria.|||Thank your hints, I have added another join criteria as follows but it still does not work correctly,just skips it.

SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket, tblProduct, tblMovie
WHERE tblBasket.productID=tblProduct.PRD_ProductID
AND tblProduct.MOV_movieID=tblMovie.MOV_MovieID

Any suggestions I can get from your guys?|||remove the@. sign at the second line.

hth|||Does the Select query work in Query Analyzer? If the select query works, then add the insert to the beginning of it and see if that works. If that doesn't, it's either because the select query returns no records, there is a syntax error, or some constraint has been violated. QA will give you a hint in any of those cases. Try that.

Also, a more standard syntax for joins is:

SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket b
join tblProduct p
 on b.productID = p.PRD_ProductID
join tblMovie m
 on p.MOV_movieID = m.MOV_MovieID

|||First, I would like to say I have solved the problem. Thank your guys's hints.
Second, I want to share my experience with your guys.

To upstairs 2 friends:

ndinakar says should "remove the @. sign at the second line.", I have to say this is wrong. Because the parameter @.Inv_invoiceid come from the the first table and it is a variable.

As I have told, the SP runs properly in QA, but it does not insert the data into the table. Paraigh analyse the reasons, "it's either because the select query returns no records, there is a syntax error, or some constraint has been violated." what you said is partly correct. This is really a "syntax error" but QA skips it. I look into it carefully and eventually I find the reason occurs in the tblMovie.mov_description field. In tblmovie, I claim the varchar length 255, but in tblinvoiceline I claim the varchar length is 50. It passes the QA but occurs error in runtime.

I think this is syntax error but QA seems no regard it as an error and skips it. In my opinion, this is a needed improvement aspects in SQL Server.

Can it be a bug in SQL CE?

Hi!

I use SQL CE with VS.NET 2003. I find the following bug second time.

The table has an "ID int IDENTITY(0,1) PRIMARY KEY,". That is my row identity.

I add rows to the table, then I made some select and update queries. Then I realized that the ID order not in the general order (from 0 to ........)

For example: 6,7,8,0,1,2,3,4,5.

Of course row 6,7 and 8 was added the very last.

The content of each row is not mixed, only the ID order.

Is it a very confused, because we develop mobile invoice programs for PDAs.

What I did wrong?

Thank you!

Do you mean rows are not sorted by ID should you retrieve them with select or IDs are created not in order of insertions? Say, you have integer column in addition to ID, and you've inserted numbers from 0 to 3 into it. Do you get data like this:

2 2

1 1

0 0

3 3

Or like this:

2 0

1 1

0 2

3 3

|||

Duplicate thread from the same user: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=229153&SiteID=1

Follow only the above given thread. This thread will not be monitored any more.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Can I pick your brains for a second? transferring a large database problem.

Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
Scott
have you tried to export your tables into flat files, compact these files
(using winzip or anything else)
copy the files and load these files on the destination server.
you could reduce the tranfert from 30gb to 1Gb.
more complex then a DTS pump, but this could be a good solution.
another way...
you can use replication.
or you can create your own "replication" model, I mean add triggers on your
table to log changes into another table in your source system, then load
these changes only every night.
Have you a "last update date" colum in your source tables?
"Scott M" <scott@.nospam.com> wrote in message
news:ezl3f2saFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This database is a data dump of our billing system data, the tables are
> dropped ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables with the primary keys from each table and a binary checksum of a
> row, then complaining it to a copy of the database from the day before.
> Then I generate SQL statements for the updates (Actually deletes and
> inserts). All of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will be in a nice, compact binary format. ANY help would be VERY
> appreciated!!! Thanks much!
> Scott

Can I pick your brains for a second? transferring a large database problem.

Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
Scotthave you tried to export your tables into flat files, compact these files
(using winzip or anything else)
copy the files and load these files on the destination server.
you could reduce the tranfert from 30gb to 1Gb.
more complex then a DTS pump, but this could be a good solution.
another way...
you can use replication.
or you can create your own "replication" model, I mean add triggers on your
table to log changes into another table in your source system, then load
these changes only every night.
Have you a "last update date" colum in your source tables?
"Scott M" <scott@.nospam.com> wrote in message
news:ezl3f2saFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This database is a data dump of our billing system data, the tables are
> dropped ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables with the primary keys from each table and a binary checksum of a
> row, then complaining it to a copy of the database from the day before.
> Then I generate SQL statements for the updates (Actually deletes and
> inserts). All of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will be in a nice, compact binary format. ANY help would be VERY
> appreciated!!! Thanks much!
> Scott

Can I pick your brains for a second? transferring a large database problem

-=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
now -=-=-
Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
ScottHello Scott,
It is not easy to use a DTS package to do this job. I think database
replication is more appropriate for this situation. You may consider
transaction or merge replication according to your requirements.
If you still want to consider DTS, lookup query feature might be helpful
http://msdn.microsoft.com/library/d...-us/dtssql/dts_
addf_misc_2dix.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Scott M" <scott_M@.nospam.nospam>
| Subject: Can I pick your brains for a second? transferring a large
database problem
| Date: Mon, 6 Jun 2005 17:33:03 -0500
| Lines: 72
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: adsl-068-209-157-050.sip.lft.bellsouth.net
68.209.157.50
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1784
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| -=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
| now -=-=-
|
| Okay, I have a problem that I need some suggestions with.
|
| I have a 30 Gig database that I need to sync across a T1 every night.
This
| database is a data dump of our billing system data, the tables are dropped
| ad re-created every night.
|
| I can do a very long and laborious process on the server of creating
tables
| with the primary keys from each table and a binary checksum of a row, then
| complaining it to a copy of the database from the day before. Then I
| generate SQL statements for the updates (Actually deletes and inserts).
All
| of this on the source server.
|
| I'd like to compact this down using a DTS package on the destination side,
| however I can't use a dynamic query in the "openRowSet" command to get
only
| the row that I want.
|
| Here is what I'm doing (Just for one table, I'll have data driven steps
for
| each table in my DB)
|
| insert into @.tempChecker
| select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
| chgno from charge_t')
|
| Declare Charge_t_Cursor Cursor
| FOR
| select
| a.chgno
| from
| (select
| BSum,
| chgno
| from @.tempChecker
| ) a
| where
| not exists
| (
| select 'x'
| from charge_t b
| where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
| )
|
| Once I get all the chgno's (The key in the table) I want to only pull back
| the data that has changes. So I have this (Which I know I can't do put
you
| get the idea)
|
| Open Charge_t_Cursor
|
| Fetch next from Charge_t_cursor into @.ChgNo
|
| While @.@.FETCH_STATUS = 0
| BEGIN
| insert into @.ChargeT select *
| from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
| cast(isnull(@.ChgNo, 0) as varchar)))
|
| Fetch next from Charge_t_cursor into @.ChgNo
| END
|
| Close Charge_t_cursor
| deallocate Charge_t_cursor
|
| select * from @.ChargeT
|
| this way I'm only pulling over the wire the data that is updated and it
will
| be in a nice, compact binary format. ANY help would be VERY
appreciated!!!
| Thanks much!
|
| Scott
|
||||Would replication work if the source tables are dropped every time it's
built?
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:s73X27waFHA.3928@.TK2MSFTNGXA01.phx.gbl...
> Hello Scott,
> It is not easy to use a DTS package to do this job. I think database
> replication is more appropriate for this situation. You may consider
> transaction or merge replication according to your requirements.
> If you still want to consider DTS, lookup query feature might be helpful
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_[/ur
l]
> addf_misc_2dix.asp
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | From: "Scott M" <scott_M@.nospam.nospam>
> | Subject: Can I pick your brains for a second? transferring a large
> database problem
> | Date: Mon, 6 Jun 2005 17:33:03 -0500
> | Lines: 72
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | format=flowed;
> | charset="iso-8859-1";
> | reply-type=original
> | Content-Transfer-Encoding: 7bit
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
> | Message-ID: <ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.datawarehouse
> | NNTP-Posting-Host: adsl-068-209-157-050.sip.lft.bellsouth.net
> 68.209.157.50
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.datawarehouse:1784
> | X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
> |
> | -=-=- Sorry for the dup post, I'm posting it under my MSDN universal
> acct
> | now -=-=-
> |
> | Okay, I have a problem that I need some suggestions with.
> |
> | I have a 30 Gig database that I need to sync across a T1 every night.
> This
> | database is a data dump of our billing system data, the tables are
> dropped
> | ad re-created every night.
> |
> | I can do a very long and laborious process on the server of creating
> tables
> | with the primary keys from each table and a binary checksum of a row,
> then
> | complaining it to a copy of the database from the day before. Then I
> | generate SQL statements for the updates (Actually deletes and inserts).
> All
> | of this on the source server.
> |
> | I'd like to compact this down using a DTS package on the destination
> side,
> | however I can't use a dynamic query in the "openRowSet" command to get
> only
> | the row that I want.
> |
> | Here is what I'm doing (Just for one table, I'll have data driven steps
> for
> | each table in my DB)
> |
> | insert into @.tempChecker
> | select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*)
> BSum,
> | chgno from charge_t')
> |
> | Declare Charge_t_Cursor Cursor
> | FOR
> | select
> | a.chgno
> | from
> | (select
> | BSum,
> | chgno
> | from @.tempChecker
> | ) a
> | where
> | not exists
> | (
> | select 'x'
> | from charge_t b
> | where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> | )
> |
> | Once I get all the chgno's (The key in the table) I want to only pull
> back
> | the data that has changes. So I have this (Which I know I can't do put
> you
> | get the idea)
> |
> | Open Charge_t_Cursor
> |
> | Fetch next from Charge_t_cursor into @.ChgNo
> |
> | While @.@.FETCH_STATUS = 0
> | BEGIN
> | insert into @.ChargeT select *
> | from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> | cast(isnull(@.ChgNo, 0) as varchar)))
> |
> | Fetch next from Charge_t_cursor into @.ChgNo
> | END
> |
> | Close Charge_t_cursor
> | deallocate Charge_t_cursor
> |
> | select * from @.ChargeT
> |
> | this way I'm only pulling over the wire the data that is updated and it
> will
> | be in a nice, compact binary format. ANY help would be VERY
> appreciated!!!
> | Thanks much!
> |
> | Scott
> |
> |
>|||have you try to create a local copy, then zip it, send the file by FTP,
unzip and load the data?
For example, you export to CSV files the new data from the source server.
A CSV file can be loaded in a database very quickly using bulk insert
commands.
Or you could detach a database, zip it, copy, reattach on the target server.
Or do the same with a backup file.
I have a demo database which is a 5gb of data, when I compress it, the size
is only 150mb and the time to transfert is excellent. (10min to compress,
10min to transfert, 5 to expand; instead-of 2h of transferts)
"Scott M" <scott_M@.nospam.nospam> wrote in message
news:ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> -=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
> now -=-=-
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This
> database is a data dump of our billing system data, the tables are dropped
> ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables
> with the primary keys from each table and a binary checksum of a row, then
> complaining it to a copy of the database from the day before. Then I
> generate SQL statements for the updates (Actually deletes and inserts).
> All
> of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only
> the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for
> each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you
> get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will
> be in a nice, compact binary format. ANY help would be VERY
> appreciated!!!
> Thanks much!
> Scott
>|||Yes, the files are too large to zip in a decent amount of time (The database
files)
however we are zipping and transferring the flat files that the UNIX box is
creating.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:Omk62fNdFHA.3620@.TK2MSFTNGP09.phx.gbl...
> have you try to create a local copy, then zip it, send the file by FTP,
> unzip and load the data?
> For example, you export to CSV files the new data from the source server.
> A CSV file can be loaded in a database very quickly using bulk insert
> commands.
> Or you could detach a database, zip it, copy, reattach on the target
> server.
> Or do the same with a backup file.
> I have a demo database which is a 5gb of data, when I compress it, the
> size is only 150mb and the time to transfert is excellent. (10min to
> compress, 10min to transfert, 5 to expand; instead-of 2h of transferts)
>
> "Scott M" <scott_M@.nospam.nospam> wrote in message
> news:ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>

Can I pick your brains for a second? transferring a large database problem

-=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
now -=-=-
Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
Scott
Hello Scott,
It is not easy to use a DTS package to do this job. I think database
replication is more appropriate for this situation. You may consider
transaction or merge replication according to your requirements.
If you still want to consider DTS, lookup query feature might be helpful
http://msdn.microsoft.com/library/de...us/dtssql/dts_
addf_misc_2dix.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Scott M" <scott_M@.nospam.nospam>
| Subject: Can I pick your brains for a second? transferring a large
database problem
| Date: Mon, 6 Jun 2005 17:33:03 -0500
| Lines: 72
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: adsl-068-209-157-050.sip.lft.bellsouth.net
68.209.157.50
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1784
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| -=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
| now -=-=-
|
| Okay, I have a problem that I need some suggestions with.
|
| I have a 30 Gig database that I need to sync across a T1 every night.
This
| database is a data dump of our billing system data, the tables are dropped
| ad re-created every night.
|
| I can do a very long and laborious process on the server of creating
tables
| with the primary keys from each table and a binary checksum of a row, then
| complaining it to a copy of the database from the day before. Then I
| generate SQL statements for the updates (Actually deletes and inserts).
All
| of this on the source server.
|
| I'd like to compact this down using a DTS package on the destination side,
| however I can't use a dynamic query in the "openRowSet" command to get
only
| the row that I want.
|
| Here is what I'm doing (Just for one table, I'll have data driven steps
for
| each table in my DB)
|
| insert into @.tempChecker
| select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
| chgno from charge_t')
|
| Declare Charge_t_Cursor Cursor
| FOR
| select
| a.chgno
| from
| (select
| BSum,
| chgno
| from @.tempChecker
| ) a
| where
| not exists
| (
| select 'x'
| from charge_t b
| where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
| )
|
| Once I get all the chgno's (The key in the table) I want to only pull back
| the data that has changes. So I have this (Which I know I can't do put
you
| get the idea)
|
| Open Charge_t_Cursor
|
| Fetch next from Charge_t_cursor into @.ChgNo
|
| While @.@.FETCH_STATUS = 0
| BEGIN
| insert into @.ChargeT select *
| from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
| cast(isnull(@.ChgNo, 0) as varchar)))
|
| Fetch next from Charge_t_cursor into @.ChgNo
| END
|
| Close Charge_t_cursor
| deallocate Charge_t_cursor
|
| select * from @.ChargeT
|
| this way I'm only pulling over the wire the data that is updated and it
will
| be in a nice, compact binary format. ANY help would be VERY
appreciated!!!
| Thanks much!
|
| Scott
|
|
|||Would replication work if the source tables are dropped every time it's
built?
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:s73X27waFHA.3928@.TK2MSFTNGXA01.phx.gbl...
> Hello Scott,
> It is not easy to use a DTS package to do this job. I think database
> replication is more appropriate for this situation. You may consider
> transaction or merge replication according to your requirements.
> If you still want to consider DTS, lookup query feature might be helpful
> http://msdn.microsoft.com/library/de...us/dtssql/dts_
> addf_misc_2dix.asp
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | From: "Scott M" <scott_M@.nospam.nospam>
> | Subject: Can I pick your brains for a second? transferring a large
> database problem
> | Date: Mon, 6 Jun 2005 17:33:03 -0500
> | Lines: 72
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | format=flowed;
> | charset="iso-8859-1";
> | reply-type=original
> | Content-Transfer-Encoding: 7bit
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
> | Message-ID: <ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.datawarehouse
> | NNTP-Posting-Host: adsl-068-209-157-050.sip.lft.bellsouth.net
> 68.209.157.50
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.datawarehouse:1784
> | X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
> |
> | -=-=- Sorry for the dup post, I'm posting it under my MSDN universal
> acct
> | now -=-=-
> |
> | Okay, I have a problem that I need some suggestions with.
> |
> | I have a 30 Gig database that I need to sync across a T1 every night.
> This
> | database is a data dump of our billing system data, the tables are
> dropped
> | ad re-created every night.
> |
> | I can do a very long and laborious process on the server of creating
> tables
> | with the primary keys from each table and a binary checksum of a row,
> then
> | complaining it to a copy of the database from the day before. Then I
> | generate SQL statements for the updates (Actually deletes and inserts).
> All
> | of this on the source server.
> |
> | I'd like to compact this down using a DTS package on the destination
> side,
> | however I can't use a dynamic query in the "openRowSet" command to get
> only
> | the row that I want.
> |
> | Here is what I'm doing (Just for one table, I'll have data driven steps
> for
> | each table in my DB)
> |
> | insert into @.tempChecker
> | select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*)
> BSum,
> | chgno from charge_t')
> |
> | Declare Charge_t_Cursor Cursor
> | FOR
> | select
> | a.chgno
> | from
> | (select
> | BSum,
> | chgno
> | from @.tempChecker
> | ) a
> | where
> | not exists
> | (
> | select 'x'
> | from charge_t b
> | where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> | )
> |
> | Once I get all the chgno's (The key in the table) I want to only pull
> back
> | the data that has changes. So I have this (Which I know I can't do put
> you
> | get the idea)
> |
> | Open Charge_t_Cursor
> |
> | Fetch next from Charge_t_cursor into @.ChgNo
> |
> | While @.@.FETCH_STATUS = 0
> | BEGIN
> | insert into @.ChargeT select *
> | from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> | cast(isnull(@.ChgNo, 0) as varchar)))
> |
> | Fetch next from Charge_t_cursor into @.ChgNo
> | END
> |
> | Close Charge_t_cursor
> | deallocate Charge_t_cursor
> |
> | select * from @.ChargeT
> |
> | this way I'm only pulling over the wire the data that is updated and it
> will
> | be in a nice, compact binary format. ANY help would be VERY
> appreciated!!!
> | Thanks much!
> |
> | Scott
> |
> |
>
|||have you try to create a local copy, then zip it, send the file by FTP,
unzip and load the data?
For example, you export to CSV files the new data from the source server.
A CSV file can be loaded in a database very quickly using bulk insert
commands.
Or you could detach a database, zip it, copy, reattach on the target server.
Or do the same with a backup file.
I have a demo database which is a 5gb of data, when I compress it, the size
is only 150mb and the time to transfert is excellent. (10min to compress,
10min to transfert, 5 to expand; instead-of 2h of transferts)
"Scott M" <scott_M@.nospam.nospam> wrote in message
news:ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> -=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
> now -=-=-
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This
> database is a data dump of our billing system data, the tables are dropped
> ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables
> with the primary keys from each table and a binary checksum of a row, then
> complaining it to a copy of the database from the day before. Then I
> generate SQL statements for the updates (Actually deletes and inserts).
> All
> of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only
> the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for
> each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you
> get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will
> be in a nice, compact binary format. ANY help would be VERY
> appreciated!!!
> Thanks much!
> Scott
>
|||Yes, the files are too large to zip in a decent amount of time (The database
files)
however we are zipping and transferring the flat files that the UNIX box is
creating.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:Omk62fNdFHA.3620@.TK2MSFTNGP09.phx.gbl...
> have you try to create a local copy, then zip it, send the file by FTP,
> unzip and load the data?
> For example, you export to CSV files the new data from the source server.
> A CSV file can be loaded in a database very quickly using bulk insert
> commands.
> Or you could detach a database, zip it, copy, reattach on the target
> server.
> Or do the same with a backup file.
> I have a demo database which is a 5gb of data, when I compress it, the
> size is only 150mb and the time to transfert is excellent. (10min to
> compress, 10min to transfert, 5 to expand; instead-of 2h of transferts)
>
> "Scott M" <scott_M@.nospam.nospam> wrote in message
> news:ui2eBfuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>

Can I Overlay Rows in a Table?

I've created a table with two group header rows and a detail row. I would
like the second group header to 'overlap with' the detail row when the
report prints. Is this possible? And if so, how do I go about
accomplishing this?
Thanks!Very good question. I'm curious as to how to do this too. Sorry this
isn't a helpful response, but hopefully others see this thread who can
answer it and know there's an interest.
Cindy wrote:
> I've created a table with two group header rows and a detail row. I would
> like the second group header to 'overlap with' the detail row when the
> report prints. Is this possible? And if so, how do I go about
> accomplishing this?
> Thanks!|||I don't want to hide my detail lines. I want my second group header to
print in the same visual row as my first row of detail.
Below is the best I can do with a textual visualization:
[Group Header #1]
[Group Header #2] Detail
Detail
Detail|||Thanks BYU,
That sounds like an approach. My second group header contains a memo field
with a lot of word-wrapping. My hope was that it would 'overlay' however
many detail lines it wraps out to. I believe with your suggestion I would
get a large space between my first detail row and my second row.
My crude illustration of what I am after:
[Group Header #1]
[Group Header #2 [Detail Row 1]
that could potentially [Detail Row 2]
wrap to multiple lines of [Detail Row 3]
text] [Detail Row 4]
[Detail Row 5]
[Group Header #1]
Your solution as I understand it:
[Group Header #1]
[Group Header #2 [Detail Row 1]
that could potentially
wrap to multiple lines of
text]
[Detail Row 2]
[Detail Row 3]
[Detail Row 4]
[Detail Row 5]
[Group Header #1]