Wednesday, August 14, 2013

[how to] Using a PostgreSQL tablespace from two different servers

[how to] Using a PostgreSQL tablespace from two different servers

Using a PostgreSQL tablespace from two different servers

Posted: 14 Aug 2013 08:27 PM PDT

I have a PostgreSQL database living on an external hard disk (RAID0 via Thunderbolt). So far I accessed it from a PostgreSQL server running on my MacBook Pro. As my work on this database is getting more and more intensive the queries are getting more and more complex, too. Therefore I'd like to use my brand spanking new iMac from now on.

My question is: Can I somehow tell the new PostgreSQL server (running on the iMac) to use the data that is already living in the tablespace on the external disk? Or will I have to export & import all the data manually (pg_dump, pg_restore)?

Better way to iterating through tables with foreign key

Posted: 14 Aug 2013 08:13 PM PDT

I have a master table (M), and 2 slave tables (S1, S2). S1 and S2 references M through one-to-many foreign keys (F1, F2). Now, I'm writing a PHP (or whatever language) function, to get some records from M, along with information stored in S1 and S2. Basically, there are 2 ways to do it:

  1. Execute a JOIN'ed SELECT statement that returns content of all 3 tables (though creating VIEW will be better, but it's not the point here);
  2. Execute SELECT on M first, then query S1 and S2 for every record in M, in a loop.

This may sound idiot, but I'm thinking about which is the better approach. The concerns are:

  1. If M contains a lot of data, using JOIN will return a whole lot of duplicate data in M, since every record in S1 and S2 will return multiple M records in the output result set;
  2. Running subsequent queries on S1 and S2 incurs significant overhead to process the queries. Although stuffs like query cache and prepared statements improve the performance, but I not sure if it's enough.

How to create a trigger in a different database?

Posted: 14 Aug 2013 07:46 PM PDT

Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides. The databases are on the same server instance. If yes then how?

For example this does not work:

create PROCEDURE [dbo].[CreateTriggrer]    @db varchar(60)  AS  BEGIN    SET NOCOUNT ON;       declare @statement nvarchar(max) = N'CREATE TRIGGER [dbo].[TestTrigger]     ON  [$database$].[dbo].[TestTable]      AFTER INSERT, UPDATE, DELETE  AS   BEGIN    PRINT ''test''  END'        set @statement = REPLACE(@statement,'$database$',@db)      EXEC dbo.sp_executesql @statement = @statement    END

When called like this:

EXEC [dbo].[CreateTriggrer] @db = N'TestDatabase'

It returns this error:

Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 6 Cannot create trigger on 'TestDatabase.dbo.TestTable' as the target is not in the current database.

Which is fair enough. Is there way to achieve what I want?

Oracle: list user-created tables in the SYS schema

Posted: 14 Aug 2013 05:46 PM PDT

I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from pre-existing stock system tables. Any help appreciated.

SQL Server - compare pictures in db as varbinary [on hold]

Posted: 14 Aug 2013 08:00 PM PDT

is possible create stored procedure which will returned similarity ratio of images saved in db as varbinary ? Input parameters could be image id and output near ratio.

This is my db structure

CREATE TABLE [dbo].[Picture]  (      [Id] [int] IDENTITY(1,1) NOT NULL,      [PictureBinary] [varbinary](max) NULL,      [MimeType] [nvarchar](40) NULL,      [IsNew] [bit] NULL,      [SeoFilename] [nvarchar](300) NULL,  )

What happens if the mysql database's innodb log files are lost?

Posted: 14 Aug 2013 07:51 PM PDT

What I did was

/etc/init.d/mysql stop

then removed the files: ib_logfile0, ib_logfile1

then modified my.cnf file, variable: innodb_log_file_size

and then:

/etc/init.d/mysql start

and allowed the files to be recreated

I later discovered that the global variable innodb_fast_shutdown is set to "1"

The question is, how much data was lost ?

Note: I still have the old files ib_logfile0, ib_logfile1, not deleted yet.

And the website relying on the database appears to be working.

Connecting to an external database with pgAdmin III

Posted: 14 Aug 2013 03:40 PM PDT

I'm trying to connect to an external database from pgAdmin III (which is installed on both machines). The client complains:

Access to database denied

The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "192.168.108.161",                                  user "postgres",                              database "postgres",                                   SSL off

To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like 

host all all 192.168.0.0/24 md5

This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24. You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file.

After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

Where the server explicitly states that all connections from the internal network are accepted.

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD  # "local" is for Unix domain socket connections only  local   all             all                                     md5  host    all             all             127.0.0.1/32            md5  host    all             all             ::1/128                 md5  host    all             all             192.168.0.0/24          md5

I have already restarted the postmaster for the changes to take effect, and have gone so far as to restart the entire machine. What else could be going wrong here?

Extract time portion of Datetime2(7) in hhmmssfff format

Posted: 14 Aug 2013 12:38 PM PDT

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:

DECLARE @mdate AS DATETIME2(7)     SET @mdate = '2012-03-15 10:13:27.5437431'    SELECT LEFT(REPLACE(REPLACE(CONVERT(VARCHAR ,@mdate ,114),':',''),'.',''),9)

Is there a better approach than doing this ugly replaces/convert/left?

I need this to join to a DimTime dimension whose key is in hhmmssfff format.

Connect to SQL Server 2012 running on Azure VM via local SSMS 2012

Posted: 14 Aug 2013 01:06 PM PDT

I have been testing VM on Azure. I have created a SQL VM running SQL 2012 on Windows 2012 and would like to connect to it via SSMS 2012 on my local instead of connecting via RDP through Azure Portal.

Thanks!

Retrieve list of matched words in PostgreSQL

Posted: 14 Aug 2013 12:44 PM PDT

I'm new to PostgreSQL and really more than your more basic queries/inserts.

I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in the table.

Now what I'm trying to do is find out what words matched in a query.

If I had the query 

SELECT ts_headline('the big red ball' , 'big & ball');

It would obviously return:

"the big red ball"

Is there a way I can get it to return a list instead so I just see big and red in seperate rows?

Table redirect / filter / trigger on select

Posted: 14 Aug 2013 10:43 AM PDT

Is there any way to redirect queries to different tables / views based on the package that's referencing the table? I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want package A to get the percent grade that's stored in the table, and package B to get a letter grade that's calculated from the percent. I'd like to avoid modifying the (dozens of) packages that reference the table. I'd rather 'spoof' the table somehow if we can, replacing the percent in the grade column with a letter when called from those packages. (The column's varchar2; percents are coded as characters.)

First thought was to create a synonym pointing to a view that massages the column based on the calling package, but the code fully qualifies the table name in most cases so that doesn't seem doable. I went looking for something equivalent to a trigger on select; the closest I've found is fine-grained audit, and it's not row-based.

Is there magic somewhere that might let me do this? Any hints appreciated.

Perry.

View with fallback (performance/optimization question)

Posted: 14 Aug 2013 03:44 PM PDT

I have a table with tariffs for stuff; the tariffs table is not important in this scenario, the "tariff values" are. In this Demonstration SQL Fiddle the tariff_plan is the FK to the tariffplans table (not included in the example). The tariff for each "thing" is the tariff_type (simplified to a simple char for demonstration purposes).

I have, for example, a default tariffplan (key = default); this is the tariff that goes for each customer unless another value is defined for the same tariff_type for that customer. A customer is assigned a tariffplan (key = plan_x in my example).

If have tariffs defined for items a, b, c and d in the default plan. In plan_x I define "override" values for a and c.

So, what I do is I select the default plan (alias p below for primary) and left-join the "override" plan (plan_x) to it (alias s below for secondary):

select *  from tariff_values as p  left outer join tariff_values s       on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')  where (p.tariff_plan = 'default')

This results, as expected, in:

id   tariff_plan tariff_type tariff_foo tariff_bar id   tariff_plan tariff_type tariff_foo tariff_bar  ---- ----------- ----------- ---------- ---------- ---- ----------- ----------- ---------- ----------  1    default     a           0.10       0.20       5    plan_x      a           0.09       0.19  2    default     b           0.55       0.66       NULL NULL        NULL        NULL       NULL  3    default     c           1.99       2.99       6    plan_x      c           0.99       1.99  4    default     d           9.99       6.33       NULL NULL        NULL        NULL       NULL

Because I want to abstract this away I want to put this into a table valued function so I can create a "dynamic view":

select * from dbo.get_tariffplan_for('plan_x', default);

This should result in a "virtual table" (or "dynamic view") similar to the tariff_values table, thus: not having two tariff_foo's and two tariff_bar's and let the application decide which one to use. And so, I resort to ISNULL and Case when... constructs to "override" the default values:

select p.tariff_type,      ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,       ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,      ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,      CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback  from tariff_values as p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')  where (p.tariff_plan = 'default')

This results in:

tariff_type tariff_foo tariff_bar tariff_plan isfallback  ----------- ---------- ---------- ----------- -----------  a           0.09       0.19       plan_x      0  b           0.55       0.66       default     1  c           0.99       1.99       plan_x      0  d           9.99       6.33       default     1

All I need to do now is stuff this query into a TVF:

CREATE FUNCTION get_tariffplan_for  (         @customerplan as varchar(50),      @defaultplan as varchar(50) = 'default'  )  RETURNS TABLE   AS RETURN   (      select p.tariff_type,          ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,           ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,          ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,          CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback      from tariff_values as p      left outer join tariff_values s          on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)      where (p.tariff_plan = @defaultplan)  );

And there we have it. We can call our function ("dynamic view") as intended (and also use it in selects/joins etc.)

select * from dbo.get_tariffplan_for('plan_x', default);    --or:    select *  from foo  inner join dbo.get_tariffplan_for('plan_x', default) bar      on foo.tariff_type = bar.tariff_type

Now my first question is:

I have a feeling all these ISNULL (or COALESCE) and/or CASE WHEN ... stunts seem to complicate things unnecessarily and something tells me this can be done more efficiently. However, I can't come up with a better and/or more efficient alternative.

So I'm hoping someone here has some ideas on how to improve this.

My second question is:

What if I had a product (tariff_type q for example) that I sold exclusively to some customer; the tariff wouldn't be in the default tariff-plan so I'd have to add another select to the above (with a union) to get all exclusive tariffs for that customer in the resultset. That would result in a query like this:

select p.tariff_type,      ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,       ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,      ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,      CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,      0 as isexclusive  from tariff_values as p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)  where (p.tariff_plan = @defaultplan)    UNION    --Exclusive values  select p.tariff_type,      p.tariff_foo,       p.tariff_bar,      p.tariff_plan,      0 as isfallback,      1 as isexclusive  from tariff_values p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) AND (s.tariff_plan = 'default')  where p.tariff_plan = 'plan_x'      and s.id is null

(Demonstrated in this SQL fiddle)

In the above example I use another left join with s.id is null (but that could be rewritten in other ways (using count, exists, not in, having...whatever)) to retrieve the customer-exclusive tariffs. But maybe there's a better way instead of the union?

Distributed transaction and Read Committed Snapshot

Posted: 14 Aug 2013 08:56 AM PDT

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?

How do I determine how much data is being written per day through insert, update and delete operations?

Posted: 14 Aug 2013 09:57 AM PDT

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by MariaDB 5.5 on a daily basis so that I can use this to estimate the possible longevity of SSDs if used in a heavy write database environment?

Current setup is that all tables are InnoDB. Can I use Innodb_data_written and Uptime to determine a rough idea of bytes written per day, or is there a better way of doing it?

Oracle Patch Update

Posted: 14 Aug 2013 11:15 AM PDT

We have an Oracle RAC production environment with primary and secondary DB. Our DBA has asked to update oracle version from 11.2.0.1.0(64 bit) to 11.2.0.3(64 bit) with patch 6880880,10404530,16803769 and 16803775.

In our current database we have Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines.

DBA has estimated that he need to do installation, settings from scratch and test.. So, when the version is updated, do we really need to reconfig and install everything (Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines) ?? If yes its fine, but no then I need to justify it.

I can understand testing would be required..

Updateable Subscriptions: Rows do not match between Publisher and Subscriber

Posted: 14 Aug 2013 09:05 PM PDT

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database and the application which uses the database. However, now I run into issues when I try updating a section in my application. It does not go through the with update and gives the following error:

Execption Message: Updateable Subscriptions: Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber. Updateable Subscriptions: Rolling back transaction. The transaction ended in the trigger. The batch has been aborted. Exception Triggered by Method: OnError

The update statement obviously doesn't go through. However, when I try it the second time it works. Replication is working. Everything seems to be replication.

Can anyone explain why this error would occur and how I can resolve this issue. I would really appreciate the help!...

Replicated Database Log File Maintenance

Posted: 14 Aug 2013 03:05 PM PDT

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, size, etc.).

Is there anything I need to do (or be cautious of) with the replication setup before trying to perform any maintenance on the log file? I am not an expert in the area of replication and cannot find anything solid that provides guidance as to what measures should be taken.

Edit: This would include working on the distribution database as well, data retention was not configured at all for some reason.

How to avoid empty rows in SSIS Excel Destination?

Posted: 14 Aug 2013 08:05 PM PDT

Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:

OLE DB Source:

OLE DB Source:

Data Conversion (to handle the annoying UNICODE / NON-UNICODE deal):

Data Conversion

The end result is either of the two below depending on value of "FirstRowHasColumnName" in the Excel Connection Manager. Note, the blank rows.

output 1

output 2

How to add rows/columns to the table in runtime in SSRS 2008

Posted: 14 Aug 2013 10:05 AM PDT

Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query?

For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example:

enter image description here

Thanks for your help in advance.

How to disable oracle's MAX_ENABLED_ROLES limit

Posted: 14 Aug 2013 04:05 PM PDT

How to disable oracle's MAX_ENABLED_ROLES limit or expand the value of limitation. [oracle 10g (win32)]

In MySQL, does the order of the columns in a WHERE clause affect query performance,why?

Posted: 14 Aug 2013 01:05 PM PDT

I have a query that doesn't use any indexes:

SELECT 32,         guid,         1,         1,         1,         0,         5  FROM   test  WHERE  level >= 20         AND ( ( fun_GetIndexValue(data, 354) >> 16 ) +                ( fun_GetIndexValue(data, 355) >> 16 ) +                ( fun_GetIndexValue(data, 356) >> 16 ) +                ( fun_GetIndexValue(data, 357) >> 16 ) +                ( fun_GetIndexValue(data, 358) >> 16 ) +                ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1;

The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

Posted: 14 Aug 2013 07:05 PM PDT

I am looking for advice on table/index design for the following situation:

i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid (int),date (date)). in addition to the pricing information, i have 200 double values that need to correspond to each record. 

CREATE TABLE `mytable` (  `assetid` int(11) NOT NULL,  `date` date NOT NULL,  `close` double NOT NULL,  `f1` double DEFAULT NULL,     `f2` double DEFAULT NULL,  `f3` double DEFAULT NULL,     `f4` double DEFAULT NULL,   ... skip a few …  `f200` double DEFAULT NULL,   PRIMARY KEY (`assetid`, `date`)) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE      latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0       PARTITION BY RANGE COLUMNS(`date`) PARTITIONS 51;

i initially stored the 200 double columns directly in this table for ease of update and retrieval, and this had been working fine, as the only querying done on this table was by the assetid and date (these are religiously included in any query against this table), and the 200 double columns were only read. My database size was around 45 Gig

However, now i have the requirement where i need to be able to query this table by any combination of these 200 columns (named f1,f2,...f200), for example:

select from mytable   where assetid in (1,2,3,4,5,6,7,....)  and date > '2010-1-1' and date < '2013-4-5'  and f1 > -0.23 and f1 < 0.9  and f117 > 0.012 and f117 < .877  etc,etc

i have not historically had to deal with this large of an amount of data before, so my first instinct was that indexes were needed on each of these 200 columns, or i would wind up with large table scans, etc. To me this meant that i needed a table for each of the 200 columns with primary key, value, and index the values. So i went with that.

CREATE TABLE `f1` (  `assetid` int(11) NOT NULL DEFAULT '0',  `date` date NOT NULL DEFAULT '0000-00-00',  `value` double NOT NULL DEFAULT '0',  PRIMARY KEY (`assetid`, `date`),  INDEX `val` (`value`)  ) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;

i filled up and indexed all 200 tables. I left the main table intact with all 200 columns, as regularly it is queried over assetid and date range and all 200 columns are selected. I figured that leaving those columns in the parent table (unindexed) for read purposes, and then additionally having them indexed in their own tables (for join filtering) would be most performant. I ran explains on the new form of the query

select count(p.assetid) as total   from mytable p   inner join f1 f1 on f1.assetid = p.assetid and f1.date = p.date  inner join f2 f2 on f2.assetid = p.assetid and f2.date = p.date   where p.assetid in(1,2,3,4,5,6,7)  and p.date >= '2011-01-01' and p.date < '2013-03-14'   and(f1.value >= 0.96 and f1.value <= 0.97 and f2.value >= 0.96 and f2.value <= 0.97)

Indeed my desired result was achieved, explain shows me that the rows scanned are much smaller for this query. However i wound up with some undesirable side effects.

1) my database went from 45 Gig to 110 Gig. I can no longer keep the db in RAM. (i have 256Gig of RAM on the way however)

2) nightly inserts of new data now need to be done 200 times instead of once

3) maintenance/defrag of the new 200 tables take 200 times longer than just the 1 table. It cannot be completed in a night.

4) queries against the f1, etc tables are not necessarily performant. for example:

 select min(value) from f1    where assetid in (1,2,3,4,5,6,7)    and date >= '2013-3-18' and date < '2013-3-19'

the above query, while explain shows that it lookgin at < 1000 rows, can take 30+ seconds to complete. I assume this is because the indexes are too large to fit in memory.

Since that was alot of bad news, I looked further and found partitioning. I implemented partitions on the main table, partitioned on date every 3 months. Monthly seemed to make sense to me but i have read that once you get over 120 partitions or so, performance suffers. partitioning quarterly will leave me under that for the next 20 years or so. each partition is a bit under 2 Gig. i ran explain partitions and everything seems to be pruning properly, so regardless i feel the partitioning was a good step, at the very least for analyze/optimize/repair purposes.

I spent a good deal of time with this article

http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/testing-partitions-large-db.html

my table currently is partitioned with primary key still on it. The article mentions that primary keys can make a partitioned table slower, but if you have a machine that can handle it, primary keys on the partitioned table will be faster. Knowing i have a big machine on the way (256 G RAM), i left the keys on.

so as i see it, here are my options

Option 1

1) remove the extra 200 tables and let the query do table scans to find the f1, f2 etc values. non-unique indexes can actually hurt performance on a properly partitioned table. run an explain before the user runs the query and deny them if the number of rows scanned is over some threshold i define. save myself the pain of the giant database. Heck, it will all be in memory soon anyways.

sub-question:

does it sound like i have chosen an appropriate partition scheme?

Option 2

Partition all the 200 tables using the same 3 months scheme. enjoy the smaller row scans and allow the users to run larger queries. now that they are partitioned at least i can manage them 1 partition at a time for maintenance purposes. Heck, it will all be in memory soon anyways. develop efficient way to update them nightly.

sub-question:

do you see a reason that i may avoid primary key indexes on these f1,f2,f3,f4... tables, knowing that i always have assetid and date when querying? seems counter intuitive to me but i am not used to data sets of this size. that would shrink the database a bunch i assume

Option 3

Drop the f1,f2,f3 columns in the master table to reclaim that space. do 200 joins if i need to read 200 features, maybe it wont be as slow as it sounds.

Option 4

You all have a better way to structure this than i have thought of so far.

* NOTE: i will soon be adding another 50-100 of these double values to each item, so i need to design knowing that is coming

thanks for any and all help

Update #1 - 3/24/2103

I went with the idea suggested in the comments i got below and created one new table with the following setup:

create table 'features'{    assetid int,    date    date,    feature varchar(4),    value   double  }

I partitioned the table in 3 month intervals.

I blew away the earlier 200 tables so that my database was back down to 45 Gig and started filling up this new table. A day and a half later, it completed, and my database now sits at a chubby 220 Gigs!

It does allow the possibility of removing these 200 values from the master table, as i can get them from one join, but that would really only give me back 25 Gigs or so maybe

I asked it to create a primary key on assetid, date,feature and an index on value, and after 9 hours of chugging it really hadn't made a dent and seemed to freeze up so i killed that part off.

i rebuilt a couple of the partitions but it did not seem to reclaim much/any space.

So that solution looks like it probably isn't going to be ideal. Do rows take up significantly more space than columns i wonder, could that be why this solution took up so much more space?

I came across this article

http://www.chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows

it gave me an idea.

where he says

"At first, I thought about RANGE partitioning by date, and while I am using the date in my queries, it is very common for a query to have a very large date range, and that means it could easily span all partitions."

Now i am range partitioning by date as well, but will also be allowing searches by large date range, which will decrease the effectiveness of my partitioning. I will always have a date range when i search, however i will also always have a list of assetids. Perhaps my solution should be to partition by assetid and date, where i identify typically searched assetid ranges (which i can come up with, there are standard lists, S&P 500, russell 2000, etc). this way i would almost never look at the entire data set.

Then again, i am primary keyed on assetid and date anyways, so maybe that wouldnt help much.

any more thoughts/comments would be appreciated

thanks

Delete word, its meanings, its meaning's example sentences from DB

Posted: 14 Aug 2013 05:05 PM PDT

I have three tables as below (simplified for demonstration):

words  =====  integer id  text    word    meanings  ========  integer id  integer word_id  text    meaning    examples  ========  integer id  integer meaning_id  text    sentence

where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how?

Edit1: I am using SQLite3 as the database.

Edit2: I figured the following solution which requires 3 sql queries in order:

DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id);  DELETE FROM meanings WHERE word_id=the_given_id;  DELETE FROM words WHERE id=the_given_id;

I'm still looking for the answer to my question: is the whole process possible to be done in one query?

How can I optimize this query and support multiple SKUs?

Posted: 14 Aug 2013 12:05 PM PDT

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also.

SELECT       available - (          SELECT COALESCE(sum(quantity), 0)           FROM product_locks           WHERE sku = 'sku1'      ) - (          SELECT COALESCE(sum(quantity), 0)           FROM               orderlineitems               INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id           WHERE               sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1'      ) AS free,       available AS total,       sku,       on_hold   FROM product_inventories   WHERE sku = 'sku1' AND salesite_id = 'site_id_1';

How to modify an update in Oracle so it performs faster?

Posted: 14 Aug 2013 02:05 PM PDT

I have this query:

UPDATE   (      SELECT   h.valid_through_dt, h.LAST_UPDATE_TMSTMP      FROM   ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h      WHERE       h.FUND_ID = d.FUND_ID      AND h.FEETYPE_NAME = d.FEETYPE_NAME      AND h.BREAKPOINT_TYPE = d.BREAKPOINT_TYPE      AND h.BREAKPOINT_QTY = d.BREAKPOINT_QTY      AND h.LOW_BREAKPOINT_AMT = d.LOW_BREAKPOINT_AMT      AND h.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy')      AND h.universe = 'DC'      AND h.universe = d.universe      AND EXISTS      (          SELECT 1          FROM FEE_SCHEDULE s          WHERE s.FUND_ID = h.FUND_ID          AND s.FEETYPE_NAME = h.FEETYPE_NAME          AND s.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE          AND s.BREAKPOINT_QTY = h.BREAKPOINT_QTY          AND s.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT          AND s.universe = 'DC'      )  ) updateTable  SET     updateTable.VALID_THROUGH = (SYSDATE - 1),  updateTable.LAST_UPDATE_TMSTMP = SYSTIMESTAMP;

The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function.

What would you suggest?

This is all the information that I believe it is relevant.

This is the execution plan of the internal select:

Execution Plan  ----------------------------------------------------------  Plan hash value: 57376096  ---------------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name                     | Rows  | Bytes| Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                          |     1 |   306 |  8427   (1)| 00:01:42 |  |   1 |  NESTED LOOPS                |                          |       |       |            |          |  |   2 |   NESTED LOOPS               |                          |     1 |    306|  8427   (1)| 00:01:42 |  |   3 |    MERGE JOIN CARTESIAN      |                          |     1 |    192|  8426   (1)| 00:01:42 |  |*  4 |     INDEX RANGE SCAN         | SYS_C000666              |     1 |     96|     2   (0)| 00:00:01 |  |   5 |     BUFFER SORT              |                          |  3045K|   278M|  8425   (1)| 00:01:42 |  |   6 |      SORT UNIQUE             |                          |  3045K|   278M|  8425   (1)| 00:01:42 |  |*  7 |       TABLE ACCESS FULL      | FEE_SCHEDULE             |  3045K|   278M|  8425   (1)| 00:01:42 |  |*  8 |    INDEX RANGE SCAN          | FEE_SCHDL_IDX1           |     1 |       |     1   (0)| 00:00:01 |  |*  9 |   TABLE ACCESS BY INDEX ROWID| FEE_SCHEDULE_HISTORICAL  |     1 |   114 |     1   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     4 - access("D"."UNIVERSE"='DC')     7 - filter("S"."UNIVERSE"='DC')     8 - access("H"."UNIVERSE"='DC' AND "S"."FUND_ID"="H"."FUND_ID" AND                "S"."FEETYPE_NAME"="H"."FEETYPE_NAME" AND                "S"."BREAKPOINT_TYPE"="H"."BREAKPOINT_TYPE" AND                "S"."BREAKPOINT_QTY"="H"."BREAKPOINT_QTY" AND                "S"."LOW_BREAKPOINT_AMT"="H"."LOW_BREAKPOINT_AMT")         filter("H"."FUND_ID"="D"."FUND_ID" AND                "H"."FEETYPE_NAME"="D"."FEETYPE_NAME" AND                "H"."BREAKPOINT_TYPE"="D"."BREAKPOINT_UNIT_TY

Table data:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  UNIVERSE|FUND_ID   |FEETYPE_NAME |BREAKPOINT_TYPE|BREAKPOINT_QTY|LOW_BREAKPOINT_AMT|HIGH_BREAKPOINT_AMT|FEE_PCT|FEE_SCHDL_SEQ_ID|GROUP_ID|LAST_UPDATE_TMSTMP  |VALID_FROM|VALID_THROUGH|INSERT_TMSTMP        |JOB_ID|  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  DC      |DC9ZTPLPHO|DeferLoad    |Percentage     |4             |10000             |300000             |3.14   |780250          |null    |1/4/2012  3:59:54 PM|6/23/2012 |12/31/9999   |1/5/2011   3:59:54 PM|666   |  DC      |DCE86Y8XFU|RedemptionFee|Percentage     |9             |  100             |100500             |7.67   |780251          |null    |6/4/2012  4:49:54 PM|11/12/2011|12/31/9999   |8/17/2011  2:00:54 PM|666   |  DC      |DCAYL0KONA|FrontLoad    |Percentage     |2             |50000             |601500             |5.00   |780252          |null    |4/25/2012 4:49:54 PM|8/2/2012  |12/31/9999   |12/19/2012 9:59:00 PM|666   |  DC      |DC9ZTPLPHO|DeferLoad    |Percentage     |7             |80000             |900000             |2.24   |780252          |null    |4/25/2012 4:49:54 PM|8/2/2012  |12/31/9999   |12/19/2012 9:59:00 PM|666   |  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is the script of the historical table:

CREATE TABLE FEE_SCHEDULE_HISTORICAL  (    UNIVERSE                        VARCHAR2(2 BYTE) NOT NULL,    FUND_ID                         VARCHAR2(10 BYTE) NOT NULL,    FEETYPE_NAME                    VARCHAR2(75 BYTE),    BREAKPOINT_TYPE                 VARCHAR2(50 BYTE),    BREAKPOINT_QTY                  VARCHAR2(10 BYTE),    LOW_BREAKPOINT_AMT              NUMBER(19,6),    HIGH_BREAKPOINT_AMT             NUMBER(19,6),    FEE_PCT                         NUMBER(19,6),    FEE_SCHDL_SEQ_ID                NUMBER        NOT NULL,    GROUP_ID                        NUMBER,    LAST_UPDATE_TMSTMP              DATE          NOT NULL,    VALID_FROM                      DATE          NOT NULL,    VALID_THROUGH                   DATE          NOT NULL,    INSERT_TMSTMP                   DATE          NOT NULL,    JOB_ID                          NUMBER        NOT NULL  );    CREATE UNIQUE INDEX FEE_SCHDL_PK ON FEE_SCHEDULE_HISTORICAL(FEE_SCHDL_SEQ_ID);    CREATE UNIQUE INDEX FEE_SCHDL_HST_IDX ON FEE_SCHEDULE_HISTORICAL (      UNIVERSE,      FUND_ID,      FEETYPE_NAME,      BREAKPOINT_TYPE,      BREAKPOINT_QTY,       LOW_BREAKPOINT_AMT,      VALID_FROM,      JOB_ID  )    CREATE INDEX FEE_SCHEDULE_HST_IDX2 ON FEE_SCHEDULE_HISTORICAL(LAST_UPDATE_TMSTMP)    CREATE INDEX FEE_SCHEDULE_HST_IDX3 ON FEE_SCHEDULE_HISTORICAL(VALID_THROUGH)    ALTER TABLE FEE_SCHEDULE_HISTORICAL ADD (      CONSTRAINT FEE_SCHDL_PK      PRIMARY KEY      (FEE_SCHDL_SEQ_ID)  );

This is the other table:

CREATE TABLE FEE_SCHEDULE  (    UNIVERSE                        VARCHAR2(2 BYTE) NOT NULL,    FUND_ID                         VARCHAR2(10 BYTE) NOT NULL,    FEETYPE_NAME                    VARCHAR2(75 BYTE),    BREAKPOINT_TYPE                 VARCHAR2(50 BYTE),    BREAKPOINT_QTY                  VARCHAR2(10 BYTE),    LOW_BREAKPOINT_AMT              NUMBER(19,6),    HIGH_BREAKPOINT_AMT             NUMBER(19,6),    FEE_PCT                         NUMBER(19,6),    JOB_RUN_ID                      NUMBER        NOT NULL,    FILE_DATE                       DATE          NOT NULL,    CYCLE_DATE                      DATE          NOT NULL  )

The temporary table is the result of FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE

Query to find and replace text in all tables and fields of a mysql db

Posted: 14 Aug 2013 06:05 PM PDT

I need to run a query to find and replace some text in all tables of a mysql database.

I found this query, but it only looks for the text in the tbl_name table and just in the column field. 

update tbl_name set column=REPLACE(column, 'fuschia', 'fuchsia');

I need it to look in all tables and all fields: (everywhere in the database)
Posted by at 1 comment:

[Articles] Lossy data and incorrect data

[Articles] Lossy data and incorrect data

Lossy data and incorrect data

Posted: 13 Aug 2013 11:00 PM PDT

Your copier could change data when creating an image. Phil Factor has some words of caution.
Posted by at No comments:

[MS SQL Server] Analysis Manager in SQL 2000

[MS SQL Server] Analysis Manager in SQL 2000

Analysis Manager in SQL 2000

Posted: 14 Aug 2013 04:14 AM PDT

Hi,I am trying to Restore the Cube from .CAB file to Analayis manager on SQL 2000.When i Right Click on server name then it suppose to show Restore database option which i am not able to see on that server.I checked another SQL 2000 Box where i find this Option was available, so Can you please help me to find root cause os this issue in SQL 2000 Analysis Manager.Thanks!

Removing log information from log file by using DBCC SHRINK FILE

Posted: 14 Aug 2013 01:36 AM PDT

Hi All,Can we remove log information from log file using DBCC SHRINK FILE( 'Logicalname', EmptyFile). Can any body please advise !!!!

Fragmentation report

Posted: 13 Aug 2013 08:42 PM PDT

Expertrs,I need to give fragementation report for one of the database.I was executing the below query, however it takes lot of time to excecute.Will it cause any blocking when I execute this query ?SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,i.name AS IndexName,indexstats.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstatsINNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_IDAND i.index_id = indexstats.index_idWHERE indexstats.avg_fragmentation_in_percent > 20Please guide.Thanks.

ASAP -Killed/Rollback - After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours

Posted: 13 Aug 2013 05:40 PM PDT

Hi All,Today I got a request to kill SSIS process which SID is XXX.After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.Please reply ASAP
Posted by at No comments:

[SQL 2012] SSIS dtutil deployment 32 vs 64 bit

[SQL 2012] SSIS dtutil deployment 32 vs 64 bit

SSIS dtutil deployment 32 vs 64 bit

Posted: 14 Aug 2013 02:56 AM PDT

SSIS dtutil 32 vs 64 bitI am just not getting the concept. I have a vb.net program that issues the following dtutil statement to deploy a package. I have only tested it on SQL 2008 R2 and SQL 2005 both 32 bit. But according to http://msdn.microsoft.com/en-us/library/ms162820(v=sql.105) a 64 bit install can installl a 64 bit version and a 32 bit version of dtutil and the command prompt will use the 32 bit versionn.Why would I care which bit version of dtutil is called to deploy a package???dtutil /DestS %SSISSERVER% /FILE %FILENAME% /COPY SQL;/%PACKAGENAME% /QUIET

Which version of SQL 2012

Posted: 14 Aug 2013 01:17 AM PDT

How can I tell which version that I have of SQL 2012? I need to reinstall the Data Tools becuase the VS 2010 Shell keeps crashing on launch. But I have two 2012 disc - Standard and Enterprise. This is the About page. Microsoft SQL Server Management Studio 11.0.2100.60Microsoft Analysis Services Client Tools 11.0.2100.60Microsoft Data Access Components (MDAC) 6.0.6002.18005Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 7.0.6002.18005Microsoft .NET Framework 4.0.30319.1008Operating System 6.0.6002

Can't execute SSIS package from remote machine

Posted: 18 Mar 2013 09:26 PM PDT

OS: Windows 2012, standard, 64-bitSQL: 2012 + SP1 + CU2 = 11.0.3339We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication). I'm stumped here. Anybody have anything else I can check or that I have overlooked?Thanks-A.

Start/Stop/Restart options are desabled on SQL 2012 in SSMS

Posted: 14 Aug 2013 12:04 AM PDT

Hi,I just realize that Start/Stop/Restart options are desabled on SQL 2012 in SSMS. Is there a way to enabled them?Thanks,Rem

Best way to import fixed length ASCII text files, that are not delimited ?

Posted: 13 Aug 2013 10:35 PM PDT

I have my output table setup in my SQL DB, and have tried the SSIS import feature, but i cant get it to work correctly!Importing the text file into Excel, then importing into SQL gives me an ACE error, even after downloading the compatibility package. Save as .csv, then changing it to .txt works, but the output table has some data overlap.I know you can manually set the input lengths in Excel, but it seems like a very long way to do something when i already have the table structure defined!Is there an easier way to do this ? I am using sql 2012 and Excel 2013. Thanks

Smart Input on Web Input Field

Posted: 13 Aug 2013 01:19 PM PDT

I have an input field on a website in which a user enters the City for a particular State in the United States. what I am trying to achieve is smart input for this field. A good example is Startbucks.com , Store Locator input field, where a user enters the beginning text and the possible choices display as smart input. the more text that is entered narrows the possible choices and the entry highlighted and clicked becomes the 'entry'. Would anyone have an idea on how to implement this smart input effect? thxf
Posted by at No comments:

[T-SQL] Help:Need to run a query against multiple SQL server instances across SQL server version(2000,2005,2008,2008 R2)

[T-SQL] Help:Need to run a query against multiple SQL server instances across SQL server version(2000,2005,2008,2008 R2)

Help:Need to run a query against multiple SQL server instances across SQL server version(2000,2005,2008,2008 R2)

Posted: 13 Aug 2013 10:07 PM PDT

Hi,I got a requirement where I need to collect report about SQL server configuration and the report should have columns like SQL server instance name,SQL Server Collation,Edition,location of TempDB,SQL server Version,Number of databases on that SQL instance etc.I need to generate the report querying 300 SQL server instances which are in various SQl server versions like 2000,2005,2008,2008 R2 and in different windows box.Need help on best possible solution and script to generate the report.

Return csv-String from Table

Posted: 13 Aug 2013 06:53 PM PDT

Hi,I am looking for a Function/Procedure, which "TableName" as a parameter, and returns each row of the table as a string with ";" as separator and "\ n" is obtained as RowDelimiter (like csv file - 1 Column for each tablerow)Function getCsvFormatFromTable (@ TableName)( @Csv_value RETURNS TABLE (Value NVARCHAR (MAX)) Return @ csv_Value)it also can written in a #Table etc.....ThanksRegards ;-)Nicole

Merge Statement

Posted: 13 Aug 2013 08:53 PM PDT

Have a nice lovely Merge statement updating the target table from the source.We capture the updates with a WHEN MATCHED We capture the insertes with a NOT MATCHED by TargetIts the pesky deletes.We may wish to not apply the deletes (just dont ask :exclamation: however we may change our mind again) However we would like to capture the number of potential deletes. Is this possible within the Merge?? I dont think it is.When we panned to do the deleteions we used the output to capture the number iof rows updated, deleted, inserted etc.Many ThanksE

Neea a better performance query

Posted: 13 Aug 2013 08:32 PM PDT

hi pros, hope every thing goes well,[code="SQL"]SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @Tbl AS TABLE ( id INT IDENTITY (1, 1), Mainid INT, Subid INT, NAME VARCHAR (100) )INSERT INTO @Tbl ( Mainid, Subid, NAME )SELECT c.[object_id], c.column_id, c.nameFROM sys.[columns] cORDER BY c.[object_id], c.column_idDECLARE @id INT = 10SELECT *FROM @Tbl tWHERE EXISTS( SELECT 1 FROM @Tbl t2 WHERE t.id < t2.id AND t.Mainid = t2.Mainid AND EXISTS( SELECT 1 FROM @Tbl t3 WHERE t3.id = @id AND t2.Mainid = t3.Mainid ) ) SELECT *FROM @Tbl tWHERE t.id NOT IN (SELECT MAX (t2.id) FROM @Tbl t2 INNER JOIN @Tbl t3 ON t3.Mainid = t2.Mainid WHERE t.Mainid = t2.Mainid AND t3.id = @id) [/code]well my requirement is when i gave a id of a record, i want to get all records of that record's Mainid group except the last record of that grouplet's say [code]id Mid Sid NAME1 3 1 rsid2 3 2 rscolid3 3 3 hbcolid4 3 4 rcmodified[/code]when i gave 1 or 2 or 3 or 4 the expected result would be[code]id Mid Sid NAME1 3 1 rsid2 3 2 rscolid3 3 3 hbcolid[/code]which one is the best way, when i use first query scan count is small but time is little big higher than the second querywhen i use the second query scan count is large but the time is smallif you have any other idea or query please suggest me to the right way

Need help with a query

Posted: 13 Aug 2013 04:34 AM PDT

Hi all,I need some help here. I posted in the wrong forum and i appreciated the help received, but i forgot the db was 2008R2. So here goes the issue.DECLARE @Testing AS TABLE (AgentID INT,ExcepCodeDetailName VARCHAR(10),Detail_Start_Time DATETIME,Detail_End_Time DATETIME)INSERT INTO @Testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Break', '2013-08-06 09:00:00', '2013-08-06 09:10:00')INSERT INTO @Testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Lunch', '2013-08-06 13:00:00', '2013-08-06 14:00:00')SELECT * FROM @Testing-------------------Results12345 Break 2013-08-06 09:00:00.000 2013-08-06 09:10:00.00012345 Lunch 2013-08-06 13:00:00.000 2013-08-06 14:00:00.000-----------Results needed12345 Break 2013-08-06 09:00:00.000 2013-08-06 09:10:00.00012345 Open 2013-08-06 09:10:00.000 2013-08-06 13:00:00.00012345 Lunch 2013-08-06 13:00:00.000 2013-08-06 14:00:00.000

problem with sql reader

Posted: 13 Aug 2013 06:57 AM PDT

hi all,I have this rather generic function that executes a stored proc with a bunch of parameters:[code="plain"] protected ArrayList ExecSProcReturnAList(string storedProcName, SqlParameter[] parameters) { ArrayList result = new ArrayList(); using (SqlConnection connection = new SqlConnection(ConnectionString)) { try { connection.Open(); using (SqlCommand command = new SqlCommand(storedProcName, connection)) { command.CommandTimeout = 200; command.CommandType = System.Data.CommandType.StoredProcedure; //add parameters foreach (SqlParameter sqlpar in parameters) { command.Parameters.Add(sqlpar); } SqlDataReader sqldr; sqldr = command.ExecuteReader(); while (sqldr.NextResult() == true) { ArrayList intermediaryResult = new ArrayList(); while (sqldr.Read()) { ArrayList sqldrRow = new ArrayList(); for (int i = 0; i < sqldr.FieldCount; i++) { //Console.Write(sqldr[i].ToString()); sqldrRow.Add(sqldr[i]); } intermediaryResult.Add(sqldrRow); } result.Add(intermediaryResult); } return result; } } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Dispose(); } } } }[/code]Every time I run this from top to bottom, the result array list is empty. When I put a breakpoint just at the while line, I can see the sql data reader, sqldr does have the expected data in it, however, it fails at the condition ... somehow sqldr.NextResult() == true is false and it jumps out of the loop.Can anybody tell me why?Thanks,kowalsky

FullName

Posted: 13 Aug 2013 02:41 AM PDT

How to identify if the first word is the foundation or bank bring full name with t-sql?

How to Group by the Past Seven Days

Posted: 13 Aug 2013 01:38 AM PDT

I am familiar with how to group my query results by year, month, week etc. But what is the best way to group my results by the past 7 days for a given timeframe? So my results would be grouped by seven days back from today (8/6/2013) then seven days back from that (7/30/2013) etc. How do I accomplish this? Please advise.

Problem with EXCEPT statement

Posted: 13 Aug 2013 03:12 AM PDT

hi, i've a query that's used to build a delta set between two tables and for some reason i'm getting what look like false positives over a particular column. Table A's column stores a varchar value, where Table B's column stores an nvarchar value. I've performed a cast on Table A's varchar value to promote it nvarchar of the same length in table B, and i've checked the content length (i.e. the LEN function) on both columns in both tables to ensure the data is same length. i'm even doing an LTRIM(RTRIM()) on both columns as well. still getting a row back indicating the two values are different. the source data is coming from a database in the UK and the values sometimes contain non alpha-numeric characters. i'm probably wrong, but i'm wondering if some of these characters have different unicode values? like, even though it looks like a duck, it doesn't necessarily mean it's a duck. any thoughts on this? many thanks in advance. ben

need urgent help on a calculation

Posted: 13 Aug 2013 04:47 AM PDT

Dear friends,I have to do a calcuation for field Duration using the formula as below-(Temporary Assignment End Date field - Target Start Date field)/30. and I need to Format the result of the formula as numeric, display to two decimal places.the database has the 2 fields in the type and format as -Req# Target Start Date ( DATETIME) example: 2012-08-01 00:00:00.000 Temporary Assignment End Date ( NVARCHAR9255)) example: 09-01-2014Need urgent hlep to solve this using SQL please since I'm new to SQL.kind RegardsDJ
Posted by at No comments:

[SQL Server 2008 issues] SQL Native Client Configuration

[SQL Server 2008 issues] SQL Native Client Configuration

SQL Native Client Configuration

Posted: 13 Aug 2013 06:57 PM PDT

Hi All,I would like to know in SQL ...If we change the order of the Protocols in the SQL Native Client Configuration.. Do we need to restart the services for sql server. Please can some body help me out.Thanks in advance !!!

LIKE Operator

Posted: 13 Aug 2013 04:49 PM PDT

Hi,I want to display list of tables , which having "_" in the part of table name?Thanks.RR

After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.

Posted: 13 Aug 2013 05:46 PM PDT

Hi All,Today I got a request to kill SSIS process which SID is XXX.After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.Please reply ASAP

Same query on a partitioned table with different date range has different execution plan

Posted: 13 Aug 2013 10:11 AM PDT

Query running slow .. Table has partitions based on date , some dates it return in 3 mins and some days it takes more than 3 hrs I have a partitioned table (partitioned by day) and have filtered stats configured. I am trying to run a query something like this ... select * from Part_tbl ptleft join t1 join_condleft join t2 join_condleft outer join t3 join_condwhere day_no = 09This is completed in 3 min for day_no = 11, 09, 08 and 07. But when I ran the same query with day_no = 10 and 12 it took 3 hours each (Data wad is not the issue as the amount of data we will get every day is almost identical). I compared the execution plans and they are different for 10/12 compared to 07/08/09/11. We enabled filtered stats on 01/2012 and never deleted any of the stats. Also, we have about 1250 partitions. I thought these might be the reason, but why the run time is different for only some of the partitions.Any help or direction would be appreciated :)

SHRINKDATABASE killed but rollback appears to be stuck

Posted: 13 Aug 2013 04:52 PM PDT

Hello...I've found several useful tips/tricks on this site over the years, and am hoping someone can help me with this one as well.A shrinkdatabase was executed and killed 8-10 hours later. The problem is that:select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requestssuggest that there has been no progress in the last 2-3 hours, and the estimated_completion_time increases. How can I proceed?Thanks,

I/O requests taking longer than 15 seconds to complete

Posted: 13 Aug 2013 04:38 PM PDT

Getting below message in error log when DBCC CHECKDB is running. SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [Drive:\MSSQL\MSSQL.1\MSSQL\Data\xyz.mdf] in database [database]. The OS file handle is 0x00000000. The offset of the latest long I/O is: 0x00000000000000I never see this message other than DBCC CHECKDB. Is this indicating any IO bottleneck or its normal to see this message while running DBCC CHECKDB ?

Querying AD thru an ASDI linked server

Posted: 13 Aug 2013 06:46 AM PDT

I'm having trouble diagnosing what the issue is here, i'm not sure the error message that i'm getting is useful in helping find it out. Am using SSMS.I've successfully created the linked server per:http://msdn.microsoft.com/en-us/library/aa746379.aspxand i know i'm using a correct LDAP connection string, as we use the same one in several web apps that work well.I have also had my network admin check my string and he indicated that it looked correct to him.My select syntax has been tested against both forms: a) LDAP://DC=Fabrikam,DC=comb) LDAP://Fabrikam.com/DC=Fabrikam,DC=com(note my querystring is different than that above, but same form)and either query parses just fine.However, all i get in returnMsg 7321, Level 16, State 2, Line 1An error occurred while preparing the query "SELECT name FROM '<LDAP STRING>' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".replace <LDAP STRING> with a or b forms.I know it's not a permissions issue as I'm using the current security context, and I can open up in ADMINISTRATIVE TOOLS and see what I"m looking at. (using ASDI Edit)But i'm stuck as to what to check next.I've checked at a bunch of places and most are a regurgitation of the original article above, and indicate i'm headed the correct way.... Thoughts on what i can try to get over this 'speed bump' i'm facing? Heck even getting a different error message would excite me at this point... (current problem related of course....)

QUERY and SP

Posted: 13 Aug 2013 02:34 AM PDT

Hey, I am confused with these files and what is asked of them. I need help creating a query with these results as well as a stored procedure help. I apologize with the lack of the DDL. So look at the XLS files please and the DOCX files. Attached below.

Email Alert when there is no entry in the table for more than one hour

Posted: 13 Aug 2013 08:57 AM PDT

Hi Experts,Requirement: check for entries in a table for every one hour and Send an email to x,y,z when there are no entries in to that table for more than 1 hour, this table has Entrytime column. For: If the programme checked table at 1:00PM and if there are no entries in table after 12:00(on EntryTime Column)it should email to the users saying there are no entries for the duration 12:00PM to 1:00PM. Next check should be done at 2:PM and it should check for the values for 1:00 pm to 2:00 pm...Any other way we can implement this without TRIGGERS?Any input is appreciatedThanks in advance

Converting Non-Spatial Data to Spatial Data

Posted: 13 Aug 2013 09:19 AM PDT

I have been looking in my SSRS book and online and can not find a clear answer to this question. I have a list of 30 dummy CPT codes in our EHR that our MD's use while on Outreaches to different shelters or Motels each dummy code represents the location they are doing their Dr. Visit at. For instance code (OR001)CPT Code = Label (Albuquerque Rescue Mission). I would like to be able to convert each code to a spatial Long and Lat that i can then use in Report Builders mapping tool. I do not mind looking up each of the 30 codes actual Long/Lat using bing. If I could get the spacial conversion, we could create a Heat Map over our city where we have the most outreach encounters. What is the best process or where can I find good information on accomplishing such a feat.Any help is appreciatedThanks Thomas

Shrink does not release space

Posted: 13 Aug 2013 07:58 AM PDT

Hi =)Im trying to shrink my production database, because we archived about 200GB of data, but - I don't know why - none space is released. I tried rebuild some indexes, shrink via file and even suspended the mirror (this environment has mirroring) but 0 KB is released rs.Recently I created an environment for testing and restore one of my lastest backups from the production database. I shrinked and ..... works fine. Released the unused space... I really don't know what to do. Pls, Heeelp rsps.: I searched in others topics, but didn't find any solutionps2: Sorry for my english...

Strange Job Slowdown 30 secs to 30 minutes

Posted: 08 Aug 2013 03:19 AM PDT

I have a job that runs 22 times a day.Four of those times, it is kicked off by another job.We recently moved to a SAN (EMC SAN is a VNX5300). That seemed great, all jobs sped up.Then we virtualized the machine to VMWare 5.1Now all jobs are fine except the one that runs 22 times a day. That one runs fine too except when it is kicked off by another job at 3:30 AM. It runs fine the other three times it is kicked off by the other job, it only slows down at 3:30 AM.Normally, the job takes between 30 seconds and 5 minutes depending on the volume of data.Before virtualization, the run at 3:30 AM was taking between 30 seconds and 1 minute.Now it is taking 28 minutes.Nothing else is running on the server at this time. Everything else has finished around 3:00.Backups (EMC, third party solution) start at 12:30 AM, finish at 3:28 AM, and don't slow down any of the other jobs that run between 12:30 and 3:30.The step in the job that is taking longer is an SSIS package.The job moved data from one database to another on the same server, then does a query. The data from the query is fed to an SSIS loop which creates files.All the files that are created have a timestamp within a minute (i.e. all are 3:55)This leads me to believe the slowdown is in one of the first two queries.The first, larger query, that inserts data to another table, typically has 500-600 records at this hour. The indexes all are under 5% fragmentation.I plan on enabling logging on the SSIS package tomorrow (today is a business-critical day, no changes allowed)Any ideas?

Create multi column view from single column data

Posted: 13 Aug 2013 05:24 AM PDT

I have the following result from my SQL query: EventID P_Num PN_NameCount1 PN_Name ABC-I-10942683 1089213 1 Company 1 ABC-I-10942683 1326624 8 Company 2 ABC-I-10942683 1565423 1 Compnay 3 ABC-I-10942683 1659874 2 Company 4I am still learning this capability in SQL and need some assistance, Pivot's are NOT working in this scenario. I have tried several different ways in attempting to do this, but was not able to create the desired results: EventID P_Num1 PNC1 PN_Name PNC_Num2 PNC2 PN_Name ABC-I-10942683 1089213 1 Company 11326624 8 Company 2The `EventID` will change based on the different events from the companies, as the `EventID` is based on a particular date the event occurred with the company.This is just a sample of the 500K+ rows of data I am working with. This will go into a temp table to be joined with the other various pieces of data needed.I have tried this without success: SELECT Key, MAX(Col1) AS Col1, MAX(Col2) AS Col2, MAX(Col3) AS Col3 FROM table GROUP BY Key

Modifying a stored procedure to point from PROD to TEST

Posted: 12 Aug 2013 11:20 PM PDT

Hi , Im looking for suggestions that would enable me to create a TEST environment.AT this point in time in my PRODUCTION system , I have stored procedures that use Linked Servers to point to the Production Environment.I am looking for ways to easily create a test environemnt from Prod.At this point in time I have 22 stored procedures that have hardcoded the database name, so when I want to create a TEST environment, I would manually need to change these 22 occurrences.I can think of using dynamic SQL to overcome this problem, however any other suggestions would be appreciatedThanks

Red gate backup issues

Posted: 13 Aug 2013 08:29 AM PDT

Hi I am using sql backup 7 from Redgate and i am having an issue whereby the sql backup process 'sqbcoreservice'is consuming way too much cpu. Way to much means, right now, when the server cpu should be near 1 - 10 % (as it now not being used) it is currently at 50% cpu. When business hours start cpu will go to around 90 - 100%.Ive just ran sp_whoisactive and i have this:dd:hh:ss.... waitinfo..........................................................CPU.......... STATUS00:11:56.....(43006055ms)ASYNC_NETWORK_IO...............32,953.........RunableI have omitted some of the other info for berevity !!When i examine the sql server processes that is running its always sqbcoreservice. We have transaction log backups throughout the day every 10 mins. There are currently no backups being performed by sql backup and there won't be for another 8 hours untill business starts again.Why am i seeing sqbcoreservice as the cpu bottleneck if no backups are currently happening?I have posted a question on the RedGate web site...i am still waiting for an answer on that one.

Select statement can't modify

Posted: 13 Aug 2013 07:35 AM PDT

I ran a select statement like below in query window but it can't be modified.select * from OrderI have to right click Order table from object explore, select "edit 200 rows".How to make select statement be editable?

SSRS: Catch/Replace #ERROR value in cell

Posted: 13 Aug 2013 06:58 AM PDT

Hi, allI have tricky tablix, and when I'm doing final TOTAL = sum all cells, it's failes because "SSRS can not deal with multiple types of values".SO in result I have on my cell value <#ERROR>, how I can erase/replace with spaces, or zeros. [code="other"]The original formula inside this cell is:=Sum(Fields!AmtX.Value * 12)I tried something like:=IIF(IsNumeric(Sum(Fields!AmtX.Value * 12)),Sum(Fields!AmtX.Value * 12),nothing)[/code]Hopind that1. It will run =Sum(Fields!AmtX.Value * 12) first2. Then check if #ERROR but it doesn't work, I think I doesn't execute original command first to evaluate status of execution.Thanks allMario

Restoring database by specifying network path for a local pc

Posted: 12 Aug 2013 09:09 PM PDT

I am using sql server 2008 r2. When i try to restore the database by using restore command[code="sql"]RESTORE DATABASE [dbname] FROM DISK = N'\\PC91\D\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'\\PC91\D\dbname.MDF', MOVE N'test_log' TO N'\\PC91\D\dbname_log.LDF', NOUNLOAD, STATS = 10[/code]I get error like[code="sql"]Msg 3634, Level 16, State 1, Line 1The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\\PC91\D\dbname.MDF'.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.[/code]Here in above code i use network path,But this network path is network path of my pc where sql server exists.If i use below restore command then it works[code="sql"]RESTORE DATABASE [dbname] FROM DISK = N'D:\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'D:\dbname.MDF', MOVE N'test_log' TO N'D:\dbname_log.LDF', NOUNLOAD, STATS = 10[/code]This command also restores the database to same folder.So if i specify the network path why does error comes?

Increase in MAX memory causing DBCC CHECKTABLE to run longer?

Posted: 13 Aug 2013 02:55 AM PDT

A number of weeks ago we upgraded the RAM in 2 of our servers. After allowing about a week burn-in time, I changed the max memory of SQL to leave the same amount of RAM to the OS as was before, which allowed us to increase the memory for SQL about 300GB. Ever since then, our weekly maintenance significantly increased in duration. Specifically, I have traced it to DBCC CHECKTABLE tableName WITH PHYSICAL_ONLY increasing for each of our tables. From about 3 sec to 15 on avg. Anyone have any thoughts on this?

Reindexing causing strange SQL to be run

Posted: 13 Aug 2013 07:19 AM PDT

I have a customer that is reindexing our database and the reindex job that is causing the following SQL to be seen:INSERT INTO [schema_a].[table_a] SELECT * FROM [schema_a].[table_a]schema_a is our schema nametable_a is our table namethe command being run is ALTER INDEX with no options except (online = on)I'm wondering if this is related to enterprise edition and how it handles online reindexing. I'm just looking for confirmation if anyone else out there has seen this.Thanks in advance for taking any time to read and assist if you do. Thanks, Even if you don't!

Improving the efficiency of a resource-intensive job

Posted: 13 Aug 2013 06:49 AM PDT

Hello,I am looking into ways to improve a resource-consuming job (that tends to cause deadlocks sometimes), and was hoping to get advice on the right approach.The job compares 50+ tables from an OLTP and a reporting DB, logs the count of rows that are different , and triggers CDC. It currently contains 50+ statements - 1 for each of the table pairs, with the following pattern:--1—- Get the delta between the DB and the DM records in each tblSELECT a.col1 INTO #Diff FROM Tbl_DB (nolock) a LEFT JOIN Tbl_DM (nolock) b –- optional additional joinsON b.Col1 = a.Col1WHERE b.Col1 IS NULL --optional other filters--2-- A 'fake' update to the source DB table to trigger CDCIF @@ROWCOUNT > 0UPDATE aSET Col2 = a.Col2 FROM #Diff b JOIN Tbl_DB aON a.Col1 = b.Col1--3—-Insert a row in a log tbl--4—Drop table #DiffIn the majority of cases the columns in the join condition are PK's (some of which are composite). The additional conditions (when applicable) in the Where clause are either ColumnN=Constant (the ColumnN's are indexed in most cases), or ColumnN<>Constant. When more than 3 tables are joined there are multiple equality conditions in the where clause – I checked 10 of the cases and there are either clustered or non-clustered indexes on those columns. The row count for about 10 these tables is in the range of 1-80 million, about a third have 100k-1million.Is there a more efficient way of achieving the same, assuming that the set of tables would changes very rarely?For example: have a ref table with the names of tables that need to be compared, find a way to detect differences between the DB and DM version without going through all the joins (calculate checksum; or another method from available sys tbl info?), and compare only the data sets with actual changes.If not, what is a good approach find areas for improvement in the existing process?Any words of wisdom from your own experience will be much appreciated.Thank you!Vilyana

Transaction Log

Posted: 09 Aug 2013 07:47 AM PDT

One of our databases transaction log file growing larger month by month.Before I increase the disk space I would like to understand is there way we can keep the transaction log file minimal other than shrinking.However, data is increasing I belive cause log file to grow.Could you please elobrate what other things can impact the trnasaction log file gowth other than maintenance plans etc.How should I do analysis for the disk space recommendations?Thanks and appreciated!

SSIS process log

Posted: 13 Aug 2013 05:25 AM PDT

I have the following SQL that I run for DTS packages to be able to determine how long individual steps in the package took.[code="sql"]USE msdb;declare @packagename varchar(255)set @packagename = 'Month_End_Step_2_Primary_Update'select stepname, starttime, endtime, convert(char(12),(endtime - starttime), 108) as elasped_time, elapsedtimefrom msdb..sysdtssteplogwhere lineagefull = (select lineagefull from msdb..sysdtspackagelog where [name] = @packagename and logdate = (select max(logdate) from msdb..sysdtspackagelog where [name] = @packagename --and starttime < '2012-08-01 00:00:00.000' ) ) and stepname not like '%e-mail%' and stepname not like 'Success email%' and stepname not like '%Parameter%'order by stepexecutionid[/code]Here is the results I get:stepname starttime endtime elasped_time elapsedtimeAgency Cross reference SQL 7/1/2013 8:51:49.000 7/1/2013 8:58:32.000 0:06:43 403.328DTSStep_DTSExecuteSQLTask_3 7/1/2013 8:58:32.000 7/1/2013 8:59:14.000 0:00:42 41.641DTSStep_DTSExecuteSQLTask_4 7/1/2013 8:59:14.000 7/1/2013 9:00:03.000 0:00:49 48.734DTSStep_DTSActiveScriptTask_5 7/1/2013 8:59:14.000 7/1/2013 8:59:14.000 0:00:00 0.031DTSStep_DTSActiveScriptTask_6 7/1/2013 9:00:03.000 7/1/2013 9:00:03.000 0:00:00 0.031UpdateRegionCodeRegionTable 7/1/2013 9:00:03.000 7/1/2013 9:12:35.000 0:12:32 752.093DTSStep_DTSActiveScriptTask_12 7/1/2013 9:12:35.000 7/1/2013 9:12:35.000 0:00:00 0.047Balancing_by_Region 7/1/2013 9:12:35.000 7/1/2013 9:12:50.000 0:00:15 15.641My question is does anyone have something similar I could run to get the same type of results for SSIS packages?I have SQL to look at the log file created from the SSIS package, it just doesn't put it in the nice format that this is with start and stop times on one row.

SSMS query connections

Posted: 13 Aug 2013 04:46 AM PDT

Hey gang, is there a way to tie the two connections of the query tabs together so that when I change one, the other changes as well?Regards,Erin

Log User Access to Server and Database

Posted: 13 Aug 2013 02:15 AM PDT

Hello,is there way, to find out which client is connecting via Linked Server to a specific database and with specific UserID?(UserID?...ClientID?...Date?)Background:The user no longer exists (Real Employee !!:w00t:, and therefore it must be deleted on the SQL server. However, there are applications in our Companie that have deposited in the connection string with the user password (also odbc-connect and Views).We have a lot of tables in this Database, and we cannot put a Trigger on each table. And if the User sends a "SELECT"-Statement, the Trigger doesn't fire.Thank youBEST REGARDSNicole ;-)

SSAS - Multiple Instances

Posted: 13 Aug 2013 01:48 AM PDT

sorry for my ignorant question: Is it possible to install multiple SSAS instances in a clustered/nonclustered environment with the same IP address with different port numbers.

Starting SSIS jobs from BMC Control-M

Posted: 11 Aug 2013 05:05 PM PDT

Our outsourcing partner is trying to setup a way to start SSIS jobs from BMC Control-M. There seams to be a problem doing that because the SSIS job returns control before the SSIS job has finnished. Is there a way to work around that problem?Control-M is a central scheduler, its installed on the SQL Server batch server so Connection between Control-M and the server is no problem.

Execute permission

Posted: 13 Aug 2013 12:19 AM PDT

Hi ,Can someone please explain what is the purpose of execute permission? What is the drawback of granting execute? Does it cause lot of performance overhead or does it make the database/ table insecure ?I added a trigger on one of the tables in sql server (trigger on insert). But the insert fails when i enter the details on the webpage and hit enter.. The error that i get is - execute permission denied.I can get the execute permission but I want to access all the pitfalls before i go ahead with that.

copy job

Posted: 13 Aug 2013 01:24 AM PDT

i need to copy the backup file from one server to another server ?can any one send me the script to achieve this

List of fixes in SQL server 2000 SP4

Posted: 13 Aug 2013 12:53 AM PDT

Hi,I need list of only fixes that were done in SQL server 2000 SP4. I got the link belowhttp://support.microsoft.com/kb/888799Please let me know if it contains the fixes of SP3 or other service pack also.

MDW - SQL 2008

Posted: 13 Aug 2013 02:04 AM PDT

I am having a problem with the disk usage reports after creating a centralised MDW on SQL Server 2008, this is the report displayed when you drill down onto a specific database. When I drill down on a database on the local server to the MDW database the disk usage report is shown correctly. However, when I drill down onto another servers database I receive the following error: A data source instance has not been supplied for the data source 'DS_TraceEvents'. Any help will be appreciated.

Index rebuild

Posted: 13 Aug 2013 12:51 AM PDT

Hi Team,After Rebuilding index, Total fragmentation is still showing 75.please suggest what to do.

Installing multiple instances of SQL 2008 R2 SSRS

Posted: 13 Aug 2013 12:14 AM PDT

Hello all,The subject says pretty much everything.I was looking for a way to install multiple instances of SSRS on Windows Server 2008 R2 either using PowerShell or any scripting language.Firstly is it possible ? If yes, I would appreciate if someone can put me in the right direction.Many thanks in advance.

SQL Server 2008R2 Evaluation edition download link

Posted: 27 May 2013 11:01 PM PDT

Hi, I am trying to find the free trail version of SQL Server 2008 R2 Evaluation edition but no success.I tried this linkhttp://www.microsoft.com/en-us/download/details.aspx?id=8158[u][/u] but the page is empty.Could someone help me out to find the location.Thanks....
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog