Friday, June 7, 2013

[how to] Restoring SQL_ASCII dumps to a UTF8-encoded database

[how to] Restoring SQL_ASCII dumps to a UTF8-encoded database

Restoring SQL_ASCII dumps to a UTF8-encoded database

Posted: 07 Jun 2013 03:53 PM PDT

I've got a Postgres 8.4 environment where the encoding on all our databases is set to SQL_ASCII - We're finally migrating to Postgres 9.2, and I would like to migrate everything over to UTF8 encoding.

Unfortunately the text data in this DB is not clean -- Trying to restore the pg_dump to a utf8-encoded database throws errors about invalid byte sequences, even if I specify --encoding=UTF8 when I run pg_dump (presumably because Postgres doesn't know what to make of them and just dumps them unchanged?).

We have a LOT of data (upwards of a million rows with text/string elements), and auditing all of it by hand would be very time consuming (and error prone) so I'd like to automate this if possible.

Is there an easy way to find the non-utf8-conforming strings/text fields in the database so we can fix them? Or am I stuck with a manual audit to straighten this mess out?

Need a log based replication / CDC solution to publish postgres to oracle subscribers

Posted: 07 Jun 2013 02:03 PM PDT

We have a moderately large postgres 9.1 db that is currently 3TB and will likely grow much larger over the next couple of years. we need a reliable and fast solution for moving this data into either oracle 11G or sql server 2012 databases. we need a log based solution like replication or CDC with a minimal foot print of the postgres server and not looking for a code based ETL solution like SSIS or SAP Data Services.

It should also be able to effectively handle blob / spatial data that is stored in postgres

How to troubleshoot high cpu problems related to postgres and postmaster service?

Posted: 07 Jun 2013 02:08 PM PDT

I'm using an open source(RHCE 6.2) based machine running SIEM software , when i do the top command i see

postgress postmaster

both eating 96% of cpu usage? Is there a way to pin-point or see what causing these service to stack up?

thanks.

How to implement somthing similar to "pointer arrays" on MYSQL

Posted: 07 Jun 2013 04:36 PM PDT

While building a site I came across the following issue:

I want to be able to have multiple containers and within each container have ids pointing to multiple items that lie on another table. (Scheme: http://i.stack.imgur.com/pb78k.png)

I am using MYSQL and would like to stick with it. However I am open to other ideas.

My questions are:

  1. Would this be a reasonable data structure, or is there any better way to organize this data?
  2. If So, How would I go about implementing this idea of "array of pointers" to elements.

User roles and permissions

Posted: 07 Jun 2013 12:11 PM PDT

Look at the attached graphic.

I'm trying to keep this as simple as possible.

Basically I want to have multiple admins(or superusers) who own regular users who own sites.

  • Admins can give regular users permission or role to a website.
  • Admins can see and edit all sites of the users they own.
  • Users can only see & edit their own sites. (based on the role).
  • One site can be managed by multiple users.

I don't know if I should have permissions table with multiple rows for each role so one user can have many rows for each role or maybe use an array in one row only... Idk. I'm stuck there.

What's the best way to do this simply?

P.S: I know the graphic has User2 under site1 and site2 but I don't need it that complex. Same users for same site but different roles is sufficient. No need for 1 regular user to edit 2 sites.

enter image description here

Running two loops simultaneously to move a list to rows in two different columns of different lengths

Posted: 07 Jun 2013 12:11 PM PDT

I need help in below table updation using SQL in Ms-Access 2007 database.

I've a table called table1 which has below entries:

table1:

--------------------------------------------------  |  col1  |   col2   |          col3         | Col4  -------------------------------------------------  |    A1  |  abcd    |      123;456;789      | Banana, water  -------------------------------------------------  |    B2  |  efgh    |       246;369         | fish, yellow, water  --------------------------------------------------  |    C3  |   ijk    |                       | socks  --------------------------------------------------

Using SQL, I want to change the above table (particularly the column col3 and col4 multiple values into individual multiple rows) as shown below:

--------------------------------------------------  |  col1  |   col2   |          col3       | col4  -------------------------------------------------  |    A1  |  abcd    |         123         |banana  -------------------------------------------------  |    A1  |  abcd    |         456         |water  -------------------------------------------------  |    A1  |  abcd    |         789         |  -------------------------------------------------  |    B2  |  efgh    |         246         |fish  --------------------------------------------------  |    B2  |  efgh    |         369         |yellow  --------------------------------------------------  |    B2  |  efgh    |                     |water  -------------------------------------------------  |    C3  |   ijk    |                     |socks

I found your answer in Ms-Access 2007-2010 SQL - from single row record how to create multiple rows and I was able to alter it to fit my needs. I am having trouble creating a nested loop. When I nest the loops I end up getting duplicated rows depending on whichever col i have running on the inner row. I cannot find a way to run the two loops simultaneously. I would really appreciate any help you can give me!

Difference between Sql server and Oracle [closed]

Posted: 07 Jun 2013 11:44 AM PDT

What is the Difference between Sql server and Oracle?

/usr/sbin/mysqld was deleted and replaced by another mysqld with different inode

Posted: 07 Jun 2013 07:23 PM PDT

This happened on three of our mysql server running on RHEL. While checking for open and deleted files, I found that mysqld in use is deleted (as seen in lsof) and was replaced by a similar mysqld (in /usr/sbin/mysqld) with a different inode. Size and blocks of both the files (deleted and current) are same. new mysqld (not in use) seems to be of the same version as the deleted one. I am trying to figure out what could have caused this (there are no cronjobs running on the system). I checked system logs, database logs and yum logs and found nothing relevant. Any input is appreciated.

Thanks!

SQL Server 2012 FileTable Missing sometimes

Posted: 07 Jun 2013 03:41 PM PDT

I have created 3 folders by using file table.

One of the folder have ~392 GB data (with heavy data read/write) and it is not showing up sometimes and I need to restart to SQL Server 2012 to make it appear in explorer again.

I don't see any issues other folders with less load.

Please advise.

Is it safe to remove an entry in a "config.mongos" MongoDB collection?

Posted: 07 Jun 2013 10:39 AM PDT

There are old entries in my "config.mongos"; they reference hosts which previously had "mongos" running on them but no longer do.

Can I safely remove those entries?

e.g.,:  # Old entries  { "_id" : "silicon:27117", "ping" : ISODate("2012-12-27T19:51:04.560Z"), "up" : 9301, "waiting" : true }  ...  # Current entries  { "_id" : "app1:27017", "ping" : ISODate("2013-06-07T17:12:02.856Z"), "up" : 5593404, "waiting" : true }  { "_id" : "app2:27017", "ping" : ISODate("2013-06-07T16:17:49.812Z"), "up" : 5590271, "waiting" : false }  { "_id" : "app4:27017", "ping" : ISODate("2013-06-07T17:12:02.110Z"), "up" : 5593413, "waiting" : true }  { "_id" : "app3:27017", "ping" : ISODate("2013-06-07T17:12:02.854Z"), "up" : 5593578, "waiting" : true }

Percona XtraDB Cluster and MySQL Replication

Posted: 07 Jun 2013 07:56 PM PDT

I've setup a 3-node multi master replication with percona xtradb cluster, it works perfectly.

Now I've tried to add some read-only slaves setting up replication as usual but it seems the binlog doesn't include the new inserts,

I've set binlog_do_db on the master of a database, slave says the log position is the same as the master ones but the new data isn't there.

Is there a special way to do replication on xtradb cluster?

Can I optimize this MERGE statement?

Posted: 07 Jun 2013 10:20 AM PDT

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows.

In my development environment, the query takes just under 8 minutes. But in the production environment, it should take significantly less (much more powerful machine). However, I anticipate the query taking at least 2 minutes to run in production. I know that this query was causing timeouts for other developers in the development environment, which means it could easily cause timeouts for customers. Is there a safer and/or faster way to perform this query?

declare @dt datetime = cast(dateadd(month, -6, getdate()) as date);    merge ShoppingCart as TargetTable  -- 07:55 to complete in Dev  using   (    select * from -- 04:55 to run select, resulting in 12,727,927 rows in Dev    (      select        visitorid  -- int, not null, foreign key        ,useripaddress  -- varchar(55), null        ,row_number() over         (partition by visitorid order by createdate desc) as [row]      from VisitorSession (nolock)      where UserIPAddress is not null      and CreateDate > @dt   -- createdate is a datetime, not null    ) as subTbl    where subTbl.[row] = 1  ) as SourceTable  on (TargetTable.VisitorID = SourceTable.VisitorID)  -- visitorid is not a primary key  when matched    then update set    TargetTable.UserIpAddress = SourceTable.UserIpAddress;

How to change x-axis labels in a report

Posted: 07 Jun 2013 10:15 AM PDT

I am creating a trend report, in which the x-axis labels are first laywr as year (2014), second quarter (1,2), third period (month) (3,1), fourth week (3,4,5,1,2).

I need to change these labels as y2014 for year, Q1,Q2 for quarter, month3,month1 for month, and W3,W4,W5 etc. for week.

All I need is to change the labels as they appear.

Can I default to T-SQL only view in VS 2012?

Posted: 07 Jun 2013 01:28 PM PDT

When creating new tables in a database project in VS 2012, opened sql files default to a split view. One is the T-SQL text editor view, and the other is the Design view. I've never used the design view, and probably never will. I just prefer the text editor. Is there a way to tell VS to default to the T-SQL view?

What signals do the DBWn processes send to LGWR?

Posted: 07 Jun 2013 08:06 PM PDT

I would really like to know what signals are in the following quote from the Process Architecture docs, and how does DBWn know LGWR has completed?

Before DBWn can write a modified buffer, all redo records that are associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.

Query keeps on executing

Posted: 07 Jun 2013 10:38 AM PDT

I'm executing below query, but it keeps on executing.

In place of some time I have a time value.

select distinct(col1)   from table1  where col2 > 'some time'    and col1 not in         (select distinct(col1)          from table2          where col2 not like '%XYZ%');

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Posted: 07 Jun 2013 10:43 AM PDT

SQL Server 2008R2 PowerShell 2.1

I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I create a CmdExec step and point to a PowerShell script file (.ps1) like this:

powershell.exe "c:\MyPSFile.ps1"

the job executes successfully (or at least gets far enough to only encounter logic or other syntax issues).

This approach won't work for a final solution, because there is a requirement to keep the PowerShell script internal to SQL. So I have a different CmdExec step that embeds the PowerShell script like so:

powershell.exe "import-module sqlps –DisableNameChecking    $server_name = "localhost"  $backup_location = "C:\BackupsGoHere"    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  $server = New-Object Microsoft.AnalysisServices.Server  $server.connect($server_name)    # Generate an array of databases to be backed up  foreach ($database in ($server.get_Databases() | Where {$_.EstimatedSize -gt 0 -and $_.Cubes.Count -gt 0})) {      $directory_path = $backup_location + "\" + $database.Name      if (!(Test-Path -Path $directory_path)) {          New-Item $directory_path -type directory | out-null      }      [string] $timestamp = date      $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')      $database.Backup("$directory_path\$database-$timestamp.abf")  }  $server.disconnect()"

However, when executed with the embedded script, the job errors out quickly with the following response:

The specified module 'sqlps' was not loaded because no valid module file was found in any module directory.

Why can't I reference the module from an embedded script, but doing so in a ps1 file works just fine?

Need an idea for a complex SQL question

Posted: 07 Jun 2013 12:04 PM PDT

I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-)

Here is what I am trying to accomplish (for PostgreSQL):

  • Got a table with logged communications related to products.
  • The table contains an ID (PK, serial), date/time when inserted, a product ID, an error code field (and some other fields not relevant for now)
  • Rows get validated upon reception, and all rows are stored.
  • Rows are either accepted or rejected (error code field).
  • When accepted, fine, the rows get processed (as a 'step 2' - not relevant for now).
  • However, when rejected the sender gets an error message and is requested to correct the data and resend.
  • So, when the corrected data is received again and is validated and accepted, we should have a product row that got rejected and a product row that got accepted.
  • We have learned that not all senders check these error message and resend their (corrected) data.

So, what I am looking for are these rows for product that were rejected but never sent again correctly - it may happen that rows get send multiple times and rejected multiple times by our validation process.

I have been at it for a couple of hours, but so far without much luck.
What SQL trick could I pull out of the hat to find these rows?

How to optimize multiple self-JOINs?

Posted: 07 Jun 2013 06:13 PM PDT

I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.

One of the tables looks as follows (relevant cols only):

CREATE TABLE IF NOT EXISTS CountryData (    countryDataID INT PRIMARY KEY AUTO_INCREMENT,    dataID INT NOT NULL REFERENCES DataSources (dataID),    dataCode VARCHAR(30) NULL,    countryID INT NOT NULL REFERENCES Countries (countryID),    year INT NOT NULL ,    data DEC(20,4) NULL,    INDEX countryDataYear (dataID, countryID, year));

The data column has values for a few hundred indicators, 90 countries, and 30 years for ~1mn rows total. A standard query requires selecting N indicators for a particular year and C countries, yielding a CxN table for 90 rows max.

With all values in a single column, self-joins seemed like the way to go. So I have experimented with various suggestions to speed those up, including indexing and creating new (temp) tables. At 9 self-joins, the query takes a little under 1 min. Beyond that, it spins forever.

The new table from where the self-joins take place has only about 1,000 rows, indexed on what seem to be the relevant variables - creation takes about 0.5 sec:

CREATE TABLE Growth      SELECT dataID, countryID, year, data      FROM CountryData      WHERE dataID > 522 AND year = 2017;    CREATE INDEX growth_ix       ON Growth (dataID, countryID);

The SELECT query then arranges up to XX indicators in the results table, with XX unfortunately <10: 

SELECT       Countries.countryName AS Country,         em01.em,      em02.em,      em03.em      ...      emX.em  FROM          (SELECT          em1.data AS em,          em1.countryID      FROM Growth AS em1      WHERE      em1.dataID = 523) as em01      JOIN       (SELECT          em2.data AS em,          em2.countryID      FROM Growth AS em2      WHERE      em2.dataID = 524) as em02      USING (countryID)      JOIN      (SELECT          em3.data AS em,          em3.countryID      FROM Growth AS em3      WHERE      em3.dataID = 525) as em03      USING (countryID)      ...      JOIN      (SELECT          emX.data AS em,          emX.countryID      FROM Growth AS em5      WHERE      emX.dataID = 527) as emXX      USING (countryID)      JOIN Countries       USING (countryID)

I'd actually like to retrieve a few more variables, plus potentially join other tables. Now I'm wondering whether there's a way to run this more efficiently, or whether I should take an altogether different approach, such as using wide tables with indicators in different columns to avoid self-joins.

Efficient way to fetch records since a given criteria

Posted: 07 Jun 2013 08:24 PM PDT

I'm trying to implement a logic where the user can say give me n records since a given id#. E.g.

SELECT TOP (100) col1, col2, ... colN   FROM Table1  WHERE ID > @id  ORDER BY ID

Performance is the biggest issue here, especially when you get into nested loops for complex joins. I've looked at the new OFFSET-FETCH feature in SQL Server 2012 but they require you to specify a number rather than a WHERE clause. I wouldn't know the offset unless I count num rows beforehand.

Is there an alternate way to do this efficiently in SQL Server (2008 R2 and above)?

Update: Complete SQL as generated by EF Code first

-- Region Parameters  DECLARE @p__linq__0 BigInt = 60375518904121  DECLARE @p__linq__1 BigInt = 60375518904121  -- EndRegion  SELECT   [Project3].[Id] AS [Id],   [Project3].[C2] AS [C1],   [Project3].[C1] AS [C2],   [Project3].[C3] AS [C3]   FROM ( SELECT       [Limit1].[Id] AS [Id],       [Limit1].[C1] AS [C1],       [Limit1].[C2] AS [C2],      [Limit1].[TmId] AS [TmId],       CASE WHEN ([Extent4].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]      FROM   (SELECT TOP (100) [Project2].[Id] AS [Id], [Project2].[TmId] AS [TmId], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2]          FROM ( SELECT               [Extent1].[Id] AS [Id],               [Extent2].[TmId] AS [TmId],               CASE WHEN ([Extent2].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],               1 AS [C2]              FROM  [dbo].[TextMessages] AS [Extent1]              LEFT OUTER JOIN [dbo].[Tms] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TmId]              WHERE ( EXISTS (SELECT                   1 AS [C1]                  FROM [dbo].[Tmr] AS [Extent3]                  WHERE ([Extent1].[Id] = [Extent3].[TmId]) AND ([Extent3].[IsDel] = 1) AND ([Extent3].[UserId] = @p__linq__0)              )) OR ([Extent1].[CreatedBy_Id] = @p__linq__1)          )  AS [Project2]          ORDER BY [Project2].[Id] DESC ) AS [Limit1]      LEFT OUTER JOIN [dbo].[Tmr] AS [Extent4] ON [Limit1].[Id] = [Extent4].[TmId]  )  AS [Project3]  ORDER BY [Project3].[Id] DESC, [Project3].[TmId] ASC, [Project3].[C3] ASC

Update: The real bottleneck turned out to be network latency caused by EC2 servers.

Is the key_buffer_size applicable to myisam tmp tables?

Posted: 07 Jun 2013 04:24 PM PDT

I have a database about 750GB in size. It's all innodb.

Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The tmp tables fit into memory.

My cache hit ratio (Key_reads / Key_read_requests) is 0.

When MySQL creates these tmp tables, I'm guessing it doesn't it create pseudo indexes to be used by key_buffer_size.

Update one table from another table while sorting that table based on one column

Posted: 07 Jun 2013 03:24 PM PDT

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new form will be submitted. So in the old table we easily have several rows with the same Firstname, Lastname, but different values in the other columns, and there's a timestamp column Date_Submission as well.

Now we are trying to move everything to a new table, but this time, for each person we only keep one row. And we want to keep some of the the latest old data of that user (like email, phone number, etc)

I could do the following:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname;

Apparently this won't give me the "latest" old date for each person.

So I tried this one:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname order by oldtable.Date_Submission;

But they MySQL will complain with:

"ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

So I'm wondering, what's the correct way to achieve this?

increasing mysql table open cache?

Posted: 07 Jun 2013 02:24 PM PDT

I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?

Thanks!

select count(*) in mysql 5.5 innodb-- rewrite advice?

Posted: 07 Jun 2013 10:24 AM PDT

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... 

select count(*)   from mails3     join questions using (question_id)   where mails3.from_user_id = '86696'     and mails3.is_community_star = 1     and mails3.rating = 3     and questions.is_rated_community = 1;

the query execution plan looks simple enough but very slow

*************************** 1. row ***************************             id: 1    select_type: SIMPLE          table: mails3           type: ref  possible_keys: question_id_index,emails_doublestars,favorite_emails,user_responses,owner_responses,sentmail,watchlist,read_responses,rating_date,read_and_unrated            key: emails_doublestars        key_len: 8            ref: const,const,const           rows: 2784          Extra: Using where  *************************** 2. row ***************************             id: 1    select_type: SIMPLE          table: questions           type: eq_ref  possible_keys: PRIMARY,is_rated_community_senddate_idx            key: PRIMARY        key_len: 5            ref: answerology.mails3.QUESTION_ID           rows: 1          Extra: Using where  2 rows in set (0.00 sec)

Search every column in every table in Sybase Database

Posted: 07 Jun 2013 08:24 PM PDT

I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is very convoluted. I've been able to figure out most of the schema to gain access to the data I need, however, there are a few things still left to find. I know what they are called in the actual POS, but I can't seem to find them anywhere in the database (although I haven't actually looked through all 200+ tables).

I'm wondering if there's any way to run a query to search for a specific string in all the columns in all the tables. BTW, I'm using the "Interactive SQL" application that comes with the Sybase Database Client software to connect to the database.

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

Posted: 07 Jun 2013 12:21 PM PDT

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming parameters to variables so as to defeat parameter sniffing. A tried/true approach. Bam, query time increased (less than 1 sec), when looking at the query plan the improvements were found in an index the original wasn't using.

Just to verify that we didn't get a false positive we did a dbcc freeproccache on the original proc and reran to see if the improved results would be the same. But, to our surprise the original proc still ran slow. We tried again with a WITH RECOMPILE, still slow (we tried a recompile on the call to the proc and inside the proc it'self). We even restarted the server (dev box obviously).

So, my question is this... how can parameter sniffing be to blame when we get the same slow query on an empty plan cache... there shouldn't be any parameters to snif???

Are we instead being affected by table stats not related to the plan cache. And if so, why would setting the incoming parameters to variables help??

In further testing we also found that inserting the OPTION (OPTIMIZE FOR UNKNOWN) on the internals of the proc DID get the expected improved plan.

So, some of you folks smarter than I, can you give some clues as to whats going on behind the scenes to produce this type of result?

On another note, the slow plan also get's aborted early with reason GoodEnoughPlanFound while the fast plan has no early abort reason in the actual plan.

In summary

  • Creating variables out of incoming parameters (1 sec)
  • with recompile (30+ sec)
  • dbcc freeproccache (30+ sec)
  • OPTION (OPTIMIZE FOR UKNOWN) (1 sec)

UPDATE:

See slow execution plan here: https://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

See fast execution plan here: https://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

Note: table, schema, object names changed for security reasons.
Posted by at No comments:

[SQL Server Data Warehousing] Collecting data from multiple sites


Collecting data from multiple sites



Your question has two different parts;


1- archiving existing databases


2- Integrating ,cleansing, de-duplicating (maybe), data warehousing, ETL , Cube


for the first part there are many articles published for archiving SQL Server databases. for example:


http://www.mssqltips.com/sqlservertip/1121/archiving-data-in-sql-server/


I am going to answer the second part of your question, which is more related to Data Warehousing and BI;


First of all you need to integrate data from those several hundred locations into an integrated database (this can be called as integrating or staging area). SSIS is good data transfer tool which helps to a lot in transferring data from those sources into the integrated database. you can use different methods for transferring data in SSIS, use FTP, exported CSV files, connect to SQL Server databases (if live connection exists), web services ,....


You will also need to design the data warehouse, Kimball is one of the most common methodologies of designing data warehouse based on business requirements. you can use The Data Warehouse Toolkit book from Kimball to learn about Dimensional Modeling.


After designing the data warehouse you will require an ETL (Extract Transform Tool) to get data from the integrated database (or staging) into the data warehouse. SSIS again is very powerful ETL tool. it will provide massive amount of data transformation components for using in ETL process.


For data cleansing; I recommend using Data Quality Services (DQS) which is part of SQL Server 2012 services. with this service you can create knowledge bases for different business domains, and somebody from business will play role of Data Steward to maintain that knowledge base, In SSIS there is a DQS Cleansing component which talks with DQS and apply data quality rules of knowledge bases to incoming data stream in ETL process, and as a result you will have more cleansed data.


For data consistency and governance; The best way to keep data consistency is a master data management tool, Microsoft tool for MDM is Master Data Services (MDS). this tool is part of SQL Server installation as well, you will create data models with entities, relationship between entities, and business rules. MDS provides only ONE SINGLE VERSION OF TRUTH for your data structure, this means that you can have only single version of customer information. or only single version of product info. this will help data consistency.


Finally for working with OLAP Cubes; you can use Analysis Services (SSAS) to build olap cubes on your data warehouse.


and if you want to create reports and dashboards from your cube; there are many data visualization tools in Microsoft stack, such as SSRS, PerformancePoint, Power View, Excel and Excel Services


http://www.rad.pasfu.com



.

social.technet.microsoft.com/Forums

Posted by at No comments:
Labels: ,

[MS SQL Server] IP Address Doesnt turn from offline to online

[MS SQL Server] IP Address Doesnt turn from offline to online

IP Address Doesnt turn from offline to online

Posted: 07 Jun 2013 04:52 AM PDT

:unsure:Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything regarding this issue;An error was logged in the Cluster and Event Log, saying Event ID:1609 Error:0x80071397 "The operation failed because either the specified cluster node is not the owner of the resource, or the node is not a possible owner of the resource"i have done all need to be done, but couldnot get the IP address online manually.its a 4 node cluster, 2 acts as primary and other 2 as secondrary,We have 5 applications over 4 depends on one node of primary and the left one on the other node.Rest two nodes remain as DR in case of failover.there are 2 IP Address for each node. In which one always remain online, the never respond to PING atleast, but one of them does respond but never comes online on selecting the option "Bring thus resource to online" How can i bring this IP address online. Please find all the work i have done on this issue.As i found few sujjestion from friends and also google, i Tried all of them resulting no solution.Suggestion 1: Restart the servers.ATTEMPT 1: I have shifted all the application to node 2 from node1, Restarted the NOde 1, Done the same with the node 2; Result: Nothing.Suggestion 2: Try from all the nodes to bring the IP address online.Attempt 2: Tried, no use. Not even one got turned in.On pinging any of the IP Address except 1 does show timed out, the exceptional IP Address does respond, but i dont know why its not getting online when requested.Suggestion 3: I found a post in some forum saying that, the IP Address from node which holds the quorum does only gets online, rest remain in offline.Attempt 3: said to manager the same thing, he suggested me to work more on it. Suggestion 4: Check subnet mask,Attempt 4: Checked every Subnet mask, does remain similar. but i could never make those IP address online from offline.BUT: when i was working on this matching the subnet masks; I found all the IP Address in different nodes, except the the IP's which i am not able to make them online, Including the one which got pinged success.I found another thing over here, I check the IPconfiguration in all the nodes, pinged all the IP's in all the nodes, including IP's which are online, The IP's which are shown online does ping in all the nodes (which is common)But the IP's Which are offline doesnt ping with the nodes of Primary and on pinging those on secondary nodes they doesnt reported as "timed out" but "Host not reachable"Does this sign anything, i haven't found anything on this, when i searched.When i report the same thing to my manager and try to involve Networking and Windows Admins in to it. He said gonna check it next monday.. I havent found perfect solution till now, i was working on this since 3 days, done a lot of googling, but could not find it any where. Hope i gave all the details. If any one had already faced such kind of issue, Please expalin me wat need to be done, Also let me know am i in right path for the solution,CAN ANY ONE HELP ME ON THIS...Please note***ROOKIE:hehe:*** here

SQL Server Version upgrade, Production DB's Left in Previous Version Compatibility

Posted: 06 Jun 2013 03:59 PM PDT

Hi guys,I was just wondering if it is safe to permanently run a production database in a newer version of SQL Server without changing the compatibility level of the database (say a SQL 2005 Database in SQL 2008R2 or SQL 2012)?We would like to spend a minimal amount of time and currently don't have the time nor the capacity to work on update stored procedures etc to work with the latest versions We have a few production servers running different versions from SQL 2005 / SQL 2008 R2, Instead of installing multiple instances on the same machine or having seperate machines for each version of SQL Server, I was just wondering if this was a safe approach.Some of the databases are from 3rd party software products.

Restore backup on lower version

Posted: 06 Jun 2013 08:03 PM PDT

Hi,Is any one know to restore backup of 2008 R2 on 2008 (higher to lower)?Thanks in advance.

Dead lock problem

Posted: 06 Jun 2013 07:17 PM PDT

i am trouble shooting one dead lock problem on SQL Server 2008 R2 SP1 which is weird for me i believe it is Intra-Query Parallel Thread Deadlocks,but i have question is this suppose that in this kind of deadlock victim is not present ?cause when i see xml then i could not find victim-list.another thing i extract this information from extended event health session .here is xml [code="xml"]<deadlock> <victim-list /> <process-list> <process id="process1a167a508" taskpriority="0" logused="10000" waittime="380" schedulerid="12" kpid="41496" status="suspended" spid="75" sbid="0" ecid="90" priority="0" trancount="0" lastbatchstarted="2013-06-06T12:43:42.467" lastbatchcompleted="2013-06-06T12:43:40.923" clientapp=".Net SqlClient Data Provider" hostname="MSWEB02" hostpid="60772" isolationlevel="read committed (2)" xactid="360175586" currentdb="20" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="" line="206" stmtstart="32758" stmtend="77810" sqlhandle="0x03001400fb3f7b6d4772f600d4a100000100000000000000" /> </executionStack> <inputbuf /> </process> <process id="process6a258a988" taskpriority="0" logused="10000" waittime="109" schedulerid="3" kpid="59444" status="suspended" spid="75" sbid="0" ecid="99" priority="0" trancount="0" lastbatchstarted="2013-06-06T12:43:42.467" lastbatchcompleted="2013-06-06T12:43:40.923" clientapp=".Net SqlClient Data Provider" hostname="MSWEB02" hostpid="60772" isolationlevel="read committed (2)" xactid="360175586" currentdb="20" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="" line="206" stmtstart="32758" stmtend="77810" sqlhandle="0x03001400fb3f7b6d4772f600d4a100000100000000000000" /> </executionStack> <inputbuf /> </process> <process id="process189753948" taskpriority="0" logused="10000" waittime="311" schedulerid="2" kpid="58892" status="suspended" spid="75" sbid="0" ecid="86" priority="0" trancount="0" lastbatchstarted="2013-06-06T12:43:42.467" lastbatchcompleted="2013-06-06T12:43:40.923" clientapp=".Net SqlClient Data Provider" hostname="MSWEB02" hostpid="60772" isolationlevel="read committed (2)" xactid="360175586" currentdb="20" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="" line="206" stmtstart="32758" stmtend="77810" sqlhandle="0x03001400fb3f7b6d4772f600d4a100000100000000000000" /> </executionStack> <inputbuf /> </process> <process id="process1a167b708" taskpriority="0" logused="10000" waittime="381" schedulerid="12" kpid="62392" status="suspended" spid="75" sbid="0" ecid="55" priority="0" trancount="0" lastbatchstarted="2013-06-06T12:43:42.467" lastbatchcompleted="2013-06-06T12:43:40.923" clientapp=".Net SqlClient Data Provider" hostname="MSWEB02" hostpid="60772" isolationlevel="read committed (2)" xactid="360175586" currentdb="20" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="" line="206" stmtstart="32758" stmtend="77810" sqlhandle="0x03001400fb3f7b6d4772f600d4a100000100000000000000" /> </executionStack> <inputbuf /> </process> </process-list> <resource-list> <exchangeEvent id="Pipe4a9b36c70" WaitType="e_waitPipeNewRow" nodeId="22"> <owner-list> <owner id="process1a167b708" /> </owner-list> <waiter-list> <waiter id="process1a167a508" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe523680ab0" WaitType="e_waitPipeNewRow" nodeId="32"> <owner-list> <owner id="process1a167a508" /> </owner-list> <waiter-list> <waiter id="process6a258a988" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe6dc6ea3e0" WaitType="e_waitPipeGetRow" nodeId="32"> <owner-list> <owner id="process6a258a988" /> </owner-list> <waiter-list> <waiter id="process189753948" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe52050ce30" WaitType="e_waitPipeGetRow" nodeId="22"> <owner-list> <owner id="process189753948" /> </owner-list> <waiter-list> <waiter id="process1a167b708" /> </waiter-list> </exchangeEvent> </resource-list></deadlock>[/code]We had turned trace flag 1222 globally on but i could not find information about this dead lock in sql server error log.So my question "is this normal to have empty victim-list ?"and if there is no victim and this deadlock does not recorded in sql server error log so should i consider dead lock?
Posted by at No comments:

[SQL 2012] Moving 2008 R2 db to 2012

[SQL 2012] Moving 2008 R2 db to 2012

Moving 2008 R2 db to 2012

Posted: 07 Jun 2013 02:49 AM PDT

I've got a database running on SQL Server 2008 R2 Standard. I'm building a SQL server 2012 Standard server to host a new version of that db. I want to move a copy of the database to the new server and run it under 2012 for testing for a couple of weeks, then move a fresh copy of the data to the new server when we're ready to go into production.What's involved in detaching a database from a 2008 R2 instance and attaching it to a 2012 instance? I found some references to updating the compatibility level (to 110) and updating statistics. What else needs to be done?

Linked Server to Call SP

Posted: 06 Jun 2013 10:05 PM PDT

Hi all, I need to call a SP on a remote server and im finding Linked servers to be troublesome to say the least.Ive enabled RPC and RPC Out.If I set up a linked server from SA on Server A to SA on Server B it will run If i set up a linked server from SQL User A (With SysAdmin rights) to SQL User B (With SysAdmin rights) it fails with the error:Msg 7416, Level 16, State 1, Line 1Access to the remote server is denied because no login-mapping exists.If i try using AD accounts (both with SysAdmin rights) it still fails.. again always with the same error message??any ideas whats going on?

Books on sql server 2012 SSIS and SSAS

Posted: 06 Jun 2013 02:59 PM PDT

Can anyone please recommend me good books on sql serer 2012 SSIS and SSAS?

SSIS 2012 Package Won't Run as SQL Server Agent Job

Posted: 06 Jun 2013 10:04 AM PDT

Hello, I'm new to the forum so sorry in advance for any etiquette I break :)I have an SSIS 2012 package that has a OLE DB data source connecting to an Access Database. The package runs fine when i run it from SQL Server Data Tools. I then deploy it to an SSIS Catalog and I can run the package fine from there. Next I add it as the only step in an SQL Server Agent Job and I get 4 consistent error messages."failed validation and returned validation status "VS_ISBROKEN"""One or more component failed validation""There were errors during task validation""An error occurred executing the provided SQL command: "select * from 'zqryExportTesting'". Object reference not set to an instance of an object.I've tries everything i can find. I've set the package encryption to be EncryptSensativeWithPassword instead of the default EncryptSensativeWithUserID. I've tried the 32 vs 64 bit runtime (I'm using 32 in the scenarios where it works). I've set the SQL Server Agent services to log on as the same user I am currently logged on to the server as. I've set up a proxy with the same user I am logged on to the servers as. I'm pretty sure i've tried every combination i've researched and still got nothing. I'm sure there is a simple setting or trick I'm missing.Thanks a TON in advance for any help you can provide.-Nick
Posted by at No comments:

[T-SQL] Combine columns into One with Delimiter

[T-SQL] Combine columns into One with Delimiter

Combine columns into One with Delimiter

Posted: 06 Jun 2013 06:09 AM PDT

I have the following:[code="plain"]Create table Table_1 ([col1] varchar(10) null,[col2] varchar(10) null,)Insert Table_1(col1, col2)values ('Bill','Smith'),('Bill',null),(null,'Smith')select rtrim(isnull(col1+'/',''))+rtrim(isnull(col2+'','')) as firsttry from Table_1[/code]This returns:Bill/SmithBill/SmithI am trying to remove the trailing '/' if col2 is null from col1.Was playing around with Reverse but couldn't get it to work...Any ideas would be appreciated.Thanks

help getting return of function and loop into stored proc

Posted: 07 Jun 2013 12:35 AM PDT

Hi,I have a tricky issue I am struggly with on a mental level.In our db we have a table showing the UK Holidays for the next few years, and a stored function returns a recordset to my front end.I have a flag in my recordset called 'deletable' which allows the frontend to decide if a context menu can be shown in the data grid, thus allowing that record to be deleted.Currently the test (in my stored proc) just checks if the date column has a date from three days ago or more.[code="other"]case when DATEDIFF(d,a.[date],GETDATE()) > 3 then 1 else 0 end as [deletable][/code]how can I modify that to find the previous working date by checking weekends and the Holidays table 'Holiday' column (which is a Datetime) and see if the [date] column in my recordset row is 3 working days before, taking into account Holidays from the Holidays table and weekends?Is there an elegent way to do that?thanksPhilip

Update Performance

Posted: 06 Jun 2013 10:58 PM PDT

Hi Guys,Any Ideas what the best way would be to perform this update?UPDATE DSET Track_ID = P.Track_ID, Territory_ID = P.Territory_ID, Major = P.Major FROM Staging.Track_Mapping P INNER JOIN Staging.Track_Play D ON P.ISRC = D.ISRC_Code AND P.Event_Date = D.Event_DateBoth tables have around 63 million rows and have numerous indexes.Any ideas appreciatedThanks,Rich

APPLY versus ROW_NUMBER versus double JOIN to retrieve most recent record from a foreign key table

Posted: 06 Jun 2013 01:52 AM PDT

I've been mulling over this for a while, and there's something about using the APPLY function (OUTER APPLY in this case) that bugs me in that it seems to force the contents of the apply to be executed as many times as there are input rows. I'm sure I've heard people say this shouldn't be the case, so I've finally got round to getting all the sample code and such together to find out where I'm going wrong.Basically, I have two tables, ServicerHistory and ServicerHistoryCommissionRate. ServicerHistory contains details of an account and who is servicing it (I've only bothered including the ServicerHistoryID in the sample below as the rest isn't pertinent to this example), whilst ServicerHistoryCommissionRate contains the effective date of the commission rates (if there are any) applied to that account.DDL for the tables and indexes:-[code="sql"]--create objectsIF OBJECT_ID('dbo.ServicerHistoryCommissionRate') IS NOT NULL DROP TABLE dbo.ServicerHistoryCommissionRateIF OBJECT_ID('dbo.ServicerHistory') IS NOT NULL DROP TABLE dbo.ServicerHistoryCREATE TABLE dbo.ServicerHistory(ServicerHistoryID int IDENTITY(1,1) PRIMARY KEY)GOCREATE TABLE dbo.ServicerHistoryCommissionRate(ServicerHistoryCommissionRateID int IDENTITY(1,1) PRIMARY KEY, ServicerHistoryID int NOT NULL FOREIGN KEY REFERENCES dbo.ServicerHistory(ServicerHistoryID), CommissionRate decimal(5,2) NOT NULL, EffectiveDate date NOT NULL)GOCREATE INDEX idx_ServicerHistoryID_EffectiveDateON dbo.ServicerHistoryCommissionrate (ServicerHistoryID, EffectiveDate DESC)INCLUDE (CommissionRate)GO[/code]And some data population (adjust the executions as you feel fit; :-[code="sql"]--populate with some sample dataINSERT INTO dbo.ServicerHistoryDEFAULT VALUESGO 100000--set up some datesDECLARE @Dates TABLE(SomeDate date NOT NULL)INSERT INTO @Dates (SomeDate)SELECT '2011-07-12'UNION ALLSELECT '2013-03-02'UNION ALLSELECT '2010-08-13'UNION ALLSELECT '2011-01-02'UNION ALLSELECT '2013-05-03'UNION ALLSELECT '2009-12-18'--set up commission ratesDECLARE @CommRates TABLE(Commissionrate decimal(5,2))INSERT INTO @CommRates (Commissionrate)SELECT 32.35UNION ALLSELECT 25UNION ALLSELECT 12.8UNION ALLSELECT 10UNION ALLSELECT 7.4--populate some servicer history ids with valid dates and commission ratesINSERT INTO dbo.ServicerHistoryCommissionRateSELECT TOP 20000 sh.ServicerHistoryID, Commrate.CommissionRate, Dates.SomeDateFROM dbo.ServicerHistory shCROSS APPLY (SELECT TOP 1 CommissionRate FROM @CommRates AS cr ORDER BY NEWID()) CommrateCROSS APPLY (SELECT TOP 1 SomeDate FROM @Dates AS d ORDER BY NEWID()) DatesLEFT OUTER JOIN dbo.ServicerHistoryCommissionRate shcr ON sh.ServicerHistoryID = shcr.ServicerHistoryID AND dates.SomeDate = shcr.EffectiveDateWHERE shcr.ServicerHistoryCommissionRateID IS NULLORDER BY NEWID()GO 50[/code]The three methods of retrieving the data that I've come up with are below:-[code="sql"]--retrieve the most recent commission rate for each row using outer apply (there may not be a rate)--uh oh, the subquery executed onece for each row in ServicerHistory tableSELECT sh.ServicerHistoryId, shrc.CommissionRateFROM dbo.ServicerHistory AS shOUTER APPLY (SELECT TOP 1 SHRC.CommissionRate FROM dbo.ServicerHistoryCommissionRate AS SHRC WHERE SHRC.ServicerHistoryID = SH.ServicerHistoryId ORDER BY SHRC.EffectiveDate DESC ) SHRC --try using ROW_NUMBER, whoo each table only touched once SELECT sh.ServicerHistoryId, shrc.CommissionRateFROM dbo.ServicerHistory AS shLEFT OUTER JOIN (SELECT SHRC.CommissionRate , ServicerHistoryId , ROW_NUMBER() OVER (PARTITION BY ServicerHistoryID ORDER BY EffectiveDate DESC) RowNum FROM dbo.ServicerHistoryCommissionRate AS SHRC ) SHRC ON sh.ServicerHistoryId = shrc.ServicerHistoryID AND SHRC.Rownum = 1 --try old fashioned double join method, SErvicerHistoryCommissionRate table touched twice so more expensiveSELECT sh.ServicerHistoryID, shrc.CommissionRateFROM dbo.ServicerHistory AS shLEFT OUTER JOIN (SELECT commid.ServicerHistoryID , MAX(ServicerHistoryCommissionRateID) ServicerHistoryCommissionRateID FROM dbo.ServicerHistoryCommissionRate commid LEFT OUTER JOIN (SELECT ServicerHistoryID ,MAX(EffectiveDate) MaxDate FROM dbo.ServicerHistoryCommissionRate GROUP BY ServicerHistoryID) commdate ON commid.ServicerHistoryId = commdate.ServicerHistoryID GROUP BY commid.ServicerHistoryID) comm ON sh.ServicerHistoryId = comm.servicerHistoryIDLEFT OUTER JOIN dbo.ServicerHistoryCommissionRate shrc ON comm.ServicerHistoryCommissionRateID = shrc.ServicerHistoryCommissionRateID[/code]Statistics time and io return the following:-[code="plain"](100000 row(s) affected)Table 'ServicerHistoryCommissionRate'. Scan count 100000, logical reads 319323, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistory'. Scan count 7, logical reads 484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 639 ms, elapsed time = 845 ms.(100000 row(s) affected)Table 'ServicerHistoryCommissionRate'. Scan count 1, logical reads 1852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistory'. Scan count 1, logical reads 163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 327 ms, elapsed time = 1040 ms.(100000 row(s) affected)Table 'ServicerHistory'. Scan count 7, logical reads 484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistoryCommissionRate'. Scan count 14, logical reads 4098, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 767 ms, elapsed time = 1847 ms.[/code]The execution plans for the above are attached; as you can see, the first query using the APPLY method executes the index seek on the ServicerHistoryCommissionRate 100k times (for the actual data this comes from there are several million rows in each table so it just gets worse!), making this seem to be by far the most inefficient method of the three when returning a set.Am I missing something with the way in which to implement using APPLY when dealing with sets, or is it just not good for that kind of query? I like APPLY from a "readable code" point of view, but for performance it just seems to be a bit horrible unless you're working with a very small set of rows.Any tips, pointers or observations are most welcome!

update for My table?

Posted: 06 Jun 2013 06:56 PM PDT

Hai friends, my table structure iscreate table journey(journey_id int identity,fromplace varchar(44),toplace varchar(44),mode nvarchar(20),seattype nvarchar(20),status_id int)insert into journey '2013-05-10','chennai','Mumbai','Air','Business','2'how to write fro update for these table depends on journey_id? i m tried its not reflecting?

Forcing a row to be related to at least another row

Posted: 06 Jun 2013 08:50 AM PDT

HalloI need to implement this:A row in table A must be in relationship with [b]at least one[/b] row in table B.Could this be achieved by constraints only?Thanks in advance

truncate error - foreign key and check constraints

Posted: 06 Jun 2013 04:25 PM PDT

Hi.could not truncate table due to FK constraintsTable defination[code="sql"]/****** Object: Table [dbo].[DiagHistory] Script Date: 06/07/2013 10:49:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DiagHistory]( [DiagHistoryKey] [uniqueidentifier] ROWGUIDCOL NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [TypeID] [uniqueidentifier] NOT NULL, [DescriptionID] [uniqueidentifier] NOT NULL, [Description] [ntext] NOT NULL, [DiagSourcesKey] [uniqueidentifier] NOT NULL, [DiagDomainKey] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_DiagHistory] PRIMARY KEY CLUSTERED ( [DiagHistoryKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs] FOREIGN KEY([DescriptionID])REFERENCES [dbo].[DiagDescriptionIDs] ([DescriptionID])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDomain] FOREIGN KEY([DiagDomainKey])REFERENCES [dbo].[DiagDomain] ([DiagDomainKey])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDomain]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagSources] FOREIGN KEY([DiagSourcesKey])REFERENCES [dbo].[DiagSources] ([DiagSourcesKey])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagSources]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagTypeIDs] FOREIGN KEY([TypeID])REFERENCES [dbo].[DiagTypeIDs] ([TypeID])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagTypeIDs]GOALTER TABLE [dbo].[DiagHistory] ADD CONSTRAINT [DF_DiagHistory_DiagHistoryKey] DEFAULT (newid()) FOR [DiagHistoryKey]GO[/code]I have tried as below script but its not workingalter table [dbo].[DiagHistory] nocheck constraint DF_DiagHistory_DiagHistoryKeygotruncate table diaghistorygoalter table [dbo].[DiagHistory] check constraint DF_DiagHistory_DiagHistoryKeygocould you suggestion me for single batch script to truncate data?Error[code="sql"]Msg 11415, Level 16, State 1, Line 1Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.Msg 4916, Level 16, State 0, Line 1Could not enable or disable the constraint. See previous errors.Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'diaghistory' because it is being referenced by a FOREIGN KEY constraint.Msg 11415, Level 16, State 1, Line 1Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.Msg 4916, Level 16, State 0, Line 1Could not enable or disable the constraint. See previous errors.[/code]

Running a DELETE Stored Proc but finding degradation in Performance in WHILE loop iterations increase

Posted: 06 Jun 2013 09:24 AM PDT

1st time/long time,Quick question in general. I have a table i'm trying to purge a table. i'm deleting using a WHILE loop with WAIT time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. refer to the following:Minute Number|Number of Records Deleted:1|162,0002|116,0003|80,0004|72,0005|62,0006|38,0007|38,0008|34,0009|20,000Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly b/c as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.thanks in advanceDennis

Creating hierarchical metadata based on DMVs or system tables

Posted: 06 Jun 2013 11:06 AM PDT

I have a requirement to archive & purge my OLTP data. The database is a tightly defined relational database with Primary Keys/Foreign Keys very thoroughly defined. For this process, I am planning to build hierarchical meta data on fly (before archiving and purging) so that I can go top to bottom while archiving and bottom to top while purging.For example, below are my sample table details...[img]http://www.sqlservercentral.com/Forums/Attachment13796.aspx[/img]I want to create hierarchical metadata for my transactional tables. I envision the following...[img]http://www.sqlservercentral.com/Forums/Attachment13795.aspx[/img]With the ranking I have in the "Level" column of the output, I am guessing I can start to copy data (for archiving) from Level going all the way to Level 2. Likewise, for purging I can start purging data from the bottom most level and go all the way to the top. In an environment like where all the relations are very well defined, I feel that it will work efficiently. For instance, if new tables are created at a later data (assuming their relations are defined as expected), I don't have to make modifications to my archive/purge scripts since the script will build the metadata at the beginning and use for the process.My first question is, how to build a query uisng DMVs and/or system tables to give me this output? And secondly, is there a better way to archive and purge data where I don't have to change my script everytime new tables are built.Following is the script to build the tables I cited above.[code="sql"]CREATE TABLE dbo.Customers ( CustomerID INT NOT NULL, CustomerName VARCHAR (100) NOT NULL, CustomerStreet VARCHAR (200) NOT NULL, CustomerCity VARCHAR (100) NOT NULL, CustomerState VARCHAR (50) NOT NULL, CustomerZip VARCHAR (10) NOT NULL, CustomerEmail VARCHAR (100) NOT NULL, CustomerPhone VARCHAR (20) NOT NULL, CustomerWebSite VARCHAR (100) NULL ); ALTER TABLE dbo.Customers ADD CONSTRAINT pkCustomerID PRIMARY KEY CLUSTERED (CustomerID);CREATE TABLE dbo.Orders ( OrderID INT NOT NULL, OrderCreatedDate DATETIME2 NOT NULL, OrderCreatedBy VARCHAR (50) NOT NULL, OrderLastModified DATETIME2 NOT NULL, CustomerID INT NOT NULL ); ALTER TABLE dbo.Orders ADD CONSTRAINT pkOrderID PRIMARY KEY CLUSTERED (OrderID); ALTER TABLE dbo.Orders ADD CONSTRAINT fkOrdersCustomer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);CREATE TABLE dbo.OrderItems ( OrderItemID INT NOT NULL, ItemID INT NOT NULL, ItemOrderQuantity INT NOT NULL, OrderID INT NOT NULL ); ALTER TABLE dbo.OrderItems ADD CONSTRAINT pkOrderItemID PRIMARY KEY CLUSTERED (OrderItemID); ALTER TABLE dbo.OrderItems ADD CONSTRAINT fkOrdersItemOrder FOREIGN KEY (OrderID) REFERENCES Orders(OrderID); CREATE TABLE dbo.OrderItemShipment ( OrderItemShipmentID INT NOT NULL, ShipmentType INT NOT NULL, OrderItemID INT NOT NULL ); ALTER TABLE dbo.OrderItemShipment ADD CONSTRAINT pkOrderItemShipmentID PRIMARY KEY CLUSTERED (OrderItemShipmentID); ALTER TABLE dbo.OrderItemShipment ADD CONSTRAINT fkOrderItemsOrderItemsShipment FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);CREATE TABLE dbo.OrderItemException ( OrderItemExceptionID INT NOT NULL, ExceptionType INT NOT NULL, OrderItemID INT NOT NULL ); ALTER TABLE dbo.OrderItemException ADD CONSTRAINT pkOrderItemExceptionID PRIMARY KEY CLUSTERED (OrderItemExceptionID); ALTER TABLE dbo.OrderItemException ADD CONSTRAINT fkOrderItemsOrderItemException FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);[/code]- Rex

Missing sequence where gap is 1

Posted: 06 Jun 2013 06:51 AM PDT

create table #MyTable (RecID INT IDENTITY(1,1),trxnNum int,Site varchar(10),Company VARCHAR(100))INSERT INTO #MyTable (trxnNum,Site,Company)SELECT 14418,'MySite','MyCompany'UNION ALLSELECT 14420,'MySite','MyCompany'UNION ALLSELECT 14431,'MySite','MyCompany'UNION ALLSELECT 14432,'MySite','MyCompany'UNION ALLSELECT 14436,'MySite','MyCompany'select * from #MyTableDROP TABLE #MyTableI have to return rows where TrxnNum = 14418 and 14420 because the gap is only 1 . If the gap is > than 1 example 14432 and 14436, I dont want to return those. If there is no gap as well 14431 and 14432 I dont want to return as well.In short where gap is = 1.Thanks,
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog