microsoft SQLServer CE

hi all..
just wondering that:
microsoft SQLServer = microsoft SQLServer CE?
how to use the microsoft SQLServer CE?
isiz the microsoft SQLServer CE can be used on the
microsoft SQLServer?
i really doubt abt this..hope someone can help me!!
thanks...CE is for embedded applications (for devices), and will have different features etc...

Microsoft SQL-DMO(ODBC SQLState:42000)

I am getting the following error message in SQL 2K when I try to give
database access to a login Id "LogInId1".
****************************************
*************
Microsoft SQL-DMO(ODBC SQLState:42000)
Error 15023: User or role 'LogInId1' already exists in the current database.
****************************************
************************************
*
The "public" role in the above "current database" has a user Id "LogInId1".
This is the one it is generating the above error message. I can not remove
the above user ID from that role (public).
What I did to generate the above error message?
I restored one user database on SQL 2K (running under windows 2003) from the
backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
database from which back was ran.
How to add database access permissions to the login Id ""LogInId1"?
Thank you,
SmithYou can try:
exec sp_changedbowner 'LogInId1'
"John Smith" wrote:

> I am getting the following error message in SQL 2K when I try to give
> database access to a login Id "LogInId1".
>
> ****************************************
*************
> Microsoft SQL-DMO(ODBC SQLState:42000)
> Error 15023: User or role 'LogInId1' already exists in the current databas
e.
> ****************************************
**********************************
**
> *
>
> The "public" role in the above "current database" has a user Id "LogInId1"
.
> This is the one it is generating the above error message. I can not remove
> the above user ID from that role (public).
>
> What I did to generate the above error message?
> I restored one user database on SQL 2K (running under windows 2003) from t
he
> backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
> database from which back was ran.
>
> How to add database access permissions to the login Id ""LogInId1"?
> Thank you,
> Smith
>
>
>|||Take a look at sp_resolve_logins in BOL, etc.
The internal (to the database) user tables have entries that are either not
in the Master SYSLOGINS table or have different pointers to that table. You
need to either repoint(resolve) or remove those logins.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Fahim" wrote:
[vbcol=seagreen]
> You can try:
> exec sp_changedbowner 'LogInId1'
>
> "John Smith" wrote:
>

Microsoft SQL-DMO(ODBC SQLState:42000)

I am getting the following error message in SQL 2K when I try to give
database access to a login Id "LogInId1".
*****************************************************
Microsoft SQL-DMO(ODBC SQLState:42000)
Error 15023: User or role 'LogInId1' already exists in the current database.
****************************************************************************
*
The "public" role in the above "current database" has a user Id "LogInId1".
This is the one it is generating the above error message. I can not remove
the above user ID from that role (public).
What I did to generate the above error message?
I restored one user database on SQL 2K (running under windows 2003) from the
backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
database from which back was ran.
How to add database access permissions to the login Id ""LogInId1"?
Thank you,
SmithYou can try:
exec sp_changedbowner 'LogInId1'
"John Smith" wrote:
> I am getting the following error message in SQL 2K when I try to give
> database access to a login Id "LogInId1".
>
> *****************************************************
> Microsoft SQL-DMO(ODBC SQLState:42000)
> Error 15023: User or role 'LogInId1' already exists in the current database.
> ****************************************************************************
> *
>
> The "public" role in the above "current database" has a user Id "LogInId1".
> This is the one it is generating the above error message. I can not remove
> the above user ID from that role (public).
>
> What I did to generate the above error message?
> I restored one user database on SQL 2K (running under windows 2003) from the
> backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
> database from which back was ran.
>
> How to add database access permissions to the login Id ""LogInId1"?
> Thank you,
> Smith
>
>
>|||Take a look at sp_resolve_logins in BOL, etc.
The internal (to the database) user tables have entries that are either not
in the Master SYSLOGINS table or have different pointers to that table. You
need to either repoint(resolve) or remove those logins.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Fahim" wrote:
> You can try:
> exec sp_changedbowner 'LogInId1'
>
> "John Smith" wrote:
> > I am getting the following error message in SQL 2K when I try to give
> > database access to a login Id "LogInId1".
> >
> >
> >
> > *****************************************************
> >
> > Microsoft SQL-DMO(ODBC SQLState:42000)
> >
> > Error 15023: User or role 'LogInId1' already exists in the current database.
> >
> > ****************************************************************************
> > *
> >
> >
> >
> > The "public" role in the above "current database" has a user Id "LogInId1".
> > This is the one it is generating the above error message. I can not remove
> > the above user ID from that role (public).
> >
> >
> >
> > What I did to generate the above error message?
> >
> > I restored one user database on SQL 2K (running under windows 2003) from the
> > backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
> > database from which back was ran.
> >
> >
> >
> > How to add database access permissions to the login Id ""LogInId1"?
> >
> > Thank you,
> >
> > Smith
> >
> >
> >
> >
> >

Microsoft SQL-DMO(ODBC SQLState:42000)

I am getting the following error message in SQL 2K when I try to give
database access to a login Id "LogInId1".
************************************************** ***
Microsoft SQL-DMO(ODBC SQLState:42000)
Error 15023: User or role 'LogInId1' already exists in the current database.
************************************************** **************************
*
The "public" role in the above "current database" has a user Id "LogInId1".
This is the one it is generating the above error message. I can not remove
the above user ID from that role (public).
What I did to generate the above error message?
I restored one user database on SQL 2K (running under windows 2003) from the
backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
database from which back was ran.
How to add database access permissions to the login Id ""LogInId1"?
Thank you,
Smith
You can try:
exec sp_changedbowner 'LogInId1'
"John Smith" wrote:

> I am getting the following error message in SQL 2K when I try to give
> database access to a login Id "LogInId1".
>
> ************************************************** ***
> Microsoft SQL-DMO(ODBC SQLState:42000)
> Error 15023: User or role 'LogInId1' already exists in the current database.
> ************************************************** **************************
> *
>
> The "public" role in the above "current database" has a user Id "LogInId1".
> This is the one it is generating the above error message. I can not remove
> the above user ID from that role (public).
>
> What I did to generate the above error message?
> I restored one user database on SQL 2K (running under windows 2003) from the
> backup of SQL2K running under Win2K. The user Id "LogInId1" existed in the
> database from which back was ran.
>
> How to add database access permissions to the login Id ""LogInId1"?
> Thank you,
> Smith
>
>
>
|||Take a look at sp_resolve_logins in BOL, etc.
The internal (to the database) user tables have entries that are either not
in the Master SYSLOGINS table or have different pointers to that table. You
need to either repoint(resolve) or remove those logins.
Joseph R.P. Maloney, CSP,CCP,CDP
"Fahim" wrote:
[vbcol=seagreen]
> You can try:
> exec sp_changedbowner 'LogInId1'
>
> "John Smith" wrote:

Microsoft SQL-DMO [SQL-DMO]Out of memory error

Hi,
We have a stored procedure that we call very frequently
that polls servers to see if they are available. This is
used from various other stored procedures that may then
use an available SQL Server or try another one.
We have implemented the stored procedure such that it uses
the SP_OA routines to perform a SQL-DMO connect. This
works very well and allows us to catch the 'server
unavailable' error in stored procedure code and branch.
Unfortunataly, if the SQL Server that issues the connect
has not been restarted for a few says we get the following
message back from the connect method (from
sp_OAGetErrorInfo):
0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
memory.
I have included the code fragment below we are using. Has
anyone any thoughts, please, on where our memory leak is
happening. All our SQL Servers are SP2 currently.
Many thanks, Ken
---
CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
@.LoginTimeout INT = 10
AS
SET NOCOUNT ON
DECLARE @.iRet int
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
-- @.iRet - return value of procedures
-- @.object - object reference
-- @.hr - handle
-- @.src - source of error message
-- @.desc - description of error message
-- Set up parameters/variables
SELECT @.iRet = 0, @.hr = 0, @.object = 0
-- create SQLDMO SQLServer object
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT,
@.desc OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
RETURN (2)
END
-- Set the properties
-- Set the LoginTimeout property.
EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
@.LoginTimeout
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (3)
END
-- Set the integrated login property.
EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (4)
END
-- attempt to connect to server
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
@.ServerName
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
SELECT @.iRet = convert(varbinary(4),@.hr)
EXEC @.hr = sp_OADestroy @.object
RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
END) -- 2 = 'SQL Server does not exist or access denied',
1 = misc. error
END
-- Disconnect
EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (5)
END
-- Destroy object
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
RETURN (6)
END
-- success
RETURN (0)
GOIt's difficult to trace memory leaks like this. I can't help with the DMO
specific troubleshooting, but... I would encourage to you to review the fix
list for SP3a. I remember reading about some memory leak issues, don't
remember what they were... but you don't want to waste time on a fix in the
latest SP.
Also... do you have an ability to either a) rollout SP3 (which you really
should anyway for security purposes) or b) at least roll it out in a QA
envrionment where you can test this memory leak against Sp3. That might save
you a lot of time...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ken Eng" <keneng23@.hotmail.com> wrote in message
news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
> Hi,
> We have a stored procedure that we call very frequently
> that polls servers to see if they are available. This is
> used from various other stored procedures that may then
> use an available SQL Server or try another one.
> We have implemented the stored procedure such that it uses
> the SP_OA routines to perform a SQL-DMO connect. This
> works very well and allows us to catch the 'server
> unavailable' error in stored procedure code and branch.
> Unfortunataly, if the SQL Server that issues the connect
> has not been restarted for a few says we get the following
> message back from the connect method (from
> sp_OAGetErrorInfo):
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
> memory.
> I have included the code fragment below we are using. Has
> anyone any thoughts, please, on where our memory leak is
> happening. All our SQL Servers are SP2 currently.
>
> Many thanks, Ken
> ---
> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
> @.LoginTimeout INT = 10
> AS
> SET NOCOUNT ON
>
> DECLARE @.iRet int
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> -- @.iRet - return value of procedures
> -- @.object - object reference
> -- @.hr - handle
> -- @.src - source of error message
> -- @.desc - description of error message
> -- Set up parameters/variables
> SELECT @.iRet = 0, @.hr = 0, @.object = 0
> -- create SQLDMO SQLServer object
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
> @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> RETURN (2)
> END
> -- Set the properties
> -- Set the LoginTimeout property.
> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
> @.LoginTimeout
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (3)
> END
>
> -- Set the integrated login property.
> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (4)
> END
> -- attempt to connect to server
> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
> @.ServerName
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> SELECT @.iRet = convert(varbinary(4),@.hr)
> EXEC @.hr = sp_OADestroy @.object
> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
> END) -- 2 = 'SQL Server does not exist or access denied',
> 1 = misc. error
> END
> -- Disconnect
> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (5)
> END
> -- Destroy object
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> RETURN (6)
> END
> -- success
> RETURN (0)
> GO
>
>|||Thanks Brian,
I will move to SP3 to take this out of the equation.
Cheers
Ken
>--Original Message--
>It's difficult to trace memory leaks like this. I can't
help with the DMO
>specific troubleshooting, but... I would encourage to you
to review the fix
>list for SP3a. I remember reading about some memory leak
issues, don't
>remember what they were... but you don't want to waste
time on a fix in the
>latest SP.
>Also... do you have an ability to either a) rollout SP3
(which you really
>should anyway for security purposes) or b) at least roll
it out in a QA
>envrionment where you can test this memory leak against
Sp3. That might save
>you a lot of time...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Ken Eng" <keneng23@.hotmail.com> wrote in message
>news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
>> Hi,
>> We have a stored procedure that we call very frequently
>> that polls servers to see if they are available. This is
>> used from various other stored procedures that may then
>> use an available SQL Server or try another one.
>> We have implemented the stored procedure such that it
uses
>> the SP_OA routines to perform a SQL-DMO connect. This
>> works very well and allows us to catch the 'server
>> unavailable' error in stored procedure code and branch.
>> Unfortunataly, if the SQL Server that issues the connect
>> has not been restarted for a few says we get the
following
>> message back from the connect method (from
>> sp_OAGetErrorInfo):
>> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
>> memory.
>> I have included the code fragment below we are using.
Has
>> anyone any thoughts, please, on where our memory leak is
>> happening. All our SQL Servers are SP2 currently.
>>
>> Many thanks, Ken
>> ---
>> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
>> @.LoginTimeout INT = 10
>> AS
>> SET NOCOUNT ON
>>
>> DECLARE @.iRet int
>> DECLARE @.object int
>> DECLARE @.hr int
>> DECLARE @.src varchar(255), @.desc varchar(255)
>> -- @.iRet - return value of procedures
>> -- @.object - object reference
>> -- @.hr - handle
>> -- @.src - source of error message
>> -- @.desc - description of error message
>> -- Set up parameters/variables
>> SELECT @.iRet = 0, @.hr = 0, @.object = 0
>> -- create SQLDMO SQLServer object
>> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
>> @.desc OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> RETURN (2)
>> END
>> -- Set the properties
>> -- Set the LoginTimeout property.
>> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
>> @.LoginTimeout
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (3)
>> END
>>
>> -- Set the integrated login property.
>> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (4)
>> END
>> -- attempt to connect to server
>> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
>> @.ServerName
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> SELECT @.iRet = convert(varbinary(4),@.hr)
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
>> END) -- 2 = 'SQL Server does not exist or access
denied',
>> 1 = misc. error
>> END
>> -- Disconnect
>> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (5)
>> END
>> -- Destroy object
>> EXEC @.hr = sp_OADestroy @.object
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> RETURN (6)
>> END
>> -- success
>> RETURN (0)
>> GO
>>
>
>.
>

Microsoft SQL-DMO error 7405 on linked server query

I am attempting to execute a query on a linked oracle server. The link
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
WARNINGS
options
to be set for the connection. This ensures consistent query semantics.
Enable
these options and then reissue your query.
I have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
> WARNINGS
> options
> to be set for the connection. This ensures consistent query semantics.
> Enable
> these options and then reissue your query.
>
> I have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 22022

Hi,
We have recently setup a new cluster server. At the moment
the resources have swapped over so that 1 is running on
node 2 and vice-versa.
However this appears to be causing a problem with the SQL
scheduled tasks. All tasks in the list have (Date and
Time are not available) under 'Next Run Date.' All the
tasks will try and run at their scheduled time however if
we try and manually execute tasks we get the error message
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 22022:
SqlServerAgent Error: Job ... does not exist in the job
cache.
Has anyone seen this error before?
We do not have this problem on our other clustered servers.
Thanks in advance for your help.
Do you have SQL Server Agent starting with a domain account that is valid
on both nodes?
Are there any messages in the SQL Server or SQL Agent logs?
Are there any messages in the event logs?
Do the jobs refer to any files or other objects that only exist on one node?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.