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

Enterprise SQL Projects (1000+ Stored Procedures)

Enterprise SQL Projects
--------
When Design is replaced with an Architectural Plan

The following post is intended as a starting point of some main concepts to consider when dealing with ent. sql projects. While it is not a direct question of any kind, it would interest people that are/or was involved in ent. projects and therefore have been troubled with similar problems.

Here is a quick overview of a couple main concepts when you have to deal with a Ent. Projects with 1000+ stored procedures.

DOCUMENTATION:
It is an absolute must to include 100% explanatory code on top of the sps.

FUNCTIONS:
Use functions to the maximum extent to reduce overal stored procedure complexity
a rule of thumb is to have 1 to 10, functions to sps ratio or simmilar.

TRIGGERS:
A lot to say about them that cannot be covered in this context

NAMING CONVENSION:
Your naming convension should be 100% pre-thought and designed, no mistakes allowed in this context as it will cause all stored procedures to be extremely difficult/impossible to browse.

a quick template could look as this:

sp
module name
underscore(_)
action (lower case)
noun (proper case)

For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory

(quoted by: tmorton)

my addition to this would be something like:
sp< as a prefix is surtently an overkill when dealing with 1000+ sps and is not needed.

however a lot more complex naming strategies can be used, that will cause the project to be a lot more easy to maintain.Just a note on your last comment - I would also like to submit that I have thought for several years now that prefacing stored procedure names with "sp" and / or table names with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but I don't think I've seen a compelling enough reason in years to continue this practice.

Of course, this is just my opinion.|||Russem:

Personally, it's all about readability and how the programmer feels which coding paradigm is easier for them to read. I personally love the Hungarian naming convention. For example, once you get into huge projects, i.e. > 1M lines of code, it gets more difficult to read the code, so by utilizing these prefixes, it sure helps the eyes (and brain)!|||::Just a note on your last comment - I would also like to submit that I have thought for
::several years now that prefacing stored procedure names with "sp" and / or table names
::with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but
::I don't think I've seen a compelling enough reason in years to continue this practice.

Naturally, though, this "compelling reason" some people seem to see has NOT included going to the documentation.

There you would find out that the official documentation says that whatever you name a stored procedure, you do NOT start it with "sp_".

Because contrary to what people that have not read the documentation do think, this means "System Procedure" (not Stored Procedure).

And it DOES make a difference. Let me quote:

::It is strongly recommended that you do not create any stored procedures using sp_ as a
::prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
::
::The stored procedure in the master database.
::
::The stored procedure based on any qualifiers provided (database name or owner).
::
::The stored procedure using dbo as the owner, if one is not specified.
::
::Therefore, although the user-created stored procedure prefixed with sp_ may exist in the
::current database, the master database is always checked first, even if the stored
::procedure is qualified with the database name.
::
::Important If any user-created stored procedure has the same name as a system stored
::procedure, the user-created stored procedure will never be executed.

Prefx if you want, but people following this should have the dignitiy to read the documentation.

Funnily, a lot of "sql gurus" in companies just prefix all "stored procedures" with "sp_" as this is "how ms does it, too".|||I'll prefix variables, sure. But I won't do it with stored procedures (and a standard prefix for all of them) and especially not with tables. I definitely agree with adding a prefix to variable names, though :)

Enterprise manager performance

Hello,
When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes to
display the stored procedures (for example).
I just clicked on the "Tables" node in the treeview, and it took over a
minute to list the tables.
Is there any way to improve the performance? As it stands, it borders on
the unusable...
Thanks for any suggestions.
Check the properties of your database. Is autoclose turned on?
By the way, you can find out lots of information via Query Analyzer. It can
do almost everything that Enterprise Manager does -- often it can do it
faster.
you can execute stored procedures to find out information
exec sp_tables
exec sp_stored_procedures
or you can execute queries
SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
use northwind
go
exec sp_tables
go
exec sp_columns 'Orders'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Thanks for the reply. I'll try Query Analyzer.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uwwAbcewEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Check the properties of your database. Is autoclose turned on?
> By the way, you can find out lots of information via Query Analyzer. It
can[vbcol=seagreen]
> do almost everything that Enterprise Manager does -- often it can do it
> faster.
> you can execute stored procedures to find out information
> exec sp_tables
> exec sp_stored_procedures
> or you can execute queries
> SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
>
> use northwind
> go
> exec sp_tables
> go
> exec sp_columns 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
> --
> Keith
>
> "Developer" <wanderer@.mapinfo.nope.com> wrote in message
> news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> to
on
>
|||Hi,
Have you found any valueable information in Query Analyzer? If you have
many tables, it might really be time-consuming.
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||QA will be sufficient.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:XQ5gLYwwEHA.2916@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you found any valueable information in Query Analyzer? If you have
> many tables, it might really be time-consuming.
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. We appreciate
> your patience and look forward to hearing from you!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is ridiculous -- I just waited 4 minutes for EM to open the property
sheet for a local database.
P4, 2.8 gig, 1 gig o' ram and a big hard disk.
There must be some sort of tuning of SQLServer (or EM) available to make it
respond like a modern application.
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Hi,
I understood that it must be frustrating!
Would you please use Profiler to trace the process when SQL Server
Enterprise Manager was opening? Send the Profiler Logs directly to me
v-mingqc@.online.microsoft.com (please remove 'online' as it's only for
SPAM). I would like to make further investigate.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
Thanks for sending me the Profiler Trace!
1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
N'true' in the trace, so that please make sure AUTO_CLOSE option for the
database was set to false(Not Selected)
2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing tab
BTW, you are using SQL Server 2000 SP3?
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||1. I have unchecked "Auto close" on the Options tab in the property sheet
for my db.
2. It appears ODBC tracing was on; I clicked the "Stop Tracing Now" button
on the Tracing tab of "ODBC Data Source Administrator".
"About SQL Server Service Manager" says it is version 8.00.760. I believe
it is SQL Server 2000 SP3.
Thanks for following up...
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I suspect that turning off ODBC tracing made the difference; EM and Query
Analyzer are responsive and useful.
Thanks.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>