Correct approach to catching execution time errors in a custom task

Hi,

I'm building a custom task and just wondering what is the correct way of passing errors back to SSIS. Is there a rcommended approach to doing this. Currently I just wrap everything in a TRY...CATCH and use componentEvents to fire it back! Here's my code:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool failed = false;
try
{
/*
* do stuff in here
*/
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
failed = true;
}
if (failed)
{
return DTSExecResult.Failure;
}
else
{
return DTSExecResult.Success;
}
}

Any comments?

-Jamie

Anyone?|||the boolean flag isn't necessary. the line: return DTSExecResult.Failure;
could be in the catch block.|||

Good point. cheers Duane!! So it should be:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
/*
* do stuff in here
*/

return DTSExecResult.Success;
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
return DTSExecResult.Failure;
}
}

-Jamie

[Microsoft follow-up]

|||

Hi Jamie,

this looks like the correct approach to me.

sql

Correct approach to catching execution time errors in a custom task

Hi,

I'm building a custom task and just wondering what is the correct way of passing errors back to SSIS. Is there a rcommended approach to doing this. Currently I just wrap everything in a TRY...CATCH and use componentEvents to fire it back! Here's my code:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool failed = false;
try
{
/*
* do stuff in here
*/
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
failed = true;
}
if (failed)
{
return DTSExecResult.Failure;
}
else
{
return DTSExecResult.Success;
}
}

Any comments?

-Jamie

Anyone?|||the boolean flag isn't necessary. the line: return DTSExecResult.Failure;
could be in the catch block.|||

Good point. cheers Duane!! So it should be:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
/*
* do stuff in here
*/

return DTSExecResult.Success;
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
return DTSExecResult.Failure;
}
}

-Jamie

[Microsoft follow-up]

|||

Hi Jamie,

this looks like the correct approach to me.

Copying SqlServer database from web host

Hi,

I have a hard time copying my db (or instance?) from a SQL Server 2000 db which resides at my web host. I have Sql Server 2005 Express and Sql Server Management Studio Express on my computer and, well, there is no "ftp-like" option so I don't have a clue about how to proceed. I've read many posts on the net on this matter but nothing seems to apply to this, in my mind, rather common, configuration.

I have receieved Excel docs that should be appended to tables in my db. I have successfully installed DTS (seehttp://mobiledeveloper.wordpress.com/ for details - it's really simple but takes two hours) so I have a wizard for the actual import from Excel. My plan is now to

1. download/detach/whatever my table/db/part of the web host's db and download it to my computer. Then

2. fill the tables with the data from Excel. And finally, to

3. upload/attach/whatever my local db or table to the web host again.

I'm sooo confused, please, please help me out here!

Pettrer

Here's how:

http://forums.asp.net/1621926/ShowThread.aspx#1621926

Copying SQL logins to new Domain

Good day!
I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
for the time being. But I am starting to move users from that domain
to an AD domain. We are doing them bit by bit. I want the Windows
Authentication-based SQL Logins to move over so that I don't have to
re-create the logins.
What is the best way to do this, assuming I want to do them slowly?
Thanks.Have you considered creating Groups on the new domain, give the groups the
relevant SQL permissions and when you're ready to migrate a user simply add
him to the new group.
HTH
Ryan Waight, MCDBA, MCSE
"Bob" <Bob@.nospam.com> wrote in message
news:nkkfqvg1fvjnc0j8lu9l8c2i2trtvvf8jq@.4ax.com...
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
> Thanks.
>|||I suppose I could do that. But is there a way to do what I am looking
to do?
On Tue, 4 Nov 2003 16:52:42 -0000, "Ryan Waight"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>Have you considered creating Groups on the new domain, give the groups the
>relevant SQL permissions and when you're ready to migrate a user simply add
>him to the new group.|||Bob wrote:
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
you might try running sp_help_revlogin and then editing the script to your
liking.|||Hi Bob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you would like to transfer the Windows Authentication
Logins and passwords to a new AD domain between the instances of SQL Server. Have I fully
understood you? If there is anything I misunderstood, please feel free to let me know.
I agree with Chxxx that you can create sp_help_revlogin stored procedure on the source
server, which this procedure generates a script that you can run on the destination server to re-
create logins with the original security identification number (SID) and retain the current
passwords.
For how to transfer Logins and Passwords between SQL Server 2000, please refer to the
following step by step articles:
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/?id=274188
Bob, does this answer your question? Please apply the suggestion above and let us know if it
helps you resolve your problem. If there is anything more we can assist you with, please feel
free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
If Bob is interested in the Windows authentication logins, why would he like
to transfer the passwords?
One of the problems with migrating the NT user accounts to AD how to deal
with the following scenario:
1. NT user NTDomain\myUser is migrated to ADDomain\myUser (likely with
SIDHistory)
2. The SID of NTDomain\myUser is different from ADDomain\myUser
3. The system tables sysxlogins and sysusers still store the SID for
NTDomain\myUser
4. If the NTDomain domain is completely disabled, SIDHistory will not be
useful any more
(Note, not being an AD migration expert, I don't know whether you can
migrate NT user accounts to AD without assigning them new SIDs. It's not how
we are migrating user account anyway.)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:7GUTrC0oDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Bob,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
> I agree with Chxxx that you can create sp_help_revlogin stored procedure
on the source
> server, which this procedure generates a script that you can run on the
destination server to re-
> create logins with the original security identification number (SID) and
retain the current
> passwords.
> For how to transfer Logins and Passwords between SQL Server 2000, please
refer to the
> following step by step articles:
> 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
Server
> http://support.microsoft.com/?id=246133
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> 274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> http://support.microsoft.com/?id=274188
>
> Bob, does this answer your question? Please apply the suggestion above and
let us know if it
> helps you resolve your problem. If there is anything more we can assist
you with, please feel
> free to post it in the group.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hi Bob,
I agree with Linchi's comments and thanks for his reminder.
If you transfer logins to an instance of SQL Server in a different domain than the source
instance of SQL Server, you should edit the script generated by the sp_help_revlogin
procedure, and REPLACE the domain name with the new domain in the sp_grantlogin
statements. Because the integrated logins granted access in the new domain will not have the
same SID as the logins in the original domain, the database users will be orphaned from
these logins.....
This is announced in the "Remarks" of the KB 246133. I recommend you review the remarks
carefully before you apply this logins transference, so that you will not miss some important
points you should pay attention to.
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
If there is anyting unclear or anything more I can assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Copying records

How would I copy x amount of records, at the same time changing one of the
fields?
I can't copy the records as is, because of unique constraints, and I can't
leave the field that needs to be changed blank, also because nulls are not
allowed. So how would I do it in one swoop?Use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||Not sure about doing in one swoop but you could use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||what does it need to be changed to? and from? and what data type is it?
x amount of records? do you mean e.g., "copy 50 rows from a to b" or
"copy however many rows match my criteria from a to b"?
Please post DDL, including constraints etc., sample data and desired
results. Otherwise, all answers can be considered nothing more than guesses.
dew wrote:
> How would I copy x amount of records, at the same time changing one of the
> fields?
> I can't copy the records as is, because of unique constraints, and I can't
> leave the field that needs to be changed blank, also because nulls are not
> allowed. So how would I do it in one swoop?
>|||Ah. That works great, thanks!
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1139592215.572339.195290@.g47g2000cwa.googlegroups.com...
> Use a staging table.
> Copy the rows from TableA to a staging table. Change the data that
> needs changing and then copy the rows in to Table B
> Barry
>sql

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
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
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> 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
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

Copying Dimensions in BIDS

Is it possible to copy a dimension from one solution/project to another. I have a generic time dimension and a few other common dimensions that I would rather just copy from one project to another rather than setting it up entirely from one project to another.

I tried copying the XML file from one project to another, but that did not work because they had different DSV's, and thus different design-time unique names.

any suggestions?

-ClaytonUsing SSMS, you should be able to script out the creation of the dimension and then execute it in the other database. If you are using different DSV (or DS) names, then you will have to modify the script before you execute it on the other system but hopefully that will be easy. At least it is just editing a single file.

_-_-_ Dave

copying data oracle2sql

Hi,
I discovered that copying data from oracle using "openquery" with linked
server is sevaral time slower that using "dtsexecui" (dts import/export
wzard). I used the same query for both methods. Does anybode has expirience
solving slowly openquery behaviour? My linked server has "allow in process"
enabled.
Seems dts import/export wizzard doesn't use rollback segment? May I could
rewrite my query?
select *
from openquery (sapr3_prd, '
select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
, UMLME ,INSME, EINME, SPEME, RETME, VKLAB
, VKUML
from sapr3.mardh
where mandt = ''600''
')
Ramunasopenquery is not designed to move large amount of data.
the import/export wizard use the bulk insert feature of SQL Server which
result on the highest performance you can for a loading.
"Seems dts import/export wizzard doesn't use rollback segment?"
what do you mean by this?
you can import data in 1 transaction or multiple transactions
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174662643.833343@.loger.vpmarket.int...
> Hi,
> I discovered that copying data from oracle using "openquery" with linked
> server is sevaral time slower that using "dtsexecui" (dts import/export
> wzard). I used the same query for both methods. Does anybode has
> expirience
> solving slowly openquery behaviour? My linked server has "allow in
> process"
> enabled.
> Seems dts import/export wizzard doesn't use rollback segment? May I could
> rewrite my query?
> select *
> from openquery (sapr3_prd, '
> select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
> , UMLME ,INSME, EINME, SPEME, RETME, VKLAB
> , VKUML
> from sapr3.mardh
> where mandt = ''600''
> ')
> Ramunas
>|||you think the bottleneck is the insert part into sql, not select from
oracle? i think different - sql server is able to select data from oracle in
different way.
the openquery from oracle always fails with message: "ora-01555 snapshot too
old: rollback segment number XX with name YYY to small". my idea is to use
fresh table and load data in the same transaction like:
begin tran
truncate table MyTable
insert into MyTable
select * from openquery (sapr3_prd, '....')
if @.@.error <> 0
rollback tran
else
commit tran
is it possible to achieve this with dtsexecui? it's strange but dtsexecui
never get this error, so my idea this is because import/export wizard
doesn't use rollback segment in oracle. you said that possible to import
data in one transaction, what you mean? because when I use dtsexecui, and
try to load 100 mln of rows and if import fails after 80 mln, so these 80
mln of rows will be added to the table.
"Jeje" <willgart@.hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> openquery is not designed to move large amount of data.
> the import/export wizard use the bulk insert feature of SQL Server which
> result on the highest performance you can for a loading.
> "Seems dts import/export wizzard doesn't use rollback segment?"
> what do you mean by this?
> you can import data in 1 transaction or multiple transactions
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1174662643.833343@.loger.vpmarket.int...
could[vbcol=seagreen]|||no, the insert into sql server is not the problem.
the problem is the loading part from the Oracle driver, and the overhead of
the openquery syntax.
using a bulk insert command through an SSIS package will provide far better
performance.
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174888638.520904@.loger.vpmarket.int...
> you think the bottleneck is the insert part into sql, not select from
> oracle? i think different - sql server is able to select data from oracle
> in
> different way.
> the openquery from oracle always fails with message: "ora-01555 snapshot
> too
> old: rollback segment number XX with name YYY to small". my idea is to use
> fresh table and load data in the same transaction like:
> begin tran
> truncate table MyTable
> insert into MyTable
> select * from openquery (sapr3_prd, '....')
> if @.@.error <> 0
> rollback tran
> else
> commit tran
> is it possible to achieve this with dtsexecui? it's strange but dtsexecui
> never get this error, so my idea this is because import/export wizard
> doesn't use rollback segment in oracle. you said that possible to import
> data in one transaction, what you mean? because when I use dtsexecui, and
> try to load 100 mln of rows and if import fails after 80 mln, so these 80
> mln of rows will be added to the table.
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...
> could
>

copying data oracle2sql

Hi,
I discovered that copying data from oracle using "openquery" with linked
server is sevaral time slower that using "dtsexecui" (dts import/export
wzard). I used the same query for both methods. Does anybode has expirience
solving slowly openquery behaviour? My linked server has "allow in process"
enabled.
Seems dts import/export wizzard doesn't use rollback segment? May I could
rewrite my query?
select *
from openquery (sapr3_prd, '
select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
, UMLME ,INSME, EINME, SPEME, RETME, VKLAB
, VKUML
from sapr3.mardh
where mandt = ''600''
')
Ramunas
openquery is not designed to move large amount of data.
the import/export wizard use the bulk insert feature of SQL Server which
result on the highest performance you can for a loading.
"Seems dts import/export wizzard doesn't use rollback segment?"
what do you mean by this?
you can import data in 1 transaction or multiple transactions
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174662643.833343@.loger.vpmarket.int...
> Hi,
> I discovered that copying data from oracle using "openquery" with linked
> server is sevaral time slower that using "dtsexecui" (dts import/export
> wzard). I used the same query for both methods. Does anybode has
> expirience
> solving slowly openquery behaviour? My linked server has "allow in
> process"
> enabled.
> Seems dts import/export wizzard doesn't use rollback segment? May I could
> rewrite my query?
> select *
> from openquery (sapr3_prd, '
> select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
> , UMLME ,INSME, EINME, SPEME, RETME, VKLAB
> , VKUML
> from sapr3.mardh
> where mandt = ''600''
> ')
> Ramunas
>
|||you think the bottleneck is the insert part into sql, not select from
oracle? i think different - sql server is able to select data from oracle in
different way.
the openquery from oracle always fails with message: "ora-01555 snapshot too
old: rollback segment number XX with name YYY to small". my idea is to use
fresh table and load data in the same transaction like:
begin tran
truncate table MyTable
insert into MyTable
select * from openquery (sapr3_prd, '....')
if @.@.error <> 0
rollback tran
else
commit tran
is it possible to achieve this with dtsexecui? it's strange but dtsexecui
never get this error, so my idea this is because import/export wizard
doesn't use rollback segment in oracle. you said that possible to import
data in one transaction, what you mean? because when I use dtsexecui, and
try to load 100 mln of rows and if import fails after 80 mln, so these 80
mln of rows will be added to the table.
"Jeje" <willgart@.hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> openquery is not designed to move large amount of data.
> the import/export wizard use the bulk insert feature of SQL Server which
> result on the highest performance you can for a loading.
> "Seems dts import/export wizzard doesn't use rollback segment?"
> what do you mean by this?
> you can import data in 1 transaction or multiple transactions
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1174662643.833343@.loger.vpmarket.int...
could[vbcol=seagreen]
|||no, the insert into sql server is not the problem.
the problem is the loading part from the Oracle driver, and the overhead of
the openquery syntax.
using a bulk insert command through an SSIS package will provide far better
performance.
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174888638.520904@.loger.vpmarket.int...
> you think the bottleneck is the insert part into sql, not select from
> oracle? i think different - sql server is able to select data from oracle
> in
> different way.
> the openquery from oracle always fails with message: "ora-01555 snapshot
> too
> old: rollback segment number XX with name YYY to small". my idea is to use
> fresh table and load data in the same transaction like:
> begin tran
> truncate table MyTable
> insert into MyTable
> select * from openquery (sapr3_prd, '....')
> if @.@.error <> 0
> rollback tran
> else
> commit tran
> is it possible to achieve this with dtsexecui? it's strange but dtsexecui
> never get this error, so my idea this is because import/export wizard
> doesn't use rollback segment in oracle. you said that possible to import
> data in one transaction, what you mean? because when I use dtsexecui, and
> try to load 100 mln of rows and if import fails after 80 mln, so these 80
> mln of rows will be added to the table.
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...
> could
>

Copying column into another

Hi,
We already have "Data Entry Time" column with hundreds of records in it, and
realized later we also need "Data Update Time." The "Data Update Time"
should not be NULL. So as a starting point, we will copy everything in the
"Data Entry Time" column into the "Data Update Time" column for the already
existing records, and then each will go its own way. So my question is how
do I copy the "Data Entry Time" column with records into the "Data Update
Time" column?
YCAsp Psa wrote:
> Hi,
> We already have "Data Entry Time" column with hundreds of records in it, a
nd
> realized later we also need "Data Update Time." The "Data Update Time"
> should not be NULL. So as a starting point, we will copy everything in th
e
> "Data Entry Time" column into the "Data Update Time" column for the alread
y
> existing records, and then each will go its own way. So my question is ho
w
> do I copy the "Data Entry Time" column with records into the "Data Update
> Time" column?
> YC
UPDATE your_table
SET data_update_time = data_entry_time
WHERE data_update_time IS NULL ;
ALTER TABLE your_table
ALTER COLUMN data_update_time DATETIME NOT NULL;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you!
YC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141860909.277930.39530@.z34g2000cwc.googlegroups.com...
> Asp Psa wrote:
> UPDATE your_table
> SET data_update_time = data_entry_time
> WHERE data_update_time IS NULL ;
> ALTER TABLE your_table
> ALTER COLUMN data_update_time DATETIME NOT NULL;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

copying a database across servers

I'm developing a web application locally using SQL Server 2000. When it's time for me to throw it up on my host, how do I copy the database from my computer to their server? Thanks.use the DTS tools provided with SQL Server (import/export data).

What I usually do is script the database structure (and objects), set that up and check it over, THEN transfer the data, but whether you want to do it that way is up to you

j|||Another way is to simply stop your sql server and copy the database and log files to the other location and then attach the database.

Regards|||There are multiple ways as in using DTS or other tools. BUt if the other server is far apart and you donot want to use the internet bandwidth then I feel the best bet is to backup the database and then transfer/FTP the DB backup to be restored into the destination server. Copying the data and the log files are fine. But not the cleanest of approaches AFAIK.|||All solutions provided here are good, I would personally go with Atrax' Scripting solution, it is probably the best way to do it if you don't need to transfer any data and bandwidth is an issue, but you will need the admin on the remote server to launch your .sql script. Here's what I do: Select all your user tables, right-click and copy. Paste them into the Query Analyzer and boom you've got the script to create your tables. Same with SPROC's and any other objects you have created. You can paste them all into one file.

Add this to the top of your SQL Script if the DB does not exist on the server:

CREATE DATABASE [YourDBName]--Skip these two lines if the DB does exist
Go
USE YourDBName -- Add this regardless

and Save it. The script can now be executed from the QA on any SQL server to create your DB.
Piece-o-cake.

If you did need to transfer any data, you could create a DTS package to export the data to the remote server. But of course you have to have access and it depends on the amount of data you have stored and bandwidth as to how effective a transfer would be.

But again, all of the solutions provided here by the other posters so far are viable. It's a matter of personal preference. All though, if you script it and something blows up locally, you can easily recreate your database without having to copy it back from the remote server. An unlikey scenario, but entirely possible... just a thought.

Good luck.

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>> We have been using the 'Copy SQL Server Objects' with success for some
>> time to copy an entire database to another server. Recent changes to our
>> database infrastructure cause DTS to fail:
>> 1) We have a check constraint which uses a user function (which refers to
>> the table on the check constraint
>> 2) We have a view which refers to another view
>> DTS fails on both. DTS fails on the check constraint and stops. if I
>> remove the check constraint it will fail on the view. See errors below.
>> Any hints how to solve this would be appreciated.
>> Jonathan Orgel
>> Errors:
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'dbo.SRS_NumberRWSubjects'
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'VMSGRECIPIENTS2'
>>
>