Internet integration: sp1

Showing posts with label sp1. Show all posts

Monday, March 26, 2012

Invalid object name...

Hello all,
however, this is my first question to this news. I am working with RS SP1,
and have question. I have example procedure:
CREATE PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
CREATE TABLE #table (ID SQL_VARIANT)
INSERT INTO #table(ID) VALUES (@.ord)
SELECT * FROM #table
DROP TABLE #table
GO
When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID) I
get following error:
Could not generate a list of fields for the querry...
Invalid object name #table
Ofcourse, this procedure works good in Query Analyser. Anyone has idea, why
this is not working ?
--
Ing. Branislav GerzoI got it to work w/o a problem. However, I do see the same error if I enter
the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation dialog
box while attempting to define the dataset it uses. Try actually executing
the procedure with a parameter or passing in a static value from the Generic
Query Designer data window once you've defined the dataset. If you use
static value like (EXECUTE GEGE_test_a '11'), simply change it after to use
your query parm.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
> I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
> why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Use a table ariable instead of the temp table:
ALTER PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
DECLARE @.table TABLE(ID SQL_VARIANT)
INSERT INTO @.table(ID) VALUES (@.ord)
SELECT * FROM @.table
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Dejan Sarka [DS], on Friday, October 29, 2004 at 17:17 (+0200)
contributed this to our collective wisdom:
DS> Use a table ariable instead of the temp table:
DS> ALTER PROCEDURE GEGE_test_a
DS> @.ord SQL_VARIANT AS
DS> SET NOCOUNT ON
DS> DECLARE @.table TABLE(ID SQL_VARIANT)
DS> INSERT INTO @.table(ID) VALUES (@.ord)
DS> SELECT * FROM @.table
DS> GO
thanks, I was afraid that someone will answer like this. Ofcourse,
this works, but my problem is, that in my situation I have to fill
@.table_var with result of another procedure. And I found this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
A3:
1. Tables variables cannot be used in a INSERT EXEC or SELECT INTO
statement.
2. You cannot use the EXEC statement or the sp_executesql stored
procedure to run a dynamic SQL Server query that refers a table
variable, if the table variable was created outside the EXEC statement
or the sp_executesql stored procedure. Because table variables can be
referenced in their local scope only, an EXEC statement and a
sp_executesql stored procedure would be outside the scope of the table
variable. However, you can create the table variable and perform all
processing inside the EXEC statement or the sp_executesql stored
procedure because then the table variables local scope is in the EXEC
statement or the sp_executesql stored procedure.
Ofcourse, i'd like to use table variables, they are fast, they are
cool. But, how to fill them with result of another procedure ?
I can't cheat them in any way, I have only one idea for that -
procedure which fill @.tabl_var using cursors. But I hope there is
better way do this.
Dejan, please help.
--
...m8s, cu l8r, Brano.
[Alright, who g r e a s e d the tagline?.]|||John H. Miller [JHM], on Friday, October 29, 2004 at 11:14 (-0400)
typed the following:
JHM> I got it to work w/o a problem. However, I do see the same error if I
enter
JHM> the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation
dialog
JHM> box while attempting to define the dataset it uses.
anyone knows, why this error occurs ? I can't use temp tables in my
procedures ?
JHM> Try actually executing
JHM> the procedure with a parameter or passing in a static value from the
Generic
JHM> Query Designer data window once you've defined the dataset. If you use
JHM> static value like (EXECUTE GEGE_test_a '11'), simply change it after to
use
JHM> your query parm.
No, it also doesn't work, I get the same message back. (could not
generate a list...). I really don't know why, it is known bug, or
what?
Thanks a lot. My all work stops on this :(((
--
...m8s, cu l8r, Brano.
[Applaflammaphobia: A vacation fear that the house will bu]

Monday, March 19, 2012

Invalid Database after SP1 Install

We applied SP1 and now are getting the following error:
The version of the report server database is either in a format that is not
valid, or it cannot be read. The found version is 'C.0.6.51'. The expected
version is 'C.0.6.43'. To continue, update the version of the report server
database and verify access rights. (rsInvalidReportServerDatabase)
Is there a remedy for this?SP1 has to be applied both where you develop application and on the server.
Have you applied SP1 at both places?
Give a little more info on your configuration.
Bruce L-C
"Gary Gordon" <sbss1@.icdc.com> wrote in message
news:10e871srs5s3762@.corp.supernews.com...
> We applied SP1 and now are getting the following error:
> The version of the report server database is either in a format that is
not
> valid, or it cannot be read. The found version is 'C.0.6.51'. The expected
> version is 'C.0.6.43'. To continue, update the version of the report
server
> database and verify access rights. (rsInvalidReportServerDatabase)
> Is there a remedy for this?
>|||We applied the SP to both the develpoment machine and the server. We ended
up having to uninstall Reporting Services and reinstall it. Then we import
the database from our backup that was done prior to application of the SP.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23Ll9303XEHA.2360@.TK2MSFTNGP10.phx.gbl...
> SP1 has to be applied both where you develop application and on the
server.
> Have you applied SP1 at both places?
> Give a little more info on your configuration.
> Bruce L-C
> "Gary Gordon" <sbss1@.icdc.com> wrote in message
> news:10e871srs5s3762@.corp.supernews.com...
> > We applied SP1 and now are getting the following error:
> >
> > The version of the report server database is either in a format that is
> not
> > valid, or it cannot be read. The found version is 'C.0.6.51'. The
expected
> > version is 'C.0.6.43'. To continue, update the version of the report
> server
> > database and verify access rights. (rsInvalidReportServerDatabase)
> >
> > Is there a remedy for this?
> >
> >
>

Monday, March 12, 2012

Invalid column name 'rowguid'

I'm trying to set up a merge publication in MSSQL 2005 SP1.

I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'

Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.

The replication monitor details the following error:

Error messages:

· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @.publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

· Server [XXXX\XXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

Can anyone shed further light on this?

Thanks.

Further note: I downloaded and applied MSSQL 2005 SP2 just now and started process from scratch (i.e. restored clean database with no replication, then created publication successfully, then ran snapshot agent). Exactly same error was encountered.

Further details that might be relevant:

Publisher and Distributor are set to same. Error persists when compatibility is 2005 or 2000.

Unfortunately the error report doesn't indicate which article was being processed when the error occurred, and the list of successfully processed articles in the log doesn't seem to follow any discernible order.

Any suggestions?

|||

Hi Ramaj;

What are the values for @.keep_partition_changes and @.use_partition_groups parameters in your replication creation script.

|||

Hi Hakan,

@.keep_partition_changes = N'true'

@.use_partition_groups not specified

Thanks

|||

Hi Ramaj;

Could you please try to set @.keep_partition_changes = N'false' then retry to run snapshot.

If the same error occours please try again by adding @.use_partition_groups= N'true' and @.keep_partition_changes = N'false' .

|||

Thanks Hakan, the first step by itself (i.e. @.keep_partition_changes = N'false') did the trick and the snapshot completed successfully. What a strange and misleading error message though - completely had me barking up the wrong tree.

Your help was much appreciated!

|||

I would still be ineterested in finding out what is causing the problem.

Can you run snapshot with higher outputverboseLevel 2. That should tell you which article you have issues with. Then isolating that article in a sepearate publication would help. Also the article schema, publication and article script would really help us troubleshoot the problem.

|||

It's very easy to repro this issue.

Create a one table db with two cols one for filtering.

Below script creates db, table and replication. But the key is to setting keep_partiton_changes = true.

-- CREATE DB
USE [master]
GO
/****** Object: Database [REPLTEST] Script Date: 02/26/2007 10:15:31 ******/
CREATE DATABASE [REPLTEST] ON PRIMARY
( NAME = N'REPLTEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'REPLTEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST_log.ldf' , SIZE = 3840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Turkish_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'REPLTEST', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [REPLTEST].[dbo].[sp_fulltext_database] @.action = 'disable'
end
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULLS OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_PADDING OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [REPLTEST] SET ARITHABORT OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [REPLTEST] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [REPLTEST] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [REPLTEST] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [REPLTEST] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [REPLTEST] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [REPLTEST] SET ENABLE_BROKER
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [REPLTEST] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [REPLTEST] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [REPLTEST] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [REPLTEST] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [REPLTEST] SET READ_WRITE
GO
ALTER DATABASE [REPLTEST] SET RECOVERY SIMPLE
GO
ALTER DATABASE [REPLTEST] SET MULTI_USER
GO
ALTER DATABASE [REPLTEST] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [REPLTEST] SET DB_CHAINING OFF
GO

-CREATE TABLE
USE REPLTEST
CREATE TABLE [dbo].[TBLTEST](
Angel [nvarchar](50) COLLATE Turkish_CI_AS NULL,
[UID] [nvarchar](10) COLLATE Turkish_CI_AS NULL,
) ON [PRIMARY]

CREATE REPL
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'REPLTEST', @.optname = N'merge publish', @.value = N'true'
GO

-- Adding the merge publication
use [REPLTEST]
exec sp_addmergepublication @.publication = N'REPLTEST', @.description = N'Merge publication of REPLTEST database from Publisher CCIMOBILE.', @.sync_mode = N'character', @.retention = 14, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.dynamic_filters = N'true', @.conflict_retention = 14, @.keep_partition_changes = N'true', @.allow_synctoalternate = N'false', @.validate_subscriber_info = N'HOST_NAME()', @.max_concurrent_merge = 10, @.max_concurrent_dynamic_snapshots = 0, @.use_partition_groups = N'false', @.publication_compatibility_level = N'90RTM', @.replicate_ddl = 1, @.allow_subscriber_initiated_snapshot = N'true', @.allow_web_synchronization = N'false', @.allow_partition_realignment = N'true', @.retention_period_unit = N'days', @.conflict_logging = N'publisher', @.automatic_reinitialization_policy = 0
GO

exec sp_addpublication_snapshot @.publication = N'REPLTEST', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005SQLAgentUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005MSSQLUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'distributor_admin'
GO

-- Adding the merge articles
use [REPLTEST]
exec sp_addmergearticle @.publication = N'REPLTEST', @.article = N'TBLTEST', @.source_owner = N'dbo', @.source_object = N'TBLTEST', @.type = N'table', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x000000003000CFF1, @.identityrangemanagementoption = N'none', @.destination_owner = N'dbo', @.force_reinit_subscription = 1, @.column_tracking = N'true', @.subset_filterclause = N'UID=HOST_NAME()', @.vertical_partition = N'false', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.subscriber_upload_options = 2, @.delete_tracking = N'true', @.compensate_for_errors = N'false', @.stream_blob_columns = N'false', @.partition_options = 0
GO

use [REPLTEST]
exec sp_changemergepublication N'REPLTEST', N'status', N'active'
GO

|||

Here is the verbose log of the snapshot agent:

2007-02-26 08:38:58.98
2007-02-26 08:38:58.98 Microsoft (R) SQL Server Snapshot Agent
2007-02-26 08:38:58.98 [Assembly Version = 9.0.242.0, File Version = 9.00.2047.00]
2007-02-26 08:38:58.98 Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
2007-02-26 08:38:58.98 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-02-26 08:38:58.98 User-specified agent parameter values:
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 -Publisher HQMRKT\SQL2005
2007-02-26 08:38:58.98 -PublisherDB REPLTEST
2007-02-26 08:38:58.98 -Publication REPLTEST
2007-02-26 08:38:58.98 -ReplicationType 2
2007-02-26 08:38:58.98 -Distributor HQMRKT\SQL2005
2007-02-26 08:38:58.98 -DistributorSecurityMode 1
2007-02-26 08:38:58.98 -Output C:\ReplOutput.txt
2007-02-26 08:38:58.98 -OutputVerboseLevel 2
2007-02-26 08:38:58.98 -XJOBID 0xEC583CE49E702944B367AF1E70053178
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 Connecting to Distributor 'HQMRKT\SQL2005'
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: master
2007-02-26 08:38:59.20 Command Text: sp_helpdistpublisher
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.20 @.publisher = HQMRKT\SQL2005
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: distribution
2007-02-26 08:38:59.20 Command Text: select 'id' = convert(int, srvid) from master..sysservers where upper(srvname) = upper(N'HQMRKT\SQL2005')
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agentid
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.publisher_id = 0
2007-02-26 08:38:59.21 @.publisher_db = REPLTEST
2007-02-26 08:38:59.21 @.publication = REPLTEST
2007-02-26 08:38:59.21 @.job_id = System.Byte[]
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agent
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.agent_id = 1244
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 SQL Command dump
2007-02-26 08:38:59.23 ================
2007-02-26 08:38:59.23 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.23 Database: distribution
2007-02-26 08:38:59.23 Command Text: sp_MShelp_profile
2007-02-26 08:38:59.23 Parameters:
2007-02-26 08:38:59.23 @.agent_id = 1244
2007-02-26 08:38:59.23 @.agent_type = 1
2007-02-26 08:38:59.23 @.profile_name =
2007-02-26 08:38:59.23 Parameter values obtained from agent profile:
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 -BcpBatchSize 100000
2007-02-26 08:38:59.23 -HistoryVerboseLevel 2
2007-02-26 08:38:59.23 -LoginTimeout 15
2007-02-26 08:38:59.23 -QueryTimeout 1800
2007-02-26 08:38:59.23
2007-02-26 08:38:59.26 Connecting to Publisher 'HQMRKT\SQL2005'
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use master
2007-02-26 08:38:59.29 select isnull(convert(int, value_in_use), 0) from master.sys.configurations where lower(name) = 'smo and dmo xps'
2007-02-26 08:38:59.29 use [REPLTEST]
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use [REPLTEST]
2007-02-26 08:38:59.29 select publishingservername(), convert(int, compatibility_level) from sys.databases where name = @.database_name
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29 @.database_name = REPLTEST
2007-02-26 08:38:59.31 Publisher database compatibility level is set to 90.
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: select convert(int, isnull(is_member('db_owner'),0))
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text:
2007-02-26 08:38:59.31 declare @.retcode int
2007-02-26 08:38:59.31 exec @.retcode = sys.sp_getapplock @.Resource = N'HQMRKT\SQL2005-REPLTEST-REPLTEST-1244',
2007-02-26 08:38:59.31 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.31 @.LockOwner = N'Session',
2007-02-26 08:38:59.31 @.LockTimeout = 0,
2007-02-26 08:38:59.31 @.DbPrincipal = N'db_owner'
2007-02-26 08:38:59.31 if @.retcode < 0 raiserror(21036, 16, -1, 'snapshot')
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 Retrieving publication and article information from the publisher database 'HQMRKT\SQL2005.REPLTEST'
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: sp_helpmergepublication
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 @.publication = REPLTEST
2007-02-26 08:38:59.31 @.reserved = fromSnapshot
2007-02-26 08:38:59.32
2007-02-26 08:38:59.32 SQL Command dump
2007-02-26 08:38:59.32 ================
2007-02-26 08:38:59.32 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.32 Database: REPLTEST
2007-02-26 08:38:59.32 Command Text: sys.sp_MSset_dynamic_filter_options
2007-02-26 08:38:59.32 Parameters:
2007-02-26 08:38:59.32 @.publication = REPLTEST
2007-02-26 08:38:59.32 @.dynamic_filters = True
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: declare @.ftp_password nvarchar(524) select @.ftp_password = ftp_password from dbo.sysmergepublications where name = N'REPLTEST' and publisher_db = N'REPLTEST' and upper(publisher) = upper(N'HQMRKT\SQL2005') if object_id('sys.fn_repldecryptver4', 'FN') is not null begin select sys.fn_repldecryptver4(@.ftp_password) end else begin select @.ftp_password end
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: sp_MSestimatemergesnapshotworkload
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39 @.publication = REPLTEST
2007-02-26 08:38:59.40
2007-02-26 08:38:59.40 SQL Command dump
2007-02-26 08:38:59.40 ================
2007-02-26 08:38:59.40 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.40 Database: REPLTEST
2007-02-26 08:38:59.40 Command Text: sp_helpmergearticle
2007-02-26 08:38:59.40 Parameters:
2007-02-26 08:38:59.40 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sys.sp_MSget_load_hint
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.qualified_source_object_name = [dbo].[TBLTEST]
2007-02-26 08:38:59.42 @.qualified_sync_object_name = [dbo].[MSmerge_REPLTEST_TBLTEST_VIEW]
2007-02-26 08:38:59.42 @.primary_key_only = False
2007-02-26 08:38:59.42 @.is_vertically_partitioned = False
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_MSvalidate_wellpartitioned_articles
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_browsemergesnapshotfolder
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sys.sp_MSgetmergeadminapplock
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.timeout = -1
2007-02-26 08:38:59.43 @.lockmode = Exclusive
2007-02-26 08:38:59.43 @.lock_acquired = -1
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sp_MSenumpubreferences
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text:
2007-02-26 08:38:59.43 declare @.retcode int,
2007-02-26 08:38:59.43 @.resource sysname,
2007-02-26 08:38:59.43 @.DbPrincipal sysname,
2007-02-26 08:38:59.43 @.pubid uniqueidentifier
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select @.resource = N'dynpub_' + convert(nvarchar(36), pubid), @.pubid = pubid
2007-02-26 08:38:59.43 from dbo.sysmergepublications
2007-02-26 08:38:59.43 where name = N'REPLTEST'
2007-02-26 08:38:59.43 and upper(publisher) = upper(publishingservername())
2007-02-26 08:38:59.43 and publisher_db = db_name()
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute sys.sp_MSrepl_FixPALRole @.pubid = @.pubid, @.role = @.DbPrincipal output
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute @.retcode = sys.sp_getapplock @.Resource = @.resource,
2007-02-26 08:38:59.43 @.LockOwner = N'Session',
2007-02-26 08:38:59.43 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.43 @.LockTimeout = 0,
2007-02-26 08:38:59.43 @.DbPrincipal = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select 'LockOutcome' = @.retcode, 'LockName' = @.resource, 'DbPrincipal' = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.45 [0%] Preparing table '[dbo].[TBLTEST]' for merge replication
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SELECT * FROM [dbo].[TBLTEST] WITH (TABLOCK HOLDLOCK) WHERE 1 = 2
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: sp_MSprepare_mergearticle
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45 @.source_owner = dbo
2007-02-26 08:38:59.45 @.source_table = TBLTEST
2007-02-26 08:38:59.45 @.publication = REPLTEST
2007-02-26 08:38:59.45 @.qualified_tablename = [dbo].[TBLTEST]
2007-02-26 08:38:59.46
2007-02-26 08:38:59.46 SQL Command dump
2007-02-26 08:38:59.46 ================
2007-02-26 08:38:59.46 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.46 Database: REPLTEST
2007-02-26 08:38:59.46 Command Text: COMMIT TRANSACTION
2007-02-26 08:38:59.46 Parameters:
2007-02-26 08:38:59.53
2007-02-26 08:38:59.53 SQL Command dump
2007-02-26 08:38:59.53 ================
2007-02-26 08:38:59.53 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.53 Database: REPLTEST
2007-02-26 08:38:59.53 Command Text: sp_MSpublicationview
2007-02-26 08:38:59.53 Parameters:
2007-02-26 08:38:59.53 @.publication = REPLTEST
2007-02-26 08:38:59.70
2007-02-26 08:38:59.70 SQL Command dump
2007-02-26 08:38:59.70 ================
2007-02-26 08:38:59.70 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.70 Database: REPLTEST
2007-02-26 08:38:59.70 Command Text: sys.sp_releaseapplock
2007-02-26 08:38:59.70 Parameters:
2007-02-26 08:38:59.70 @.Resource = HQMRKT\SQL2005-REPLTEST-REPLTEST-1244
2007-02-26 08:38:59.70 @.LockOwner = Session
2007-02-26 08:38:59.70 @.DbPrincipal = db_owner
2007-02-26 08:38:59.71 [3%] The replication agent had encountered an exception.
2007-02-26 08:38:59.71 Source: Replication
2007-02-26 08:38:59.71 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-02-26 08:38:59.71 Exception Message: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 Message Code: 4615
2007-02-26 08:38:59.71
2007-02-26 08:38:59.71 Call Stack:
2007-02-26 08:38:59.71 Microsoft.SqlServer.Replication.ReplicationAgentSqlException: Invalid column name 'rowguid'. > System.Data.SqlClient.SqlException: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 End of inner exception stack trace
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.Run()

Friday, March 9, 2012

Invalid column name 'rowguid'

I'm trying to set up a merge publication in MSSQL 2005 SP1.

Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.

The replication monitor details the following error:

Error messages:

· Server [XXXX\XXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

Can anyone shed further light on this?

Thanks.

Further details that might be relevant:

Publisher and Distributor are set to same. Error persists when compatibility is 2005 or 2000.

Any suggestions?

|||

Hi Ramaj;

What are the values for @.keep_partition_changes and @.use_partition_groups parameters in your replication creation script.

|||

Hi Hakan,

@.keep_partition_changes = N'true'

@.use_partition_groups not specified

Thanks

|||

Hi Ramaj;

Could you please try to set @.keep_partition_changes = N'false' then retry to run snapshot.

If the same error occours please try again by adding @.use_partition_groups= N'true' and @.keep_partition_changes = N'false' .

|||

Your help was much appreciated!

|||

I would still be ineterested in finding out what is causing the problem.

|||

It's very easy to repro this issue.

Create a one table db with two cols one for filtering.

Below script creates db, table and replication. But the key is to setting keep_partiton_changes = true.

-CREATE TABLE
USE REPLTEST
CREATE TABLE [dbo].[TBLTEST](
Angel [nvarchar](50) COLLATE Turkish_CI_AS NULL,
[UID] [nvarchar](10) COLLATE Turkish_CI_AS NULL,
) ON [PRIMARY]

CREATE REPL
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'REPLTEST', @.optname = N'merge publish', @.value = N'true'
GO

use [REPLTEST]
exec sp_changemergepublication N'REPLTEST', N'status', N'active'
GO

|||

Here is the verbose log of the snapshot agent:

Monday, March 26, 2012

Invalid object name...

Monday, March 19, 2012

Invalid Database after SP1 Install

Monday, March 12, 2012

Invalid column name 'rowguid'

Friday, March 9, 2012

Invalid column name 'rowguid'

Internet integration

Blog Archive

About Me