After using the wizard to create a very simple snapshot. I keep receiving this message:
Replication-Replication Snapshot Subsystem: agent D700PT106-gdalcd_10-test-3 failed. The parameter is incorrect. The step failed.
Anybody have any idea what this means.
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View Related I am getting an error the parameter is incorrect when using a parameter within a case statement. Here is my SQL:
SELECT J.Commitment, J.Vendor, A.Name, J.Category, J.Transaction_Type, J.Job, CASE WHEN J.Transaction_Type = 'AP cost' AND
J.Accounting_Date < @MonthEndDate THEN J.Amount END AS InvoicedtoDate, C.Date, C.Delivery_Date
FROM JCT_CURRENT__TRANSACTION AS J INNER JOIN
JCM_MASTER__COMMITMENT AS C ON J.DBID = C.DBID AND J.Commitment = C.Commitment LEFT OUTER JOIN
APM_MASTER__VENDOR AS A ON J.DBID = A.DBID AND J.Vendor = A.Vendor
WHERE (J.Commitment <> ' ') AND (J.Job <> ' ')
ORDER BY J.Job, J.Commitment
If I replace @MonthEndDate with a date value it will run but as soon as I run the SQL with the parameter it gives me the error the parameter is incorrect. @MonthEndDate is data type datetime.
Thanks for your help.
I have a report with a parameter that I would like to have created every night but also allow a user to change the default parameter and rerun it if they need to . When the Snapshot is opened, the parameter box is disabled. How can I work around this?
TIA
Dean
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu
Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.
View 1 Replies View RelatedHi,
I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.
Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.
Thanks,
Prashant
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
------------------------------
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
I am looking forward at performing a SNAPSHOT REPLICATION between a 2000-Publisher and a 2005-Subscriber. But on following the wizard the latter is unable to recognize the publisher.
Could anyone help to let me know the possible reason or if there is some setting which i may have missed out?
Thanks,
Varun
Hi,
I'm having a problem with my SQL 7.0 (SP2) server running on NT4 (SP6a).
When I use Enterprise Manager on my NT4 workstation (SP6) and I try to double click a local DTS package to go into the design view I get the following error message:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: The parameter is incorrect
If I log on to the actual server and run Enteprise Manager everything is o.k
It is not a permissioning problem as I'm logging in with domain Admin in both cases. Any ideas ?
Regards,
Tim
Hi,
I have looked through the forum but not found the solution.
I have a OLE DB source connected to the SCD component. I have tried to change the Data Access mode from reading the table and also a 'select * from table' SQL command in the OLE DB source editor, but neither worked.
The SCD component fails with :-
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80070057 Description: "The parameter is incorrect.".
Error: 0xC0047022 at Extract revenue codes, DTS.Pipeline: The ProcessInput method on component "Slowly Changing Dimension" (12964) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
Has anyone else had a similar situation?
The SQL server verison is 9.0.2047.
I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.
I'm trying to dynamically set a Data Source for an Excel connected, using a query.
Query:
DECLARE @directory VARCHAR(1000)
SET @directory = 'C:'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @directory
The Store Procedure:
CREATE PROCEDURE spr_GetNewestExcel
(
@directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @cmdParam VARCHAR(8000)
--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)
--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)
--Sets the command parameter
SET @cmdParam = 'dir "' + @directory + '"'
--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @cmdParam
--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'
--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@directory, LEN(@directory), 1) = '' THEN @directory + fileName
ELSE @directory + '' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC
DROP TABLE #output
DROP TABLE #files
END
The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.
When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".
Any help would be much appreciated, this has had me stumped for quite some time.
peace,
CR
Hi Everybody:
We encounter a difficult problem in our production environment.
I have a merge replicaiton which contains 10 tables from Server s1 and database d1 to Server s2 and database d2. Among those 10 tables 5 of them are true merge tables ( carry data both ways, from publisher to subscriber and from subscriber to publisher). Other 5 tables I would like them to carry data only one way from publisher to subscriber ( never merge data from subscriber). Anyone has any suggestions how I impliment this?
Thanks in advance.
Joan
I get the following error:
Another snapshot is running for the publication.
I just upgraded to Service pack2 and can not get the replication working. I have not clue what is wrong.
Thanks in Advance,
Phillip M. Tricoli
Hello,
We are using Snapshot replication to move data for our Data Warehouse from a server which performs the loading to the production reporting server. We are currently using Sql Server 2005 in both environments. The loading server is setup as the Distributor and Publisher and the production reporting server is setup as the subscriber. However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load. I have listed the below commands that the sql agent is running for replication in the order in which they are ran.
1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1
2)-Subscriber [SRVREPORTER] -SubscriberDB [PRESENTATION] -Publisher [SRVLOADER] -Distributor [SRVLOADER] -DistributorSecurityMode 1 -Publication [SRVREPORTER] -PublisherDB [PRESENTATION]
In our own job we run each of these jobs using the EXEC SP_START_JOB() procedure. Each job listed above runs under their own PROXY account.
Finally, we are experiencing an intermittent problem where the Agent will report it is unable to Copy data into "X" table, with "X" being any one of the tables in the reporting database. This has only ever occurred during the automation process and not from manually running each agent job. This problem also might not occur for a couple of days and then might occur 2 or 3 days in a row, which has led us to question the dependability of this approach.
In closing I would like to ask if there is a problem with the way we have set it up? Something we could do to improve the process so it becomes more reliable? Any advice would be greatly appreciated.
Is there anyway to prevent deadlocks during the snapshot replication?
I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?
Thank you
I configured the transactional replication using push method. I use the option generate snapshot. Until the Snapshot complete, I didn't any tables in the subscriber db.After that also I can see in some tables it has indexes and some tables it doesn't but it has data in that table .
How does the bulk copy works from snapshot?i.e. is it first create the table with columns then insert data later it will create the indexes...How the sequence works in the subscriber.
Hello,
Harry Half wit here!!
I know that snapshot replication is the simplest form of syncing with SQL server and I can't even figure that out today!!.
I keep getting myself confused as to who should be configured to be a publisher, distibutor or subscriber etc etc.
My scenario is simple:
1 server creating a daily snapshot of a table and then 1 remote laptop (msde) pulls the snapshot into it's own database.
Heres what i did so far;
I configured the server to be a publisher and distributor (is that right?) and didn't set up any subscribers because i want to do that from my remote.
From the remote I did nothing but go into EM tools"create new pull subscription" but I cannot see the publication on the server.
SHould I set my remote to be a distributor to do this?
any help very much appreciated!!
Snapshot replication.
View synchronization status.
Last status message:
"The job failed. The Job was invoked by User distributor_admin. The last step to run was step 3 (Detect nonlogged agent shutdown.)."
What should I do to make the synchronization work?
I have two clusters and one publishes snapshots and the other cluster subscribes. I am planning an SP2 installation but I see in the SP2 readme that there are only special considerations for merge and transaction replication.
Will I need to patch in a certain order and will it matter if I patch the publisher who is also the distributor and then a week later patch the subscriber?
Thanks
When configuring snapshot replication of the OnePoint database used by MOM i am having the following error being reported in SQL Replication Monitor:
Command attempted:
CREATE TABLE "dbo"."Computer"(
"idComputer" "uniqueidentifier" ROWGUIDCOL NOT NULL,
"Name" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
"Description" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData1" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData2" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"Domain" "nvarchar"(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"DNSName" "nvarchar"(512) COLLATE SQL_Latin1_General_CP1
(Transaction sequence number: 0x000004670002E32300BF00000002, Command ID: 375)
Error messages:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Unfortunately I can't find much help with this error.
Any suggestions?
What I'm trying to do is to create a snapshot replication where it is pushed from the publication server. I have it kinda working but my problem is that the subscription server is not grabbing the tables its asking for. It only grabs some replication tables.
I have noticed two things.
First: When I go to Job syncrinization progress it states that its still in progress even though the replicated database is Very small.
Second: In replication Monitor under the subscription watch list tab it states that the subscription has never been initialized.
Thanks,
Kraig
We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
When I try to drop the publication in this order:
exec sp_dropsubscription @publication = @publicName, @subscriber = @servName, @article = N'all'
exec sp_droppublication @publication = @publicName
I am getting the following error after dropping the publication
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Could anyone give me a suggestion about how to avoid this error?
Thanks!
hi all,
I grouped my tables into two publications
To each publication i added my "datamart1" server as a subcriber
using the same database as a destination
I dont have problem when i create snapshot since each of the publication
generates its own snapshot.
The only problem i encounter is that. when i begin to sychronize any one of the two
subscription The two subscription gets synchronized.
I need to isolate each publication's syncronization to know exactly how much time would each publication take to get synchronized in the datamart
thanks,
joey
Hi,
I have got a snapshot replication and have got distributor, publisher and subscriber on different servers all having sql server 2005.
I am looking for a piece of code that i can execute in order to initialize snapshot with new data and push data to subscriber on demand.
I assume there are TSQL calling replication sproc but i m not sure which proc should be used. any help would be highly appriciated.
Thanks,
Furrukh Baig
Hi
Help with syntax, I get the error in the line: myDA.Fill(ds, "t1")
Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String)
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myDA As New SqlClient.SqlDataAdapter _
("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
' Add an input parameter and supply a value for it
myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4)
myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId
Dim ds As New DataSet
Dim pageds As New PagedDataSource
myDA.Fill(ds, "t1")
pageds.DataSource = ds.Tables("t1").DefaultView
pageds.AllowPaging = True
pageds.PageSize = 4
Dim curpage As Integer
If Not IsNothing(Request.QueryString("Page")) Then
curpage = Convert.ToInt32(Request.QueryString("Page"))
Else
curpage = 1
End If
pageds.CurrentPageIndex = curpage - 1
lblCurrpage.Text = "Page: " + curpage.ToString()
If Not pageds.IsFirstPage Then
lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage - 1)
End If
If Not pageds.IsLastPage Then
lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage + 1)
End If
list.DataSource = pageds
list.DataBind()
End Function
Best Regards
Primillo
Hi all Thanks in advance ..
I have some DTS Shedules on SQL 7.0 SP1 . All shcedule was running properly . When I made some changes in an SQL Task and saved it the shedule starts to fail. It gives me an error "DTS RUN LOAding Error- 2147024809(80070057)Ptovider Error (0) Error String : The parameter is incorrect etc)
But All my DTS is running smoothly without error Only the schedule fails.
Please help me.
I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','
View 1 Replies View Related