"unable to load MDT2G.DLL" (sql 7.0)?

I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !
>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

"unable to load MDT2G.DLL" (sql 7.0)?

I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
--
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

"SQL Server does not exist or access denied", again...

I'm have that darn problem...

Here is our setup:

Remote clustered SQL Server 2000 running on a Windows Server 2003 Enterprise Edition box.

Local IIS 5.1 web server (my development box) running on Windows XP Tablet Edition.

I'm using Visual Studio.Net 2003 and using ASP.Net 1.1.4322.

I have an ASP.Net application that hits the remote SQL Server. When I'm on my development box and viewing my ASP.Net project locally via IE, the application retrieves values from the remote SQL Server with no problems. If I get onto another machine, launch IE, and then hit my development box's web server (which is running the asp.net app), I get the dreaded "SQL Server does not exist or access is denied" msg.

In my ASP.Net app, I have impersonate set to true and the website is set only to windows authentication. The remote SQL Server is set to SQL and Windows authentication.

I'm at a loss on what to do, any help would be greatly appreciated.

Thanks,

ExitusLSU

Hi,

This seems to be firewall issue.
Check it after disabling firewall.
http://bhatiaworld.blogspot.com/2005/12/dot-net-error-aspnet-website-or-web.html|||

I also forgot to mention my connection string. It is:

"Data Source=<server name>;Integrated Security=SSPI;Initial Catalog=<database name>"

I've tried using the IP address, but that didn't work.

Thanks,

ExitusLSU

"Orphaned" maintenance plan

Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
Julie
Hi Julie
"Kaidi" wrote:

> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John
|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
|||Hi Julie
"Kaidi" wrote:

> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!

> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.

> :P
> Julie
>
John
|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!

> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie

"Orphaned" maintenance plan

Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:

> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, yo
u
will see a node for management under which is SQL Server Agent and then Jobs
.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should b
e
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenan
ce plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:

> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!

> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.

> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!

> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie

"Orphaned" maintenance plan

Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> > Followup, for anyone who happens to hit this post on a usenet search.
> > (I hate finding my problem with no solution.
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> > Do *not* simply delete the job rows that are causing the problem from
> > the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> > taken me a couple of hours to track down all of the pieces/references
> > to the job in other tables. I'm just hoping to heck I got them all...I
> > went manually through all of the delete commands in the sp_delete_job
> > procedure, and praying that it worked!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie

"Login failed for user" error

Hi

I have installed sql server on my standalone PC.

I used "window and sql mix authentication" for the sql server in the enterprise manager set up.
when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
So Even though I use mix authentication, I am still using window login name.

Then I create the DSN file.

But when I run the ASP program, I have this error "login fail for user-("user name that I used for dsn file configuration").

I try to configure the dsn file set up with both authentications, windows and sql login names.

But I got the error again.

I am really lost here.
Please help me. thanks...What's your connection string?|||when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".

You cannot use a sql server login for the service accounts, these need to be windows accounts (domain or local user, or system account). The mixed authentication only applies to logins to the server.

"Full-Text Indexing" is Grayed Out, Disabled

I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
database, except when I go into the SQL Server Enterprise Manager and
click on the "Tools" drop down menu in the tool bar, the option
"Full-Text Indexing" is grayed out (aka greyed out).
I think I need to start a service named MSSEARCH; however, when I go
into Start -> Settings -> Control Panel -> Administrative Tools ->
Services, I do not see any service whose name is close to "MSSEARCH".
I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
but the closest I got was an error message stating "Cannot get
full-text default path from registry."
I also installed SQL Server Service Pack 3.
When I execute stored procedure `sp_fulltext_database 'enable'`, I get
this error message:
"Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
Line 46
Full-Text Search is not installed, or a full-text component cannot be
loaded"
Therefore, how do I get the "Full-Text Indexing" to no longer be
grayed out?
Or, how to I install, start or implement full-text indexing?
Sounds like SQL FTS has not been installed.
Put your SQL Server cd on the cd platter.
double click setup.bat from the cd. click next.
select local computer, click next.
select upgrade, remove, or add components to an existing instance of SQL
Server, click next.
select the instance you wish to add SQL FTS to, click next.
select add components to your existing installation, click next.
In the component section give focus to Server Component (it should be given
focus by default).
In the sub components section, select full text search, click next. click
next, and accept all remaining defaults.
<charismatic_evangelist@.yahoo.com> wrote in message
news:dd8e1652.0409081147.21b78b87@.posting.google.c om...
> I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
> database, except when I go into the SQL Server Enterprise Manager and
> click on the "Tools" drop down menu in the tool bar, the option
> "Full-Text Indexing" is grayed out (aka greyed out).
> I think I need to start a service named MSSEARCH; however, when I go
> into Start -> Settings -> Control Panel -> Administrative Tools ->
> Services, I do not see any service whose name is close to "MSSEARCH".
> I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
> but the closest I got was an error message stating "Cannot get
> full-text default path from registry."
> I also installed SQL Server Service Pack 3.
> When I execute stored procedure `sp_fulltext_database 'enable'`, I get
> this error message:
> "Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
> Line 46
> Full-Text Search is not installed, or a full-text component cannot be
> loaded"
> Therefore, how do I get the "Full-Text Indexing" to no longer be
> grayed out?
> Or, how to I install, start or implement full-text indexing?
|||C_E,
It is always a good idea to know what SQL Server version as well as Edition
is as the Full-Text Search (FTS) components are not supported on all SQL
Server 2000 Editions. See SQL Server 2000 Books Online (BOL) title "Features
Supported by the Editions of SQL Server 2000" as the "Microsoft Search
(MSSearch.exe) service is installed only with certain SQL Server editions.
Could you post the full output of the following SQL script?
select @.@.version
Installing SQL Server Service Pack 3 alone will not resolve this issue as
you will need to install the FTS components (if supported) and then
re-install SQL Server Service Pack 3 to fix any FTS bugs, issues, etc.
Depending upon the output of the sql script, you may be able to install the
FTS components from your SQL 2000 installation CD via custom installation
and select Full-Text Search under the server components list.
Regards,
John
<charismatic_evangelist@.yahoo.com> wrote in message
news:dd8e1652.0409081147.21b78b87@.posting.google.c om...
> I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
> database, except when I go into the SQL Server Enterprise Manager and
> click on the "Tools" drop down menu in the tool bar, the option
> "Full-Text Indexing" is grayed out (aka greyed out).
> I think I need to start a service named MSSEARCH; however, when I go
> into Start -> Settings -> Control Panel -> Administrative Tools ->
> Services, I do not see any service whose name is close to "MSSEARCH".
> I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
> but the closest I got was an error message stating "Cannot get
> full-text default path from registry."
> I also installed SQL Server Service Pack 3.
> When I execute stored procedure `sp_fulltext_database 'enable'`, I get
> this error message:
> "Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
> Line 46
> Full-Text Search is not installed, or a full-text component cannot be
> loaded"
> Therefore, how do I get the "Full-Text Indexing" to no longer be
> grayed out?
> Or, how to I install, start or implement full-text indexing?
|||Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
tried the steps that you gave below. The instance of the Server that I
applied it to was the (local)(Windows NT) server, where my database resides.
Even after going through all the steps, and having turned SQL FTS on, the
full text search is still greyed out when I right click on the particular
table I need to be able to search. Any other ideas of what to do?
- MacL
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Sounds like SQL FTS has not been installed.
> Put your SQL Server cd on the cd platter.
> double click setup.bat from the cd. click next.
> select local computer, click next.
> select upgrade, remove, or add components to an existing instance of SQL
> Server, click next.
> select the instance you wish to add SQL FTS to, click next.
> select add components to your existing installation, click next.
> In the component section give focus to Server Component (it should be
> given
> focus by default).
> In the sub components section, select full text search, click next. click
> next, and accept all remaining defaults.
>
|||Mac,
While you may have the same *symptoms* as Charismatic_Evangelist, you may or
may not have the same problem, especially as s/he never provided his/her SQL
Server 2000 version & edition info. It is always a good idea to know what
SQL Server version as well as Edition is as the Full-Text Search (FTS)
components are not supported on all SQL Server 2000 Editions. See SQL Server
2000 Books Online (BOL) title "Features Supported by the Editions of SQL
Server 2000" as the "Microsoft Search (MSSearch.exe) service is installed
only with certain SQL Server editions. Could you post the full output of the
following SQL script?
select @.@.version
You should also review the following KB article Q270671 (Q270671) "PRB: Full
Text Search Menus Are Not Enabled for Local Windows NT Accounts" at
http://support.microsoft.com/default...;en-us;q270671 depending
upon how you have the MSSQLServer service startup account setup.
If you did recently upgrade or install the FTS components, could you also
attach the following SQL setup log files: sqlstp.log (usually located at
C:\WINNT\Temp) and SearchSetup.log usually located at C:\WINNT\. These files
can also help in troubleshooting FTS installation issues.
Regards,
John
"Mac Littlefield" <warrenlittlefield@.sbcglobal.net> wrote in message
news:#Ai13dnpEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
> tried the steps that you gave below. The instance of the Server that I
> applied it to was the (local)(Windows NT) server, where my database
resides.[vbcol=seagreen]
> Even after going through all the steps, and having turned SQL FTS on, the
> full text search is still greyed out when I right click on the particular
> table I need to be able to search. Any other ideas of what to do?
> - MacL
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
click
>
|||There can be serveral causes of the Full-Text Index Table menu option not
being enabled
a.. SQL FTS is not enabled on this SQL server. Click here for instructions
on how to install SQL FTS on your SQL Server.
a.. You are not in the dbo role for this database
a.. The System Administrator role is not in the Local Administrators group
for this machine or the NTAuthority\Logon group is not in the System
Administrators group.
If you remove BUILTIN\Administrators and Full Text is enabled, add a login
account for the Local System account using sp_grantlogin [NT
Authority\System], and ensure that the login account is a member of the
sysadmin fixed server role. Here is some sample TSQL code to do this:
if not exists (select * from master.dbo.syslogins where loginname = N'NT
Authority\System')
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System', N'us_english'
GO
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
GO
a.. Something else yet undetermined. Microsoft acknowledges this problem in
this kb article
(http://support.microsoft.com/default...1&Product=sql2
k) and recommends that you use the full text stored procedures to manage
your catalog or tables.
When I run across this problem (I don't run across is often as typically I
use the full text stored procedures) I normally use the SQLFTSWiz.exe
program which can be found by default in: C:\Program Files\Microsoft SQL
Server\80\Tools\Binn you can launch it by opening a command prompt and
typing for NT Authentication:
sqlftwiz /n /sSQLServerName[\InstanceName] /dpubs
or for SQL Authentication:
sqlftwiz /sSQLServerName[\InstanceName] /dpubs /uAccount /pPassword
In both of the above examples, SQLServerName[\InstanceName] is the name of
the SQL Server you wish to connect to. If you are using the default instance
you would use the below:
sqlftwiz /sSQLServerName /dpubs /uAccount /pPassword
If you are using an instance, other than the default instance you would use
the below (where InstanceName is the name of the instance you are using):
sqlftwiz /sSQLServerName\InstanceName /dpubs /uAccount /pPassword
Pubs is the database you wish to create your full text table in.
The dialogs you get are for the most part identical to the dialogs you would
get if you were to just right click on a table. After the initial splash
screen select the table you wish to index. The rest of the dialogs are
identical to the dialogs you will see if you were to right click on a table
in EM and select Full Text Index table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mac Littlefield" <warrenlittlefield@.sbcglobal.net> wrote in message
news:%23Ai13dnpEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
> tried the steps that you gave below. The instance of the Server that I
> applied it to was the (local)(Windows NT) server, where my database
resides.[vbcol=seagreen]
> Even after going through all the steps, and having turned SQL FTS on, the
> full text search is still greyed out when I right click on the particular
> table I need to be able to search. Any other ideas of what to do?
> - MacL
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
click
>

"downgrade" edition ?

Hi
I save SQLServer 7.0 enterprise edition installed on NT
4.0 PDC.
And now i want to install SQLServer 2000 standard edition
and upgrade default instance. When i run SQLServer 2000
setup, the option "Upgrade..." is not available.
I think it happens because different version: enterprise
and standart (or not ?)
How i can "downgrade" SQLServer edition ?
Or i should backup all 7 databases, install SQLServer 2000
and restore all databases from backup,include master (to
preserve logins)?I can point you in the direction of a good article on this subject :-
http://www.sqlteam.com/item.asp?ItemID=9066
--
HTH
Ryan Waight, MCDBA, MCSE
"Slava Melnikov" <melnikov@.pochta.ru> wrote in message
news:0a0201c38eed$a99fee90$a301280a@.phx.gbl...
> Hi
> I save SQLServer 7.0 enterprise edition installed on NT
> 4.0 PDC.
> And now i want to install SQLServer 2000 standard edition
> and upgrade default instance. When i run SQLServer 2000
> setup, the option "Upgrade..." is not available.
> I think it happens because different version: enterprise
> and standart (or not ?)
> How i can "downgrade" SQLServer edition ?
> Or i should backup all 7 databases, install SQLServer 2000
> and restore all databases from backup,include master (to
> preserve logins)?

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?
Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>
|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> and
> against
> Enterprise
> do
>
|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
http://www.aspfaq.com/
(Reverse address to reply.)
|||
> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.
|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.

> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
http://www.aspfaq.com/
(Reverse address to reply.)

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
>> Within Sql Server Enterprise, I click on an object to show dependencies,
> and
>> there is something I am not seeing here.
>> For example, I want to see all dependencies for a given table. It shows
>> a
>> view and a couple of stored procedures that perform SELECT statements.
>> However, there is a stored procedure that performs UPDATE and INSERT
> against
>> that particular table, but I am not seeing on in the Display Dependencies
>> screen.
>> When I attempt to see the Dependencies screen on the stored procedure
>> that
>> does UPDATE and INSERT, I am not seeing the table listed.
>> There seems to be a disconnect some place either in my head or in
> Enterprise
>> Manager. What am I missing? How can I display, all, I mean ALL
>> dependencies? For a given table, I want stored procedures listed if they
> do
>> ANYTHING on the table. And views. And functions. All dependencies,
>> period. How can I do that?
>>
>|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.
> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
--
http://www.aspfaq.com/
(Reverse address to reply.)

"Change Tracking" with "Update Index in Background" FREEZE ENTERPRISE

Hi,
When trying to set up "Change Tracking" with "Update Index in Background"
the Enterprise Manager console freezes.
Do I need to stop some Windows services to do that ?
Configuration :
SQL Server 2000 + SP3
Windows 2000 + SP3
Any help will be appreciated.
Thanks.
This should not happen at all. What happens if you configure change tracking
with update index in background using TSQL.
Here is an example of how you would do this for the authors table:
exec sp_fulltext_table 'authors', 'start_change_tracking'
GO
exec sp_fulltext_table 'authors', 'start_background_updateindex'
GO
It is possible that you are experiencing some locking during the intial
population if you have a high number of inserts/updates/and deletes running.
Also are you running profiler? Profiler will degrade performance even while
you have paused the trace.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||Jean-Marc,
Most likely setting "Change Tracking" with "Update Index in Background" has
launched either an Incremental (if the table has a timestamp column) or a
Full Population in order to sync-up the MSSearch controlled FT Catalog with
the SQL Server 2000 table. Depending upon your server's resources this may
cause very high CPU-usage as well as with high memory and disk I/O to occur
during certain phases of the Incremental or Full Population and this will
have the affect of temporarily freezing the Enterprise Manager. Note, you
can use SQL Profiler or Perfmon or Task Mgr. to monitor the system usage.
Regards,
John
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||John-
I have seen your name on quite a few posts and I believe you are helping
one of my colleagues on a FTS problem.
I thought I saw somewhere that you might have a list of Microsoft Full
Text Search replacements?
I am having a heck of a time finding something to replace the MS FTS in
SQL Server 2000. So far all I can find are web crawlers.
Could you give me some suggestions?
Thanks,
Dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I'm sorry - hit submit by mistake.
I was actually going to offer some assistance on this question.
I did a small FTS build the other day, and I had the same problem. I
was using Query Analyzer, and it took 10 minutes for the second command
to complete execution (see previous posting).
If you do this in EM, expect the same behavior. It will appear to
freeze up for quite a while until it processes the commands.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

multiple schema report

tnx in advance for any help.
I have a multiple enterprise system in which every enterprise is being
modeled as a schema in the DB. This means that schemas are identical in
structure and when you connect to one schema, you are connecting to one
logical enterprise.
Now, I need to write a bunch of reports with the ability to connect
indiferently to on Enterprise or another (the user selects against which
Enterprise he wants to run the report to).
RS allows to define one or more Datasets for every report. But that has to
be an SQL instruction with schema qualifying "hardcoded" for every table you
use.
I've thought about dynamic SQL inside a stored procedure, and the enterprise
(schema) being passed as a parameter, but that's not such an elegant
solution.
So any suggestion will be gratefully welcome...
regardsIn RS 2005 you can base a data source on an expression. This sounds like
your scenario is exactly what this feature was put in for.
From Books Online:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> tnx in advance for any help.
> I have a multiple enterprise system in which every enterprise is being
> modeled as a schema in the DB. This means that schemas are identical in
> structure and when you connect to one schema, you are connecting to one
> logical enterprise.
> Now, I need to write a bunch of reports with the ability to connect
> indiferently to on Enterprise or another (the user selects against which
> Enterprise he wants to run the report to).
> RS allows to define one or more Datasets for every report. But that has to
> be an SQL instruction with schema qualifying "hardcoded" for every table
> you
> use.
> I've thought about dynamic SQL inside a stored procedure, and the
> enterprise
> (schema) being passed as a parameter, but that's not such an elegant
> solution.
> So any suggestion will be gratefully welcome...
> regards
>
begin 666 copycode.gif
M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
MT?_______P``````````````````````````````````````````````````
M`````````````````````````````````````````````````"'Y! $``&8`
M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
`
end|||Hello Edmundo,
Have you got the the problem sorted? I am having the same trouble. In my
case, I need to select a db schema at the run time. Here are methods I've
tried:
1. create a parameter to contain the db name. Add in the ODBC connection
string "database=Parameters!db_name.Value", this doesnt work. The alert
"database-Parameters!db_name.Value doesn't exist" turns up.
2. delete the "database = XXX" part from the connection string, so no
specific database in the DB server will be connected, a connection to the DB
server itself is established. create a parameter to contain the db name. in
the query use ?.attr_name=XXX. By doing this, I expect the parameter can
specify the DB name at the run time. this doesnt work either.
So if you have the solution, could you please post it up. It'll be really
appreciated!
cheers,
P
"Bruce L-C [MVP]" wrote:
> In RS 2005 you can base a data source on an expression. This sounds like
> your scenario is exactly what this feature was put in for.
> From Books Online:
> Data Source Expressions
> You can put an expression into a connection string to allow users to select
> the data source at run time. For example, suppose a multinational firm has
> data servers in several countries. With an expression-based connection
> string, a user who is running a sales report can select a data source for a
> particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a report
> parameter named ServerName:
> Copy Code
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
>
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
>
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
>
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
>
> e.. Be sure that the list of data sources shares the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
>
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Edmundo J. Davila" <edavila@.monisa.com> wrote in message
> news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> > tnx in advance for any help.
> >
> > I have a multiple enterprise system in which every enterprise is being
> > modeled as a schema in the DB. This means that schemas are identical in
> > structure and when you connect to one schema, you are connecting to one
> > logical enterprise.
> >
> > Now, I need to write a bunch of reports with the ability to connect
> > indiferently to on Enterprise or another (the user selects against which
> > Enterprise he wants to run the report to).
> >
> > RS allows to define one or more Datasets for every report. But that has to
> > be an SQL instruction with schema qualifying "hardcoded" for every table
> > you
> > use.
> >
> > I've thought about dynamic SQL inside a stored procedure, and the
> > enterprise
> > (schema) being passed as a parameter, but that's not such an elegant
> > solution.
> >
> > So any suggestion will be gratefully welcome...
> >
> > regards
> >
>
>

Database Scripting Question

I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,

> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:

> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>

Database Scripting Question

I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,

> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:

> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>