Environment discrepencies concerning stored procedures & data types

Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

The text, ntext, and image data types are invalid in this subquery or aggregate

the error is supposedly the top line here:

SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='',
'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId) ,
c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId
END

GO

wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.Somehow you got single quotes stuck around your column names.

Select 'cms_Document_Contents_ID'=''
...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

SELECT *,
cms_Document_Contents_ID='',
cms_Language_ID='',
Title='',
XML='',
search_text='',
File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId),
c.active,
c.sequence,
c.cms_Document_Section_ID,
c.cms_Section_ID,
c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId

blindman|||Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

sp_attatch_db @.dbname = 'MyDB',
@.filename1 = 'c:\path\to\datafilename.mdf',
@.filename2 = 'c:\path\to\datafilename.mdf'

if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

.^sUbMSg-\/.

see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp for info on this stored procedure.

ps: f*ck that DTS export b*llocks im sticking with the raw data files.

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

Entries in Sysindexes

Recently a copy of a production database was made in order to try to improve
for an application that was experiencing slowness. Performance had
apparently improved until today. The developers are claiming that the
degredation in performance is due to extra enties in sysindexes with names
starting with '_WA_SYS_'. A more complete example is
'_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
When I execute sp_helpindex on a table with these kind of entires, I do not
see anything that corresponds to this name. I did notice that for a given
table there could be many entries like this. I do see entires in sysindexes
corresponding to actual indexes. I've also noticed that the entries startin
g
with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
Can someone explain what these '_WA_SYS_' entries are and can they be
deleted safely?
There is a mainteneace plan set up to due data & index page reorganization
everyday except Sunday. Could this be what is creating this entires?
Thanks
--
MGI believe those are index column statistics and I am sure they are not the
cause of any degraded performance. IMHO you should not delete them.
Nathan H. Omukwenyi.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
> Recently a copy of a production database was made in order to try to
> improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do
> not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in
> sysindexes
> corresponding to actual indexes. I've also noticed that the entries
> starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||When the Auto Create Statistics option is enabled, _WA_ indexes are
created for columns that do not have an index.
Somewhere I read, can't find the article right now, that if you are
seeing indexes named that way, it is advisable to add indexes to replace
them, as the Auto created ones are not as efficient as a regular index.
The article also stated that the Auto Create Statistics should not be
disable as any index is better then none.
HTH
Michael
nathan wrote:
> I believe those are index column statistics and I am sure they are not the
> cause of any degraded performance. IMHO you should not delete them.
> Nathan H. Omukwenyi.
>
>
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
>|||"Michael T" <michaelteff@.skyline.com> wrote in message
news:uD1bdQlYGHA.4424@.TK2MSFTNGP05.phx.gbl...
> When the Auto Create Statistics option is enabled, _WA_ indexes are
> created for columns that do not have an index.
> Somewhere I read, can't find the article right now, that if you are seeing
> indexes named that way, it is advisable to add indexes to replace them, as
> the Auto created ones are not as efficient as a regular index. The article
> also stated that the Auto Create Statistics should not be disable as any
> index is better then none.
>
Ok. Indexes and Statistics are distinct, but related objects. When you
create an index, statistics are created automatically, but you might want to
create additional statistics on unindexed columns or sets of columns.
See:
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Much of the information is valid for SQL 2000 too.
David|||MGeles,
Those entries are create automatically by sql server when the database
option "auto create statistics" is on. SQL Server create those statistics on
columns used in the "join" clause, or in the "where" clause, and there is no
t
an index associated to them (from where sql server can access distribution
statistics about those columns). These statistics are used by the query
optimizer when creating the execution plan. If you delete those entries, SQL
Server will create them again as soon as it needs them.
Statistical maintenance functionality (autostats) in SQL Server
http://support.microsoft.com/kb/q195565/
AMB
"MGeles" wrote:

> Recently a copy of a production database was made in order to try to impro
ve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do no
t
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindex
es
> corresponding to actual indexes. I've also noticed that the entries start
ing
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||MG-
Check out the link below.
http://www.extremeexperts.com/SQL/FAQ/SysStats.aspx
--
Thomas
"MGeles" wrote:

> Recently a copy of a production database was made in order to try to impro
ve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do no
t
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindex
es
> corresponding to actual indexes. I've also noticed that the entries start
ing
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MGsql

Entries in Sysindexes

Recently a copy of a production database was made in order to try to improve
for an application that was experiencing slowness. Performance had
apparently improved until today. The developers are claiming that the
degredation in performance is due to extra enties in sysindexes with names
starting with '_WA_SYS_'. A more complete example is
'_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
When I execute sp_helpindex on a table with these kind of entires, I do not
see anything that corresponds to this name. I did notice that for a given
table there could be many entries like this. I do see entires in sysindexes
corresponding to actual indexes. I've also noticed that the entries starting
with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
Can someone explain what these '_WA_SYS_' entries are and can they be
deleted safely?
There is a mainteneace plan set up to due data & index page reorganization
everyday except Sunday. Could this be what is creating this entires?
Thanks
--
MGI believe those are index column statistics and I am sure they are not the
cause of any degraded performance. IMHO you should not delete them.
Nathan H. Omukwenyi.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
> Recently a copy of a production database was made in order to try to
> improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do
> not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in
> sysindexes
> corresponding to actual indexes. I've also noticed that the entries
> starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||When the Auto Create Statistics option is enabled, _WA_ indexes are
created for columns that do not have an index.
Somewhere I read, can't find the article right now, that if you are
seeing indexes named that way, it is advisable to add indexes to replace
them, as the Auto created ones are not as efficient as a regular index.
The article also stated that the Auto Create Statistics should not be
disable as any index is better then none.
HTH
Michael
nathan wrote:
> I believe those are index column statistics and I am sure they are not the
> cause of any degraded performance. IMHO you should not delete them.
> Nathan H. Omukwenyi.
>
>
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
>> Recently a copy of a production database was made in order to try to
>> improve
>> for an application that was experiencing slowness. Performance had
>> apparently improved until today. The developers are claiming that the
>> degredation in performance is due to extra enties in sysindexes with names
>> starting with '_WA_SYS_'. A more complete example is
>> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
>> When I execute sp_helpindex on a table with these kind of entires, I do
>> not
>> see anything that corresponds to this name. I did notice that for a given
>> table there could be many entries like this. I do see entires in
>> sysindexes
>> corresponding to actual indexes. I've also noticed that the entries
>> starting
>> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
>> Can someone explain what these '_WA_SYS_' entries are and can they be
>> deleted safely?
>> There is a mainteneace plan set up to due data & index page reorganization
>> everyday except Sunday. Could this be what is creating this entires?
>> Thanks
>> --
>> MG
>|||"Michael T" <michaelteff@.skyline.com> wrote in message
news:uD1bdQlYGHA.4424@.TK2MSFTNGP05.phx.gbl...
> When the Auto Create Statistics option is enabled, _WA_ indexes are
> created for columns that do not have an index.
> Somewhere I read, can't find the article right now, that if you are seeing
> indexes named that way, it is advisable to add indexes to replace them, as
> the Auto created ones are not as efficient as a regular index. The article
> also stated that the Auto Create Statistics should not be disable as any
> index is better then none.
>
Ok. Indexes and Statistics are distinct, but related objects. When you
create an index, statistics are created automatically, but you might want to
create additional statistics on unindexed columns or sets of columns.
See:
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Much of the information is valid for SQL 2000 too.
David|||MGeles,
Those entries are create automatically by sql server when the database
option "auto create statistics" is on. SQL Server create those statistics on
columns used in the "join" clause, or in the "where" clause, and there is not
an index associated to them (from where sql server can access distribution
statistics about those columns). These statistics are used by the query
optimizer when creating the execution plan. If you delete those entries, SQL
Server will create them again as soon as it needs them.
Statistical maintenance functionality (autostats) in SQL Server
http://support.microsoft.com/kb/q195565/
AMB
"MGeles" wrote:
> Recently a copy of a production database was made in order to try to improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindexes
> corresponding to actual indexes. I've also noticed that the entries starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||MG-
Check out the link below.
http://www.extremeexperts.com/SQL/FAQ/SysStats.aspx
--
Thomas
"MGeles" wrote:
> Recently a copy of a production database was made in order to try to improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindexes
> corresponding to actual indexes. I've also noticed that the entries starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG

Enterprise Mgr

I have recently installed MSDE on my computer. Now I need to get a copy of
Enterprise Mgr or equivalent. Does anyone know if I can obtain this from
somewhere without having to purchase anything from M$http://www.aspfaq.com/show.asp?id=2442

--
David Portas
SQL Server MVP
--

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 settings migration?

Anyone knows if it is at all possible to Copy or Move the Server Registered under EM to another machine ?
ThanksOriginally posted by aashu
Anyone knows if it is at all possible to Copy or Move the Server Registered under EM to another machine ?

Thanks

but if have the registered server on ur EM then u can just use that to register the same servers on another instance of EM... or you could get the information for the server from the Client Network Utility: ipaddresses, servername etc.|||I am aware of that . Doing for 50 Servers will be a nightmare .. So , back to the question again , is there some ini file or EM setup file I could move over?

Thanks|||I know the information about registered servers is in the registry:
HKEY_CURRENT_USER\software\microsoft\microsoft sql server\80\tools\sqlew\registeredservers x

You should be able to export this key with regedt32. If you use regedit, you may only get the directory structure, and none of the data. Hope this helps.|||Thats what I was looking for !|||The same information is in HKEY_USERS\... But you have to guess at what your NT SID is. This is probably the more definitive place to get and store the information, as I think HKEY_CURRENT_USER is cleared when you log out.

As always, modifying your registry can have dire consequences, yadda, yadda, yadda.

Enterprise Manager Database Diagram Wizard

I am creating a Table Diagram via Enterprise Manager and the Database Diagra
m
Wizard.
When I copy and paste into Excel, it creates a first column with the numbers
0,1, and 2. Each of these numbers is next to a column. Are these numbers
indicating keys or indexes or what?
Let me know.
Thanks!Hi
Testing this out it seems that this is some internal code, the first column
always seems to have a 1 all other columns seem to be 0, unless it is the PK
which seems to add two to the value i.e. 3 if it is the first column and 2
if not.
Nullability/Datatype/FKs/Indexes do not seem to effect the value.
John
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E7F36117-2633-4F45-994F-3234A9843978@.microsoft.com...
>I am creating a Table Diagram via Enterprise Manager and the Database
>Diagram
> Wizard.
> When I copy and paste into Excel, it creates a first column with the
> numbers
> 0,1, and 2. Each of these numbers is next to a column. Are these numbers
> indicating keys or indexes or what?
> Let me know.
> Thanks!

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!Check to see if you have the Auto-Close option turned on any of your databases.
and check to see if OBDC tracing is turned on.|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?
> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?
> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>|||Sosh123 wrote:
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>> Check to see if you have the Auto-Close option turned on any of your
>> databases. and check to see if OBDC tracing is turned on.
Did you check all your databases for Auto-Close?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>> Check to see if you have the Auto-Close option turned on any of your
>> databases. and check to see if OBDC tracing is turned on.
Did you check all your databases for Auto-Close?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!
Check to see if you have the Auto-Close option turned on any of your databases.
and check to see if OBDC tracing is turned on.
|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>
|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>
|||Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas

"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!Check to see if you have the Auto-Close option turned on any of your databas
es.
and check to see if OBDC tracing is turned on.|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your datab
ases.
> and check to see if OBDC tracing is turned on.
>|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your datab
ases.
> and check to see if OBDC tracing is turned on.
>|||Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager and Licensing

Dear all
I've had difficulty finding any reference to this on Microsoft's site.
I have a copy of SQL Server installed on a server. I use he Enterprise
Manager, Query Analyser etc on my desktop PC.
Am I in breach of any licensing agreement if I were to install the
Enterprise Manager on more than one PC?
I look forward to hearing from you
Regards
Russell
It is my understanding that you can install the client tools on as many PCs
as you are licensed to connect from.
With that said, you probably don't want to install Enterprise Manager or
Query Analyzer on an end user's machine as these tools are geard for
developers and DBA types.
Keith
"Russell Farr" <RussellFarr@.discussions.microsoft.com> wrote in message
news:D578E8C5-F33C-4E93-941C-068621691098@.microsoft.com...
> Dear all
> I've had difficulty finding any reference to this on Microsoft's site.
> I have a copy of SQL Server installed on a server. I use he Enterprise
> Manager, Query Analyser etc on my desktop PC.
> Am I in breach of any licensing agreement if I were to install the
> Enterprise Manager on more than one PC?
> I look forward to hearing from you
> Regards
> Russell

Enterprise Manager 2000?

Hi all... I'm a total novice to MSSQL.
My web host says I need a copy of Enterprise Manager 2000 to access a MSSQL
DB they have set up on my site.
After looking at the MS site and various product literature... I am TOTALLY
confused.
What must I buy... the most minimum thing... to get a copy of Enterprise Ma
nager 2000. OR - is this anthing that is available for download anywhere?
Obviously, I'm not RUNNING SQL, I just need to manage a database that my web
host is running on their server.
Thanks in advance...
TimThis is quite a common question so I trying to find a "legally correct"
answer. However, in the mean time you could use this
SQL Server Web Data Administrator
http://www.microsoft.com/downloads/...&displaylang=en
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tmilo" <webmaster@.emailmover.com> wrote in message
news:397DCFF7-CD92-4478-9585-C3C3DEA07573@.microsoft.com...
> Hi all... I'm a total novice to MSSQL.
> My web host says I need a copy of Enterprise Manager 2000 to access a
MSSQL DB they have set up on my site.
> After looking at the MS site and various product literature... I am
TOTALLY confused.
> What must I buy... the most minimum thing... to get a copy of Enterprise
Manager 2000. OR - is this anthing that is available for download
anywhere?
> Obviously, I'm not RUNNING SQL, I just need to manage a database that my
web host is running on their server.
> Thanks in advance...
> Tim|||Thanks for the link, but.... unless I'm mistaken, the web solution you poi
nted me to is another server installed MS product. I'm not on a dedicated s
erver, so I cannot install additional software on it. So, I guess I'm stil
l where I started, and that
is what I must buy, at a minumum, to get a copy of Enterprise Manager. Or,
perhaps someone knows of an equivalent (and hopefully cheap) product?|||Hi,
Jasper pointed you to a correct link. To be more detailed
MS Web Data administrator acts similar to Enterprise
manager with reasonable functionalities. You dont need to
Install this on the Database Server(ISP).
The supported OS for running this tool are Windows XP and
Windows 2000 Server which has IIS . What else u r looking
for? Just Install this on your machine and you can manage
remote databases on a fly.
I am afraid you cannot use Enterprise manager until you
are a owner of a copy of SQL Server
Developer /standard/Personal/Enterpsise Editions.
Regards
Thirumal

>--Original Message--
>Thanks for the link, but.... unless I'm mistaken, the
web solution you pointed me to is another server installed
MS product. I'm not on a dedicated server, so I cannot
install additional software on it. So, I guess I'm still
where I started, and that is what I must buy, at a
minumum, to get a copy of Enterprise Manager. Or,
perhaps someone knows of an equivalent (and hopefully
cheap) product?
>
>.
>|||Developer Edition you can get for $49, and it includes a database engine as
well. I'm pretty sure you will be allowed to use that EM against your SQL
Server, but you'd want to check to be certain.
Also, on my web-site, the links section, you find URL's to a number of other
management tools, some free.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tmilo" <webmaster@.emailmover.com> wrote in message
news:2CC334F4-E3E7-446D-9D94-469F4318DAD2@.microsoft.com...
> Thanks for the link, but.... unless I'm mistaken, the web solution you
pointed me to is another server installed MS product. I'm not on a
dedicated server, so I cannot install additional software on it. So, I
guess I'm still where I started, and that is what I must buy, at a minumum,
to get a copy of Enterprise Manager. Or, perhaps someone knows of an
equivalent (and hopefully cheap) product?
>
>|||> I'm pretty sure you will be allowed to use that EM against your SQL
> Server, but you'd want to check to be certain.
I was told that my claim above is incorrect. I'm trying to hunt down some
documentation or link about it and will post back if I find such.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejiBpMmDEHA.3016@.TK2MSFTNGP11.phx.gbl...
> Developer Edition you can get for $49, and it includes a database engine
as
> well. I'm pretty sure you will be allowed to use that EM against your SQL
> Server, but you'd want to check to be certain.
> Also, on my web-site, the links section, you find URL's to a number of
other
> management tools, some free.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tmilo" <webmaster@.emailmover.com> wrote in message
> news:2CC334F4-E3E7-446D-9D94-469F4318DAD2@.microsoft.com...
> pointed me to is another server installed MS product. I'm not on a
> dedicated server, so I cannot install additional software on it. So, I
> guess I'm still where I started, and that is what I must buy, at a
minumum,
> to get a copy of Enterprise Manager. Or, perhaps someone knows of an
> equivalent (and hopefully cheap) product?
>|||Thanks all... I bought a copy of MSSQL Developer's version today. It sho
uld fit my needs nicely.
Appreciate the feedback!|||My MVP lead found such a reference for me. A below URL:
http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
There's a Q:
Q. Can I use SQL Server tools and services in conjunction with MSDE?
In the answer, there's this section:
"Note: The tools and services included with SQL Server Developer Edition may
not be used to manage production server environments."
This is pretty clear on the subject. I have requested from MS to provide
some FAQ or URL which discusses this scenario explicitly. If such will
appear, I don't know, or how long it would take...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYM3DGpDEHA.3804@.TK2MSFTNGP09.phx.gbl...
> I was told that my claim above is incorrect. I'm trying to hunt down some
> documentation or link about it and will post back if I find such.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ejiBpMmDEHA.3016@.TK2MSFTNGP11.phx.gbl...
> as
SQL
> other
you
> minumum,
>