Now I want to create a view called AssignedTo. [The application I'm
doing, will track the status of our customer requests. Each request
can be assigned to either an individual employee or an entire
department]
I want the view to have two columns, ID and LongName. ID should be
either the DeptID or the EmployeeID. The LongName column should be
either the LongName or the FirstName + LastName.
I'm not even sure how to begin to write such a complex SQL.
EnterpriseManager is being less than helpful too.
I'm developing a VB.Net for a CE.NET device. I use SQL Server 2000. I use merge replication to synch with the CE.NET device.
I have this problem where a view is not replicating to the CE.NET but my table articles are replicating without problems. All the base tables for the view are replicating correctly.
Using SQL 2005 as pub and SQL EXPRESS as sub using Merge replication. Got the following error message
The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. Error Detail: The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164 Invalid object name 'dbo.Debit_v'. (Source: MSSQLServer, Error number: 208) Get help: http://help/208
According to error message, it seems that debit_v is missing. However, I cannot control the sequence of view to replicate. How can I solve this problem
Using SQL 2005 as pub and SQL EXPRESS as sub using Merge replication. Got the following error message
The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. Error Detail: The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164 Invalid object name 'dbo.Debit_v'. (Source: MSSQLServer, Error number: 208) Get help: http://help/208
According to error message, it seems that debit_v is missing. However, I cannot control the sequence of view to replicate. How can I solve this problem
Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.
Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.
Here is what I am doing:
Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,
table2.txt_sort_order
FROM table1 join table2 on table1.status_code = table2.status_code
I have two tables (ship2005 and ship2006) all with same structure Sold_to Week Year Units Dollars Style
I have to merge them together, and add some other columns from other tables Custmstr (director) and stylemstr (style) based on Sold_to
In the format so I’m able to see 2005 week, 2006 week, 2005 unit, 2006 unit, 2005 dollars, 2006 dollars and so on.
This one is working, but forever…
where Custmstr.Sold_to *= ship2005.Sold_to and Custmstr.Sold_to *= ship2006.Sold_To and stylmstr.style_num *= ship2005.style_number and stylmstr.style_num *= ship2006.style_number
And another thing: how can I populate the Year column for both?
Hi guys! I'm trying to figure out how to join 3 tables, but I can't seem to find a solution. What I want to do is to put table 1, table 2 and table 3 into table_merged. table_merged = table 1 + table 2 + table 3 Is it possible to merge tables even if they have different fields?
If possible, I would like to create a view using two different tables, but with the fields merged (ie. create one table from two tables). The table structures are pretty much the same. Can you do this from a singe SQL statement? Any ideas? My only alternative is creating a temp file that inserts records from both tables, but I want to avoid doing that.
I have two totally different tables with completely different data fields. Also, there is no common relationship between these two tables. However, I want to pick few data fields from the each table and merge into a new table! Is this even possible?
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5 ,@LASTtime TIME =CAST('23:55' as TIME) ,@Time TIME =CAST('00:00' as TIME) ,@Timeprev TIME =CAST('00:00' as TIME) WHILE ( @Time<>@LASTtime) BEGIN
Hey all For performance reasons i want to move the Merge Replication tables in a DB of mine (mainly MSMerge_Tomestone and MSMerge_Contents) to a seperate filegroup ... it's a heavy transactional DB and the index fragamentation rate is quite phenominal!
I need to take certain items of data from four different tables and out them into one table.
Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.
I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.
That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?
i have 3 tables (T1, T2, T3), each with the same structure: ID1 -> char(10) ID2 -> char(12) NULL ID3 -> char(10) Value1 -> money Value2 -> money Value3 -> money Note1 -> Text Note2 -> Text
ID1+ID2+ID3 is the clusterd unique key in each table
what i want: ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1
what i tried is to get all possible rows with
SELECT ID1,ID2,ID3 FROM T1 UNION SELECT ID1,ID2,ID3 FROM T2 UNION SELECT ID1,ID2,ID3 FROM T3
but i dont know how to join or add the other columns. maybe with
WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...
I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.Â
Hi, eveyone.. I have a problem. One of developer deleted Merge Replication Job. However there is no doc for that replication. What I want to know is like this: which tables are in that replication? Is there any system or user tables track down these replication as well as configuration in that replication?
If you have 2 tables with the same columns and you would like to see all distinct records in a result of a select and also the information in the records which table the record comes from (for instance: from table A or from table B or bot tables contain it) what should you do?
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as ( Select ContactID from Contact where ContactID=@ContactID) MERGE Employee as NewEmp Using Cont as con
There is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question. I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example: Table A -------------- 1 test test1 test2 test3 test4 test5 2 test test6 test7 test8 test9 test10
Table X ------------ 1 test test1 test2 test99 test4 test5 2 test test98 test97 test 96 test95 test94 -------------------------------------------------------- Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1.. The new Table A should look like
NEW Table A' -----------------
test test1 test2 test3 test4 test5 test test6 test7 test8 test9 test10 test test98 test97 test 96 test95 test94
------------------------------------ I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
We have merge replication running with anamous subscribers We have generested lots of views tables and stored procedures like sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
What I need to do is merge the two tables into a single table based on the matching timestamp:
timestamp, servername1, servername2 as headers then values under "1/1/1-1:10:1" , "10" , "10" - as an example "1/1/1-1:20:1" , "20" , "21" - as an example "1/1/1-1:30:1" , "1" , "5" - as an example
Powershell or MSSQL 2008 queries work.
I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.
The table is for some performance stat collection app.
Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object. Table #2 is the same collection but for a different server.
The layout in the database is terrible but this is what I get to work with.
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A: sp_addlinkedserver B INSERT dbo.ResultsTable SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following: OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided? 2) Is there another approach to run cross-servers JOIN in SSIS?
I have suddenly lost the ability to view the tables for one of my database via Enterprise Manager. It just sits at an hourglass and never returns! Alos, if I attempt to link to the tables via MSAccess's ODBC interface, I get a timeout.
I am able to view the table list for other user databases, as well as the system databases.
I am able to view other object list (like SPs) for the database in question, via Enterprise Manager.
I am able to perform a select query against the sysobjects table for the database in question.
Any ideas on why Enterprise Manager, and ODBC links, are hanging while trying to access the tables for one particular user database?
Any help is appreciated.
All other functions appear normal. If I can't figure anything out, or no one has any other ideas, I will try rebooting the server after the users are gone for the day.
Hi, I€™m new to sql ex 2005 and have a couple of questions I can€™t find the answers to. Q.1 I am working with VS web dev ex 2005 and I€™m trying to backup my db, is it as simple as copying the .mdf file? Or should I create a text file, or something else? Q.2 How do I view the content of the db in my web programs in the sql server 2005 ex? I can€™t figure out what to do, to view the content. I have tried to find tutorials on ex 05 but can€™t find answers to my questions. Help would be appreciated. Thank you.
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....
