Thursday, September 26, 2013

[how to] How to create login and logout trigger for client audit?

[how to] How to create login and logout trigger for client audit?

How to create login and logout trigger for client audit?

Posted: 26 Sep 2013 09:30 PM PDT

I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration...

Can anyone offer me a simple method to create login and logout trigger for audit specific client not the sysadmin,

Thanks in advance.

Best way to display number of overspent projects

Posted: 26 Sep 2013 07:46 PM PDT

I have the first view displayed as:

CREATE VIEW Projects AS      SELECT DepartmentName,           COUNT(*)NumberOfProjects,          OfficeNumber,          Phone      FROM   DEPARTMENT AS D JOIN PROJECT AS P              ON D.DepartmentName = P.Department        GROUP BY Department;

Which gives:

| DepartmentName | NumberOfProjects | OfficeNumber | Phone        |    | Accounting     |                1 | BLDG01-100   | 360-285-8300 |  | Finance        |                2 | BLDG01-140   | 360-285-8400 |  | Marketing      |                2 | BLDG02-200   | 360-287-8700 |

However, I want to display a "NumberOfOverBudgetProjects" for each department in this view. I created a view which lists which projects are going over their hourly budget: 

CREATE VIEW Budgets AS      SELECT Department,           MaxHours AS ProjectMaxHours, SUM(HoursWorked) AS TotalHoursWorked,          SUM(HoursWorked) - MaxHours AS Balance      FROM   PROJECT AS P JOIN ASSIGNMENT AS A          ON P.ProjectID = A.ProjectID      GROUP BY P.ProjectID

;

Which gives:

+------------+-----------------+------------------+---------+  | Department | ProjectMaxHours | TotalHoursWorked | Balance |  +------------+-----------------+------------------+---------+  | Marketing  |          135.00 |           160.00 |   25.00 |  | Finance    |          120.00 |            85.00 |  -35.00 |  | Accounting |          145.00 |           130.00 |  -15.00 |  | Marketing  |          150.00 |           165.00 |   15.00 |  | Finance    |          140.00 |            52.50 |  -87.50 |  +------------+-----------------+------------------+---------+

Marketing department should list 2 projects being over budget and Accounting and Finance should both list 0.

Any ideas how I can incorporate a column of "OverBudgetProjects" in the original view? Or by creating a new view?

How to drop a column named 'column'?

Posted: 26 Sep 2013 05:41 PM PDT

I need to delete a column named "column" form a table but the standard:

mysql> ALTER TABLE table_name DROP COLUMN 'column';

returns:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''column'' at line 1

I am thinking "column" is reserved word and shouldn't be used. But how do I get around to deleting it ?

Deny access to one database, allow all the rest

Posted: 26 Sep 2013 07:49 PM PDT

How can I deny access to specific databases from a login? I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite? I want the login to have access to everything but the ones that I specify

MYSQL - Unable to change 'old_passwords' variable

Posted: 26 Sep 2013 05:53 PM PDT

I have full access to a 'MYSQL 5.0.27-community-nt-log' database one of our new updates for a core app requires PHP 5.4, after installing and configuring I'm stuck at the error regarding old vs. new passwords.

I've followed every thread I can find and I'm pretty sure I understand the process:

  1. Disable 'old_passwords'
  2. Reset the password to the new 41 encryption

But I cannot seem to get 'old_passwords' to be set to 'OFF'. Logging in as root I've tried the following:

  • I've removed it from the my.cnf file (I've also tried setting it to '0', 'False' etc)
  • Manually setting it in the query browser both globally and session
  • Turning the option on/off in MYSQL administrator

and restarted the service but 'show variables like 'old_passwords' always reports as 'ON'.

Changing the password still remains at a 16 length.

I'm sure once I'm able to turn this variable off I should be fine, Any advice?

How to interpretate and modify a dBase (.dbf) file?

Posted: 26 Sep 2013 03:48 PM PDT

I'm working with dBASE (.dbf) files for a project using a GIS software because it seems that they are more efficient. I've converted several .xml file to .dbf with OpenOffice 3.4 and loaded into the GIS however in some columns, the fields that appeared with different numbers in Open Office, turn to a series of one (1) in the GIS attribute table. This is an example of my first row of one element:

Na,N,6,2

I think that I can affect it changing the first row of my .dbf file.

First question:

I would like to know what does it means every character. I know that the first (e.g. Na) is the name of the field, N is probably the type? Number? I've no idea about the third and the forth (red and blue arrows).

Second question:

Why if I write "Na" when I load the file it become "NA", with capitalized letters? .dbf open with OpenOffice

Thanks a lot!

Can I speed up this slow query which should be limited by MIN/MAX values?

Posted: 26 Sep 2013 07:21 PM PDT

The intention here is to look at the user retention over a period of time.

My current solution employs a temporary table, since I do this over several ranges.

CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT);    INSERT INTO user_retention_temp  (SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id FROM day_item GROUP BY player_id);

Here I would have liked to limit this query to only match player_id's that has a first/last that's in a specific period of time.

Instead I'm force to do that for the consequent query:

SELECT LEAST(last - first, 7) AS diff, count(*) AS amount   FROM user_retention_temp  WHERE first >= ? AND last < ? GROUP BY diff

This is pretty awful since I'm filling up the temporary table with lots of data I don't need.

Is there a faster way? Assume at least 2-3 million entries in day_item and that everything we need is indexed.

Xml stored as varbinary

Posted: 26 Sep 2013 09:22 PM PDT

I'm wondering if I have to store a XML information inside a column (just storing, not for querying ) (SQL SERVER) which Datatype is better to use XML or Varbinary? I did an example:

DECLARE @ClientList XML  SET @ClientList =  '<?xml version="1.0" encoding="UTF-8"?>  <!-- A list of current clients -->  <People>  <Person id="1234">  <FirstName>Juan</FirstName>  <LastName>Perez</LastName>  </Person>  <Person id="98765">  <FirstName>Alfredo</FirstName>  <LastName>Domiguez</LastName>  </Person>  </People>'    Declare @ClientListBinary Varbinary(max)  set @ClientListBinary = CONVERT (varbinary(max),@clientlist,1)  print datalength(@ClientList)  print datalength(@ClientListBinary)

and the result is that Varbinary uses more space... any thoughts on that

How do I find out how far along my PostgreSQL query is?

Posted: 26 Sep 2013 02:45 PM PDT

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

Posted: 26 Sep 2013 04:44 PM PDT

Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course.

What are the advantages and disadvantages of either using or not using the builtin or modular innodb features?

Thanks very much for any explanation :)

MongoDB cloneCollection between CentOS servers times out at 2 hours

Posted: 26 Sep 2013 12:46 PM PDT

I'm trying to clone a MongoDB collection from CentOS server A to CentOS server B. Both are running 2.4.6. The copy works great until exaxctly 2 hours, at which point I get a 9001 socket exception: "errno:10054 An existing connection was forcibly closed by the remote host."

I have tried to set the tcp_keepalive_time to 3600, down from 7200 (2 hours) on both servers with the hope that my keep alive probes will tell routers to keep my connection alive after the 1 hour mark.

No dice. Even with a shorter keepalive time (and 70 second interval for keepalive probes), my connection dies at 2 hours.

Does anyone know how to set up my servers and/or my mongodb configuration to allow my tcp connection to be active past 2 hours?

Thanks!

Convert SQL Server query to MS Access syntax

Posted: 26 Sep 2013 12:40 PM PDT

I have this query in SQL Server:

SELECT Dated, AccountID, AccountName, Rate,     case (when (Credit-Debit) > 0 then Credit-Debit END) as Credit,    case (when (Credit-Debit) < 0 then Credit-Debit END) as Debit,    Balance  FROM TempTrallBalance

How can I convert it to Access syntax?

Oracle query is slower the second time

Posted: 26 Sep 2013 01:20 PM PDT

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried lowering it down, but lost the symptom)

The first time I run the query, it's 2 seconds. Subsequent times it's 3, 4, or even 5 seconds. This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up.

This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache. The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it. But that of course makes no sense.

Does anyone know why this is happening? Or any red flags in the execution plan that would cause something like this?

FYI, this is a SELECT, with multiple joins, aggregation, and views. No internal PL/SQL.

Thank you

Should I be concerned about excessive Com_change_db commands?

Posted: 26 Sep 2013 04:38 PM PDT

I installed MONyog recently to check for problems on a MySQL server that I admin. Most of the information was useful and helpful, but there's one warning I am not sure is worth fixing.

It reports that Com_change_db commands are excessive (currently 9+ million or 58 per second), or unusually high. The connections to the database are strictly Windows .NET applications and they use Connector/Net as an API.

I typically connect such that I specify which database to use when creating a MySqlConnection object. I discovered in a test that doing this causes the Com_change_db to increment by one when the connection is established.

I can "fix" it by not specifying a database, and instead qualifying the database within the query every time. (e.g. db.table.col instead of table.col)

Is it worth going through the entire code base of the application to make this change? Is MONyog concerned with something that doesn't matter?

db2ckbkp - How to determine what tablespaces are temp-tablespace

Posted: 26 Sep 2013 11:55 AM PDT

I am writing a script to restore databases from backup. The script should need as little input as possible to fulfill this task (in an efficient way). The restore command takes the parallelism parameter. The idea is to set this parameter to the number of tablespaces that are not temporary tablespaces.

I found the db2ckbkp command which will not only verifies the backup file, but also outputs lots of (useful) information.

Currently I was planning to run it with -t to get the tablespace information. I just have troubles to interpret the information printed. following the output that is printed for one of the tablespaces.

        USERSPACE1                tbspInImage: T                           ID: 2                      flags: 3122                     flags2: 400                extent_size: 32              prefetch_size: 32                    version: 9                    flavour: 6                      state: 0        statechangeobjectid: 0          statechangepoolid: 0                    LifeLSN: 00000000000005C2             LoadPendingLSN: 0000000000000000            LoadRecoveryLSN: 0000000000000000                   BeginLSN: 0000000000000000                     EndLSN: 0000000000000000                 StordefLSN: 0000000000000000            Full Backup LSN: 0000000000000000            Last Backup LSN: 0000000000000000           Full Backup Time:  00000000 = "19691231180000"           Last Backup Time:  00000000 = "19691231180000"                 TotalPages: 8192               UseablePages: 8160                reorgPoolID: 0                 reorgObjID: 0             poolReorgCount: 0            # of containers: 1              current_group: 0                  cont_csum: 2004299914        current_map_entries: 1                  page_size: 4096                   map_csum: 4294967294       tsp rfwd encountered: 16                Container CB                               Type: 6                         TotalPages: 8192                        UsablePages: 8160                 # of OS rsvd bytes: 512                      Page 0 offset: 131072                         Tag offset: 512                      Extent offset: 0                               Name: /data/devinsth/NODE0000/.../T0000002/C0000000.LRG

To determine whether it is an temp space or not, the flavour is not suitable, since it is 6 for all of the tablespaces. However the container type seems to be different for temp spaces. Type is 0 for temporary tablespaces and 6 for all other tablespaces.

When I use the -a parameter, I get another piece of information. 

    00003A91: SYSCATSPACE              ID:          0  Type:                 2 Datatype:           0              Size:    29932  NumContainers:        1 tbspInImage:        1        00003AC5: TEMPSPACE1              ID:          1  Type:                 1 Datatype:          16              Size:        1  NumContainers:        1 tbspInImage:        1        00003AF9: USERSPACE1              ID:          2  Type:                 2 Datatype:          32              Size:       96  NumContainers:        1 tbspInImage:        1        00003B2D: TS_DAT_SPF_4K              ID:          3  Type:                 2 Datatype:          32              Size:     7776  NumContainers:        1 tbspInImage:        1        00003B61: TS_IND_SPF_4K              ID:          4  Type:                 2 Datatype:          32              Size:       96  NumContainers:        1 tbspInImage:        1        00003B95: TS_LOB_SPF_4K              ID:          5  Type:                 2 Datatype:          32              Size:       96  NumContainers:        1 tbspInImage:        1        00003BC9: TS_TMP_4K              ID:          6  Type:                 1 Datatype:          16              Size:        1  NumContainers:        1 tbspInImage:        1        00003BFD: TS_USR_TMP_BAT_4K              ID:          7  Type:                 1 Datatype:          64              Size:        1  NumContainers:        1 tbspInImage:        1

Can I use the type from this table? It seems to be 1 for temporary tablespaces and 2 for all other tablespaces. I tried to find documentation on the internet that explained the output of db2ckbkp but wasn't successful. Any help in this matter is appreciated.

Find rows where any column matches a search pattern

Posted: 26 Sep 2013 02:49 PM PDT

I have a table "Test" with 15 different columns of different data types.

I'm looking for rows having characters like "ABC". But "ABC" can be present in any column and I want to find whole records having "ABC".

I cannot restrict the row using a WHERE clause, because I really do not have any idea in which column the ABC resides? Is there a way to do this?

SQL Server 2005 get execution plan of a overnight stored procedure

Posted: 26 Sep 2013 03:49 PM PDT

There is a overnight job runs long time. I have used profiler to get the trace and identified a statement (in a stored procedure) that takes most of execution time. Now I need to see the execution plan of the statement so I can see what I can do. But I can't rerun the query since it uses a temp table created in the proc. And the logic of the proc is rather complex and very hard to reproduce (involving getting data from file system and data exchanging with Oracle database via linked server). I am not saying reproducing is not possible but would be rather time consuming. So is there a way I can see the execution plan of the proc or the statement in particular?

Attribute and Value tables on SQL

Posted: 26 Sep 2013 07:21 PM PDT

I'm not a SQL expert and I'd like to ask for the best practices on the following schema.

On my current project, we're using Attribute and AttributeValue tables quite often. I'm thinking this might not be a good practice since it involves joins when we need that data.

Example. Given the Person table, there's 2 more tables that basically contain "extra" data about person; those are PersonAttribute and PersonAttributeValue. I don't have a strong argument to prevent this practice on my team, but I'd like to hear what SQL experts have to say.

Thanks

How to speed up query on table with millions of rows

Posted: 26 Sep 2013 12:02 PM PDT

The Issue:

I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are sending many measurements in different intervals on different times. I want to make a query which selects all the most actual ( 'actual' I mean the latest from all measurements made in each day) measurement of each day for 2 months e.g from 2013-01-01 to 2013-02-01.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns. I've also created auxiliary table that contains max(MeterDate) and MeasurementsId when the measurement was given. I've noticed that index can't be made on MeterDate because it contains date and time which is not useful for making an index on it. So i converted the MeterDate -> CONVERT(date, MeterDate). I though that after joining The auxiliary table with [dbo].[Measurements] the query would be faster but still query takes more than 12s which is too long for me.

The structure of table:

Create table [dbo].[Measurements]    [Id] [int] IDENTITY(1,1) NOT NULL,  [ReadType_Id] [int] NOT NULL,  [Device_Id] [int] NULL,  [DeviceInterface] [tinyint] NULL,  [MeterDate] [datetime] NULL,  [MeasureValue] [decimal](18, 3) NULL

Every row of Measurements table include measurement value on direct MeterDate e.g. "2008-04-04 13:28:44.473"

Direct select structure:

DECLARE @startdate datetime= '2013-07-01';   DECLARE @enddate datetime = '2013-08-01';    SELECT *  FROM [dbo].[Measurements]   WHERE [MeterDate] BETWEEN @startdate and @enddate

Does anyone knows how to rebuilt table or add new or add indexes on which column that speed up query a bit ? Thanks in advance for any info.

Edit:

The table that I used was created by this query

with t1 as  (      Select  [Device_Id], [DeviceInterface],  CONVERT(date,  MeterDate) as OnlyDate, Max(MeterDate) as MaxMeterDate      FROM [dbo].[Measurements]       GROUP BY [Device_Id], [DeviceInterface], CONVERT(date,  MeterDate)  )  Select t1.[Device_Id], t1.[DeviceInterface],t1.[OnlyDate], r.Id    INTO [dbo].[MaxDatesMeasurements]  FROM t1  JOIN [dbo].[Measurements] as r ON r.Device_Id = t1.Device_Id AND r.DeviceInterface = t1.DeviceInterface AND r.MeterDate = t1.MaxMeterDate

Then I wanted to join the new created table [dbo].[MaxDatesMeasurements] with old [dbo].[Measurements] and select direct rows 

DECLARE @startdate datetime= '2013-07-01';   DECLARE @enddate datetime = '2013-08-01';       Select *  From [dbo].[MaxDatesMeasurements] as t1   Join [dbo].[Measurements] as t2 on t1.[Id] = t2.[Id]   WHERE t1.[OnlyDate] BETWEEN @startdate AND @enddate

How can I integrate a new system to a design scheme I previously created?

Posted: 26 Sep 2013 08:18 PM PDT

This is a follow-up to a question I wrote a while ago, for reference, you can look it up here.

I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown) enter image description here

Now that a new educational system is introduced(while the old one is still not "deprecated") I have been looking for a way to integrate both curriculum systems together without creating a whole new different system; As the two systems are fundamentally different, this have been quite a challenge for me.

Here's a picture describing the new system . . .

enter image description here

(English level 1, 2, and 3 are classified as courses)

See, the new system removes the whole grades system. A jr. high school student can enroll in the exact same course(English level 3, for example) as a sr. high school student.

Is there a way to make those two systems work with my current design scheme? Or should I create a whole new scheme for the new system?

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

Posted: 26 Sep 2013 08:23 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.

Oracle shared memory exception ORA-04031

Posted: 26 Sep 2013 02:23 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?

Memcached plugin on MariaDB?

Posted: 26 Sep 2013 04:23 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?

Failed copy job deletes all users

Posted: 26 Sep 2013 09:23 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 Tables Require Daily Repairs - Server, Table or HD?

Posted: 26 Sep 2013 05:23 PM PDT

I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.

The bigger ones are:

Table A - 599k rows / 867MB  Table B - 2.1m rows / 146MB  Table C - 2.2m rows / 520MB

It seems table C needs to be repaired pretty frequently, Tables A & B not as much.

When the table needs to be repaired, I'm not seeing it being marked as crashed or in use. But through other tools, I can see the data is not what it should be.

When I do repair the table, I'll see a message similar to:

[table c] repair info Wrong bytesec:  54-55-102 at 368251940; Skipped  [table c] repair warning Number of rows changed from 2127934 to 2127931

or

[table c] repair info Wrong bytesec:  171-30-101 at 341237312; Skipped  [table c] repair warning Number of rows changed from 1984585 to 1984582

I've tried making adjustments in my.cnf but no difference.

The server is a cloud server running both MySQL and Apache. Plenty of space available on all HDs:

Filesystem            Size  Used Avail Use% Mounted on  /dev/xvda2             99G   14G   80G  15% /  tmpfs                 1.9G     0  1.9G   0% /dev/shm  /dev/xvda1             97M   49M   44M  53% /boot  /dev/xvdc1            296G   25G  257G   9% /data

I'm not sure if this is a problem with the cloud HD, the server or the tables themselves. The problem didn't start happening until about 2 months ago and the size of the DB has only changed by 300-400MB until now.

Any idea what I should be looking at to verify where the problem might be?

Using MySQL v5.1.66 and MyISAM

Thanks in advance.

Best, Cent

Azure SQL Administrator Can't Access Master Database

Posted: 26 Sep 2013 12:23 PM PDT

I created an SQL server and database. I can log in to the database manage page with my administrator account, and manage the database I created, but when I try to look at the master database, it says:

Failed to create context. Details: The current user does not have VIEW DATABASE STATE permissions on this database.

What am I doing wrong? I couldn't find anything else like this on the internet.

I log in with the account that the sql server page on the azure managing portal says is the "Administrator Login".

Constructing an SQL query with time intervals

Posted: 26 Sep 2013 03:23 PM PDT

I've a simple but large table in an SQL database which has four fields (id(int PK), time (unix timestamp, not null), value (double, not null) and an ID_fk (integer foreign key).

Data is recorded every minute, but at a seemingly random second for subsets of sensors, each sensor has a foreign key value though.

My question is this, I need to write a query that will tell me when, over all two minute intervals in the dataset, a series of sensors have a value greater than 0.9.

I was thinking for trying to create a temporary table and do it that way, simplifying the time by only storing it at minute resolution?

Any advice would be greatly received, Thanks, James

Mysqldump tables excluding some fields

Posted: 26 Sep 2013 07:23 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?

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

Posted: 26 Sep 2013 01:23 PM PDT

we are using a Sybase SQL Anywhere 12 db.

In the db there are indices, which are unique, but shouldn't be unique.

Therefore I search for a quick way to list all tables with unique primary keys.

I tried

SELECT z.name FROM sysobjects z JOIN sysindexes ON (z.id = i.id) WHERE type = 'U'

The result was an error message: Anweisung konnte nicht ausgeführt werden. Tabellenname 'sysindexes' ist mehrdeutig SQLCODE=-852, ODBC 3-Status="42S13" Zeile 1, Spalte 1

Roughly translated: sysindex is ambiguous.

I found on internet the query:

select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),  'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end  + case when (status & 1)<>0 then ', '+'ignore duplicate keys' else '' end  + case when (status & 2)<>0 then ', '+'unique' else '' end  + case when (status & 4)<>0 then ', '+'ignore duplicate rows' else '' end  + case when (status & 64)<>0 then ', '+'statistics' else case when (status & 32)<>0 then ', '+'hypothetical' else '' end end  + case when (status & 2048)<>0 then ', '+'primary key' else '' end  + case when (status & 4096)<>0 then ', '+'unique key' else '' end  + case when (status & 8388608)<>0 then ', '+'auto create' else '' end  + case when (status & 16777216)<>0 then ', '+'stats no recompute' else '' end),  'index_name' = name  from sysindexes where (status & 64) = 0  order by id

Which looked what i wanted. But there was still the same result of ambigious sysindexes.

What dows ambigious indexes mean in this context? Will/Can this cause any error in future?

As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for.

But I still want to know how a programmatically solution looks like.

Mongo connection failing with 'Transport endpoint is not connected'

Posted: 26 Sep 2013 06:23 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] Best way to store images

[SQL Server] Best way to store images

Best way to store images

Posted: 08 Mar 2013 06:59 PM PST

Hi.I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.With this condition which way is better to store images?

Can You make this code Shorter??..

Posted: 26 Sep 2013 01:25 PM PDT

[code="sql"] DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max) SET @SqlQuery = '' SET @SqlQueryStatus = '' SET @SqlQueryFirstname = '' SET @SqlQueryMiddlename = '' SET @SqlQueryLastName = '' SET @SqlQuerySex = '' SET @SqlQueryStatus = '' IF @sexID <> 0 SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID) IF @statusID <> 0 BEGIN IF LEN(@SqlQuerySex) > 0 SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID) ELSE SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID) END IF LEN(@firstname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%''' END IF LEN(@middlename) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%''' END IF LEN(@lastname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0 SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%''' END SET @SqlQuery = ' SELECT * FROM TestMyView ' + @SqlQuerySex + @SqlQueryStatus + @SqlQueryFirstname + @SqlQueryMiddlename + @SqlQueryLastname [/code]

Conversion failed when converting the varchar value ' WHERE statusID = 1' to data type int.

Posted: 26 Sep 2013 12:16 PM PDT

Hi everyone ;-)why is this statement has error??.. :-( i can't figure it out.. SET @statusID = ' AND statusID = ' + convert(varchar(20), @statusID)

Red-Gate SQL DBA Bundle

Posted: 25 Sep 2013 10:33 PM PDT

Hi Guys,Firstly I would like to say thanks to all replies on my previous post. SQLServerCentral is my number 1 forum site.Getting to the point. We as a company are looking at the Red-Gate software. Now me as newly inexperienced appointed dba are looking on providing this solution to the company.I'm currently looking at the SQL DBS Bundle option. Anybody have experience with this? What is your findings on this application. I've downloaded and used the free trial, and must say that it looked easy to setup and to use.Any suggestions from your side?

sql server 2008 r2

Posted: 25 Sep 2013 05:46 PM PDT

please from $ days I am struggling With it hi while installingsql server 2008 r2 at the end its giving error service 'sql browser' start errorand installation failing.please can anybody help me I have tried 13 to 14 timesI have searched the net they said use default instancedelete xml 7.0 nothing workingos:windows 7 home premiumregardssudarshan

Is this Vulnerable for SQL injection?..

Posted: 25 Sep 2013 04:02 PM PDT

Hi EveryoneI hope everyone is having a nice day.This is my Code[code="sql"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SearchBiography] @firstname varchar(50), @middlename varchar(50), @lastname varchar(50), @sex varchar(50), @status varchar(50), @sexID int, @statusID intASBEGIN SET NOCOUNT ON; DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max) SET @SqlQuery = '' IF LEN(@sex) > 0 SET @SqlQuerySex = ' AND sex like ''%' + @sex + '%''' ELSE SET @SqlQuerySex = '' IF LEN(@status) > 0 SET @SqlQueryStatus = ' AND status like ''%' + @status + '%''' ELSE SET @SqlQueryStatus = '' IF LEN(@firstname) > 0 SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstName = '' IF LEN(@middlename) > 0 SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddleName = '' IF LEN(@lastname) > 0 SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastName = '' SET @SqlQuery = 'SELECT * FROM TestMyView WHERE sexID = ' + convert(varchar(20), @sexID) + ' AND statusID = ' + convert(varchar(20), @statusID) SET @SqlQuery = @SqlQuery + @SqlQuerySex + @SqlQueryStatus + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName EXEC (@SqlQuery) /* Should i need a parameter here??? */ please tell me :-( PRINT(@SqlQuery)END[/code]
Posted by at No comments:

[Articles] Scripting

[Articles] Scripting

Scripting

Posted: 25 Sep 2013 11:00 PM PDT

Scripting will become a more important skill in the future, for a variety of reasons.
Posted by at No comments:

[SQL 2012] Performance issues SQL 2012

[SQL 2012] Performance issues SQL 2012

Performance issues SQL 2012

Posted: 26 Sep 2013 12:22 AM PDT

Hi,Everyday I truncate a table of roughly 40 millions rows, populate it and re-create the indexes. If I restart the SQL server just before, the process take a bit more then 1 hour. After a week, the same process takes 2hours. I can't explain why the time of execution is increasing that much.I think this it the creation of the indexes that takes more time.Any idea where I can look at?Thanks

AlwaysOn performance on Replica databases

Posted: 26 Sep 2013 02:08 AM PDT

I am looking into AlwaysOn Availabilty Groups as both an HA and load spreading solution. But there is something I would like to clarify before I present to management. Our production databases suffer from lots of blocking and deadlocks. Will this resource contention be "replicated" on the replica databases when the logs are applied? I'd like to offload many of the read operations (adhoc reports, etc.) to the replicas but don't just want to end up in the same situation as I am in now.Thanks for any help.Tomp.s. - work is underway to address deadlocking, but it won't be fixed any time soon :(

Using cdc LSN for SSIS multiple table export without database snapshot

Posted: 26 Sep 2013 01:24 AM PDT

Hi,I have a range of tables enabled with CDC. At a set point I want to export all the net changes from these tables.I am using SSIS, and the first thing I need to do is decide on the start LSN and the end LSN.This is easy if I have a database snapshot. However, say that I do not.How do I ensure that I select the start LSN for each of the 5 tables so that the data exported fromall of these 5 tables reconcile back to each other like a snapshot in time?Do I record the time when the SSIS process is kicked off, and then use this time for each of the 5 table exports, to find the LSN? Would this remove any risk of the 5 tables being inconsistent?The start LSN is simpler since it will be the previous run's end LSN.Thanks

multiple conditions

Posted: 25 Sep 2013 03:59 AM PDT

hi, idk if I'll give to explain very well.but I need to make a query with the following conditionsexample:I have three fields are the parameters for my querythe fact is that when one of the parameters not leave blank or empty I need you always return the information and if not that if I put the three fields always return the information.thnk

SSRS Number - Custom Format Issue

Posted: 25 Sep 2013 09:17 PM PDT

Hi Please find the attached snapshot for SSRS Custom Number format issue.I am using as Number format :- #,##0.00%;(#,##0.00%)Font Format :- IIF(ReportItems!txtBox4.Value < 0 "Red" , "Black")But the result is coming with 0.00% as 3 type Black, Red(Without bracket) and Red(with bracket)Can you please let me know how #,##0.00%;(#,##0.00%) work ?

Query Issue

Posted: 25 Sep 2013 06:56 AM PDT

DECLARE @County NVARCHAR(100)DECLARE @Longitude decimal(19,15) = -87.979529DECLARE @Latitude decimal(19,15) = 42.219469DECLARE @SQLQuery AS NVARCHAR(500)DECLARE @LongitudeLatitude geographySET @SQLQuery = N'SET @LongitudeLatitude = geography::Parse(''POINT(' + cast(@Longitude as varchar) + ' ' + cast(@Latitude as varchar) + ')'')'-- SET @LongitudeLatitude = geography::Parse('POINT(-87.979529000000000 42.219469000000000)')print @SQLQuery exec sp_executesql @SQLQueryGives me Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@LongitudeLatitude".What could be the issue?

Replication Migration problem

Posted: 25 Sep 2013 07:36 AM PDT

I have a 2008R2 environment that I want to migrate to 2012 for replication. The production servers will remain 2008R2 and I want to replicate using a new 2012 distributor to a new 2012 destination. I have over 30 replication jobs to migrate and want to minimize downtime. My original thought was to add new publications to my existing production servers that would point to the new distribution database. From everything I have been able to find out, that is not an option. So, first question, does anyone know of a way to to this? This would be ideal for the new replication.Failing that, what is the best way to migrate (with least downtime and best possibility for rollback) from the old replication to the new. My original solution was to leave the old replication untouched until I could verify that the new replication was working properly. If I can't have a publisher that points at more than one distributor, then I don't see how I can do that.This seems like the only thing is to script out all the existing publications /subscriptions, modify them to point to the new servers with new logins etc. Drop all the old publications /subscriptions en masse and recreate them via the script pointing at the new environment. This seems like a fairly high risk maneuver, with little room for mistake or rollback.Any ideas? I'm sure I'm not the first to do this.Thanks,Dave Bennett

Best way to configure jobs on AlwaysON

Posted: 25 Sep 2013 04:34 AM PDT

Hi,We are planning to configure AlwaysON with 3 servers. Two in the same datacenter with synchronous commit and automatic failover and the third one in a different datacenter as a DR with asynchronous commit. Now what is the best way to configure the SQL jobs like Reindexing, DBCC Checkdb, Update Statistics and other application related jobs. The reindexing, update statistics and Checkdb should only be run on the Primary and should we keep them enabled on the secondary also.
Posted by at No comments:

[T-SQL] How does Greater than operator works in Joins

[T-SQL] How does Greater than operator works in Joins

How does Greater than operator works in Joins

Posted: 25 Sep 2013 02:39 PM PDT

Hello,I have writing SQL Queries for sometime now and writing inner joins, but always used the equal to operator(=) for joining 2 tables. Just recently I came across a SQL written by my co worker and he is uisng greater than and equal to operator (>=) to join tables. How in the earth you can join 2 tables with this operator(>=). I always thought of joining 2 tables if they have a matching value in the columns and using the equal operator to do that.He is doing some kind of a rolling average and using this (>=) operator.can someone please explain, how exactly this (>=) operator works in joins.Thanks.

how to replace this cursor with set based solution?

Posted: 25 Sep 2013 08:05 AM PDT

I've got a cursor script that I want to replace with a set based solution because the cursor script takes a long time to run. All I've got is the cursor script, for which I've reverse engineered the DDL to solve this problem (without success). The cursor script is the actual script that is running in a production environment, and the DDL is something I've created (for myself) to work out a set based solution. I've posted it all below and I would so much appreciate if you took a look. The cursor creates an array variable to hold a list of names (LabC1, LabC2, etc) and then fetches the name one by one and compares its value to a value found in the 'name' column of the Machines table. I hope a copy of the script and DDL will make this explanation clearer. Here's the cursor script: [code="plain"]DECLARE @benches TABLE (BenchName varchar(5))INSERT INTO @benchesVALUES ('LabC1'), ('LabC2'), ('LabC3'), ('LabC4'), ('LabD1'), ('LabD2'), ('LabD3'), ('LabD4'), ('LabE1'), ('LabE2'), ('LabE3'), ('LabE4'), ('LabE5'), ('LabE6'), ('LabE7'), ('LabE8') DECLARE @runsByBench TABLE (BenchName varchar(5), NumberOfRunsOnBench int, LastRun datetime) -- for each value in the @benches array return the count of runs and the last createdate associated with that run. DECLARE toQuery CURSOR LOCAL FAST_FORWARD FOR SELECT b.BenchName FROM @benches b OPEN toQuery -- loop through all of the scripts DECLARE @bench varchar(5) FETCH NEXT FROM toQuery INTO @bench WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @runsByBench (BenchName , NumberOfRunsOnBench , LastRun ) SELECT @bench as BenchName, COUNT(*) as NumberOfRunsOnBench, MAX(CreateDate) as LastRun FROM (select r.runid, r.creatorid, r.createdate, r.name, r.enddate, count(*) as MachinesInRun from Runs r join runs_machines rm on rm.runid=r.runid join Machines m on m.machineid=rm.machineid-----HERE'S WHERE IT COMPARES THE VALUE M.NAME TO THE VALUE FETCHED BY THE CURSOR-------------- where m.name like (@bench +'[0-9][0-9][0-9]')---------------------------------------------------------------------------------------------------------- and r.createdate > dateadd(M,-3,getdate()) group by r.runid, r.creatorid, r.createdate, r.name, r.enddate ) as foo FETCH NEXT FROM toQuery INTO @bench END CLOSE toQuery DEALLOCATE toQuery SELECT * FROM @runsByBench[/code]and here's the DDL I created for the tables used by the SQL script to work on the problem:[code="plain"]create table Runs (RunID int,CreatorID int,CreateDate datetime,EndDate datetime,Name varchar(20),MachineID int);insert into Runsvalues(1, 201, DATEADD(mi,-3,getdate()-1), DATEADD(mi,+60,getdate()-1), 'LABC1521', 521),(2, 202, DATEADD(mi,-3,getdate()), DATEADD(mi,+75,getdate()), 'LABC2681', 681),(3, 203, DATEADD(mi,-3,getdate()), DATEADD(mi,+63,getdate()), 'LABC3123', 123),(4, 204, DATEADD(mi,-3,getdate()), DATEADD(mi,+65,getdate()), 'LABC4765', 765),(5, 205, DATEADD(mi,-1,getdate()), DATEADD(mi,+78,getdate()), 'LABC1521', 521);create table Machines (MachineID int,Name varchar(20));insert into Machinesvalues(521, 'LABC1521'),(681, 'LABC2681'),(123, 'LABC3123'),(765, 'LABC4765');create table Runs_Machines(Runid int,MachineID varchar(20));insert into Runs_Machinesvalues(1, 521),(2, 681),(3, 123),(4, 765),(5, 521);create table RunsByBench(Benchname varchar(20),NumberOfRunsOnBench int,LastRun datetime);[/code]the query inside the script will work against the upper DDL as long as the you hardcode the m.name value:select r.runid, r.creatorid, r.createdate, r.name, r.enddatefrom Runs rjoin runs_machines rm on rm.runid=r.runidjoin Machines m on m.machineid=rm.machineidwhere m.name like 'LabC1%'I would like to put the array into a #temp table and compare the results of the query to it. But I have failed. I tried a correlated subquery, but without a unique key, I discovered it isn't the solution. Can someone help me find the T-SQL equivalent for the above cursor script?Thanks!

Find a character in string

Posted: 25 Sep 2013 09:18 AM PDT

Hi,Is there a sql function that will look for a character within a string and return a 1 or 0 if this character is showing?For example if the below sql select statement has a chratcer of '@' i want it to show a result of 1?[code="other"]Select 'AGDJS@JDJD' --will show as 1Select 'AGDJSJDJD' --will show as 0[/code]I just want to know if there is a current sql function that does this or if anyone can think of a case statement to get round this.I don't want a User Defined Function if possible.Thanks

select truncates varchar(max) column(s)

Posted: 25 Sep 2013 06:13 AM PDT

Hi all,SqlServer 2008R2.Anyone of you ever dealt with an issue when you're trying to concatenate several columns (including varchar(max)), the result is truncated?I am going to provide an example of it. Sorry for the long text.[code="sql"]--drop table A;CREATE TABLE A( [col_1] [nvarchar](30) NOT NULL, [col_2] [varchar](256) NULL, [col_3] [varchar](max) NULL, [col_4] [varchar](max) NULL);insert into A (col_1, col_2, col_3, col_4)values('AAA-111' ,'Vendor' ,' The duties and responsibilities of a Database Administrator (DBA) make a long and dynamically changing list, ranging from offering query tuning advice, to cutting stored procedures, all the way through to system process design and implementation for high availability. A DBA''s tasks, from day to day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error or disaster. This means that if a database, for whatever reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA''s responsibility to restore that database to the state it was in before the problem occurred, or as close as is possible. Of course, this doesn''t mean that a DBA is required to restore a database each and every day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless of when or why it occurs. If a DBA isn''t prepared, and significant data is lost or databases become unavailable to end-users for long periods of time, then that DBA probably won''t be in their job for too long. This is why a good, and tested, SQL Server backup and restore plan must be at the top of every administrative DBA''s list of tasks. ' ,' Each of these factors will help decide the types of backup required, how often they need to be taken, how many days'' worth of backup files need to be stored locally, and so on. All of this should be clearly documented so that all parties, both the DBAs and application/ database owners, understand the level of service that is expected for each database, and what''s required in the plan to achieve it. At one end of the scale, for a non-frontline, infrequently-modified database, the backup and recovery scheme may be simplicity itself, involving a nightly full database backup, containing a complete copy of all data files, which can be restored if and when necessary. At the opposite end of the scale, a financial database with more or less zero tolerance to data loss will require a complex scheme consisting of regular (daily) full database backups, probably interspersed with differential database backups, capturing all changes since the last full database backup, as well as very regular transaction log backups, capturing the contents added in the database log file, since the last log backup. For very large databases (VLDBs), where it may not be possible to back up the entire database in one go, the backup and restore scheme may become more complex still, involving backup of individual data files, for filegroups, as well as transaction logs. All of these backups will need to be carefully planned and scheduled, the files stored securely, and then restored in the correct sequence, to allow the database to be restored to the exact state in which it existed at any point in time in its history, such as the point just before a disaster occurred. ');[/code]When I run the following, all columns contain the right data.[code]select * from A;[/code]However, when I try to concat the fields, the output is truncated[code]select col_1 ,'Alert: Blah, blah, blah ....' ,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) + CASE col_2 WHEN 'Vendor' THEN 'Not our fault' ELSE 'Our fault' END + CHAR(13)+CHAR(10) +'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10) +'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10) +'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10) +'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10) +'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_2 + CHAR(13)+CHAR(10) +'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_3 + CHAR(13)+CHAR(10) ,'' ,0 ,''from A;[/code]Anyone?Thanks,

Moving all Jobs on a server

Posted: 25 Sep 2013 06:01 AM PDT

Is there a way to get a SQL Script, from my old server, that has all of its jobs? So I can then run the SQL on the new server and not have to manually re-create all of my jobs?

Locks when using Service broker

Posted: 25 Sep 2013 04:38 AM PDT

I am using Service broker to execute a stored procedure. In the stored procedure 3 tables are frequently used to insert/update and delete. The problem I am facing is when I use service broker to run the procedure there are too many locks on these tables.Has anyone faced a similar issue ? How should I go about troubleshooting it ?

Top 50 based on Sum

Posted: 25 Sep 2013 01:41 AM PDT

Hi all based on my query below, how do I select just 50 based on a Sum of duration matching the criteria defined?SELECT Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Reason, ReasonReq, Section, Waittime, Weekend, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Operator_Name IS NOT NULL) AND (Operator_Name <> 'None') AND (Docket_Category IS NOT NULL) AND (Contract = 1) AND (Docket_Category NOT LIKE '%Out Of SLA%')Thanks

cursor question

Posted: 16 Sep 2013 06:14 AM PDT

Hi guys,Can i load a cursor from a procedure?Like DECLARE cursor_importedPatients CURSOR FOR EXEC procedureThanks!
Posted by at No comments:

[SQL Server 2008 issues] Commenting

[SQL Server 2008 issues] Commenting

Commenting

Posted: 25 Sep 2013 06:30 PM PDT

How much detail should be used in comments, do you assume a level of understanding? One comment I saw once was "If you need comments to understand this - you shouldn't be reading it" – extreme to one side, on the other hand, do you really need a comment for [code="sql"]Insert into table_aSelect b.col1,c.col1,b.col2,c.col2from tableb b inner join tablec c on . .. . . [/code]The reason for asking - I am writing documentation for a few inherited databases, to me the current level of commenting is fine, it give an overview of what the stored procedure does, and if there is anything that seems outside of the overview is commented.

Sql Agent

Posted: 25 Sep 2013 01:53 AM PDT

Hi Guys,Can someone please point me in the correct direction, been searching in vain for a number of days.I'm monitoring sql agent jobs with a mixture of SCOM & notifier alerts with a job, howeverencountering problems where a few jobs complete successfully.However within the steps there have been errors, I've queried various agent historytable alas no errors are reported.How can I extract information within a successful scheduled job?Cheers

Shrink the database only upto its initial size which is set

Posted: 02 Sep 2013 05:21 PM PDT

I have some database which are already created which are already created with default initial size. But i want to reset the initial size of these. I did it using SSMS.But now if i shrink the database it will shrink beyond the initial size i have set.It will shrink till the initial size it had during creation of database. But i don't want it. I was expecting to shrink till the initial size which i have set.So please suggest me how it can be done?

SQL With Browser Disabled

Posted: 25 Sep 2013 12:07 AM PDT

I am sure I have had this working before, but how can I connect to SQL Server remotely, using the instance name (With instance name not hidden), but with the browser disabled? It works with Servername, port number. But not Server\instance.browser enabled is lets me connect with server\instance. I know reading on the internet it says browser must be enabled for this but I am sure ive done it with this disabled!

2008 SQL replication

Posted: 25 Sep 2013 02:48 AM PDT

I have a 2008 SQL server i need to replicate for a reporting server on a Nightly basis. I had the replication working originally but had to break it for a software upgrade. The DB has grown in size from 40 GB to 150 GB ( a lot of imports .When i tried to re-enable snapshot replication the Publisher takes around 40 Minutes the subscriber I let run for over 12 hrs and it never completed. Currently i am doing a manual backup From the publisher and then a manual restoration to the subscriber. When the DB is compressed the backup is 28 GB and transfers to the subscriber in less then 5 minutes backup. Would it be better to do a transnational replication ? Is there ways to have SQL only update the tables that changed ? When all of our imports are done i expect the Db to be approximately 250 GB

String Split

Posted: 17 Sep 2013 09:29 PM PDT

I want to write query which procures me result like below.General rule is if any word starts from number then it should split as word.Input-----Ørstedsvej 7BVolvo 25D104ND NokiaResult------Ørstedsvej 7 BVolvo 25 D104 ND NokiaOr the simplest way to do.

Importing from a improperly formatted excel file

Posted: 25 Sep 2013 06:39 AM PDT

Any guidance would be appreciated. Despite my protestations I have been tasked to import from an excel file. The file is not what I would call a proper data file. The data will start in cell D10 and end in cell Kx where x is the number of data rows in the file.What are my options here. My first thought would be to use SSIS and dump say cell A1 through K10000 ( assuming I would never have more than 10000 rows) into a staging table and parse the data out.Anyone been down this road...?

Multi instance SQL 2008 R2 upgrade

Posted: 25 Sep 2013 01:59 PM PDT

I have 3 SQL 2008 R2 SP1 instances on a server. And i want to upgrade only 1 instance to SP2. I use an automated installation code in a batch file as below which cannot be changed now.R2SP2setup.exe /IAcceptSLServerLicenseTerms="TRUE" /quiet /allinstancesFor the other 2 instances which i don't want to patch with SP2, if i keep their SQL services in stopped and disabled state, will it work and upgrade only the one i require. Please let me know if there is any other option. I cannot change the /allinstances parameter in the above code as it is already in Production.

merging different clients data onto same box

Posted: 25 Sep 2013 07:22 AM PDT

Hi.Our current infrastructure is set up whereby we have 20 different companies using the service we provide, each having their own server with sql server 2008r2 installed. Each database server has the same main 3 databases installed all with the same table structure across the estate. Some of the servers are being well under utilised so we are looking at consolidating say 2 clients onto 1 server but we need to keep them totally separate for security/data protection.I am thinking that the best way to do this is by installing multiple instances on each server, so an instance A for company A , and instance B for company B.Can anybody who has done this before or knows about it, please advise me of the pro's and cons of doing it and any 'gottchas'We use transactional replication as well if this adds any extra info for your reasoning.

Replication Latency Issues. Comprehending data in MSdistribution_History

Posted: 25 Sep 2013 05:57 AM PDT

Hello, Can anyone please help us understand the MSdistribution_History table better. Here is our problem and what we understood so far: Randomly in the night, we are getting alerted on replication latency issues. By the time, we open up our computers and remote in, latency issues are gone.We are trying to understand MSdistribution_History for that particular Publication and we are observing weird thing in it. I will explain what we are observing it first.our latency window: 3:34AM - 3:39AMwe see a bump in delivered_transactions and its associated delivered_commands and obviously there is and will be current delivery latency for that many number of transactions.However, if you can see in the attached xl, there is a bump in number of transactions @3:19AM(which was not the cultprit for the alert. Our latency threshold is 5 min) and the alerts started coming in @3:34AM.From 3:34AM until 3:50AM, we got alerted for latency. In the attached table, we have high latency rate but, no transactions/commands associated with it.So my question here is, when the synch happened fine until 3:24AM(given the bump in no.of transactions/commands) why are we getting alerts @3:34AM until 3:50AM? hope I explained it clearly.Note:*Rev columns are the derived columns - converted the cumulative readings into per session readingsPlease shed some light at your earliest convenience.ThanksJagan K

Select TOP 1 Runs forever

Posted: 25 Sep 2013 05:37 AM PDT

I have 2 identical tables that we just recently deleted about 15M rows on 2 separate databases.After the delete I rebuilt the index on both tables:ALTER INDEX REBUILDTables both show fragmentation (from the following query):[code="sql"]DECLARE @db_id SMALLINT;DECLARE @object_id INT;SET @db_id = DB_ID(N'<DBNAME>');SET @object_id = OBJECT_ID(N'<TABLENAME>');IF @object_id IS NULL BEGIN; PRINT N'Invalid object';END;ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');END;GO[/code]Table 1:[code]database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count20 1285579618 0 1 HEAP IN_ROW_DATA 1 0 13.9545707492285 41736 53.1668343875791 2218971 0.136384976525819 4540 0 0 4901 4957 4917.541 0[/code]Table 2:[code]database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count17 1285579618 0 1 HEAP IN_ROW_DATA 1 0 16.138381869376 41280 45.3369670542636 1871510 0.820434890042006 24858 0 0 4902 4971 4920.613 0[/code]The query:[code="sql"]SELECT TOP 1 * FROM <TABLE>[/code]Runs for almost 15 minutes in Table 1 and 3 seconds in Table 2.I can fix the issue if I drop Table 1 and reload it but can anyone point me somewhere else to figure out why this was happening?Both tables have approx. 370 columns on them...I realize you can't reproduce the data, I'm just looking for general ideas.Thanks

format decimal in modify() XML method

Posted: 25 Sep 2013 04:26 AM PDT

Hi, In my XML file, the following format is being generated: 0It was to be generated with this format: 0.00I'm using it:declare @mydecimal decimal(12,2) = 0.00;...SET @myXml.modify('insert <tag1 value1=''{sql:variable("@mydecimal")}''/>');...<tag1 value1="0"> -- as is showing... <tag1 value1="0.00"> -- as it should be!I've used convert, but it did not work ..Thanks,JoseAcJR

Error message when changing location of share drive in SSIS package

Posted: 18 Sep 2013 03:56 AM PDT

Hi all,At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS package delivering the data in a flat file.This week i had to change the location of the sharedrive in the package, after doing the changes the package is not working and the following message appears (see below), does anyone knows how to solve this one?Executed as user: (Database Name)\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4279.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:46:26 AM Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [111] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (111) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [16] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (16) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.44 Code: 0xC020200E Source: Data Flow Task Flat File Destination [166] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.44 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (166) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:46:26 AM Finished: 9:46:27 AM Elapsed: 0.672 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,0,(Database Name),Aurum - Backup to share,(Job outcome),,The job failed. The Job was invoked by Schedule 14 (daily). The last step to run was step 1 (*****).,00:00:00,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,1,(Database Name),Aurum - Backup to share,Aurum Share Backup,,Best regards,Daniel

Mapping inserted / source identity values

Posted: 14 Sep 2013 05:16 AM PDT

Hi,I have an interesting problem that i haven't been able to find a good solution for.What i want to do is to make an insert from table source into destination and find out the mapping between the source and destination table rows.The problem is that I cannot match the non-identity values on each other because they're no uniqueness between them. In real world, it's actually adress logging tables.Here are the sample script:[code="sql"]CREATE TABLE [source] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [destination] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [mapping] (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column-- Import some sample data...INSERT INTO [source] (some_value)SELECT TOP 30 nameFROM sysobjects-- Destination has some data alreadyINSERT INTO [destination] (some_value)SELECT TOP 30 nameFROM sysobjects--Here, i want to transfer everything from source into destination, but be able to keep a mapping on the two tables:-- I try to use OUTPUT clause, but i cannot refer to columns outside of the ones being inserted:INSERT INTO [destination] (some_value)--OUTPUT inserted.i, s.i INTO [mapping] (i_new, i_old) -- s.i doesn't work!!SELECT some_valueFROM [source] s[/code]I cannot change table definition, and i'd rather not mess with SET IDENTITY_INSERT ON either...Does someone have an idea?

Best way to mass delete partitioned data?

Posted: 25 Sep 2013 04:26 AM PDT

We have a > 40 TB data warehouse that once resided on a single SQL 2008 Enterprise instance which was "mostly" migrated to a three node SQL 2012 Enterpise failover cluster.The largest tables are partitioned, and we use a relatively simple partitioning key on a datetime2 column, which is also the clustering key. Only the 2012 and 2013 data are "live" on the new failover cluster. 2012 and older data also still resides on the single instance 2008 server. SAN storage is getting skinny and we need to delete the 2012 data on the single instance and reclaim the "double dipped" space.So my question is, what is the best approach to mass delete a large quantity of partitioned data? My target is only the 2012 data, which should reside on very specific filegroups. We used only 1 file per filegroup, for example with a very boring but descriptive logical name like '20120901File'.Can this be as simple as simply updating the partitioning scheme and dropping the 2012 file groups? Or if I do this will I be causing some insidious corruption?Do I need to take a more conservative approach and do batch row deletions in something like a while loop (to enforce say 1000 row batches) while also switched to bulk logged recovery model to avoid trashing the transaction log?Is there a way to get clever with partition switching? I have done sliding windows in the past, but this isn't quite what I'm after.Ballpark estimates are deleting on the order of tens of billions of rows, and 10 - 20 TB of data, so you can see the appeal of being able to quickly drop the floor out from under a bunch of data by simply nuking an entire filegroup. Just not sure if that is feasible and won't have other undesirable consequences.As a last thought, I believe our setup has indexes aligned.Cheers,Jared

Bulk Insert - CSV with and without quotes

Posted: 25 Sep 2013 03:39 AM PDT

HelloI need to Bulk Insert a .csv file into an SQL table. The problem is within the .csv file some fields have quotes some have commas within the fields. Does anyone have a solution or a workaround of how I can handle this?.csv file as follows:[code="plain"]"1 High Street","London","United Kingdom""1,High Street","London","United Kingdom""1,High Street",,United Kingdom[/code]SQL Code as follows:[code="plain"]BULK INSERT test FROM 'c:\test\test.csv' WITH (FIELDTERMINATOR=',',ROWTERMINATOR = '')select * from test[/code]You should be able to see all the issues I am experiencing. I tried importing with the quotes specified but due to not all fields having these present it imported incorrectly again.Any ideas?Thanks

SSIS Use a conditional statement on a file name?

Posted: 25 Sep 2013 02:26 AM PDT

I have a file that comes from a vendor who can't seem to spell. The file has a Prefix of D,E, or P. 95% of the time they put the 'D' ( or others) at the beginning of the file names so in my ForLoop.Collection.Files: box I have d*.txt, but every now and then, some muckity-muck puts the prefix on the end instead and the gates of heck come crashing down.I want to test the file name somehow, or maybe put an -OR- in that Files: box. Any ideas?Bueller?Thanks in advance.Crusty.

Point in time restore Not from latest .BAK file

Posted: 24 Sep 2013 11:03 PM PDT

Lets say I need to restore a db to from BACKUP A to TRANSLOG BACKUP Z.I have a script to restore and include the .trn files up to Backup Z. This works fine and can be done via the GUI.Lets take this scenario as an example. This is my issue.FULL Backup ATrans Backup aTrans Backup bTrans Backup cTrans Backup dFULL Backup B _Corruption FoundTrans Backup eTrans Backup fTrans Backup gTrans Backup hFULL Backup C – Corruption FoundTrans Backup iTrans Backup jTrans Backup kTrans Backup ZI need to restore up to 'TRANS BACKUP Z', However my script now breaks at the time that Full Backup B was created. This breaks my backup chain. How do I get around this and get it to restore FULL BACKUP A – TRANS BACKUP Z.The script I am using does have all the relevant TRN files listed to be loaded and are in the correct order. Why does the backup break the chain? Not a live issue but preparing for unfixable db corruption. To eliminate a lengthy downtime.

Need Tool to FTP Files to Secured SFTP Site

Posted: 25 Sep 2013 01:54 AM PDT

I'm looking for a 3rd party tool that I can execute from my SSIS package to FTP files to a secured ftp site. Because I will have many files to send from several folders, it needs to be able to accept a parameter containing the file path each time it is called.I have WinSCP but I'm wondering if there is a better tool, one that I can pass a filename into. I appreciate any ideas.Thanks, Jack

Replication-Distribution Database already exists problem

Posted: 25 Sep 2013 12:32 AM PDT

Hi All,When i complete the replication wizard it will try to run through, but the following error message appears:[i]Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\distribution.MDF' because it already exists. Change the file path or the file name, and retry the operation.CREATE DATABASE failed. Some file names listed could not be created. Check related errors.Changed database context to 'master'. (Microsoft SQL Server, Error: 5170)[/i]I have checked in the system database folder and the Distribution database isnt there. However, the mdf and ldf files reside in the following location:C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. Question 1. Can i delete these two files?Question 2. will the replicaiton wizard create a new mdf and ldf?thanks in advance guys!

CTE

Posted: 24 Sep 2013 09:19 PM PDT

Is it possible to call procedure in CTE?
Posted by at 3 comments:

Wednesday, September 25, 2013

[SQL Server] sleeping connection limit

[SQL Server] sleeping connection limit

sleeping connection limit

Posted: 25 Sep 2013 09:20 AM PDT

One of my SQL Servers has 15,835 connected session with only 4 active. One application account has 15500+ sleeping connections with last batch date on most of them going back almost a month.My other SQL Servers will see between a few and a few hundred sessions. Is 15,000 excessive? Could it be due to some app pool setting? Does SQL Server have an option to release connections that are idle for a period of time?Thanks!Howard

Creating a CASE login within a CASE logic

Posted: 25 Sep 2013 12:57 AM PDT

I have the following code which seems to accumulate the complete file - rather than the upper record set -When OperationPhase = 2 and ResCat = 'MC' - I want to sum it.SUM( CASE OperationPhase WHEN 2 THEN (CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpMC,SUM( CASE OperationPhase WHEN 3 THEN (CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalRunMC,SUM( CASE OperationPhase WHEN 2 THEN (CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpPERS,SUM( CASE OperationPhase WHEN 3 THEN (CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalRunPERS,

how to script an conditional update

Posted: 25 Sep 2013 12:05 AM PDT

Hi all clever scripters out there!I am going to make a one time update after having made a new function.I have one table[dbo].[savedduty]( [NAME] [varchar](20) NULL, [STARTTIME] [int] NULL, [DAYS] [int] NULL, [ENDTIME] [int] NULL, [DAEKBEMAND] [tinyint] NULL, [EMPLOYEEGROUPID] [int] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, [Selected_Record] [tinyint] NULL, [Name_Day_2] [char](20) NULL, [InstitutionsId] [int] NULL, [Color] [int] NULL, [Color_R] [tinyint] NULL, [Color_B] [tinyint] NULL, [Color_G] [tinyint] NULL,The important part here is [NAME] and [ID], which is the two components i should use to update this table:[dbo].[dutyrostershift]( [DATO] [datetime] NULL, [STD] [tinyint] NULL, [SPECIALVAGT] [tinyint] NULL, [DAEKBEMAND] [tinyint] NULL, [EXTRATIMER] [real] NULL, [OVERARBTIMER] [real] NULL, [MANUEL] [tinyint] NULL, [BESKYTTET] [tinyint] NULL, [CONFIRMED] [tinyint] NULL, [VACATIONTYPE] [varchar](50) NULL, [BREAKSWISH] [tinyint] NULL, [DUTYROSTERID] [int] NULL, [EMPLOYEEID] [int] NULL, [EMPLOYEEGROUPID] [int] NULL, [CHILDFORCAREDAYID] [int] NULL, [ORIGINATINGSTAFFINGREQUIREMENTID] [int] NULL, [SHIFTTYPE] [int] NULL, [FROMTIME] [int] NULL, [TOTIME] [int] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, [LeaveType_Id] [int] NULL, [LoginID] [int] NULL, [StatusNo] [int] NULL, [Time_Stamp] [datetime] NULL, [Comment] [char](120) NULL, [Is_Free_sat] [tinyint] NULL, [Is_Center_Opening] [tinyint] NULL, [is_fo_day] [tinyint] NULL, [SavedDuty_Id] [int] NULL,The rule is Pretty simple:If dbo.dutyrostershift.vacationtype = dbo.savedduty.name then dbo.dutyrostershift.savedduty_id = dbo.savedduty.id.i.e. if vacationtype exists in name from savedduty, update saveddutyroster_id with ID from saveddutyBut, how to script this?Best regardsEdvard Korsbæk
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog