Thursday, September 5, 2013

[SQL Server 2008 issues] Project Plan

[SQL Server 2008 issues] Project Plan

Project Plan

Posted: 04 Sep 2013 07:04 PM PDT

Need Project Plan for SQL server 2008 Database server.. general overview.. please suggestLike we have SQL servers. so what would be the project plan...

Getting table size greater than zero even row count is zero ?

Posted: 04 Sep 2013 05:37 PM PDT

Hi,I was checking table size and row count and found a table type heap with rowcount 0 is have space.Can anyone please describe what could be the reason ?Below is query i used on my database.SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKBFROM sys.tables tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idWHERE t.NAME NOT LIKE 'dt%' -- AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, p.RowsORDER BY t.Name

Log Shipping using Virtual Servers

Posted: 03 Sep 2013 09:13 PM PDT

Good morning all,I'm looking at implementing log shipping on two virtual servers running VMWare and SQL Server 2008 R2 (Standard) and attached to a SAN, does anyone know if I need to do anything different regarding permission levels for the SQL Server Service and Agent accounts (i.e. do I need to grant permissions on the physical host server/SAN and the virtual servers etc?).Thanks in advance for any help offered.M

Create table of unique alpha numbers to use as sku's

Posted: 04 Sep 2013 12:16 PM PDT

Hello all,I am new in the since of posting in the forum I do have some past experience with SQL mostly in v2000/v2005.What I have, is a need for a table of unique alpha numeric values that I can use for sku numbers and printing simple bar code labels for our products. (I do not need barcoding help as my label software just needs the number).I will be populating in blocks of 15,000 skus to start and will need to remove any duplicates if they so happen to exist after the initial population.The alpha part will come from a separate table of values. This table is as so:Table = SkuCategoriesTable Columns = (t_id IDENTITY , skuCategory_Name nvarchar(MAX), skuCategory_KeyCode char(10))Values = (t_id, 'SomeCategory Product', 'SP')What I need is the ability to run a statement, cursor or whatever method to populate another table with a set number of records with a unique sku number using the 'skuCategory_KeyCode' column value 'SP'Where "S" is inserted at the beginning and "P" added at the end of a unique 9 digit number.For instance or a sample of the value I am looking to get is as follows.sku= "S123412345P"The sku list table needs the following columns(id IDENTITY, sku VARCHAR, datecreated (TimeStamp), isUsed BIT default 0) Thank you and go easy on me as this is my first post!

Clustered Indexes on Identity columns

Posted: 04 Sep 2013 07:27 AM PDT

Hello,I had a quick question for everyone... I often wondered why Clustered Indexes are usually placed on PK Identity(1,1) columns (1,2,3,4,5,6,7,8...) instead of being placed on columns that would impact performance better. A Clustered Index on a date range column would be much more efficient than a Clustered Index on a basic "ID" column. I have seen this time and time again. Usually NON_Clustered indexes are placed on date ranges instead of Clustered Indexes. For the most part, I presume that this is due to that fact that a clustered index must be on a unique column and most datetime columns are not unique. Most articles that I have read lead you to the direction of placing indexes on JOINS, WHERE CLAUSES, and sometimes ORDER BY. But Clustered indexes on a join would be so much faster than NON-Clustered. What is the best practice? Thanks for your time.Dave

Service Broker takes long time to insert into a table

Posted: 04 Sep 2013 08:01 AM PDT

I am new to service broker. One of our databases uses service broker on SQL Server 2008 R2. A Stored Procedure makes use of service broker. And the procedure is called nearly 100 times.Now the problem is that the procedure execution completes very quickly in 13 seconds but I am able to see the inserts to the main table occurring for a very long time. For nearly 6 hrs after the procedure execution completes. Can someone shed some light on this ? Do I tune the query/change the send or receive queue number/tune the table ?

SQL server startup parameters

Posted: 04 Sep 2013 05:05 AM PDT

Hi,i'm trying to find the startup parameters for sql server 2008 and 2005 using t-sql...for sql 2008 R2 i was able to get the information using the below script......SELECT * FROM sys.dm_server_registry Please let me know if there is any t-sql script to find the start up parameters....i would like to avoid the gui...Thanks in advance...

Same query, two users, different performance

Posted: 04 Sep 2013 06:14 AM PDT

Hey Gurus,I have a query that performs differently depending upon the user executing it. This was first brought to my attention because an Excel spreadsheet was taking a long time to load data (Excel 2010, Sql Server 2008 R2). The query was awful, I rewrote it and got better performance. But when the user tried running it from Excel, still had bad performance. After a bit of head scratching I saw that the connection from Excel had a username/password specified, whereas when I ran it from SSMS I was logging in using my username via windows integrated security (and I am dbo). So, I tried a few things:1. In two windows in the same SSMS, run the query simultaneously. One as me, the other using "Execute as Login =".2. Start up two instances of SSMS. Connect in one as me, in the other as the specified login.In any case, when I run the query as me, it takes about 7 1/2 minutes. When I run as the specified user, it takes 12+ minutes (today's measurements. Other days when I run as me I can have it run in as little as 2 minutes, but these times have been consistent today).In any case, I am always executing the exact same statement (SELECT * FROM a table valued function) with the same parameters. The only difference is "who" is logged in.Why would two different user logins have different performance profiles? What should I be looking at to track this down?Thanks in advance.Tom

Insert Temp Table help.

Posted: 04 Sep 2013 06:54 AM PDT

I have a query that "Mickey" on here helped me with that I made some changes and it works wonderfully for what we need. I want to create a associated Zip Code for my cpt codes just as I have given them a Geography lat/long. My brain must be drained or I just cant think straight because I cant figure it out and I bet its super simple:crazy:The Geography query below works like a charm. I would like to duplicate it however with the following modification.CREATE TABLE #TempLocation (Code Varchar(5) PRIMARY KEY,Location Varchar)INSERT INTO #TempLocationVAlUES ('OR001', ------this is where I'm loosing it, I want to assign this a Zip Code like I have below with Geography points)---The one below this comment works very wellCREATE TABLE #TempLocation (Code Varchar(5) PRIMARY KEY,Location Geography) INSERT INTO #TempLocation VALUES ('OR001',Geography::Point(35.136195,-106.658707, 4326)) INSERT INTO #TempLocationVALUES ('OR002',Geography::Point(35.083956,-106.688232, 4326))INSERT INTO #TempLocationVALUES ('OR003',Geography::Point(35.105305,-106.5193182, 4326))INSERT INTO #TempLocationVALUES ('CS001',Geography::Point(35.132264,-106.712952, 4326))INSERT INTO #TempLocationVALUES ('CS002',Geography::Point(35.132260,-106.712951, 4326))INSERT INTO #TempLocationVALUES ('CS003',Geography::Point(35.132262,-106.712951, 4326))SELECT tl.Location ,[Visit: Visit Type] ,[Visit: Visit ID] ,[Procedure: Code] ,[Date: Service date of the Charge] ,[Insurance: Charge Primary Ins. Plan] ,[Patient: Age] ,[Patient: First Name] ,[Patient: Last Name] ,[Patient: Patient ID] ,[Patient: Patient Name] ,[Patient: Preferred Provider] ,[Patient: Sex] ,[Patient: Zip Code] ,[Patient: Date Of Birth] ,[Patient: Address Line 1] ,[Patient: Middle Initial] ,[Patient: Phone Primary] ,[Patient: Race] ,[Patient: SSN] ,[Patient: State] ,[Procedure: Description] ,[Provider: Billable] ,[Provider: Rendering] FROM [PrimeRecord130010].[dbo].[vCHCCharges] v JOIN #TempLocation tl ON v.[Procedure: Code] = tl.Code --Where [Date: Service date of the Charge] between '01/01/13' and '06/01/13'GODROP TAble #TempLocation

Caclulating two-tailed Student's t-distribution

Posted: 04 Sep 2013 01:58 AM PDT

I would like to calculate the two-tailed Student's t-distribution from within T-SQL. Excel does this with it's TINV() function. I want to be able to pass it the degrees of freedom and spedify the probability level and get returned the t-distribution value. Is there some way of either calling the excel function or recreating it in T-SQL?TIA,Jon

Executa a Job When Replication ends

Posted: 04 Sep 2013 06:29 AM PDT

Grettings,I need to know if I can execute a Job when a replication ends but by a automatic way. It doesn't means I must set a shedule to this Job.Before somebody ask me WHY?, I need this because I replicate temporaly tables, after that, I need to put those rows on a production table but I must insert on production table when the data is ready.I 'll be waiting for suggestions.Thanks.

sql 2008 r2 cluster installation failed

Posted: 04 Sep 2013 02:27 AM PDT

While installing sql 2008 r2 cluster ,it failed with below error."microsoft sql server 2008 r2 cluster setup attempted to perform an unauthorized operation"TITLE: Microsoft SQL Server 2008 R2 Setup------------------------------The following error has occurred:Attempted to perform an unauthorized operation.Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.After clicking cancel the "Database Engine" and "Replication" failed and reaming feature are installed sucesfullyLog FileOverall summary: Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup. Exit code (Decimal): -2068119551 Exit facility code: 1211 Exit error code: 1 Exit message: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup. Start time: 2013-01-25 10:59:08 End time: 2013-01-25 11:13:30 Requested action: Install Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130125_105747\Detail.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0x6121810A%400x92D13C14Please suggest

Update Script to handle databases where Inactive

Posted: 03 Sep 2013 08:08 PM PDT

Hi, I am looking to find script that works for updates to handle databases which have been Inactive. basically if databases are offline that will go in active and if we apply new enhancements that needs come to active.If any one can help will be Appreciate.Fella@@@Attitude Builds The Trust@@@

TSQL QUERY HELP

Posted: 04 Sep 2013 01:15 AM PDT

Can anybody help with the following query:Basically its returning 100% when all records are populated and 0% when only some are populated ![code="sql"] select coll, count(case when coll is not null and value is not null then 1 end) CntCol, count(case when coll is not null and value is not null then 1 end) / count(coll)*100.0 Percentagefrom( select 'AltPhone' coll, AltPhone value from ABC_User_CustomProfile union all select 'lastname' coll, lastname value from ABC_User_CustomProfile union all select 'staffid' coll, staffid value from ABC_User_CustomProfile) srcgroup by coll[/code]

Trimming column values in place (without temporary tables, etc)

Posted: 04 Sep 2013 01:11 AM PDT

I have four tables containing the regular chars and varchars. Unfortunately, no one bothered to trim the values of white space before inserting them into the columns. Is there a way to ltrim(rtrim()) in place without resorting to temporary tables or variables?I guess I could use a cursor but there has to be a better way.Thanks!John

sqlcmd and XML ON

Posted: 04 Sep 2013 12:47 AM PDT

I've been given a script that is similar to following. This script is saved out to a network share and called from a SQL job using sqlcmd. I am not at all familiar with sqlcmd and this :XML ON (also this :OUT command). What I ultimately need to do, is dynamically change the file name and append the date to it. Here is a modified version of the script:Use [DB1]GO:XML ON :OUT \\fileserv1\customer\XML\getCompScores.xmlSELECT loginname ,substring(convert(VARCHAR, grade), 1, 4) AS 'Grade' ,date_submittedFROM OrganizationLogin AND convert(VARCHAR, date_submitted, 101) = CONVERT(DATE, DATEADD(DD,-1,GETDATE()))FOR XML raw ,root('Accounts') ,elementsGO:out stdout :XML OffLike I mentioned previously, I just need to be able to dynamically change getCompScores.xml to be getCompScores_20130903.xml.Any and and all help will be greatly appreciated.

Merge_Replication_Tables_Not_In_Sync(MSmerge_tombstone)

Posted: 03 Sep 2013 11:05 PM PDT

We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it is a production issue.Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)

Reporting Services

Posted: 03 Sep 2013 07:48 PM PDT

Two Questions about reporting services1) Is it possible to rename the ReportServer database? I have just tried but change it as part of a migration, but then the reporting services was referencing objects within the database with the old database name. I guess all objects would also need to be changed if we were going to do this or is there any easier way?2) Changing the service account for report services. I have inherited some servers which use Network Service account for reporting services. I prefer using AD accounts for all SQL Services. Where would I need to add this group to to enable reporting services to start up?

comparision

Posted: 03 Sep 2013 09:10 PM PDT

Hi,I have a table with column name is[u]proposaldata[/u]123456542213and other table column is [u]proposaldata[/u]000123456000542213 so i need to compare to this columns of different table s but 123456 and 000123456 should treated as same so wat i will do

SQL 2008 R2 Replication with database/table collation change

Posted: 03 Sep 2013 09:44 PM PDT

I have a transactional replication setup with a database with a collation of Arabic_CS_AS however, when setting up the subscriber the collation is the standard SQL_Latin1_General_CP1_CI_AS collation. The affect is that certain columns in the database are now garabage characters.Is there a way to modify the subscriber database collation as the publication?
Posted by at No comments:

Wednesday, September 4, 2013

[SQL Server] Case statement

[SQL Server] Case statement

Case statement

Posted: 03 Sep 2013 05:38 PM PDT

Please help to write the SQL query in a more cleaner way...CASE ISNULL(TCBOV.cboValueName, '') WHEN '' THEN '' ELSE TCBOV.cboValueName END Thanks
Posted by at No comments:

[how to] Problem using merge into a remote table over dblink

[how to] Problem using merge into a remote table over dblink

Problem using merge into a remote table over dblink

Posted: 04 Sep 2013 08:02 PM PDT

I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3.

We already have a similar existing merge that works fine (but with the query running on server A and merging into a table on server B) so I'm a bit flummoxed as to what could be causing the problem.

I've reduced the case to the simplest - having identical tables and using the entire content of the local table for the merge, and still the merge fails with ORA-02064 "distributed operation not supported":

MERGE INTO attendance@dblnk tgt  USING (    SELECT *    FROM attendance    -- WHERE TRUNC(in_date) = TO_DATE('01.09.2013', 'DD.MM.YYYY')  ) src  ON (tgt.emp_no = src.emp_no AND tgt.in_date = src.in_date)  WHEN NOT MATCHED THEN    INSERT (emp_no, in_date, out_date)    VALUES (src.emp_no, src.in_date, src.out_date)

Any idea what I should be checking next? Many thanks in advance

Software for working with SQL Server databases in Windows Server 2008

Posted: 04 Sep 2013 08:41 PM PDT

We have an ASP .NET project that uses SQL Server that we host on a Windows Server 2008 machine. What software can I use to interact with this database manually? I'm willing to use built-in tools and command line tools. (Command line tools would even be preferable.)

Export from phpMyAdmin and import later from another phpMyAdmin fail

Posted: 04 Sep 2013 03:46 PM PDT

I have a MySQL Server and a phpMyAdmin in a server at office, I exported a database using the phpMyAdmin export feature. I come home and try to import the exported .sql and get this error:

-- -- Constraints for table product_detail_has_category -- ALTER TABLE product_detail_has_category ADD CONSTRAINT fk_product_detail_has_category_category1 FOREIGN KEY ( category ) REFERENCES category ( id ) ON UPDATE CASCADE , ADD CONSTRAINT fk_product_detail_has_category_product_detail1 FOREIGN KEY ( detail ) REFERENCES product_detail ( id ) ON UPDATE CASCADE ;

MySQL said: Documentation

1452 - Cannot add or update a child row: a foreign key constraint fails (kraken.#sql-6f3_141, CONSTRAINT

fk_product_detail_has_category_category1 FOREIGN KEY (category) REFERENCES category (id) ON UPDATE CASCADE)

what should I do in order to import the file without problems in my server?

MySQL randomly refuses to use index

Posted: 04 Sep 2013 09:00 PM PDT

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:

# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';

This is a MYISAM table with lead_id as primary key

| Field         | Type             | Null | Key | Default     |Extra                   |  | lead_id       | int(9) unsigned  | NO   | PRI | NULL        | auto_increment         |

The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?

mysql> show create table dial_list\G
************* 1. row *************
Table: dial_list
Create Table: CREATE TABLE dial_list (
lead_id int(9) unsigned NOT NULL AUTO_INCREMENT,
entry_date datetime DEFAULT NULL,
modify_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
status varchar(6) DEFAULT NULL,
user varchar(20) DEFAULT NULL,
vendor_lead_code varchar(20) DEFAULT NULL,
source_id varchar(50) DEFAULT NULL,
list_id bigint(14) unsigned NOT NULL DEFAULT '0',
gmt_offset_now decimal(4,2) DEFAULT '0.00',
called_since_last_reset enum('Y','N','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9','Y10') DEFAULT 'N',
phone_code varchar(10) DEFAULT NULL,
phone_number varchar(18) NOT NULL,
title varchar(4) DEFAULT NULL,
first_name varchar(30) DEFAULT NULL,
middle_initial varchar(1) DEFAULT NULL,
last_name varchar(30) DEFAULT NULL,
address1 varchar(100) DEFAULT NULL,
address2 varchar(100) DEFAULT NULL,
address3 varchar(100) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
state varchar(2) DEFAULT NULL,
province varchar(50) DEFAULT NULL,
postal_code varchar(10) DEFAULT NULL,
country_code varchar(3) DEFAULT NULL,
gender enum('M','F','U') DEFAULT 'U',
date_of_birth date DEFAULT NULL,
alt_phone varchar(12) DEFAULT NULL,
email varchar(70) DEFAULT NULL,
security_phrase varchar(100) DEFAULT NULL,
comments varchar(255) DEFAULT NULL,
called_count smallint(5) unsigned DEFAULT '0',
last_local_call_time datetime DEFAULT NULL,
rank smallint(5) NOT NULL DEFAULT '0',
owner varchar(20) DEFAULT '',
entry_list_id bigint(14) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (lead_id),
KEY phone_number (phone_number),
KEY list_id (list_id),
KEY called_since_last_reset (called_since_last_reset),
KEY status (status),
KEY gmt_offset_now (gmt_offset_now),
KEY postal_code (postal_code), KEY last_local_call_time (last_local_call_time),
KEY rank (rank),
KEY owner (owner),
KEY phone_list (phone_number,list_id),
KEY list_phone (list_id,phone_number),
KEY list_status (list_id,status)
) ENGINE=MyISAM AUTO_INCREMENT=35830810 DEFAULT CHARSET=latin1

Publishing database changes with SSDT that include altering data types of columns

Posted: 04 Sep 2013 02:04 PM PDT

I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the publishing fails with the error

(49,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur. (44,0): SQL72045: Script execution error. The executed script: /* The type for column QuantityReceived in table [dbo].[Reconciliation_Receiving] is currently INT NOT NULL but is being changed to DECIMAL (18, 4) NOT NULL. Data loss could occur. */

IF EXISTS (select top 1 1 from [dbo].[Reconciliation_Receiving]) RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT An error occurred while the batch was being executed.

I understand why I'm receiving that error, and I know it could be resolved by disabling the "Block Incremental Deploy if Data Loss May Occur" flag. However, there's very strong opposition to disabling that feature, so it's not going to be an acceptable solution.

The only other solution I can think of is to do the following:

  1. Make a temporary table and copy the existing table's contents into the temporary table
  2. Truncate the existing table
  3. Let SSDT update the data type
  4. Fill the data back in from the temporary table

That seems horribly clunky and inefficient, though.

Is there a better alternative?

Error On Collection_set_2_upload, SQL Server 2008 R2

Posted: 04 Sep 2013 11:29 AM PDT

The following error appears in the Job history Log in SQL Server

An error occured while trying to access the cache directory: "D:\". Inner Error ------------------> Access is denied. Process Exit Code 4. The step failed.,00:00:00,0,0,,,,0

Has any one experienced this error before?

This is occuring during MDW collection_set_2_upload

Because of this the database log Becomes full.

Any suggestions?

Thanks!

before insert trigger for insert duplicate rows into another table

Posted: 04 Sep 2013 04:14 PM PDT

I have a table called tblspmaster in which sp column i have unique index so there will be no duplicates will be inserted, but i want to insert duplicate rows into tblspduplicate . so i decided to write trigger for this . IN master table which is tblspmaster records will be inserted using Load File of mysql 

create trigger tblspmaster_noduplicate  before insert on tblspmaster  for each row  begin    if ( select count(sp) from tblspmaster where sp=new.sp > 0 )then      insert into tblspduplicate (sp,FileImported,AMZFileName)   values (NEW.sp,NEW.FileImported,NEW.AMZFileName)    END  END

I have list of questions

  1. Is this right approach to stop duplicates and insert into another table ?

  2. My trigger is not executing as its showing some syntax errors

ERROR response is Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END END' at line 7

MySQL looking up more rows than needed (indexing issue)

Posted: 04 Sep 2013 04:15 PM PDT

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows:

+----------------+-------------+-----------------+--------+  | participant_id | question_id | given_answer_id | status |  +----------------+-------------+-----------------+--------+  |       500      |      12     |        25       |    0   |  +----------------+-------------+-----------------+--------+

The combination participant_id + question_id + given_answer_id is unique.

At the moment we have following keys:

PRIMARY KEY (in this order)

  • participant_id
  • question_id
  • given_answer_id

INDEX KEY

  • question_id

For this table, we have two kinds of select queries in our application:

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

and

[...] WHERE question_id = x;

Normally, every participant_id has between 0 and <100 rows with different question_ids. The other way around, every question_id can have unlimited (usually not more than 100 000) rows with different participant_ids. The first query is executed way more often than the second one.

When we execute following query, it shows us that 32096 rows where looked up:

EXPLAIN SELECT * FROM example WHERE question_id = 500;    +----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+  | id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows  | Extra |  +----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+  | 1  | SIMPLE      | example   | ref  | question_id   | question_id | 8       | const | 32096 |       |  +----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+

Whereas, when we execute the same query without EXPLAIN only 18732 rows are returned.

What indexes do we need on this table in order to prevent this overhead but still performing for both kinds of queries?

Here's is the code to create this table:

  CREATE TABLE `example` (      `participant_id` BIGINT(20) UNSIGNED NOT NULL,      `question_id` BIGINT(20) UNSIGNED NOT NULL,      `given_answer_id` BIGINT(20) UNSIGNED NOT NULL,      `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',      PRIMARY KEY (`participant_id`, `question_id`, `given_answer_id`),      INDEX `question_id` (`question_id`)  )  ENGINE=InnoDB;

Data structure for storing synonyms

Posted: 04 Sep 2013 10:50 AM PDT

Hello all I am new to this forum and also fresher in computer science industry so this question may seem unstructured to you. I am asking this question as i have to include it in my project. Even if you could not help me by exact answers i request you to show me path to reach to my answer.

Question

I am in process of finding self evolving data structure for alignment of Terms and phrases (synonyms under some rules) . For example mobile and cell will be synonyms if the query fired to find synonyms of mobile will also include some calling related part (Rule will be calling here) . Neither meaning of Mobile and cell can also be different. Mobile can also be also mobile library , mobile hospital etc. and cell can be biological term.

Work by Me

I have explored somewhat NOSQL and neo4j graph database . They are indeed great but was unable to find any useful concepts that can relate to my above question.

Thanks in Advance . Any help would be appreciated

Best way to handle temporary data

Posted: 04 Sep 2013 10:56 AM PDT

Say I have a table prices. Users can see the prices of this table (I'm simplifying it). Users also can send prices to the server, so the server is going to collect these data in another table (temp_prices).

When the server detects that there are 50 prices or more, then the server calculates a median of those temporal prices and update the table prices with the calculated value (again, very simplified).

The first idea that came to my mind was have two tables with the same schema but different names (prices and temp_prices).

I don't know if this is a good/common practice. I am missing something? Also, what mechanism should I use to trigger the server to inspect the table temp_prices? I think about cron but again, I don't know if there is something I'm missing (another tool or common practice for doing this kind of stuff).

Thanks.

Server Wide DDL Trigger Permissions Issue

Posted: 04 Sep 2013 11:03 AM PDT

I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here

The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message.

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19  The user does not have permission to perform this action.

I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue.

Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS?

EDIT: Some further info

The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows:

CREATE TRIGGER LogDDLEvent  ON ALL SERVER  FOR DDL_EVENTS  AS    DECLARE     @eventInfo XML  SET         @eventInfo = EVENTDATA()    INSERT INTO Tools.audit.DDLEvent  VALUES  (        REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName        , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName        , (SELECT   CAST(client_net_address AS VARCHAR(128))              FROM  sys.dm_exec_connections              WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType        , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand        , @eventInfo -- DDLEventXML  )

The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above.

After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself.

The ordinal position for a column changed without my knowledge

Posted: 04 Sep 2013 10:00 AM PDT

I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way.

I've been researching this for quite some time and am coming up empty. I also could not reproduce it in testing.

I have informed the developers that they are not allowed to run queries that do not explicitly name columns, for exactly this reason.

In the mean time, though, it's driving me crazy that I have no idea how we came to this state of affairs.

How to avoid locks by large transactions?

Posted: 04 Sep 2013 10:03 AM PDT

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application.

Basically, the delete is executed from a stored procedure, which first does a couple of selects to define the data to be deleted, after which it starts deleting from different tables. As the link between this data is essential for the deletion, it has to be avoided to delete for example and order without deleting some dependencies. Therefore it has to run in one transaction.

Problem is, whenever the script is running, the application itself become not unusable: timeouts when getting data from the web, or trying to update a certain record. Al those queries are blocked by the sessions that runs the transaction

The data being deleted is not relevant anymore, and should thus not be updated by the application.

I've tried running the transaction in different isolation levels, including snapshot, but it still doesn't work.

How can I avoid these locks? Should I use READ_COMMITTED_SNAPSHOT?

Thanks in advance...

Securely remove columns (and traces thereof) from a SQL database

Posted: 04 Sep 2013 09:47 AM PDT

I've come upon a database that has previously held sensitive data (think "passwords/credit-card-numbers" kind of sensitive) in plain text. All of the sensitive data in question has been deleted, but I'm concerned that if accessed by someone with malicious intentions, the data might be recoverable through backups and/or transaction logs.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?

picking consecutive or sequential number in a resultset

Posted: 04 Sep 2013 08:07 AM PDT

I have a mySQl database containing tables that contain all of the possible 5 number combinations for the take 5 lottery (pick 5 numbers: 1-39), organized by the sums of the 5 number combinations. EX. { (sum table: 15 - 1,2,3,4,5), (sum table: 17 - 1,2,3,4,7 | 1,2,3,5,6)} ... FOR SUMS 15 THROUGH 185.

I need a way to be able to filter my queried results to those results that contain at least two sequential numbers for a selected sum table. Ex. For sum 17, this would be: RESULT: 1,2,3,4,7 | 1,2,3,5,6
1,2 or 2,3 or 3,4 1,2, or 2,3, or 5,6

First I query the table for everything. using a query of a query I query the above resultset for rows containing numbers that are consecutive or sequential.

I need to put some kind of code or regular expression in the query to select just those rows containing 2 or more consecutive numbers.

the table is organized as follows: col: num1 to num 5 as integers and are currently in numerical order ascending the tables have an auto increment and a row ID as a primary key, and an regular index on the entire row.
There are no duplicates sets in the table. Ex. 12345, 21354, 54321, etc... would all be considered the same set of numbers since they all add up to 15, so only one of these would exist in the table.

Q) Is it possible to use a regular expression? If not what is the best solution vector?

Data Gateway for ODBC Connect Problems with ORA-28513

Posted: 04 Sep 2013 07:54 AM PDT

We try to connect from an Oracle 11g (11.2.0.3.0) data base server to a MS-SQL Server running on some other server. Our machine has Windows 2008 R2 Enterprise 64 Bit.

We use dg4odbc for our link to MS-SQL, and configured it as follows:

Before anything, we ran the script @ORA_HOME\RDBMS\ADMIN\caths.sql as a SYSDBA. Next we configured the ODBC data source with the program odbcad32.exe located in the system32 folder of the windows system (we also tried odbcad32.exe from the sysWOW64 folder) and named it MSQL_ODBC.

As a thrird step we created a new file for our heterogenous service (dg4odbc) and added a suitable configuration to Oracle's listener and TNS config files.

The configuration of our ODBC data source for dg4odbc is contained in a file named ORA_HOME\hs\admin\initMSQL_ODBC.ora and looks like this

HS_FDS_CONNECT_INFO = MSQL_ODBC  HS_FDS_TRACE_LEVEL = Debug  HS_FDS_TRACE_FILE_NAME = ORA_HOME\hs\trace\MSQL_ODBC.trc

Our ORA_HOME\network\admin\Listener.ora contains the definition of a listener for the data gateway (dg4odbc). The entry we added is

(SID_DESC =      (SID_NAME = MSQL_ODBC)      (ORACLE_HOME = ORA_HOME)      (PROGRAM = dg4odbc)  )

Also in this directory we edited the tnsnames.ora to contain an entry for the ODBC connection which looks like this

MSQL_LNK =  (DESCRIPTION =      (ADDRESS =          (PROTOCOL = TCP)          (Host = LOCALSVRNAME)          (Port = 1521)      )      (CONNECT_DATA =          (SID = MSQL_ODBC)          (SERVICE_NAME = MSQL_ODBC)      )      (HS=OK)  )

We receive an error message when we try to execute a select statement (we only tried to select so far) which also creates a trace file in the ORA_HOME\hs\trace folder. The error message is

ORA-28513: internal error in heterogeneous remote agent  ORA-02063: preceding line from MSSQL_LINK

and the log file contains the following lines

Oracle Corporation --- MITTWOCH   SEP 04 2013 16:49:04.007  Heterogeneous Agent Release   11.2.0.3.0   Unable to open init file initMSQL_ODBC.ora in directory ORA_HOME/hs/admin

We already checked all file access rights of some users, but can not single out the real reason of this problem. Any hints are appreciated.

How to track if employees are late which has more than one work schedules or shifts?

Posted: 04 Sep 2013 05:43 PM PDT

I have designed a MySQL database for an employee monitoring system, it was working perfectly well until I was asked to put a new work schedule for some of the employees.

The new schedule was what they called a broken schedule because an employee would have different shifts in one day, for example, an employee could be given a work schedule for a given day starting from 7:00AM-11:00AM and then continues at 3:00PM-7:00PM...

The problem I encountered now is, I am confused on how to keep track if the employees are late of not now that some employees have two or more work schedules...

Could anyone help me pls?

addendum:

for more clarity, lets say we have employee A. employee A for example has the following work schedule for a given day - 5 days in a week.
7:00AM-11:00AM - shift 1
3:00PM-7:00PM - shift 2
9:00PM-11:30PM - shift 3

then, lets say employee A logged in at these different scenarios:
scenario #1: logged in at 7:10AM
scenario #2: logged in at 2:30PM

how do I check that employee A's log info at scenario #1 is not referencing shift2 or shift3?
likewise, how do I check if employee A's log info at scenario #2 would only reference shift2 only and would not make him/her late at his/her schedule at shift #1 or too early at shift #3?

Data Base Testing [on hold]

Posted: 04 Sep 2013 08:07 AM PDT

We have 5000+ Dbs (on MySQL - Unix Machine) each having similar 30-40 tables. We are performing Migration activity from one machine to another (again same on MySQL - Unix Machine).

Could any one suggest best possible solution to validate all the data is migrated properly. Also what other things should we consider while testing this activity?

Thanks in advance!

-- C.F.

Configure Email to be sent on restoring a database

Posted: 04 Sep 2013 11:10 AM PDT

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.

Looking for a database-design to model a availability problem (large data sets are expected) [on hold]

Posted: 04 Sep 2013 08:24 AM PDT

I am looking for a database-design to store and query information about disposability of cars in a to be designed care-sharing-community, where users can rent cars provided by other users. There will be a (proximity) search which should only show all available cars.

I'll expect to have following data: Car data with general availability - set by the car owner. Rent contracts for a specific or recurring date/time, which reduces the availability.

Here some example queries that should be possible:

  1. get all cars available now
  2. get all cars available tomorrow from 8am to 2:30pm
  3. get all cars continuously available from 2013-10-01 until 2013-10-30
  4. get all cars available each Tuesday to Thursday from 10am to 6pm from 2013-11-01 until 2013-11-24

No matter if SQL or NoSQL is used for the model, any ideas are welcome. Important: The database-design should scale well, as large data sets are expected.

SQL Query too slow on SUM function

Posted: 04 Sep 2013 02:18 PM PDT

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is

SELECT  f.RetailerID  ,       TypeID                                AS TypeID  ,       c.CalendarMonth                         AS CalendarValue                      ,       SUM(ISNULL([Volume],0)                  ) AS Volume  ,       SUM([VolumeBox]                         ) AS VolumeBox  ,       SUM([VolumeKG]                          ) AS VolumeKG  ,       SUM([VolumeUnit]                        ) AS VolumeUnit  ,       SUM(ISNULL([R_Turnover] , 0.0)          ) AS R_Turnover  ,       SUM(ISNULL([R_VAT],0.0)                 ) AS R_VAT  ,       SUM(ISNULL([R_TurnoverExVAT],0.0)       ) AS R_TurnoverExVAT  ,       SUM([SupplierRealisation_Amt]           ) AS [SupplierRealisation_Amt]  ,       SUM([SupplierDiscount1_Amt]             ) AS [SupplierDiscount1_Amt]  ,       SUM([Supplier_1NetSales_Amt]            ) AS [Supplier_1NetSales_Amt]  ,       SUM([SupplierDiscount2_Amt]             ) AS [SupplierDiscount2_Amt]  ,       SUM([Supplier_2NetSales_Amt]            ) AS [Supplier_2NetSales_Amt]  ,       SUM([SupplierDiscount3_Amt]             ) AS [SupplierDiscount3_Amt]  ,       SUM([Supplier_3NetSales_Amt]            ) AS [Supplier_3NetSales_Amt]  ,       SUM(ISNULL(S_CostofGoodsSold, 0)        ) AS [S_CostofGoodsSold]  ,       SUM(ISNULL(S_Profit, 0)                 ) AS S_Profit  ,       SUM(0.0                                 ) AS AdditionalCostofGoodsSold  ,       SUM(ISNULL([R_DistributionCost],0.0)    ) AS R_DistributionCost  ,       SUM(ISNULL([R_Profit],0.0)              ) AS R_Profit  FROM [dbo].[EventScenarios] es   JOIN dbo.[Event] e ON es.[EventID] = e.ID    JOIN dbo.EventProduct ep on es.EventID = ep.EventID   JOIN [dbo].Product p ON p.ID=ep.ProductID   JOIN dbo.EventPL f  ON  e.CountryID = f.CountryID AND f.RetailerID = e.RetailerID AND f.EventID = e.ID  AND ep.ProductID = f.ProductID    INNER JOIN Calendar c   ON  c.DateID = f.DateID     WHERE  f.CountryID= 14  AND c.CalendarMonth BETWEEN 201301  AND 201312   GROUP BY f.RetailerID , c.CalendarMonth ,TypeID

The query plan is showing 88% time on Clustered Index seek on EventPL table but still its taking around 15 seconds to complete. Is there any way I can optimise it to around 1/2 seconds?

Selecting from an excel spreadsheet into SQL Server table

Posted: 04 Sep 2013 11:18 AM PDT

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express).

So for the following code snippet:

Declare @strSQL as varchar(200)  declare @file as varchar(200)     SET @file='C:\temp\file.xlsx'  SET @strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'  SET @strSQL=@strSQL+N'SELECT * FROM ##mytemptable'  PRINT @strSQL  Exec (@strSQL)

EXPANDED SQL STATEMENT

SELECT *   INTO #mytemptable   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\file.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');    SELECT * FROM ##mytemptable

I get this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

How do I track why my SQL Azure occupied space pulsates?

Posted: 04 Sep 2013 12:18 PM PDT

This is inspired by this StackOverflow question. I have this code:

SELECT CONVERT(INT, SUM(reserved_page_count) * 8 / 1024) FROM sys.dm_db_partition_stats

for detecting database space consumption and it returns exactly the same result as SQL Azure management portal pie chart. The problem is the number fluctuates greatly without apparent reasons - sometimes it's around 35% and sometimes it's around 54%.

This is kinda worrying - I don't get what happens and the major concern is that we suddenly consume all the space and our service comes to a halt.

I seriously tried to look into sys.dm_db_partition_stats and sys.partitions but they look like a pile of magic numbers to me.

What data should I use to find the root cause of the fluctuations?

Scaling of PostGIS vs MySQL cos/sin calculations

Posted: 04 Sep 2013 05:53 PM PDT

I need to sort database rows according to GPS coordinates (or any other geographical representation).

The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least articles and my own tests indicate so).

So I can either switch to PostgreSQL to use PostGIS which seems to be working excellently, or I can do it mathematically in MySQL:

((acos(sin(latitude * pi() / 180) * sin($latitude * pi() / 180) + cos(latitude * pi() / 180) * cos($latitude * pi() / 180) * cos((longitude - $longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.1515 * 1.609344) km

I'm not asking for a PostgreSQL vs MySQL debate, but simply how the performance of the MySQL expression scales as rows grow compared to using PostGIS/PostgreSQL.

It's a drastic step to switch database system, so the upside better be significant.

MySQL Continue Handler Problems inside a Function

Posted: 04 Sep 2013 08:18 PM PDT

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I still end up with a warning "No data - zero rows fetched, selected, or processed". My code is below, what am I doing wrong? I really want to get rid of this warning :)

DROP FUNCTION IF EXISTS `getCompanySetting`;  DELIMITER |    CREATE FUNCTION `getCompanySetting`(setting_company_id INT, setting_name VARCHAR(255))      RETURNS TEXT  BEGIN      DECLARE setting_value TEXT DEFAULT NULL;      DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;      DECLARE CONTINUE HANDLER FOR NOT FOUND SET setting_value = NULL;        SELECT          value      FROM company_settings      WHERE          company_id = `setting_company_id`          AND          name       = `setting_name`      INTO setting_value;        RETURN setting_value;  END|    DELIMITER ;    mysql> SELECT getCompanySetting(24, 'observers_active');  +-------------------------------------------+  | getCompanySetting(24, 'observers_active') |  +-------------------------------------------+  | NULL                                      |  +-------------------------------------------+  1 row in set, 1 warning (0.00 sec)    mysql> show warnings;  +---------+------+-----------------------------------------------------+  | Level   | Code | Message                                             |  +---------+------+-----------------------------------------------------+  | Warning | 1329 | No data - zero rows fetched, selected, or processed |  +---------+------+-----------------------------------------------------+  1 row in set (0.00 sec)

Update: MySQL Version 5.5.25

Data sharing between client and server two ways

Posted: 04 Sep 2013 09:18 AM PDT

One of my Silverlight applications is running. This Application is using one central database (SQL Server 2008) and three other client databases (SQL Server 2008) in server other locations.

Our requirement is that with minor changing or without changing the Silverlight application, when we make changes in the central database (insert, update and delete in multiple tables) these changes automatically occur in all client databases. And when any change is made in any client database all client as well as central database should be updated automatically.

For this I read some about Sync Framework and SQL Server 2008 Change Tracking. But I have no idea about how will I do this. Please help me. How can we achieve this?

Database table design question

Posted: 04 Sep 2013 03:04 PM PDT

Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able to split a Sales Order Line Item into 2 or more Purchase Orders and a Purchase Order Line Item into 2 or more Shipping Containers.

There will be some(a lot of) adjustments back and forth where once a Sales Order Line Item gets split and put into multiple Purchase Orders it might get split differently later on. And same when a Purchase Order Line Item is processed into Shipping Containers. I can't for the life of me how to best handle this operation.

If it helps, here are the tables I have so far with some details excluded for brevity:

CREATE TABLE PurchaseOrder (      Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,      PurchaseOrderNumber VARCHAR(15) UNIQUE,        PRIMARY KEY(Id)  );      CREATE TABLE Container (      Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,      ContainerName VARCHAR(20) UNIQUE,      PurchaseOrderId INTEGER UNSIGNED,        PRIMARY KEY(Id),      FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),  );    CREATE TABLE SalesOrder (      Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,      ClientId INTEGER UNSIGNED,      SalesOrderNumber VARCHAR(10),        PRIMARY KEY(Id),      FOREIGN KEY(ClientId) REFERENCES Client(Id)  );    CREATE TABLE SalesOrderLineItem (      Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,      SalesOrderId INTEGER UNSIGNED,      ProductId INTEGER UNSIGNED,      Qty INTEGER,      Price DECIMAL(5,2),      Cost DECIMAL(5,2),        PRIMARY KEY(Id),      FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),      FOREIGN KEY(ProductId) REFERENCES Product(Id)  );    CREATE TABLE PurchaseOrderLineItem (      Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,      PurchaseOrderId INTEGER UNSIGNED,      SalesOrderId INTEGER UNSIGNED,      ProductId INTEGER UNSIGNED,      ClientId INTEGER UNSIGNED,      MfgId INTEGER UNSIGNED,        PRIMARY KEY(Id),      FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),      FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),      FOREIGN KEY(ProductId) REFERENCES SalesOrder(Id),      FOREIGN KEY(ClientId) REFERENCES Client(Id),      FOREIGN KEY(MfgId) REFERENCES Mfg(Id)  );

I'm thinking to create additional association tables between Sales Order Line Item and Purchase Orders and for Purchase Order Line Item and Containers to keep track of this kind of back and froth splitting?

Help appreciated!
Posted by at No comments:

[Articles] It's not you; it's us

[Articles] It's not you; it's us

It's not you; it's us

Posted: 03 Sep 2013 11:00 PM PDT

The announcement that the MCM program was being discontinued brings us a few thoughts from Gail Shaw, one of the 0.08% to achieve the certification.
Posted by at No comments:

[MS SQL Server] Kill SQL SPIDs for a certain SPID range, or a Date Range

[MS SQL Server] Kill SQL SPIDs for a certain SPID range, or a Date Range

Kill SQL SPIDs for a certain SPID range, or a Date Range

Posted: 04 Sep 2013 02:40 AM PDT

Hello EveryoneIs there a way to kill SPIDs in a certain SPID range? like all spids 75-100 etc... Or with a date range? Say all SPIDs that are over a day old? I am seeing some stale connections that are not closing.ThanksAndrew SQLDBA

database in simple mode with growing log file into the GBytes

Posted: 04 Sep 2013 12:23 AM PDT

I'd like to get suggestions on how to handle the issue I'm facing. I have a database that sole purpose is to be a staging place for data. Every night thousands of records are re-loaded to transfer new information and refresh the database. I have the database setup to simple mode however the log file grows up to close to 30GB due to the inserts and deletes from the batch scripts. I have a script that shrinks the database log file every day but since the reload is daily is grows up to 30GB again. I'm only responsible for the database maintenance not loading the data however, I'd like to give the people responsible for it a suggestion on how to do it better.What's the best way to handle a process like this?

Merge_Replication_Tables_Not_In_Sync(MSmerge_tombstone)

Posted: 03 Sep 2013 11:07 PM PDT

We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it is a production issue.Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)

Query tuning performance

Posted: 03 Sep 2013 07:19 PM PDT

how can we get execution plan of a query executed on two different days so that i come to know that what is cause of slowing down the query today? i know that query was running fine one week back.what are the steps to find the cause of slowing down the query today which was running very well in past?what could be the possible reason of slowing down the query today which was running very well in past?I have faced this question in interview lot of time could you please help me?

SQLServer 2008 R2 SP 2 confusion

Posted: 03 Sep 2013 06:32 PM PDT

We have three node cluster configuration for SQLserver 2008 R2. We are planning to do upgrade SP 2. We realized that when we did the upgrade on Pre Prod environments our eventviewer started to generate spam message "[sqagtres] CheckServiceAlive: returning TRUE (success)"To overcome that I understand that we need to apply CU 3 after SP2. We decided to take the latest CU (CU 7) at that time. We tried Cu 7 again on the lower environment than Pre Prod. It started throwing errors on change data capture.2013-09-02 02:52:59.16 spid9s DBCC execution completed. If DBCC printed error messages, contact your system administrator.2013-09-02 02:52:59.22 spid9s Unable to upgrade database 'Meta__datat_accounts'. Error '22841' was raised: 'Could not upgrade the metadata for database 'Meta__datat_accounts' that is enabled for Change Data Capture. The failure occurred when executing the action 'sp_cdc_create_change_enumeration_functions'. The error returned was 102: 'line 45, state 1, Incorrect syntax near ')'.'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the reported error to determine the cause of the failure and then execute sys.sp_cdc_vupgrade in the database context to rerun upgrade.2013-09-02 02:52:59.22 spid9s sp_vupgrade_replication executed successfully2013-09-02 02:52:59.22 spid9s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.2013-09-02 02:52:59.22 spid9s Saved upgrade script status successfully.2013-09-02 02:52:59.23 spid9s Database 'master' is upgrading script 'upgrade_ucp_cmdw_discovery.sql' from level 171053246 to level 171053250.I don't see any abnormal activity in the server. My question 1. Will that be a problem in if I upgrade my prod environments?2. What happens If I leave the eventvwr been spammed with messages? 3. When is SP 3 for SQLserver 2008 R2 coming?Can you please help me?FYI. We tried with CU 8 also same result. Thank you.
Posted by at No comments:

[SQL 2012] How to tell if instance is MSDN licenced or not

[SQL 2012] How to tell if instance is MSDN licenced or not

How to tell if instance is MSDN licenced or not

Posted: 04 Sep 2013 12:27 AM PDT

Hi,I've been evaluating SQL Server 2012 for a while now and have started to use it in production. I am concerned that my evaluation install media (MSDN) has escaped into the production environment, but don't know how to check for sure. Is there a property or something to indicate which license key was used to install the instance so I can check where they've all come from?Thanks

SSIS 2012 Log Provider for SQL Server displays progress error when logging messages using shared project OLE DB connection

Posted: 20 Jun 2013 03:58 PM PDT

Hi AllI am building a SSIS project using the Project Deployment model of SSIS 2012. I am configuring package level logging using the SSIS log provider for SQL Server and using a [i]project[/i]-level connection in the configuration. The [i]project[/i]-level connection has a project.param of the connection string.The log events are being correctly logged to SQL Server however the Progress information tab indicates two duplicate errors that the project-level connection used for the log provider cannot be found."Error: The connection "xxxx_SSIS_Logging" is not found. This error is thrown by Connections collection when the specific connection element is not found."If I use a [i]package[/i]-level connection in the SSIS log provider, the log events are correctly logged and there are no connection errors in the Progress information tab.Is anyone else observing this behaviour with project-level connections in the SQL Server log provider?Has anyone had failure to log events due to a project-level connection in the SQL Server log provider?

SSIS 2005 --&gt; SSIS 2012 (ActiveX Script gone?)

Posted: 12 Nov 2012 10:58 PM PST

Hello everybody,I need someone to point me in the right direction:We are in the process of converting SSIS packages from 2005 to 2012. Some of them have ActiveX scripts in them and when converting them over they fail. (I have no experience with SSIS yet).I see there is a Script Task in SSIS 2012 and I am thinking I have to rewrite the script in .NET? Is this the only option?My question is what options (I am sure there is more than the above mentioned one) I have to upgrade ActiveX Scripts? Time is realy tight too. :-)Thank you!

SQL Server Full to Simple to Full Recovery Model

Posted: 03 Sep 2013 05:26 AM PDT

Whenever I have a huge log file for any database with Full as its recovery modelThis is what I do to recover disk space...I change the recovery to simple..then shrink the file and change it back to full.What exactly is happening here ?Is this a recommended process?

Using WMI alert for mirroring in Sql server 2012

Posted: 03 Sep 2013 06:28 AM PDT

We are using mirroring in Sql Server 2012 and we are using WMI alerts for some of events such as Connection lost,Manual failover,...In alert definition we have set the followings:Type: WMI event alertNameSpace: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVERQuery: select * from DATABASE_MIRRORING_STATE_CHANGE where State = 6The alert is bing triggered correctly,but on email description instead of having meaningful explaination like 'Connection lost',We have the same query:Query: select * from DATABASE_MIRRORING_STATE_CHANGE where State = 6We have used the same implementation in our Sql Server 2008 servers and it is working fine.This mashine (Sql Server 2012) is a new mashine.I would appreciate if someone help me on this.AlsoI checked and 'Include body of e-mail in notification message' was already set.ThanksAspet
Posted by at No comments:

[T-SQL] Help with this query (duplicate records)

[T-SQL] Help with this query (duplicate records)

Help with this query (duplicate records)

Posted: 04 Sep 2013 12:45 AM PDT

Hi all, i have this data: [code="sql"]DECLARE @data TABLE (tesplan_id int,tesplan_name varchar(250),tc_external_id int,tcversions_id int,tc_name varchar(250),estimated_time int,is_numeric int,ticket int,name varchar(150),prefix varchar(50),login varchar(50),Status char(1),EXECUTION_TS datetime)INSERT @dataSELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','p','2013-08-23 10:38:54.000' UNION ALLSELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','f','2013-08-22 16:54:43.000'select * from @data[/code]I want to stay with the last record executed (EXECUTION_TS), y mean with the '2013-08-23 10:38:54.000'.Thanks for the help!.

Converting GMT to EST

Posted: 03 Sep 2013 04:03 AM PDT

I need to write a function that converts a time from GMT to EST taking daylight savings time into consideration. On a high level I was thinking of doing the following:a) a table that holds the daylight saving dates for this year and upcoming years. does anyone know a website that has these dates published?b) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 11/2013) but less than the spring daylight saving date (i.e., 3/2014) then subtract 6 from the gmt to get estc) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 3/2014) but less than the spring daylight saving date (i.e., 11/2014) then subtract 5 from the gmt to get est

case when looping through column values and applying conditions depending on value

Posted: 03 Sep 2013 10:15 AM PDT

Hi Hi I want to create a sql script which loops through a column (segment) and transforms the value if it meets a certain requirements. For example -In the following table the column 'Segment' contains a string of airportcodes with departurecodes and arrivalcodes. All the legs are separated by '*' in the Segment column. One 'leg' is a pair of departure code and arrival code. The column 'Desired Result' is the one I need to create. Following is the condition.If the arrival code of the first leg is same as the departure code of the second or subsequent leg, then only one of the value is chosen. For example ID =5, SYD is arrival code for leg1 and dept code for leg2, so in the desired result SYD is repeated only once.If the arrival code is different to that of the departure code of the subsequent leg, then the 2 codes needs to be separated by a '***' in between both the codes. So for ID=10, the desired result is 'MEL/SYD/***/BNE/MEL'I tried case statements but it currently hard coded and I need to the script to be dynamic ID Segment Desired result5 MEL/SYD*SYD/MEL MEL/SYD/MEL10 MEL/SYD*BNE/MEL MEL/SYD***/BNE/MEL14 CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG CDG/SIN/SYD***/CBR/SYD/SIN/CDGthis is the code i have so far but it is hard coded.[code="sql"]DECLARE @String AS varchar(50)= 'MEL/SYD*SYD/MEL'DECLARE @str1 AS VARCHAR(50)= (SELECT SUBSTRING(@string,CHARINDEX('*',@String)+1,3))DECLARE @str2 AS VARCHAR(50) =(SELECT SUBSTRING(@string,CHARINDEX('*',@String)-3,3))SELECTCASEWHEN @str1 = @str2 THEN (SUBSTRING(@string,1,7)+ SUBSTRING(@string,CHARINDEX('*',@String)+4,4))--WHEN (condition2)--WHEN (condition3)ELSE NULL ENDfrom Airport [/code]==========Create Table Airport[code="sql"]USE [DW]GO/****** Object: Table [dbo].[Airport] Script Date: 09/03/2013 21:05:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Airport]( [id] [int] NULL, [Segment] [varchar](100) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[Airport] ([id], [Segment]) VALUES (1, N'MEL/SYD*SYD/MEL')INSERT [dbo].[Airport] ([id], [Segment]) VALUES (2, N'MEL/SYD*BNE/MEL')INSERT [dbo].[Airport] ([id], [Segment]) VALUES (3, N'CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG')[/code]Any pointers in the right direction will be really helpful thanks.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog