Friday, June 28, 2013

[how to] "SQL1042C An unexpected system error" when creating a database

[how to] "SQL1042C An unexpected system error" when creating a database

"SQL1042C An unexpected system error" when creating a database

Posted: 28 Jun 2013 04:16 PM PDT

I installed DB2 Express-C 10.1 on OS X 10.8.4. I installed it in user mode, and it seems to have created an instance with my username (avernet). I checked the instance is indeed there by running db2ilist. The database manager is running, as when I run db2start, I get the message SQL1026N The database manager is already active.

Now, I am trying to create a database by running db2 from the command line, and typing the command create database orbeon, but it returns:

QL1042C  An unexpected system error occurred.  SQLSTATE=58004

What am I missing here? How can I create a simple database from the command line (I prefer not to bother with Data Studio as it isn't available for OS X)?

what is the best practice when making changes to the the application and database?

Posted: 28 Jun 2013 02:38 PM PDT

I need to make changes to an application which utilizes a database that is replicated across locations. I was wondering what the best practice is when working with databases and making changes to the database.

I will be backing up the database to dev environment and making changes to both the application and the database. Then when testing the application and the database I will likely make changes to the records. In that case, would it make sense run the t-sqls that I applied in the dev to the production database?

Creating view recursive query in Oracle DB results in ORA-00600 (ORA-600)

Posted: 28 Jun 2013 02:29 PM PDT

Hello Ladies and Gents,

I've been trying to deploy a recursive query as a view in Oracle XE and Standard Edition, neither to much success.

The query is located in this question here: http://stackoverflow.com/questions/17358109/how-to-retrieve-all-recursive-children-of-parent-row-in-oracle-sql

with recursion_view(base, parent_id, child_id, qty) as (     -- first step, get rows to start with     select        parent_id base,        parent_id,        child_id,        qty    from       md_boms      union all      -- subsequent steps    select      -- retain base value from previous level      previous_level.base,      -- get information from current level      current_level.parent_id,      current_level.child_id,      -- accumulate sum       (previous_level.qty + current_level.qty) as qty     from      recursion_view previous_level,      md_boms        current_level    where      current_level.parent_id = previous_level.child_id  )  select     base, parent_id, child_id, qty  from     recursion_view  order by     base, parent_id, child_id

The query itself works and returns results. However, when I try to create a view with that query, I receive errors.

I've posted two screenshots: http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/ViewError.jpg and http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/InternalError.jpg The first one with the ! and * is when I am using JDeveloper 11g, using the Database Navigator, right-click on View and select "New View". I receive the error when I click OK. I can indeed create the view through a sql script, but when I attempt to view the data, I receive the ORA_00600 error.

I've tried this on both my local machine for XE (version 11.2.0.2.0) and on 11g Standard Edition (11.2.0.2.v6) via Amazon Web Services. The result is the same.

Does anyone know how to get around this?

Transnational replication altering tables and adding stored procedures

Posted: 28 Jun 2013 02:42 PM PDT

I have a database that is replicated in several location. It is setup as transnational replication. However, now i need to add more columns to the table. Add more stored procedures and etc. Hence, i make the change in the publisher, will it replicate across the subscribers or will it negatively impact the replication.

Suggestions will be very helpful.

Implementing a Record Keeper/Rollback System for a MySQL Database

Posted: 28 Jun 2013 11:46 AM PDT

What's the best way to create a record keeping table for all actions in my database?

I have a number of tables inside one database and I want to create another that records all movements in every other table - adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button.

Is there a tried and tested method for this or a native feature of (InnoDB) MySQL useful for this?

If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.

Choosing a database type for recording infrequent changes in a large number of variables

Posted: 28 Jun 2013 11:38 AM PDT

I am writing an application that monitors and archives the values of hundreds of thousands of variables. Since they change infrequently, only updates are recorded after the initial value.

Three primary types of queries are expected:

  1. Return the values of a set of variables at a given time:

    | var_name_1 | time_stamp | value |  | var_name_2 | time_stamp | value |    .    .

    For each var_name, the listed time_stamp is the last time the value changed before or at the given time.

  2. Compare the values of a set of variables between two given times:

    | var_name_1 | time_stamp_1 | value | time_stamp_2 | value |  | var_name_2 | time_stamp_1 | value | time_stamp_2 | value |    .    .

    For each var_name, time_stamp_1 is the last time the value changed before or at the first given time and time_stamp_2 is the last time the value changed before or at the second given time.

  3. Return all the values of a set of variables between two given times:

    | var_name_1 | time_stamp_1 | value |               | time_stamp_2 | value |                 .                 .               | time_stamp_m | value |    | var_name_2 | time_stamp_1 | value |               | time_stamp_2 | value |                 .                 .               | time_stamp_n | value |    .    .

    For each var_name, the first time_stamp is the last time the value changed before or at the first given time and the last time_stamp is the last time the value changed before or at the second given time.

The set of var_names returned is determined by a text matching search.

I would also like to indicate that a var_name was not being recorded for a requested time if the application had not been running then.

There is more information stored for each var_name for a given time_stamp than just the value, and it should be returned as well, but this should give the basic idea.

I was wondering what kind of database type might be best suited for this kind of application? Does a custom database already exist?

Linking third table with other two table to have a dynamic populated data inside using postgresql

Posted: 28 Jun 2013 11:34 AM PDT

I am in a phase where I need to have a data populated dynamically in a table which is associated with two other tables using foreign key constraint. Below is the schema/table that I have created in postgresql.

Table#1: 

 application   ----------------------------      id | name | size | rate

Table# 1 "application" have 'id' as "serial" which is also a primary key and 'name' as Varchar, 'Size' and 'rate' as Integer

Table#2:

Create  -------------------------  createId | name | score

Similarly. createId is again a serial and a primary key of this table and 'name' as varchar and 'score' as integer.

Now, I am creating a third table which is suppose to be a mapping table of the above two with a following fields-

Table# 3

app_create  ---------------------  app_create_id | id | createId |

In the above table, I have made app_create_id again a sequence and a primary key and other two fields as a foreign key with references to above tables application(Id) and create(createId).

Next, when I am inserting values inside table # 1 and # 2, my understanding says that it should automatically populate it into third table#3 with respect to the data I inserted in other two tables. But, instead I am getting a blank table meaning having no values in table # 3 (app_create) the mapping table. (after insertion in table # 1 & 2)

I am looking to have the output something like this shown below, like for example- application table and create table have following data populated-

application  --------------------------  id | name | size | rate      1  |    xyz  |   2096 | 12                                           2  |   mno   |  1280  | 34        Create  -------------------------  createId | name | score                                                           1        | a.b.c | 50         2       |  m.cpm  |20

Now, after I inserted data into these two above table, I am not sure what will happen in my third below table. I thought it will also get populated the same way as soon as I am populating the above two table as there is a primary and foreign key relationship.

Table# 3

app_create  ----------------------------------  app_create_id | id | createId |

I believe it has something to do with many-to-many relationship, but I am not sure how should I make the one or any right way how to proceed the same and have the output likewise. Please help me understand and correct me if I am doing anything wrong or guide me how should I proceed in order to have the data inside a mapping table# 3 dynamically?

Any suggestion/help would really help.

Thank you!

What's a good way to model user authorization to hierarchical data?

Posted: 28 Jun 2013 02:14 PM PDT

I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy.

Our data hierarchy looks like this:

System  - Farm    - Group      - Animal

The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System. Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it.

Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires).

What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see. Implemented here, it would look like this:

Table:  Authorizations          Table:  FullAuthorizations  Columns:    Id (PK)             Columns:    Id (PK)              UserId                          UserId              ObjectId                        SystemId              ObjectType                      FarmId                                              GroupId                                              AnimalId

The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc). The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data. A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations. I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records.

The queries would look something like:

SELECT *  FROM dbo.Animals a  WHERE EXISTS (      SELECT 1      FROM dbo.FullAuthorizations fa      WHERE fa.UserId = 1 AND fa.AnimalId = a.Id  )

In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects. I'd appreciate feedback on some other possible solutions. I've been looking at things like Nested Sets, but not sure something like that fits this particular problem.

In order to reproduce a display bug

Posted: 28 Jun 2013 10:57 AM PDT

Unless I asked you to, please do not act on this question, even if it appears in the suggested aardvark edit review queue or the close votes review queue.

Thanks, Aaron

Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop

Posted: 28 Jun 2013 10:50 AM PDT

I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform.

Is there a version of Oracle XE 11g R2 available for Windows 7? And if so, could you please point me to it?

Thanks...

Mysql is larger than wamp

Posted: 28 Jun 2013 03:14 PM PDT

Wamp which includes mysql,php,apache is only 24.7 MB but the only Mysql is 207 MB here.Why?

How to edit publication's generation_leveling_threshold configuration value

Posted: 28 Jun 2013 11:57 AM PDT

I dont know where I put this code:

update sysmergepublications set [generation_leveling_threshold] = 0

I only need do it. But I need edited any funcion of agent.

Terminology for table types

Posted: 28 Jun 2013 11:58 AM PDT

I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have.

I have included my current understanding of the types that I am aware of further down in this question.

Q: Please provide a list of the different table types, with brief definitions and examples to illustrate them.

Sidenote: The context to this is that I am currently attempting to reduce a database to a bare-bones version (for testing). As part of this I am hoping to identify tables that contain values to be looked up and will not change (eg job titles and department titles) vs the "data" tables which may be emptied with no ill-effect on the remainder of the database (cf, referential integrity in the case of adding a staff member where no departments or job titles are defined).

Lookup tables

I believe this to be where a table refers to a second table, where many rows/records in the first table share identical fields/column data. In the simpler cases, this table may be replaced by an ENUM type on the original table.

Example, where many users can have the same job title, the "Job Title" table is identified as a lookup table and the "Users" table is identified as the data table (see below):

Users >- Job Title

Transactional / Junction tables

I believe this to be where a many-to-many relationship occurs between two tables, and this transaction/junction table resolves this relationship.

Example, where many developers may be working on many projects, the "developer_project" table is identified as the transaction/junction table, and the "Developer"/"Project" tables are indentified as data tables (see below).

Developer -< developer_project >- Project

Data/Entity table

I am pretty much making this one up, but where a table is able to exist on its own, irrespective of other tables I consider this to be primarily a data storage table as opposed to a structural table. It may use lookup tables as part of the normalisation/optimisation process, but this is incidental.

I have seen the term "weak" entity floating around and it may/may not apply here. I need to do further reading to be sure.

Example, "staff" and "building" may exist independently of any other tables whereas transactional or relational tables/entities may not:

Staff  Building

No predicate warning [on hold]

Posted: 28 Jun 2013 03:47 PM PDT

I am getting a No predicate warning on this query. Can someone help?

SELECT DISTINCT TOP 100 T0.nsid,                           T0.id,                           T0.version,                           T0.name,                           T1.currency,                           T0.disbursmentamount,                           T0.effectivedate,                           T0.enddate,                           T0.contractname,                           T0.scheduledbillingdate,                           T0.dueamount,                           T0.paidamount,                           T0.cashpaidamount,                           T0.paidamount,                           T0.writeoffamount,                           T0.canceled,                           T0.creationdate,                           T0.creationtime,                           T0.mustbepaidat,                           T0.isrebill,                           T1.name,                           T0.status,                           T2.sendtoprint,                           T2.isprinted   FROM   (awfbillingstatementitem T0           INNER JOIN awfbusinessaccount T1                   ON (( ( T0.attachedtoclientaccount_nsid = T1.nsid )                         AND ( T0.attachedtoclientaccount_id = T1.id ) )))          LEFT OUTER JOIN awfbillingstatementitem T2                       ON ( ( ( T2.nsid = T0.nsid )                              AND ( T2.id = T0.id ) )                            AND ( T2.h_iskilled = 0 )                            AND ( T2.h_clid = 218169570 ) ),          awfcontract T3          LEFT OUTER JOIN awfdescversionedbydate T5                       ON ( ( ( T3.versionedpolicyowner_nsid = T5.nsid )                              AND ( T3.versionedpolicyowner_id = T5.id )                              AND ( -T3.versionedpolicyowner_version =                            T5.version ) )                            AND ( T5.h_iscurvers = 1 )                            AND ( T5.h_iskilled = 0 )                            AND ( T5.h_clid = 234821058 ) )          LEFT OUTER JOIN (awfpolicyownerversion T6                           LEFT OUTER JOIN awfdescversionedbydate_versions T8                                        ON ( T8.nsid = T6.nsid                                             AND T8.id = T6.id                                             AND T8.version = -T6.version ))                       ON ( T5.nsid = T8.lonsid                            AND T5.id = T8.loid                            AND T5.versions_version = T8.loversion                            AND ( T6.h_iscurvers = 1 )                            AND ( T6.h_iskilled = 0 ) ),          awfperson T4   WHERE  ( ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND (( T0.canceled = 0 ))            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND ( 0 = 0 )            AND (( ( T0.contract_nsid = T3.nsid )                   AND ( T0.contract_id = T3.id ) ))            AND ( 0 = 0 )            AND ( 1 = 1 )            AND ( (( ( T0.client_nsid = T4.nsid )                     AND ( T0.client_id = T4.id ) ))                   OR (( ( T1.thirdparty_nsid = T4.nsid )                         AND ( T1.thirdparty_id = T4.id ) )) )            AND ( 0 = 0 )            AND ( (( ( T3.subscriber_nsid = T4.nsid )                     AND ( T3.subscriber_id = T4.id ) ))                   OR (( ( T3.policyowner_nsid = T4.nsid )                         AND ( T3.policyowner_id = T4.id ) ))                   OR (( ( T6.policyowner_nsid = T4.nsid )                         AND ( T6.policyowner_id = T4.id ) )) )            AND ( 0 = 0 ) )          AND ( T3.h_iscurvers = 1 )          AND ( T4.h_iscurvers = 1 )          AND ( T0.h_iskilled = 0 )          AND ( T3.h_iskilled = 0 )          AND ( T4.h_iskilled = 0 )          AND ( ( T4.h_clid = 234817316 )                 OR ( T4.h_clid = 234820248 )                 OR ( T4.h_clid = 234816906 )                 OR ( T4.h_clid = 234817143 )                 OR ( T4.h_clid = 234821693 )                 OR ( T4.h_clid = 218169345 ) )   ORDER  BY T0.creationdate DESC,             T0.creationtime DESC,             T0.effectivedate DESC

Oracle shared memory exception ORA-04031

Posted: 28 Jun 2013 01:10 PM PDT

I'm trying to establish an Oracle database connection. It throws the following exception:

ORA-04031: unable to allocate 4048 bytes of shared memory     ("shared pool","unknown object","sga heap(1,0)","kglsim heap")

I have tried connecting the database through JDBC as well as SQL Developer, however it throws the exception in both case.

How can I fix this?

MySQL bin log missing data?

Posted: 28 Jun 2013 04:28 PM PDT

I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there. 

# at 1050548  #130617 18:40:03 server id 1  end_log_pos 1050619   Query   thread_id=844988    exec_time=0 error_code=0  SET TIMESTAMP=1371519603/*!*/;  BEGIN  /*!*/;  # at 1050619  # at 1050782  #130617 18:40:03 server id 1  end_log_pos 1050809   Xid = 33121028  COMMIT/*!*/;

It's puzzling because I get expected SQL in the mysqlbinlog output for statements executed in phpmyadmin but those coming from other PHP-based remote web servers appear to not be recorded.

My settings bin logging are:

bin_log = /path_to_sql_incrementals  binlog_ignore_db = mysql

Am I missing a logging option?

Mysql 5.0.95 / CentOS 5.9

Error 3154 while restoring a backup using WITH REPLACE

Posted: 28 Jun 2013 07:02 PM PDT

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.

I have a database with the name test2 which is the same database, but the data changed.

I want to restore test.bak over test2 database.

I am always getting the error:

Error 3154: The backup set holds a backup of a database other than the existing database.

I tried:

  1. I right-cliked on test2 -> Restore database -> From device

    I chose test.bak and checked With Replace but I get the error.

  2. Then I tried to right-click on test2 -> Restore file and filegroups

    I chose test.bak and checked With Replace but I get the error.

I can delete my old database and then restore my backup with the right name, but when I was using SQL 2008, I had no problem restoring over an existing database.

It seems that since I use SQL2012, I get this error a lot!

Memcached plugin on MariaDB?

Posted: 28 Jun 2013 02:22 PM PDT

I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL.

I'm wondering whether the memcached plugin has been added to MariaDB? And if not whether one can still use it as an addon?

And if not, can I use the existing Cassandra plugin of MariaDB to the same effect?

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Posted: 28 Jun 2013 12:45 PM PDT

I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package.

I am using SQLDeveloper 3.0 with an Oracle 11g database.

I have already developed a stored procedure 'acctg_detail_proc()' that generates a detailed list of accounting transactions within a specified period. What I am trying to do is create a summary report procedure 'acctg_summary_proc()' using the detailed data returned by acctg_detail_proc().

Since I am new to PL/SQL, all I have learned so far is how to retrieve the detailed data via a ref cursor, then LOOP through it, FETCHing each individual detail row. I want to figure out how acctg_summary_proc() can substitute that call to acctg_detail_proc() for a table name in a SELECT statement with a GROUP-BY clause. Here is the source code for an anonymous block where I tried to test it:

SET SERVEROUTPUT ON;  DECLARE      start_date VARCHAR2(50) := '04/01/2012';      end_date VARCHAR2(50) := '04/30/2012';      c_acctg_refcur    SYS_REFCURSOR;  BEGIN    acctg_rpt_pkg.acctg_detail_proc(start_date, end_date, c_acctg_refcur);    SELECT       date_posted,      debit_acct,      credit_acct,      SUM(dollar_amt)    FROM c_acctg_refcur    GROUP BY      date_posted,      debit_acct,      credit_acct;    CLOSE c_acctg_refcur;  END;

When I try to execute this code, I get the following error:

PL/SQL: ORA-00942: table or view does not exist

I realize I could use a nested SELECT statement instead of a table name, but I want to avoid duplication of source code. Is there any way to 'alias' a ref cursor so I can reference its data in a SELECT statement?

Here is some further background info: The called sub-procedure has ~600 lines of code, and selects 40 columns of data from a de-normalized VIEW. The corporate DBAs will not let me create any VIEWs that contain WHERE clauses, so that is not an option for me.

Thanks in advance, Ken L.

How to debug a db memory-leak causing mysql to go before it's own limits?

Posted: 28 Jun 2013 08:45 PM PDT

We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.

Until the second week of April, our db server has a stable consumption of memory of about 5 gigs (with a maximum of 7 gigs). But then, it started to increase limitless, even surpassing it's theorically maximum possible allocation.

This is our yearly munin graph showing the increase in the last 2 months:

This is another view from the last seven days after a restart in mysql:

This is the report created by mysqltuner.pl:

  -------- Performance Metrics -------------------------------------------------    [--] Up for: 4d 1h 56m 28s (152M q [431.585 qps], 383K conn, TX: 593B, RX: 29B)  [--] Reads / Writes: 90% / 10%  [--] Total buffers: 5.3G global + 10.2M per thread (200 max threads)    [OK] Maximum possible memory usage: 7.3G (46% of installed RAM)  [OK] Slow queries: 0% (2K/152M)  [OK] Highest usage of available connections: 13% (26/200)  [OK] Key buffer size / total MyISAM indexes: 16.0M/300.0K  [OK] Key buffer hit rate: 100.0% (61M cached / 9 reads)  [OK] Query cache efficiency: 70.8% (103M cached / 146M selects)  [!!] Query cache prunes per day: 501819  [OK] Sorts requiring temporary tables: 0% (926 temp sorts / 3M sorts)  [!!] Joins performed without indexes: 39128  [OK] Temporary tables created on disk: 16% (821K on disk / 5M total)  [OK] Thread cache hit rate: 99% (26 created / 383K connections)  [!!] Table cache hit rate: 10% (845 open / 7K opened)  [OK] Open file limit used: 3% (148/4K)  [OK] Table locks acquired immediately: 99% (65M immediate / 65M locks)  [!!] InnoDB data size / buffer pool: 5.5G/5.0G

We are in unknown territory here. Any help will be appreciated!

Edit: Adding my.cnf

  # The MySQL database server configuration file.    [client]  port            = 3306  socket          = /var/run/mysqld/mysqld.sock    [mysqld_safe]  socket          = /var/run/mysqld/mysqld.sock  nice            = 0    [mysqld]  character_set_server = utf8  collation_server = utf8_general_ci    user            = mysql  socket          = /var/run/mysqld/mysqld.sock  pid-file        = /var/run/mysqld/mysqld.pid  port            = 3306  basedir         = /usr  datadir         = /var/lib/mysql  tmpdir          = /tmp  skip-external-locking  bind-address            = 0.0.0.0    # Fine Tuning  max_connections         = 200  key_buffer              = 16M  max_allowed_packet      = 16M  thread_stack            = 192K  join_buffer_size        = 2M  sort_buffer_size        = 2M  read_buffer_size        = 2M  read_rnd_buffer_size    = 4M  thread_cache_size       = 128  thread_concurrency      = 24  table_cache             = 2K  table_open_cache        = 2K  table_definition_cache  = 4K    # This replaces the startup script and checks MyISAM tables if needed  # the first time they are touched  myisam-recover         = BACKUP    # innodb  innodb_buffer_pool_size         = 5G  innodb_flush_log_at_trx_commit  = 1  innodb_support_xa               = 1  innodb_additional_mem_pool_size = 32M  innodb_log_buffer_size          = 8M  innodb_flush_method             = O_DIRECT    # Query Cache Configuration  query_cache_limit               = 32M  query_cache_size                = 256M  query_cache_min_res_unit        = 256    # Logging and Replication  log_error                       = /var/log/mysql/error.log  log-slow-queries                = /var/log/mysql/slow.log  long_query_time                 = 1    # REPLICATION CONFIGURATION  log_bin                 = /var/log/mysql/mysql-bin.log  log-bin                 = mysql-bin  expire_logs_days        = 15  sync_binlog             = 1  server-id               = 1    ssl-ca   =/etc/ssl/private/repl/cacert.pem  ssl-cert =/etc/ssl/private/repl/master-cert.pem  ssl-key  =/etc/ssl/private/repl/master-key.pem    [mysqldump]  quick  quote-names  max_allowed_packet      = 16M    [isamchk]  key_buffer              = 16M

updation of a column after expire of specific time in mysql

Posted: 28 Jun 2013 06:45 PM PDT

  1. i have a table where i have a column named 'state'(int(2)) and 'modify_time' (time_stamp on update current timestamp).

  2. i have an update trigger which changes the value of state column to 0 based on some condition.

    3 . i want to set the value of state column to 1 after 24 hours of modify_time , if it still 0 .

    4 . i tried below method to test :

CREATE EVENT myevent1 ON SCHEDULE AT current_timestamp + interval 1 minute DO UPDATE test.mytabletable SET state = 0;

but it is not doing any thing.

is there any other alternative method ?

Repairing Broken Binary Replication in PostgreSQL 9.0

Posted: 28 Jun 2013 11:45 AM PDT

I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far is following the steps on the PostgreSQL wiki:

  • Issue select pg_start_backup('clone',true); on master
  • rsync everything except for pg_xlog from master to slave
  • Issue select pg_stop_backup(); on master
  • rsync pg_xlog

But the database is too big (300 GB), my connection is not really amazing (like 800 kB/s) and the files in base keep changing. So I was wondering if there's a more optimal way to do this.

Failed copy job deletes all users

Posted: 28 Jun 2013 01:45 PM PDT

Since the progression of this was apparently somewhat difficult to follow the first time around:

I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy database wizard with the detach/reattach method.

The copy failed. The log indicates that it was unable to execute a CREATE VIEW action for a particular view, because the datasource for the view did not exist. This is interesting in its own right, as the source most certainly exists, and the view(s) in question are fully functional in the source database. I'm not really clear, just yet, on how significant this is, as I've yet to figure out precisely why this generated an error.

This resulted in the deletion of all non-system user associations from the source database, leaving me with users dbo, information_schema, sys, and guest. Non-system roles were also deleted. Schemas were unaffected.

I have since restored the damaged database from backup. Academically, however, I would like to know the following:

  1. Why would a failed copy operation strip the user associations from the source database?
  2. Is there any sort of maintenance/rebuild/repair operation that could be performed on the source database to repair it?
  3. The loss of the users was immediately obvious, but given the rather mysterious nature of a failed copy job apparently damaging the source database, how concerned should I be about less obvious effects? In theory, I would expect restoring from backup would alleviate this concern, but do I have any cause to worry about, e.g., the master database?

This is entirely repeatable. I've made a handful of copies (manually) for the sake of experimenting with this particular issue, and in each case, the failed copy job obliterates the users and roles from the source database.

Removing the views that generated errors allows the copy to complete, and, as one would expect, produces a copy with identical data, users, etc., in addition to leaving the source database unaltered.

If it's important, I've tried rebuilding the indexes of the system databases, as well as the damaged database, to no appreciable effect.

The error generated:

1:00:25 PM,5/28/2013 1:00:25 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [Sourcing].[PermittedArrProducts]  AS  SELECT     dbo.tblArrProducts.ArrProductID, dbo.tblArrProducts.ArrangementID, dbo.tblArrProducts.ContainerTypeID, dbo.tblArrProducts.Quantity  FROM         Sourcing.PermittedArrangements INNER JOIN                        dbo.tblArrProducts ON Sourcing.PermittedArrangements.ArrangementID = dbo.tblArrProducts.ArrangementID    " failed with the following error: "Invalid object name 'Sourcing.PermittedArrangements'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Mysql - How to optimize retrival time in a table

Posted: 28 Jun 2013 07:45 PM PDT

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
| 8242063
+------------------------------+
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing ..! how can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!

thanking you!

SQL Server 2005 Replication

Posted: 28 Jun 2013 05:45 PM PDT

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.

server 1 windows 2003 server 192.168.10.1 connected by vpn SQL Server 2005 domain1.local

server 1  windows 2003 server  192.168.10.1 connected by vpn  SQL Server 2005  domain1.local

server 2 windows 2003 server 192.168.10.6 connected by vpn SQL Server 2005 domain2.local

server 2  windows 2003 server  192.168.10.6 connected by vpn  SQL Server 2005  domain2.local

When I setup up Replication everything looked fine until I looked at the sync status and it said:

The Agent could not be started    An exception occurred while executing a transact-sql statement or batch    sqlserveragent error request to run job  server1-username blah blah blah

From user sa refused because the job is already running from a request by user sa changed database context to technical error 22022.

I have cleared jobs in the server agent as well as restarted the service.

Could this be something to do with authentication between two non trusted domains as I can browse and even control each sql server via SQL studio but just not setup replication?

Yes I can manage each SQL Server in SSMS and we are using merge with snapshot.

Multiple parents and multiple children in product categories

Posted: 28 Jun 2013 10:45 AM PDT

I am making a ecommerce site. In this site I want to categorise the items into three different layers

primary category             sub category           sub category    >>electronic             >>smart phone          samsung    cameras                       tablets              nokia                                    laptop               apple                                  headphone

In the above table, I want to display the sub category after the customer selects the primary one. At the same time the 'samsung' also comes under 'camera'. Like this a parent can have any number of children and one child can have many parents. In the future we may change the primary and secondary.

What is the best solution for this? Which model will adopt our category: tree or nested?

Mysqldump tables excluding some fields

Posted: 28 Jun 2013 03:45 PM PDT

Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?

Replication master binlog rotation when network is unavailable

Posted: 28 Jun 2013 04:45 PM PDT

I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable.

After solving the network issue, the slave was not able to follow the master as it was doing through previous binlog and position.

That was solved by purging the binlog to last binlog in master and pointing the slave to that last binlog and previous binlog's position which was following.

I am wondering if this issue is normal?

Mongo connection failing with 'Transport endpoint is not connected'

Posted: 28 Jun 2013 02:45 PM PDT

I have a 2 server installation.

A web server with Apache and a DB server with MongoDB.

I am load testing it, and on ~300 RPS I am getting this error:

PHP Fatal error: Uncaught exception 'MongoConnectionException' with message 'Transport endpoint is not connected'.

The only thing I am noticing is that right before the fail, I am getting a lots of connections on Mongo:

insert  query update delete getmore command flushes mapped  vsize    res faults locked % idx miss %     qr|qw   ar|aw  netIn netOut  conn       time        0    659      0      0       0       1       0   208m  1.28g    40m      0        0          0       0|0     0|0    62k   217k   486   03:57:20

Almost 500 connections here... but never more than that!

Mongo is 2.0.3. PHP is 5.3.x (latest of Debian install...)

Help!
Posted by at No comments:

[SQL Server] Connect SQL Server 2012 with Database Engine

[SQL Server] Connect SQL Server 2012 with Database Engine

Connect SQL Server 2012 with Database Engine

Posted: 28 Jun 2013 02:52 PM PDT

Hi all,Installed SSMS 2012 Express addition with localDB. I don't know how to connect with Database engine and start using Sql Server?I also want to download Sample Database TSQL2012 for 70-461 practice?One more question do I have to give all the three Microsoft exams at the same time to become MCSA?Thank you.

Concatenating Values of 2 rows in SQL Server

Posted: 28 Jun 2013 09:32 AM PDT

Hi There,In the Picture attached this is the Table for one single item and all the items will be having the same 4 values since it has 4 fields, Is there any simple way to concatenate the Field STRGA255 to display as Shoes, Pumps.

Columns from one table match with rows in another table

Posted: 28 Jun 2013 07:52 AM PDT

Hi, I have 3 tables in the first table customernumber i have data filled. it has 4 columns which specifies the total number of different kinds of customers in the market.The second table has two columns customertypeID and customers type so it has only 4 rows (every customerType is assigned with a customerTypeID). here comes my work there is a third table which has CustomertypeID and number of customers. the data in the third table is filled basing on the first and second tables. the columns in the first table should match with the rows in the second table and data has to come into third table can any one of you help me in this ? Create table Customersnumber( cityname varchar(25),Loyal Customers interger,DiscountCustomers integer,ImpulseCustomers integer,NeedBasedCustomers integer,WanderingCustomers integer)insert into customersnumbervalues(1,5,8,56);insert into customersnumbervalues(7,65,98,756);create table customertype( customertypeID int identity(1,1) , Customertype ) insert into customertype values('DiscountCustomers'); insert into customertype values('ImpulseCustomers'); insert into customertype values('NeedbasedCustomers'); insert into customertype values('WanderingCustomers') create table customers ( ID int identity(1,1) , customertypeID int ,NumberOfCustomers int )

How to replace a value in Bracket with negative value in ssis

Posted: 28 Jun 2013 02:52 AM PDT

Hi, I have imported data into a database from an excel file and some of the columns contain values are like (392.03), (2.25), (65.00). Actually these values are should be -ve values can you guys help me how to convert these into -392.03,-2.25,-65.00
Posted by at No comments:

[MS SQL Server] Restore database

[MS SQL Server] Restore database

Restore database

Posted: 28 Jun 2013 04:00 AM PDT

We do weekend full backup, daily differential, and hourly transcation log backup.Do I need to do a full backup right after I restore a database (for example , from production to a test server?) so that the backup chain can be consistent, Or I don't really, in case we want to restore database later, I just need use the full backup of the database that I used to restore and then use differential and transaction log backup?But I found at least from SSMS UI, if you want to restore to point of time, in the listed backup there is no one that I copied from prodution.Thanks

EXEC xp_cmdshell error

Posted: 28 Jun 2013 02:57 AM PDT

So I am trying to run a powershell script in SQL Management Studios: netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP localport=any profile=any enable= yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3" exit-pssession set-item wsman:\localhost\Client\TrustedHosts -value 127.0.0.2 -force $pw = convertto-securestring -AsPlainText -Force -String P@$$word $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$pw $session = new-pssession -computername 127.0.0.2 -credential $cred invoke-command -script {netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP localport=any profile=any enable=yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3"} -session $session exit-pssession $session set-item wsman:\localhost\Client\TrustedHosts -value 127.0.0.3 -force $pw2 = convertto-securestring -AsPlainText -Force -String P@$$word $cred2 = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$pw2 $session2 = new-pssession -computername 127.0.0.3 -credential $cred2 invoke-command -script {netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP localport=any profile=any enable=yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3"} -session $session2 exit-pssession $session2 It runs the first part of the script but it errors out when attempting the second part of the script:----------------------------------------------------------------------------------------------------------------------new-pssession : [127.0.0.2] Connecting to remote server 127.0.0.2 failed with the following error message : WinRM cannot process the request. The following error with errorcode 0x8009030d occurred while using Negotiate authentication: A specified logon session does not exist. It may already have been terminated. Possible causes are: -The user name or password specified are invalid. -Kerberos is used when no authentication method and no user name are specified. -Kerberos accepts domain user names, but not local user names. -The Service Principal Name (SPN) for the remote computer name and port does not exist. -The client and remote computers are in different domains and there is no trust between the two domains. After checking for the above issues, try the following: -Check the Event Viewer for events related to authentication. -Change the authentication method; add the destination computer to the WinRM TrustedHosts configuration setting or use HTTPS transport.----------------------------------------------------------------------------------------------------------------------At my wits end with this thing. I have scoured the interwebs but can't seem to find anything that pertains to this. Any help would be appreciated.Using SQL Server 2008

Dreaded Documentation.

Posted: 28 Jun 2013 01:44 AM PDT

Its that dreaded time. The IT manager just handed me a long list of of all the servers that are probably running SQL and wants me to get him some basic documentation. Is anyone aware of a tool that can automagically work through a list of severs and record server/instance/db information to a central management server. I've looked a few offerings and they don't offer everything i want. Either they just work per instance or db, or they just export to a doc or flat file. I'm not interested in some fancy word/html/excel output just return the results to a db to report on demand.

buffer latch Issues

Posted: 27 Jun 2013 05:22 PM PDT

Hi,I am seeing more of the following buffer latch issues these days in my error log and quite dont understand whats the issue with? Please advice.Environment:Windows 2003 SP2SQL Server 2008 SP3Disk Space : SANRAM: 128 GigsCPU: 24A time-out occurred while waiting for buffer latch -- type 4, bp 0000001710FF4280, page 1:11062976, stat 0x16c0040d, database id: 10, allocation unit Id: 72057594185187328, task 0x000000001F209708 : 0, waittime 300, flags 0x1a, owning task 0x000000001F209708. Not continuing to wait.A time-out occurred while waiting for buffer latch -- type 2, bp 0000001E19FEE380, page 1:7611136, stat 0x6c0040d, database id: 7, allocation unit Id: 72057594430291968, task 0x000000000C208748 : 0, waittime 300, flags 0x1a, owning task 0x000000000C208748. Not continuing to wait.Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 000000002C3A09F8, type 4, Task 0x000000000C4E7B88 : 0, waittime 300, flags 0x1a, owning task 0x0000000006031DC8. Continuing to wait.SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL10.RISKWAREHOUSE\MSSQL\Data\MarketRisk_Prelive_1.LDF] in database [MarketRisk_Prelive] (7). The OS file handle is 0x0000000000000BD4. The offset of the latest long I/O is: 0x000001d825a800
Posted by at No comments:

[SQL 2012] Split one Name Column into Two Colmns using SSIS

[SQL 2012] Split one Name Column into Two Colmns using SSIS

Split one Name Column into Two Colmns using SSIS

Posted: 27 Jun 2013 07:16 AM PDT

I am working with SQL Server 2012 and Visual Studio 2010 with Excel 2007. I am building a package that will pull information from a .CSV file and import it into a table in a DB that we are creating for an HRIS project.The .CSV file has the following fields starting in B9 and running through column J (row length will vary)| PersNo | IDNo | Name | PArea | OrgUnit | OrgName | UserID | EntryDate |The issue I have is that I need to take the Name column and split it into a FirstName and LastName columns. I have scoured the internet and found a number of forums that detail methods that use Derived Columns, Conditional Split, Script Component and MultiCast. None of them were helpful or fit my needs.The issue is complicated by the name arrangement. I have three different name types that can be displayed.John SmithJohn A SmithJohn Smith IIIThe last two they will need to be split differently.If there is a middle initial they want it split as such.| FirstName | LastName |------------------------| John | Smith A |i.e. Smith and the Middle Initial will be in the LastName columnIf they have a suffix then they will need to be split as such.| FirstName | LastName |-------------------------| John | Smith III |i.e. Smith and the suffix will be in the LastName columnI need to know if there is a way that I can split this out in one package. Eventually this will be automated to run daily so I will also have to utilize a lookup I assume to filter out any duplicates and enter only new data.I appreciate anything information that anyone can provide.

SSIS 2012 Package run error with Script task.

Posted: 28 Jun 2013 12:14 AM PDT

Hi All,I have been looking for a solution for this to see if someone has encountered it before and I am on the verge of giving it up...We have SSIS packages(specifically Script Task) that have been created in VS2008 and with a .net 3.5 compatibility for the Script task. Can I run this packages using the dtexec in a 2012 server? Currently I am unable to do so on one of my test servers..What i understood from looking up online on Tech net is this : For 2012 :"The scripting engine has been upgraded to VSTA 3.0, which gives us a Visual Studio 2010 shell, and support for .NET 4. and the Script task in SSIS 2008 is using .Net framework 3.5, so that there have some incompatibilities "However that being said we have QA server where this ran just fine. It gave warnings that the package needs to be migrated; but it worked just fine.I checked the config of both the server where this packages are run. The CMD file invokes the right location of dtexec with the right provider of SQLNCLI11 C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exeBoth have 3.5 and 4.5 installed.. Am i missing something here? If anyone can provide any insights that would be great..Again..converting the package to the new 2012 is currently not an option and is a last resort..But since it runs on a QA server, I am just baffled :crazy:

DBmail stopped working

Posted: 27 Jun 2013 11:42 PM PDT

Hi,I have a newMicrosoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)While trying to alter sp_db_mail, my dbmail stopped working. I am getting below error in logs:Message1) Exception Information===================Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseExceptionMessage: The Transaction not longer valid.Data: System.Collections.ListDictionaryInternalTargetSite: Void ValidateConnectionAndTransaction()HelpLink: NULLSource: DatabaseMailEngineStackTrace Information=================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction() at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)Steps I have tried after this issue:1) Recreated send_db_mail procedure from instance running on 20122) Stopped and Started DB mail using sysmail_stop_sp and sysmail_start_sp3) Checked that service broker is running4) SMTP is working fine as DBMAIL using this SMTP on other server are running fine5) Deleted all unsent emails6) Rebooted the server/services/agent7) Anti-virus also seems to be okay as only this server is throwing errors8) Replaced databasemail.exe and other *.dll's from other 2012 serverAny help would be appreciated!Thanks

SSIS - MOVE File with File Sysyem Task

Posted: 27 Jun 2013 10:12 PM PDT

Hi there, Simply want to move a .BAK file from one location to the other..BAK starts in the following location - \\mhsvi-mgmt03\e$\oneserve_DW\ExportFromOneserveThe destination that I want it to end up is - E:\Oneserve Datacut (This is the E:\ where the SSIS is running from)A variable called bakfile has been created = MHS_DataCut_PROD.bakTwo paramters exsist = destinationpath = E:\Oneserve Datacut\ = sourcepath = \\mhsvi-mgmt03\e$\oneserve_DW\ExportFromOneserve\Two File Sources exsist which uses the following expressions - Backup File Source = @[$Project::sourcepath] + @[User::bakfile]Backup File Destination = @[$Project::destinationpath] + @[User::bakfile]When I add a File System task to my SSIS and simply copy the file it completes fine.But when I change it to move....it errors??? It has the exact same connection source and destination I don't understand why the error simply tells me in the Execution Results - [File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.".Can't for the life of me understand why

SSIS Package - Oracle client and networking components were not found.

Posted: 27 Jun 2013 09:38 AM PDT

We are running SQL Server 2012 EE 64-Bit Eval Copy on Windows Server 2008 R2 EE 64-Bit SP1 on our DB Server. We have installed Oracle 11G Client Install. We did a Custom Install and selected the following:SQL*PlusOracle NetOracle ODBC DriverOracle Services For Microsoft Transaction ServerOracle Objects for OLEOracle Provider for OLE DBOracle Data Provider for .NETConfigured sqlnet.ora and tnsnames.ora files. Tested tnsping to our Oracle Server/Database and it worked.Tested sqlplus to our Oracle Server/Database and it worked.Tested ODBC (System DSN and 'Oracle in OraClient11g_home1' driver) to our Oracle Server/Database and it worked.Set up Linked SQL Server to Oracle using the following and it worked:--With Microsoft OLE DB Provider for Oracle, use the Oracle server alias --(that is configured in the TNSNames.Ora file) for the @datasrc parameter.-------------------------------------------------------------------------- Test_DBTest Link-------------------------------------------------------------------------- Add a linked server for Test DBTest.USE [master]GOEXEC sp_addlinkedserver @server = 'Test_DBTest_shared' ,@srvproduct = 'DBTest' ,@provider = 'OraOLEDB.Oracle' ,@datasrc = 'DBTest'GO-- Add a login for the the remote linked server.USE [master]GOEXEC sp_addlinkedsrvlogin @rmtsrvname = 'Test_DBTest_shared' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = 'shared' ,@rmtpassword = 'Test1232' Then to create the SSIS Package, I go into Microsoft Visual Studio 2012 (it opens and displays Microsoft Visual Studio 2010 Shell). When I try to create a new OLE DB Connection to Oracle via Connection Manager, I select the "Native OLE DB\Microsoft OLE DB Provider to Oracle" I get the following error:"Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed."The Connection Manager does not display any other Oracle Providers. I saw the following during my research:"ProblemsBecause BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client."Is BIDS and Visual Studio the same application or uses the same code? Since, I installed SQL Server 2012 EE 64-Bit Eval Copy and the OS is 64-Bit, does this mean a 64-Bit version of Visual Studio was installed? What may I try to get this resolved?Thanks in advance, Kevin

SQL SERVER 2012 INSTALLATION Error 1311.Source file not found: C:\Users\pc\Desktop\Sqlserver2012\redist\VisualStudioShell\

Posted: 27 Jun 2013 08:07 AM PDT

TITLE: Microsoft SQL Server 2012 Setup------------------------------The following error has occurred:Error 1311.Source file not found: C:\Users\pc\Desktop\Sqlserver2012\redist\VisualStudioShell\VC10SP1\x64\vc_red.cab. Verify that the file exists and that you can access it.Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0xDF039760%25401201%25401------------------------------BUTTONS:&RetryCancel------------------------------this is sql server 2012 Eval Edition and I'm getting this error any time I want to install. I appreciate if you know and want to help me.
Posted by at No comments:

[T-SQL] Converting Integer date & time into datetime variable .. help!

[T-SQL] Converting Integer date & time into datetime variable .. help!

Converting Integer date & time into datetime variable .. help!

Posted: 27 Jun 2013 07:43 AM PDT

Hi!I have two fields that are integer type in this formatDATE: 1130627 = (exclude the first digit)(13-06-27)TIME: 51458 (24hr) = 5:14:58I am trying to get this into a single datetime field and I cant figure it out :( Can anyone help please.Thanks!R.

Please Help me in the query

Posted: 27 Jun 2013 09:23 PM PDT

Hi team,The below query takes 100 % CPU. When I see in execution plan, its going in index seek. Kindly help me to get rid off this issue.SELECT CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END 'cli', min(call_date) 'date' FROM DbName..table_name1(nolock) WHERE network_id = '1' and dialed_number not in(select msisdn from table_name2(nolock)) group by CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END Note : CLI & call_date comes in table_name1 tableThanks in advance

Help with comma separated string in UDF to return comma separated string

Posted: 27 Jun 2013 06:43 AM PDT

Hi all,I have a top level sproc that returns x number of records. Each record has a field called CategoryID that stores a comma separated list of ID's like this:12,15,33Now I need to create a UDF that accepts this string of ID's and returns the corresponding Category Names for each, like this:'Sports,Education,Science'These are the 3 category names for the ID's of 12, 15 and 33.Ideally, using the input param of the UDF like this would be perfect:select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)from AccountOpeningCategories AOCwhere AOC.AccountOpeningCategoryID in (@CategoryIDList) But that doesn't work. I can't use temp tables in a UDF nor can I split the ID string in to a table to use as part of my select. Also, I can't execute a sproc in a select from another sproc.Since I've learned many ways how NOT to do this, I was hoping someone could look at my sproc below (which works) and tell me how I can convert this to a UDF to call within a select of another sproc like this:select F.column1, F.column2, dbo.acct_f_ReturnCategoryList(F.CategoryIDList) as CategoryListfrom Foo as FThanks,Mark[code]alter PROCEDURE acct_f_ReturnCategoryList @IDList varchar(100)asDeclare @list varchar(8000), @sql varchar(8000), @UID uniqueidentifier, @idx smallint, @Delimiter varchar(5), @slice varchar(100) --------------------------------------------------------------------------------- Split the incoming comma separated list in to a temp table and match it with -- a guid in case multiple users are in the DB at the same time.-------------------------------------------------------------------------------set @UID = newid()select @idx = 1 set @Delimiter = ',' if len(@IDList)<1 or @IDList is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@IDList) if @idx!=0 set @slice = left(@IDList,@idx - 1) else set @slice = @IDList if(len(@slice)>0) insert into dbo.tempIDListTable(TempID, GUID) values(@slice, @UID) set @IDList = right(@IDList,len(@IDList) - @idx) if len(@IDList) = 0 break end-------------------------------------------------------------------------------set @list = ''select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)from AccountOpeningCategories AOC, dbo.tempIDListTable IDLT where AOC.AccountOpeningCategoryID = IDLT.TempIDand IDLT.[GUID] = @UID-- Clear this users entry.delete from tempIDListTable where GUID = @UID-- Return the list.select @list[/code]

Top 2 with Count

Posted: 27 Jun 2013 10:17 PM PDT

Hi All,I have written a query. [quote]declare @N intdeclare @N intSet @n=isnull(@N,2000)select top(@N) * from(select 10 'Top 2'UnionSelect 20UnionSelect 30unionselect 40unionselect 50)T1[/quote]this is working fine. if change 2000 to 2 it gives me the result only 2 results. Now my question is when I am not setting any value then it should show me all the records.[quote]declare @N int--Set @n=isnull(@N,2000)select top(@N) * from(select 10 'Top 2'UnionSelect 20UnionSelect 30unionselect 40unionselect 50)T1[/quote]when I am executing the above query it gives the error that TOP clause contains an invalid value as I have not set any value.I want to see all the records when I am not setting any value.Please help..........Thanks in advance

How to avoid this error when scripting multiple sp? Thanks.

Posted: 27 Jun 2013 07:42 AM PDT

I have multiple sp with @sql defined, when I script these sp, it throws error saying @spl already defined, well, true or false, in the final generated script, yes; but I want to re-generate all the sp in a new database, eventually there would be multiple sp generated and those @sql will fall into different sp and hence @sql is NOT already defined.How do I avoid this error?Thanks.

XML Query

Posted: 27 Jun 2013 06:59 PM PDT

Hi all,This is is quite simple, but i need you help pls!I need to get the value of one of the parameters (CustomProperty1) included in the Field of XML data type. Here the example of data in this field: DECLARE @XML XMLSET @XML ='<CP> <CustomProperty1>Someroperty1</CustomProperty1> <CustomProperty2>Someroperty2</CustomProperty2> <CustomProperty3>Someroperty3</CustomProperty3></CP>'Meaning, i want the select to return the value: Someroperty1Thanks!!

Function inside a View

Posted: 27 Jun 2013 03:40 PM PDT

Hi, We have a View which calls a function to get the data. Following is the code of the View:[code="sql"]CREATE View View_FacultyTimeTableAsSelect Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,'' As VirtualClass From SchoolDefinePeriod As A,SchoolClass As B Where A.ClassId=B.ClassIdUnionSelect Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,A.VirtualClass From SchoolDefinePeriod As A,SchoolClass As B,FN_SchoolVirtualClass()As C Where A.VirtualClass=C.VirtualClass And B.ClassId=C.ClassID[/code] Is it a bad practice to call a function inside a View??....How many times does this function get compiled??....Only Once(when the View is created) or every time the View is used in a Query?......Thanks in Advance for your help guys. :-):-)

Extract first numeric value from a string

Posted: 27 Jun 2013 05:57 AM PDT

Brothers,I need to parse the first numeric value from a string that usually contains several numerics.This function works OK when the first number has no decimals ( 1 - returns '500000'). In #2 (naturally!) the result is 0Can you spare some help? [code="sql"]alter FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))RETURNS VARCHAR(8000) ASBEGIN DECLARE @X VARCHAR(100)Select @X=@inString Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))RETURN @XEND-- 1. select dbo.fnExtractDigits ('NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)')-- 2. select dbo.fnExtractDigits ('NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)')[/code]

Converting varbinary to numeric type in tsql -- decimal in c#

Posted: 27 Jun 2013 01:39 AM PDT

We are currently dealing with a legacy application, where the decimal numbers from c# are stored in the database (sqlserver) as varbinary types. This (I think) was done to keep formatting with the number.The problem now is that we can not search/index on the number in the database. It has to be restored to a c# decimal in the application and then only does this make sense.How can I convert the varbinary to a decimal/numeric type in the sqlserver?I don't mind creating a new column/table to store the numeric value and formatting information derived from the varbinary.I know in c# you can create a decimal number by giving it an array of ints.Here is the description of how c# interprets and converts an int array to decimal type. [url=http://msdn.microsoft.com/en-us/library/aa326746(v=vs.71).aspx]http://msdn.microsoft.com/en-us/library/aa326746(v=vs.71).aspx[/url]The binary representation of a Decimal number consists of a 1-bit sign, a 96-bit integer number, and a scaling factor used to divide the integer number and specify what portion of it is a decimal fraction. The scaling factor is implicitly the number 10, raised to an exponent ranging from 0 to 28. bits is a four-element long array of 32-bit signed integers. bits [0], bits 1, and bits [2] contain the low, middle, and high 32 bits of the 96-bit integer number. bits [3] contains the scale factor and sign, and consists of following parts: Bits 0 to 15, the lower word, are unused and must be zero. Bits 16 to 23 must contain an exponent between 0 and 28, that indicates the power of 10 to divide the integer number. Bits 24 to 30 are unused and must be zero. Bit 31 contains the sign; 0 meaning positive, and 1 meaning negative. A numeric value might have several possible binary representations; all are equally valid and numerically equivalent. Note that the bit representation differentiates between negative and positive zero. These values are treated as being equal in all operations.Help is highly appreciated.
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog