.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
abhays
satyapriyanayak
SP
Ashutosh Jha
ASPEvil
Post New Web Links

sp_addsubscription: "sync_type" issue on SQL Server 2005

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
 

Hello,

I am seeing some unexpected malfunctioning of the sp_addsubscription procedure when I try to setup the bidirectional replication between 2 servers running SQL Server 2005:

1. When I generate the subscription through the wizards the sp_addsubscription call is issued with sync_type = 'automatic' which implies a snapshot to be taken before the replication can start. The subscription seems to be running ok in such case, just that it waits for the initial snapshot.

2. If I try to change the sync_type to 'none' (which is the same as 'replication support only') which actually means that only data updates are being sent and no initial snapshot is required, the sp_addsubscription procedure fails with:

The process could not connect to Subscriber 'LL-FFF-DDD'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Source: MSSQLServer, Error number: 18456)
Get help: http://help/18456

Am I missing something in this parameter configuration [change] or is a hotfix that needs to be applied?


View Complete Post


More Related Resource Links

Kerberos issue with SQL Reporting Services 2005 on Server 2003 R2

  
Hi Guys,apologies if this is the incorrect forum, so moderators, feel free to move it to SQL/IIS/SharePoint as appropriate... [Windows Server Security moderator pushed me this direction]I have a test environment that I'm trying to get SQL Reporting Services 2005 SP3 working in integrated mode with SharePoint 2007 SP2.The environment is all in VMWare, running Server 2003 R2 x86 and is layed out like this:SERVER A:AD/DNS/DHCPSERVER B:SQL 2005 SP3 CU8SERVER C:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82SERVER D:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82Through the use of DNS and (SharePoint) Alternate Access Names, SERVER D is used to deliver the Main Content in SharePoint and the Reporting Service website.  SERVER C is used to deliver the Central Admin, SSP and MySite.I've set up SPN's for the SharePoint App Pools, using the following: [main content] setspn -S HTTP/SERVERA DOMAIN\AppPoolUserA setspn -S HTTP/SERVERA.FQDN DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB.FQDN DOMAIN\AppPoolUserA [repor

SQL Server Express 2005 connection timeout issue.

  
Client: Sql Sever Management Studio Express 2005 (SSMSE) on Vista Server: Sql Server 2005 Express on Windows Server 2008 Standard When I first try to log into SSMSE from the client machine, the login operation times out. When I retry, the login succeeds almost immediately. If I close the app and re-open it, the same thing happens. This appears to be infinitely repeatable. I am able to reproduce this behavior using SQLCMD.exe. Here's the error: "Timeout expired. The timeout elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)" The technical details reference Error Number -2 What may be unique or tricky about this client's network is that it's a mixture of IPv4 and IPv6. I believe the server is configured for both, but honestly the networking side of things is outside of my expertise. Has anyone else experienced a problem like this? I'm looking for some troubleshooting tips and of course a flat-out answer :-). Thank you in advance for your time and expertise - I hope I've broken the problem down to its simplest form. Will.

SQL Server 2005 Cluster failover issue

  
Hi All, I'm fequently experience Cluster failover issue. The cluster tries to failover and it fails back. Because of this the SQL server is getting restarted and this is a bugging issue during business hours. The following are the System event logs, Cluster resource 'SQL Server' in Resource Group 'Production' failed. The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop control. The SQL Server Agent (MSSQLSERVER) service entered the stopped state. The SQL Server (MSSQLSERVER) service was successfully sent a stop control. The SQL Server (MSSQLSERVER) service entered the stopped state. The SQL Server (MSSQLSERVER) service was successfully sent a start control. The SQL Server (MSSQLSERVER) service entered the running state. The Cluster Service brought the Resource Group "Production" online.    The following are the application event logs, The client was unable to reuse a session with SPID 131, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed [sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Query timeout expired [sqsrvres] OnlineThread: QP is not online. The client was unable

Issue with SAN Drive Migration in master database in clustered SQL Server 2005 SP1

  

All,

 

In our SQL Server 2005 SP1 cluster, I have to retire the existing SAN and move all my databases to new SAN drive. I have successfully moved all datafiles (user,system databases, resource) except master database.

 

Even when I change the parameters in start-up from configuration manager, I am getting error when starting up from cluster administrator / configuration manager.

 

But I am able to start the SQL Server successfully from command line (without any flags).

 

When I start the SQL Server from cluster administrator / configuration manager (after changing the parameters), I see that the start-up parameters are changing back to originalpath (old SAN) .

 

Please let me know incase you need any more information.


Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

3) Also one more point about the s

Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

SQL Server 2005 Express Connectivity Issue

  

I have a virtual dedicated 2003 server (standard edition) on which I installed SQL Server Express 2005 in mixed mode.  After installing the server I added the 2010 Web Developer package.  I updated the remote connections to allow TCP/IP and named pipes.  I also ensured that the TCP/IP protocol was enabled under the protocols tab.  After restarting the SQLEXPRESS service and attempting to configure a SqlDataConnection in Web Developer I got the ubiquitous error,

"A network-related or instance-specific error occured while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify the instance name is correct and that the SQL Server is configured to allow remote connections.  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)". 

When I try using a trusted connection with sqlcmd I got this error,

"HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft 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..
Sqlcmd: Error: Microsoft SQL Native Client : Log

Issue with connecting to sql server 2005

  

Greetings!

I upgraded my existing sql 7 database system to sql 2005 and it seems that everything is fine. But when i tried to connect using the existing program we used in sql 7 i experienced some log-in failure(i use sa user). The strange thing is that when i change the password to numeric i can successfully log-in to database, but setting the password to character that's the time i experienced log-in failure. It seems that there is a problem with the datatype configuration. By the way i already set the security to mixed mode. Some help much appreciated. Thank you.

SQL Server 2005 store procedure performance issue

  

We are calling store procedure from vb.net client following are the observation.

1) sqlserver 2005  takes huge amount of time (more than 1 hour) to execute procedure. The procedure has select statement using join statement. (Generally this procedure executes and populate data in 5 seconds when it is called from .net client.)

2) If same procedure is executed from sql server management studio, procedure executes and populate data in 5 seconds (works as per expectation)

3) If we recompile procedure (i.e. open procedure in alter mode in management studio and click on execute button) ,  the procedure executes and populate data as per expectation.

4) This is intermittent issue on production machine. Most of the time procedure is executed and populate data as per expectation when called from vb.net client. But it does not give expected outout in some scenario. The frequency for occurring the issue is  once in 15 days and there is no pattern in which the issue is occurring.

 

Can any one help to resolve the issue.

 

Thanks in advance!!!

 

 

 


SQL Server 2005 Deadlock issue - Incompatible locks honoured!!!

  

I'm seeing an interesting deadlock problem with SQL Server 2005 (2000 mode) where incomptible locks are being honoured (U & X). This was already granted before the deadlock & the each of the processes try to get a 'U' lock again and results in deadlock. Deadlock graph is shown below. The table in question (PLTransactionSeq)  is used for Sequencing & only has 1 record. It has a clustered index on the sequence field.

Did I read the deadlock graph incorrectly ?

Thanks,

Rajeev

Deadlock graph

<deadlock-list>

<

 

deadlock

Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

SQL Server 2005 Replication - Replicated Databse Performance Issue

  

Hello,

 

I am struggling to work out a very frustrating replication issue and wondered if anyone could help me.

Here is my situation.

 

I have a virtual machine with 16gb RAM and 4 quad core 3Ghz processors and 250gb disk. This has Windows Server 2008 64 bit installed.

The machine has a SQL Server 2005 64 bit instance installed with SP3.

I have setup a publication on this SQL Server called pub1 , this publication publishes all tables/sp's /views and UDF's . I have set the article properties for the published tables as below.

- Copy Check Constraints = True

- Copy Clustered Index = True

- Copy Non Clustered Index = True

- Copy Default Value Spec = True

- Copy Collation = True

- Copy Insert, Update and Delete = True

- Copy unique key contraints = True

- Copy XML Indexes = True

Every other setting is set to false.

I have created a subscription to pub1 within the same SQL Server instance. The database has replicated everything I require without an issue.

If I run a query against the databse that I have published, it returns the query in less than 10 secs. If I run the same query against the subscription DB then this query takes over 30 mins to return.

I thought this was an issue with the indexes not replicating. From what I have selecte

Microsoft SQL server 2005 database LDF MDF files attaching issue

  

Dear all,

Last month our Windows 2003 server hardware raid was failed, all live databases available on this raid partition, now we have recover all databases which MDF and LDF files.

 Below is the error snap link. http://cid-51b01a6564f704c6.photos.live.com/self.aspx/Kamran/Error%20Snap.png

We are in the process of attaching the Recovered Database “nice_interactions ” using .MDF and .LDF files; it prompts the Following error [Please view the snapshot below for reference]. Kindly advise further how to restore or attach database using .MDF and .LDF files which is not cleanly shutdown.

Microsoft SQL forwarding to following URL in the Detail of below  listed Error while we are trying to attach the recovered .mdf file.

 

http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824

Need help in this regards.

Kamran Khan

 

Issue with linked server 2008 (and R2) not an issue in 2005

  

Hi,

I have been using SQL 2005 successfully to connect to tables in a 10g Oracle data warehouse. I have been using the 11g client.

I have tried several times to get this working in SQL Server 2008 (and R2) and have tried the 10g and 11g clients...

I do a query like Select top 100 * from DWD..DW.IC_TRAN_PND and I get the error: 

"Invalid data for type "numeric".

 

I have read about the reasons for this but is there a way to make it work like it does in SQL server 2005?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend