Sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload' In 2005
Jan 31, 2006
With my Sql server I install SQLxml3.0 and run this with problem.
DECLARE @ObjectError INT
, @Object INT
, @ErrMsg VARCHAR(50)
EXEC @ObjectError = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload', @Object OUT,4I plan to go with the Sql server 2005 but this code give me a error...:eek: Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
My user right not change. And I dont see some "SQLXML" in the "Surface Area Configuration"???
Hi, Am I right in saying that by default, an element of complex typein an XSD annotated schema maps to the table with the same name in thespecified database. If so does anyone have any idea why my app runswith no errors, but doesn't add anything to the table in the DB. I washoping to find the value 'GDS-2392265' in the table.Any Ideas?Thanks in advance.Richvb.net code-----------------Dim objBLobjBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString = "provider=SQLOLEDB.1;data source=xxxSQLEXPRESS;database=xxx;uid=sa;pwd=xxxxx"objBL.ErrorLogFile = "c:error.log"objBL.KeepIdentity = FalseobjBL.Execute("c:SCHEMA.XML", "c:INPUT.XML")objBL = NothingMe.Enabled = TrueDB Table Def-------------------CREATE TABLE [dbo].[FeatureCollection]([fid] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]Schema File------------------<?xml version="1.0" ?><xsd:schema xmlns="urn:schemas-microsoft-com:xml-data"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sql="urn:schemas-microsoft-com:mapping-schema"xmlns:osgb="http://www.ordnancesurvey.co.uk/xml/namespaces/osgb"xmlns:gml="http://www.opengis.net/gml"xmlns:xlink="http://www.w3.org/1999/xlink"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><xsd:element name="FeatureCollection"><xsd:complexType><xsd:attribute name="fid" type="xsd:string"/></xsd:complexType></xsd:element></xsd:schema>XML File------------<?xml version='1.0' encoding='UTF-8'?><osgb:FeatureCollectionxmlns:osgb='http://www.ordnancesurvey.co.uk/xml/namespaces/osgb'xmlns:gml='http://www.opengis.net/gml'xmlns:xlink='http://www.w3.org/1999/xlink'xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'xsi:schemaLocation='http://www.ordnancesurvey.co.uk/xml/namespaces/osgb http://www.ordnancesurvey.co.uk/xml/schema/v5/OSDNFFeatures.xsd'fid='GDS-2392265'></osgb:FeatureCollection>
I've just taken over a project and there's an issue with null values in some fields. The XML file is loaded into SQL Server 2005 with the SQLXMLBulkLoad
The XML file is about 60mb.
Anyone any ideas, the fields in particular that seem to be causing the issue are of type ntext and allow nulls.
Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.
I have a process running SQLXMLBulkload.3.0 against an XML data file which is completing without issue. I cloned the same process for another XML data file but am getting an error I can't seem to resolve. I've checked the working process vs. the one not working (vbscripts, xml data files, xml schema files,etc.) and see no differences that might cause this error. The error when I run SQLXMLBulkload.3.0 is this: "Microsoft OLE DB Provider for SQL Server: All bound columns are read-only" Has anyone run across this before. We are running under sql server 2000. Thank you. J.
How to instanciate SQLXMLBulkLoad object in C#? I have SQL Server 2000 installed and added Microsoft.Data.SqlXml reference to the project, but I can't find any SQLXMLBulkLoad object in it.
What have to be installed to have SQLXMLBulkLoad object?
When the first TrackCodeid is NULL all remaining NULL TrackCodeid is entered as null in the database table but if the first/prev trackcodeID is not NULL then following null trackCODEID is populated with the prev trackcodeID and not as null in database <TrackCodeId>ABCDEFG</TrackCodeId> All Null TrackCOdeID is populated as ABCDEFG if we remove ABCDEFG and then bulkload all null values are populated if a null trackCODEID is to be inserted the prev trackCODEID must be null and must not contain any value
I'm using SQLXMLBulkLoad to insert data into the SQL database. It worked with the test database on my PC but when I changed it into a VPS it throws an error: Invalid Connection String. This connection string that I used worked in my program before the addition of 'provider = sqloledb'.
Here's the connection string:
@"Provider = sqloledb; Data Source=CRM;Initial Catalog=Works;Persist Security Info=True;User ID=userid;Password=password;Asynchronous Processing=True";
I want to import the contents of a remote database every morning via a Web service to populate a data mart. I am looking for the most efficient way to do this. Step 1 is to use the Web Service Task in SSIS to grab the data and save to an XML file (since the content is far too large to store in the String data type).
For step 2, I can either use the SQLXMLBulkLoad object in VB Script to read in the file and populate the tables. Or I can using the XML Source object in Integration Services. It is clear that SQLXMLBulkLoad is the most efficient way to load data, but is the XML Source object in SSIS just a graphical representation of the SQLXMLBulkLoad object, or is it something else entirely that is inefficient because it requires loading of the XML content entirely into RAM before it can process the XML?
I have created a small COM in C# so that I can programatically create and execute stored procedures with SMO. At this point the COM has nothing in it but just a test prototype. But when I tried to create the object as follows, I get the error indicated below. It is not a memory issue because I have adequate storage and RAM.
Please Help!
DECLARE @object int DECLARE @hr int DECLARE @property varchar(255) DECLARE @return varchar(255) DECLARE @src varchar(255), @desc varchar(255)
-- Create an object. EXEC @hr = sp_OACreate 'SQLInterop.CsharpHelper', @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 END
This is the error I am getting:
Error Code: 0x8007000E Description: Not enough storage is available to complete this operation. Source: ODSOLE Extended Procedure
This is the C# code for the COM:
using System; using System.Runtime.InteropServices; using System.Reflection; using System.Runtime.CompilerServices; using System.EnterpriseServices;
// General Information about an assembly is controlled through the following // set of attributes. Change these attribute values to modify the information // associated with an assembly. [assembly: AssemblyTitle("CSServer")] [assembly: AssemblyDescription("Test SQL .NET interop")] [assembly: AssemblyDelaySign(false)] [assembly: AssemblyKeyFile("MyKey.snk")]
// Setting ComVisible to false makes the types in this assembly not visible // to COM components. If you need to access a type in this assembly from // COM, set the ComVisible attribute to true on that type. [assembly: ComVisible(true)]
// The following GUID is for the ID of the typelib if this project is exposed to COM [assembly: Guid("ff35c6b4-81bf-47dd-9290-fcbbb49008d9")]
// Version information for an assembly consists of the following four values: // // Major Version // Minor Version // Build Number // Revision // // You can specify all the values or you can default the Revision and Build Numbers // by using the '*' as shown below: [assembly: AssemblyVersion("1.0.0.1")] [assembly: AssemblyFileVersion("1.0.0.1")]
// the ApplicationName attribute specifies the name of the // COM+ Application which will hold assembly components [assembly: ApplicationName("SQLInterop")]
// the ApplicationActivation.ActivationOption attribute specifies // where assembly components are loaded on activation // Library : components run in the creator's process // Server : components run in a system process, dllhost.exe [assembly: ApplicationActivation(ActivationOption.Server)] namespace SQLInterop { public interface ITest { string SayHello(); string SayIt(String strMessage); }
//[SecurityRole("RBSecurityDemoRole", SetEveryoneAccess = true)] [ComVisible(true)] [CLSCompliant(false)] [ClassInterface(ClassInterfaceType.None)] public class CSharpHelper : ITest { public string SayHello() { return "Hello from CSharp"; }
public string SayIt(String strMessage) { return strMessage + ": from CSharp"; } } }
I tried to send the html format email alert through sql server. Its working fine in sql server 2000 (Dev Server). then i moved to production server (sql server 2005). when i run this email program (USP_SendHolidayAlert) in sql server 2005, i got the folloing errors, Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Stored Procedure---------------- CREATE Procedure USP_SendHolidayAlert /* Name: USP_SendHolidayAlert Description: Send Email holiday Alert Author: Gopalan Mani Modification Log: Create Description Date Changed By Created procedure 13-05-2008 Gopalan Mani */ @SenderName varchar(250), @SenderAddress varchar(250), @RecipientName varchar(250), @RecipientAddress varchar(250), @Subject varchar(250) , @Title varchar(350) , @HolidayDate varchar(350) as DECLARE @HtmlContent varchar(8000) DECLARE @object int DECLARE @executable int DECLARE @RecCount int EXEC @executable = sp_OACreate 'CDONTS.NewMail', @object OUT EXEC @executable = sp_OASetProperty @object, 'BodyFormat', 0 EXEC @executable = sp_OASetProperty @object, 'MailFormat', 0 select @HtmlContent =MailText from Tbl_EmailContent where RecID=2 select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Title',@Title) select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Date',@HolidayDate) EXEC sp_OAMethod @object, 'Send', NULL,@SenderAddress ,@RecipientAddress,@Subject,@HtmlContent EXEC sp_OADestroy @object please do some necessery steps to solve this problems.
I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005 It looks some thing like this
EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5 IF @hr <> 0 BEGIN --Not able to create the object Return @hr END --Not able to create the object
EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman IF @hr <> 0 BEGIN --Not able to execute Com function Return @hr END --Not able to execute Com function
I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll? One executing this fucntion I am returning -2147221005 -214721148 error code.
Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's
I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005 It looks some thing like this
EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5 IF @hr <> 0 BEGIN --Not able to create the object Return @hr END --Not able to create the object
EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman IF @hr <> 0 BEGIN --Not able to execute Com function Return @hr END --Not able to execute Com function
I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll? One executing this fucntion I am returning -2147221005 -214721148 error code.
Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's
Hi, I have com object on another machine, and I want to call from diffetent machine,Is It possible to use sp_oACreate to creating object of com object that resides on another computer.
this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.
Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class Math
Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)
trying to use MS Access Automation from within SQL Server 2000 .
Seems that OpenCurrentDatabase (Access Application property) just hangs...
Following, the code that is used:
--BOF--
--object vars DECLARE @w_object INT, @s_object INT
--error handling vars DECLARE @error INT DECLARE @src VARCHAR(255), @desc VARCHAR(255)
--execution vars DECLARE @command NVARCHAR(100), @property NVARCHAR(20)
--create an Access application EXEC @error = sp_OACreate 'Access.Application', @w_object OUT IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT SELECT CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description] RETURN END
--get default security level EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT SELECT @property AS default_security_level
--set application security level to low (no sandbox) --thought this would help, but no luck EXEC @error = sp_OASetProperty @w_object, 'AutomationSecurity', '1' --Low Security IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object GOTO Cleanup END
--Test security level EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT SELECT @property AS my_security_level
--check current user EXEC sp_OAGetProperty @w_object, 'CurrentUser', @property OUT --should be Admin SELECT @property AS current_application_user --it is Admin
--Open non password protected database, SET @command = 'OpenCurrentDatabase("C:mikros.mdb")'
--unfortunately this hangs... EXEC @error = sp_OAMethod @w_object, @command IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT SELECT 'Failed to Open database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description] GOTO Cleanup END
--Close current database EXEC @error = sp_OAMethod @w_object, 'CloseCurrentDatabase' IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT SELECT 'Failed to Close database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description] END
Cleanup: --close Application EXEC @error = sp_OAMethod @w_object, 'Quit' IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object END
--destroy Application Object EXEC @error = sp_OADestroy @w_object IF @error <> 0 BEGIN EXEC sp_OAGetErrorInfo @w_object END
I have a user login that is executing an sp. It gets the follwoing error: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1 EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1 EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1 EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1 EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.
Everything ic an find on the net refers to Sql Server 2000 but this is 2005, all the resolutions say you must grant exec permissions to the user account for these sp's in the master database.
BUT is SS2005 they are in the mysqlsystemresource database.
WHen i try the following
grant exec on mssqlsystemresource.sys.sp_OACreate to UserLogin:
I get this error:
Cannot find the object sp_OACreate, becuase the object does not exist or you do not have permission.
I am logged in as sysadmin so i doubt it is permission.
How do i get a user login to be able to exec these sp's?
I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error. EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'. How canI solove this problem?
I copy script that use SQL proc to call DTS package from this site.
When I run this procedure in SQL query alalyzer. I got an error messages: EXECUTE permission denied on object sp_OACreate/method/GetProperty/Destroy. I did not change anything in spExecutePKG. Even I used sa as ServerPWD. How to fix this problem? my code is exec spExecutePKG 'myserver','execl_DTS','sa','0','' Thanks jimmy
------proc original code CREATE PROC spExecutePKG @Server varchar(255), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME()) @IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = ''-- Package Password AS SET NOCOUNT ON /* Return Values - 0 Successfull execution of Package - 1 OLE Error - 9 Failure of Package */ DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT IF @hr <> 0 BEGIN PRINT '*** Create Package object failed' EXEC sp_displayoaerrorinfo @oPKG, @hr RETURN 1 END
I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
First of all I am planning to install sql 2005 standard and sql standard sp2 on vista ultimate professional using vs 2008 pro I have some questions about installing sql 2005 what is the best way to install sql 2005 on vista My article I got this from ms 403393 (upgrading sql standard 2005 to sql standard 2005sp2) 1 sql standard .(shortcut right click from cd/dvd not possible) I have heard command prompt start menu, click cmd type run D: cd/dvd exe From a wizard (sql sp2)
2. sql standard sp2 a. upgrade using a wizard b. open a command prompt (not sure if right click is best option) install dvd media example dqlserver2005sp2-kb921896-exe SKUUPGRADE=1(is run as administrator option on media) do I type run or what do I type ? c. go to system configuration page(where is this) you should see a single warnining you need to install sp2, the edition should say success if it does not please reapply d. on the registration page please enter your registeration number e. on the comments to install please enter database services, reporting services workstation components, books online and development tools( I wish to install all components)
f. on the instance page click default instance(default install) g. finish rest of wizard h. apply sql sp2
I also have of seperate question of upgrading sql from vista using a Configuring local admin account account (configuring administration from a wizard-will this notify me by default) I do not want my administrative priviledges elevated. I will be using default configurations for my installations......... I often wish there would a nice picture examples of doing this Please help
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
I am trying to import Access reports using Visual Studio 2005 and Access 2007. SQL Server 2005 with Reporting Services is also installed. I select Reports in the Solution Explorer and then Import Reports... Microsoft Access and browse to my Access database. After I select the database I see it open briefly as if it is trying to import the reports but then Access quickly closes and a error pops up: "You already have the database open." I have checked and double checked and the database is not open. I have even tried several different databases and the same errror occurs. Any help would be greatly appreciated.
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I have created a database using VB Express 2005 express edition and I have created number of tables and SP's etc.
I am trying to use the database diagram tool but I get an error stating that the database doesn't have a valid dbo. I say yes to the prompt about making myself a valid dbo but I then get a message stating that I do not have permissions to imprersonate the dbo.
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll Public member 'Variables' on type 'IDTSPackage90' not found.
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL' into my microsoft sql server database. I am not talking about the below line of code where website is the name of my paramater. If i do that it will just place a blank into that field in the database. If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbox it needs to say NULL. I also tried the second line of code but that places a single quote in front and behind NULL. So the field will have the value 'NULL'. website.value = txtwebsite.text.tostirng if txtwebsite.text ="" then website.value ="NULL" end if The reason why I need the NULL there is because I bind the website filed to a hyperlink template in a gridview. The actual text of the hyperlink is bound to PAYER which is a name of a insurance payer but the navigateto is bound to the website field. If i do not enter any data into the website field and it stays as NULL, then when my gridview is loaded, payers that dont have a website will not be underlined and user wont have the option to click on them since there is no value for the navigateto. But if i use my form to update/add a payer and leave the website textbox blank in puts a blank into that field in my database and when it loads that new row into the gridview the PAYER is underlined and u can click on it but it will just take u to the web site is unavailable page. So is there anyway to actaull have NULL placed into a filed and not just blank space
I am out of brain cells. I can not figure out how to upgrade instances of sql express to sql 2005 or even add an instance of sql 2005. I have installed/upgraded everything else (SSMS, etc.) to SQL 2005. I am dazed and confused. I don't even know where to start in explaining what I have tried and done. I have 303 log files going back to 3/27/2007 from various attempts. Where do I start in figuring this debacle out???
