Trying To Use SQL_DMO To Output Replication Scripts Like EM Does!

Dec 20, 2006

I have been attempting to use SQL-DMO to create replication scripts like Enterprise Manager allows you to manually create. I have been unable to match the EM-generated code. The EM code includes "sp_addsubscriber" statements and mine does not! Either SQL-DMO can not do this or I have set the SQLDMORepScript value incorrectly (I have tried all combinations I can think of!) If anyone has done this I would like to hear from them. Please don't guess!

Code snippet:

Set oReplicationDatabases = srv.Replication.ReplicationDatabases
SQLDMORepScript = 16777216 + 1024 + 1048576 + 65536 + 67108864
res = oReplicationDatabases.Script(SQLDMORepScript, "c:projectRepScript.sql")

The output file contains the same content as EM less the "sp_addsubscriber" statements.

Thanks!

View 1 Replies

SQL_DMO Sessions

Jan 12, 2000

Hi all!
First of all, thanks to those who gave me suggestions to use sql-dmo
to generate sql scripts of a database.
I've found it extremely useful!

Here's my new problem.
I have VB code that connects to a database, extracts the schema
and generates the sql.
After some work with this script, I have some new scripts to alter the database and I have tried running these scripts in a separate project
with the ExecuteImmediate method of the Database object, which works well.

However, when I put this code into the first project, to reconnect to the database and execute the batch command,
I get SQL-DMO error -2147199229 (80045203)
...whatever that means..

Can anyone help me?
Thanks

View 1 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

PrimeOutput : Difference Between 'Output' And 'output Buffer'

Aug 12, 2005

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

View 5 Replies View Related

SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.

Jun 15, 2007

I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.

View 3 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

Jan 17, 2002

Hi,

In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don

View 1 Replies View Related

DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication

Aug 22, 2007

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

View 4 Replies View Related

Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.

Mar 6, 2007

Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm

View 1 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

SQL Express Replication And Problems Accessing Data After Replication

Jul 28, 2006

Hi,

I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows

- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem

- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine

however.......

- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error

- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.

I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.

I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc

Any advice appreciated !

Thanks
Ronan

View 2 Replies View Related

Does Replication Affect Tempdb GROWTH Replication

Jun 28, 2007

Hi all,

I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????

Thanks in advance

Jacx

View 3 Replies View Related

How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?

Aug 30, 2007

Hi all,

I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.

I am using SQLSERVER 2005 (SP1).

View 3 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),

SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

DB Replication Or Table Replication Via Triggers?

Apr 17, 2007

Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony

View 11 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

Publisher Is Empty Under Replication Monitor - Publisher.! But Replication Is Still Running Fine..

Nov 9, 2006

I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..

View 2 Replies View Related

Red X Show On Replication Monitor (publications Is Disconnected From Replication Monitor)

Jan 30, 2007

I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.

Everyone known how to fix this problem?

Thanks,

Frank

View 1 Replies View Related

Replication Without Replication? Thoughts Welcome

Apr 24, 2007

Hi,

I have a particularly difficult problem.

I have a SQL Server in an internal network and need to "replicate" to an identical server and database in a DMZ. The DMZ can only receive files sent by a custom component and no port(s) can be opened in the firewall or standard FTP used.

I also need to minimise traffic (i.e. sending whole tables is not an issue as some contain millions of rows), replicate approx once hourly and allow record locking only (as opposed to DB locking/exclsuive access or table locking).

Any thoughts / experiences greatly appreciated otherwise I'm looking at putting triggers on all the tables to monitor changes and generate SQL statements for execution against the DMZ server.

View 4 Replies View Related

SQL OUTPUT

Sep 14, 2007

Hi there,Is it possible for me to do an insert
INSERT (some values) OUTPUT INSERTED.ID AS @ID
Grab ID in my code behind and create a session with it so i can redirect to another page and use the session to access the same record?
Or maybe
INSERT (some values)
SELECT @@IDENTITY
then grab that in my code behind and create a session?
I have been googling for a couple of hours and can't find a solution that i can follow or anyone saying you cant do it.
Any help much appreciated.
Thanks

View 1 Replies View Related

XML Output.

Mar 10, 2006

OK, I'm starting up a project that requires an XML output format, which I haven't played around with before. My plan is to load the data into a schema which matches the XML format, and the run my queries with the FOR XML AUTO, ELEMENTS options.

So first, any comments on the plan of attack?

Second, how do I get the XML output in a clean enough format to read in, say, either Visual Studio or even Internet Explorer? The output comes out as a solid block, with no identing, which I guess the display utilities can deal with, but it also contains carriage returns and an odd header at the top (XML_F52E2B61-18A1-11d1-B105-00805F49916B), and the ubiquitous separation dashes (-------------------------------), and I'm having to manually fix these issues before I can display the XML in any reasonable format.

Any tips/tricks on dealing with XML are appreciated.

View 3 Replies View Related

Get Output From One SP In To Another SP

Oct 11, 2007

Hi I have an SP Omg�ngsVal
ALTER PROCEDURE Omg�ngsVal
@SexVal nvarchar,
@Sasong int

AS
BEGIN

SET NOCOUNT ON;

SELECT MAX(Omgang) AS [Omg�ng]
FROM Resultat
WHERE @SexVal = Lag And @Sasong = S�song
END

And want [Omg�ng] as input in next SP

DECLARE
@SexVal nvarchar,
@Omg�ng INT,
@Sasong int
SET @SexVal='A'
SET @Sasong=20072008
EXEC Ubc90Omg�ngsVal @SexVal,@Sasong

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omg�ng = @Omg�ng

But i get error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@Omg�ng'.

B Regards
Gert

View 11 Replies View Related

DTS To Output .csv

May 20, 2004

I am selecting the following fields from my table and drop them into my .csv, the problem is that if i select all the fields which I commented out, and click on define columns, populate from source, execute, then on the Destination Tab, none of my columns are selected, it is blank, please help, here is my table

select
trad_type,
reference,
principal,
book,
strategy,
cpty,
buy_sell,
Quantity,
ident_type,
ext_ident,
sec_name,
price,
price_divisor,
traded_net_ind,
trade_ccy,
trade_ldt,
value_date,
commission,
exchange_fee,
other_fees
gross_consid,
net_consid,
sett_ccy,
trad_sett_ccy_xrate,
/*trad_sett_ccy_xrate_mdv_ind
trad_inst_ccy_xrate
trad_inst_ccy_xrate_mdv_ind
*/
pb,
acct,
inst_class
/*cont_desc
pl_book_ccy_xrate
Id*/
from Table1

View 2 Replies View Related

How To Get This Output?

Apr 25, 2008

i hav a table lik this

bank amount status
----------------------------
hdfc 1000 credit
icici 2000 credit
hdfc 500 debit
icici 1000 debit

i need to get the output like diz

bank credit debit
--------------------------------
hdfc 1000 500
icici 2000 1000

how can i get this output????

Thanks

View 4 Replies View Related

Output

Nov 15, 2007

ho to make this output in one line of this

set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbs01.txt;

select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name,
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbs_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbs_fahmi.service_num = tel_profile.service_num and
temp_ogbs_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);

set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbz02.txt;

select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name||'|'||
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbz_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbz_fahmi.service_num = tel_profile.service_num and
temp_ogbz_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);

exit;

output

XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX

slipzst@yahoo.com

View 5 Replies View Related

Different Output

Nov 20, 2007

hi all,
i've inserted one row like this

insert into e1 select 'phani',18000,'15-oct-2007'

i got the out put like this

phani180002007-10-15 00:00:00.000

how can i get the same output as i've given?

thankyou very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 2 Replies View Related

Output To Log

Jan 25, 2008

I have a simple process that goes through a directory and deletes files older than some number of days. I determine the full filename in a script task by setting a variable and then I use File System Task to do the delete. My goal is to output the file name to a log file. Nothing fancy, just output text (file name) to the default log provider so it can be logged if logging in enabled.

If anyone has any clues on how to do this that'd be great. If you need more information, let me know.

Cheers!

View 2 Replies View Related

XML Output

Jul 20, 2005

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru anormal Select statement, depending upon the input parameter specified(@xmflag). The default of @xmflag = 0 which means non-XML output. NowProc1 is calling Proc2 and inserting the output of Proc2 in a temptable without specifing the @xmflag parameter. The message coming is"The FOR XML clause is not allowed in a INSERT statement". I don'tunderstand why this msg is coming when the Select statement FOR XML isnot executing.Anybody knows the reason?Thanks in advance.Subodh

View 4 Replies View Related

OUTPUT - Help With Please

Aug 29, 2007

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @SQL @Params

@SQL is the Proc Name (varchar) and @Params is the parameter string (nvarchar).

If I include the @Fatal_Error variable in the dynamic creation of the @Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @SQL @Param_List = @Fatal_Error

AND I also tried:

EXEC @SQL @Param_List + ' '+@Fatal_Error+' '

View 7 Replies View Related