Thursday, June 27, 2013

[how to] MS SQL 2005 data to MS SQL 2012 full text catalog issue

[how to] MS SQL 2005 data to MS SQL 2012 full text catalog issue

MS SQL 2005 data to MS SQL 2012 full text catalog issue

Posted: 27 Jun 2013 06:46 PM PDT

I have a MS SQL 2005 database that I backup and restored it into a new server running MS SQL 2012. So far so good. This morning I had a really bad power outage and the new server when down. When the new server came back online the DB has the "Revery Pending" status.

I put the DB in single user, did a DBCC CHECKDB and then I put the DB back into multi user and the status when away. When testing the backup jobs I notice there was an issue with the full text catalog.

I try to drop the catalog and to recreate it, but it will not do it complaining that it is not empty that it has a full text index. I tried removing the index in the table that is using the full text catalog, but then I got a message saying the text catalog is empty, read only or offline.

What am I missing?

Note: DB is only, but the backup jobs keep failing. When I do a DBCC CHECKDB I get the following:

Msg 8996, Level 16, State 1, Line 1  IAM page (3:9) for object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) controls pages in filegroup 0, that should be in filegroup 2.  Msg 2575, Level 16, State 1, Line 1  The Index Allocation Map (IAM) page (3:9) is pointed to by the next pointer of IAM page (0:0) in object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data), but it was not detected in the scan.  Msg 8968, Level 16, State 11, Line 1  Table error: IAM page (3:9) (object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data)) is out of the range of this database.  Msg 7965, Level 16, State 2, Line 1  Table error: Could not check object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) due to invalid allocation (IAM) page(s).  CHECKDB found 2 allocation errors and 2 consistency errors in table 'sys.fulltext_avdl_1062555119' (object ID 983374968).  CHECKDB found 2 allocation errors and 2 consistency errors in database 'MY-DATABASE'.

Force SQL 2005 to join tables in certain order

Posted: 27 Jun 2013 05:46 PM PDT

I have a SQL table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID column). Every child has exactly 1 parent based on these joins, and every record is either a parent or a child record (i.e. there are no grandparent records)

SELECT  *  FROM    TheTable AS ChildRecords  JOIN    TheTable AS ParentRecords  ON      ChildRecords.Column1 = ParentRecords.Column1  AND     ChildRecords.Column2 = ParentRecords.Column2  AND     ChildRecords.Column3 = ParentRecords.Column3  AND     ChildRecords.Column4 = ParentRecords.Column4  AND     ChildRecords.Column5 = ParentRecords.Column5  AND     ChildRecords.Column10 = ParentRecords.Column6 --Note column 10 joins to column 6, but this column does not find a unique parent by itself - there could be multiple "parents" with column10 = column6

This normally works fine, however if we have it as part of a bigger query, SQL often tries to resolve this join first before it resolves the other. This is particularly true when it is in a CTE or joining with a CTE. It's often the first join that happens in the Query plan. This often results in 10s of thousands of joins before it is then filtered down to a 100 or so records that I'm interested in. When this happens the query takes minutes to run.

I've noticed that I can affect the query plan by making it a left join. This makes sense because if it's a left join then SQL doesn't know that every child has 1 parent so it always has to find the child records first.

SELECT  *  FROM    TheTable AS ChildRecords  LEFT JOIN TheTable AS ParentRecords  ON      ChildRecords.Column1 = ParentRecords.Column1  AND     ChildRecords.Column2 = ParentRecords.Column2  AND     ChildRecords.Column3 = ParentRecords.Column3  AND     ChildRecords.Column4 = ParentRecords.Column4  AND     ChildRecords.Column5 = ParentRecords.Column5  AND     ChildRecords.Column10 = ParentRecords.Column6

When it runs the query this way it reduces the time from minutes to < 2 seconds.

Because each child has a parent, the left join gives the same result as the inner join, however it feels wrong - it should be an inner join.

I have checked that the indexes are setup properly on this table and I've tried adding and editing the ones that we have but it doesn't change the query plan. The time seems to be because it is querying to get every child/parent combo before it limits it down to just the ones I want.

Can I force SQL to run the joins in the order I specified, rather than letting it try to re-order the query?

Error using an Execute SQL script to load a date variable in SSIS

Posted: 27 Jun 2013 08:09 PM PDT

I'm trying to populate a variable using an Execute SQL task in SSIS. The table structure is as follows: 

CREATE TABLE BuildControl   (Id INT NOT NULL Identity, BuildDate Date,        Country char(2), BuildStatus varchar(20),        BuildStatusDate DateTime)

The SQL Statement is

SELECT TOP 1 ? = Id, ? = BuildDate  FROM BuildControl  WHERE BuildStatus IS NULL  ORDER BY Id

and it works fine if I remove the ? = BuildDate. The parameter mapping tab looks like this:

enter image description here

The error I'm recieving is this:

SSIS package "WFG Statement Build.dtsx" starting.  Error: 0xC002F210 at Get Build Data, Execute SQL Task: Executing the query "SELECT TOP 1 ? = Id, ? = BuildDate  FROM BuildContr..." failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  Task failed: Get Build Data  Warning: 0x80019002 at WFG Statement Build: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  SSIS package "WFG Statement Build.dtsx" finished: Failure.

I believe the problem is with the date output variable since when I remove it I don't have a problem. Both the server and the version of SSIS I'm using are SQL Server 2008 R2. The Connection Manager is an OLE DB connection using the Native OLE DB\SQL Server Native Client 10.0 provider. I've tried changing the table structure to use a DateTime with no success, and tried DBDate and DBDateTime as Data Types in the Parameter Mapping page also with no success.

I'm completely baffled. Thanks for any help!

Is there any way to tell the number of matches found in a database value matched with LIKE?

Posted: 27 Jun 2013 02:16 PM PDT

Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)?

EXAMPLE table, "exampleTable":

**ObjectID**        **value**       1           I Love Lemonade       2             I Love Love

Using a query like this one: SELECT * FROM exampleTable WHERE value LIKE '%love%', I can easily find any entries where the value contains the word (or has a word where part of it contains the word) "love." I would like to be able to know just "how many" matches were found (for sake of search priority) in the matched fields, so that they can be displayed as more relevant. I realize that I'm probably asking a lot out of a simple query, however, I also know how powerful SQL can be, too, so thought I would ask if this was possible and if not, if there is any other approach I might take (that won't force me to rewrite the database).

I thought of using sub-queries somehow, but there is no way (that I have been able to find) to combine "LIKE" and "IN". Also, I am not even sure using a sub-query would get the job done since I'm working with a relational database where one main table relates to 8 others and the "8 other" tables have the values I want to search on, but the main table has the location I need (although, I suppose I don't have a problem adding a "location" column to these "8 other" tables, as it would be helpful in other areas, too).

It's entirely possible that I didn't design the "relational" part of the relational database to perfection, but this is the first time I have designed (although, certainly not worked with) a relational database by myself, and, at least, it works well with the back-end of my website (yes this site is a private partial CMS).

Any help is appreciated, and thank you for your time!

constrain/expand same set of privileges to exclude/include databases in mariadb

Posted: 27 Jun 2013 12:42 PM PDT

I have a user that has a set of privileges for all databases in an instance of MariaDB. I'm wondering how the same set of privileges can be made to cover a certain set of databases. Below are my questions with regard to this.

MariaDB [(none)]> show grants;  +----------------------------------------------------------------------------+  | Grants for tester1@localhost                                               |  +----------------------------------------------------------------------------+  | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO |  | 'tester1'@'localhost' IDENTIFIED BY PASSWORD '*AF123'                      |  +----------------------------------------------------------------------------+

What is the simplest way to constrain those privileges to a single, or a set of databases?

How can a certain set of privileges be expanded to include additional databases?

Can this be done using phpMyAdmin?

Make MySQL InnoDB always use an index for a given table

Posted: 27 Jun 2013 12:19 PM PDT

For many reasons, there is a given table on my mysql innodb database with mant foreign keys, columns and indexes, and I can't remove any of those index. But MySQL always fails to choose the optimal index, in 99% of the times the query runs faster if I force mysql to use a specific single index.

I don't want to mess up my app code adding "use index(xpto)" to force mysql to use the optimal index, is it possible to configure MySQL to always use a specific index for a specific table?

SQL Server 2012 Failover Cluster and attached SAN

Posted: 27 Jun 2013 02:00 PM PDT

I have three servers in a Windows Server failover cluster. SQL Server 2012 Failover Cluster is installed on all nodes. I have an issue with shared disks attached from SAN.

overview with the same configurations for all

  • DNS are SQL1, SQL2, SQL3

  • RAID1 + 5 SAS, 50Gb from SAN for Windows failover, 1TB for SQL failover

  • Windows Server failover cluster was initialized from SQL1

  • all installations before Windows Server failover cluster was created were initialized from SQL1

  • I shared SQL2 -> SQL1

my questions are:

  1. All shared disks (SAN 50GB + 1TB) are added to Windows Server failover cluster, but only from SQL1 is possible to leave available storage for SQL Server 2012 Failover Cluster. I'm not able to set available disk on SQL2 / SQL3 node, they can be only members of clustered storage or in offline mode. Is this possible? If yes, then how?

  2. (Windows OS specific question) built_in SAS HDDs in RAID1 + 5 isn't possible to add to the pool in Windows Server failover cluster (members are empty) but is possible to create a pool for each of noded in Server view, e.g. then to create iSCSI. Why those two options seems like as not doing the same things; specific server can see available HDD for pool, from Windows failover is/are member empty.

  3. Is it possible to migrate with installated SQL Server 2012 Failover Cluster and their files from one storage to another, for example when I attach a new SAN to Windows Server failover cluster?

  4. Then is possible to install Windows Server failover cluster only from SQL1? On SQL2 / SQL3 node installations ended with no available storage.

Deploying to SSIS catalog and get permissions error

Posted: 27 Jun 2013 11:29 AM PDT

When I attempt to deploy to SSIS 2012 Catalog on new server I get an error. I have researched it on the web for several hours and all of the information available online does not fix my issue.
enter image description here

Subquery That Shows Records Only Where the JOIN Doesn't Work [migrated]

Posted: 27 Jun 2013 11:24 AM PDT

I created a relatively simple query in MySQL to give me a JOIN on three tables based on where first names and last names matched. From there, I wanted to write another query that would then only show me the records that didn't get matched from the JOIN -- but I couldn't quite figure out how to do it. I'm assuming that it has to do with using a subquery involving something like NOT IN and my original query, but I couldn't get it to give me the results I wanted.

This is the work-around I tried to come up with that partially functioned properly:

SELECT *,     if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match") AS "t2 Match",    if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match") AS "t3 Match"  FROM t1  LEFT JOIN t2 ON t2.first=t1.first AND t2.last=t1.last  LEFT JOIN t3 ON t3.first=t1.first AND t3.last=t1.last  WHERE if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match")="No Match"     OR if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match")="No Match";

I feel like this is something that's fairly simple and straight-forward, but I'm not getting the correct results. Can anybody help?

Thanks!

What is an incremental checkpoint (in the context of indirect checkpoints)?

Posted: 27 Jun 2013 09:33 AM PDT

I was using extended events to trace checkpoint activity of a database with indirect checkpoints enabled. The event stream looks like this:

enter image description here

And the details of the selected event are:

enter image description here

The description of the selected event is:

Chose to enqueue an incremental checkpoint as we are near our recovery target

It was determined using the following query:

SELECT name, description FROM sys.dm_xe_objects WHERE name LIKE '%checkpoint%' ORDER BY name

I'm trying to better understand how indirect checkpoints work under the covers. What is an incremental checkpoint? What IO patterns will it cause? How does it relate to a "normal" checkpoint?

I have studied the docs about checkpoints but I could not find any information about this.

How do I prevent SQLite database locks?

Posted: 27 Jun 2013 02:49 PM PDT

From SQLite FAQ I've known that:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

So, as far as I understand I can: 1) Read db from multiple threads (SELECT) 2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about Write-Ahead Logging that provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found it, when specified:

Here are other reasons for getting an SQLITE_LOCKED error:

  • Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
  • Trying to write to a table while a SELECT is active on that same table.
  • Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
  • fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to mv the DB away, and copy it back so that it has a new Inode value

So, I would like to clarify for myself, it is necessary to avoid the lock? Can I read and write at the same time from two different threads? Thanks.

Vendor Wants to Run MSDB job every 5 minutes for Business Application

Posted: 27 Jun 2013 11:54 AM PDT

We have a 3rd party vendor trying to integrate 2 different applications where both DBs reside on our SQL Server instance with 150+ other DBs, and they want to create a MSDB job to "synch" the 2 different applications every 5 minutes (at first they wanted to run it every minute).

My initial hunch is that they should instead do this somehow in the Application tier with a Windows scheduled job, or perhaps even a dreaded trigger (which we typically resort to in situations like this).

I prefer to keep MSDB jobs reserved for DBA tasks as much as possible to reduce the clutter there, and have also run into slow querying of MSDB when viewing job histories with super active jobs like this (which also drown and drop out important job histories of more important things like backup histories). But then again, maybe my preferences are wrong and I need to make some space for the Application tier in MSDB and roll up my sleeves and fix problems of job histories taking forever to load when I need to retain a lot more history entries to capture the important stuff like backups (or purge the hyper active job entries).

Another issue I have is that now I need to give this vendor "sysadmin" rights instead of only "dbo" rights on only their DBs when they perform their upgrades via their GUI and hope they don't blow up the instance where my mission critical DBs are (one of the downsides of consolidation).

I guess I can put them on another "isolated" instance where we put all the vendors that don't play nice, but then we need reconfigure the Applications to point to the new SQL instance (sigh unfortunately not trivial in this case).

The vendor already pushed back on my concerns talking about how bad triggers are. So, I "googled" on this a bit and came up empty. Has anyone seen any link out there "authoritative looking" that this is a bad idea and I can refer them to it? Or should I embrace their approach?

I don't believe I've ever posted in a sql forum before asking for help, so hopefully my inquiry is properly framed.

EDIT: We're running SQL Server 2008 Enterprise R2 x64 SP1 (Thanks for pointing out that I forgot to mention version!). Hmmm, hopefully they don't need to change their MSDB upgrade scripts when we go to a newer version.

Thanks for your time! Rich

Multiple JOIN of 6 tables via 5 database instances

Posted: 27 Jun 2013 11:29 AM PDT

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the roles of a user which he has of all instances.

I already used UNION ALL, but the output was unstructured and you couldn't tell which role on which instance. So I tried the following for only 3 tables:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"  FROM SCHEMA.USR_ALL_USERS U  LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W     ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')  LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V     ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')  WHERE U.USERNAME = 'USER'  ORDER BY U.USERNAME ASC;

It actually worked, but the output wasn't satisfying:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2  --------------------------- --------------------------  ROLE_1  ROLE_2  ROLE_3  ROLE_4                              ROLE_1                              ROLE_2                              ROLE_4                              ROLE_5                              ROLE_6

Is there any way to make an output like this:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2  --------------------------- --------------------------  ROLE_1                      ROLE_1  ROLE_2                      ROLE_2  ROLE_3  ROLE_4                      ROLE_4                              ROLE_5                              ROLE_6

I tried ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2') but the output was even worse.

You guys have any suggestions or helpful thoughts?

Mapping a shared physical folder to filestream

Posted: 27 Jun 2013 08:54 AM PDT

We are working on a project where we have large amounts of images, texts etc which we need to work on in SQL Server. We were thinking of using Filestream and filetable functionality. However, even after trawling through a lot of data on the net, we aren't able to find answers to the below queries:

  1. We are trying to create a shared folder on our system and then map it to the filestream. So we are creating a shared folder (lets say D:\Ftable ) which has access level set to full access for everyone. Now when we go to the SQL Server configuration manager and try to set this folder as shared folder for filstream (the properties tab where we enable the filestream access) it throws us error : The windows share folder name is not a valid windows share. We know that filestream folder has to be a local folder, however even when we just try to put a different local folder path (like D:\test etc) it gives us an error.

  2. We would also like to know if it is possible to create a filestream shared directory on a mapped drive? We have created a shared folder on one system and mapped that folder on a different system (so that it appears as local). However once again, when we try to set a specific folder on the mapped drive as filestream shared folder, it throws us an error.

Would really appreciate any pointers/help on the topic. Our main access is to create a filestream share on a physical location of our choice which is shared so that we can transfer data to it either remotely/ or via a tool.

Unused Indexes - Consider Primary Key Constraint Supporting Indexes?

Posted: 27 Jun 2013 11:38 AM PDT

I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key Index isn't being used, shouldn't that be of some concern? Unless, perhaps, it is a small table that isn't accessed very often?

Indexing strategy for dynamic predicate

Posted: 27 Jun 2013 08:32 PM PDT

Assume SQL Server 2012 Standard edition.

My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).

The clustered index (also primary key) is an identity column.

The application using this table has a screen where the user can search on most of the columns. One search field on the screen, which is required, has the option to search starts with or contains, resulting in either

WHERE ABC LIKE 'something%' -- starts with

or

WHERE ABC LIKE '%something%' -- contains

The actual queries are parameterized, unlike my examples here.

The other search fields do a starts with search just like the first example above, but they are not required. So, any combination of these fields can be searched on resulting in a dynamic where clause.

Given this information, what indexes should be created for optimal performance?

Bearing in mind that I'm new to query performance tuning, my naive strategy for this scenario alone is to create a non-clustered index for each column and using full text search for the column that has the contains search option. I'd love to hear why or why not that's a bad idea and what a better approach would be.

Update

It's known to me that full text searching is how to optimize the case of a "contains" search.

I'm much more interested in the other aspect of the problem: how to optimize for the other search fields which may or may not be present in any given query predicate. The details surrounding the field that can benefit from a full text index are included in my question only to help paint a more complete picture of my particular situation.

SQL Server: subscriber as publisher and hierarchical replication

Posted: 27 Jun 2013 02:45 PM PDT

In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time.

But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication?

Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server?

UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication?

how to count primary key of one table inanother table? [on hold]

Posted: 27 Jun 2013 08:45 AM PDT

how to count primary key of one table which is used in another table in 5 to 6 column?

If table 1 has primary key srno which is used in table2 in column d1,d2,d3,d4.

I want to count how many times srno=1,2,3,4... etc used in table2.

any one know how to do that???

Cannot see Green Button with white arrow in object explorer; DTC config problems?

Posted: 27 Jun 2013 11:45 AM PDT

I have SQL Servers across locations setup for replication. One of the SQL Server instances that I installed, running locally I can see the green button with the white arrow in the object explorer when I connect to it. However, when I connect to it from any other location, I cannot see that or neither can I start or Stop the SQL Serer Agent; even though locally I am able to do that.

Additionally, the MDTC doesn't has only 2 items sent both with were rejected, where as other servers have 100's committed and sent. Is there something wrong with the DTC settings?

Please help.

Database hangs randomly

Posted: 27 Jun 2013 12:45 PM PDT

My database works fine but suddenly it stops responding to queries and I get only timeout errors on my website then after about 10-30 minutues it starts responding again on it's own but queries take about 20-30 times more time than normal to execute and then slowly speeds up until it returns to normal again.

I tried using entity framework profiler and I wasn't able to find which query causes the problem, sometimes a query that normally takes 100ms causes the Sql to hang, and sometimes all the heaviest queries run smoothly, it's quite random.

it happened after I added some new relationships to my database. my development machine uses a somewhat outdated backup of my production server database and the problem exists on both of them, I tried adding some missing indexes and nothing changed, I'm totally lost right now.

MySQL failover - Master to Master Replication

Posted: 27 Jun 2013 03:54 PM PDT

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered through different geographical locations, each containing a MySQL 5.5 server with several DBs, that for the time being are merely slave-replicating from the production server - the objective up until now was just checking the latency and general resilience of MySQL replication.

The plan however is to add a Master-Master replication environment between two servers in two separate locations, and these two instances would handle all the DB writes. The idea wouldn't necessarily imply concurrency; rather the intention is having a single one of the instances handling the writes, and upon a downtime situation using a DNS Failover service to direct the requests to the secondary server. After the primary comes back online, the b-log generated in the meantime in the secondary would be replicated back, and the DNS Failover restored the requests back to the first one.

I am not an experienced administrator, so I'm asking for your own thoughts and experiences. How wrong is this train of thought? What can obviously go wrong? Are there any much better alternatives? Bash away!

Thanks!

How to migrate SQL Server to MySQL

Posted: 27 Jun 2013 05:45 PM PDT

I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:

  • MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen.

  • sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL.

  • SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows.

Anyone have suggestions?

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Posted: 27 Jun 2013 10:45 AM PDT

Here is my scenario

Master1 hosting DB1  Master2 hosting DB2  ...  MasterN hosting DBN    replicate to:    Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

Single slave - multiple master MySQL replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

SQL Server account delegation setup

Posted: 27 Jun 2013 09:45 AM PDT

I am try to run bulk insert command on SQL Server 2008 but I am having issues with the security. after researching via internet, the problem has something to do with account delegation and impersonation.

However all solutions seems to point to an Active Directory setup which my setup is not.

My setup is SQL Server 2008 Express on Windows Server 2008 Standard configured as a workgroup.

Despite a SQL Server user account, assigned all the user mappings and server roles and ensuring security set to SQL, I am getting this error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\server_name\file_name.txt" could not be opened. Operating system error code 5 (Access is denied.).

So, how does one setup SQL Server account delegation and impersonation on a workgroup environment?

Thanks in advance

Trouble setting up Nagios to monitor Oracle services

Posted: 27 Jun 2013 01:45 PM PDT

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30  CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

Posted: 27 Jun 2013 04:45 PM PDT

I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;  +----------+  | COUNT(1) |  +----------+  | 25453476 |  +----------+  1 row in set (2 min 36.20 sec)      mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  | id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  |  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

Database setup/design for multiple services

Posted: 27 Jun 2013 03:45 PM PDT

I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database?

Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated!

Also, here is a sample of the type of info that will be stored:

enter image description here

SSIS Row Count: Getting a null variable error where there is clearly a selected variable

Posted: 27 Jun 2013 08:41 AM PDT

Validation error. Build Files Count VIE [245]: The variable "(null)" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

From what I can tell, this error is thrown when a variable is not assigned to the VariableName property; however, I definitely have a variable assigned, as seen in the image below:

"Count VIE" Row Count Properties

I've deleted the Row Count component and remade it, but the error continues to show up. Here is a snapshot of the Data Flow in question:

"Build Files" Data Flow

I'm not sure if its inclusion in a Conditional Split may be causing this error, but none of the other Row Count components seem to be throwing this error.

What is a common way to save 'debit' and 'credit' information?

Posted: 27 Jun 2013 01:11 PM PDT

I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database):

METHOD 1

  • Amount (decimal)
  • Type (enum, debit/credit)

METHOD 2

  • Debit (decimal)
  • Credit

In the first setup, I save the type of transaction, but in the second way I rather save the amounts in the debit or credit column. Pros of this method are that I can more easily sum both debit and credit totals than in method 1. But I am wondering if there is a common way to do this?

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog