Friday, August 9, 2013

[how to] PostgreSQL maintenance

[how to] PostgreSQL maintenance

PostgreSQL maintenance

Posted: 09 Aug 2013 08:38 PM PDT

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III.

I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. Moreover, I need to update database statistics via ANALYSIS so query executor could select the best plan and I also need to REINDEX to regenerate indexes.

The windows I use for that purposes is here: http://www.pgadmin.org/docs/dev/maintenance.html

Some questions regarding the behavior and options:

  1. if I run VACUUM without selecting any options below (FULL, FREEZE, ANALYZE) what PostgreSQL will do? Will it only free some space and that's it?

  2. If I run VACUUM with Analyze option is it the same as running VACUUM without Analyze and then running ANALYZE separately. What is working faster?

  3. Is it worth to do REINDEX after doing VACUUM and ANALYZE? I can see in verbose messages that indexes are affected somehow by VACUUM without options. Should I run REINDEX to make my indexes works faster?

How can I get MySQL to accept both resume and resumé on a primary key?

Posted: 09 Aug 2013 06:29 PM PDT

If I add both then the uniqueness constraints fail, even though my field is in UTF-8. What can be done? I'm running 5.5.16

mysql> show create table foo_person;  +------------+------------------------------------------------+  | Table      | Create Table                                   |  +------------+------------------------------------------------+  | foo_person | CREATE TABLE `foo_person` (    `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,    PRIMARY KEY (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |  +------------+------------------------------------------------+    mysql> INSERT INTO foo_person (`name`) VALUES ('resumé');  Query OK, 1 row affected (0.01 sec)    mysql> INSERT INTO foo_person (`name`) VALUES ('resume');  ERROR 1062 (23000): Duplicate entry 'resume' for key 'PRIMARY'    mysql> select * from foo_person;  +---------+  | name    |  +---------+  | resumé  |  +---------+  1 row in set (0.00 sec)

Replication fails because of a non-existent foreign key

Posted: 09 Aug 2013 06:46 PM PDT

I have been trying to get a previously working replication to start working.

I have tried many settings, but I can't seem to make it work.

Here is the scenario. I dropped all the FK constraints from the destination database (call it DestDB).

I then re-initalize my replication and when I Runs I get this (de-identified) error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_TableOnlyInDestDB_MyReplicatedTable". The conflict occurred in database "DestDB", table "SomeSchema.MyReplicatedTable", column 'MyReplicatedTableId'. (Source: MSSQLServer, Error number: 547)

It is driving me nuts because the FK indicated does not exist in that database.

Now, I did copy this database (backup and restore) from another database. So the only thing I can think of is that it is somehow crossing the streams.

But that seems unlikely.

Any ideas would be great!

NOTE: This is the query I ran to ensure that I have no FKs (It returned no rows):

use DestDB    SELECT f.name AS ForeignKey,     OBJECT_NAME(f.parent_object_id) AS TableName,     COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,     OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,     COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName  FROM sys.foreign_keys AS f  INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

I also checked to ensure that the FK in question was not on the source table (in the database I am replicating from).

I have also tried: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Also tried Dropping MyReplicatedTable. Dropped just fine and replication re-created it. (Still gave me the error though.)

I tried creating an empty database as a subscriber. That worked fine, so it is something in the DestDB that is causing the issue, but the FK in the ERROR does not exist.

How to order by first record of another order by result?

Posted: 09 Aug 2013 12:54 PM PDT

I am trying to create my own messaging like with SMS in phones.

I have a MySQL table like this

id                  (the message id)  from_member_id      (the id of the member who sent this message)  to_member_id        (the id of the member who the message was sent to)  date sent           (the date it was sent)  active              (if this message is deleted or active)  text                (the text)

And I want to get the information in a special ordered way.

First it has to be sorted by the id that's not you (call it 'other' id). Then for each section of that ordering, it needs get the top most record (which should be the most recent date), and then sort the sections by the date value of that record.

I can do the first ordering by this:

SELECT    from_member_id,    to_member_id,    (CASE WHEN from_member_id = ? THEN to_member_id ELSE from_member_id END CASE) AS conversation_member_id,    date_sent  FROM table  WHERE from_member_id = ?  OR to_member_id = ?  ORDER BY conversation_member_id DESC, date_sent DESC

where the ? is my id.

But the problem is how to do the second ordering, where I need to order the sections by the date of the top record (which should be most recent date). Note when I say section, I mean the group of records with the same 'other' id.

Does anyone know how to do this?

Thanks.

EDIT:

Example

If my id is 5:

From To   Date  5     6   july 28  6     5   july 7  6     5   july 7  5     2   july 26  5     2   july 26  2     5   july 26

So the july 28 date should come on top, because it happened the most recently even though there are dates in that section that are before july 26.

So when I do the first ordering, it orders by the other id, which would be the 6, and then 2. The ones with 6 is one section, and the ones with 2 is the second section.

Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine?

Posted: 09 Aug 2013 12:53 PM PDT

With SQL Server 2012, can SSRS instances be setup on two different machines, with both instances using the same machine for their SQL Server databases?

Machine 1

  • SQL Server 2012 Database Server
  • SSRS instance 1

Machine 2

  • SSRS instance 2 pointed to machine 1's database server

There's this document describing installing multiple instances in 2005, but on the same machine. Can you point me to a better resource for our design? Is this still supported on 2012? How are the SSRS databases (ReportServer, ReportServerTempDB) created for the 2nd instance?

How to partition an Oracle table with 65 million rows?

Posted: 09 Aug 2013 03:17 PM PDT

The table that needs partitioning contains a list of Components and their Values.

So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the Components/Fields, so it has a Component foreign key as well as a Field foreign key. Right now this tblValues table contains 68,000,000 values. Components table contains ~80,000 rows. Each component has around 500-800 fields of inventory.

I was thinking about creating a partition by hash on the field id. As the user will query, they will pick something like.. ."Show me the components where field id 5 = '03530'" Something along those lines.

Sample queries are below that are common:

select * from tblComponents comp inner join tblValues v on comp.comp_id = v.comp_id  where v.field_id = 55 and dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Also, the values are currently stored as CLOB's and can have numeric values or string values.

Any tips regarding how you would partition or index this would be great, especially for optimizing querying.

Sub-query in update function

Posted: 09 Aug 2013 11:30 AM PDT

I have a really terrible set of data that I've inherited and part of my job is going through and cleaning it up to something that's actually usable. Currently, I'm trying to update a column in my table based on the name of entry.

Table: (category is currently null for almost everything)

+-----------------------------------------------------+  | programID | programName | programVersion | category |  +-----------------------------------------------------+

My query:

update Programs       set category="Adobe Suites"       where programName           in (              select distinct programName                   from Programs                  where programName regexp '^adobe.*cs[1-9]$'              );

The problem is, no matter whatever I try I keep getting the error:

ERROR 1093 (HY000): You can't specify target table 'Programs' for update in FROM clause

I'm not sure what other way this could be done. Any ideas on how to replace the from statement with something else?

Setting up binary replication between two PostgreSQL instances

Posted: 09 Aug 2013 10:47 AM PDT

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191). Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone. I need to have the databases on dbrepb be read-only copies of those on dbrepa.

And so, I set the appropriate settings of dbrepa/postgresql.conf:

listen_address = '*'  wal_level = hot_standby  max_wal_senders = 3 # Aside, I assume '3' is arbitrary

and dbrepa/pg_hba.conf (paying attention to spacing):

host   replication   all   192.168.108.191/32   trust

and dbrepb/postgresql.conf:

hot_standby = on

and dbrepb/recovery.conf (a new file):

standby_mode = 'on'  primary_conninfo = 'host=192.168.108.175'

Each database is empty, but the following command was run on both:

[root@dbrep-]# service postgresql-9.2 initdb

Now, since there are no databases, I don't need to worry if they are out of sync (since there is no data to be out of sync). I start the master:

[root@dbrepa]# service postgresql-9.2 start  Starting postregsql-9.2 service:   [  OK  ]

and I start the standby:

[root@dbrepb]# service postgresql-9.2 start  Starting postregsql-9.2 service:   [FAILED]

Examining the log files of each:

dbrepa/data/pg_log/-.log is empty.

dbrepb/data/pg_log/-.log:

LOG:  entering standby mode  WARNING:  WAL was generated with wal_level=minimal, data may be missing  HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.  FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server  HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.  LOG:  startup process (PID 4320) exited with exit code 1  LOG:  aborting startup due to startup process failure

but when I double-check dbrepa/data/postgres.conf, wal_level is clearly set to hot_standby.

What else could be going wrong?

isql*plus crashes in 9i when multiple user execute query simultaneously in oracle 9i

Posted: 09 Aug 2013 06:18 PM PDT

In my workplace Multiple users are using isql*plus(oracle 9i) to access single database through server url .But after some time isql*plus crashes and its services needs to started.

Its causes a lot of time waste and is also decreasing the productivity please help. Sever is a desktop with 2 GB of RAM and 2.5Ghz AMD processor No of user 25.

  1. The way database is accessed isql*plus
  2. isql*plus is insatalled in pc in which main database access is provided (like proxy server ). here is the main problem and you can say isql*plus services crashes in proxy server.
  3. If services runs correctly then its routed to database and database gives result to proxy server.
  4. user see the result in isqlplus browser(url =ip address of proxy ).

clients make request using

Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

Posted: 09 Aug 2013 01:09 PM PDT

We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived from the table and the Index has not been rebuilt since. By rebuilding I'm hoping to reduce the size of the index considerably and free up a lot of space in our database.

The problem we have is the disk only has 20GB of free space and I'm worried how much will be temporarily required when rebuilding the index.

What is the best way to do this? Do I bite the bullet, rebuild the index and hope there is enough space? Or do I drop the PK and rebuild it from scratch?

Archiving Details
The archiving job is done on a datetime column and archives everything older than a year. This should run in parallel with the primary key which is an autoincrement since the datetime column is also autopopulated when a row is created.

DB, Table and Index Details
Row Count: ~212,300,000
Data space: ~109,000 MB
Index space: ~517 MB
Recovery model: Full

Data file free space: 5,991 MB / 151,000 MB
Data file drive space: 18.7 GB

Log file free space: 2275 MB / 25000 MB
Log file drive space: 79 GB

tempdb free space: 7476 MB / 19232 MB

There is only one index on the table, which is the clustered primary key:

Key Columns: 1x bigint
Sort: Ascending

We're using SQL Server 2008 R2 Standard Edition.

Meaning of SQLIO output "counts per second"

Posted: 09 Aug 2013 09:05 AM PDT

I realise that my question isn't strictly 'database' but I know that a lot of DBA's will use the SQLIO tool to check I/O performance on their systems so I'm hoping that somebody here will be able to answer this question:

When I run SQLIO the 3rd line of output says something like:

using system counter for latency timings, 14318180 counts per second

What does that mean? I get different values on different systems, and because I'm comparing different systems I would like to know what that number is telling me (if anything).

Locking in "Read Committed Snapshot"

Posted: 09 Aug 2013 02:44 PM PDT

IF update command is run on a table with "Read Committed_Snapshot" isolation level and Commit is pending

eg:

1) update table1 set col1 = col1 + 1 where PKcol < 3

2) update table1 set col1 = col1 + 1 where PKcol = 3

3) update table1 set col1 = col1 + 1 where NonPKcol < 3

4) update table1 set col1 = col1 + 1 where NonPKcol = 3

5) update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

(In above case PKcol is primary key in table and NonPKcol is a non-primary key)

then whether Update is locked for only rows satisfying 'where' condition ? (is it based on value or index or Primary column ?)

How to Drop tables being a SYS user

Posted: 09 Aug 2013 12:34 PM PDT

I am not being able to drop my tables using :

drop table <table_name>;

command. It says "the table is referencing primary keys of some other tables as foreign keys." , and when I tried to drop foreign key constraints using :

alter table <table_name> drop constraint <constraint_name>;

statement. It says: "cannot drop constraint", and when I tried to drop column itself on which foreign key constraint was applied, using:

alter table <table_name> drop <column_name>;

statement. It says: "cannot drop column owned by SYS table.". Somebody told me, SYS doesn't has the privileges to drop columns especially when foreign keys constraint are present, and he suggested me to make new User account apart from default SYS and customise all the privileges. Making new user is very lengthy process and I read many articles of making a new user already, but it doesn't help , coz, I've done the installation by choosing the option of "install database software only" and then later created and configured Database using DBCA(Database configuration Assistant ). I might have skipped certain steps or didn't look it properly while using DBCA and just kept the default settings.

Creating a new user requires you to use the CREATE USER privilege and the syntax given in the oracle documentation to create user is :

CREATE USER sidney   IDENTIFIED BY out_standing1   DEFAULT TABLESPACE example   QUOTA 10M ON example   TEMPORARY TABLESPACE temp  QUOTA 5M ON system   PROFILE app_user   PASSWORD EXPIRE;

This is the example I copied from the oracle documentation. Now the problem I am facing is with the PROFILE keyword in the above statement. I don't know what value to fill for PROFILE keyword and moreover the documentation suggests :

"Oracle recommends that you use the Database Resource Manager rather SQL profiles to establish database resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use."

Now, when I started looking for Database Resource Manager, I came accross various steps such as:

To administer the Resource Manager in Enterprise Manager:

1) Access the Database Home page.

and it says: The Database Home page is the main database management page in Oracle Enterprise Manager Database Control (Database Control).

It asks to confirm certain steps to access the "DataBase Home Page" such as :

1.) Ensure that the dbconsole process is running on the database host computer.

I checked the dbconsole process by running the following statement on the command prompt :

C:\oracle_base\product\11.2.0\dbhome_1\BIN>./emctl status dbconsole

It showed a warning on the command prompt saying :

"OC4J configuration issue . C:\oracle_base\product\11.2.0\dbhome_1\oc4j\j2ee\OC4J_DBConsole_rubbalbhusri-PC_orcl not found."

Note : rubbalbhusri-PC is my system-name(the name I defined for my Operating System)

Now, my question is this :

Is "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory created during installation itself ?

Why, I don't have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

What should I do to have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

Please don't tell me to install the whole "oracle database 11g" again.

Tell me some repair, which solves this problem, if any ?

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Posted: 09 Aug 2013 10:37 AM PDT

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million rows.

I am having desperate problems in getting the data out of this table to migrate it to another server.

I have tried all sorts - mysqldump (with and without -quick), dumping the results of a query via the command line. Using a MySQL admin tool (Navicat) to open and export the data to file, CSV, or do a data transfer (line by line) to another DB and/or another server but all to no avail.

When trying to use the DB admin tool (Navicat), it gets to approx 250k records and then fails with an "Out of memory" error. I am not able to get any error messages from the other processes I have tried, but they seem to fall over at approximately the same number of records.

I have tried playing with the MySQL memory variables (buffer size, log file size, etc) and this does seem to have an effect on where the export stops (currently I have actually made it worse).

Also - max_allowed_packet is set to something ridiculously large as I am aware this can be a problem too.

I am really shooting in the dark, and I keep going round and round trying the same things and getting no further. Can anyone give me any specific guidance, or recommend perhaps any tools which I might be able to use to extract this data out??

Thanks in hope and advance!

A little more information below - following some questions and advice:

The size of the table I am trying to dump - it is difficult to say, but the sql dump gets to 27gb when the mysqldump dies. It could be approximately 4 times that in total.

I have tried running the following mysqldump command: 

mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql

And this gives the error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table attachments at row: 251249 – Mayb2Moro 4 hours ago

The server has 8gb RAM, Some of the relevant settings copied below. It is an INNODB database/table. 

innodb_buffer_pool_size=3000M  innodb_log_file_size=1113M  max_allowed_packet=2024M  query_cache_size=52M  tmp_table_size=500M  myisam_sort_buffer_size=50M

Cant get postgresql running on ubuntu 12.04

Posted: 09 Aug 2013 01:09 PM PDT

I ran sudo apt-get install postgresql on ubuntu 12.04. I didnt get a postgresql.config, instead I got a postgresql.config.sample and pg_hba.conf.sample. I renamed these (they installed in usr/share/postgresql) and uncommented listen address to localhost and the port to 5432. But still it doesnt seem to start? I get could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? in my webapp log (no log from postgresql). I have no clue what Im doing could someone please help me? I followed all the tutorials I found on the internet (I re-installed my server 3 times). But I cant get it to work.

Thanks.

Full text search through many tables in MySQL

Posted: 09 Aug 2013 01:50 PM PDT

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its own table(s).

I decided to create a table that holds all of contents from all types:

CREATE TABLE full_text_search  (      master_id INT NOT NULL,      content_text TEXT NOT NULL,      PRIMARY KEY ( master_id )  );

I generate a unique number as master_id for each content of all types to identify each content_text in full_text_search table.

for example:

News table:  +----+-------------+---------+---------+----------+------------+  | id | news_title  | lead    | subtitle|  content | master_id  |  +----+-------------+---------+---------+----------+------------+  |  1 |  sometitle  |some lead| subtitle|content 1 |     3      |  |  2 |  some title |some lead| subtitle|content 2 |     5      |  +----+-------------+---------+---------+----------+------------+    article table:  +----+-------------+---------+------------------+---------+------------+  | id | title       | author  | short description| content | master_id  |  +----+-------------+---------+------------------+---------+------------+  |  1 |  sometitle  | someone | very short desc  |content1 |     1      |  |  2 |  some title | otherone|  some short desc |content2 |     4      |  +----+-------------+---------+------------------+---------+------------+

As you can see master_id is unique between above tables. When ever a new content from each type inserted, also I should INSERT it in to full_text_search table.

QUESTIONS

  • For many inserts for a day(about 3000 from all types), is it a good solution or it is anti pattern?
  • Is it better choice if I separate this table from my other tables, and put it in any other DB such as other RDBMS or NoSQLs?
  • Any other solutions?

Need help designing table with list of IDs to store

Posted: 09 Aug 2013 02:49 PM PDT

I have a table that I need to create to hold saved emails for FUTURE delivery (to, from, message, scheduled send date, etc).

The catch here is that I don't know who's supposed to receive the email until the day of delivery.

What I mean is, the email is created to go to certain selected organizations, but the "members" of the organization will be constantly joining and leaving, so if someone creates an email for delivery next month, the list of member email addresses to send to by then will be different.

So, what I need to save in the table is the list of organizations that the email should go to, so that I can query for the latest list of member email addresses when I actually send the email. Hope that makes sense.

Anyway, so my question is: what is considered a "proper design" for this?

My initial thought is to just save a comma delimited list of organization IDs.

I know I will never have to search on which organizations were on the list, so I don't care if it's not query-able, and I know I could normalize it into one row per recipient organization, but it seems such an unnecessary repeat of data for no purpose, especially since I only query on the SENDER not the recipients.

So is a list of IDs just a horrible, no good, only-a-newbie-would-think-of-that, bad thing? Or can it be used in some cases? Or is there some other way to do this that I don't know about? I'm sure I can't be the only one who's run into a situation like this before!

table with item names as a composite key - joining with missing combinations

Posted: 09 Aug 2013 04:09 PM PDT

I've got a table design with many levels of item, and each has a name that's inside a separate table with a composite key linking to a language table.

i.e. Country, City, Street, Building, Floor, Suite. Each with the 1 to many relation.

if a country has two languages (fr + en) the database will be polled for both, and expecting a value in both languages whether or not it exists.

I'd like to create a query (a view ideally) that can combine all these rows. The problem I keep hitting is that I either get all variations of languages/names across each item, or i lose entries that do not have that language value and any subsequent(child) items.

language: [id,name]  country: [id]  countryName: [languageid,countryid,name]  city: [id,countryid]  cityName: [languageid,cityid,name]  building: [id,cityid]  buildingName: [languageid,buildingid,name]

etc...

what I have done is created a table that links all levels (country,city,building, etc.) only the cityname(which determines if there is an alternate name.

then I do a join the tables in a view and get all the names as-well. I've added a line that adds the alternate value which is something like this ISNULL(dbo.buildingNames.name, (SELECT TOP (1) name FROM dbo.buildingNames AS x WHERE (buildingId = dbo.buildings.id)))

it works ok, but I'm it's getting a bit messy and slow. Using views, this requires each item to have it's separate name view, which relies on recreating the hierarchy to check which languages are needed.

the other problem related to this, is that I have a location Table that can have multiple items used.

i.e. assuming that the location is restricted to being on a floor, or in a suite.

ballLocation: [ballid,floorid,suiteid]

if i have both filled out I can use the suiteName view(suite with atlernate names) which already has the floorName, but if I only provided the floorID, then I can't use the same view, but have to use two separate ones.

Update

schema + data

this view [CitiesBuidingsFloorsSuites]: 

SELECT     dbo.Cities.ID AS CityID, dbo.Buildings.ID AS BuildingID, dbo.Floors.ID AS FloorID, dbo.Suites.ID AS SuiteID  FROM         dbo.Suites RIGHT OUTER JOIN                        dbo.Floors ON dbo.Suites.FloorID = dbo.Floors.ID RIGHT OUTER JOIN                        dbo.Buildings ON dbo.Floors.BuildingID = dbo.Buildings.ID RIGHT OUTER JOIN                        dbo.Cities ON dbo.Buildings.CityID = dbo.Cities.ID

shows all the Cities, Buildings, Floors and Suites.

#1

I'd like to extend this, so that I have a view where every item has a name for every language that is defined in CityLanguages. (if a city doesn't have a language, no other linked tables(building,floor,suite,object) need that language)

#2

essentially the same for the Objects (get all names in all languages where combination exists in citylanguages), the one difference is that the suite name is not mandatory, because ObjectLocations can have a NULL suiteID.

I've been able to get this working in a way that I'm not too happy with. I'm using too many views to get to this point, and because I use the last-level view to filter a city or building, the views that are used to generate the final view do a lot of redundant compiling.

I hope this clears it up

Changing password doesn't work

Posted: 09 Aug 2013 03:58 PM PDT

I need to login to a database. I have good credentials, but when I was trying to login with SQL Developer, I got this error message:

ORA-28001: the password has expired

Then I tried to login with SQL*Plus, which worked fine, I connected successfully and I was asked to change my password.

So I typed in a new password and then retyped it. Then I got other message:

ORA-01017: invalid username/password; logon failed

Can you help me how can I change this password? I don't have administrative permissions.

Difference between partitioning and fragmentation

Posted: 09 Aug 2013 12:58 PM PDT

I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results (deviding a relation horizontally/vertically/both).

So what's the difference between fragmentation and partitioning?

disk I/O error in SQLite

Posted: 09 Aug 2013 01:58 PM PDT

What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file that contains SQL inserts.

Sample data in the file:

insert into files (filesize, filedate, md5, fullpath, origin) values (5795096,1370159412, "e846355215bbb9bf5f30102a49304ef1", "SDs/16G-1/DSC00144.JPG", "SDs");   insert into files (filesize, filedate, md5, fullpath, origin) values (5435597,1370159422, "1a7bcf3a4aaee3e8fdb304ab995ff80f", "SDs/16G-1/DSC00145.JPG", "SDs");  insert into files (filesize, filedate, md5, fullpath, origin) values (5121224,1370159432, "16d28e83599c731657a6cd7ff97a4903", "SDs/16G-1/DSC00146.JPG", "SDs");

I tried inserting that in the db file with the following command:

$ sqlite3 allfiles.db < insert.sql

See below the error that I get:

Error: near line 27: disk I/O error  Error: near line 28: disk I/O error  Error: near line 34: disk I/O error  Error: near line 39: disk I/O error  Error: near line 47: disk I/O error  Error: near line 129: disk I/O error

The input lines that don't generate error are successfully included, but I don't understand why some lines have errors, and they are not inserted into the DB. There's nothing special in the lines with error, and if I run the command again I get errors in different lines, which means it's random (not related to the data itself). I tried adding pragma syncrhonous = off; and pragma temp_store = memory;, to no success. I'm running that on a lubuntu, which runs in a VirtualBox virtual machine. The host machine is a windows 7. The pwd of the files is a shared folder, i.e., it's a folder in the host machine. If I run it in a "local folder" in the guest machine, the error doesn't happen, although for some reason it's much slower... In any case, I'd like to know about the I/O error.

SQL Server Import Job Removed Data After Job Failed

Posted: 09 Aug 2013 10:58 AM PDT

I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.

Creating the MySQL slow query log file

Posted: 09 Aug 2013 11:58 AM PDT

What do I need to do to generate the slow logs file in MySQL?

I did:

 log_slow_queries  = C:\Program Files\MySQL\MySQL Server 5.1\mysql-slow.log    long_query_time  = 1

What more do I need to do to?

MariaDB, Triggers and last_insert_id

Posted: 09 Aug 2013 03:15 PM PDT

I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information).

CREATE TABLE `sample` (    `sample_id` int unsigned NOT NULL AUTO_INCREMENT,    PRIMARY KEY (`sample_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    CREATE TABLE `sample_name` (    `sample_name_id` int unsigned NOT NULL AUTO_INCREMENT,    `name` varchar(255) NOT NULL,    `project` varchar(255) NOT NULL,    `sample_id` int unsigned NOT NULL,    PRIMARY KEY (`sample_name_id`),    FOREIGN KEY `fk_sample` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,    UNIQUE KEY `un_sample_name` (`name`,`project`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    CREATE TABLE `run` (    `run_id` int unsigned NOT NULL AUTO_INCREMENT,    `sample_id` int unsigned NOT NULL,    PRIMARY KEY (`run_id`),    FOREIGN KEY `fk_run1` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I would like to check if a sample (based on sample_name) exists or if not, then insert both and use the sample_id to insert run entry as well. To do this I planned first to check if the unique name (name + project) exists and if so use it. However if it doesn't, then use trigger to do double insertion (I have a perl script to do this logic; I tried also the commented update version).

delimiter |  CREATE TRIGGER tr_new_sample BEFORE INSERT ON sample_name FOR EACH ROW BEGIN    IF NEW.sample_id IS NULL THEN      INSERT INTO sample () VALUES ();      -- UPDATE sample_name SET NEW.sample_id = LAST_INSERT_ID();      SET NEW.sample_id := (SELECT LAST_INSERT_ID());    END IF;  END |  delimiter ;

So when sample name does not exists, I try to insert a record with null sample_id and thus the trigger reacts. However this doesn't work and I get a following error. Can someone suggest on how to fix this or make it work.

The database is actually MariaDB 5.2.12.

Thanks,

mysql> INSERT INTO sample_name (name,project) VALUES ('a','a_project');  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pipeline_runs`.`sample_name`, CONSTRAINT `sample_name_ibfk_1` FOREIGN KEY (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE)

Open source and commercial tools to build dynamic queries of fact tables in star schema

Posted: 09 Aug 2013 02:57 PM PDT

What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z cross reference tables, we'd like to query a random fact table. If you are aware of such tools, can you provide a reference of each.

Color syntax printing in SSMS 2012

Posted: 09 Aug 2013 04:41 PM PDT

It seems the SSMS of Sql server 2012 cannot print the SQL statements with syntax color anymore. Is it a bug? Does it have any workaround?

SSIS Script to split string into columns

Posted: 09 Aug 2013 02:58 PM PDT

I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, etc...). The output columns should be something like:

U1   U2  U3                  U4   U5   etc.  null odw odw : CH : de : hom null null     EUR  sss DE:de:hom           null null     EUR  crm crm                 null null

Other-Data:

u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v  u1=EUR;u2=sss:Checkout-Step4:Orderacknowledgement;u3=DE:de:hom;u11=1;u12=302338533;u13=SVE1511C5E;u14=575.67;~oref=https://shop.bidl.de/shop/bibit/success.do  u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do

Can anyone help with this?

Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

Posted: 09 Aug 2013 09:58 AM PDT

I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).

The subscription databases are also SQL Server 2008 R2, however the destination databases are set to compatibility level 100 and replication is working fine.

If I change the compatibility level for the Published databases, will it affect replication in any way, or will it just be a case of reinitializing all the subscriptions and restarting replication?

I suspect that changing the published database compatibility level may change how the replication stored procedures function slightly, but I'm not 100% sure.

Is this the case?

Running total with count?

Posted: 09 Aug 2013 12:16 PM PDT

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: 

sum(count (distinct (customers)))

Say if I ran the count alone, the result would be: 

Day | CountCustomers  ----------------------  5/1  |      1  5/2  |      0  5/3  |      5

I need output with the sum to be :

Day | RunningTotalCustomers  ----------------------  5/1  |      1  5/2  |      1  5/3  |      6

I've done running totals before using the coalesce method, but never with a count. I'm not sure how to do it now that I have the count.

Best practices on common person fields (Name, email, address, gender etc...)

Posted: 09 Aug 2013 03:13 PM PDT

What are the most common best practices on length and data type on common fields like:

  • First Name
  • Last Name
  • Address
  • Email
  • Sex
  • State
  • City
  • Country
  • Phone Number

etc....

Posted by at No comments:

[MS SQL Server] How to find the SQL Server databse Last used by db users ?

[MS SQL Server] How to find the SQL Server databse Last used by db users ?

How to find the SQL Server databse Last used by db users ?

Posted: 10 Apr 2012 02:22 AM PDT

How to find the SQL Server databse Last used by db users ?

event 11

Posted: 08 Aug 2013 10:40 AM PDT

Hi gurusI need some serious help and I hope some one here can help me with my problem, I am new to sql so go easy on meI was checking my sql server (sql2008 R2) on a windows 2008 R2 server , when suddenly I start to get an error on my event viewer MSOLAP$myserverevent 11An error occurred while writing a trace event to the file, \\?\f:\OLAP\Log\FlightRecorderCurrent.trc.I don't know how to troubleshot this error, so any help I can get would be appreciated Thanks a bunch gurus

Upgrade from Sql 2003 to Sql 2008

Posted: 09 Jul 2013 11:08 PM PDT

Hi,We are planning to upgrade from Sql 2003 to Sql 2008 and I would like to know what needs to be prepared and how to do as I will doing very first time.We have sql 2003 - on windows 2003 server with 4 CPU and 4 GB Memory.We have almost 12 Databases and biggest DB is around 250 GB which is also feed by Replication DB as subscriber and Publisher resides on 3rd party.1) What should be the new hardware and server we should configure?2) What other things we need to consider?3) What's the proper steps to migrate/upgrade DB?Thanks,

Ideas on how to handle 1000+ databases in one instance

Posted: 08 Aug 2013 07:04 PM PDT

Hi all,I am looking for some feedback on a solution I've been tasked with providing.Currently, we have a subsidiary company who provides a pretty lightweight application to their client-base. The application is based on a SQL Server 2008 database with approximately 30 user tables in (containing client-specific data), and around 70 - 90 system tables (containing unchanging system-related information on the product).Their current method of deployment is one database per customer in a fully managed environment (client has no access to the infrastructure or db), resulting in a SQL server with around 1000 individual databases on, ranging from just a few MB to a few hundred MB.There are a number of recommendations I am going to make to them, but my main quandry is how best to provide a solution to them to make the product more efficient. It would seem to me that the one db to one customer approach is pretty inefficient in storage, performance and maintenance.My thoughts are to have one db containing all data for all customers, and use schemas to separate each environment (with the obvious object-level security changes required for this approach). Further to this, I thought of suggesting one Filegroup per customer and grouping all objects for each customer into said Filegroups. This would provide for relatively easy backups/restores and performance. I was also going to suggest having a separate db or schema for the system data.I've checked the maximum capacity specs for SQL Server and it looks like it shouldn't be a problem, but I wondered if anyone had any experience of this scenario and/or any alternatives or better suggestions.Thanks in advance,Mike.

Linked server issue

Posted: 08 Aug 2013 11:07 AM PDT

I am trying to use the security option Be made using the login's current security context to setup linked server. I have two server A and B and I have sysadmin rights on both the boxes. I have setup linked server from A to B and B to A using the above setting. I can run the cross communication queries from A to B but the cross communication queries fail from B to A with error login failed for NT Authority\Anonymous login. I can add impersonate my domain account with sql server acct to make it work. But I am trying find the exact reason behind this behavior
Posted by at No comments:

[Articles] Would You Choose SQL Server?

[Articles] Would You Choose SQL Server?

Would You Choose SQL Server?

Posted: 08 Aug 2013 11:00 PM PDT

This Friday Steve Jones has a poll asking how you feel about SQL Server for your own projects, or even your company's projects. Is it a given you'd use SQL Server?

SQL Compare Need to compare and sync database schemas?
Let SQL Compare do the hard work. "With the productivity I'll get out of this tool, it's like buying time." Robert Sondles. Download a free trial.
Posted by at No comments:

[SQL 2012] Backward compatibility mode for SQL Server 2012

[SQL 2012] Backward compatibility mode for SQL Server 2012

Backward compatibility mode for SQL Server 2012

Posted: 09 Aug 2013 01:39 AM PDT

I got a requirement that I need to install SQL 2012 and make sure that its compatible to host SQL 2008 R2 applications only.I went to the model database properties and changed the compatibility and tested creating a new database and that got created in SQL 2008 mode.Was this method right?Also, is there a seperate compatibility mode for SQL 2008 and SQL 2008 R2?

Always On Primary Offline = Secondary Offline

Posted: 09 Aug 2013 01:57 AM PDT

Setup FCI01 – SEVER A,B,C,D Named Instance 1- Primary ReplicaPreferred Nodes A and BFCI02- SEVER A,B,C,D Named Instance 2 – Secondary ReplicaPreferred Nodes C and D.Quorum Disk- For VotingSERVER E – DFR server holding a secondary replica.Always On Listener – on NODE A – NAMED INSTANCE 1FCI01 -Primary running on Node AFCI02 – Secondary running on Node CSERVER E – Secondary DR replica.1 Primary 2 Secondary copies.----------------------------------------------------------------------------------Perform a manual Windows Failover to NODE B. The Always On Listener goes offline.This can be resolved by reverting back to NODE A. Or failing over the listener to convert the Secondary AOG to the AOG Primary before doing the Windows failover . Perform the windows failover the Fail to NODE B. Then fail the AOG back to Instance 1 . Always On Listener will now be running on Node B.However if there was a hardware failure to NODE A while it was running instance 1 and the Always On Listener was using this node A. How to recover the listener as it will be pointing to Node A as it is not windows cluster aware and only recognises the node name. Databases are in SYNC Pending mode and all dbs in the AOG are not accessible.Primary offline = Secondary offline. I know this is fixed in SQL 2014 but what is the work around?

Query returning one extra record

Posted: 08 Aug 2013 06:25 PM PDT

I have a query that is returning two copies of the last record. The record exists only once - there is an index on the table to prevent two such records, and examining the actual table confirms that it is present only once. The field [EvidenceLetter] selects groups of related records. No other value besides 'E' exhibits this behavior.It is definitely the same record - my application displays the unique autonumber primary key, a when I select one (either one) of the duplicates in the application and make a change, the change is immediately reflected in both displayed copies of the record.Both the application AND SSMS give the same results. I can't think of anything that might be causing such a thing. Can anyone give me some ideas on where to start looking for the problem?I call it from a MSAccess application as a pass-through query, and from SSMS via an EXEC statement, like this:[code="vb"]Exec NoFiltr.spPodrobnosti @What = 'Zaznamy', @LtrFiltr = 'E'[/code]The execution plan is attached, and here is the query generated and executed by the stored procedure:[code="vb"]SELECT * FROM ( SELECT P.PodrobnostiAutoID, P.EvidenceLetter, P.EvidenceNumber, P.EvidenceExtra, P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok, P.PocetKusu, P.NepublikovatYN, P.Lokalita, tT.Zkratka Typ, P.Original, P.Popis, P.Poznamka, P.SystemAutoID, P.SeriesAutoID, P.StageAutoID, P.SubStageAutoID, P.LithographicUnitAutoID, P.LithographicSubUnitAutoID, P.ZoneAutoID, P.GroupAutoID, P.OrderAutoID, P.FamilyAutoID, P.GenusAutoID, P.SubGenusAutoID, P.SpeciesAutoID, P.SubSpeciesAutoID, P.ODAutoID, P.PDAutoID, P.OriginAutoID, P.TypAutoID, case when P.EvidenceLetter Is Not Null then P.EvidenceLetter + ' ' + convert(varchar(5),P.EvidenceNumber) + case when P.EvidenceExtra <> '' then ' (' + P.EvidenceExtra + ')' else '' end else '' end AS DrEvid, case AkcesitPred when 'A' then A.AkcesitPred + '/' when 'Br' then A.AkcesitPred + '-' else '' end + convert(varchar(5),A.Akcesit) + '/' + convert(varchar(4),A.Rok) AKC, CASE WHEN tDF.DepozitarAutoID IS NULL THEN '' ELSE tDF.DepozitarFormatted END + CASE WHEN UlozisteDocasne IS NULL THEN '' ELSE Replace(Replace(' {' + UlozisteDocasne + '}','{{','{'),'}}','}') END AS Ulozeni, tNI.Clovek + ', ' + left(CONVERT(varchar(20), tNI.Datum, 21),10) As Inventarizace, dbo.fnsTaxonomieFormatted(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS Taxonomie, dbo.fnsTaxonomie(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS TaxonomiePlain, dbo.fnsStratigrafie(tSy.[System], tSe.Series, tSt.Stage, tSSt.SubStage, tLU.LithographicUnit, tLSU.LithographicSubUnit, tZo.Zone) AS Stratigrafie FROM Podrobnosti P INNER JOIN dbo.fntStringsToTable ('E') L on L.EvidenceLetter = P.EvidenceLetter INNER JOIN dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID LEFT JOIN vwTableOfDepozitars tDF ON P.DepozitarAutoID = tDF.DepozitarAutoID LEFT JOIN vwNejnovejsiInventarizace tNI ON P.PodrobnostiAutoID = tNI.PodrobnostiAutoID LEFT JOIN TableOfGenuss tGe ON P.GenusAutoID = tGe.GenusAutoID LEFT JOIN TableOfSubGenuss tSGe ON P.SubGenusAutoID = tSGe.SubGenusAutoID LEFT JOIN TableOfSpeciess tSp ON P.SpeciesAutoID = tSp.SpeciesAutoID LEFT JOIN TableOfSubSpeciess tSSp ON P.SubSpeciesAutoID = tSSp.SubSpeciesAutoID LEFT JOIN TableOfFamilys tFa ON P.FamilyAutoID = tFa.FamilyAutoID LEFT JOIN TableOfOrders tOrd ON P.OrderAutoID = tOrd.OrderAutoID LEFT JOIN TableOfGroups tGr ON P.GroupAutoID = tGr.GroupAutoID LEFT JOIN TableOfSystems tSy ON P.SystemAutoID = tSy.SystemAutoID LEFT JOIN TableOfSeriess tSe ON P.SeriesAutoID = tSe.SeriesAutoID LEFT JOIN TableOfStages tSt ON P.StageAutoID = tSt.StageAutoID LEFT JOIN TableOfSubStages tSSt ON P.SubStageAutoID = tSSt.SubStageAutoID LEFT JOIN TableOfLithographicUnits tLU ON P.LithographicUnitAutoID = tLU.LithographicUnitAutoID LEFT JOIN TableOfLithographicSubUnits tLSU ON P.LithographicSubUnitAutoID = tLSU.LithographicSubUnitAutoID LEFT JOIN TableOfZones tZo ON P.ZoneAutoID = tZo.ZoneAutoID LEFT JOIN TableOfTyps tT ON P.TypAutoID = tT.TypAutoID ) PP WHERE 1=1 Order By PP.EvidenceLetter, PP.EvidenceNumber, PP.EvidenceExtra[/code]

SQL Server Benchmark Testing - Urgent

Posted: 08 Aug 2013 02:11 PM PDT

[b]Is SQLIOsim an older tool? Is SQLIOStress a newer tool? I want to stress test a SQL 2012 environment which is a VM environment.Also, other than HammerDB, is there another TPC-C tool which is a freeware?Any other ideas/tools for SQL Server bench mark testing?[/b]I am trying to test one of our environment which is pre-production and is a clustered environment and hooked up to SAN which is RAID-DP.The following are the statements I am trying to run and it errors out saying there is not enough space on the drive even though it has about a TB free.This is what I have in params.txtL:\sqlIO\testfile.dat 2 0x0 100This is what I have in the batch file which contains the sqlio commandsqlio -kW -t16 -s180 -dl -o8 -frandom -b16 -BH -LS Testfile.dat[b]Error is:[/b]do_ovlp_io: WriteFileEx: There is not enough space on disk.file offset high: 0x00000493 low: 0x3a54c000As you can see even though I say 100MB file, it creates a test file for 3.5 GB or something and finally fails saying there is not enough disk space when the L drive contains about 935GB free.Any ideas?

using a multi-valued parameter in SSRS 2012

Posted: 08 Aug 2013 04:54 PM PDT

If I use a single-valued parameter, it works a champ.I'm trying (and apparently failing) to follow Brian Knight's example, which is here: [url=http://www.sqlservercentral.com/articles/Video/64369/][/url]I changed my stored procedure, so now it looks like this:[code="sql"]ALTER PROC uspEnrollReport @StartWeek INT, @EndWeek INT, @ProtocolList VARCHAR(1000)ASBEGIN WITH EnrollData (Protocol, StudyWeek, WeeklyGoal, ActualEnrolls) AS ( SELECT eg.ProtocolNo, eg.WeekNumber, eg.Goal, COUNT(e_PatientID) AS EnrollCount FROM EnrollmentGoal eg INNER JOIN enroll e ON eg.ProtocolNo=e_ProtocolNo AND eg.WeekNumber=PWeek GROUP BY eg.ProtocolNo, eg.WeekNumber, eg.Goal ) SELECT Protocol, StudyWeek, WeeklyGoal, ActualEnrolls, ActualEnrolls - WeeklyGoal AS OverUnder FROM EnrollData WHERE StudyWeek BETWEEN @StartWeek AND @EndWeek AND Protocol [b]IN (@ProtocolList)[/b];END[/code]I changed the parameter to be multi-value (check the box) - simple enough.Everything appears to work, but when I run the report, it only filters for the first checked item. What blindingly obvious thing am I doing wrong?Thanks!Pieter

"Run Immediately" Disabled in Import Wizard

Posted: 08 Aug 2013 07:57 AM PDT

I just started using SQL Server 2012 but have used 2000, 2005 and 2008 and have always used the import wizard to import Excel files. The "run immediately" option is disabled on the wizard so my only option is to save the import as an SSIS package. I do a lot of ad-hoc imports and don't really want to save any import as a package unless I will continually use it. My questions is how do you enable the "run immediately" option on the wizard? Thanks.

Rant: Can`t Wait Until They Bring MDI Windows Back to SSMS in 2012 ... But by then I Will Probably Be Gone ...

Posted: 08 Aug 2013 07:52 AM PDT

... after almost 15 years of using SQL Server As great as SQL Server is ... this is yet another reason I am switching to the LAMP stack. Aside: Anyone remember [ctrl-B]? Used to let you grab the splitter bar between the query window and the result pane instead of having to play air hockey with your mouse to grab the (nearly) invisible line to adjust the height (occurs to me that I should write a plug-in for that rather than just whine). No MDI: There is (another) rant here: http://connect.microsoft.com/SQLServer/feedback/details/713996/ssms-2012-bring-back-mdi-environmentThis is apparently the/a response by M$ "Posted by Microsoft on 12/15/2011 at 10:26 PMGot it.We lost this feature when SSMS moved to the VS2010 shell. Apparently, getting MDI back has been a strong ask on VS as well. See: http://connect.microsoft.com/VisualStudio/feedback/details/576371/put-mdi-back-asap Unfortunately, this is not something SSMS can fix on it's own until this feature comes back into the shell (which the VS team is looking at in their next release).I'm closing this bugs as external for now. Meanwhile, have you tried the MDI tabbed group feature? Right click on a tab, and choose to create a horizontal or vertical tab group. More info here: http://msdn.microsoft.com/en-us/library/cc165830.aspx"

SSRS - Values in X-axis and Y-axis

Posted: 08 Aug 2013 07:24 AM PDT

Hi Experts,I am new to SSRS. My requirement is to populate ElapsedTime column in Y-axis and Starttime column X-axis. Can you please let me know how to do it. I am confused with values, Series groups, category groupsAny input is appreciated

Always On troubles

Posted: 08 Aug 2013 05:26 AM PDT

Ok, I am about at my limit on this. I am trying to set up SQL 2012 Always On. I have tried setting it up with Two Instances on the same 2 node cluster. Did not work.Now I have two separate clusters. Cluster1 with the database I want to replicate to the other cluster. Cluster2 with no database yet.Getting an error that says The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster node. I get this message when I try to add the Cluster2 as a replica.What am I doing wrong?Thank you

Deny execution of a particular SQL statement

Posted: 08 Aug 2013 03:28 AM PDT

SQL Server 2012 Enterprise We have a database table/view that we want users to be able to select from, but we don't want them to be able to JOIN this object to other objects in a SQL statement.Any ideas on how to accomplish this? Custom error code triggered through a database level trigger? I don't see how this is possible using a DML, DDL or logon trigger.Evaluate SQL statements using extended events and then trigger a custom error code if needed? Is that possible?
Posted by at No comments:

[T-SQL] try/catch not taking catch path

[T-SQL] try/catch not taking catch path

try/catch not taking catch path

Posted: 08 Aug 2013 09:00 AM PDT

if you have the following code (where temptable does not exist):[code]BEGIN TRANBEGIN TRY PRINT 'Before bad insert' INSERT temptable VALUES ( 1, 2, 3 ) PRINT 'After bad insert' COMMIT TRANEND TRYBEGIN CATCH PRINT 'Inside CATCH before ROLLBACK' ROLLBACK TRAN PRINT 'Inside CATCH after ROLLBACK'END CATCH [/code]I get the following in my query message window:Before bad insertMsg 208, Level 16, State 1, Line 4Invalid object name 'temptable'.Why doesn't it take the Catch condition?Thanks,Tom

BCP Help

Posted: 08 Aug 2013 09:17 PM PDT

Hi all I am tasked with importing large fixed width text file to SQL, previously I used a library called filehelpers to assist through my code. Now the files are growing I keep getting out of memory errors so decided to try BCP.My command bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmtand the result SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: tt open a connection to SQL Server [2].SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]A network-related or specific error has occurred while establishing a connection to SQL Server is not found or not accessible. Check if instance name is correct and Server is configured to allow remote connections. For more information server Books Online.SQLState = S1T00, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expiredThis is a localhost server with windows authPlease help me learn why, I have tried with the -t switch and same result

try/catch not taking catch path

Posted: 08 Aug 2013 09:00 AM PDT

if you have the following code (where temptable does not exist):[code]BEGIN TRANBEGIN TRY PRINT 'Before bad insert' INSERT temptable VALUES ( 1, 2, 3 ) PRINT 'After bad insert' COMMIT TRANEND TRYBEGIN CATCH PRINT 'Inside CATCH before ROLLBACK' ROLLBACK TRAN PRINT 'Inside CATCH after ROLLBACK'END CATCH [/code]I get the following in my query message window:Before bad insertMsg 208, Level 16, State 1, Line 4Invalid object name 'temptable'.Why doesn't it take the Catch condition?Thanks,Tom
Posted by at No comments:

[SQL Server 2008 issues] Read committed snapshot:Finding lock on table

[SQL Server 2008 issues] Read committed snapshot:Finding lock on table

Read committed snapshot:Finding lock on table

Posted: 08 Aug 2013 06:40 PM PDT

IF update command is run on a table and commit is pending then another user cannot be able to update the table.Is there any query to get the user or PC which caused lock on the table.

The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2

Posted: 10 Feb 2011 07:15 PM PST

hi All,I found this Error The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasescan any body please explain ,if we encounter the above error what are the steps to resolve this errorThanks & RegardsDeepak.A

Calculating rows processed and updated

Posted: 08 Aug 2013 11:03 AM PDT

Hi ProfessionalsI am running a procedure to calculate the total number of rows updated but I also wan the number of rows it hasnt updated, could I be going wrong somewhere would I need to calculated a select count(*) from the table minus the total rows updated or something.anyway here is my procedure[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[cleanseSMPN] Script Date: 08/09/2013 09:25:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[cleanseSMPN]asbegindeclare @TotalRows int =0, @numrows int; --Update the Software Manufacturer and the Product name begin truncate table myupdates UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer, productname = dbref.Amended_Product_Name FROM dbo.newtable dbsource INNER JOIN ( SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name FROM datalookuptable GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name ) dbref ON dbref.Raw_SW_Manufacturer = softwaremanufacturer --and dbref.Raw_Product_Version = dbsource.productversion and dbref.Raw_Product_Name = productname -------------------------------------------------------------------------------------------------------- set @NumRows = @@ROWCOUNT; set @TotalRows= @TotalRows + 1; insert into myupdates(NumRows,TotalRows) values (@NumRows,@TotalRows) --------------------------------------------------------------------------------------------------------- end END[/code]

Replication pre-requisties

Posted: 08 Aug 2013 03:40 PM PDT

Hi All,I am going to set up replication in an OTLP environment. I would like to know what as a DBA should i check before setting up replication.Last time, when I set up replication in such an environment, i got memory issue and SQL Server started generating memory dumps.I had to remove replication temporarily. Please let me know if there is way to determine the memory required for replication.thanks,

Strange File issue &gt; "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file"

Posted: 08 Aug 2013 02:26 AM PDT

Recently I have had to move and restructure my SQL farms and environment. I had detached a SolarWinds database called NetPerfMon that consisted of three data (mdf) files and one log(ldf) file. The database detached without issue. Today one of my engineers needed to retrieve some data and when I went to restore it I found the files still there. That's great I thought. Just re-attach. Then I received this error:"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'd:\userdbs\pathToFile.mdf'"I listed the files and database names from sys.databases and this database is DEFINITELY not loaded in to SQL. When I looked at all the open file handles with Sysinternals "Handle.exe", it lists the netperfmon mdf files as being open by the SQL server process. This server has been rebooted several times during the move since I dropped the original database. Why (WHY!?!?) would SQL still have these files open? I already worked around the issue by restoring it somewhere else but I'm dying of curiosity....why would SQL have these files open when the database is not currently attached to the system and hasn't been for several starts? Also why when attaching to an EXISTING file would it be trying to create the data file as it indicates in the error?

Replication DB migration on new sql server 2008R2

Posted: 08 Aug 2013 07:49 AM PDT

Hello,We will be planning to migrate sql 2005 and sql 2008R2 on new windows 2008R2 server, we have one Replicated DB which ones we are subscribing and we don't have any control over publisher.Could you please guide me that how can we handle it?We are planning to do side by side installation and will be using Backup and Restore method but some worries how we can handle it for Replication DB and which is the biggest DB almost more then 300GB.Thanks,

Messaging from SQL Server Agent to a web service???

Posted: 08 Aug 2013 07:29 AM PDT

Sorry if I didn't place this question in the right forum.....Is it possible to send a SQL Server Agent message on a job process completion % to a web service?? Service Broker?? Not sure...anybody got a direction I can head in???thank you in advance!!:-P

Exports other than bcp/Data Flow tasks

Posted: 07 Aug 2013 11:31 PM PDT

I'm looking for other file transfers than bcp queryout and Data Flow tasks in SSIS packages. Are there other programmatical processes that are upgradable? I hear that bcp is not quite reliable, but it works for us and it will take too long to create 114 individual data flow tasks in an SSIS package. Thanks.

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?

Generate "One Merged Email" from any query result ran against multiple instances in CMS (Central Management server)

Posted: 03 Aug 2013 05:01 PM PDT

Hello,We manage our instances via CMS. As a daily process, we ran a script against PROD group under CMS to check Last night backup and Job failures and many other things. We then place the output of those query result into excel sheet and then manually add colors to this excel and then send report to the group. It's been going on for a while. I like the fact that we use CMS for this info. But I would like to change this process now. I have created a script that ran against any "individual" server and generate a html good looking email with all the filtering we do in the excel. Now I am wondering if I can get this script to run against CMS server and generate one single email with all the data in it, then all our manual work on generating excel will be gone. It will save 90 minutes of our dedicated work everyday.Script is generating output in temp tables and from those temp tables, am filtering out the unwanted stuff and asking it to generate a email. Now the Issue is, when I ran it on CMS Server. It is sending me "individual mails" from "each individual servers" that are part of the CMS group. Resulting I am getting 90+ emails.Is there any way to send only "One Merged Email" with all the data in it, rather then individual emails from all the servers individually with the result of any query ran against CMS.I am not sure if this is possible. Since in sql query option there is a option for multiple server, over there by default "merge" has been set to true. That is how CMS generate all the result merge into a single result window.Is there any way to do this ? Please advice..Thanks!

cook book

Posted: 07 Aug 2013 10:56 PM PDT

what is cookbook in sql , how to install in sql ?

difference between sql2005 and sql2008 installation

Posted: 08 Aug 2013 02:12 AM PDT

Hi All,Can some one tell me the difference b/w installation of SQL Server 2005 and SQL server2008R2.Thanks,Santosh.

Use a Linked Server or move DB location to another Server

Posted: 07 Aug 2013 09:48 PM PDT

I have 2 SQL servers on same domain.ServerOLD: running workgroup edition 2008 R2 with AccOldDB (old accounts DB now read only)ServerNEW: running standard edition 2008 R2 with AccNewDB (new 3rd party accounts system)To maintain legacy reporting I kept the AccOldDB on ServerOLD and added a Linked Server to ServerNEW.In this way I made views that make the new accounts system report data like the old system did.The benefit has been no calling code/reports have had to change, in fact many people do not realise we have changed systems and that the 2 systems have very different schemas.An Example of the views I've changed:[code="sql"]create view AccOldDB..vCustomersASSELECT cust.name, tAdd.Add1, tAdd.Add2, tAdd.PostCode, ....from ServerNEW.AccNewDB..tblCustomer as tCustINNER JOIN ServerNEW.AccNewDB..tblAddress as tAddON tCust.CID = tAdd.CIDWHERE tAdd.AddressType = 'Main'[/code]I'm considering moving the AccOldDB from ServerOLD --> SERVERNEW thinking it would be quicker.I realised that I don't understand what happens with queries across Linked servers. I presume the work is done by ServerNEW and just the results are passed to ServerOLD.Would it benefit from moving AccOldDB to the new Server? So both dbs are on same serverI suppose an even better improvement would be to create the views directly on ServerNEW..AccNewDB, but the 3rd party vendor frowns on this.Of course moving the DB Location would involve some changes to hard coded connection strings, however much of the connection strings out there use global File DSNs.I'd appreciate advice from people more experienced in Linked servers than I..Many thanks

Mind blowing parallelism issue

Posted: 08 Aug 2013 12:14 AM PDT

To be clear, I'm not posting this looking to have anyone tell me exactly how I should set MAX DOP or the threshold. I realize this is more voodoo than exact science. What I am seeking is any information or suggestions for a particular issue we had yesterday.We have an application (OLTP) that has its own database server (SQL 2008 SP2 (I know it needs and update but downtime is hard to come by)) and generally things run very well. However, calls started coming in with users saying they could open up "these" records but not "those" records and the application was timing out while trying to look up the problematic records. The app has decent logging so I was able to capture the statements it claimed were time outs and run them manually. These statements came back in a snap so I have no idea how a timeout could occur on them. I ran a trace of all app activity related to these look ups and all those statements ran fast as well. Of course, before I did all this I looked for the obvious such as locking/blocking and anything else under the sun. As far as I could tell the server had nothing going on that was detrimental to performance.This server is also used by a piggy back set of applications written in-house that add additional functionality to the 3rd party app mentioned previously. There are times when it executes queries that cause parallelism and over the last 8 months or so I have tweaked the MAX DOP setting trying to find a balance. When I set it low the users of the 3rd party app are happy but the add-on apps are not and if I switch it the other way the situation reverses.While we were having the issue with the look ups I checked to see how much parallelism was going on and it was nothing out of the ordinary. The server is a dual Xeon 4 core with hyper threading on so there are sixteen logical processors available and I had the MAX DOP set to eight. I had no reason to change this value based on anything I had seen but I decided to take a chance and I dropped it to four.What happened? All of the look up problems went away. I could understand this if all records had an issue but it was only some of them. These records are all the same type as far as table structure, data types, etc. and the look up method is the same for either the good or problematic records. So the shot in the dark change of MAX DOP resolved the issue but why? Of course we were happy to get past the issue but we do not like that we have absolutely no idea why this arbitrary change was so effective.

Replication: add a new table with out a new snapshot

Posted: 07 Aug 2013 09:40 PM PDT

Hello,i've got a database with 500 GB DATA and 200 GB INDEX. I had to add an article (table) to the replication. I want do avoid, that the replication creates a new snapshot. We need about 7 hours to create the new index in the database.Is it possible, to add article without a new snapshot. I read something, that only the difference ( in my example: 1 table) is stored in the snapshot.Publisher: SQL 2008 R2 SP2 / OS Windows Enterprise 2008 48 GB RAMSubscriber: SQL 2008 R2 SP2 / OS Windows Standards 2008 48 GB RAMDistribution-Server: SQL 2012 / OS Windows Enterprise 24 GB RAMcreate the snapshot needed about 30 Minutesdeliver the snapshot needed about 6,5 Hourscreate index neede about 7 HoursThanks for you help,Andreas

CDC records Updates as Insert and Delete

Posted: 06 Mar 2013 05:41 AM PST

I am experimenting with using CDC to perform auditing in one of our user databases. If I perform an INSERT or UPDATE statement in SQL Server Management Studio, the operations are recorded correctly as 2 (INSERT) and 3 (BEFORE UPDATE) and 4 (AFTER UPDATE) but when I use our VB.net application, which passes a table-valued parameter to a stored procedure which then performs an UPDATE statement, cdc records the operations as 1 (DELETE) and 2 (INSERT). Has anyone run into this before? How can I get the stored procedure execution to record the update correctly?Thanks.

How to update Flag based on Maximum Amount

Posted: 07 Aug 2013 10:00 PM PDT

Create Table #Temp(Lnno Varchar(15),Custcode varchar(10),Flag varchar(10),Amount Int,Amount_Flag varchar(1))Insert Into #TempValues ('1','A1','Cust',1000,''),('1','A2','CoAp',500,''),('1','A3','CoAp',100,''),('1','A4','CoAp',2000,''),('2','B1','Cust',1000,''),('2','B2','CoAp',1000,''),('2','B3','CoAp',1000,''),('2','B4','CoAp',1000,''),('3','C1','Cust',0,''),('3','C2','CoAp',1000,''),('3','C3','CoAp',1000,''),('3','C4','CoAp',5000,'')Select * from #Temp/*Hi,I have this data where it has Lnno,Custcode,Amount and Flag.My requirement is,I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.1)Now in case Lnno = 1,there is one Cust and three CoAp ,So Amount_Flag should be updated as Y to Custcode 'A4',since it has maximum amount.2)In Case Lnno = 2,Amount is same for all,so in this case Amount_Flag should be Updated to Flag = 'Cust',that is the priority should be given to Cust.3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is 1000,so Amount_Flag should be updated to any one of the record.Please Help me.Thanks in Advance!!*/

Find and Replace in a UPDATE Statement

Posted: 08 Aug 2013 12:36 AM PDT

hi SSC,am having a text in one column. assume that below is the text text : in a delete, insert, or update trigger, i want a single update statement, find multiple text strings and replace with another text.eg: 1) find delete and replace it with del2) find insert and replace it with ins3) find update and replace it with upd.any help please

Update

Posted: 07 Aug 2013 11:23 PM PDT

update #InwardTransDetails set Branch='BP66' where Branch='NG26' and logindate='2013-08-07'is not working why?

Attach MDF that wasn't cleanly shutdown

Posted: 06 Aug 2013 02:13 AM PDT

Hi experts, im trying to recreate a disaster recovery scenario and simulate a drive going down that hosts the database log (ldf) file. I want to see / document the steps I'd need to perform to recover the database from the mdf file (if at all possible). The steps i've performed so far are:Create a new database 'Test'mdf file in default SQL locationldf file on a different drive (happens to be a removable pen drive)I then remove the pen drive to simulate a drive going down. So now the Test database is inaccessable right.So what I need to know is: A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)B. If so, how?Thanks in advance

Joins and Counts

Posted: 07 Aug 2013 09:23 PM PDT

Hi,My SQL knowledge is basic to average and need some help in counting rows in a join where 2 dates match:SELECT MT.[Organisation ],MT.[Assignment Number ],MT.[First Name ],MT.[Last Name ],MT.[Position Title ],MT.[Site Name ],MT.[Date of Expense ],MT.[Reason ],MT.[Expense Item ],MT.[From ],MT.[FromPostcode ],MT.[To ],MT.[ToPostcode ],MT.[Step Number ],MT.[Step Mileage ],MT.[Total Journey Mileage ],MT.[Total Journey Value ],S.[Assignment Number] as [Assignment No.],S.[CRS Identifier],S.[Org Cost Centre],SC.[Name],C.[Contact Date][size="4"]--need to add in a new line something like below--,count (C.Contact_Date) when C.Contact_Date = MT.[Date of Expense ][/size]FROM [lchs_ref].[dbo].[Mileage_Table2] MTjoin lchs__esr_dwh.dbo.dwh_201_staff_list S on S.[Assignment Number] = MT.[Assignment Number ] join lchs_data_warehouse.dbo.dwh_091_s1_smartcard_list SC on SC.[Smartcard ID] = S.[CRS Identifier]join nhs_costing.dbo.cost_contacts C on SC.[Name] = C.[DoneBy1]Dont know if what i am trying is poosible but any help would really be appreciated.Thanks

Cannot open New SSIS Project in SQL Server 2008 R2

Posted: 02 Nov 2012 12:03 PM PDT

I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer use the SSIS to create New Projects. I cleaned out the registry with a Registry cleaner from Wise. I deleted the SQL Server and Visual Studio folders as well.When I create a New SSIS Project, I immediately get an error as follows:"Microsoft Visual Studio""An error prevented the view from loading.""Additional information:""Specified Module cannot be Found. (Exception from HRESULT: 0x8007007E)""(System.Windows.Forms)"How do I resolve this problem? What this means is that I cannot create any new SSIS Projects.Cecil
Posted by at No comments:

Thursday, August 8, 2013

[SQL Server] Help creating a View

[SQL Server] Help creating a View

Help creating a View

Posted: 08 Aug 2013 02:47 PM PDT

Hi all,This is my first post here so hopefully I'll include everything that's needed. Please also be aware that I am self taught so I may not do things as easy as possible. I'm using SQL Server 2005.I have some data that contains a string with comma separated values in it and I have used a cursor and a loop to load this data and separate it into another table. My problem is that I need this data available in a View so that it is always up to date with the latest values. I can't use a trigger on the table as the database is shared so I can create new tables, views, stored procs etc but I can't alter existing items.Here is some sample test data: create table TempTesting (ID int, TestData varchar(80)) insert into TempTesting values (1122, '11111,222228,33333,44444') insert into TempTesting values (12345, '12345,54321') insert into TempTesting values (876543, '25847,369587,98758,145289,425986,76532') insert into TempTesting values (255, '951458')And a table for the results: create table TempResults (ID int, TestResult int)And this is the code I use to separate the data: declare @string varchar(100) declare @result varchar(10) declare @stringlocation int declare @ID int declare cursor_name Cursor for select ID, TestData from TempTesting open cursor_name Fetch next from Cursor_name into @ID, @string WHILE (@@FETCH_STATUS <> -1) Begin select @stringlocation = PATINDEX('%,%',@string) while @stringlocation > 0 begin select @result = left(@string,@stringlocation-1), @string = right(@string,(len(@string)-@stringlocation)) insert into TempResults (ID, TestResult) select @ID, convert(int,@result) where not exists (select 1 from TempResults where ID = @ID and TestResult = @result) select @stringlocation = PATINDEX('%,%',@string) continue end insert into TempResults (ID, TestResult) select @ID, convert(int,@string) where not exists (select 1 from TempResults where ID = @ID and TestResult = @string) and @string is not null Fetch next from Cursor_name into @ID, @string End Close cursor_name Deallocate cursor_name --delete from tempresultsSo what I need is to be able to create a View which looks like the results table as the data will be changing constantly. Is this possible?

Password Format woes

Posted: 08 Aug 2013 04:42 AM PDT

During my very steep learning curve in trying to implement an ASP.Net membership system, I naively set the passwordFormat in my web.config file to [i]Hashed[/i]. This was before all my data was entered! I then read that Hashed passwords could not be retrieved. Break it to me gently... do I have to start over and re-enter all the data into my DB? Or is there a clever way of undoing my follies?Tx,Martin

Copy certain tables,stored procedures and views from one Database to another

Posted: 30 Apr 2013 04:05 AM PDT

Hello,I am new sorry, hope i can explain this right, I have an old database called database1, which now we have a new database called database2 (these are not the real names lol), database1 and database2 have the same table names, stored procedures etc, but database1 had foreign keys etc, what I want to know is there a way to copy over only certain database tables from one database to another, not copying the entire database but only the tables needed and restore them onto database2? is there a way for doing the same for certain stored procedures and/or views and not all only certain ones? I have SSIS but so far i can only see for data purposes...Thanks in advance

User does not have permission to perform this action.

Posted: 07 Aug 2013 08:06 PM PDT

Hi all,I wrote like this use MYDBgoexec sp_addrolemember 'db_owner', 'myusername'goit is showing thatUser does not have permission to perform this action.please give me solution.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog