I'm new to Replication and trying to understand some basic concepts. What I really want to do is replicate a View such that it shows as a table in the subscriber database, but it appears as though I cannot do that. First, is that correct? Second, since it appears as though Stored Procedures can be replicated, I was wondering if I could accomplish the same thing via that route. However, I am unclear: does the SP itself replicate? Does replication execute the SP? If the SP simply does a query, are the query results replicated?
I know that I can replicate subsets of tables; limiting rows and columns. This is part of what I wanted the view to do, however I also wanted to be able to include joins.
Thanks for any help anyone can provide this newbie!
Can you do it? I have some transformation and business logic I need to place in the transactional replication process.
Just curious if its possible.
BTW, I know I should not be doing transformation during transactional replication. At the moment the client wants realtime updates to a central repository thought transactional replication.
Hello all. I have a question, but first the setting.
I have one Publisher pushing out to 4 subscriptions, all are SQL 2000 Servers. The tables all replicate like they are supposed to. My issue is that if I make changes to a stored procedure, I have to physically go to each subscription and make the same changes. I included the stored procedures when I set up the publication.
Now the question, is there an easier way? I want to be able to make the changes on the publisher and the changes to replicate to the other servers.
I have a system with three replications: merge replication between SERVER and CLIENT transactional replication from SERVER to CLIENT transactional replication from CLIENT to SERVER
SERVER is always distributor.
Everything has been running fine for over a week now, but today something went wrong with the second transactional replication (from CLIENT to SERVER). SERVER is distributor and CLIENT is publisher. The subscription is push.
When I view the synchronization status, the following error message appears: "Could not find stored procedure 'sp_MSins_dbosessions'."
Indeed, when I look at the stored procedure, it has been renamed to 'sp_MSins_dbosessions_msrepl_ccs'.
I don't know why, when or how this renaming happened, but it happened for every stored procedure that is used for this transactional replication (the other replications are still working fine)
I am new to replication but I have a question, I have my server which is using a transaction replication process and a client with using SQL Express with replication turned on. I have the identical DB on both nodes yet when I subsrcibe to the server from the client, data is transfer but for some reason, all of my stored procedures are renamed with sp_MSDel_DBOxxxx. How do I keep SQL from renaming these SP's? The server maintains the origial names to the SPs its just happening on my client.
Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property. If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki
I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.
I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
Hello I have two stored procedures @ID INT AS SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER FROM (SELECT (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM, (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X and @ID INT AS SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL( (SELECT SUM(TRANSACTION_VALUE) FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1 GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player Thanks in advance for any light you can shine on this.
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource? And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"? Now I have around 40 stored procedures, and quite many of them looks the same except the where-part... (Iam just a beginner with SQL)
Hello every one, I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you Thank you
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
cREATE PROCEDURE emp @val varchar(50)AS declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null' execute(@test) GO this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
