alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Will the next sql version allow referencing an alias in the same select level ?

alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Will the next sql version allow referencing an alias in the same select level ?

alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Will the next sql version allow referencing an alias in the same select level ?

sql

Aggregates containing subqueries...

So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.

Anyways, in my select I have something like this:

sum( case when code in (1,2,3,4) then 0 else 1 end ) as total

which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:

sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total

because apparently you can't use selects or aggregates within an aggregate.

So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.

Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).

Try this

SELECT COUNT(t1.code) AS TOTAL FROM TABLE1 t1 WHERE t1.code NOT IN (SELECT t2.code FROM TABLE2 t2)

|||

Do a LEFT JOIN with the table ExcludedCodes and then use CASE to return 0 or 1 appropriately. Then you can SUM around the CASE.

|||

The left join works perfectly, I'm not sure why I didn't think of that earlier. Just set my case to check if current_code.code_id = exclude_code.code_id then 0 else 1 and the rest of the query didn't have to change at all thanks.

|||

Well sure, use the solution from the guy who has 150 times as many points as me

Aggregate Functions Support?

Does anyone know if the jdbc driver for SQL Server
support aggregate functions such as AVG, SUM, COUNT ?
I can't find any documentation stating it does or
doesn't, but my testing seems to show that it does not.
Thanks
JD
JD wrote:

> Does anyone know if the jdbc driver for SQL Server
> support aggregate functions such as AVG, SUM, COUNT ?
> I can't find any documentation stating it does or
> doesn't, but my testing seems to show that it does not.
> Thanks
> JD
In general, the driver doesn't care about what SQL you
send to the DBMS. What problems are you having using
those functions?
Joe Weinstein at BEA
|||The result set is empty. I can successfully run the query
manually via isql.
On second thought perhaps it's an issue with the version
of java I'm running, JDK 1.2.2, that might not support
the aggregate functions.
JD
>--Original Message--
>
>JD wrote:
>
>In general, the driver doesn't care about what SQL you
>send to the DBMS. What problems are you having using
>those functions?
>Joe Weinstein at BEA
>.
>
|||anonymous@.discussions.microsoft.com wrote:

> The result set is empty. I can successfully run the query
> manually via isql.
> On second thought perhaps it's an issue with the version
> of java I'm running, JDK 1.2.2, that might not support
> the aggregate functions.
Hi. No that's not it either. If the driver doesn't care about your SQL,
the JVM certainly won't. SHow your JDBC code and function definition.
Then show what isql shows when you call it.
Joe Weinstein at BEA
[vbcol=seagreen]
> JD
|||<anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2078b01c45933$f788d340$a401280a@.phx.gbl...
> The result set is empty. I can successfully run the query
> manually via isql.
> On second thought perhaps it's an issue with the version
> of java I'm running, JDK 1.2.2, that might not support
> the aggregate functions.
Nope. I'd guess that it's rather a database connection thing: do you
connect to the same database via JDBC and ISQL? Do you use the same
credentials?
robert
[vbcol=seagreen]
> JD

After upgrade MSDE Sp4 Can't connect it through Enterprise Manager

Hi,
I have upgrade MSDE to SP4, but can't connect it throught enterprise manager
after upgrade.
How can I fix it?
Thanks,
Tiger
hi Tiger (Oracle user? :D ),
Tiger wrote:
> Hi,
> I have upgrade MSDE to SP4, but can't connect it throught enterprise
> manager after upgrade.
actually you shoud have no technical problem (but legal)...
what kind of exception are you reported with when trying connection to MSDE
instance?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||No, I'm not use Oracle
I just use SQL Server Enterprise Manager to connect client PC MSDE, but
after upgrade MSDE to SP4, then fail to connect.
Tiger
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ngm9cF1ecmrU1@.individual.net...
> hi Tiger (Oracle user? :D ),
> Tiger wrote:
> actually you shoud have no technical problem (but legal)...
> what kind of exception are you reported with when trying connection to
> MSDE instance?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tiger,
Tiger wrote:
> No, I'm not use Oracle
>
that was a joke, as Scott / Tiger are the "default" sample credentials for
accessing Oracle

> I just use SQL Server Enterprise Manager to connect client PC MSDE,
> but after upgrade MSDE to SP4, then fail to connect.
what kind of exception are you reported with when trying connection to MSDE
instance?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Mon, 29 Aug 2005 17:01:01 +0200, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

> hi Tiger (Oracle user? :D ),
> Tiger wrote:
> actually you shoud have no technical problem (but legal)...
Legal problem?
I thought a SQL Server Developer Edition license ($39 or free with MSDN)
allows you to use Enterprise Manager to configure your development copies
of MSDE.
Managing production copies is another matter, but Tiger never mentioned
that.
|||hi,
Lucvdv wrote:
> Legal problem?
> I thought a SQL Server Developer Edition license ($39 or free with
> MSDN) allows you to use Enterprise Manager to configure your
> development copies of MSDE.
> Managing production copies is another matter, but Tiger never
> mentioned that.
if you install SQL Server Dev Edition (for dev and test) you already have a
full SQL Server instance and should not require MSDE at all, but I must
confess as well I've both installed on the same dev pc for my own tests/dev
tasks...
as regard the "legal" problem, I always refer to http://tinyurl.com/ac3v2 ,
where Steve presents a good metaphor about using SQL Server Client Tools
against MSDE in production...
then it's all up to you...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi!
I had the same problem - it seems that SP4 changes the server port ... check
this in server network utility.
greeting
Lukas
"Tiger" <tigerwong666@.hotmail.com> schrieb im Newsbeitrag
news:OBwjydHrFHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have upgrade MSDE to SP4, but can't connect it throught enterprise
> manager after upgrade.
> How can I fix it?
> Thanks,
> Tiger
>

After restoring MASTER database, can't start SQLServer Service: Configuration block version 0 i

Now, I checked and verified that my backup version of SQL Server is the same as the version installed on the computer I'm restoring too.

I have SQL Server on a production machine that I backed up and want to test a full restore on a dev machine to make sure it will work when I need it to.

Now that I've run the restore command on my tape backup and go to restart the SQL server service I receive:

Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.

I'm afraid I don't understand why this is happening. If the builds are the same, then shouldn't restoring the MASTER database have worked normally and I'd be able to restart the service now?

Any thoughts or suggestions?

If the service cannot be started, you must rebuild the

system databases, adding information that has been lost by attaching existing tables and recreating

objects.

After this, Restoring the master database when SQL Server is accessible

With master database accessible, you should start SQL Server in single-user mode and then restore your copy of the

master database from your most recent full database backup in the usual way, as described in the

following steps.

#1 - Start SQL Server in single-user mode.

with command:

sqlservr.exe -c –m

#2 - Restore the master database from the most recent backup:

RESTORE DATABASE master

FROM masterbackup

If Master isn′t accessible:

To rebuild the master database, you should run the SQL Server setup program with the following

options:

? The /qn switch to suppress the user interface.

? The REINSTALLMODE = AMUS property to rebuild system databases.

? The REINSTALL = ALL property to set up the server with the previously installed features.

This must be used when specifying the REINSTALLMODE property.

|||

I tried rebuilding using this command:

start /wait d:\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=YourPassword

However, you are saying those were not the proper commands and switches to use during the reinstallation?

(Also I didn't want to specify an SA account and password, I wanted to use windows authentication but didn't see an option for that).

|||

To use Windows authentication, you need use this parameters:

start /wait d:\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1

SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword>

|||

Thanks.

Anyway, I tried rebuilding and that DID work.

But when I restored the master database, I was back where I started.

I KNOW the database installations are the same version as I did SELECT @.@.VERSION on both and they match.

So I don't understand why restoring a tape from one server to the other causes the other server to throw this error when the versions of the server do appear to be the same.

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right? if i'm right, anything i can do to resolve this problem
thanks for any reply
Rico
Problems about orphaned users ?
http://msdn.microsoft.com/library/de...tabse_0ttf.asp
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> glsD:uKfxmfhWFHA.3584@.TK2MSFTNGP14.phx.g bl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon fai
led
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right' if i'm right, anything i can do to resolve this problem
thanks for any reply
RicoProblems about orphaned users ?
http://msdn.microsoft.com/library/d...
tabse_0ttf.asp
http://vyaskn.tripod.com/troublesho...rphan_users.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon f
ailed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon f
ailed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> glsD:uKfxmfhWFHA.3584@.TK2MSFTNGP14.p
hx.gbl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right' if i'm right, anything i can do to resolve this problem
thanks for any reply
RicoProblems about orphaned users ?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_0ttf.asp
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> ¼¶¼g©ó¶l¥ó·s»D:uKfxmfhWFHA.3584@.TK2MSFTNGP14.phx.gbl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>> Dear all,
>> My Lab has one SQL Server which used by ApplicationA, during
>> the installation process of ApplicationA, ApplicationA will
>> add New Logins for one local user group, and add one domain
>> useraccount to be a member of this local user group, i want
>> to simulate disaster rescovery of ApplicationA, so i do the
>> steps listed bellow:
>> (1) backup all related SQL database, inclued Master DB Full Backup
>> (2) Install one new Windows 2003 Server
>> (3) create local user group used by ApplicationA
>> (4) Install SQL Server and SP3a in this new Server, and every
>> installation options is the same as old server
>> (5) Install ApplicationA
>> (6) Restore all Database from backup media
>> (7) make sure no error happend when restore
>> after restore database, domain user account which is a member
>> of that local user group can't logon go SQL Server, when use SQL
>> Query Analyzer, error message appear:
>> can't connect to server SQLServerName:
>> Server:Message18456,Level16,Status1
>> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
>> if i delete SQL logins for that local user group, and then new logins
>> for that local user group, the domain user account can logon
>> successfully,
>> so i think although the name of local user gorup is the same, but
>> the sid of local user gorup is different, because this is the new Windows
>> 2003 Server.
>> Am i right' if i'm right, anything i can do to resolve this problem
>> thanks for any reply
>> Rico
>

After restore db, transactional replication can't delete.

Hi,
Can someone help me with the following situation?
1) I did dump for database test with transactional replication set up on it,
push data from test to test1 db on the same server.
2) Drop the transactional replication of publication for test database.
3) Drop the subscriber db test1.
4) Restore the dump to test db. After that I can't delete the Subscription
at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server Error:
20032.
Could not delete Publication 'test'. Could not update the distribution db
subscription table. The Subscription status could not be chaned.
How can I do right now? Please help. I want drop them even this is test
server.
The whole steps are for the DR test.
Regards!
Chen
did you try sp_removedbreplication on both the publisher and subscriber in
the publication and subscription dbs?
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
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
> Hi,
> Can someone help me with the following situation?
> 1) I did dump for database test with transactional replication set up on
> it,
> push data from test to test1 db on the same server.
> 2) Drop the transactional replication of publication for test database.
> 3) Drop the subscriber db test1.
> 4) Restore the dump to test db. After that I can't delete the Subscription
> at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server
> Error:
> 20032.
> Could not delete Publication 'test'. Could not update the distribution db
> subscription table. The Subscription status could not be chaned.
> How can I do right now? Please help. I want drop them even this is test
> server.
> The whole steps are for the DR test.
> Regards!
> Chen
|||Hi Hilary,
I did following steps:
use master
go
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'test1'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
use distribution
go
exec sp_removedistpublisherdbreplication @.publisher = 'SQMA-AT01'
, @.publisher_db = 'test'
But I still see the test:test
[SQMA-AT01].[test1]
under Local Publications and when I try to delete the same errors occur. How
can I do? Actually this is the SQL Server 2005 with SP1.
Regards!
Chen
"Hilary Cotter" wrote:

> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
|||Hi Hilary,
Problem has been solved after I did sp_removedbreplication on both the
publisher and subscriber.
Thank you so much!
Chen
"Hilary Cotter" wrote:

> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>

After attaching Sql Server 7 database can't make a diagram

I have successfully attached an sql server 7 database, changed the owner to
sa and the compatibility level to 2000 but when I try to create a diagram, I
get the following message. I go to the MS site and it is no help:
TITLE: Microsoft SQL Server Management Studio
--
Invalid column name 'uvalue'.
Invalid column name 'uvalue'.
Could not find stored procedure 'dbo.sp_upgraddiagrams'.
Object is invalid. Extended properties are not permitted on
'dbo.sysdiagrams', or the object does not exist.
Object is invalid. Extended properties are not permitted on
'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
Server, Error: 207)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
--
BUTTONS:
OKHi Ellie
The link talks about case sensitive collations. What collation are you using?
It also mentions Management Studio, but you said SQL 2000!
John
"Ellie" wrote:
> I have successfully attached an sql server 7 database, changed the owner to
> sa and the compatibility level to 2000 but when I try to create a diagram, I
> get the following message. I go to the MS site and it is no help:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Invalid column name 'uvalue'.
> Invalid column name 'uvalue'.
> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> Object is invalid. Extended properties are not permitted on
> 'dbo.sysdiagrams', or the object does not exist.
> Object is invalid. Extended properties are not permitted on
> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> Server, Error: 207)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> --
> BUTTONS:
> OK
>
>|||Hi John,
The collation is:
SQL_Latin1_General_CP1_CI_AS
It was the default. I am using Sql Server 2005 but made the database
compatible with 2000. I'm really not that familiar with the workings of the
server. I have another database that I am able to use the diagrams and it
looks like I have the same settings as this one so I don't know what's
wrong. Thanks Ellie
-- Original Message --
From: "John Bell" <jbellnewsposts@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 23, 2008 3:33 AM
Subject: RE: After attaching Sql Server 7 database can't make a diagram
> Hi Ellie
> The link talks about case sensitive collations. What collation are you
> using?
> It also mentions Management Studio, but you said SQL 2000!
> John
> "Ellie" wrote:
>> I have successfully attached an sql server 7 database, changed the owner
>> to
>> sa and the compatibility level to 2000 but when I try to create a
>> diagram, I
>> get the following message. I go to the MS site and it is no help:
>>
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Invalid column name 'uvalue'.
>> Invalid column name 'uvalue'.
>> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sysdiagrams', or the object does not exist.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
>> Server, Error: 207)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
>> --
>> BUTTONS:
>> OK
>>
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3EF7E80C-B854-4EF9-B1CF-D0B2F345E544@.microsoft.com...
> Hi Ellie
> The link talks about case sensitive collations. What collation are you
> using?
> It also mentions Management Studio, but you said SQL 2000!
> John
> "Ellie" wrote:
>> I have successfully attached an sql server 7 database, changed the owner
>> to
>> sa and the compatibility level to 2000 but when I try to create a
>> diagram, I
>> get the following message. I go to the MS site and it is no help:
>>
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Invalid column name 'uvalue'.
>> Invalid column name 'uvalue'.
>> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sysdiagrams', or the object does not exist.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
>> Server, Error: 207)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
>> --
>> BUTTONS:
>> OK
>>|||Hi Ellie
I can't say I use diagrams much! The easiest option may be to drop them
before detaching and re-creating them when they on the destination system.
Which @.@.version of SQL 2005 are you using?
John
"Ellie" wrote:
> Hi John,
> The collation is:
> SQL_Latin1_General_CP1_CI_AS
> It was the default. I am using Sql Server 2005 but made the database
> compatible with 2000. I'm really not that familiar with the workings of the
> server. I have another database that I am able to use the diagrams and it
> looks like I have the same settings as this one so I don't know what's
> wrong. Thanks Ellie
> -- Original Message --
> From: "John Bell" <jbellnewsposts@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 23, 2008 3:33 AM
> Subject: RE: After attaching Sql Server 7 database can't make a diagram
>
> > Hi Ellie
> >
> > The link talks about case sensitive collations. What collation are you
> > using?
> >
> > It also mentions Management Studio, but you said SQL 2000!
> >
> > John
> >
> > "Ellie" wrote:
> >
> >>
> >> I have successfully attached an sql server 7 database, changed the owner
> >> to
> >> sa and the compatibility level to 2000 but when I try to create a
> >> diagram, I
> >> get the following message. I go to the MS site and it is no help:
> >>
> >>
> >> TITLE: Microsoft SQL Server Management Studio
> >> --
> >>
> >> Invalid column name 'uvalue'.
> >> Invalid column name 'uvalue'.
> >> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sysdiagrams', or the object does not exist.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> >> Server, Error: 207)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> >>
> >> --
> >> BUTTONS:
> >>
> >> OK
> >>
> >>
> >>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3EF7E80C-B854-4EF9-B1CF-D0B2F345E544@.microsoft.com...
> > Hi Ellie
> >
> > The link talks about case sensitive collations. What collation are you
> > using?
> >
> > It also mentions Management Studio, but you said SQL 2000!
> >
> > John
> >
> > "Ellie" wrote:
> >
> >>
> >> I have successfully attached an sql server 7 database, changed the owner
> >> to
> >> sa and the compatibility level to 2000 but when I try to create a
> >> diagram, I
> >> get the following message. I go to the MS site and it is no help:
> >>
> >>
> >> TITLE: Microsoft SQL Server Management Studio
> >> --
> >>
> >> Invalid column name 'uvalue'.
> >> Invalid column name 'uvalue'.
> >> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sysdiagrams', or the object does not exist.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> >> Server, Error: 207)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> >>
> >> --
> >> BUTTONS:
> >>
> >> OK
> >>
> >>
> >>
>
>