SQL Configure: conflicting

Showing posts with label conflicting. Show all posts

Saturday, 25 February 2012

Conflicting SQL Version Info

Hi,
I am managing an instance of SQL server which is running replication, I
am not sure what SP is running on this version as I am getting
conflicting information when I run "Print @.@.Version", the following is
the result:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
The version number tells me it is SP4 but the last line "Build 3790:
Service Pack 1" tells me otherwise.
Has anyone come across this before? I tried running the SP4 upgrade but
got an error during installation, something about replication.
Also the subscribers are running the desktop engine version of SQL and
have a different version than the main server. When I run "Print
@.@.Version" I get:
"Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
Will this cause me issues? Should I upgrade the subscribers to SP4 as
well?
Please help.
Ben
the last line refers to the version of the os. The first one is SQL 2000 sp
4 running on win2003 sp 1. The second is SQL 2000 RTM, running on Windows xp
sp2.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Benzine" <bfausti@.gmail.com> wrote in message
news:1168386601.375776.238150@.i56g2000hsf.googlegr oups.com...
> Hi,
> I am managing an instance of SQL server which is running replication, I
> am not sure what SP is running on this version as I am getting
> conflicting information when I run "Print @.@.Version", the following is
> the result:
> "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
> The version number tells me it is SP4 but the last line "Build 3790:
> Service Pack 1" tells me otherwise.
> Has anyone come across this before? I tried running the SP4 upgrade but
> got an error during installation, something about replication.
> Also the subscribers are running the desktop engine version of SQL and
> have a different version than the main server. When I run "Print
> @.@.Version" I get:
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
> Will this cause me issues? Should I upgrade the subscribers to SP4 as
> well?
> Please help.
> Ben
>

Conflicting SQL Client Settings

Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
backups), but Microsoft Content Management Server 2002 SP1A likes Named
Pipes for MCMS Server Side Site Deployment Object export to work properly!
I am faced with a big dilemma!
See
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
I have been experiencing frequest "General network Error"s, when I use
Server side SDO to export the CMS repository (as a backup, before importing
a SDO file from another test server). This article recommends the use of
Named Pipes in preference over TCP/IP for SQL Client Network settings.
However, there has been some recent failures in SQL Server Flat file backup
(SQL SErver maintenance plan to do full backup all application databases
with verify). Invesigation reveal (quite consistently) that the flat file
backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
use. As soon as SQL Client is changed to use TCP/IP, backup function as
expectd. The error that are logged when Named pipes are used as follows
*1) The error logged in the error log file is :
2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK ' failed to open.
Operating system error = 32(error not found).
2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
BACKUP DATABASE [YJBLive] TO DISK =
N'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BA K' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
*2) If I change the job to just do the backup without verify, then the job
completes, but
*2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
which only succeeded in backing up without verifying is 1xxMB large
*3) The Job History indicates
Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
step to run was step 1 (Step 1).
*4) The backup file appear corrupted:
*4.1) If I look at the Maintenance Plan history, I found:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408231407.BAK '. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
*4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB, it
doesn't work. After the popup to say restore has completed, Enterprise
Manager shows the database as grey with "(Loading)" appended to the database
name (even after 5mins)
*5) The user group Everyone has full permission on d:\MSSQL\BACKUP
*6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
So, I am really stuck here, could some SQL Server / MCMS expert help me out
here! If I use Named Pipes, SQL Backup is not going to work. If I use
TCP/IP, then my MCMS export/import would not work most of the time!
Hi Patrick,
1) MCMS likes all protocols if SP1 or SP1a are installed
2) creating an ALIAS in SQL tools for the different application would allow
to configure the protocols for all apps individually.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
http://www.gotdotnet.com/community/u...emen t+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
> backups), but Microsoft Content Management Server 2002 SP1A likes Named
> Pipes for MCMS Server Side Site Deployment Object export to work properly!
> I am faced with a big dilemma!
> See
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> I have been experiencing frequest "General network Error"s, when I use
> Server side SDO to export the CMS repository (as a backup, before
importing
> a SDO file from another test server). This article recommends the use of
> Named Pipes in preference over TCP/IP for SQL Client Network settings.
> However, there has been some recent failures in SQL Server Flat file
backup
> (SQL SErver maintenance plan to do full backup all application databases
> with verify). Invesigation reveal (quite consistently) that the flat file
> backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
> use. As soon as SQL Client is changed to use TCP/IP, backup function as
> expectd. The error that are logged when Named pipes are used as follows
> *1) The error logged in the error log file is :
> 2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK ' failed to open.
> Operating system error = 32(error not found).
> 2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
> BACKUP DATABASE [YJBLive] TO DISK =
> N'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BA K' WITH INIT ,
NOUNLOAD
> , NOSKIP , STATS = 10, NOFORMAT
>
> *2) If I change the job to just do the backup without verify, then the job
> completes, but
> *2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
> which only succeeded in backing up without verifying is 1xxMB large
> *3) The Job History indicates
> Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
> step to run was step 1 (Step 1).
> *4) The backup file appear corrupted:
> *4.1) If I look at the Maintenance Plan history, I found:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408231407.BAK '. Device error or
device
> off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> *4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB,
it
> doesn't work. After the popup to say restore has completed, Enterprise
> Manager shows the database as grey with "(Loading)" appended to the
database
> name (even after 5mins)
> *5) The user group Everyone has full permission on d:\MSSQL\BACKUP
> *6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
> So, I am really stuck here, could some SQL Server / MCMS expert help me
out
> here! If I use Named Pipes, SQL Backup is not going to work. If I use
> TCP/IP, then my MCMS export/import would not work most of the time!
>
|||Thanks stefan,
Can I just check if the FAQ in regards to "General Network Error" when using
server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
comes with SP1A) or not? If not, could you suggest any other remedies? I
have tried all the remedies listed in the FAQ at
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
and still we are getting quite frequent "General Network Error" when
performing export on a repository (whose SQL DB is 1GB ish and whose SDO
file is 80MB). e.g. timeouts/maxium request size are much larger than that
required already!!
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> Hi Patrick,
> 1) MCMS likes all protocols if SP1 or SP1a are installed
> 2) creating an ALIAS in SQL tools for the different application would
allow
> to configure the protocols for all apps individually.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
> MCMS Sample Code:
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
properly!
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
> importing
of[vbcol=seagreen]
> backup
file[vbcol=seagreen]
to[vbcol=seagreen]
device[vbcol=seagreen]
> NOUNLOAD
job[vbcol=seagreen]
file[vbcol=seagreen]
> 42000]
last
> SQL
> device
> it
> database
> out
>
|||Hi Patrick,
you should surely test if using named pipes solves the problem or not. We
had customers indicating that this solved their problem even after SP1a -
although we assume that the problem was related to their network
architecture as we haven't been able to repro this inhouse.
Using an ALIAS allows to do this easily without affecting any other
applications.
From my understanding backup should work with all protocols - so this
sounds as if there are indeed problems with your network architecture. You
should follow up on this in an SQL related newsgroup.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
http://www.gotdotnet.com/community/u...emen t+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks stefan,
> Can I just check if the FAQ in regards to "General Network Error" when
using
> server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
> comes with SP1A) or not? If not, could you suggest any other remedies? I
> have tried all the remedies listed in the FAQ at
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> and still we are getting quite frequent "General Network Error" when
> performing export on a repository (whose SQL DB is 1GB ish and whose SDO
> file is 80MB). e.g. timeouts/maxium request size are much larger than
that
> required already!!
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> allow
> rights.
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
Named
> properly!
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
> of
databases[vbcol=seagreen]
> file
Pipes[vbcol=seagreen]
> to
as[vbcol=seagreen]
follows[vbcol=seagreen]
> device
> job
> file
> last
[Microsoft][ODBC[vbcol=seagreen]
DB,[vbcol=seagreen]
Enterprise[vbcol=seagreen]
errors[vbcol=seagreen]
me[vbcol=seagreen]
use
>
|||Actually:
1) Tests reveal that we cannot consistently prove whether Server side SDO
export/import would work with TCP/IP or Named-pipes
2) Tests reveal that Flat file backups
2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
Network utility is set to use Named Pipes.
2.2) For other databases (including a 3GB database, as opposed to a mere 1GB
MCMS SQL DB) all works with named pipes
3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
should be using (I believe) the internal kernel, which is meant to be super
fast?
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> Hi Patrick,
> you should surely test if using named pipes solves the problem or not. We
> had customers indicating that this solved their problem even after SP1a -
> although we assume that the problem was related to their network
> architecture as we haven't been able to repro this inhouse.
> Using an ALIAS allows to do this easily without affecting any other
> applications.
> From my understanding backup should work with all protocols - so this
> sounds as if there are indeed problems with your network architecture. You
> should follow up on this in an SQL related newsgroup.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
> MCMS Sample Code:
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> using
I
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> that
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
doing
> Named
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
use[vbcol=seagreen]
use[vbcol=seagreen]
settings.[vbcol=seagreen]
> databases
flat[vbcol=seagreen]
> Pipes
function[vbcol=seagreen]
> as
> follows
command[vbcol=seagreen]
the[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
The
> [Microsoft][ODBC
> DB,
> Enterprise
> errors
> me
> use
>
|||Hi Patrick,
Just wondering if you found a solution to your backup problem. We have a
similiar issue and are banging our head trying to figure out what the problem
is.
"Patrick" wrote:

> Actually:
> 1) Tests reveal that we cannot consistently prove whether Server side SDO
> export/import would work with TCP/IP or Named-pipes
> 2) Tests reveal that Flat file backups
> 2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
> Network utility is set to use Named Pipes.
> 2.2) For other databases (including a 3GB database, as opposed to a mere 1GB
> MCMS SQL DB) all works with named pipes
> 3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
> should be using (I believe) the internal kernel, which is meant to be super
> fast?
>
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> rights.
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> http://www.gotdotnet.com/community/u...emen t+Server
> I
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> http://www.gotdotnet.com/community/u...emen t+Server
> doing
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> use
> use
> settings.
> flat
> function
> command
> the
> [SQLSTATE
> The
>
>

Conflicting SQL Client Settings

Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
backups), but Microsoft Content Management Server 2002 SP1A likes Named
Pipes for MCMS Server Side Site Deployment Object export to work properly!
I am faced with a big dilemma!
See
MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...>
MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
I have been experiencing frequest "General network Error"s, when I use
Server side SDO to export the CMS repository (as a backup, before importing
a SDO file from another test server). This article recommends the use of
Named Pipes in preference over TCP/IP for SQL Client Network settings.
However, there has been some recent failures in SQL Server Flat file backup
(SQL SErver maintenance plan to do full backup all application databases
with verify). Invesigation reveal (quite consistently) that the flat file
backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
use. As soon as SQL Client is changed to use TCP/IP, backup function as
expectd. The error that are logged when Named pipes are used as follows
*1) The error logged in the error log file is :
2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408
212125.BAK' failed to open.
Operating system error = 32(error not found).
2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
BACKUP DATABASE [YJBLive] TO DISK =
N'd:\MSSQL\BACKUP\DBLive\DBLive_db_20040
8212125.BAK' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
*2) If I change the job to just do the backup without verify, then the job
completes, but
*2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
which only succeeded in backing up without verifying is 1xxMB large
*3) The Job History indicates
Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE 42
000]
(Error 22029). The step failed.
The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
step to run was step 1 (Step 1).
*4) The backup file appear corrupted:
*4.1) If I look at the Maintenance Plan history, I found:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408
231407.BAK'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
*4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB, it
doesn't work. After the popup to say restore has completed, Enterprise
Manager shows the database as grey with "(Loading)" appended to the database
name (even after 5mins)
*5) The user group Everyone has full permission on d:\MSSQL\BACKUP
*6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
So, I am really stuck here, could some SQL Server / MCMS expert help me out
here! If I use Named Pipes, SQL Backup is not going to work. If I use
TCP/IP, then my MCMS export/import would not work most of the time!Hi Patrick,
1) MCMS likes all protocols if SP1 or SP1a are installed
2) creating an ALIAS in SQL tools for the different application would allow
to configure the protocols for all apps individually.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...>
MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
ntent+Management+Server" target="_blank">http://www.gotdotnet.com/community/...nagement+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossne...2/07/41859.aspx
--
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
> backups), but Microsoft Content Management Server 2002 SP1A likes Named
> Pipes for MCMS Server Side Site Deployment Object export to work properly!
> I am faced with a big dilemma!
> See
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> I have been experiencing frequest "General network Error"s, when I use
> Server side SDO to export the CMS repository (as a backup, before
importing
> a SDO file from another test server). This article recommends the use of
> Named Pipes in preference over TCP/IP for SQL Client Network settings.
> However, there has been some recent failures in SQL Server Flat file
backup
> (SQL SErver maintenance plan to do full backup all application databases
> with verify). Invesigation reveal (quite consistently) that the flat file
> backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
> use. As soon as SQL Client is changed to use TCP/IP, backup function as
> expectd. The error that are logged when Named pipes are used as follows
> *1) The error logged in the error log file is :
> 2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408
212125.BAK' failed to open.
> Operating system error = 32(error not found).
> 2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
> BACKUP DATABASE [YJBLive] TO DISK =
> N'd:\MSSQL\BACKUP\DBLive\DBLive_db_20040
8212125.BAK' WITH INIT ,
NOUNLOAD
> , NOSKIP , STATS = 10, NOFORMAT
>
> *2) If I change the job to just do the backup without verify, then the job
> completes, but
> *2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
> which only succeeded in backing up without verifying is 1xxMB large
> *3) The Job History indicates
> Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
> step to run was step 1 (Step 1).
> *4) The backup file appear corrupted:
> *4.1) If I look at the Maintenance Plan history, I found:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408
231407.BAK'. Device error or
device
> off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> *4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB,
it
> doesn't work. After the popup to say restore has completed, Enterprise
> Manager shows the database as grey with "(Loading)" appended to the
database
> name (even after 5mins)
> *5) The user group Everyone has full permission on d:\MSSQL\BACKUP
> *6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
> So, I am really stuck here, could some SQL Server / MCMS expert help me
out
> here! If I use Named Pipes, SQL Backup is not going to work. If I use
> TCP/IP, then my MCMS export/import would not work most of the time!
>|||Thanks stefan,
Can I just check if the FAQ in regards to "General Network Error" when using
server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
comes with SP1A) or not? If not, could you suggest any other remedies? I
have tried all the remedies listed in the FAQ at
MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...>
MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
and still we are getting quite frequent "General Network Error" when
performing export on a repository (whose SQL DB is 1GB ish and whose SDO
file is 80MB). e.g. timeouts/maxium request size are much larger than that
required already!!
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> Hi Patrick,
> 1) MCMS likes all protocols if SP1 or SP1a are installed
> 2) creating an ALIAS in SQL tools for the different application would
allow
> to configure the protocols for all apps individually.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
l](complete)+FAQ.htm
> MCMS Blog: [url]http://blogs.msdn.com/stefan_gossner/category/4983.aspx" target="_blank">http://download.microsoft.com/downl...egory/4983.aspx
> MCMS Sample Code:
>
+Server" target="_blank">http://www.gotdotnet.com/community/...
+Server
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossne...2/07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
properly![vbcol=seagreen]
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> importing
of[vbcol=seagreen]
> backup
file[vbcol=seagreen]
to[vbcol=seagreen]
device[vbcol=seagreen]
> NOUNLOAD
job[vbcol=seagreen]
file[vbcol=seagreen]
> 42000]
last[vbcol=seagreen]
> SQL
> device
> it
> database
> out
>|||Hi Patrick,
you should surely test if using named pipes solves the problem or not. We
had customers indicating that this solved their problem even after SP1a -
although we assume that the problem was related to their network
architecture as we haven't been able to repro this inhouse.
Using an ALIAS allows to do this easily without affecting any other
applications.
From my understanding backup should work with all protocols - so this
sounds as if there are indeed problems with your network architecture. You
should follow up on this in an SQL related newsgroup.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...>
MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
ntent+Management+Server" target="_blank">http://www.gotdotnet.com/community/...nagement+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossne...2/07/41859.aspx
--
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks stefan,
> Can I just check if the FAQ in regards to "General Network Error" when
using
> server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
> comes with SP1A) or not? If not, could you suggest any other remedies? I
> have tried all the remedies listed in the FAQ at
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> and still we are getting quite frequent "General Network Error" when
> performing export on a repository (whose SQL DB is 1GB ish and whose SDO
> file is 80MB). e.g. timeouts/maxium request size are much larger than
that
> required already!!
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> allow
> rights.
>
l](complete)+FAQ.htm
>
[url]http://www.gotdotnet.com/community/usersamples/Default.aspx?ProductDropDownList=Content+Management
+Server" target="_blank">http://download.microsoft.com/downl...
+Server
Named[vbcol=seagreen]
> properly!
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> of
databases[vbcol=seagreen]
> file
Pipes[vbcol=seagreen]
> to
as[vbcol=seagreen]
follows[vbcol=seagreen]
> device
> job
> file
> last
[Microsoft][ODBC[vbcol=seagreen]
DB,[vbcol=seagreen]
Enterprise[vbcol=seagreen]
errors[vbcol=seagreen]
me[vbcol=seagreen]
use[vbcol=seagreen]
>|||Actually:
1) Tests reveal that we cannot consistently prove whether Server side SDO
export/import would work with TCP/IP or Named-pipes
2) Tests reveal that Flat file backups
2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
Network utility is set to use Named Pipes.
2.2) For other databases (including a 3GB database, as opposed to a mere 1GB
MCMS SQL DB) all works with named pipes
3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
should be using (I believe) the internal kernel, which is meant to be super
fast?
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> Hi Patrick,
> you should surely test if using named pipes solves the problem or not. We
> had customers indicating that this solved their problem even after SP1a -
> although we assume that the problem was related to their network
> architecture as we haven't been able to repro this inhouse.
> Using an ALIAS allows to do this easily without affecting any other
> applications.
> From my understanding backup should work with all protocols - so this
> sounds as if there are indeed problems with your network architecture. You
> should follow up on this in an SQL related newsgroup.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
l](complete)+FAQ.htm
> MCMS Blog: [url]http://blogs.msdn.com/stefan_gossner/category/4983.aspx" target="_blank">http://download.microsoft.com/downl...egory/4983.aspx
> MCMS Sample Code:
>
+Server" target="_blank">http://www.gotdotnet.com/community/...
+Server
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossne...2/07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> using
I[vbcol=seagreen]
>
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> that
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm
>
[url]http://www.gotdotnet.com/community/usersamples/Default.aspx?ProductDropDownList=Content+Management
+Server" target="_blank">http://download.microsoft.com/downl...
+Server
doing[vbcol=seagreen]
> Named
>
[url]http://download.microsoft.com/download/4/2/5/4250f79a-c3a1-4003-9272-2404e92bb76a/MCMS+2002+-+[/ur
l](complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
use[vbcol=seagreen]
use[vbcol=seagreen]
settings.[vbcol=seagreen]
> databases
flat[vbcol=seagreen]
> Pipes
function[vbcol=seagreen]
> as
> follows
command[vbcol=seagreen]
the[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
The[vbcol=seagreen]
> [Microsoft][ODBC
> DB,
> Enterprise
> errors
> me
> use
>|||Hi Patrick,
Just wondering if you found a solution to your backup problem. We have a
similiar issue and are banging our head trying to figure out what the proble
m
is.
"Patrick" wrote:

> Actually:
> 1) Tests reveal that we cannot consistently prove whether Server side SDO
> export/import would work with TCP/IP or Named-pipes
> 2) Tests reveal that Flat file backups
> 2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
> Network utility is set to use Named Pipes.
> 2.2) For other databases (including a 3GB database, as opposed to a mere 1
GB
> MCMS SQL DB) all works with named pipes
> 3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
> should be using (I believe) the internal kernel, which is meant to be supe
r
> fast?
>
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> rights.
> a/MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...
a/MCMS+2002+-+(complete)+FAQ.htm
> Content+Management+Server" target="_blank">http://www.gotdotnet.com/community/...nagement+Server
> I
> a/MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...
a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> a/MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...
a/MCMS+2002+-+(complete)+FAQ.htm
> Content+Management+Server" target="_blank">http://www.gotdotnet.com/community/...nagement+Server
> doing
> a/MCMS+2002+-+" target="_blank">http://download.microsoft.com/downl...
a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> use
> use
> settings.
> flat
> function
> command
> the
> [SQLSTATE
> The
>
>

conflicting object names in sql server 2000

I can't seem to solve this problem. I hope someone can help. I have a table
which is owned by dbo called xxx. I also have a user define function also
called xxx but owned by a different user. Everything works fine on the local
database. From query analyzer if I type in select * from xxx everything
works fine on the local database.
Here's the problem. When I use dts to move this over to another server, and
then try select * from xxx it returns the error "Server: Msg 208, Level 16,
State 3, Line 1
Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
Unfortunately because of the situation I can't go back in and change the
user function name to something else, or change all the code to prefix the
table with dbo.xxx.
The only clue I've come up is that on the local machine when I do an sp_help
it shows "xxx dbo user table 2004-02-16 19:14:17.280"
but when I do an sp_help on the other server it shows "xxx michael table
function 2005-01-08 13:21:57.500"
that seems to explain my problem, but my question is how/what can I update
so that the table gets the "higher priority" so when I do the select it looks
at the table and not the function when just using xxx and not dbo.xxx or
michael.xxx?
Hope that makes sense. I'm in a real bind with this and would appreciate
any help. Thanks.
JohnHi
This is a bit confusing. Are you trying to access the table or the function?
What user are YOU when you try to access the object?
Here are a couple of points that may help:
if you say
SELECT * from xxxx
you are selecting from a table. The only question is, which table? SQL
Server will first try to select from a table xxx that YOU own, if there is
none, it will see if there is one owned by dbo.
If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
dbo, or anybody besides Michael, wants to access the table owned by
Michael, she must specify the owner:
SELECT * from michael.xxx
If you are selecting from a table valued function, you MUST specify an
owner, PLUS you must specify a parameter list, even if there are no
parameters.
So,
SELECT * from xxx()
is incorrect syntax because you didn't specify the owner
SELECT * from dbo.xxx()
will access the xxx FUNCTION owned by dbo
and
SELECT * from michael.xxx()
will access the xxx FUNCTION owned by michael
I hope this helps. If not, please clarify
What object you are trying to access, and if it's a function, what are the
parameters
Who owns that object
What user you are
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>I can't seem to solve this problem. I hope someone can help. I have a
>table
> which is owned by dbo called xxx. I also have a user define function also
> called xxx but owned by a different user. Everything works fine on the
> local
> database. From query analyzer if I type in select * from xxx everything
> works fine on the local database.
> Here's the problem. When I use dts to move this over to another server,
> and
> then try select * from xxx it returns the error "Server: Msg 208, Level
> 16,
> State 3, Line 1
> Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> Unfortunately because of the situation I can't go back in and change the
> user function name to something else, or change all the code to prefix the
> table with dbo.xxx.
> The only clue I've come up is that on the local machine when I do an
> sp_help
> it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> but when I do an sp_help on the other server it shows "xxx michael table
> function 2005-01-08 13:21:57.500"
> that seems to explain my problem, but my question is how/what can I update
> so that the table gets the "higher priority" so when I do the select it
> looks
> at the table and not the function when just using xxx and not dbo.xxx or
> michael.xxx?
> Hope that makes sense. I'm in a real bind with this and would appreciate
> any help. Thanks.
> John
>
>|||Hi,
Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.
the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.
Thanks again.
"Kalen Delaney" wrote:
> Hi
> This is a bit confusing. Are you trying to access the table or the function?
> What user are YOU when you try to access the object?
> Here are a couple of points that may help:
> if you say
> SELECT * from xxxx
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
> SELECT * from michael.xxx
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
> and
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
> I hope this helps. If not, please clarify
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
> >I can't seem to solve this problem. I hope someone can help. I have a
> >table
> > which is owned by dbo called xxx. I also have a user define function also
> > called xxx but owned by a different user. Everything works fine on the
> > local
> > database. From query analyzer if I type in select * from xxx everything
> > works fine on the local database.
> >
> > Here's the problem. When I use dts to move this over to another server,
> > and
> > then try select * from xxx it returns the error "Server: Msg 208, Level
> > 16,
> > State 3, Line 1
> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> >
> > Unfortunately because of the situation I can't go back in and change the
> > user function name to something else, or change all the code to prefix the
> > table with dbo.xxx.
> >
> > The only clue I've come up is that on the local machine when I do an
> > sp_help
> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> >
> > but when I do an sp_help on the other server it shows "xxx michael table
> > function 2005-01-08 13:21:57.500"
> >
> > that seems to explain my problem, but my question is how/what can I update
> > so that the table gets the "higher priority" so when I do the select it
> > looks
> > at the table and not the function when just using xxx and not dbo.xxx or
> > michael.xxx?
> >
> > Hope that makes sense. I'm in a real bind with this and would appreciate
> > any help. Thanks.
> >
> > John
> >
> >
> >
>
>|||A login name of michael in the connection string does not mean the user name
is also michael. A login name maps to a user name when the login is given
access to a particular database. A login michael could be a user named
michael in one database, a user named clerk in another, and a user named
user1 in a third database. If michael is the owner of a database, his user
name will be dbo in that database.
My guess is that the login michael is dbo on one database and not dbo on
another.
How exactly are you 'looking' at the function michael.xxx and how do you
know you are looking at the results of the function and not the table xxx.
Can you post the create table statements, the create function statement, and
the exact commands you are using for access. Also, when you are in a
database, you can execute SELECT USER_NAME() to find out what your user name
is in that database.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Oops, I did make one mistake. You do not have to specify an owner name when
selecting from a table valued function, but you DO need to include the param
list, even if there are no params.
When you say SELECT * from xxx you can only be selecting from a table or a
view, NOT from a function. The owner of the object will depend on the user
name your login name is mapped to.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.
S. L.
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Thank you. It appears in the database that it works in the user is the dbo,
and in the other database the user isn't. I believe that will solve my
issue.
"Kalen Delaney" wrote:
> A login name of michael in the connection string does not mean the user name
> is also michael. A login name maps to a user name when the login is given
> access to a particular database. A login michael could be a user named
> michael in one database, a user named clerk in another, and a user named
> user1 in a third database. If michael is the owner of a database, his user
> name will be dbo in that database.
> My guess is that the login michael is dbo on one database and not dbo on
> another.
> How exactly are you 'looking' at the function michael.xxx and how do you
> know you are looking at the results of the function and not the table xxx.
> Can you post the create table statements, the create function statement, and
> the exact commands you are using for access. Also, when you are in a
> database, you can execute SELECT USER_NAME() to find out what your user name
> is in that database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> > Hi,
> >
> > Yes, this is very confusing, unfortunately this is what I have to work
> > with. Thanks for responding though.
> >
> > the connection string specifies "michael" as the user when connecting to
> > the database. On the local database server when it does the select * from
> > xxx it is expecting to see the table which is owned by dbo, and it does
> > produce these results. When it looks at the function, it does it with
> > michael.xxx Note that michael is the owner of the database. I have
> > attempted to detach the database and reattach it on another server and it
> > works like that as well. However the server I need to get these tables
> > and
> > functions to I do not have access to reattach the database, only to move
> > the
> > database over via dts. The only clue I can find is the difference in the
> > sp_help results which I provided earlier.
> >
> > Thanks again.
> >
> >
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi
> >>
> >> This is a bit confusing. Are you trying to access the table or the
> >> function?
> >> What user are YOU when you try to access the object?
> >>
> >> Here are a couple of points that may help:
> >>
> >> if you say
> >>
> >> SELECT * from xxxx
> >>
> >> you are selecting from a table. The only question is, which table? SQL
> >> Server will first try to select from a table xxx that YOU own, if there
> >> is
> >> none, it will see if there is one owned by dbo.
> >> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> >> dbo, or anybody besides Michael, wants to access the table owned by
> >> Michael, she must specify the owner:
> >>
> >> SELECT * from michael.xxx
> >>
> >> If you are selecting from a table valued function, you MUST specify an
> >> owner, PLUS you must specify a parameter list, even if there are no
> >> parameters.
> >>
> >> So,
> >> SELECT * from xxx()
> >> is incorrect syntax because you didn't specify the owner
> >>
> >> SELECT * from dbo.xxx()
> >> will access the xxx FUNCTION owned by dbo
> >>
> >> and
> >>
> >> SELECT * from michael.xxx()
> >> will access the xxx FUNCTION owned by michael
> >>
> >>
> >> I hope this helps. If not, please clarify
> >>
> >> What object you are trying to access, and if it's a function, what are
> >> the
> >> parameters
> >> Who owns that object
> >> What user you are
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> >> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
> >> >I can't seem to solve this problem. I hope someone can help. I have a
> >> >table
> >> > which is owned by dbo called xxx. I also have a user define function
> >> > also
> >> > called xxx but owned by a different user. Everything works fine on the
> >> > local
> >> > database. From query analyzer if I type in select * from xxx
> >> > everything
> >> > works fine on the local database.
> >> >
> >> > Here's the problem. When I use dts to move this over to another
> >> > server,
> >> > and
> >> > then try select * from xxx it returns the error "Server: Msg 208, Level
> >> > 16,
> >> > State 3, Line 1
> >> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
> >> > fine.
> >> >
> >> > Unfortunately because of the situation I can't go back in and change
> >> > the
> >> > user function name to something else, or change all the code to prefix
> >> > the
> >> > table with dbo.xxx.
> >> >
> >> > The only clue I've come up is that on the local machine when I do an
> >> > sp_help
> >> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> >> >
> >> > but when I do an sp_help on the other server it shows "xxx michael
> >> > table
> >> > function 2005-01-08 13:21:57.500"
> >> >
> >> > that seems to explain my problem, but my question is how/what can I
> >> > update
> >> > so that the table gets the "higher priority" so when I do the select it
> >> > looks
> >> > at the table and not the function when just using xxx and not dbo.xxx
> >> > or
> >> > michael.xxx?
> >> >
> >> > Hope that makes sense. I'm in a real bind with this and would
> >> > appreciate
> >> > any help. Thanks.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>

conflicting object names in sql server 2000

Conflicting Members

I have a report that includes a set, customer divisions as columns and products as rows. At certain intersections members are filtered by the same attribute (ie customer is filtered by product flag as is the product). Some of these have conflicting attribute values (ie product flag is true for the customer but false for the product). In these cases the customer setting is taking precedence. However I need the member setting for the product to override the customer. Is there a way of achieving this?

From your description it is not clear to me what your issue is. Axes are resolved independently, so there could be no conflicts in filtering customers and products. And if you refer to cell values, then what does filtering means ? It would be best if you will provide the exact MDX you are using and results you want to get, then this forum will be able to help you with MDX modifications.|||

In response to your question I am referring to cell values. My MDX query is basically SELECT [Customer Set] on Columns, [Product Set] on rows. I have members in my customer set and my product set which are filtered by the same attribute (Product Flag):

Product Member = ([Product - Own Keg Total] , [Product].[Product Flag].[TRUE])

Customer Member = ([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Customer].[Product Flag].[FALSE])

The values results returned indicate that the attribute value for the product flag of the customer member has been used.

Hope this makes it clearer. If not let me know. Thanks.

|||Can you please provide a definition of named sets [Customer Set] and [Product Set]. I am still not sure what you meant by Product Member and Customer Member in your response, but one thing contradicted what you said about same attribute (Product Flag) being used - in your tuple expression once you used attribute in the Product dimension and once in Customer dimension, so it cannot be the same attribute.|||

Sorry to add to the confusion. The customer member should have read:

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

Ignoring sets, if I run the query with just the product member on the row and the customer member on the column I get the same result:

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True])

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||

Thanks - it is clear now. The mechanism to control order of conflict resolution on calculated members is through SOLVE_ORDER property. I.e. in the following query Customer Test has higher solve order, therefore the Product Flag=True would win

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=1

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=2

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

And in the following query, the SOLVE_ORDER is reversed, therefore Product Flag=False will win.

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=2

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=1

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||Is there a way of configuring the query so that where there is a conflict neither member wins and nothing is returned?

Saturday, 25 February 2012

Conflicting SQL Version Info

Conflicting SQL Client Settings

Conflicting SQL Client Settings

conflicting object names in sql server 2000

conflicting object names in sql server 2000

conflicting object names in sql server 2000

Conflicting Members

SQL Configure

Blog Archive

About Me