Tuesday, October 1, 2013

[how to] How to arrange rows selected in MYSQL query?

[how to] How to arrange rows selected in MYSQL query?

How to arrange rows selected in MYSQL query?

Posted: 01 Oct 2013 08:46 PM PDT

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) .

I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A where PK_ONE in (2,88,3999,4,282,33399,1,394);

When I'm using dataset mentioned above in PHP cursor; I want to ensure that data fetched comes in the sequence of values of "PK_ONE" in WHERE clause.

Can you please give me some direction to achieve same? [Currently rows are coming in asending order]

Looking for JET Blue (Extensible Storage Engine - ESE) administration tool

Posted: 01 Oct 2013 06:44 PM PDT

I am looking for database administration/viewer/editor tool for JET Blue / ESE databases - basically, any database using esent.dll. Something similar to HeidiSQL for MySQL. I would expect Google would return quick results especially as it is built into Windows but I could not find any tool for that.

Can anyone recommend any (free or open source if possible) tool for that? Perhaps some of the other tools that support other database formats have an ESE "driver". Thanks in advance.

Query with PIVOT fails with second LEFT OUTER JOIN to same table

Posted: 01 Oct 2013 06:02 PM PDT

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm not sure why.

It appears to me that the PIVOT does not like two left outer joins to the same table.

HERE is a SQL Fiddle Schema Build for reference I tried to create a minimal sample for this question, the SQL statement below will not run in SQL Fiddle however so I dont know how much help this is.

Here is the current query that works as expected.

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;  Set @startno = 1;  Set @endno = 104;        WITH DateRange(dt) AS          (              SELECT CONVERT(int, @startno) dt              UNION ALL              SELECT dt+1 FROM DateRange WHERE dt < @endno          )  SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE               WHEN dt <= 52                  THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3))))                  ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3))))          END)) FROM DateRange          FOR XML PATH(''), TYPE          ).value('.', 'NVARCHAR(MAX)')       ,1,1,'')      OPTION (maxrecursion 0);      set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, ABALPH as VendorName, IBBUYR as BuyerNo,  ' + @cols + '  FROM      (      SELECT LIITM, IBVEND, ABALPH, IBBUYR, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo       FROM F41021        LEFT OUTER JOIN F4111          ON F41021.LIITM = F4111.ILITM         LEFT OUTER JOIN F4102      ON F41021.LIITM = F4102.IBITM AND F41021.LIMCU = F4102.IBMCU         LEFT OUTER JOIN vwF0101      ON F4102.IBVEND = vwF0101.ABAN8         ) d  PIVOT  (      SUM(ILTRQT)      for WeekNo in (' + @cols + ')  ) piv  GROUP BY LIITM, IBVEND, ABALPH, IBBUYR,   '+ @cols + ''    execute(@query)

Sample output is along the lines of this (heavily trimmed for readability):

ShortItemNumber   |   VendorNo   |   VendorName        |   BuyerNo   |   2012-Week1   |   2012-Week2   |   2012-Week3  -----------------------------------------------------------------------------------------------------------------------  140               |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80049             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80071             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80072             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL

The second query, I add a second join to the vwF0101 table to retrieve another alpha name for a userId (IBBUYR).

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;  Set @startno = 1;  Set @endno = 104;        WITH DateRange(dt) AS          (              SELECT CONVERT(int, @startno) dt              UNION ALL              SELECT dt+1 FROM DateRange WHERE dt < @endno          )  SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE               WHEN dt <= 52                  THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3))))                  ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3))))          END)) FROM DateRange          FOR XML PATH(''), TYPE          ).value('.', 'NVARCHAR(MAX)')       ,1,1,'')      OPTION (maxrecursion 0);      set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, vendor.ABALPH as VendorName, IBBUYR as BuyerNo,  buyer.ABALPH, ' + @cols + '  FROM      (      SELECT LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo       FROM JDE_Production.PRODDTA.F41021        LEFT OUTER JOIN JDE_Production.PRODDTA.F4111          ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4111.ILITM         LEFT OUTER JOIN JDE_Production.PRODDTA.F4102      ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4102.IBITM AND JDE_Production.PRODDTA.F41021.LIMCU = JDE_Production.PRODDTA.F4102.IBMCU         LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as vendor      ON JDE_Production.PRODDTA.F4102.IBVEND = vendor.ABAN8         LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as buyer      ON JDE_Production.PRODDTA.F4102.IBBUYR = buyer.ABAN8        ) d  PIVOT  (      SUM(ILTRQT)      for WeekNo in (' + @cols + ')  ) piv  GROUP BY LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH,  '+ @cols + ''    execute(@query)

These are the error messages I received. It seems to me the PIVOT doesn't like me referencing the ABALPH column twice, even though I have it aliased separately. 

Msg 8156, Level 16, State 1, Line 20  The column 'ABALPH' was specified multiple times for 'd'.  Msg 8156, Level 16, State 1, Line 24  The column 'ABALPH' was specified multiple times for 'piv'.  Msg 4104, Level 16, State 1, Line 25  The multi-part identifier "vendor.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 25  The multi-part identifier "buyer.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 1  The multi-part identifier "vendor.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 1  The multi-part identifier "buyer.ABALPH" could not be bound.

Why do I have hundreds of connections open

Posted: 01 Oct 2013 04:44 PM PDT

I just had a transient issue with a development server. So this isn't urgent.

I had a "successful" restore that wasn't

So as part of checking I ran sp_who and found 307 open connections 2 minutes later when I ran it again there were 77 open connections

But what I saw causing the connections was that there were 3 connections open from the SQL Server host using the NT Authority account to every single database on the server.

I've never seen that before, and having spent 10 minutes playing with Google I can't see anything that looks like an explanation, so I was wondering if anyone knew what was going on.

How do I determine the licensing cost of Oracle Standard Edition for HyperV instances? [on hold]

Posted: 01 Oct 2013 02:02 PM PDT

I'm confused by Oracle's licensing for their Standard Edition.

My understanding is that licensing for the Standard Edition is determined by the number or physical processors (actual chips, not cores) available in the host machine. For example if I have a host box with 8 processors and I have 4 virtual machines running on it, Oracle's documentation says that each of those processors counts toward the license total regardless of how many are allocated to the virtual machine running the database.

The part that confuses me is that Standard Edition is limited to 4 processors. Does this mean that I'd have to pay for 4 processor licenses, 8 processor licenses, or not be allowed to install Oracle Standard on such a box because it has too many processors?

MDX: How do I combine/merge/union two Measures dimensions with calculated members?

Posted: 01 Oct 2013 12:54 PM PDT

I am trying to combine two dimensions in an MDX query. 

SELECT   NON EMPTY  (      [XXX].[YYY].children,  ) ON ROWS,  NON EMPTY  {      [Measures].[Dimension1A],      [Measures].[Dimension1B],      [Measures].[Dimension2A],      [Measures].[Dimension2B]  } ON COLUMNS  FROM [RePro]  WHERE  (      [ABC].[ABC].[Val]  ) CELL PROPERTIES VALUE

This produces results where all the [Measures] dimensions are in separate columns. How can I make it so that [Dimension1A] and [Dimension1B] are combined/merged into the same column, and likewise for [Dimension2A] and [Dimension2B]?

I've tried the solution here, but I get '#Error' in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimensions". Any ideas?

Thanks :)

Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

Posted: 01 Oct 2013 02:45 PM PDT

When I execute the two queries below, 

SELECT       session_id, transaction_id      FROM sys.dm_tran_session_transactions;

AND

SELECT       session_id, request_id, at.transaction_id      FROM sys.dm_tran_active_transactions at          JOIN sys.dm_exec_requests r              ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.

How to use column name 'use' in SQL Server [on hold]

Posted: 01 Oct 2013 01:52 PM PDT

I have a database that I migrated from MySQL to SQL Server. One of the table's column names is use. How can I write my insert statement to not throw an error because of it?

I'm getting the error:

Msg 156, Level 15, State 1, Line 4830
Incorrect syntax near the keyword 'use'.

My insert looks something like:

insert into foo (id, use, some_other_attrs)   values(1, fun, other_stuff);

Joining on a field with different values

Posted: 01 Oct 2013 06:22 PM PDT

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule program gives times as BREAK1, BREAK2, BREAK3, and LUNCH, while the tracking program simply lists them as Lunch and Break. I can join the data and get the lunches just fine, but the breaks are throwing me off. If I convert BREAK1, BREAK2, and BREAK3 to just "Break", I end up with too many segments because it is matching every instance with every other instance. Is there a way that anyone can think of to join these two pieces of information?

Here is some sample data:

This is the Scheduled Times:

EMP_ID  NOM_DATE    SEG_CODE    START_MOMENT    STOP_MOMENT  626009  26-Sep-13   BREAK2          9/26/13 5:00 PM 9/26/13 5:15 PM  625650  26-Sep-13   BREAK2          9/26/13 4:30 PM 9/26/13 4:45 PM  638815  26-Sep-13   BREAK2          9/26/13 4:00 PM 9/26/13 4:15 PM  621649  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM  567005  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM  626009  26-Sep-13   LUNCH           9/26/13 2:30 PM 9/26/13 3:30 PM  625650  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM  638815  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM  621649  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM  567005  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM  626009  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM  625650  26-Sep-13   BREAK1         9/26/13 11:30 AM 9/26/13 11:45 AM  638815  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM  621649  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM  567005  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM

This is the Actual Times

EMP_ID  Seg_Code    Start_Time  Stop_Time  625650  Break           9/26/2013 17:54 9/26/2013 17:55  567005  Break           9/26/2013 14:56 9/26/2013 14:59  567005  Break           9/26/2013 15:32 9/26/2013 15:44  638815  Break           9/26/2013 16:34 9/26/2013 16:47  567005  Break           9/26/2013 10:08 9/26/2013 10:21  626009  Break           9/26/2013 17:01 9/26/2013 17:15  625650  Break           9/26/2013 11:31 9/26/2013 11:45  626009  Break           9/26/2013 11:52 9/26/2013 12:07  621649  Break           9/26/2013 9:34  9/26/2013 9:48  621649  Break           9/26/2013 15:31 9/26/2013 15:45  638815  Break           9/26/2013 11:46 9/26/2013 12:02  625650  Break           9/26/2013 16:35 9/26/2013 16:51  567005  Lunch           9/26/2013 12:31 9/26/2013 13:29  625650  Lunch           9/26/2013 13:31 9/26/2013 14:30  626009  Lunch           9/26/2013 14:31 9/26/2013 15:30  638815  Lunch           9/26/2013 13:31 9/26/2013 14:30  621649  Lunch           9/26/2013 12:31 9/26/2013 13:30

I am trying to get the difference (in minutes) between when they are scheduled, and when they are actually taking breaks. A correct example is:

Badge   Seg_Code    Scheduled Start     Scheduled Stop      Actual Start           Actual Stop      Difference      Seg_Duration  192329  Lunch       9/26/13 8:15 AM     9/26/13 9:15 AM     9/26/2013 8:18:27 AM    9/26/2013 9:17:59 AM        3       0:00:59:32

Thank you

Restrict range of dynamic ports available to SQL Server

Posted: 01 Oct 2013 11:33 AM PDT

Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?

If a transaction is "committed" , is it then saved for sure?

Posted: 01 Oct 2013 12:13 PM PDT

If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ?

Today our log files had reached the limit of the drive and we got a lot of errors. Furthermore, some other services crashed. We increased the disk and restarted the server.

During startup, the server did a "database recovery" -- can I be sure that everything is okay again ?

Trigger and temp table or materialized view

Posted: 01 Oct 2013 11:01 AM PDT

I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know. Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much. Which is better for big tables, with million(s) rows please? Thank you all.

Is MSDTC required for SQL Server 2012 Fail Over Cluster?

Posted: 01 Oct 2013 11:21 AM PDT

I am making a 2 node SQL Server 2012 Fail Over Cluster, do I need to install MSDTC also?
If yes then can both be installed on single shared disk?

Optimize IN clause in where query with order by - MySQL

Posted: 01 Oct 2013 11:41 AM PDT

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` WHERE user_id =9898 AND status IN (1,3,4) order by id limit 30;

Here is the result of explain, as you can see the query is filesort

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra  1   SIMPLE  test    range   user_id     user_id     8   NULL    3   Using where; Using index; Using filesort

Here is my table structure

CREATE TABLE IF NOT EXISTS `test` (    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `user_id` int(10) unsigned NOT NULL,    `status` int(3) unsigned NOT NULL,    PRIMARY KEY (`id`),    KEY `user_id` (`user_id`,`status`)  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;    --  -- Dumping data for table `test`  --    INSERT INTO `test` (`id`, `user_id`, `status`) VALUES  (5, 9797, 2),  (6, 9797, 3),  (4, 9898, 0),  (1, 9898, 2),  (2, 9898, 3),  (3, 9898, 4);

How can I optimize the query? In my real table I can see the following information in error log: # Query_time: 26.498180 Lock_time: 0.000175 Rows_sent: 100 Rows_examined: 4926

Issue moving database between servers (cannot find .frm file even though it's there)

Posted: 01 Oct 2013 09:15 AM PDT

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great.

The name of my database is roster and it has one table called users. I copied the 'roster' folder which contains the following files:

db.opt  users.frm  users.MYD  users.MYI

I put the 'roster' folder in the mysql directory. I run the following:

<?php  $db = mysql_connect("localhost","username","password") or die ('Fail message1');  mysql_select_db("roster") or die("Fail message2");  ?>

No error. Awesome. But when I run the following:

$query = "SELECT last_name, first_name, u_name, skype_id, primary_location FROM users";    $result = mysql_query($query)or die('query error'.mysql_error());

Then I receive the following error:

query errorCan't find file: './roster/users.frm' (errno: 13)

Furthermore, when I login to the server and login to mysql. I see the database and table:

mysql> show databases;  +--------------------+  | Database           |  +--------------------+  | information_schema |  | mysql              |  | phones_db          |  | roster             |  +--------------------+  4 rows in set (0.00 sec)    mysql> use roster;  Reading table information for completion of table and column names  You can turn off this feature to get a quicker startup with -A    Database changed    mysql> show tables;  +------------------+  | Tables_in_roster |  +------------------+  | users            |  +------------------+  1 row in set (0.00 sec)    mysql> select * from users;  ERROR 1017 (HY000): Can't find file: './roster/users.frm' (errno: 13)  mysql>

I checked the 'roster' folder and it contains the four files mentioned at the beginning of this post. The file is where it supposed to be, right? Did I move the database wrong? What gives? Any ideas?

Microsoft SQL server 2012 error 18456 with domain credentials

Posted: 01 Oct 2013 08:53 AM PDT

I have a SQL server 2012 setup to use windows authentication.

Remotely I can connect to "Server A" no problem with domain credentials but when I try to locally connect to "Server A" with SSMS it errors out to say that there is a login failure.

I have all the correct permissions set I beleive any other suggestions?

How to set identity_insert to on in SQL Server

Posted: 01 Oct 2013 01:52 PM PDT

EDIT: See answer here: http://stackoverflow.com/questions/19119303/how-to-set-identity-insert-to-on-in-sqlserver

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on;

but I get the following error:

Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS  -Databases  --System Databases  --mySchema

Does anyone know a solution to this problem? Thanks.

Double queue reader or is it still busy?

Posted: 01 Oct 2013 08:45 AM PDT

I came across this line in the verbose log on replication QueueReader Agent job startup.

The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent.' when connecting to 'distribution' on 'Svr01\Inst01'. Ensure that the publication and subscription are defined properly and that both servers are running.

The first clue I had that something was wrong was the vague replication message:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Replication IS working but Replication Monitor is showing red X's. This second error has shown up from time to time and sometimes goes away. For a while now I though this was a bug as it has been reported a few times on Connect.

Assuming the first quoted error is correct, how can I find the double QueueReader or previous request?

tasklist /FI "imagename eq qreader.exe"

Returns nothing.

SELECT job_id, name, enabled FROM msdb..sysjobs WHERE category_id = 19 ORDER BY date_created;

Returns 1 row on each distributor.

Setup:

  1. Svr01 = Publisher, Distributor, SQL 2005
  2. Svr02 = Publisher, Distributor, SQL 2005, Subscribor (Svr01)
  3. Svr03 = SQL 2005, Subscribor (Svr01, Svr02)

Both Svr01 and Svr02 are reporting these errors.

Resources:

DBA.Stackexchange search results

MSDB forum

Oracle: Tables and Views missing from the all_objects dictionary

Posted: 01 Oct 2013 01:17 PM PDT

I have a couple of database instances that are having the curious issue where tables and views do not show up in the all_objects dictionary which is causing some issue with certain schema viewing applications. Not all are missing, but there is only like a dozen of the 130 or so tables/views that are in the schema. If you query all_tables or all_views, they all show up just fine. If you select/update/delete with those tables, it works fine.

Any one know of what types of things I should be looking at that would cause this?

BTW, I am not the DB administrator to this DB, I'm just trying to figure this out for our client. I don't have full access to the DB.

put number inside database sqle server in arabic format I do not want it in english format I need it in arabic [on hold]

Posted: 01 Oct 2013 02:49 PM PDT

I do not want the number in English format I need it in Arabic

I am sorry my problem I inserted number in my table in English(e.g 5),but I want to replace it in Arabic format. because I need it in Arabic to return it in text box in my windows form . hope you get what I want thanks

Performance setting for MyISAM tables - my.cnf file

Posted: 01 Oct 2013 09:02 AM PDT

I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) .

QUESTIONS

  • Since the databases size is smaller than the available memory, how can I make full usage of my server's resources?
  • What settings should I attempt configure in my.cnf?
  • Can I force data + indexes to stay in memory?

Possibilities to speed up InnoDB INSERTs and UPDATEs

Posted: 01 Oct 2013 09:41 AM PDT

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower.

I'm monitoring when, and it turns out it's usually when a bot is hitting my site (googlebot for instance always gets the same session assigned, so each hit will require an UDPATE on the session table).

I presume google hits me so hard that mysql can't keep up with updating the same record. Usually an update takes a few mills, but I've seen it reach a full second. My table btw has never reached more than 7000 records.

Knowing that I have only 1 disk at my disposal, but still some free memory to spare, I was wondering what would be the best possibilities to enhance the UPDATE performance on this particular table (I'm not experiencing any issues on other tables as it's much less write intensive).

why do I have three copies of data/log files per database on same server instance

Posted: 01 Oct 2013 06:23 PM PDT

OS: Windows 7 Professional

Database: SQL Server 2008 R2 Standard Edition

Just detected by SpaceMonger that, each of my databases on test instance has three copies of data files (.mdf) and log files (.ldf).

they are located at three folders:

  • ..\DataFiles\Test
  • ..\LogFiles\Test
  • ..\Microsoft SQL Server\Mssql10_50.test\Mssql\Data

and they have exactly same file size and timestamp.

I confirmed that I have no mirroring setup for any of my databases, neither do I have any special setup to enforce the database to have files splitted.

The only suspicious setting I can find is:

  • the first two locations are the server level database default location (right click server in the MSSMS -> Properties -> Database Settings -> Database default locations): the first is for Data while the second is for Log,

  • and the last is each database's file location appearing in the MSSMS (right click a database -> Properties -> Files -> Database Files)

This annoying problem has made my server available disk space very low and forced me to do housekeeping very often.

any idea why does this happen?

any methodology i can follow to find out what has been wrong?

-- EDIT ---

Thank you all for the comments and reply.

We have identified the problem - not related to the sql database setting, but the folders themselves: the three folders have a setup to mirror each other. we have got our network team to investigate why it's so.

How can I get my linked server working using Windows authentication?

Posted: 01 Oct 2013 04:24 PM PDT

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

Posted: 01 Oct 2013 05:24 PM PDT

I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition.

My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008.

The error message I get when trying to login is

Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452)

I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy).

Any help would be appreciated.

Inserting Records - How to get next incremented PK for each insert

Posted: 01 Oct 2013 01:54 PM PDT

I'm using spoon to look for Devices that have several attributes that define its location, and then create a Cabinet in that location if one doesn't exist. Is there an operation that lets me get a cabinet.id value that is 1 greater than the highest currently in the table before each row is inserted.

Here's some pseudo code for what I want to do:

  • Find Devices with location data AND not with cabinet_id
  • Create a Cabinet record in the location of the first Device record returned, using the next Cabinet.id
  • Get the next Device, ensure a Cabinet was not created during this operation matching this Devices position, create a Cabinet record with the next Cabinet.id

Thanks for any guidance.

Mysql DB server hits 400% CPU

Posted: 01 Oct 2013 01:42 PM PDT

I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top. 

 - load average 40 to 50   - CPU % - 400%    - idle % - 45%   - wait % - 11%   - vmstat procs r-> 14 and b-> 5

And then drains down within 5 minutes. And when I check the show processlist I see queries for DML and SQL are halted for some minutes. And it processes very slowly. Whereas each query are indexed appropriately and there will be no delay most of the time it returns less than 1 second for any query that are being executed to server the application.

  • Mysql Version : 5.0.77
  • OS : CentOS 5.4
  • Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)
  • Database Size: 450 GB
  • 16 Processor & 4 cores
  • Not in per-table model.
  • TPS ranges 50 to 200.
  • Master to a Slave of the same configuration and seconds behind is 0.

Below url shows show innodb status \G and show open tables; at the time spike. And this reduced within 5 minutes. Sometimes rare scenarios like once in two months I see the processes takes more than 5 to 8 hours to drain normal. All time I notice the load processor utilization and how it gradually splits its task and keep monitoring the process and innodb status and IO status. I need not do anything to bring it down. It servers the applications promptly and after some time it drains down to normal. Can you find anything suspicious in the url if any locks or OS waits any suggestion to initially triage with or what could have caused such spikes ?

http://tinyurl.com/bm5v4pl -> "show innodb status \G and show open tables at DB spikes."

Also there are some concerns that I would like to share with you.

  1. Recently I have seen a table that gets inserts only about 60 per second. It predominantly locks for a while waiting for auto-inc to get released. And thus subsequent inserts stays in the processlist tray. After a while the table gets IN_USE of about 30 threads and later I don't know what it makes to free them and clears the tray. (At this duration the load goes more than 15 for 5 minutes)

  2. Suppose if you say application functionality should be shapped to best suite the DB server to react. There are 3 to 5 functionalities each are independent entities in schema wise. Whenever I see the locks it gets affected to all other schemas too.

  3. Now what makes best fuzzy is the last one. I see slave keeps in synch with master with a delay of 0 second all time whereas slave has a single thread SQL operation that is passed from relay IO that which acts in FIFO model from the binary logs where Master had generated. When this single headed slave can keep the load less and have the operations upto-date, should the concurrent hits are really made to be concurrent for the functionalities which I assume making the possible IO locks in OS level. Can this be organized in application itself and keep the concurrent tenure density thinner?

Multiple database servers for performance vs failover

Posted: 01 Oct 2013 07:24 PM PDT

If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?

Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active?

I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.'

If it matters, we have traffic around 3,000-7,000 simultaneous users.

Best way to delete very large recordset in Oracle

Posted: 01 Oct 2013 12:22 PM PDT

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store.

Every month we are required to purge records from the two of the main tables. The criteria for the purge varies and is a combination of row age and a couple of status fields. We typically end up purging between 10 and 50 million rows per month (we add about 3-5 million rows a week via imports).

Currently we have to do this delete in batches of about 50,000 rows (ie. delete 50000, comit, delete 50000, commit, repeat). Attempting to delete the entire batch all at one time makes the database unresponsive for about an hour (depending on the # of rows). Deleting the rows in batches like this is very rough on the system and we typically have to do it "as time permits" over the course of a week; allowing the script to run continuously can result in the a performance degradation that is unacceptable to the user.

I believe that this kind of batch deleting also degrades index performance and has other impacts that eventually cause the performance of the database to degrade. There are 34 indexes on just one table, and the index data size is actually larger than the data itself.

Here is the script that one of our IT people uses to do this purge:

BEGIN  LOOP    delete FROM tbl_raw     where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;      exit when SQL%rowcount < 49999;      commit;    END LOOP;    commit;    END;

This database must be up 99.99999% and we've only got a 2 day maintenance window once a year.

I'm looking for a better method for removing these records, but I've yet to find any. Any suggestions?
Posted by at No comments:

[Articles] The Need for Tape

[Articles] The Need for Tape

The Need for Tape

Posted: 30 Sep 2013 11:00 PM PDT

Are tape systems obsolete? A recent incident has Steve Jones thinking perhaps not.
Posted by at No comments:

[MS SQL Server] Backuping Stored procs of the Database by using scripts

[MS SQL Server] Backuping Stored procs of the Database by using scripts

Backuping Stored procs of the Database by using scripts

Posted: 30 Sep 2013 05:34 AM PDT

hi all,i want to create a script to backup the stored procs of the database plz suggest.

Restoring my StandBy / ReadOnly Db - HELP PLEASE

Posted: 30 Sep 2013 08:46 PM PDT

Hey guys... i really need some help... I'm new as SQL-DBA, Our LogShipping DB is out of sync (since the windows-team patched the server)We have a primary DB running on a server with logshippingA Secondary DB running on an other server which is in standby / readonly mode. (wich copys and restores the .trn logs from primary. this worked fine until the serverteam patched the backup server (and didnt start the sql-server again) Now my problem... how can i restore the standby db ? I made a fullbackup from the primary today (log shipping is not working for 4 days)--- my plan was to resotre the standby db with the fullback from the primary and after this (the trn from today are still here) it should resync itself... But i'm not able (using SQL Server Manager) to use the "Tasks/Restore/Database" (greyd out) to restore my fullback.now i read something about a command:Restore Database (dbname) from disk='C:\Temp\Fullbackup.bak" with standby = 'xxxxx.ldf'What is this .ldf path for ? i found a .ldf from the Database (secondary) on the path: F:\Microsoft SQL Server\MSSQL\Data\"ourDB-Name".ldf ... Is it ok when i restore my BackupDB (in standby mode) with the following command ? Restore Database OURDBNAME from disk='C:\Temp\Fullbackup.bak" with standby = 'F:\Microsoft SQL Server\MSSQL\Data\"ourDB-Name".ldf' ... i dont wanna break our secondary db :( --> or do i have to create a new .ldf file ? Thanks a lot for your help

SQL Server Service pack upgrade

Posted: 30 Sep 2013 07:27 AM PDT

Hello,I'm trying to upgrade the service packs of all SQL Server (2008,2008 R2 )instances in our environment.I would like to upgrade it through a batch file instead of the GUI. In our environment we use cmd or batch file for sql installation but i'm not sure how to create a batch file for service pack upgrades.Did anyone got a chance to work on this ? Please let me know your suggesstions or thoughts.Fyi, i have the service pack .exe file , i'm looking for a template batch file ...once i'm ready for the upgrade i just want to update the instance name in the batch file.Please let me know if you need more informationThanks in advance
Posted by at No comments:

[SQL 2012] Unable to shrink Database

[SQL 2012] Unable to shrink Database

Unable to shrink Database

Posted: 30 Sep 2013 11:54 PM PDT

I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB. Unallocated space available in this database of around 130 GB. Please see below stats of this database: database_name database_size unallocated space--------------- --------------- ------------------Manager 174929.31 MB 131038.92 MBreserved data index_size unused ------------- ------------ ------------- ----------44329360 KB 38557904 KB 5342496 KB 428960 KBI have tried to shrink this database from GUI & through command (DBCC SHRINKFILE), but unable to do so. Unallocated space is remain the same.I have tried following ways to get this DB shrink:1) Defrag all the indexes then ran DBCC SHRINKFILE2) Trunate T-Log file then ran DBCC SHRINKFILE3) Manually ran Ghost cleanup stored procedure then ran DBCC SHRINKFILEbut finally the Output of DBCC CHECKALLOC is still same: The total number of extents = 740590, used pages = 5808614, and reserved pages = 5923760 in this database.I have tried all possible way but DB is not getting shrink. Can any one help me on this issue?

SQL Server Merge - Pull replication error

Posted: 30 Sep 2013 09:41 PM PDT

I have a configuration problem to create a Merge/Pull replication between two databases SQLServer 2012.The machine Publisher is a Windows Server 2008 R2 not in windows domain, this Server is also the Distributor. The machine Subscriber is a Windows 8 not in windows domain.I have made the following settings: in the Published Server i created the distributor's folder, "repldata" and I also shared it and suser "everyone full control". The path's folder is \servername\repldatasee image: [img]http://www.alessandrosenesi.com/download/distributor.jpg[/img]I created Publisher, see image: [img]http://www.alessandrosenesi.com/download/Publisher.jpg [/img]I created Subscriber see image: [img]http://www.alessandrosenesi.com/download/subscriber.jpg[/img]I have done some settings in Distributor, Publisher and Subscriber but I don't get results, I always have access error.see image: [img]http://www.alessandrosenesi.com/download/log.jpg[/img]-----------------------------------------------------------------------------------Error messages:The schema script 'BOOKING_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)The process could not read file '\omemacchina\repldata\uncomemacchina$MSSQLSER VER2012_nomereplica\20130930125944\BOOKING_2.sch' due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)Access Denied.(Source: MSSQL_REPL, Error number: MSSQL_REPL5)-----------------------------------------------------------------------------------Can you tell me where is the problem? I have to do this replication but it looks like to be not possibleThanks in advanceAlessandro

Traces question

Posted: 30 Sep 2013 08:49 PM PDT

Hi,I'm running a server trace to determine where the procedures take more time to execute.I'm tracking events 10, 12, 43 and 45.My procedures call lots of other procedures but my main question is: shouldn't the sum of the time taken inside a procedure (ObjectType = 'procedure name') be the same as the time taken by it's execution (TextData LIKE 'EXEC procedure name')?The procedure proc0 calls proc1 and the sum of proc1 takes 0,23ms (event 45 and objectype = 'proc1'). but event 43 of proc1 takes 5ms... Is the difference from SQL "loading" and parsing proc1? proc1 is already in cache so no time to do that should be taken...Why does this happen?Thanks,Pedro

could not understand what the error is

Posted: 30 Sep 2013 05:20 AM PDT

Hi guys,I got an error message like this....i could not figure out what made it to occur, do any one have any idea???---------------------------------------------------Error Type - System.Data.SqlClient.SqlExceptionError Source - .Net SqlClient Data ProviderError Details - System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'IncidentAttachment', database 'OHLEGRMS_LOB', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) at OTECH.Framework.DataProvider.SQLDataProvider.ExecuteDataSet(String spName, Object[] parameters, Int32& totalrecords) at OTECH.Entity.Framework.IEntity.SelectAllFor(IDBCommand command, Int32& totalrecords) at Ohleg.WebUI.Incident.IncidentAttachments.LoadAttachments() at Ohleg.WebUI.BasePage.OnLoad(EventArgs e) at Ohleg.WebUI.Incident.IncidentBasePage.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)ClientConnectionId:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxSERVERNAME9/30/2013 1:46:11 PMMicrosoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null/LM/W3SVC/4/ROOT-1-130249518647548018522IIS APPPOOL\RMS2013 ProdNumber of Session Variables:5PropertyRequired2069872 = TrueVehicleRequired2069872 = FalseIncidentType2069872 = 1SubmissionRequired2069872 = TrueLastError = System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'IncidentAttachment', database 'OHLEGRMS_LOB', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) at OTECH.Framework.DataProvider.SQLDataProvider.ExecuteDataSet(String spName, Object[] parameters, Int32& totalrecords) at OTECH.Entity.Framework.IEntity.SelectAllFor(IDBCommand command, Int32& totalrecords) at Ohleg.WebUI.Incident.IncidentAttachments.LoadAttachments() at Ohleg.WebUI.BasePage.OnLoad(EventArgs e) at Ohleg.WebUI.Incident.IncidentBasePage.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)---------------------------------------------------------------------------:hehe:
Posted by at No comments:

[T-SQL] get a specific time window across the night between two days

[T-SQL] get a specific time window across the night between two days

get a specific time window across the night between two days

Posted: 30 Sep 2013 10:05 AM PDT

Hi allI need some help me to get me in the right direction.I have collected some sql stats into a tables across a week.I want to see those stats at the time a nightly job run. so from day1 at 20pm to day2 at 11am.day2 at 20pm to day3 at 11amday3 at 20pm to day 4 at 11amand so on...so of the entire week i am only intrested to see that time window. What SQL query allow me to do that? I can use between day1 for time more than 20pm and day7 until 11am? this way i get all other timewindows i don't want.many thanks

Displaying nvarchar datatype column having a date value

Posted: 30 Sep 2013 02:58 PM PDT

Please help me in displaying a nvarchar datatype column called 'txtValue' having date value in the format '15 Jul 2013' in my SSRS report

SQL Agent Question

Posted: 30 Sep 2013 01:33 PM PDT

Hi Guys,I have Virtual Machine that I installed SQL Dev Edition. Here I need SQL Agent So I can Schedule my SSIS Packages.Please correct me if I am wrong. FYI I am talking VM (Virtual Machine) not a Server. If my VM on sleep mode or I log off, my scheduleis not gonna run or If I loged off or VM is in Sleep Mood Agent is running find as per Schedule time, is it true?If not, then how i can use this VM for what mention above? any thought or advise would be really appreciated. Thanks.

trying to write a case statement for datetime stamp - some trouble

Posted: 30 Sep 2013 09:29 AM PDT

Hi. I'm working on creating a histogram using time, as the set of 6 buckets that data can fall into. ie. something can happen between Midnight and 4am, 4am and 8am, 8am and 12pm, and so on.The column I have to break into these buckets is a datetime column.First, I checked on how to extract the time from the datetimeconvert(char(5), GETDATE(), 108)Returns this format. 15:22<--GoodNext I am trying to create the case statement. See below. SQL doesn't like the Between 12:00 and 04:00. select convert(char(5), GETDATE(), 108),case convert(char(5), GETDATE(), 108) between 12:01 and 04:00 then MidnightToFourelse restOfTimeend as timeframeError message:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'between'.How do I frame the condition so that Times falling between midnight and 4am are assigned "MidnighttoFour" value in the TimeFrame column?

Compare delimited data - same data but in a different order

Posted: 30 Sep 2013 02:26 AM PDT

Hello All, I am currently working on comparing two versions of data for a comparison report. I have a delimited string of characters and I am trying to figure out if the data is the same but in a different order, I should not mark the record as different. I eliminated all data with different lengths. Edited...with data creation scriptsThe approach I am using is to split the values using a cross apply to a "Split by delimiter function" into a new table and doing left outer join on part number between the CURRENT AND PREVIOUS record sets and looking for where records are missing in the second table/record set. For example: -- Original DataCREATE TABLE #Table_Orig (PartNumber int, FILEVERSION varchar(100), VALUE varchar(100))INSERT INTO #Table_Orig VALUES (1234, 'CURRENT', 'A B')INSERT INTO #Table_Orig VALUES (1234, 'PREVIOUS', 'B A')INSERT INTO #Table_Orig VALUES (456, 'CURRENT', 'A B')INSERT INTO #Table_Orig VALUES (456, 'PREVIOUS', 'A D')-- Split Table - Once run through a splitter functionCREATE TABLE #Table_A (PartNumber int, FILEVERSION varchar(100), SplitVALUE varchar(100))INSERT INTO #Table_A VALUES (1234, 'CURRENT', 'A')INSERT INTO #Table_A VALUES (1234, 'CURRENT', 'B')INSERT INTO #Table_A VALUES (1234, 'PREVIOUS', 'B')INSERT INTO #Table_A VALUES (1234, 'PREVIOUS', 'A')INSERT INTO #Table_A VALUES (456, 'CURRENT', 'A')INSERT INTO #Table_A VALUES (456, 'CURRENT', 'B')INSERT INTO #Table_A VALUES (456, 'PREVIOUS', 'A')INSERT INTO #Table_A VALUES (456, 'PREVIOUS', 'D')-- Queryselect *from #Table_A prevLEFT OUTER JOIN #Table_A curr on prev.PartNumber = curr.PartNumber and curr.FileVersion = 'CURRENT' AND prev.SplitValue = curr.SplitValueWHERE prev.FileVersion = 'PREVIOUS'AND curr.PartNumber IS NULL-- Expected OutputPartNumber: 456SplitValue: DThis seems to work fine, but is a bit cumbersome. I did some searching on Stackoverflow and here with no real matches. Does anyone have any other "better" suggestions? Thanks, Anton

Using Dynamic SQL to build temp table...doesn't work?

Posted: 30 Sep 2013 03:24 AM PDT

I am trying to pass a column name as a parameter to build a #temp table combining data from two tables. When I exec the dynamic query it seems to build the temp table because I am receiving a message saying 2048 rows affected; however, I can't select from it, I am receiving an error saying that #TempLevels is an invalid object. Is this possible to do? What am I missing? Thank you in advance![code="sql"]declare @ColumnLevel as varchar(10);declare @ReportLevel as varchar(10);Set @ReportLevel = '2' set @ColumnLevel = case when @ReportLevel = '2' then 'level2' when @ReportLevel = '3' then 'level3' when @ReportLevel = '4' then 'level4' end print @ColumnLevel declare @sql as varchar(1000)set @sql = 'select * into #TempLevels from tblLevels as a join tblReports as b on a.'+ColumnLevel+' = b.ReportLevel' exec(@sql)select * from #TempLevelsdrop table #TempLevels[/code]

T-SQL and BCP to qureyout to a file on database premissions

Posted: 30 Sep 2013 04:55 AM PDT

Ok I at an end, I'm close but no grasping at straws. In this set of statement I collect instance and database permissions, put them into temp files, the merge the two files into one select. I'm now trying to dump that select into an output file. The command shell is opened and then closed, there are print statements that will show you results at each state, and there are also two different attempts to dispatch the query to a file. Can some please look and tell me why when the T-SQL is executed I get the bcp help menu back.-- OPEN the command SHELL EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1GORECONFIGUREGO EXEC sp_configure 'show advanced options', 0GORECONFIGUREGO--Declare Variable needDECLARE @OutputFile NVARCHAR(100);DECLARE @FilePath NVARCHAR(100);DECLARE @bcpCommand NVARCHAR(1000);--Build Temp tablesCREATE TABLE #InstanceLevel( in_name nvarchar(128) not null, in_type_desc nvarchar(60), is_disabled int, create_date datetime, modify_date datetime, Default_database_name nvarchar(128), sysadmin int, securityadmin int, serveradmin int, setupadmin int, processadmin int, diskadmin int, dbcreator int, bulkadmin int);-- Table for database level premissionsCREATE TABLE #DBPremissions( DBName nvarchar(128) NOT NULL, MemberName nvarchar(128) NOT NULL, RoleName nvarchar(128) NOT NULL, DefaultSchema nvarchar (128), ServerLogin nvarchar(128) NOT NULL);-- Populate temp tablesINSERT INTO [#InstanceLevel] SELECT sp.name ,sp.type_desc ,is_disabled ,sp.create_date ,sp.modify_date ,sp.Default_database_name ,sl.sysadmin ,sl.securityadmin ,sl.serveradmin ,sl.setupadmin ,sl.processadmin ,sl.diskadmin ,sl.dbcreator ,sl.bulkadmin FROM sys.server_principals sp JOIN sys.syslogins sl ON sp.sid = sl.sid where sp.type in ('S','G','U');--populate database premissions tableINSERT INTO [#DBPremissions]EXEC sp_MSforeachdb 'use ? SELECT DB_NAME() as [Database name] ,MEM.name AS MemberName ,RL.name AS RoleName ,MEM.default_schema_name AS DefaultSchema ,SP.name AS ServerLogin FROM sys.database_role_members AS DRM INNER JOIN sys.database_principals AS RL ON DRM.role_principal_id = RL.principal_id INNER JOIN sys.database_principals AS MEM ON DRM.member_principal_id = MEM.principal_id INNER JOIN sys.server_principals AS SP ON MEM.[sid] = SP.[sid]';--- Test the REsults of the Insert/*SELECT * FROM #InstanceLevel order by in_name;SELECT * FROM #DBPremissions --where MemberName = 'A70ADOM\LS_WMSOPS' order by MemberName;SELECT il.* ,dbp.* FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName --WHERE dbp.MemberName ='A70ADOM\LS_WMSOPS' order by il.in_name;*/--Try1/*SET @bcpCommand = 'bcp "SELECT il.* ,dbp.* FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName order by il.in_name" queryout D:\test_it.txt -c -T -t' */--Try2SET @bcpCommand = 'bcp "SELECT il.In_name,il.in_type_desc,il.is_disabled,il.create_date,il.modify_date,il.Default_database_name,il.sysadmin,il.securityadmin,il.serveradmin,il.setupadmin,il.processadmin,il.diskadmin,il.dbcreator,il.bulkadmin,dbp.DBName,dbp.MemberName,dbp.RoleName,dbp.DefaultSchema,dbp.ServerLogin FROM #InstanceLevel as il JOIN #DBPremissions as dbp ON il.in_name = dbp.MemberName order by il.in_name" queryout D:\test_it.txt -c -t' PRINT 'The actual command ' ++ @bcpCommand;SET @FilePath = 'D:\'PRINT @FILEPATH;SET @OutputFile = 'test_it.txt'PRINT @OutputFile;--SET @bcpCommand = @bcpCommand + @OutputFile + '" -c -t","'PRINT ' The excuable comand ' ++ @BcpCommand;EXEC master..xp_cmdshell @bcpCommand--Clean up-- Temp Table Clean upDROP TABLE #InstanceLevel;DROP TABLE #DBPremissions;-- CLOSE the command SHELL EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 0GORECONFIGUREGO EXEC sp_configure 'show advanced options', 0GORECONFIGUREGO--------------------------------------------------------------------------------

Query Help

Posted: 30 Sep 2013 02:35 AM PDT

Hello.I have one table that looks something like this..3022224 G 4980 65 33022224 U 4980 596 23022224 G 4980 67 3And another that looks something like this..28077 1 302222428078 1 302222428079 1 3022224I need insert the following into another table based on the two tables above. The long number (3022224) is what I use to join the two original tables.28077 1 G 4980 65 328077 1 U 4980 596 228077 1 G 4980 67 328078 1 G 4980 65 328078 1 U 4980 596 228078 1 G 4980 67 328079 1 G 4980 65 328079 1 U 4980 596 228079 1 G 4980 67 3Any ideas would be much appreciated.Thank you,Keith

diffrence between nolock with braces and without braces

Posted: 03 Mar 2011 03:51 AM PST

Can someone explain me about the difference between nolock with braces and without bracesnolock (nolock)

FILEPROPERTY 'SpaceUsed' what it returns

Posted: 30 Sep 2013 01:17 AM PDT

Can anyone explain the behavior of the query below. I was trying to write a query that can be run on a SQL server that will return the database name, filegroup, logical filename and physical filename for every file on the SQL server with the size of each file and the space available within the file. the total space in for each file is returned correctly no matter which database the query is run from. The SpaceUsed values are only correct for the specific database that the query is run in.Is there a different way [different table] to get the "SpaceUsed" value from?Or does someone have a better way to get this group of information?Do you spot a mistake in my joins?Thank you for your help.SELECT d.name as DatabaseName, fg.groupname as FileGroupName, df.name as LogicalFileName,CASE df.type WHEN 0 then 'Data' ELSE 'Log' END AS UsageType,df.size/128.0 as SpaceInMB,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,(df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/1024 as AvailSpaceInGB,df.physical_nameFROM ((sys.sysdatabases d join sys.master_files df on d.dbid = df.database_id ) join sys.sysfiles f on df.file_id = f.fileid ) left outer join sys.sysfilegroups fg on f.groupid = fg.groupid ORDER by d.name, fg.groupname, df.file_id;
Posted by at No comments:

[SQL Server 2008 issues] deleting all data\logins\views

[SQL Server 2008 issues] deleting all data\logins\views

deleting all data\logins\views

Posted: 30 Sep 2013 02:55 AM PDT

What is the best way to remove all data/views/logons from a database ?The scenario is this: We have a database called 'X' to hold out client data and to interact with our in house applications. Each customer has their own server with database 'X' installed. The database on each of the servers is the same format, that is, it has the same table structures/stored procs.I have to create a new database 'X' on a new server for a new customer - so I have restored one of our current clients database 'X' backups to the new server and now have the database but obviously with data that i need to get rid of completely whilst keeping the table structures, constraints, seeding values (if any present ). Also this has restored incorrect users under the Security\Users folder of the database. Do i just manually delete these ?I have used the following to disable referential integrity, truncate tables, and then renebale referential integrity[code="sql"]- disable referential integrityEXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' GO - enable referential integrity again EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO[/code]If anybody can offer any advice of the above topic I would to hear from you ! :-)

Send SQL Server Dashboard reports via database mail

Posted: 13 Sep 2012 05:57 AM PDT

Hi All,Is this is possible to enable a job, which sends us the dashboard reports of a server thru mail ?Thanks in advance

SQL to list the headers in a table?

Posted: 22 Nov 2011 10:44 AM PST

Does anyone know offhand the SQL to list all the headers in a specified table?Thank you!

Error Message: BACKUP failed to complete the command BACKUP LOG msdb

Posted: 30 Sep 2013 02:20 AM PDT

Hi,I am getting following error message daily into my error log.I am running FULL Backup, Diff Backup an T-Log Backup.This message is every morning early I see and looks like during same time my T-log backup job also running.I have also checked that MSDB is in SIMPLE recovery mode and it's not part of my T-Log back up, it's only part of FULL BAckup - weekly.ERROR:BACKUP failed to complete the command BACKUP LOG msdb. Check the backup application log for detailed messages.

SSIS source file with time in the name

Posted: 26 Sep 2013 06:18 AM PDT

Hi everyone.I have been tasked with building some SSIS packages to load data from a csv into the database each day. There are four file sources. Three of them are very simple because the file name is source_date.csv. The file name is predictable so creating an expression that get the current day's file is easy. One of the files is source_date_time. Because the time stamp is always a few seconds different, I don't know how to create an expression to get the file by name and load it.What do people do when they need to have their package go out and grab the day's file if there is a time stamp in the name leading to an unpredictable file name?

Can you please let me know the link to download the tool named as - DBHammer?

Posted: 30 Sep 2013 06:08 AM PDT

Can you please let me know the link to download the tool named as - DBHammer?

Transfer Data

Posted: 30 Sep 2013 04:51 AM PDT

Howdy everybody,What is the best way to transfer 20GB in 3.122 km(three thousand kilometers) distance?1. Backup direct to target2. Backup local and copy to target3. Data Import/Export....what yours suggest??

How to DEFINE REGEX for CSV FILE

Posted: 30 Sep 2013 03:29 AM PDT

Im transfering data from csv file and it is fixed format and only date changes. so i want to split the date and name to endter into table20130930_Get_MY File Name.csvSo in table should enter like belowdate_name :20130930file name: Get_MY File Nameanyone have idea.regardsShuan..

How to run multiple stored procedures in parallel?

Posted: 30 Sep 2013 01:56 AM PDT

I have a stored procedure which runs in database DB1This stored procedure will have other stored procedures inside it which should run in parallel.Create procedure db1.proc1AS use db2call proc2use db3 call proc3 use db4 call proc4 Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?Thanks

Trouble with nested CTE

Posted: 30 Sep 2013 03:27 AM PDT

I need to create a query that is comprised of subqueries, I am using a CTE, but one of the subqueries also is using a CTE...Can you nest CTE like this??[code="plain"]WITH CTE_Results AS(SELECT CASE WHEN HISTORY >= 2 AND ELA >= 4 AND MATH >= 4 AND SCIENCE >= 3 AND FL >= 3 AND VA >= 1 AND Prep >= 0 THEN 'Yes' ELSE 'No' END AS [On Target?] FROM ( SELECT COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = 4068968) AS derivedUNION ALLWITH cteSource(CN, U1)AS ( SELECT r.CN, r.U1 FROM dbo.SSS AS s INNER JOIN dbo.STU AS t ON t.SN = s.SN INNER JOIN dbo.CRS AS r ON r.CN = s.CN WHERE t.ID = 4068968 UNION ALL SELECT r.CN, r.U1 FROM dbo.HIS AS i INNER JOIN dbo.CRS AS r ON r.CN = i.CN WHERE i.PID = 4068968)SELECT CASE WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' ELSE 'No' END AS [On Target?]FROM cteSource AS sPIVOT ( COUNT(s.CN) FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G]) ) AS p;SELECT CONVERT(VARCHAR(5),SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END)) + '/2'FROM CTE_Results[/code]

listing of months for a particular year

Posted: 26 Sep 2013 12:52 AM PDT

Dear All,Hope you are doing fine. What am I trying to do is get a stock count for each item per month for a particular year. I am trying to do a report on that.Table1: list all Purchase OrdersPoNum Date P001 2013-01-01 P002 2013-02-01 P003 2013-02-10P004 2013-03-01Table2: list items for each PoNumPoNum ItemRef QtyP001 I0001 10P001 I0002 5P002 I0003 15P003 I0003 20P004 I0003 5is it possible to have something like that?Year 2013Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec I000110 0 0 0 0 0 0 0 0 0 0 0I0025 0 0 0 0 0 0 0 0 0 0 0I0030 35 5 0 0 0 0 0 0 0 0 0Basically, I will need each item's qty per month for a year.I came up with something. But I have the item's qty for the month the items are in only. How do I get it 0 for the other month?I was reading on creating a CTE to list the months of the year. But I am stuck.Please advise.Thanks,Ashley

Passing external sproc params to internal variables - Explanation?

Posted: 30 Sep 2013 02:11 AM PDT

Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables and then using those internal variables instead of referencing the external parameters.Can anyone explain why this is happening, or point me to some microsoft technet article on the subject? I need to explain this to the rest of my team, but I don't know exactly what's going on. I just know of the problem and solution. hah!

NOdeA is crashed ..on ctive active cluster.

Posted: 30 Sep 2013 02:56 AM PDT

Hi,We have active active sql 2008 r2 running on Win 2008 r2 cluster.Instance_A is running on Node1 Instance_b is runing on NOde2Due to Os issue , NOdeA Crashed and Wintel Team want to re-install the O.S and cluster on NODEA only.While building the SQL server in initial stage we have followed the below way:For Instance A, we have ran the Main setup on NodeA and then added node on NOdeBFor Inctanse B, we have ran Main setup on NodeB and then Added Node on NodeANOw Node A ,is down both sql i running on Node B.If Node A is re-building then ,how to add this node to sql instance ?As per sql prospect how we have to do installation and what are steps we need to follow.Please help me. It is very urgentYour help much appreciated...Thank you in advance....

SQL Server Database Backup Monitoring

Posted: 29 Sep 2013 11:53 PM PDT

Hi SQL Masters,Greetings!Do you have a script that will determine if the backup failed/succeeded?Sample output:Database Name Status Log Date--------------- ---------- --------------AdventureWorks2008 FAILED 20130125AdventureWorks2008 SUCCEEDED 20130126Something like that.....Any ideas SQL Masters?Thank you and Best Regards,

NUMA

Posted: 26 Jun 2012 09:46 PM PDT

In preparation for my MCITP exam tomorrow I have been reading through the books and just crossing the t's and dotting the i's. One thing which I just want to get right in my head is NUMA as I had a feeling that I had the right answers then changed them to wrong answers so this is more of a confirmation than an actual question.1. SQL ignores NUMA when Hard-NUMA is <=4 CPUs and at least 1 node has only 1 CPU.So.....1 physical proc with 4 cores, then SQL will ignore NUMA2 physical procs with 2 cores, ignore4 physical procs with 1 core ignoreAnything other than the above then SQL will use Hard-NUMA as long as its not interleaved memory configured[i]This is the one mainly confusing me[/i]2. Use typical use for Soft-NUMA is when there is no Hard-NUMA, but can be used to split Hard-NUMA into more NUMA nodes3. Soft-NUMA doesnt provide memory to CPU affinity4. Soft-NUMA can increase performance in relation to I/O as each NUMA node creates a new I/O path and new LazyWriter thread5. Instead of doing point 4, you could CPU affinity instead to spread workload across multiple CPU's6. Use SSCM to configure port to NUMA affinityAnd now to cross my fingers and hope tomorrow is a good day.

Using CASE Condition for Prefixing a field from a table

Posted: 30 Sep 2013 12:38 AM PDT

Hi All,I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on no of times the employee has been rehired with CASE statementCASE WHEN LEFT(EMPLOYEECODE,1) = 'Z' THEN 'A'+EMPLOYEECODE ELSE CASE WHEN ISNUMERIC(LEFT(EMPLOYEECODE,1)) = 1 THEN 'A'+EMPLOYEECODE ELSE CHAR(ASCII(LEFT(EMPLOYEECODE,1))+1)+SUBSTRING(EMPLOYEECODE,2,99) END ENDand it is working fine with these parameters : Employee Code is 'A10010' then its returning B10010 and when it is 10010 it is returning A10010 which is correct but the challenge comes when the employee code is Z10010 then it should return AA10010 not AZ10010 ....how can i do that?? help

TSQL Recomile Problems when using subqueries

Posted: 29 Sep 2013 10:02 PM PDT

Good Day AllI have found a problem with a Simple TSQL script that is returning incorrect results due to a schema change that didn't cause the old plan to recompile.I will just post how to duplicate the problem and hopefully people can tell me if it is intended to function this way.You have an Instance of SQL, on that instance you have 2 Databases, TestDB1 and TestDB2.You have a table in each of these databases that are exactly the same (At the start).You have a query to select an ID that is in TestDB1 where the values exist in [TestDB2]. eg. Select AccountID from TestDB1.dbo.Table1 where AccountID in (Select AccountID from TestDB2.dbo.Table2)The above query runs and the expected results are returned.. all is wellThen someone changes the TestDB2.dbo.Table2 Column name changes from AccountID to AccountIDArchive (No idea why it changed but it does)At this point i would assume the schema change should invalidate the plan so that if you run the original query with the AccountID being specified in the subquery it would result in a error.. column cannot be found.If you run the subquery on its own, it does return an error but if you run the Original query Select AccountID from TestDB1.dbo.Table1 where AccountID in (Select AccountID from TestDB2.dbo.Table2)It does not give you an error, and runs successfullyObviously the Origional queries subquery had to be changed to AccountIDArchive to accommodate for the change but i was surprised to see it still using the old plan and that an invalid TSQL code could execute "successfully", or generating a working plan.Using option recompile with the origional query does not cause the query to error out but if you do change the subquery to a new invalid column name it does error outAny opinions to prevent this or explanation as to why this is happening would help :-)Regards
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog