Equalize mis-synched tables

Occationally, my publisher and push subscribers don't matchup on a few
tables. The subscriber has a few more rowguids than Publisher even after
synching. Synch glip or conflict or such.
What is the best practice to get them matching without cascading more
oddities to other subscribers?
When I try a meaningless update, to kick in the repl trigger, it doesn't
seem to move over. Delete and reinsert? Same Guid or new?
thanks for any suggestions.
Mike
If data falls in the woods and nobody is there to see it ...... ?
This can happen in several circumstances that I know of. Most likely it's
from when you bulk insert the rows and choose the defaults, then
FIRE_TRIGGERS is false and consequently the rows are not added to
MSmerge_contents. Also, if you do a fast-load using the Transform Data task
in DTS: In all cases case, you need to run sp_addtabletocontents to include
the rows then resynchronise. Alternatively you can use sp_mergedummyupdate
for a single row. For the fast load case, in future if you deselect the
check box the triggers will fire.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
The extra rows are on my pull subscriber (server) so I used the
sp_mergedummyupdate to test. It did add a row to the msmerge_contents table
but the generation didn't transfer to the publishers _contents table.
No synch error messages/conflicts. Other data is moving fine.
The extra rows were deleted 1/2/06, reasons unknown (system delete). I
reinserted them with a std insert from a restored db. ** I kept the original
rowguid.
Mistake? I wanted to avoid getting duplicate values in the table.
The pub does have the initial delete in tombstone (orig rowguid). Would
that would filter out the rowguid for future synching?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Paul Ibison" wrote:

> This can happen in several circumstances that I know of. Most likely it's
> from when you bulk insert the rows and choose the defaults, then
> FIRE_TRIGGERS is false and consequently the rows are not added to
> MSmerge_contents. Also, if you do a fast-load using the Transform Data task
> in DTS: In all cases case, you need to run sp_addtabletocontents to include
> the rows then resynchronise. Alternatively you can use sp_mergedummyupdate
> for a single row. For the fast load case, in future if you deselect the
> check box the triggers will fire.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I'm a little unsure of your current situation but if the rows don't exist at
all on the publisher then I'd delete these newly added subscriber rows, then
reinsert them and let new rowguids be created.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Enumerating SQL Server database tables rows and sprocs using MFC

Hi,
I've been looking around for a while now after an efficient way to enumerate
tables, rows and sprocs of a MsSQL database. The best I could come up with
was SQL DMO, but one cannot have it installed without installing SQL Server
itself or MSDE, so I'm looking for alternative.
After a table name was fetched, I will need to know the rows it contains,
what type they are, and what are the indexes and identity defined for this
table. Effectively, if I could somehow get the CREATE SQL statement from the
DB itself that would be ideal. Also, I will need a way to get the sprocs
code from the DB, and all that without having any prequisites on either
sides - client and server.
I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
algorythm in an unmanaged enviroment.
Any links, articles, replies and sample code would be highly appreciated.
Thanks in advance,
Stilgar.Stilgar wrote:
> Hi,
> I've been looking around for a while now after an efficient way to enumera
te
> tables, rows and sprocs of a MsSQL database. The best I could come up with
> was SQL DMO, but one cannot have it installed without installing SQL Serve
r
> itself or MSDE, so I'm looking for alternative.
> After a table name was fetched, I will need to know the rows it contains,
> what type they are, and what are the indexes and identity defined for this
> table. Effectively, if I could somehow get the CREATE SQL statement from t
he
> DB itself that would be ideal. Also, I will need a way to get the sprocs
> code from the DB, and all that without having any prequisites on either
> sides - client and server.
> I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
> algorythm in an unmanaged enviroment.
> Any links, articles, replies and sample code would be highly appreciated.
> Thanks in advance,
> Stilgar.
To connect to the database you'll normally need an ODBC or OLEDB driver
and the usual way to get that is to install the client connectivity
from the SQL Server disc or install MDAC from microsoft.com (unless you
plan to write your own ODBC/OLEDB client).
The metadata is available in the ADO object model or through TSQL
commands that return a recordset. SQL Server provides the Information
Schema views for that purpose and you can read about them in Books
Online.
David Portas
SQL Server MVP
--|||Will DESCRIBE <tablename> work on MsSql?
For each row simply do SELECT * FROM <table>
- MR
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've been looking around for a while now after an efficient way to
> enumerate tables, rows and sprocs of a MsSQL database.|||DESCRIBE <tablename> doesn't seem to work with MsSQL. I've executed it and
it errored.
How can I get the field types using SELECT *? Not to mention indexes and
determine what is the identity field?
Stilgar.
"Mark Randall" <markyr@.gmail.com> wrote in message
news:%23oLYohE7FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Will DESCRIBE <tablename> work on MsSql?
> For each row simply do SELECT * FROM <table>
> - MR
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
them myself, as I don't know what to look for exactly here.
How can MDAC help here?
Stilgar.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
> Stilgar wrote:
> To connect to the database you'll normally need an ODBC or OLEDB driver
> and the usual way to get that is to install the client connectivity
> from the SQL Server disc or install MDAC from microsoft.com (unless you
> plan to write your own ODBC/OLEDB client).
> The metadata is available in the ADO object model or through TSQL
> commands that return a recordset. SQL Server provides the Information
> Schema views for that purpose and you can read about them in Books
> Online.
> --
> David Portas
> SQL Server MVP
> --
>|||http://msdn.microsoft.com/library/d...r />
_9sfo.asp
Click 'Up One Level' and there you go ... I tried to give you that direct
link but ...
-Mark
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
> I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
> them myself, as I don't know what to look for exactly here.
> How can MDAC help here?
> Stilgar.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
>|||Hello Stilgar,
Take a look at the GetSchema() method using the most recent SqlClient bit
drops. BobB has good article about them at [0], but that's framed in ADO.NET
terms. I believe you can use the same patterns in C++/MFC, however. Just
harder :)
[0] http://msdn.microsoft.com/library/d...net2schemas.asp
Long live the fighters!
Kent Tegels
DevelopMentor|||I got an even better one - EXEC sp_tables, sp_columns <table>,
sp_stored_procedures.
Thanks for your help.
Stilgar.
"Mark Nijhof" <Mark@.Nijhof.com> wrote in message
news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
> http://msdn.microsoft.com/library/d.../>
es_9sfo.asp
> Click 'Up One Level' and there you go ... I tried to give you that direct
> link but ...
> -Mark
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||...And I have an even better Idea;
go to
http://dev.mysql.com/downloads/
and download the MySQL++, this makes connecting from VC++ to a MySQL
database really easy, no ODBC, or any other connectors, straight to the
MySQL database.
John
The_Code_Master@.hotmail.com
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> I got an even better one - EXEC sp_tables, sp_columns <table>,
> sp_stored_procedures.
> Thanks for your help.
> Stilgar.
> "Mark Nijhof" <Mark@.Nijhof.com> wrote in message
> news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sa-ses_9sfo.asp
direct
find
message
either
driver
you
>|||Sure, only problem is I asked about MS SQL Server, not MySQL. Thanks, but
I'm already familiar with it.
Stilgar.
"The Code Master" <The_Code_Master@.hotmail.com> wrote in message
news:BPOff.21604$tV6.12458@.newssvr27.news.prodigy.net...
> ...And I have an even better Idea;
> go to
> http://dev.mysql.com/downloads/
> and download the MySQL++, this makes connecting from VC++ to a MySQL
> database really easy, no ODBC, or any other connectors, straight to the
> MySQL database.
> John
> The_Code_Master@.hotmail.com
>
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/ur
l]
> sa-ses_9sfo.asp
> direct
> find
> message
> either
> driver
> you
>

enumerating all tables for size

Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
ArunTry this:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
Arun|||Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun

quote:

---

>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

---

|||Run the following and see what it says:
EXEC sp_spaceused 'SoftwareFile'
If that reports the same size, then your sysindexes table is not accurate,
and you will have to run DBCC UPDATEUSAGE to fix that.
Look up SQL Server Books Online for more information on DBCC UPDATEUSAGE.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:0af201c3d9eb$284ef130$a501280a@.phx.gbl...
Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun

quote:

---

>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

---

enumerating all tables for size

Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
ArunTry this:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
Arun|||Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun
>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in
message
>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>|||Run the following and see what it says:
EXEC sp_spaceused 'SoftwareFile'
If that reports the same size, then your sysindexes table is not accurate,
and you will have to run DBCC UPDATEUSAGE to fix that.
Look up SQL Server Books Online for more information on DBCC UPDATEUSAGE.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:0af201c3d9eb$284ef130$a501280a@.phx.gbl...
Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun
>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in
message
>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

Enumerate all tables in a server without dynamic sql.

Hi, I'm new to SQL and have a question. Is there any way to programatically enumerate all tables on a server without resorting to dynamic sql?

What I would like to do, in pseudocode is:

for each database db in master.sys.databases

for each table tbl in db.sys.tables

insert tbl into @.table variable

So far from what I've read, it looks like this require dynamic SQL. The only problem is that I'm not allowed to use dynamic SQL for this project! Any help is appriciated!

Thanks

If you want a .NET way to do this, check out SMO (SQL Management Objects). It seems to be the same backbone in which the SQL Management Studio was built upon.|||

There's the undocumented

sp_MSForEachDB

and

sp_MSForEachTable

|||

Just a note: You can't create a table variable with an [ SELECT... INTO ... ]

You will have pre-define the table variables,

or [ SELECT ... INTO #Temp ] tables.

|||

Sorry, didn't see this was a TSQL forum. The undocumented foreach would work, but would be dynamic.

Code Snippet

EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'

The ? is a way to represent the name of the database. This returns multiple result sets, but is the best you are going to get out of the box. Mainly, there is no consolidated metadata for the server. Each database hosts its own metadata.

|||

Nope, there is not way to do this. The ForEachDB thing that other suggest is a way to do it, but it would in fact be dynamic SQL

Why no dynamic SQL? If this is a production piece of code that has to be run multiple times, this is very much baffling to me.

Not even sure why you would want to do this. Can you explain what you are trying to do?

|||

Basically, I'm an intern at MS. I can't go into specifics of what I'm working on (NDA!), but it has to do with automating auditing tasks. The rest of my team is working on the main function and I'm writing a wrapper that needs to call that function on every Database, Table, and Object on the server. Because of the security risks, I was told no dynamic SQl... But that appears to be the only way to do it! What a conumdrum!

Thanks for the advice anyway!

|||

Here's the best way in my opinion...

Use SMO (SQL Management Objects) to enumerate through the tables in the server. Wrap this code into a .NET-based SQL Function. This, of course, assumes you can use SQL CLR for your solution. Otherwise, you will not be able to accomplish what you want to accomplish without dynamic SQL.

|||

Are you sure you aren't just being hazed? This sounds like something I would tell an intern if I had one to mess with

Seriously, this is a wierd stance considering that all of the tools that Microsoft builds use dynamic sql from the tool. SSMS does for sure. And, considering that the tool owns the code, not sure how security comes into play.

I agree with the SMO answer personally, if you aren't writing this in T-SQL directly, which you really couldn't. In 2005, using EXECUTE AS and proper use of QUOTENAME() you can mitigate the issues with security, if you are trying to write a stored procedure.

entry number thousand from 3 Tables with primary key number

Hi!
I have 3 tables with a primary key (number integer 4 ) and I have a datetime
field date_ (datetime 8) with the following value: 16.01.2003 00:00:04.
The primary key is not starting with one and some numbers are also missing.
Now I want to get the entry number thousand in the order after the date_
field with all rows from this 3 tables.
How must my select statement look like?
ThanksCan you further elaborate the following sentence:
Now I want to get the entry number thousand in the order after the date_
field with all rows from this 3 tables.
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068474664.222762@.news.liwest.at...
> Hi!
> I have 3 tables with a primary key (number integer 4 ) and I have a
datetime
> field date_ (datetime 8) with the following value: 16.01.2003 00:00:04.
> The primary key is not starting with one and some numbers are also
missing.
> Now I want to get the entry number thousand in the order after the date_
> field with all rows from this 3 tables.
> How must my select statement look like?
> Thanks
>

entity relationship diagram

I have VS 2005 and SQL Server 2005 Express installed and I created 4 tables and setup the primary and foreign keys and can view the individual foreign key relationships by right clicking on the foreign key.

Is there a way to view a table relationship diagram (fields with primary and foreign keys), such as Access provides. Does VS 2005 or SQL Server 2005 have that capability?

Thanks

If you right-click on the folder named Database Diagrams, you can create a databse diagram. You can create a diagram here. To view the folder, click the plus sign next to the database that you want to create the diagram for.

Entities Disappear in Ad Hoc Reports

I have a single data source view which contains 3 tables. Each table is a
completely denormalized (like a spreadsheet) dataset from a particular
business system. The tables themselves don't contain any foreign key
relationships, but within the data source view editor I created primary keys
on each table and created relationships between each table. However, when I
try to create a report in report builder, as soon as add an entity from one
table to my report, the other 2 tables disappear. I have found a few other
posts about this problem and the resolutions offered were (1) add a "refer",
but I don't know what that is; and (2) create or add relationships, but I
believe I have done that. What do I have to do to prevent the tables from
disappearing? Thanks.
Also, is there a good resource for reading up on Ad-Hoc reporting? The
books I have seen only cover it briefly.I figured this out. I need to add a role to each table in the Report Model
Designer. It is all in Books Online.
"Bodhicitta" wrote:
> I have a single data source view which contains 3 tables. Each table is a
> completely denormalized (like a spreadsheet) dataset from a particular
> business system. The tables themselves don't contain any foreign key
> relationships, but within the data source view editor I created primary keys
> on each table and created relationships between each table. However, when I
> try to create a report in report builder, as soon as add an entity from one
> table to my report, the other 2 tables disappear. I have found a few other
> posts about this problem and the resolutions offered were (1) add a "refer",
> but I don't know what that is; and (2) create or add relationships, but I
> believe I have done that. What do I have to do to prevent the tables from
> disappearing? Thanks.
> Also, is there a good resource for reading up on Ad-Hoc reporting? The
> books I have seen only cover it briefly.
>

Enterprise Mgr Problem in 2000 (Please HELP!)

Typically when I bring up Enterprise Mgr, I get the options to look at the databases, tables, etc. Now, when I choose EM, it brings up the Microsoft Management Console without anything in it except 'Console', 'Window', and 'Help'. When I open a new window, I can see the databases etc. but the top of the window says 'SQL Server Enterprise Manager [2:Console Root\Microsoft SQL Servers]' and a few of the icons are grayed out like 'New Database', 'New Login', and 'New Job'. I haven't changed anything that I know of. Please Help!

thx

Kat

One of the easy ways to fix this is to re run the install of the cliet tools from the server instal disk.

|||

fyi, the reinstallation worked. Also, working on an older pc without much space probably caused the problem.

thx,

Kat

Enterprise Mgr Not showing Table permissions Correctly

After moving DB's to a new server, the public role was
removed on some, but not all of the tables. We tried
setting the permissions to public in EM but although it
correctly sets it, it does not appear to be set. So now
by using EM I can't tell which tables have the correct
permissions. Has anybody seen this problem where EM does
not correctly show the permissions? Does anyone know how
to resolve this? Also, can anyone show me a way to set
public permissions on all tables through QA?
ThanksYes, I've seen it before. It seems to be some disconnect between
syspermissions and sysprotects. I thought that a way to resolve it might be
to create a new database and copy the user tables into it and grant
permissions in the new database. However, I never got around to doing this.
Public is a role, what permissions would you want to grant to public? Select
to all tables?
GRANT SELECT
ON authors -- you could use a cursor here
TO public
Or, if you have your users already in a group, you could just add that group
to the db_datareader role (if you want everyone to have select).
<paul.j@.aptalaska.com> wrote in message
news:3bcc01c47f14$7f5002c0$a401280a@.phx.gbl...
> After moving DB's to a new server, the public role was
> removed on some, but not all of the tables. We tried
> setting the permissions to public in EM but although it
> correctly sets it, it does not appear to be set. So now
> by using EM I can't tell which tables have the correct
> permissions. Has anybody seen this problem where EM does
> not correctly show the permissions? Does anyone know how
> to resolve this? Also, can anyone show me a way to set
> public permissions on all tables through QA?
> Thankssql

Enterprise Manager: defaults lost in import/export

Hi,
When I copy tables in a database from one server to another using
enterprise manager, everything copies ok, except for field defaults.

Has anyone seen this, and what is the solution?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973steve (UseLinkToEmail@.dbForumz.com) writes:
> When I copy tables in a database from one server to another using
> enterprise manager, everything copies ok, except for field defaults.
> Has anyone seen this, and what is the solution?

No, I have not seen it. Then again, I never copy tables with Enterprise
Manager.

The way that metadata should be installed in my opinion are from scripts
kept under source control. Relying on tools that you don't know what they
do under the cover is not a reliable process.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.dbForumz.com) writes:
> > When I copy tables in a database from one server to another
> using
> > enterprise manager, everything copies ok, except for field
> defaults.
> > Has anyone seen this, and what is the solution?
> No, I have not seen it. Then again, I never copy tables with
> Enterprise
> Manager.
> The way that metadata should be installed in my opinion are
> from scripts
> kept under source control. Relying on tools that you don't
> know what they
> do under the cover is not a reliable process.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks, Erland

I am used to mysql where you just backup table defs and data, and
easily copy the stuff to a new server.

Since I have a ton of tables, I dont want to set up anything by hand.
Is there an easy and bulletproof way to transfer data and contents
(and views and stored procedures) from one sever to another?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=588260|||steve (UseLinkToEmail@.dbForumz.com) writes:
> I am used to mysql where you just backup table def's and data, and
> easily copy the stuff to a new server.
> Since I have a ton of tables, I don't want to set up anything by hand.
> Is there an easy and bulletproof way to transfer data and contents
> (and views and stored procedures) from one sever to another?

If you want to transfer the entire database, just use BACKUP/RESTORE.
That's far more robust than scripting the lot. An alternative is
to use sp_detach_db/sp_attach_db.

The only time this is not possible is when you for some reason need to
change the collation. (Or the database is corrupt.) In this, if you don't
have scripts for your database under version control, you can script the
database from Enterprise Manager to det the definitions, and use BCP
to bulk data in and out. But relying on scripting is deceivable as you
have noticed. (I don't know what the Export/Import wizard does, but it
probably packages scripting and BCP:ing. I have never used it.)

If you want to move the metadata and data for some other reason, for
instance shipping from a development environment to a production
environment, then a version-control system is essential.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.dbForumz.com) writes:
> > I am used to mysql where you just backup table defs and data,
> and
> > easily copy the stuff to a new server.
> > Since I have a ton of tables, I dont want to set up anything
> by hand.
> > Is there an easy and bulletproof way to transfer data and
contents
> > (and views and stored procedures) from one sever to another?
> If you want to transfer the entire database, just use
BACKUP/RESTORE.
> Thats far more robust than scripting the lot. An alternative is
> to use sp_detach_db/sp_attach_db.
> The only time this is not possible is when you for some reason need
to
> change the collation. (Or the database is corrupt.) In this, if you
> dont
> have scripts for your database under version control, you can
script
> the
> database from Enterprise Manager to det the definitions, and use
BCP
> to bulk data in and out. But relying on scripting is deceivable as
you
> have noticed. (I dont know what the Export/Import wizard does,
> but it
> probably packages scripting and BCP:ing. I have never used it.)
> If you want to move the metadata and data for some other reason,
for
> instance shipping from a development environment to a production
> environment, then a version-control system is essential.

Hi Erland,

Backup/Restore is really good BUT cannot be used with a remotely
hosted server (if one does not have access to the file system, which I
dont). I am still at a loss for an easy solution.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=589880|||"steve" wrote:
> [quote:22bdd234c4="Erland Sommarskog2"]steve
> (UseLinkToEmail@.dbForumz.com) writes:
> > I am used to mysql where you just backup table def's and
> data, and
> > easily copy the stuff to a new server.
> > Since I have a ton of tables, I don't want to set up
> anything by hand.
> > Is there an easy and bulletproof way to transfer data and
> contents
> > (and views and stored procedures) from one sever to another?
> If you want to transfer the entire database, just use
> BACKUP/RESTORE.
> That's far more robust than scripting the lot. An alternative
> is
> to use sp_detach_db/sp_attach_db.
> The only time this is not possible is when you for some reason
> need to
> change the collation. (Or the database is corrupt.) In this,
> if you don't
> have scripts for your database under version control, you can
> script the
> database from Enterprise Manager to det the definitions, and
> use BCP
> to bulk data in and out. But relying on scripting is
> deceivable as you
> have noticed. (I don't know what the Export/Import wizard
> does, but it
> probably packages scripting and BCP:ing. I have never used
> it.)
> If you want to move the metadata and data for some other
> reason, for
> instance shipping from a development environment to a
> production
> environment, then a version-control system is essential.
> [/quote:22bdd234c4]
> Hi Erland,
> Backup/Restore is really good BUT cannot be used with a
> remotely hosted server (if one does not have access to the
> file system, which I don't). I am still at a loss for an easy
> solution.

I think I have figured out a strategy with Enter. Manager (EM) that
works.

First, I have EM create me a file which includes all the sql create
statements. Then I execute that on the server where I like to migrate
the db to. I can do that using Query Analyzer.

Now I have all the "metadata" properly set up. Next step is to copy
the records over using EM, which is easy.

So the key is to use the above process to create the tables FIRST, and
dont just copy tables over using EM.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=590002sql

Enterprise Manager, table CreateDate doesn't sort correctly

In EM, when viewing the tables in a database, the tables do not sort
correctly by CreateDate when you click the header. However, in other
databases on the same server, it sorts correctly. Odd.
The only subtle difference we've found is that those databases where the
table create date displays with AM or PM sorts correctly. Those that
display the millisecond in the Create Date do not sort correctly?
Any ideas on how to solve this problem? Thanks in advance!
Mark
SQL Server 2000 with latest and greatest service packs> In EM, when viewing the tables in a database, the tables do not sort
> correctly by CreateDate when you click the header. However, in other
> databases on the same server, it sorts correctly. Odd.
I've seen this too, and can't explain it (nor do I know the fix). I'd show
you the article on my site, but a middleman provider is having issues.
If you really need this functionality, run a query from sysobjects WHERE
xtype='U' ORDER BY crdate DESC...
--
Aaron Bertrand
SQL Server MVP
> The only subtle difference we've found is that those databases where the
> table create date displays with AM or PM sorts correctly. Those that
> display the millisecond in the Create Date do not sort correctly?
> Any ideas on how to solve this problem? Thanks in advance!
> Mark
> SQL Server 2000 with latest and greatest service packs
>

Enterprise Manager vs. INSTEAD OF triggers?

Hello all, i seem to have run into something weird...
I have a view which joins several tables (one main, and several relational
lookups). The view is simple-updateable, but i overrode this with an INSTEAD
OF UPDATE trigger to prevent people from changing the key fields, or the
lookup values. This works fine from Query Analyzer, or Access. If i try to
update one of the other fields, it simply doesnt take but the fields which
do get handled by the trigger do... all works fine.
However, if i open the view in Ent. Mgr. and type in changes to one of the
"non-updatable" fields, it actually updates it. It appears to be that when
making changes to data through the Enterprise Manager data views, it
bypasses any triggers (or at least INSTEAD OF triggers).
Is this actually the case? It seems kind of screwey, but that would seem to
be borne out by my little experiments here.
Cheers,
- Arthur.
When you open a view and update the data from EM, the UPDATE statemenet is
issued directly to the table, not to the view. That's why the trigger is
bypassed.
"Arthur Dent" wrote:

> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several relational
> lookups). The view is simple-updateable, but i overrode this with an INSTEAD
> OF UPDATE trigger to prevent people from changing the key fields, or the
> lookup values. This works fine from Query Analyzer, or Access. If i try to
> update one of the other fields, it simply doesnt take but the fields which
> do get handled by the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one of the
> "non-updatable" fields, it actually updates it. It appears to be that when
> making changes to data through the Enterprise Manager data views, it
> bypasses any triggers (or at least INSTEAD OF triggers).
> Is this actually the case? It seems kind of screwey, but that would seem to
> be borne out by my little experiments here.
> Cheers,
> - Arthur.
>
>
|||Arthur Dent wrote:
> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several
> relational lookups). The view is simple-updateable, but i overrode
> this with an INSTEAD OF UPDATE trigger to prevent people from
> changing the key fields, or the lookup values. This works fine from
> Query Analyzer, or Access. If i try to update one of the other
> fields, it simply doesnt take but the fields which do get handled by
> the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one
> of the "non-updatable" fields, it actually updates it. It appears to
> be that when making changes to data through the Enterprise Manager
> data views, it bypasses any triggers (or at least INSTEAD OF
> triggers).
> Is this actually the case? It seems kind of screwey, but that would
> seem to be borne out by my little experiments here.
> Cheers,
> - Arthur.
I'm not seeing that behavior here. I created an instead of update
trigger on a table and SQL EM spits out an error when the trigger is
violated. Here's the code. What do you see when you change a date to
value greater than today?
create table test (col1 int identity not null, col2 datetime not null)
go
insert into test values (getdate())
insert into test values (getdate() - 1)
insert into test values (getdate() - 2)
go
create trigger test_ioi on test
instead of update
as
begin
If Not Exists (Select * from inserted where col2 > getdate())
Update test
Set col2 = i.col2
From test, inserted i
Where test.col1 = i.col1
Else
RAISERROR ('Date provided is greater than today'' date. Update
failed.', 16, 1)
end
go
David Gugick
Imceda Software
www.imceda.com
|||My trigger is on a VIEW, not on a table. Jack answered above though, and
apparently explained why i get this behaviour.
In EM, when you open a view, its not actually opening the view, but the
underlying tables, only using the view definition. So triggers on the view
DO get bypassed.
|||Just an FYI, there is not such thing as "opening" a table or a view. EM does SELECT against the view
but issue the UPDATE/INSERT/DELETE against the table. Check out the WITH VIEW_METADATA option when
you create the view to handle this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:egeVjksOFHA.2468@.tk2msftngp13.phx.gbl...
> My trigger is on a VIEW, not on a table. Jack answered above though, and apparently explained why
> i get this behaviour.
> In EM, when you open a view, its not actually opening the view, but the underlying tables, only
> using the view definition. So triggers on the view DO get bypassed.
>

Enterprise manager taking up to 10 minutes to save design changes

Hi,
I am running SQL 2000 with an 8 GB database. I have two relatively large
tables (each with about 2 million records in them), and I occasionally need
to make changes to the table design (add a column, increase the size of an
existing field, etc.) on these two tables through the Enterprise Manager.
However, saving these changes can take betwen 5-10 minutes to complete.
If I make changes to any of my other smaller tables, they are saved
instantly. Is it really supposed to take this long for larger tables to be
updated?
Any suggestions would be greatly appreciated.Never make a change to a production db with EM. Most of the time it will
create a temp table, copy all the data over from the original, drop the
original and then rename the temp table to the original name. Most of the
time a simple ALTER TABLE is all you need to issue. If it is something
simple like increasing a varchar column it might just be a meta data change.
--
Andrew J. Kelly SQL MVP
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:BACB0B6C-FDA2-439A-BFA5-A5672AE7AE21@.microsoft.com...
> Hi,
> I am running SQL 2000 with an 8 GB database. I have two relatively large
> tables (each with about 2 million records in them), and I occasionally
> need
> to make changes to the table design (add a column, increase the size of an
> existing field, etc.) on these two tables through the Enterprise Manager.
> However, saving these changes can take betwen 5-10 minutes to complete.
> If I make changes to any of my other smaller tables, they are saved
> instantly. Is it really supposed to take this long for larger tables to
> be
> updated?
> Any suggestions would be greatly appreciated.

Enterprise Manager Problem

Suddenly one of my user started seeing one problem.. Whenever he tries
to connect to database and see tables through enterprise manager. Gets
error as shown below.
"An unexpected error happened during this operation.
[MS Design Tools] - You might not have permission to perform this
operation, or the object Administrator might no longer exist in
database."
Though previously there wasn't any problem.. I just droped and
recreated this login with same permission as previously. Even tried
assigning system administrator role to the login but no success.
Please help
Thanks
NJ
Try reinstall the client.
Quentin
"neeju" <njvds.o11@.gmail.com> wrote in message
news:1138897109.940035.202020@.z14g2000cwz.googlegr oups.com...
> Suddenly one of my user started seeing one problem.. Whenever he tries
> to connect to database and see tables through enterprise manager. Gets
> error as shown below.
> "An unexpected error happened during this operation.
> [MS Design Tools] - You might not have permission to perform this
> operation, or the object Administrator might no longer exist in
> database."
>
> Though previously there wasn't any problem.. I just droped and
> recreated this login with same permission as previously. Even tried
> assigning system administrator role to the login but no success.
> Please help
> Thanks
> NJ
>

Enterprise Manager Problem

Suddenly one of my user started seeing one problem.. Whenever he tries
to connect to database and see tables through enterprise manager. Gets
error as shown below.
"An unexpected error happened during this operation.
[MS Design Tools] - You might not have permission to perform this
operation, or the object Administrator might no longer exist in
database."
Though previously there wasn't any problem.. I just droped and
recreated this login with same permission as previously. Even tried
assigning system administrator role to the login but no success.
Please help
Thanks
NJTry reinstall the client.
Quentin
"neeju" <njvds.o11@.gmail.com> wrote in message
news:1138897109.940035.202020@.z14g2000cwz.googlegroups.com...
> Suddenly one of my user started seeing one problem.. Whenever he tries
> to connect to database and see tables through enterprise manager. Gets
> error as shown below.
> "An unexpected error happened during this operation.
> [MS Design Tools] - You might not have permission to perform this
> operation, or the object Administrator might no longer exist in
> database."
>
> Though previously there wasn't any problem.. I just droped and
> recreated this login with same permission as previously. Even tried
> assigning system administrator role to the login but no success.
> Please help
> Thanks
> NJ
>

Enterprise Manager Problem

Suddenly one of my user started seeing one problem.. Whenever he tries
to connect to database and see tables through enterprise manager. Gets
error as shown below.
"An unexpected error happened during this operation.
[MS Design Tools] - You might not have permission to perform this
operation, or the object Administrator might no longer exist in
database."
Though previously there wasn't any problem.. I just droped and
recreated this login with same permission as previously. Even tried
assigning system administrator role to the login but no success.
Please help
Thanks
NJTry reinstall the client.
Quentin
"neeju" <njvds.o11@.gmail.com> wrote in message
news:1138897109.940035.202020@.z14g2000cwz.googlegroups.com...
> Suddenly one of my user started seeing one problem.. Whenever he tries
> to connect to database and see tables through enterprise manager. Gets
> error as shown below.
> "An unexpected error happened during this operation.
> [MS Design Tools] - You might not have permission to perform this
> operation, or the object Administrator might no longer exist in
> database."
>
> Though previously there wasn't any problem.. I just droped and
> recreated this login with same permission as previously. Even tried
> assigning system administrator role to the login but no success.
> Please help
> Thanks
> NJ
>

Enterprise Manager in version 2000

Typically when I bring up Enterprise Mgr, I get the options to look at the databases, tables, etc. Now, when I choose EM, it brings up the Microsoft Management Console without anything in it except 'Console', 'Window', and 'Help'. When I open a new window, I can see the databases etc. but the top of the window says 'SQL Server Enterprise Manager [2:Console Root\Microsoft SQL Servers]' and a few of the icons are grayed out like 'New Database', 'New Login', and 'New Job'. I haven't changed anything that I know of. Please Help!

thx

Kat

A reinstallation of SQL Server solved the problem. I think it came up because I'm working on an old laptop while my newer one is getting fixed. Not much space, memory, and it is slow. Oh, the joy of technology and the obsolescence of all machines...

Kat

Enterprise Manager html error

Normally in Enterprise Manager the right hand pane displays icoms for
Diagrams, Tables, Views, etc. when a particulat database is clicked on in the
left hand pane.
For one particulat database, and only one, Enterprise Manager attempts to
display an html page with various tabs but this errors when loading and
displays wthout any information on it.
Why is this database differnet - I can't find any setting wich controls this
display or indeed any reference in BOL, and can I change it?
The error pops up an alert box as follows:
A Runtime Error has occurred.
Do you wish to Debug?
Line: 306
Error: Unspecified error.
unknown exeption
Break
Res://C:\...\sqlmmc.rll/Tabs.html
szDB =
parent.window.objSQLNSContext.GetDisplayName(strCo ntextCookie)
strContextCookie = GetParameterX("SQLNSCookie");
szDB = undefined
strContextCookie = "00FA25200000000000000001"
The line is called from JScript global code.
So, secondly, what is the error and can I fix it?
Thanks
Grant Ord
Grant,
You need to click on the database and then on the View tab change to
something other than Taskpad. Then change to Taskpad. The details should now
be fine. It is a known bug I think.
Chris Wood
Alberta Department of Energy
CANADA
"Grant Ord" <grant.ord@.nospam.nospam> wrote in message
news:1D78B191-9D4C-4834-957B-E07828400110@.microsoft.com...
> Normally in Enterprise Manager the right hand pane displays icoms for
> Diagrams, Tables, Views, etc. when a particulat database is clicked on in
the
> left hand pane.
> For one particulat database, and only one, Enterprise Manager attempts to
> display an html page with various tabs but this errors when loading and
> displays wthout any information on it.
> Why is this database differnet - I can't find any setting wich controls
this
> display or indeed any reference in BOL, and can I change it?
> The error pops up an alert box as follows:
> A Runtime Error has occurred.
> Do you wish to Debug?
> Line: 306
> Error: Unspecified error.
>
> unknown exeption
> Break
> Res://C:\...\sqlmmc.rll/Tabs.html
> szDB =
> parent.window.objSQLNSContext.GetDisplayName(strCo ntextCookie)
> strContextCookie = GetParameterX("SQLNSCookie");
> szDB = undefined
> strContextCookie = "00FA25200000000000000001"
> The line is called from JScript global code.
> So, secondly, what is the error and can I fix it?
> --
> Thanks
> Grant Ord
|||http://www.aspfaq.com/2293
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Ord" <grant.ord@.nospam.nospam> wrote in message
news:1D78B191-9D4C-4834-957B-E07828400110@.microsoft.com...
> Normally in Enterprise Manager the right hand pane displays icoms for
> Diagrams, Tables, Views, etc. when a particulat database is clicked on in
the
> left hand pane.
> For one particulat database, and only one, Enterprise Manager attempts to
> display an html page with various tabs but this errors when loading and
> displays wthout any information on it.
> Why is this database differnet - I can't find any setting wich controls
this
> display or indeed any reference in BOL, and can I change it?
> The error pops up an alert box as follows:
> A Runtime Error has occurred.
> Do you wish to Debug?
> Line: 306
> Error: Unspecified error.
>
> unknown exeption
> Break
> Res://C:\...\sqlmmc.rll/Tabs.html
> szDB =
> parent.window.objSQLNSContext.GetDisplayName(strCo ntextCookie)
> strContextCookie = GetParameterX("SQLNSCookie");
> szDB = undefined
> strContextCookie = "00FA25200000000000000001"
> The line is called from JScript global code.
> So, secondly, what is the error and can I fix it?
> --
> Thanks
> Grant Ord

Enterprise Manager html error

Normally in Enterprise Manager the right hand pane displays icoms for
Diagrams, Tables, Views, etc. when a particulat database is clicked on in the
left hand pane.
For one particulat database, and only one, Enterprise Manager attempts to
display an html page with various tabs but this errors when loading and
displays wthout any information on it.
Why is this database differnet - I can't find any setting wich controls this
display or indeed any reference in BOL, and can I change it?
The error pops up an alert box as follows:
A Runtime Error has occurred.
Do you wish to Debug?
Line: 306
Error: Unspecified error.
unknown exeption
Break
Res://C:\...\sqlmmc.rll/Tabs.html
szDB = parent.window.objSQLNSContext.GetDisplayName(strContextCookie)
strContextCookie = GetParameterX("SQLNSCookie");
szDB = undefined
strContextCookie = "00FA25200000000000000001"
The line is called from JScript global code.
So, secondly, what is the error and can I fix it?
--
Thanks
Grant OrdGrant,
You need to click on the database and then on the View tab change to
something other than Taskpad. Then change to Taskpad. The details should now
be fine. It is a known bug I think.
Chris Wood
Alberta Department of Energy
CANADA
"Grant Ord" <grant.ord@.nospam.nospam> wrote in message
news:1D78B191-9D4C-4834-957B-E07828400110@.microsoft.com...
> Normally in Enterprise Manager the right hand pane displays icoms for
> Diagrams, Tables, Views, etc. when a particulat database is clicked on in
the
> left hand pane.
> For one particulat database, and only one, Enterprise Manager attempts to
> display an html page with various tabs but this errors when loading and
> displays wthout any information on it.
> Why is this database differnet - I can't find any setting wich controls
this
> display or indeed any reference in BOL, and can I change it?
> The error pops up an alert box as follows:
> A Runtime Error has occurred.
> Do you wish to Debug?
> Line: 306
> Error: Unspecified error.
>
> unknown exeption
> Break
> Res://C:\...\sqlmmc.rll/Tabs.html
> szDB => parent.window.objSQLNSContext.GetDisplayName(strContextCookie)
> strContextCookie = GetParameterX("SQLNSCookie");
> szDB = undefined
> strContextCookie = "00FA25200000000000000001"
> The line is called from JScript global code.
> So, secondly, what is the error and can I fix it?
> --
> Thanks
> Grant Ord|||http://www.aspfaq.com/2293
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Ord" <grant.ord@.nospam.nospam> wrote in message
news:1D78B191-9D4C-4834-957B-E07828400110@.microsoft.com...
> Normally in Enterprise Manager the right hand pane displays icoms for
> Diagrams, Tables, Views, etc. when a particulat database is clicked on in
the
> left hand pane.
> For one particulat database, and only one, Enterprise Manager attempts to
> display an html page with various tabs but this errors when loading and
> displays wthout any information on it.
> Why is this database differnet - I can't find any setting wich controls
this
> display or indeed any reference in BOL, and can I change it?
> The error pops up an alert box as follows:
> A Runtime Error has occurred.
> Do you wish to Debug?
> Line: 306
> Error: Unspecified error.
>
> unknown exeption
> Break
> Res://C:\...\sqlmmc.rll/Tabs.html
> szDB => parent.window.objSQLNSContext.GetDisplayName(strContextCookie)
> strContextCookie = GetParameterX("SQLNSCookie");
> szDB = undefined
> strContextCookie = "00FA25200000000000000001"
> The line is called from JScript global code.
> So, secondly, what is the error and can I fix it?
> --
> Thanks
> Grant Ord