SQL Server Publication of snapshot takes too long to add a new article
I am doing the SQL Server replication and I use T-SQL to do this.
Whenever I want to add a new article to the publication, I use below T-SQL
"exec sp_addarticle" with the respective list of parameters.
After adding a new article to the publication, I use,
exec sp_startpublication_snapshot @publication = 'publication_name'
This will generate the snapshot again and the newly added articles are added to subscribers accordingly.
However, this is a time consuming process. This database is used by an application which creates number of dynamic table in the database.
Dynamic tables are created by the code using the T-SQL queries and after creating a dynamic table above T-SQL queries to add that table to the publication will happen.
Is there a way to add an article to the publication without creating a new snapshot again and again for every article being added?
See also questions close to this topic
How to replace a BIT with a STRING value in a select statement in PostgreSQL
How can I replace a BIT value in the dataset returned by a SELECT statement with a STRING (of words, no numbers) in PostgreSQL? I'm very new to SQL in general, so my experience and googleing has not proved bountiful.
In this particular case, c.visible is a bit value storing either 0 (is visible) or 1 (not visible). In my query, I want to show all results where c.visible = 0, but display 'this course is visible to students.' instead of '0' in the dataset returned by this select statement. I've tried casting it as text in the SELECT statement, then changing the value in a CASE WHEN statement in the WHERE clause, but this results in an invalid input syntax error. c.visibility is the only attribute in the database that records this information.
-------------------------------------------------------- | CourseName | ID | CourseVisibility | -------------------------------------------------------- | ABC | 10 | This course is visible to students.| -------------------------------------------------------- | DEF | 22 | This course is visible to students.| --------------------------------------------------------
SELECT c.fullname AS CourseName, c.id AS ID, CAST(c.visible AS TEXT) AS CourseVisibilty FROM prefix_course c, prefix_logstore_standard_log lsl WHERE c.visible = 0 CASE WHEN c.visible = '0' THEN c.visible = 'This course is visible to students.' END AND lsl.courseid = c.id AND lsl.timecreated BETWEEN extract(epoch from NOW()- INTERVAL '12 month') AND extract(epoch from NOW()) GROUP BY c.fullname, c.id, c.visible
ERROR: invalid input syntax for integer: "This course is visible to students." LINE 5: THEN c.visible = 'This course is visible to students.' ^
What is the correct method to do this?
order group_concat values conditional aggregation
I have a following query:
select `product_sku`, `request_id`, `product_id`, group_concat(case when ai_result = "Match" then concat(r_image_url,"&_id=",hash_id) ORDER BY confidence_score DESC END) as matched, group_concat(case when ai_result = "Non Match" then concat(r_image_url,"&_id=",hash_id) ORDER BY confidence_score DESC END) as non_matched, group_concat(s_image_url) as s_images from `product_match_unmatches` group by `request_id`, `product_id` order by `confidence_score` desc
I need to sort
But this gives error:
MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY `confidence_score` DESC END) as matched, group_concat(case when ai_res' at line 3
postgresql - replacing the values in a column in select query
I have a table where one of the columns is ids as foreign key to another table. how can I replace the ids with data from the other table inside select query?
2 Tables - one customer, one transactions. How to handle a customer with no transaction?
I have 2 tables-one customers, one transactions. One customer does not have any transactions. How do I handle that? As I'm trying to join my tables, the customer with no transaction does not show up as shown in code below.
SELECT Orders.Customer_Id, Customers.AcctOpenDate, Customers.CustomerFirstName, Customers.CustomerLastName, Orders.TxnDate, Orders.Amount FROM Orders INNER JOIN Customers ON Orders.Customer_Id=Customers.Customer_Id;
I need to be able to account for the customer with no transaction such as querying for least transaction amount.
Access a SQL server database from a different computer which is connected to the same router via java?
I know I have to do the following code. But I don't quite understand what this URL is. I mean what should I use as my IP address, where do I get the IP address for the computer where the database is currently located in?
Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url,"root","1234");
How to SUM column 1 and select column 2 by condition?
I've stuck with how to sum column
Aand select column
Bwith a condition if column
B >= 50select this row id.
Example Table Like this
+----+-----------+---------+ | ID | PRICE | PERCENT | +----+-----------+---------+ | 1 | 5 | 5 | | 2 | 18 | 20 | | 3 | 7 | 50 | | 4 | 16 | 56 | | 5 | 50 | 87 | | 6 | 17 | 95 | | 7 | 40 | 107 | +----+-----------+---------+
SELECT ID, SUM(PRICE) AS PRICE, PERCENT FROM Table
PERCENT, I want to select from a row with
PERCENT >= 50
The result should be
Eror While Executing a dynamic queries in a variable
I'm executing a dynamic query from a variable but it always says:
Could not find stored procedure
I have tried to use simpler queries like
set @query = 'select * from [table_name]'but it gives the same error.
CREATE TABLE #test( id int identity (1,1) not null, name varchar(200) ) DECLARE @TRIGER_NAME VARCHAR(200) DECLARE @V_MAX INT DECLARE @V_MIN INT DECLARE @QUERY VARCHAR(MAX) SELECT @V_MAX = MAX(id) FROM #test SELECT @V_MIN=Min(id) FROM #test WHILE @V_MIN <= @V_MAX BEGIN SELECT @TRIGER_NAME = name FROM #test WHERE id = @V_MIN SET @QUERY = 'DROP TRIGGER '+ @TRIGER_NAME; EXEC @query SET @V_MIN = @V_MIN+1 END
In my temporary table is the list of all my trigger name so the expected output I want to get is Command(s) completed successfully. Not 'Could not find stored procedure', because I supposed to executing a dynamic queries not a store procedure.
Can SQL Server sysadmin create a rule in a DB that prevents users from executing certain types of queries?
I am the sysadmin on a small SQL Server. Can I store rules in the Databases that prevent certain types of SQL statements to be run? Example would be when a user tries to execute
SELECT * FROM ...Can I "ban" these types of solutions? Other examples I would like to ban would be create ##Temp Tables in memory, or even creating a table / querying a table without a Primary Key.
It would be great if I could display a message saying why this statement isn't allowed with a message of my choosing.
SQL Server adding the slashes to the date on the fly
I am trying to figure out how to add some sort of mask to my data. Currently I have a query like this:
SELECT [EmployeeTC_No] AS "Employee TC#" ,[pye_nlast] AS "Name Last" ,[pye_nfirst] AS "Name First" ,[Dept] AS "Department" ,[pye_status] AS "Active" ,[HireDate] AS "Hire Date" ,[SeparationDate] AS "Separation Date" FROM [testing].[dbo].[testing]
The data in the hiredate and separationdate column shows as 09282015 but I need the dates to show the slash ("/") like 09/28/2015 is there any way to add some kind of mask on the fly for these?
Replication MYSQL error when configure Slave
Hello i want replication MYSQL data from master to slave
i'm already configure master and create account in server for slave
when i'm configure slave input
username : user_account password : user_password host : ip computer server / master port : 3306 and copy server-id to my.ini save then restart mysql
and click Go, show error like this
how to fix this error ? i'm already try other computer same error.
Note : Master & Slave same network and already try turn off firewall master and slave but same error :(
Help me thank's
Mysql multi source replication configuration issue
I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done?
I have a server 1 where i have two database A,B which i need to replicate the complete databases to slave.
and on server 2 where i have one database C where i need to replicate only two tables in the slave.
so this is the configuration i did in the slave, but its not working for me in case of
server-id=950 binlog_format=row log-bin=mysql-bin skip-slave-start gtid-mode=on enforce-gtid-consistency log-slave-updates master-info-repository=TABLE relay-log-info-repository=TABLE replicate-do-db=A replicate-do-db=B replicate-do-table=C.tbl_name1 replicate-do-table=C.tbl_name2
even tried with
Please help.Thanks in advance
PostgreSQL 10 logical replication trigger function not executed
I've implemented the Logical replication in PostgreSQL 10 within 2 db in 2 different server and I'm aware that the sequences are not sync so I created a trigger function like this in both database:
CREATE FUNCTION update_ogc_fid() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ Declare iInt integer; BEGIN execute 'Select nextval('|| chr(39) || 'address_ogc_fid_seq' || chr(39) ||');' into iInt; NEW.ogc_fid = iInt; RETURN NEW; END; $BODY$; CREATE TRIGGER update_ogc_fid_address BEFORE INSERT ON address FOR EACH ROW EXECUTE PROCEDURE update_ogc_fid();
A part from the fact that is actually using just odd
IDin the DB1 and even in DB2 why is the function not triggered in the second database?
If I insert a record in the DB1 i have
ID3, it do the replication and in the DB2 i have the same record with the same
ID, I'm going to check the sequence in the DB2 and is still 1 instead of be 3.
Now I'm going to insert a record in the DB2 and I have
ID2 and then 4.
For what I want to achieve that is a master master system, in case that one is down I can write in the second one I don't really care too much but thinking in case of a disaster when I'm going to restore the DB in the server that is down (maybe with a pg-dump backup) I need to update all the sequences to start writing from where was before.