We are trying to configure Delta View in our senario and are confused at a couple of points:
1. What is the difference between modify and modify_attribute? How does modify_attribute works at attribute level? Is it only for multivalued attributes?
2. Rather than having an extra table for Delta. If we would use the prime table itself with an extra field acting as a flag (0/1) (0=Prime Record, 1=Delta Record) and generate the delta view according to the flag. Would it be fine or we should have a different delta table only?
I am performing data migration from a legacy sql server (2000) to a new one (SQL 2005). I am using SSIS packages to perform the migration. The source legacy sql server is a live machine (used by the customer). Therefore, during the data transfer there would certainly be new records inserted/updated/deleted on the source legacy sql server. Now, after the migration i wish to migrate the delta to the new sql server. how can i do this ? Does SSIS have any provision to take care of it ?
i'm new to this platform and am wondering if there is an easy way to identify ADDs changes and deletes between two tables - or whether i will have to do a ror for row comparison using the business key?
Suppose a database server and client are separated by a low bandwidthlink such as DSL, and the client repeatedly issues a query for, say, acurrent product list.Suppose the product list is large, but only a handful of entries havetypically changed between queries. It would be nice if only the changesfrom last query to current one could be sent, saving bandwidth.Is there any way to do this?Thanks,--"Always look on the bright side of life."To reply by email, replace no.spam with my last name.
I'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,
SELECT Â Â Â a.col1,a.col2,b.col3,b.col4,c.col5 FROM a left outer join b ON a.col1 = b.col1 left outer join c ON b.col1 = c.col1.
Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination table.
I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.
DECLARE @MaxCountHistogram TABLE  (  MaxId  INT IDENTITY PRIMARY KEY NOT NULL,  PublicationId  INT NOT NULL,  ProviderId    INT NOT NULL,  DateLog  DATETIME NOT NULL,  Amount     FLOAT NOT NULL  ) INSERT INTO @MaxCountHistogram VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider  and getting the diferential between two different months, for example:
Period             Delta Amount           Provider    PublicationId 20150530          10.2564                   3 432 20150630           3  Result of (13.2564- 10.2564 )     3            432
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
/*Title: Created By: Create Date: Notes:This function is used to concatenate the fields of a table except any identity and hashbyte column passed into the function. It works for temp tables are phyisical tables.
** NOTE: The temp table has be on the same SQL connection to work. If you use this in SSIS you will need to make your connection persistant.
The original concept came from [URL] .... and was modified.
Revisions:
*/ ALTER FUNCTION [dbo].[get_hash_fields] ( @p_table_name VARCHAR(128), @p_schema_name VARCHAR(20), @chksum_col_name varchar(255) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @sqlString as varchar(max)
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
I have a Sql7.0 box inside a firewall and Sql6.5 outside the firewall. I would like to use a DTS to transfer data. I followed the KB artice on configuring SQL to listen on a specfic port, but still have problems. I added entries in Client Config on both machines. Still no luck, looks like the 7.0 box not talking on new port...
I have a question about SQL Server configurations, especially those related to memory.
We are running SQL 6.5 SP5a on a 400 MHz machine with 1GB of RAM.
Our memory setting is 409600 (800 MB), which according to all the documentation that I have read is a conservative figure. The problem I believe is in our settings for Locks and Open Objects. The settings that are in place are Locks=3000000 and Open Objects=500000.
I am not a DBA, I am a developer, but have somehow become part of the DBA team. These settings seem out of proportion with the memory we have. The reason given for 3 million locks, by other members of the team, is that our biggest unit of work is a query that performs a select into of approx. 3 million records. To further compound things, we are getting an out of memory error on the server after only a day of running.
The other members of the team have implemented a start and start of the service to occur at 4 AM every morning, but I am convinced that this is not the appropriate action, but they will not action any configuration changes unless I can prove that it will help the memory problem and not effect the queries.
If anyone has any information or links to information regarding Locks and how they relate to query performance, or at least a sample of what your settings are it would go a long way towards convincing either myself or the rest of the team what direction to go.
I have SQL Reporting Services 2005 installed. When I go into the SQL Reporting Services Configuration Manager I immediately get an error that says that "No report servers were found on the specified machine" then Details: Invalid Namespace.
The machine that it is running on is our report server. The name in the Machine Name says the name of the report server not the name of the specific machine so maybe this is the problem? I think the name of the report server should go in instance? Problem is that it won't let me change anything and it just gives me that error and everything else on the screen is disabled.
Hey guys, got a question regarding hardware configuration for a new SQL server we are installing here. We are migrating from a Windows 2000 standard, SQL 2000 standard box with 4GB of RAM to a Windows 2000 datacenter, SQL 2000 enterprise box with 32GB of RAM.
What would be the best way to set this up in Enterprise Manager? Should I just let SQL Dynamically use the RAM or should I select a fixed amount? Also with the processors, should I allow SQL to use all available processors or just 3 of the processors? Also with such a large amount of RAM, I've read people setting there pagefile to 0 so there is no load on the hard drives.
We are running SQL 7 on a server, and are moving to a new server and will beupgrading to SQL 2005 at the same time. Currently, both the old and the newservers have two drives, one for programs and one for data.With the current configuration, SQL 7 and the data are both installed on thedata drive, in the MSSQL7 directory. Our sys admin wants to install SQL 2005on the program drive of the new server, while putting our databases on thedata drive. I argue that if SQL itself is on the program drive, then thesystem databases will be in one place, while our databases will be inanother. So I'd prefer to have SQL 2005 installed on the data drive, as itis now.I'm interested in any feedback regarding what you guys think is the betterconfiguration, and also if there's any performance hit from having theprogram and the database on two drives of the same machine.Thanks,Neil
In my Package Configurations, I have three different configurations. Two are of the type, SQL Server and one of type XML. I want to get the currently used Configuration name, Configuration String and the configuration type in my Script Task code. Can anybody help me with this code. Thank you.
