Cannot propogate to subscriber

I'm getting this error when I try to replicate
The schema script
'\\CVTDEESQL005\ETMSData\unc\CVTDEESQL005_ZenithSQ L_L_060104_ZenithSQL_L_060104\20070120231902\tblCa ll_1.sch'
could not be propagated to the subscriber. The step failed.
I can find this row in sysMergeSchemaChange but I'm not sure why it's
causing a problem. Can someone give me some guidance?
kr
Spike
I'd do some logging and see if this records more info
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||"Paul Ibison" wrote:

> I'd do some logging and see if this records more info
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Message: The process could not read file '\\CVTDEESQL005\ETMSData\unc\CVTDEESQL005_ZenithSQ L_L_060104_ZenithSQL_L_060104\20070120231902\tblCa ll_1.sch' due to OS error 53.
53 is a network error, so I quess either the file is not there or I've lost
access to the directory. If I were to remove this line from
sysMergeSchemeChange would that sort it? Or be a very bad idea?
The row in question is the second one after exec.sp_MsChange_Retention etc.
|||I'd run the snapshot once again. Perhaps there are also other files that
went missing, and if there's an underlying problem it'd be best to sort it
out systematically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||"Paul Ibison" wrote:

> I'd run the snapshot once again. Perhaps there are also other files that
> went missing, and if there's an underlying problem it'd be best to sort it
> out systematically.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Okay I'll try that but one thing I don't understand is why I'm the only person affected. All my other users can replicate ok but even if I create a new database and set up a new pull subscription I still get that error.
|||OK - if it is reproducible, then let's look a little deeper. After the
snapshot agent runs, does the file exist? Does the account that sql server
agent runs under (are you using push or pull?) have access to the repldata
share? Is there enough space on the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||"Paul Ibison" wrote:

> OK - if it is reproducible, then let's look a little deeper. After the
> snapshot agent runs, does the file exist? Does the account that sql server
> agent runs under (are you using push or pull?) have access to the repldata
> share? Is there enough space on the subscriber?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Hi Paul
I've tried to run the snapshot agent but it fails with "The Agent is
suspect. No response after 30 minutes. I'm using pull subscription. I need
to get access to the server via PCAnywhere to verify that the file/path is
acessible by the SQLAgent account but that may take some time. So I'll get
back to you.
all the best
spike
|||Hi,
Have you downloaded the SP4 hotfix (if your on SP4 that is!)
I downloaded and installed this;
http://support.microsoft.com/kb/916287
TIM
"Spike" wrote:

>
> "Paul Ibison" wrote:
> I've tried to run the snapshot agent but it fails with "The Agent is
> suspect. No response after 30 minutes. I'm using pull subscription. I need
> to get access to the server via PCAnywhere to verify that the file/path is
> acessible by the SQLAgent account but that may take some time. So I'll get
> back to you.
> all the best
> spike
|||"Paul Ibison" wrote:

> OK - if it is reproducible, then let's look a little deeper. After the
> snapshot agent runs, does the file exist? Does the account that sql server
> agent runs under (are you using push or pull?) have access to the repldata
> share? Is there enough space on the subscriber?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> We've rebooted the server and that seems to have cleared everything up!
Thanks for your help, cheers spike

Cannot open user default database - login failed

Hey,

I renamed a database, and it happened to be my default; so I basically screwed myself, because I can't change it through script (doesn't exist or you don't have permissions). What can I do?

Thanks.

Well do you have another database?

If you do then on the connect screen click the "options" button and pick the other database in the drop down list. Then you can log in and change your default.

|||

Hey,

I do, but still no result; because of the default database problem, as I go to connect, it gives me that error... I don't have the option to even try that unfortunately. Any other ideas?

Thanks.

|||

Well what database are you using? Sql Server 2005, 2000?

Anyway, I had the same problem as you, but I changed my default database from outside SqlServer using the DOS Command prompt.

The method to do this is here. It's pretty simple and easy.

http://support.microsoft.com/?kbid=307864

|||

If you're using SQL2005, open Management Studio->new a query (connect to Database Engine)->modify Options, change 'Connect to database' DropdownList to a valid database name (choose one your account can access)->Connect-> Execute the following system procedure:

sp_defaultdb 'youlogin','newdefaultdatabasename'

If you're using SQL2000 Query Analyzer, enter something like 'SQLInstanceName;database=dbname' in the 'SQL Server' textbox and connect, then execute the system procedure as above.

Cannot open database requested in login...

Hi,

I am using ASP to connect to an MS SQL database. The script connects to the database OK and I can retrieve recordset data and add new rows without a problem.

However, when I try to edit a recordset (using the recordset.update command), I get the following error:

Cannot open database requested in login 'andthen_development'. Login fails.

My first thought was that it must be a permissions problem, but that doesn't explain why I can view and add rows, but not edit. Any ideas?

Thanks, Pauldid you try an "Update" command

objCommand.ActiveConnection = objConnection objCommand.CommandText = "Update Table tbl set ..."
Call objCommand.Execute

just see if this works with your login

or try updating with the QA and your login|||then we'll see why the RecordSet.Update doesn't work|||Just tried an "UPDATE TABLE..." command directly and it works fine. However, I have an ASP include file that I use to handle all database functionality centrally. Included in this is the following function:

Function editRow(sqlString, rowParameters)
'#-- UPDATES AN EXISTING DATABASE ROW

'#-- SET UP RECORDSET FOR EDITING A RECORD
Set recordSet = Server.CreateObject("ADODB.RecordSet")
recordSet.CursorLocation = adUseServer
recordSet.CursorType = adOpenForwardOnly
recordSet.LockType = adLockOptimistic

recordSet.Open sqlString, connection, , ,adCmdText

'#-- ITERATE THROUGH rowParameters TO UPDATE ITEM'S FIELDS
Dim rowField
For Each rowField In rowParameters
recordSet(rowField) = rowParameters.Item(rowField)
Next

'#--UPDATE RECORDSET (UPDATE RECORD)
recordSet.Update

End Function

The function accepts an SQL string to identify the row (e.g. "SELECT * FROM...") and a Dictionary object that contains keys relating to field names and values giving the new contents for the row.

This produces an error on the recordset.Update line.

Paul|||maybe it's the cursor location
or one of the option of the recordset

Set recordSet = Server.CreateObject("ADODB.RecordSet")
recordSet.CursorLocation = adUseServer
recordSet.CursorType = adOpenForwardOnly
recordSet.LockType = adLockOptimistic

cannot install Northwind database in SQL Server Express

Hi,

I tried to install the Northwind database into SQl Server Express via the instnwnd.sql script using the command line:

osql -E -i instnwnd.sql

after a time out though I get this message:

"[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections."

I already granted access to the administrator account on my machine.

Anyone knows what I need to do more?

Thanks in advance

greetings from Belgium,

Anthony

Are you running osql on the same machine as SQL Server or on a remote machine? If you are executing this on a remote machine, you need to enable remote connections.

Thanks
Laurentiu

|||

Hi Laurentiu,

No, I'm running SQL Server on the same machine.

Thanks for your reply,

Anthony

|||

You should specify the SQL Express instance name:

sqlcmd -E -S.\sqlexpress -i instnwnd.sql

The same will work for osql, of course. Make sure you type the dot. If you're connecting remotely, replace the dot with the machine name.

Thanks
Laurentiu

Cannot get Server Status using DMO on SQL 2005 : Access Denied

Hi everybody,

I'm facing a small problem using SQL-DMO in a vbs script. This script was written for SQL 2000 and 2005 so using SMO is not really an option for me.

In SQL 2000 and SQL 2005 SP1, it looks like I have no problem doing the following from a client computer:

Dim oServer
Const cServerName = "(local)"
Const cUserName = "sa"
Const cPassword = "sa"
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.Name = cServerName
MsgBox "Status (before connection): " & oServer.Status

note that this happens *before* I connect to the server. This is because I want to start the service if is not started or wait until it is started if it is currently starting.

In SQL 2005 SP2, I get the following "Service Control Error: Access Denied", no matter what user I use...

I can only query the server's status *after* I am connected, which well, does not do much for me in that particular case ;-).

I was wondering if anybody had encountered the same problem before and if so, how they went around it. I have to confirm this, but doing this from the server itself looks like it is working... Could it then be due to a mismatch between client and server service packs or sql server backward compatibility package being required on both client and server?

Thanks,

Greg

Well, in case anybody's interested, here's what I found that proved to be the problem. It's a 2000 sp4/2003 sp1 problem. The ACL has changed and remote queries without prior connection are not allowed by default anymore. There is a fix to allow them by changing the ACL to give the required rights. In my case the fix is to try to connect anyways and if the server is not up then it will simply fail since many companies will simply not allow to do such things to get the previous behavior back .

kb Artice #: 907460

Greg

Cannot get Server Status using DMO on SQL 2005 : Access Denied

Hi everybody,

I'm facing a small problem using SQL-DMO in a vbs script. This script was written for SQL 2000 and 2005 so using SMO is not really an option for me.

In SQL 2000 and SQL 2005 SP1, it looks like I have no problem doing the following from a client computer:

Dim oServer
Const cServerName = "(local)"
Const cUserName = "sa"
Const cPassword = "sa"
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.Name = cServerName
MsgBox "Status (before connection): " & oServer.Status

note that this happens *before* I connect to the server. This is because I want to start the service if is not started or wait until it is started if it is currently starting.

In SQL 2005 SP2, I get the following "Service Control Error: Access Denied", no matter what user I use...

I can only query the server's status *after* I am connected, which well, does not do much for me in that particular case ;-).

I was wondering if anybody had encountered the same problem before and if so, how they went around it. I have to confirm this, but doing this from the server itself looks like it is working... Could it then be due to a mismatch between client and server service packs or sql server backward compatibility package being required on both client and server?

Thanks,

Greg

Well, in case anybody's interested, here's what I found that proved to be the problem. It's a 2000 sp4/2003 sp1 problem. The ACL has changed and remote queries without prior connection are not allowed by default anymore. There is a fix to allow them by changing the ACL to give the required rights. In my case the fix is to try to connect anyways and if the server is not up then it will simply fail since many companies will simply not allow to do such things to get the previous behavior back .

kb Artice #: 907460

Greg

Cannot figure out how to write this script?

Hi was wondering if somebody could help on this one.
I have a table called LocalisationText and a field called "ResourceText".After localization bad data went into the ResourceText field (double and triple quotes instead of singles).

EG
Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""

Now I have been asked to write a script that
converts all the double/triple quotes preceding {number} with single quotes .

The above corrected should look like

Entrada "{0}" en ubicación "{1}"
在或大约 "{0}" 的命令出错
User Post Code Group of Type "{0}" is "{1}"

Can you help?

Is this a text/ntext column or varchar/nvarchar column? Please take a look at PATINDEX function. You can use a pattern like '%""{[0-9]}""%' to locate the double-quotes you want to replace and then use updatetext for text/ntext or stuff for varchar/nvarchar. And you will need to perform this iteratively until you don't find any matches for the pattern. This will provide a replace with search based on a pattern behavior.|||

Hi there

thanks for your reply.

The column in question is an nvarchar 512.

I will look into patindex function .

Do you have a quick example how I could do it?

thanks

|||

You can then use just replace like:

declare @.s nvarchar(512), @.p int
set @.s = N'Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""
'
print replace(replace(replace(@.s, '""', '"'), '""', '"'), '""', '"')

|||

Or you can do your update in a loop and additionally include required criteria

SELECT top 1 * FROM LocalisationText
WHILE @.@.ROWCOUNT > 0
BEGIN

UPDATE LocalisationText_Test
SET ResourceText = REPLACE(ResourceText,'''''','''')
WHERE ResourceText LIKE '%''''{[0-9]%'
OR ResourceText LIKE '%[0-9]}''''%'

END

GO

|||

thanks a lot for your reply that worked!!!!!!!

thanks again

|||You don't need the loop since REPLACE will replace all occurrences of the specified string. And the WHERE clause of UPDATE gets the qualifying rows. Just a simple UPDATE will suffice.

Cannot find file or assembly error.....

I created an assembly to access my SSRS web service in a Script task. The package runs fine on my machine but gets the following error from the production box.....

The script threw an exception: Could not load file or assembly 'Microsoft.SqlServer.ReportingServices2005, Version=0.0.0.0, Culture=neutral, PublicKeyToken=3bd4760abc5efbcb' or one of its dependencies. The system cannot find the file specified.

I followed the exact same procedures for creating the .dll on the production box as I did developing on my machine...strong name, load to gac etc....but it still cannot find it.

My production SQL Server is 64 bit so perhaps there is another step I need to take? Anyone have a clue as to what I may be missing?

TIA

Well, I found the answer to my own question on the following blog.....

http://www.developerdotstar.com/community/node/333

Apparently, the resolution was to open the package/task in BIDS on the production box and save it and then redeploy. Something about the versioning on the assembly, which is beyond a .Net newbie like me. My guess is when I created/moved the new assembly to the production box it put a new version number on it. To pick up the new number I had to open the package/task so it would load the new assembly with the new version number.

Perhaps one of the MS guys could tell me if there is there a better way I should be doing this? Perhaps through the SSIS deployment configurations...which I have admittedly not been using (have just been importing my packages to the msdb folder in SMS)?

Cannot figure out how to write this script?

Hi was wondering if somebody could help on this one.
I have a table called LocalisationText and a field called "ResourceText".After localization bad data went into the ResourceText field (double and triple quotes instead of singles).

EG
Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""

Now I have been asked to write a script that
converts all the double/triple quotes preceding {number} with single quotes .

The above corrected should look like

Entrada "{0}" en ubicación "{1}"
在或大约 "{0}" 的命令出错
User Post Code Group of Type "{0}" is "{1}"

Can you help?

Is this a text/ntext column or varchar/nvarchar column? Please take a look at PATINDEX function. You can use a pattern like '%""{[0-9]}""%' to locate the double-quotes you want to replace and then use updatetext for text/ntext or stuff for varchar/nvarchar. And you will need to perform this iteratively until you don't find any matches for the pattern. This will provide a replace with search based on a pattern behavior.|||

Hi there

thanks for your reply.

The column in question is an nvarchar 512.

I will look into patindex function .

Do you have a quick example how I could do it?

thanks

|||

You can then use just replace like:

declare @.s nvarchar(512), @.p int
set @.s = N'Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""
'
print replace(replace(replace(@.s, '""', '"'), '""', '"'), '""', '"')

|||

Or you can do your update in a loop and additionally include required criteria

SELECT top 1 * FROM LocalisationText
WHILE @.@.ROWCOUNT > 0
BEGIN

UPDATE LocalisationText_Test
SET ResourceText = REPLACE(ResourceText,'''''','''')
WHERE ResourceText LIKE '%''''{[0-9]%'
OR ResourceText LIKE '%[0-9]}''''%'

END

GO

|||

thanks a lot for your reply that worked!!!!!!!

thanks again

|||You don't need the loop since REPLACE will replace all occurrences of the specified string. And the WHERE clause of UPDATE gets the qualifying rows. Just a simple UPDATE will suffice.