Wednesday, March 28, 2012
Problems with Sql2k install on Windows 2K3 server std
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon
> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Problems with Sql2k install on Windows 2K3 server std
s
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
--
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Problems with Sql2k install on Windows 2K3 server std
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
--
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sql
Friday, March 23, 2012
Problems with queries on Win 2003 server
I run it on a windows 2003 server having Sql 2000 SP3A
pack.
I was able to run the following query just fine on
Windows NT with Sql 7 -
SELECT filing_area, File_num, contract_num, * from
eng_file where filing_area+ COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST')
in (select filing_area + COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST') from eng_file
group by filing_area + COALESCE(file_num, 'TEST') +
COALESCE(contract_num, 'TEST') having count(*) > 1 )
But the same query gives me the following errors
when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
2k sp3.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILING_AREA' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILE_NUM' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.FILE_NUM' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.CONTRACT_NUM' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'eng_file.CONTRACT_NUM' is invalid in the select
list because it is not contained in either an aggregate
function or the GROUP BY clause.
What I am missing?
Thank you,
-LiThe behaviour of queries with expressions in the GROUP BY list doesn't
always seem very logical. I don't have 7.0 to verify but the following query
should work on both versions. In your IN comparison, notice that (a1+b1+c1 =a2+b2+c2) is NOT the same comparison as (a1=a2, b1=b2, c1=c2) if these
values are variable-lengths strings. Unless the strings are CHAR or VARCHAR
of equal length then the result of your query may not be what I think you
intended.
SELECT T1.filing_area, T1.file_num, T1.contract_num, *
FROM eng_file AS T1
JOIN
(SELECT filing_area,
COALESCE(file_num, 'TEST'),
COALESCE(contract_num, 'TEST')
FROM eng_file
GROUP BY filing_area,
COALESCE(file_num, 'TEST'),
COALESCE(contract_num, 'TEST')
HAVING COUNT(*)>1)
AS T2(filing_area, file_num, contract_num)
ON T1.filing_area = T2.filing_area
AND COALESCE(T1.file_num, 'TEST') = T2.file_num
AND COALESCE(T1.contract_num, 'TEST') = T2.contract_num
--
David Portas
SQL Server MVP
--|||Your original query works on SQL Server 2005 (9.00.852), so although it
doesn't help you now it does appear that the problem has been fixed. :-)
--
David Portas
SQL Server MVP
--|||Li,
This certainly looks like a bug to me. I believe your query will
succeed if you replace COALESCE by ISNULL. The two functions are not
identical, however. The data type of COALESCE(x,y) accommodates all
the arguments, but the type of ISNULL(x,y) is the type of x. Also,
COALESCE takes multiple arguments and ISNULL must be nested.
I'll pass this on to Microsoft, since I have not seen it reported
before. Thanks!
Steve Kass
Drew University
Li wrote:
>I am running into lot of problems with queries when
>I run it on a windows 2003 server having Sql 2000 SP3A
>pack.
>I was able to run the following query just fine on
>Windows NT with Sql 7 -
>SELECT filing_area, File_num, contract_num, * from
>eng_file where filing_area+ COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST')
>in (select filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') from eng_file
>group by filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>But the same query gives me the following errors
>when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
>2k sp3.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILING_AREA' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>
>What I am missing?
>Thank you,
>-Li
>
>|||Li,
A better workaround (I only checked a simpler query) is to replace
COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y END, which is
equivalent. I think the bug here is a parsing problem that comes up if
a subquery groups on a CASE WHEN NOT ... expression, which is how the
COALESCE must be getting rewritten.
SK
Li wrote:
>I am running into lot of problems with queries when
>I run it on a windows 2003 server having Sql 2000 SP3A
>pack.
>I was able to run the following query just fine on
>Windows NT with Sql 7 -
>SELECT filing_area, File_num, contract_num, * from
>eng_file where filing_area+ COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST')
>in (select filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') from eng_file
>group by filing_area + COALESCE(file_num, 'TEST') +
>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>But the same query gives me the following errors
>when run on Win 2003 with Sql 2000 SP3 or Win 2k with Sql
>2k sp3.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILING_AREA' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.FILE_NUM' is invalid in the select list
>because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>list because it is not contained in either an aggregate
>function or the GROUP BY clause.
>
>What I am missing?
>Thank you,
>-Li
>
>|||Li,
ISNUll worked - thank you.
Please do let me know if you get a chance to report
this to Microsoft and their reaction to it.
Thank you,
-Li
>--Original Message--
>Li,
> This certainly looks like a bug to me. I believe your
query will
>succeed if you replace COALESCE by ISNULL. The two
functions are not
>identical, however. The data type of COALESCE(x,y)
accommodates all
>the arguments, but the type of ISNULL(x,y) is the type
of x. Also,
>COALESCE takes multiple arguments and ISNULL must be
nested.
> I'll pass this on to Microsoft, since I have not seen
it reported
>before. Thanks!
>Steve Kass
>Drew University
>Li wrote:
>>I am running into lot of problems with queries when
>>I run it on a windows 2003 server having Sql 2000 SP3A
>>pack.
>>I was able to run the following query just fine on
>>Windows NT with Sql 7 -
>>SELECT filing_area, File_num, contract_num, * from
>>eng_file where filing_area+ COALESCE(file_num, 'TEST')
+
>>COALESCE(contract_num, 'TEST')
>>in (select filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') from eng_file
>>group by filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>>But the same query gives me the following errors
>>when run on Win 2003 with Sql 2000 SP3 or Win 2k with
Sql
>>2k sp3.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILING_AREA' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>
>>What I am missing?
>>Thank you,
>>-Li
>>
>.
>|||Steve,
I agree the query may not be the best one as it was
written to quickly take care of some data issues we are
facing and came up with this. Since it worked fine on Sql
7.0, I don't find any reason for it not to work with Sql
2K. David mentioned that it works on Sql 2k so the
problem may have been fixed. What am I missing that it
isn't working for me?
Any ideas?
I am going to write to David too.
Thank you very much for yout time and input -
-Li
>--Original Message--
>Li,
> A better workaround (I only checked a simpler query)
is to replace
>COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y
END, which is
>equivalent. I think the bug here is a parsing problem
that comes up if
>a subquery groups on a CASE WHEN NOT ... expression,
which is how the
>COALESCE must be getting rewritten.
>SK
>Li wrote:
>>I am running into lot of problems with queries when
>>I run it on a windows 2003 server having Sql 2000 SP3A
>>pack.
>>I was able to run the following query just fine on
>>Windows NT with Sql 7 -
>>SELECT filing_area, File_num, contract_num, * from
>>eng_file where filing_area+ COALESCE(file_num, 'TEST')
+
>>COALESCE(contract_num, 'TEST')
>>in (select filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') from eng_file
>>group by filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>>But the same query gives me the following errors
>>when run on Win 2003 with Sql 2000 SP3 or Win 2k with
Sql
>>2k sp3.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILING_AREA' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>
>>What I am missing?
>>Thank you,
>>-Li
>>
>.
>|||Steve,
Ignore my comments about why it isn't working for me.
When I went to reply to DAvid's email, I noticed that he
said it is fixed on 2005 and not 2000, that answers my
question.
-Li
>--Original Message--
>Li,
> A better workaround (I only checked a simpler query)
is to replace
>COALESCE(x,y) with CASE WHEN x IS NULL THEN x ELSE y
END, which is
>equivalent. I think the bug here is a parsing problem
that comes up if
>a subquery groups on a CASE WHEN NOT ... expression,
which is how the
>COALESCE must be getting rewritten.
>SK
>Li wrote:
>>I am running into lot of problems with queries when
>>I run it on a windows 2003 server having Sql 2000 SP3A
>>pack.
>>I was able to run the following query just fine on
>>Windows NT with Sql 7 -
>>SELECT filing_area, File_num, contract_num, * from
>>eng_file where filing_area+ COALESCE(file_num, 'TEST')
+
>>COALESCE(contract_num, 'TEST')
>>in (select filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') from eng_file
>>group by filing_area + COALESCE(file_num, 'TEST') +
>>COALESCE(contract_num, 'TEST') having count(*) > 1 )
>>But the same query gives me the following errors
>>when run on Win 2003 with Sql 2000 SP3 or Win 2k with
Sql
>>2k sp3.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILING_AREA' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.FILE_NUM' is invalid in the select
list
>>because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>Server: Msg 8120, Level 16, State 1, Line 1
>>Column 'eng_file.CONTRACT_NUM' is invalid in the select
>>list because it is not contained in either an aggregate
>>function or the GROUP BY clause.
>>
>>What I am missing?
>>Thank you,
>>-Li
>>
>.
>sql
Tuesday, March 20, 2012
problems with merge rep after upgrading from sql server 2000 sp3a to sql server 2005 build 2153
Hi,
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
2)
Command attempted:
{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}
Error messages:
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @.retcode = -1 and thus cannot obtain exclusive lock on resource
EXEC @.retcode = sys.sp_getapplock @.Resource = 'Merge Agent Name',
@.LockMode = 'Exclusive',
@.LockOwner = 'Session',
@.LockTimeout = 0,
@.DbPrincipal = 'MSmerge_PAL_role'
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
can anyone help?
Thanks
anyone?|||Looks to me like the upgrade did not happen correctly on the subscriber machine.
Can you check the upgrade log for any failures?
Also would dropping the subscriber db and recreating the subscription help?
If you create a new database and subscriber, do you still see the same error?
|||Thanks for the reply
however the error with the database role occured at the publisher/distributor and not the subscriber
have went back to check the installation logs for both the publisher/distributor and the subscriber and didn't find any errors
also, both instances did not report any errors during upgrade
any other ideas why this might happen?
thanks
|||We have the same error under similar circumstances. We upgraded a data from an SQL Server 2000 SP4 machine to a new machine of the same name running SQL Server 2005 (9.0.1399). Prior to the upgrade I dropped all subscriptions for this database from the publisher (SQL Server 2005 9.0.) and then copied the MDF and LDF files to the new server and attached them.
Snapshot and Transactional replication run fine, but Merge replication does not run at all. As per the original messenger I have tried to initialize the subscriptions, delete the subscriptions and re-create them, and nothing is working.
Any ideas would be gratefully received.
|||Sorry. The publisher is version 9..3054.|||Im having this problem as well, anybody mange to solve it?problems with merge rep after upgrading from sql server 2000 sp3a to sql server 2005 build 2153
Hi,
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
2)
Command attempted:
{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}
Error messages:
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @.retcode = -1 and thus cannot obtain exclusive lock on resource
EXEC @.retcode = sys.sp_getapplock @.Resource = 'Merge Agent Name',
@.LockMode = 'Exclusive',
@.LockOwner = 'Session',
@.LockTimeout = 0,
@.DbPrincipal = 'MSmerge_PAL_role'
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
can anyone help?
Thanks
anyone?|||Looks to me like the upgrade did not happen correctly on the subscriber machine.
Can you check the upgrade log for any failures?
Also would dropping the subscriber db and recreating the subscription help?
If you create a new database and subscriber, do you still see the same error?
|||Thanks for the reply
however the error with the database role occured at the publisher/distributor and not the subscriber
have went back to check the installation logs for both the publisher/distributor and the subscriber and didn't find any errors
also, both instances did not report any errors during upgrade
any other ideas why this might happen?
thanks
|||We have the same error under similar circumstances. We upgraded a data from an SQL Server 2000 SP4 machine to a new machine of the same name running SQL Server 2005 (9.0.1399). Prior to the upgrade I dropped all subscriptions for this database from the publisher (SQL Server 2005 9.0.) and then copied the MDF and LDF files to the new server and attached them.
Snapshot and Transactional replication run fine, but Merge replication does not run at all. As per the original messenger I have tried to initialize the subscriptions, delete the subscriptions and re-create them, and nothing is working.
Any ideas would be gratefully received.
|||Sorry. The publisher is version 9..3054.problems with merge rep after upgrading from sql server 2000 sp3a to sql server 2005 build 2153
Hi,
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
2)
Command attempted:
{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}
Error messages:
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @.retcode = -1 and thus cannot obtain exclusive lock on resource
EXEC @.retcode = sys.sp_getapplock @.Resource = 'Merge Agent Name',
@.LockMode = 'Exclusive',
@.LockOwner = 'Session',
@.LockTimeout = 0,
@.DbPrincipal = 'MSmerge_PAL_role'
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
can anyone help?
Thanks
anyone?|||Looks to me like the upgrade did not happen correctly on the subscriber machine.
Can you check the upgrade log for any failures?
Also would dropping the subscriber db and recreating the subscription help?
If you create a new database and subscriber, do you still see the same error?
|||Thanks for the reply
however the error with the database role occured at the publisher/distributor and not the subscriber
have went back to check the installation logs for both the publisher/distributor and the subscriber and didn't find any errors
also, both instances did not report any errors during upgrade
any other ideas why this might happen?
thanks
|||We have the same error under similar circumstances. We upgraded a data from an SQL Server 2000 SP4 machine to a new machine of the same name running SQL Server 2005 (9.0.1399). Prior to the upgrade I dropped all subscriptions for this database from the publisher (SQL Server 2005 9.0.) and then copied the MDF and LDF files to the new server and attached them.
Snapshot and Transactional replication run fine, but Merge replication does not run at all. As per the original messenger I have tried to initialize the subscriptions, delete the subscriptions and re-create them, and nothing is working.
Any ideas would be gratefully received.
|||Sorry. The publisher is version 9..3054.|||Im having this problem as well, anybody mange to solve it?Friday, March 9, 2012
Problems with firstname.lastname logins and SQL2000?
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JT
You could use the connection related events in Profiler to see when these
logins are losing connections, and see if there's anything happening in the
domain at that time.
I haven't tried this myself, but if the app is making ODBC connections,
worth looking at the ODBC trace.
I couldn't find anything related to this in the KB.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jeff" <jeff@.nomail.thanks._amc_.com> wrote in message
news:O1NaUJvTEHA.3872@.TK2MSFTNGP10.phx.gbl...
Hi
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JT
|||There has been a problem with some ODBC applications where the "." gets
stripped and everything after it gets truncated during the login process.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Problems with firstname.lastname logins and SQL2000?
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JTYou could use the connection related events in Profiler to see when these
logins are losing connections, and see if there's anything happening in the
domain at that time.
I haven't tried this myself, but if the app is making ODBC connections,
worth looking at the ODBC trace.
I couldn't find anything related to this in the KB.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jeff" <jeff@.nomail.thanks._amc_.com> wrote in message
news:O1NaUJvTEHA.3872@.TK2MSFTNGP10.phx.gbl...
Hi
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JT|||There has been a problem with some ODBC applications where the "." gets
stripped and everything after it gets truncated during the login process.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Problems with firstname.lastname logins and SQL2000?
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JTYou could use the connection related events in Profiler to see when these
logins are losing connections, and see if there's anything happening in the
domain at that time.
I haven't tried this myself, but if the app is making ODBC connections,
worth looking at the ODBC trace.
I couldn't find anything related to this in the KB.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jeff" <jeff@.nomail.thanks._amc_.com> wrote in message
news:O1NaUJvTEHA.3872@.TK2MSFTNGP10.phx.gbl...
Hi
Anyone aware of any problems with SQL 2000 (SP3a) on Windows 2003 Server
when users have a '.' in their e.g mydomain\mary.smith
We are having trouble with an application that runs on an XP client into the
SQL Server loosing the connection. There is nothing in any error log and
the only clue we have is that users who don't have the period in the login
names never have a problem.
TIA
JT|||There has been a problem with some ODBC applications where the "." gets
stripped and everything after it gets truncated during the login process.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Monday, February 20, 2012
Problems when performing RESTORE DATABASE on Windows XP Mediacenter with Intel Core Duo
We have an application, which as a database server uses
MS SQL Server 2000 Desktop Engine (MSDE; SP3a version). One
of the application users reported that after he switched
computers (installed our application with MSDE on a new
computer with parameters mentioned in the message subject),
the application began to have problems with backup functionality.
The backup functionality of the application does only one
thing: it uses SQL-DMO interface to execute this TransactSQL:
"RESTORE DATABASE ...". The effect of that is one of two:
either the database is apparently restored, but the control
does not return to the application (so the application hangs),
or the database remains in "restoring" state (and the control
does not return to the application too) and is useless to
the user.
The question is: Do you know any possible causes and / or
solutions to this problems? I'd be grateful for any information.
The user did not send me the server log files yet. As soon
as I get them, I'll try to post anything interesting that
I find in them.
HilarionHi
Can you run the command from osql on this machine?
John
"Hilarion" wrote:
> Hi!
> We have an application, which as a database server uses
> MS SQL Server 2000 Desktop Engine (MSDE; SP3a version). One
> of the application users reported that after he switched
> computers (installed our application with MSDE on a new
> computer with parameters mentioned in the message subject),
> the application began to have problems with backup functionality.
> The backup functionality of the application does only one
> thing: it uses SQL-DMO interface to execute this TransactSQL:
> "RESTORE DATABASE ...". The effect of that is one of two:
> either the database is apparently restored, but the control
> does not return to the application (so the application hangs),
> or the database remains in "restoring" state (and the control
> does not return to the application too) and is useless to
> the user.
> The question is: Do you know any possible causes and / or
> solutions to this problems? I'd be grateful for any information.
> The user did not send me the server log files yet. As soon
> as I get them, I'll try to post anything interesting that
> I find in them.
> Hilarion
>|||Hilarion wrote:
> > We have an application, which as a database server uses
> > MS SQL Server 2000 Desktop Engine (MSDE; SP3a version). One
> > of the application users reported that after he switched
> > computers (installed our application with MSDE on a new
> > computer with parameters mentioned in the message subject),
> > the application began to have problems with backup functionality.
> > The backup functionality of the application does only one
> > thing: it uses SQL-DMO interface to execute this TransactSQL:
> > "RESTORE DATABASE ...". The effect of that is one of two:
> > either the database is apparently restored, but the control
> > does not return to the application (so the application hangs),
> > or the database remains in "restoring" state (and the control
> > does not return to the application too) and is useless to
> > the user.
> > The question is: Do you know any possible causes and / or
> > solutions to this problems? I'd be grateful for any information.
> > The user did not send me the server log files yet. As soon
> > as I get them, I'll try to post anything interesting that
> > I find in them.
John Bell wrote:
> Can you run the command from osql on this machine?
I have no direct access to the machine and not a very good contact
to the user, but I'll try to ask that user to try osql to perform
the restore operation. I'll post the results as soon as I get them.
Thank you for your suggestion. If you have any other ideas, then
please post them (do not wait for my reply on the "osql" use -
- it may take much time). Thank you in advance for any future
suggestions and any other help.
Hilarion|||Hi
Maybe it would be possible to use remote desktop or some other alternative
to get this tested? If you have an alternative backup file you could try then
it may be worth trying.
John
"Hilarion" wrote:
> Hilarion wrote:
> > > We have an application, which as a database server uses
> > > MS SQL Server 2000 Desktop Engine (MSDE; SP3a version). One
> > > of the application users reported that after he switched
> > > computers (installed our application with MSDE on a new
> > > computer with parameters mentioned in the message subject),
> > > the application began to have problems with backup functionality.
> > > The backup functionality of the application does only one
> > > thing: it uses SQL-DMO interface to execute this TransactSQL:
> > > "RESTORE DATABASE ...". The effect of that is one of two:
> > > either the database is apparently restored, but the control
> > > does not return to the application (so the application hangs),
> > > or the database remains in "restoring" state (and the control
> > > does not return to the application too) and is useless to
> > > the user.
> > > The question is: Do you know any possible causes and / or
> > > solutions to this problems? I'd be grateful for any information.
> > > The user did not send me the server log files yet. As soon
> > > as I get them, I'll try to post anything interesting that
> > > I find in them.
>
> John Bell wrote:
> > Can you run the command from osql on this machine?
> I have no direct access to the machine and not a very good contact
> to the user, but I'll try to ask that user to try osql to perform
> the restore operation. I'll post the results as soon as I get them.
> Thank you for your suggestion. If you have any other ideas, then
> please post them (do not wait for my reply on the "osql" use -
> - it may take much time). Thank you in advance for any future
> suggestions and any other help.
> Hilarion
>