Tuesday, March 27, 2012
Delete and import data in a table in replication environment
I have made a replication between Server A and B. It work well
One date, the customer need to renew all data in one table. So I run a
script to delete the data then try to import such data from another outside
server. I used Sql server enterprise tool to import data,I make it
successful. But after runed the replication. I found the data have been
deleted in the table in Server B. The customer complain me that they can not
retrieve data from Server B. It make me sad.
So I just want to ask whethe the Sql server enterprise tool import
function can not triger to replication?
Regards
Jackson Chan
You don't mention the replication type, but my guess is that it is merge. If
so, if you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents. In
this case, you need to run sp_addtabletocontents to include the rows then
resynchronise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Delete an excel worksheet only if it exists, in a dts package
1. I import data into sqlserver, from an excel file, from a worksheet
within it, named 'input_data'
2. then i scrub the data in sqlserver
3. then i export some data into the same excelfile but into another
worksheet named 'output_data'
I have got it working and this is how i am doing it right now. I am
having a problem in the few steps identified below :
1. I delete the worksheet 'output_data' using an execsql task
DROP TABLE `output_data'
2. Then i have another execsql task to create it
CREATE TABLE `output_data`
3. Then i have datapump task to put data into worksheet.
The work fine, but i am running into a problem, when the output_data
worksheet doesnt exist.
So i need to write a query telling
IF `output_data` doesnt exist, then DROP IT
How do i do this
I am open to doing this in ActiveX Script(vbscript) and ExecSQL task
Also i do not have excel dll or runtime in my dbserver.
Please let me know
thanks
RSapologize for typo
I do have a problem, when 'output_data' worksheet does exist
and hence need a query telling,
if output_data worksheet exists, then drop it
thank you
RS
Monday, March 19, 2012
Defragmentation of database file
Is the only way to export data from the database and then import back into the database? If so, how to do it?
thanks.
Hi,
Have a look into the below article.
http://www.winnetmag.com/SQLServer/A...283/24283.html
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for
indexes?
> Is the only way to export data from the database and then import back into
the database? If so, how to do it?
> thanks.
|||http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for indexes?
> Is the only way to export data from the database and then import back into the database? If so,
how to do it?
> thanks.
|||We are using database having a current size of over 300GB to store attachment files in an image column of a table. is the approach of exporting and importing data records from the table a good way to defragment the table? if so, is it possible to export t
he data records as a flat file to a tape drive.
thanks.
"Tibor Karaszi" wrote:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SY" <SY@.discussions.microsoft.com> wrote in message
> news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> how to do it?
>
>
|||Hi,
Check the fragmentation for those tables using the below command
dbcc showcontig('table_name')
If the scan desity is too bad then execute
DBCC DBREINDEX('table_name')
for those table with high fragmentation.
Note:
It is not required to export and import data for tables.
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:B95EB230-210D-4B40-96B6-5B7C05CACA7C@.microsoft.com...
> We are using database having a current size of over 300GB to store
attachment files in an image column of a table. is the approach of exporting
and importing data records from the table a good way to defragment the
table? if so, is it possible to export the data records as a flat file to a
tape drive.[vbcol=seagreen]
> thanks.
>
> "Tibor Karaszi" wrote:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
for indexes?[vbcol=seagreen]
into the database? If so,[vbcol=seagreen]
Defragmentation of database file
dexes?
Is the only way to export data from the database and then import back into t
he database? If so, how to do it?
thanks.Hi,
Have a look into the below article.
http://www.winnetmag.com/SQLServer/...4283/24283.html
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for
indexes?
> Is the only way to export data from the database and then import back into
the database? If so, how to do it?
> thanks.|||http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for
indexes?
> Is the only way to export data from the database and then import back into the dat
abase? If so,
how to do it?
> thanks.|||We are using database having a current size of over 300GB to store attachmen
t files in an image column of a table. is the approach of exporting and impo
rting data records from the table a good way to defragment the table? if so,
is it possible to export t
he data records as a flat file to a tape drive.
thanks.
"Tibor Karaszi" wrote:
> l]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "SY" <SY@.discussions.microsoft.com> wrote in message
> news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> how to do it?
>
>|||Hi,
Check the fragmentation for those tables using the below command
dbcc showcontig('table_name')
If the scan desity is too bad then execute
DBCC DBREINDEX('table_name')
for those table with high fragmentation.
Note:
It is not required to export and import data for tables.
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:B95EB230-210D-4B40-96B6-5B7C05CACA7C@.microsoft.com...
> We are using database having a current size of over 300GB to store
attachment files in an image column of a table. is the approach of exporting
and importing data records from the table a good way to defragment the
table? if so, is it possible to export the data records as a flat file to a
tape drive.[vbcol=seagreen]
> thanks.
>
> "Tibor Karaszi" wrote:
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx[vbcol=seagreen]
for indexes?[vbcol=seagreen]
into the database? If so,[vbcol=seagreen]
Defragmentation of database file
Is the only way to export data from the database and then import back into the database? If so, how to do it?
thanks.Hi,
Have a look into the below article.
http://www.winnetmag.com/SQLServer/Article/ArticleID/24283/24283.html
--
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for
indexes?
> Is the only way to export data from the database and then import back into
the database? If so, how to do it?
> thanks.|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <SY@.discussions.microsoft.com> wrote in message
news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> Can I do the defragmentation of database file in SQL Server 2000, not for indexes?
> Is the only way to export data from the database and then import back into the database? If so,
how to do it?
> thanks.|||We are using database having a current size of over 300GB to store attachment files in an image column of a table. is the approach of exporting and importing data records from the table a good way to defragment the table? if so, is it possible to export the data records as a flat file to a tape drive.
thanks.
"Tibor Karaszi" wrote:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SY" <SY@.discussions.microsoft.com> wrote in message
> news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> > Can I do the defragmentation of database file in SQL Server 2000, not for indexes?
> >
> > Is the only way to export data from the database and then import back into the database? If so,
> how to do it?
> >
> > thanks.
>
>|||Hi,
Check the fragmentation for those tables using the below command
dbcc showcontig('table_name')
If the scan desity is too bad then execute
DBCC DBREINDEX('table_name')
for those table with high fragmentation.
Note:
It is not required to export and import data for tables.
--
Thanks
Hari
MCDBA
"SY" <SY@.discussions.microsoft.com> wrote in message
news:B95EB230-210D-4B40-96B6-5B7C05CACA7C@.microsoft.com...
> We are using database having a current size of over 300GB to store
attachment files in an image column of a table. is the approach of exporting
and importing data records from the table a good way to defragment the
table? if so, is it possible to export the data records as a flat file to a
tape drive.
> thanks.
>
> "Tibor Karaszi" wrote:
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "SY" <SY@.discussions.microsoft.com> wrote in message
> > news:19838AE1-9327-49D1-BCE1-135AABE88F53@.microsoft.com...
> > > Can I do the defragmentation of database file in SQL Server 2000, not
for indexes?
> > >
> > > Is the only way to export data from the database and then import back
into the database? If so,
> > how to do it?
> > >
> > > thanks.
> >
> >
> >
Wednesday, March 7, 2012
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.
Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/a...1/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/...01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Tuesday, February 14, 2012
Default settings for strings and numbers
new table in EM or import a table from Excel, I will always get varchar(100)
for any string instead of nvarchar(255), and change the default for numbers
to numeric(19,6) instead of float(53)?
Thanks.
ArcherNo, Enterprise Manager does not have any provisions to change such defaults
for datatypes.
Anith