Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts

Tuesday, March 27, 2012

Can't check "Check existing data on creation" in relationship

Hi
When I added relationship to table, checked "Check existing data on
creation" and clicked "Save" in Enterprise Management tool, it has no error.
However, when I check tables again, the "Check existing data on creation"
option was unchecked.
Help me!It's the expected behaviour: the first time you create the
relationship, the existing data is verified (if "Check existing data on
creation" is checked) and subsequently all the new data is verified (if
"Enforce relationship for INSERTs and UPDATEs" is checked).
If you modify something in the relationship (for example: the
relationship's name or the cascade updates/deletes checkboxes), the
existing data does not need be verified again (that's why this checkbox
is unchecked when you look at it later).
Only if you modify the relationship in more semnificative ways (for
example: different columns), you should make sure that you click on
"Check existing data on creation" so the existing data will be verified
according to the new relationship.
Razvan

Can't Change Registry keys in Failover Clustering

Plat form: Windows 2003 enterprise ed,
SQL Server 2000 Failover Clustering SP3A enterprise ed.
Action: Change some registry keys after bring SQL resources offline.
I am trying to change the following registry keys to point to new location
on registry at
Master node, member node and virtual server. However, Sytem didn't allow me
to change. The Keys changed themself back after I brought SQL resources back
online or reboot nodes:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
FullTextDefaultPath
E:\DATA\MSSQL\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication
WorkingDirectory
E:\DATA\MSSQL\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot
E:\DATA\MSSQL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory
E:\DATA\MSSQL\JOBS
There must be somewhere hidden in the registry to override those keys I did
change. Can anyone help me figure out how to change those keys in failover
clustering? Is there anyone with such expience? how do you fix that?
Thank you.
Maggie
Hello Maggie,
This is the expected behavior for a clustered SQL Server 2000 instance. The behavior that you saw is due to the fact the registry entries are stored in the Quorum and are over written when you get the SQL Server
resource online. Changing the registry entries is NOT Supported (although there are ways to do it) and I do not recommend it.
Are you trying the registry changes on a production server or a test server?
What exactly are you trying to achieve?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||For SQL Server default installation, we have default the locations for the
following subdirectories:
BACKUP - default backup location
FTDATA - default FT Catalog location and FTS system config files
JOBS - SQL Agent working area
REPLDATA - Replication working area
However, we need to move them from default installation location to other
cluster disck drives in order apply our capacity planning, such as:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\
Backup from E:\data\mssql\backup to F:\backup
FullTextDefaultPath from E:\DATA\MSSQL\FTDATA to E:\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication\WorkingDirectory
E:\DATA\MSSQL\REPLDATA to R:\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot from E:\DATA\MSSQL to E:\DATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory from E:\DATA\MSSQL\JOBS to J:\JOBS
Plus, we even could not change any
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0 for applications, gathering
manager and indexer to redirect our full text indexes.
We are in middle of building SQL Server Failover Clustering . We are able
to change registry keys on other non-clustering regulger SQL Server to apply
our disk/file allocation design. But we can't change on the same registry
keys on Clustering as we always did on non-cluster. I hope that Microsoft
can work with us to solve this issue, and make failover clustering for SQL
Server configuable same as on reguler Window 2003 server enterprise ed.
Thank you.
Maggie
|||Maggie,
Even though you can change the registry entries for a regular SQL Server instance, it is not the recommended/supported method (infact it is the worst case scenario and done only with the assistance of a SQL Server
support engineer). SQL Server clustered instances are slightly different and the registry entries are replicated to all cluster nodes and are stored in the quorum. It is not supported to change the registry. That does not
mean one cannot change the registry keys for clustered SQL instances but then what is the guarantee that something may not break. So why go the unsupported path. Not worth it for a production server.
But this doesn't mean that one cannot change the location of the data files, log files, backups etc to other shared disk on the cluster. Supported steps that could be used on regular instances can still be used on
clustered SQL Server. For example, to move the log files on a seperate drive, one could used attach/detach method or Restore with Move.
KB "http://support.microsoft.com/?kbid=314546" has some ideas and links to related articles that you may find helpful.
You can also open up a case with Microsoft Customer Support Services for additional support. Call Support Customer Care: 1-800-936-3500 (U.S. and Canada Only)
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Actually, I already know these articles about moving databases around. And
also I always applied these procedures when each time I built new SQL Server.
However, There are not appliable to move default BACKUP, FTDATA ,JOBS and
REPLDATA folders to other disk drives instead of default E:\data\mssql\ on
failover clustering, such as,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\
Backup from E:\data\mssql\backup to F:\backup
FullTextDefaultPath from E:\DATA\MSSQL\FTDATA to E:\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication\WorkingDirectory E:\DATA\MSSQL\REPLDATA to R:\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot from E:\DATA\MSSQL to E:\DATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory from E:\DATA\MSSQL\JOBS to J:\JOBS
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0 for applications, gathering
manger, indexer
We do not want to point Backup, jobs, replication and FTDATA to same disk
drives. We need spread out files across the physical drives in order to
allocate resources efficiently. That's best way to implement enterprise
level database servers.
Oracle provide OFA(Optimal Flexible Architecture) to allow us to implement
very large database as part of software without additonal support call.
Microsoft should do the same feature if you have a big vision to support
enterprise databases.
Thanks.
"Uttam Parui[MS]" wrote:

> Maggie,
> Even though you can change the registry entries for a regular SQL Server instance, it is not the recommended/supported method (infact it is the worst case scenario and done only with the assistance of a SQL Server
> support engineer). SQL Server clustered instances are slightly different and the registry entries are replicated to all cluster nodes and are stored in the quorum. It is not supported to change the registry. That does not
> mean one cannot change the registry keys for clustered SQL instances but then what is the guarantee that something may not break. So why go the unsupported path. Not worth it for a production server.
> But this doesn't mean that one cannot change the location of the data files, log files, backups etc to other shared disk on the cluster. Supported steps that could be used on regular instances can still be used on
> clustered SQL Server. For example, to move the log files on a seperate drive, one could used attach/detach method or Restore with Move.
> KB "http://support.microsoft.com/?kbid=314546" has some ideas and links to related articles that you may find helpful.
> You can also open up a case with Microsoft Customer Support Services for additional support. Call Support Customer Care: 1-800-936-3500 (U.S. and Canada Only)
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||You are correct that one should spread out files across the physical drives in order to allocate resources efficiently. And this is how most of our customers do for their GB and TB dbs.
But can't we do the same thing without touching the registry. For example, if I want to use drive V: for Backup, W: for jobs, X: for Replication then we can make these drives dependencies for SQL Server and then use
them. For example, for the backup one will just have to point the new drive (in this case V. Similary for the jobs, the job definition will point to the appropriate drive. Same will be true for Replication (using TSQL). I am
not an expert on Fulltext and cannot speak for that though. Somebody in the Fulltext newsgroup may be able to answer how to change the default location using TSQL/SEM.
Please note that changing registry (for any SQL instance whether default or virtual) is not supported and may give you undesired results. It is always best to use TSQL or SEM to make the changes. There may be
certain things that cannot be done (as of now) -- for example changing the virtual server name (other than uninstalling/reinstalling) and we have filed a Design Change Request to the SQL development team.
If you feel a particular feature or change should be made, please feel free to write to sqlwish@.microsoft.com decribing the feature you want and its business impact. You may not get a reply back but the SQL
Developers are monitoring it and they will consider it for next versions/service pack changes.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thank you for your advice. I still have some questions:
1. Can you tell me how to change default location for V: for Backup, W: for
jobs, X: for Replication with TSQL/SEM ?
2. Can you ask your Fulltext team on how to change the default location
using TSQL/SEM on failover clusterng environment?
3. You may request Microsoft SQL Team to offer OFA(Optimal Flexible
Architecture) to the customers on Failover Clustering environment with
formal documents on implementation.
Best Regards,
Maggie
"Uttam Parui[MS]" wrote:

> You are correct that one should spread out files across the physical drives in order to allocate resources efficiently. And this is how most of our customers do for their GB and TB dbs.
> But can't we do the same thing without touching the registry. For example, if I want to use drive V: for Backup, W: for jobs, X: for Replication then we can make these drives dependencies for SQL Server and then use
> them. For example, for the backup one will just have to point the new drive (in this case V. Similary for the jobs, the job definition will point to the appropriate drive. Same will be true for Replication (using TSQL). I am
> not an expert on Fulltext and cannot speak for that though. Somebody in the Fulltext newsgroup may be able to answer how to change the default location using TSQL/SEM.
> Please note that changing registry (for any SQL instance whether default or virtual) is not supported and may give you undesired results. It is always best to use TSQL or SEM to make the changes. There may be
> certain things that cannot be done (as of now) -- for example changing the virtual server name (other than uninstalling/reinstalling) and we have filed a Design Change Request to the SQL development team.
> If you feel a particular feature or change should be made, please feel free to write to sqlwish@.microsoft.com decribing the feature you want and its business impact. You may not get a reply back but the SQL
> Developers are monitoring it and they will consider it for next versions/service pack changes.
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||See inline.
HTH,
Wish you a very happy and prosperous New Year.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
From: =?Utf-8?B?TWFnZ2ll?= <Maggie@.discussions.microsoft.com>
Subject: RE: Can't Change Registry keys in Failover Clustering
Date: Thu, 30 Dec 2004 09:13:06 -0800
Newsgroups: microsoft.public.sqlserver.clustering
Thank you for your advice. I still have some questions:
1. Can you tell me how to change default location for V: for Backup, W: for
jobs, X: for Replication with TSQL/SEM ?
<uttam>
- Assuming SQL Server resource is dependent on V:, W: and X:
- For Backup, if it is a TSQL backup job then I will basically change the TSQL statement to point to V:
For ex, If the backup job is as
BACKUP DATABASE TestDB
TO DISK = 'c:\Testdb.bak'
I will change it to
BACKUP DATABASE TestDB
TO DISK = 'V:\Testdb.bak'
Or if you are using database maintenance plan wizard to create the backup job, you will be able to use V: as the backup location.
- Same idea for jobs.
- For Replication, one method would be to use the TSQL command sp_changedistpublisher. See SQL Server Books Online for syntax.
</uttam>
2. Can you ask your Fulltext team on how to change the default location
using TSQL/SEM on failover clusterng environment?
<uttam> As experts in SQL Clustering are monitoring this newsgroup, experts in SQL fulltext are monitoring the fulltext newsgroup.
Please post your qs in the sql fulltext newsgroup.
http://www.microsoft.com/sql/communi...ver.fulltex t
For all SQL Server newsgroups, see
http://www.microsoft.com/sql/communi...s/default.mspx
</uttam>
3. You may request Microsoft SQL Team to offer OFA(Optimal Flexible
Architecture) to the customers on Failover Clustering environment with
formal documents on implementation.
<uttam> I can do that. But I would suggest that you directly email sqlwish@.microsoft.com. Its better for the development team to hear directly from our customers. It does makes a difference. Do write the business
impact (i.e. how this will benefit your business and/how you are hurting without it, etc) . All emails are monitored and are considered for next versions/service packs.
</uttam>
Best Regards,
Maggie

Sunday, March 25, 2012

Can't apply transaction logs after restoring a database in enterprise manager

Hi!
I created Full Backup of database and transaction logs in Enterprise manager
.. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.
Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.

Can't apply transaction logs after restoring a database in enterprise manager

Hi!
I created Full Backup of database and transaction logs in Enterprise manager
. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.

Thursday, March 22, 2012

Cant add bit column to unique index

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.

Can anyone shed any light on the problem?

Thanks.

You can not create index on BIT data type and that's the reason you can not see column.|||

Where can I find documentation on that? Why is that restriction in place?

|||

JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:

DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO
The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.
I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.
|||

I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.

|||Oh Thanks ZLA. Sorry about that.

Cant access table due to no owner (I think)

I can't access a number of tables on my SQL Server 7.0 database. Have checked using Enterprise Manager and the table does not appear to have any owner. When trying to do anything with the table I always get the following message

{SQL-DMO} The name 'Table Name' was not found in the tables collection. If the name is a qualified name, use [] to seperate various parts of the name, and the try again.

Have tried to change the owner by using sp_changeobjectowner but I am either getting the syntax wrong or it ain't working.

Any ideas??

c8lWhen you look in Enterprise Manager, what do you see?|||Hi,

The only thing I see is the table name (TABLE1), the type (USER) and the creation date. The owner column is blank. Normally you would expect it to say dbo.|||Blank?

What happends when you run this in QA?

SELECT * FROM INFORMATION_SCHEMA.Tables

Maybe you need to close EM and relaunch it. ...|||Thanks for the help but the problem has been resolved, I had to manually update the sysobject table so that the UID for each of the objects that could not be accessed was set to 1. For some reason this was 6.

Cheers
c8l

Tuesday, March 20, 2012

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
Thanks for your help
Israel,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Manager -- this should open the QA connection with the same credentials with which the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is the linked server name correct? Did you check on your server "B" that "SELECT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from Table') syntax?
4. Can you double check your linked server settings match the settings (e.g. Provider) that you have on the servers which are able to execute the query successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
> Thanks for your help

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Man
ager. When I log into Query Analyzer, I try to access the remote tables with
the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlse
rver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it wo
rks fine. Other servers have a linked server to this same server B (PGJE_SP_
06) and they work fine.
Thanks for your helpIsrael,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Mana
ger -- this should open the QA connection with the same credentials with wh
ich the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is
the linked server name correct? Did you check on your server "B" that "SELE
CT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from
Table') syntax?
4. Can you double check your linked server settings match the settings (e.g.
Provider) that you have on the servers which are able to execute the query
successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise M
anager. When I log into Query Analyzer, I try to access the remote tables wi
th the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sql
server authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it
works fine. Other servers have a linked server to this same server B (PGJE_S
P_06) and they work fine.
> Thanks for your helpsql

Monday, March 19, 2012

Cannot View Reports in Report Manager

Alrighty... This has been a bugger of a pain all day.
We have Enterprise RS on our test server - only the web part. The databases are located on the test sql server, and both servers are more than happy to talk to each other just fine. On the webserver where report manager is, we have tossed the default website and relocated the virtual directories to a different website, which is accesible over the network via an ip address. I can get into the report manager just fine.
Here's the problem: when I click on a report in the report manager, I cannot view the report. Initially the problem was that it would give a 403 access forbidden message, which doesn't make sense since the user was a content manager and full rights were available across the board. Now after several hours of playing it gives an action cancelled, cannot link to the requested webpage error. It gives it on any report, but I can access the report properties just fine. I can get into data sources just fine as well.
Upon reading something in here, I've tried editing the RSWebApplication.config file to change the
<ReportServerUrl>http://TestWWW/ReportServer</ReportServerUrl>
value to
<ReportServerUrl>http://10.1.1.74/ReportServer</ReportServerUrl>
which is the address of the site it should be located at. When I do that and save it, it get's kinda interesting - when I try to view any report, I get a directory listing (with it enabled for the directory) of 10.1.1.74 - /ReportServer/. Then when I click any of the directories, it takes me to 10.1.1.74/ReportServer/ - (note the change in hyphens) which displays all the uploaded files on the report server. If I click on a report, it displays the report!
What the heck is going on? Anyone seen this and fixed it?
- TCorrection: when I said it displayed the report, I spoke too soon. It brings up the window with the standard report bars, but hangs with the green spinning generation graphic.

Sunday, March 11, 2012

Cannot use TEXTIMAGE_ON when a table...

I am wondering if someone can help solve this question I have a table
in sql server 2000, I setup it using Enterprise manager.

When I generate an SQL Script for this table it scripts as:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Which is fine, however when I try to insert that into another database
using query anaylser I get the following error:

Server: Msg 1709, Level 16, State 1, Line 2
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image
columns.

OK I know I can remove the TEXTIMAGE_ON [PRIMARY] and that solves the
problem, however I have written some scripts to automate script
generation process, and this TEXTIMAGE thing, throws a spanner in the
automation process.

Any to suggestions as to why this is happening?

If I try building a new table manually using enterprise manager
creating the same table definition above, then script it, I get:

CREATE TABLE [dbo].[CubicleConfiguration2] (
[CubicleConfigurationID2] [int] IDENTITY (1, 1) NOT NULL ,
[Description2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Which is correct and should be generated in the first place

Any ideas as to why enterprise manager decides to add a TEXTIMAGE_ON
[PRIMARY] and break it?

The question is has something in the schema been corrupt?, how do I
return it back to normal?Hi

It seems that at some point the table may have contained a text or image
column and there is an entry left in sysindexes with an indid of 255 for
that table.

Without modifying sysindexes directly you may have to resort to re-creating
that table under another name, transfering the data, dropping the original
table and renaming the new one. Possibly setting the SQL-DMO TextFileGroup
Property will be possible, but I have not tried it.

John

"MrDom" <mr_dom_is@.hotmail.com> wrote in message
news:1117665527.868445.165330@.f14g2000cwb.googlegr oups.com...
>I am wondering if someone can help solve this question I have a table
> in sql server 2000, I setup it using Enterprise manager.
> When I generate an SQL Script for this table it scripts as:
>
> CREATE TABLE [dbo].[CubicleConfiguration] (
> [CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Which is fine, however when I try to insert that into another database
> using query anaylser I get the following error:
> Server: Msg 1709, Level 16, State 1, Line 2
> Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image
> columns.
>
> OK I know I can remove the TEXTIMAGE_ON [PRIMARY] and that solves the
> problem, however I have written some scripts to automate script
> generation process, and this TEXTIMAGE thing, throws a spanner in the
> automation process.
> Any to suggestions as to why this is happening?
> If I try building a new table manually using enterprise manager
> creating the same table definition above, then script it, I get:
>
> CREATE TABLE [dbo].[CubicleConfiguration2] (
> [CubicleConfigurationID2] [int] IDENTITY (1, 1) NOT NULL ,
> [Description2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Which is correct and should be generated in the first place
> Any ideas as to why enterprise manager decides to add a TEXTIMAGE_ON
> [PRIMARY] and break it?
> The question is has something in the schema been corrupt?, how do I
> return it back to normal?|||I don't really know, but what does this return:

select objectproperty(object_id('CubicleConfiguration',
'TableHasTextImage'))

If you get 1, and CubicleConfiguration doesn't have a text column, then
it's likely that there's some sort of metadata corruption - you could
try dropping and recreating the table to see if it fixes the problem.

Alternatively, if that isn't an option for some reason, and if you only
have one filegroup, then you could use the SQLDMOScript2_NoFG constant
to prevent the filegroup clause from being included in your script. (I
assume you're using SQLDMO to generate your scripts - if you're using a
third-party tool, then you'd have to check the documentation for the
tool).

Simon|||yes when i ran the above script it did infact return 1.|||I checked the the sysindexes for that database and I did infact find
another index, with an indid of 255.

It's strange that the index doesn't show up in enterprise manager, or
DBCC doesn't update it and remove it from the sysindex table.

Thanks for your help guys!|||Hi

That is because it is not really an index.

In BOL the documentation for sysindexes/Indid
255 = Entry for tables that have text or image data

I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
mopped it up, but it doesn't seem to.

John

"MrDom" <mr_dom_is@.hotmail.com> wrote in message
news:1117728125.050213.13480@.g14g2000cwa.googlegro ups.com...
>I checked the the sysindexes for that database and I did infact find
> another index, with an indid of 255.
> It's strange that the index doesn't show up in enterprise manager, or
> DBCC doesn't update it and remove it from the sysindex table.
> Thanks for your help guys!|||John Bell (jbellnewsposts@.hotmail.com) writes:
> That is because it is not really an index.
> In BOL the documentation for sysindexes/Indid
> 255 = Entry for tables that have text or image data
> I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
> mopped it up, but it doesn't seem to.

This script repros the problem:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
some_text text,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
Go
ALTER TABLE CubicleConfiguration DROP COLUMN some_text
go
select objectproperty(object_id('CubicleConfiguration'),
'TableHasTextImage')
go
DROP TABLE CubicleConfiguration

The good news is that SQL 2005 gets it right.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cannot use Large Page Extensions & AWE

Hi,

We are seeing the following line on the sql errorlog on a Itanium2 server running SQL Server 2005 Enterprise RC1.

Cannot use Large Page Extensions: lock memory privilege was not granted.

Is it something to be alarmed with?

This server has 16GB memory so it's my understaning that AWE does not have to be enabled on the instance as the 64-bit architecture can directly address more than 3GB of memory?
Cheers,
Priyanga


Few questions:

(1) Have you enabled AWE option. Assume not, but checking.
(2) Are you running 32-bit version of SQL Server on a 64-bit machine? 32-bit windows applications automatically run on 64-windows through WoW.
(3) Are you starting SQL Server with any trace flags?

|||

Short answer to your question, no, you don’t have to worry, but read on.

Indeed 64-bit SQL Server can address more than “usual” limit of 32 bit address space.

However, locked pages in memory privilege allows SQL Server to keep pages in memory and do not allow the OS to page them out. This may translate to performance gain, but does require the privilege. The privilege also allows to use large page extensions, which also may be benefitial for performance reasons.

You will get this message in default server configuration if you have large amounts of RAM (which you do).

I would recommend enabling the privilege and testing your server. You may get performance gain.

HTH,
Boris.

|||

Thanks Boris.

How do i enable the "locked pages in memory privilege" setting?
I could not see this as a sql server system configuration.

Also, to clarify, i don't need to enable "AWE enabled" configuraton on this instance as 64-bit does it natively?

Cheers,
Priyanga

|||

It is Windows level privilege. This is from Windows help:

You can configure this security setting by opening the appropriate policy and expanding the console tree as such: Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\.

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

I would like to emphasize the last point. Understand the implication of granting this privilege. E.g. if you have something else on this server, SQL Server may effectively consume most of the RAM, unless you set 'max server memory' as well. This is why I recommend testing your server.

As for the second quesiton, no, you do not need to set "AWE enabled" on 64-bit server.

Regards,
Boris.

Cannot use Enterprise Manager to browse database.

Dear.
In recently , I cannot use Enterprise Manager to browse
datase the database. Waiting are long long time.
However,when I use Query Analyzer that is ok, no problem.
Could you help me.
Thanks.Hi,
Can you please clear your Windows users profile and re- register the SQL
server.
Note: Take a beackup of profiler befire dropping.
Thanks
Hari.
MCDBA
"Raymond" <anonymous@.discussions.microsoft.com> wrote in message
news:12c9901c3f6b0$8f817990$a401280a@.phx
.gbl...
> Dear.
> In recently , I cannot use Enterprise Manager to browse
> datase the database. Waiting are long long time.
> However,when I use Query Analyzer that is ok, no problem.
> Could you help me.
> Thanks.
>|||Thanks your reply.
I want to ask ..? what is Windows users profile in SQL server.
Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OvAIYRr9DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can you please clear your Windows users profile and re- register the SQL
> server.
> Note: Take a beackup of profiler befire dropping.
>
> Thanks
> Hari.
> MCDBA
> "Raymond" <anonymous@.discussions.microsoft.com> wrote in message
> news:12c9901c3f6b0$8f817990$a401280a@.phx
.gbl...
>

Cannot use Enterprise Manager to browse Database.

Dear all.
In recently , I cannot use Enterprise Manager to borwse the database.
Waiting are long long time.
Server not responding.
However, Use the Query Analyzer to type SQL command that is OK...No problem.
Could you help me...?
Raymond.Hi,
Have you set auto close option on that database? If yes, disable it.
thanks
GYK

Cannot use Enterprise Manager to browse Database.

Dear all.
In recently , I cannot use Enterprise Manager to borwse the database.
Waiting are long long time.
Server not responding.
However, Use the Query Analyzer to type SQL command that is OK...No problem.
Could you help me...?
Raymond.Hi,
Have you set auto close option on that database? If yes, disable it.
thanks
GYK

Thursday, March 8, 2012

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx
.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.

Cannot update record through vb or Enterprise manager

When trying to update a specific row in a table an error
occurs.
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
SQLDumExceptionhandler. Process 51 generated fatal
exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
What could be the reason for this? There are no relations
hips or constraints on and between any tables.
What build of SQL Server is it ? Would suggest
I would run DBCC CHECKDB to check for corruption / specifically DBCC
CHECKTABLE on the table being queried
I would then look at the last SQL Server Errorlog when it happened and see
if there are any matches on http://www.microsoft.com/support for kb's that
have a similar pattern ie on the Short Stack Dump info reported. Below is
an example - so if you had this output I would search the kb and google
(groups and web) for Fill6Xdata . Be aware that some of the function calls
are quite generic so watch out for false positives.
Ideally you should open a case with PSS
-
Short Stack Dump
0069EF5F Module(sqlservr+0029EF5F) (Fill6xData(unsigned char *,class
CXVariant *,class CTypeInfo const *,unsigned long *)+0000009A)
0069BEDE Module(sqlservr+0029BEDE) (intnl_paramdata(struct srv_proc
*,int)+000000DB)
regards,
Andy.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:249c701c46020$9cdb8a70$a601280a@.phx.gbl...
> When trying to update a specific row in a table an error
> occurs.
> [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]
> SQLDumExceptionhandler. Process 51 generated fatal
> exception c0000005 ACCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> What could be the reason for this? There are no relations
> hips or constraints on and between any tables.

Cannot uninstall reporting services

Hi,
I am trying to install reporting services client tools onto my desktop PC.
It appears that reporting services enterprise edition have been installed on
here before but there is nothing listed in add/remove programs for RS.
An excerpt of the install log is below, I am not sure what is setting the
paths for the reporting services databases to be on "d:\program files" as
this is not on my PC. I have searched through the registry with no joy.
Can anyone tell me how I can completely remove RS and start again? Oh yes
and I have checked for the COM objects prescence on my PC too and there are
not there.
Thanks in advance for your help,
Nick
-- excerpt from installation log
Property(C): DataBases.3882F579_1BCB_4C44_991C_060D38C9C09D = D:\Program
Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Databases\
Property(C): SamplesDB1 = AdventureWorks2000
Property(C): CostingComplete = 1
Property(C): OutOfDiskSpace = 0
Property(C): OutOfNoRbDiskSpace = 0
Property(C): PrimaryVolumeSpaceAvailable = 0
Property(C): PrimaryVolumeSpaceRequired = 0
Property(C): PrimaryVolumeSpaceRemaining = 0
Property(C): _FeatDescription = Report Server
Property(C): _DefaultInstance = Installed
Property(C): SQL_SHOWLIC = 1
Property(C): RSAutoStart = 1
Property(C): _Service = RS
Property(C): Asp_Net_Accout_For_GUI_Display = URSUS\ASPNET
Property(C): WizardDialog = StartCopyDlg
Property(C): DisableBack = MaintenanceDlg
Property(C): SqlMaintType = 1
=== Logging stopped: 28/02/2006 15:11:48 === MSI (c) (30:20) [15:11:48:982]: Note: 1: 1729
MSI (c) (30:20) [15:11:48:982]: Product: Microsoft SQL Server 2000 Reporting
Services Enterprise Edition -- Configuration failed.
MSI (c) (30:20) [15:11:48:992]: Grabbed execution mutex.
MSI (c) (30:20) [15:11:48:992]: Cleaning up uninstalled install packages, if
any exist
MSI (c) (30:20) [15:11:48:992]: MainEngineThread is returning 1603
=== Verbose logging stopped: 28/02/2006 15:11:48 ===Hi,
replying to my own post!!
I actually eventually found some rogue entries in the registry with the help
of "regmon". There were alittle obscure, well that's my excuse and I'm
sticking to it!
Nick
"Nick Long" wrote:
> Hi,
> I am trying to install reporting services client tools onto my desktop PC.
> It appears that reporting services enterprise edition have been installed on
> here before but there is nothing listed in add/remove programs for RS.
> An excerpt of the install log is below, I am not sure what is setting the
> paths for the reporting services databases to be on "d:\program files" as
> this is not on my PC. I have searched through the registry with no joy.
> Can anyone tell me how I can completely remove RS and start again? Oh yes
> and I have checked for the COM objects prescence on my PC too and there are
> not there.
> Thanks in advance for your help,
> Nick
> -- excerpt from installation log
> Property(C): DataBases.3882F579_1BCB_4C44_991C_060D38C9C09D = D:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Databases\
> Property(C): SamplesDB1 = AdventureWorks2000
> Property(C): CostingComplete = 1
> Property(C): OutOfDiskSpace = 0
> Property(C): OutOfNoRbDiskSpace = 0
> Property(C): PrimaryVolumeSpaceAvailable = 0
> Property(C): PrimaryVolumeSpaceRequired = 0
> Property(C): PrimaryVolumeSpaceRemaining = 0
> Property(C): _FeatDescription = Report Server
> Property(C): _DefaultInstance = Installed
> Property(C): SQL_SHOWLIC = 1
> Property(C): RSAutoStart = 1
> Property(C): _Service = RS
> Property(C): Asp_Net_Accout_For_GUI_Display = URSUS\ASPNET
> Property(C): WizardDialog = StartCopyDlg
> Property(C): DisableBack = MaintenanceDlg
> Property(C): SqlMaintType = 1
> === Logging stopped: 28/02/2006 15:11:48 ===> MSI (c) (30:20) [15:11:48:982]: Note: 1: 1729
> MSI (c) (30:20) [15:11:48:982]: Product: Microsoft SQL Server 2000 Reporting
> Services Enterprise Edition -- Configuration failed.
> MSI (c) (30:20) [15:11:48:992]: Grabbed execution mutex.
> MSI (c) (30:20) [15:11:48:992]: Cleaning up uninstalled install packages, if
> any exist
> MSI (c) (30:20) [15:11:48:992]: MainEngineThread is returning 1603
> === Verbose logging stopped: 28/02/2006 15:11:48 ===>|||If you're re-installing all of SQLServer, make sure you delete all the
MSSQL folders.
For RS, you may want to check and confirm that everything is gone out
of IIS as well.
Jim

Wednesday, March 7, 2012

Cannot stop or start SQL server from SQLEM

Hi,
Merry Christmas to Everyone.
We have a SQL2k server that is not in any domain (in a
workgroup). I registered it in the Enterprise Manager on
my workstation with the sa account. However, I can do
anything but stop or start the SQL server and server
agent. Here is the error message:
An error 5 - (Access is denied) occurred while performing
this service operation on the SQLServerAgent service.
Any suggestions?
Thanks.
David WangServices are an OS thing, not an SQL thing. Log in as
admin.
>--Original Message--
>Hi,
>Merry Christmas to Everyone.
>We have a SQL2k server that is not in any domain (in a
>workgroup). I registered it in the Enterprise Manager on
>my workstation with the sa account. However, I can do
>anything but stop or start the SQL server and server
>agent. Here is the error message:
>An error 5 - (Access is denied) occurred while performing
>this service operation on the SQLServerAgent service.
>Any suggestions?
>Thanks.
>David Wang
>.
>|||Yes, you are right. I have no problem to do this if I log
in from the local server. I am trying to do this from a
remote machine. I thought the sa account could impersonate
the local system admin role.
Thanks.
>--Original Message--
>Services are an OS thing, not an SQL thing. Log in as
>admin.
>>--Original Message--
>>Hi,
>>Merry Christmas to Everyone.
>>We have a SQL2k server that is not in any domain (in a
>>workgroup). I registered it in the Enterprise Manager on
>>my workstation with the sa account. However, I can do
>>anything but stop or start the SQL server and server
>>agent. Here is the error message:
>>An error 5 - (Access is denied) occurred while
performing
>>this service operation on the SQLServerAgent service.
>>Any suggestions?
>>Thanks.
>>David Wang
>>.
>.
>|||sa is not an administrator to the operating system.

Saturday, February 25, 2012

cannot start or verify that SQL 2005 is running

Hi,

I am new to sql 2005.

I have installed a trial version of 2005 enterprise edition.

After installing I went to Management Studio. I noticed that the server name

dropdown menu was blank so I tried to browse. No server names appear under local

or network servers. I then went to configuration manager and noticed that

although client protocols are listed under SQL client native config,

SQL server 2005 services and sql server 2005 network config are both blank. Is SQL

installed correctly? I hope this is enough info to illicit a helpful response.

Thank You.

Problem solved. I went to add remove apps and clicked change

on Microsoft SQL Server 2005 button. I am not sure what Microsoft

SQL Server Native Client is so I didn't mess with it. I saw that no instances

were listed under SQL Server 2005 instances so I clicked away until I had

added an instance - MSSQLSERVER. I am going to be using .net 2.0 framework

objects to connect to SQL so I am not sure if I messed up by Not installing in

mixed mode. Any advice on the mixed mode issue would be appreciated.

|||If you have no need for mixed mode, then don't install it. It kind of sounds like you don't so no worries.

cannot start more transactions on this session

when I modify data in Enterprise manager, I get the message
"cannot start more transactions on this session"
Why?Is the data your modifying contained within a view rather than a table..?
--
HTH
Ryan Waight, MCDBA, MCSE
"haode" <iamjhkang@.hotmail.com> wrote in message
news:enVQjp1oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> when I modify data in Enterprise manager, I get the message
> "cannot start more transactions on this session"
> Why?
>|||It is a table for sure.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uepb573oDHA.3256@.tk2msftngp13.phx.gbl...
> Is the data your modifying contained within a view rather than a table..?
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "haode" <iamjhkang@.hotmail.com> wrote in message
> news:enVQjp1oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > when I modify data in Enterprise manager, I get the message
> > "cannot start more transactions on this session"
> >
> > Why?
> >
> >
>