Thursday, August 8, 2013

[how to] Complex constraint across all data in a table in Postgres

[how to] Complex constraint across all data in a table in Postgres

Complex constraint across all data in a table in Postgres

Posted: 08 Aug 2013 07:53 PM PDT

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status.

I want to ensure that the result of this is always either zero or one:

select count(id) from jobs where status in ('P', 'G');

We're using explicit transactions so ideally this check would happen on commit and abort the transaction if the invariant doesn't hold. Handling any racy processing that throws the occasional error is much easier for us to deal with than suddenly ending up with more than one job that is 'in process'.

The solution only needs to work with Postgres so we're quite happy to take a non-standard solution to this. We're currently on 8.4, but we'll be upgrading at some point to 9.x if that makes any difference.

Create Language plperl - Error: could not load library plperl.dll

Posted: 08 Aug 2013 07:35 PM PDT

When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found.

But in my computer, "plperl.dll" file is exist in "C:/Program Files/PostgreSQL/9.1/lib/..." folder ( I can not post illustrative image, this forum require >= 10 reputations)

And if I select * pg_pltemplate, I get:

-[ RECORD 4 ]-+-------------------------  tmplname      | plperl  tmpltrusted   | t  tmpldbacreate | t  tmplhandler   | plperl_call_handler  tmplinline    | plperl_inline_handler  tmplvalidator | plperl_validator  tmpllibrary   | $libdir/plperl

PostgreSQL 8.0 - Windows Server 2008 R2 install - Service won't start

Posted: 08 Aug 2013 05:43 PM PDT

I am trying to install postgreSQL 8.0 on a windows server 2008 R2 machine. After the installation, the service does not start. When I try to start it manually, I get a message saying that the service started and stopped automatically. I tried running the service on a postgres account and as a local system account (in the service properties).

I don't know if this is relevant, but during the installation, I got a few alert messages about "procedural languages" and "contrib files" being installed but not activated on the database.

Every log file in pg_log is empty.

Does anyone know what's going on here?

Any suggestions would be much appreciated

How to link a customer to a country when the city/state isn't known

Posted: 08 Aug 2013 03:44 PM PDT

We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the country, as hospital groups never span countries.

We want to be able to save as much information as we know, so if we know the city, state and country, we want to save all three. If we only know state and country, we want to save those two. If we only know the country, we want to save just that.

Now, I can see three ways of doing this, and was hoping someone would be able to comment as to which is preferable...

1) Have three tables, Countries, States and Cities, each of which is linked from the previous, so any city will have a StateID, and any state will have a CountryID. That way, if we know the city, we can work up the links and find out the state and country. This is neat, and preserves data integrity, but has the disadvantage that if we don't know the city, we can't save it.

One way we thought of to get around this was for each country to contain a placeholder state, that wouldn't be shown in the UI, and for each state to hold one placeholder city. That way, if we only know the country, the placeholder state for that country would be saved, as would the placeholder city for the state. This neatly gets around the problem, but requires placeholder states and cities to be inserted. This isn't a performance issue, as there wouldn't be enough of them to be a problem, but feels a bit like a hack.

2) Another approach is to have a single self-referencing Locations table, that contains a LocationTypID that identifies if the location is a country, state or city, as well as a ParentLocationID, which (if non-null) would refer to the containing location (ie states contain cities and countries contain states). That way, we can save the most granular location that we know.

The disadvantage of this is that the querying would be messy. We are using an ORM, which would work very well with the first approach, as you can choose a country, then navigate to the Regions property to see the associated regions, then for any region, use the Cities property. Approach #2 would require a manual query every time you wanted to get a different entity.

This approach is very flexible, but is probably more flexible than we need, and probably not worth the extra effort required to implement it.

3) The final suggestion was for the Systems table to have a CountryID, a StateID and CityID, and populate whichever one(s) is/are known. This is simple, and doesn't require any placeholder entries, but has one huge disadvantage (in my non-expert opinion), and that is that it seems to involve repeated data which can easily become invalid.

For example, suppose CountryA contains StateA, which in turn contains CityA, whereas CountryB contains StateB, which in turn contains CityB - imagine the same with Cs. There is little to stop someone mistakenly setting a system to be in CountryA, StateB and CityC, even though CityC is not in StateB, and StateB is not in CountryA.

Having played around with this a bit, my feeling is strongly towards the first approach, despite the requirement for placeholder rows, but I would like to hear what the experts have to say.

Anyone any comments?

MySQL data retained after OS reinstall

Posted: 08 Aug 2013 03:04 PM PDT

Something strange happened today.

I reinstalled my OS and then when I reinstalled MySQL workbench some of my old data was still there. I think the actual local db is empty, but all the connections, models, and listed servers are still there. I can't view which users still exist because I get an connection error. The server is running, though.

I have a backup. I do a dump nightly. I was going to restore from that, but I expected a clean slate when I reinstalled. Should I delete everything before moving ahead with the restore?

Does anyone have an idea why/how that happened?

Is PostgreSQL appropriate for processing this large but simple dataset?

Posted: 08 Aug 2013 02:07 PM PDT

I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y combinations, though it could be some combination that gives us 20 million records in the end.

The processing involves retrieving 1-10 columns for each x and y pair and storing various calculated numeric values. Are we nearing/passing the limit of fast response times for Postgres with this many rows?

The processing is all done in-house by one person and shouldn't need to happen more than a couple dozen times as we settle on what summaries we want. So we're not worried about a high number of writes or connections, strict security, making updates to existing records, table relations, losing data because of network connection issues. Basically, we're not concerned about the kinds of things I understand the ACID part of a RDBMS like Postgres brings to the table. But we also don't need to replicate or distribute the data, high availability, change the schema on the fly, or manage an unusual number of writes (say a dozen for each of the 90k x/y pairs)- the kinds of things I understand NoSQL DBs offer.

So I guess the real issue is read-speed out of Postgres for a table of this size and the real question is whether there's a better storage mechanism for what we need to accomplish. Does the answer change if we have 40 billion records? 60?

Condense Query Results with Wildcard

Posted: 08 Aug 2013 02:08 PM PDT

I have the following query:

select programVersion, count(programVersion)        from records R        inner join Programs P            on P.programID=R.usageProgramID        where programName="word"        group by programVersion;

Results:

+----------------+-----------------------+  | programVersion | count(programVersion) |  +----------------+-----------------------+  | 12.0.0         |                     1 |  | 12.2.0         |                 32190 |  | 12.2.3         |                    20 |  | 12.2.6         |                  3990 |  | 12.2.8         |                 13128 |  | 12.3.1         |                   114 |  | 12.3.2         |                     4 |  | 12.3.4         |                    20 |  | 14.0.0         |                    57 |  | 14.1.0         |                 86587 |  | 14.1.3         |                    44 |  | 14.1.4         |                   728 |  | 14.2.0         |                    42 |  | 14.2.2         |                   130 |  | 14.2.3         |                 88219 |  | 14.2.5         |                    66 |  | 14.3.0         |                   126 |  | 14.3.1         |                    36 |  | 14.3.2         |                    74 |  +----------------+-----------------------+

For this purpose I don't really care about the minor version increments. What I'm really interested in is the differences in major increments. Is there a way that I can get the results combined past the first period?

Example Output 

+----------------+-----------------------+  | programVersion | count(programVersion) |  +----------------+-----------------------+  | 12             |         total of 12's |  | 14             |         total of 14's |  +----------------+-----------------------+

how to run Db2 export command in shell

Posted: 08 Aug 2013 05:14 PM PDT

I am trying to run the following db2 command through the python pyodbc module

IBM DB2 Command : "DB2 export to C:\file.ixf of ixf select * from emp_hc"

i am successfully connected to the DSN using the pyodbc module in python and works fine for select statement

but when i try to execute the following command from the Python IDLE 3.3.2

cursor.execute(" export to ? of ixf select * from emp_hc",r"C:\file.ixf") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "db2 export to ? of" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601\r\n (-104) (SQLExecDirectW)')

or cursor.execute(" export to C:\file.ixf of ixf select * from emp_hc")

Traceback (most recent call last): File "", line 1, in cursor.execute("export to C:\myfile.ixf of ixf select * from emp_hc") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0007N The character "\" following "export to C:" is not valid. SQLSTATE=42601\r\n (-7) (SQLExecDirectW)')

am i doing something wrong ? any help will be greatly appreciated.

From what i came to know db2 export is a command run in shell, not through SQL via odbc.

can you please give me some more information on how to run the command in the shell i am confused and what does that mean ? any guide or small quick tutorial will be great

How can I generate TPC-DS queries for PostgreSQL?

Posted: 08 Aug 2013 01:14 PM PDT

All,

I am using TPC-DS to run some benchmarks on a few versions of postgres that I have. I noticed that there is no template for generating the TPC-DS queries with the target database as postgres. The query_templates directory contains template files for ansi, sqlserver, db2, oracle, and netezza, but none for postgres.

I have tried running the queries generated by each of the previously listed targets on postgres, but they all seem to have some kind of syntax that postgres doesn't support, because most of them fail to run.

Do you know if it is possible to generate these TPC-DS queries for postgres? Or is there a postgres.tpl file that can be downloaded somewhere?

Thanks.

Grouping and counting rows by existence of matching criteria in any of several columns

Posted: 08 Aug 2013 12:11 PM PDT

Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting software (currently Style Intelligence, which has its own data interpretation interface (supports standard SQL, etc.). I'm open to some data restructuring if necessary.

I have several tables of assessment data from which I need to count rows that "pass" or "fail" the assessments (one row = one assessment). Each row has a series of columns which contain the answers YES, NO, NA or null, and these answers comprise the assessment. A NO value in any of the answer columns constitutes a fail. I need a count of rows that contain NO in any of these columns, and then a count of the remainder (or, no "No" values in any of the answer columns. I don't need to count the number of NOs on one row, just group and count by the existence of at least 1 NO answer in any of the given columns. There are other tables which store assessment data in a similar structure, but instead use 1, 2 and 3 as the "yes, no, NA" answers, because they're storing the value from an MS Access form's option group.

Here are some examples of rows that I have, and how they would be grouped for counting:

AsmtID  Q1      Q2      Q3      Q4      Q5      Q6      Q7      Desired grouping    3       NO      YES     YES     YES             NA              FAIL  4       NO      YES     YES     YES     YES     NO      NA      FAIL  5       YES     YES     YES     YES     YES     YES     YES     PASS  6       YES     YES     YES     YES     YES     YES     NA      PASS

It's worth noting that I'm working with a very small amount of data, certainly no more than 200 rows from any one table at a time, so computational efficiency isn't a big concern for me. My best guess would be some routine which steps through each column on a row and then returns Pass or Fail based on the first NO value found. I would really appreciate any insight on how to interpret this data. Thank you in advance!

Full-text Search XML Properties

Posted: 08 Aug 2013 05:56 PM PDT

I'm trying to determine whether I can retrieve a property (attribute) value from an xml blob stored in a FILESTREAM-enabled column using Full-text search. Here is a sample where message is a varbinary(max) field that stores xml messages:

SELECT message FROM AuditTable  WHERE CONTAINS ( PROPERTY ( message, 'AccountNo' ), '123456');

On the MSDN page Search Document Properties with Search Property Lists, the summary at the top of the page includes this statement: "On the other hand, the XML IFilter does not emit properties."

I'm confused because it appears that this should work for an XML document. I'm starting to prep an environment to test, but I wanted to ask first just in case someone already knew.

Conversation_handle in "CO" but unable to send the message successfully

Posted: 08 Aug 2013 05:27 PM PDT

I have an issue with Service Broker. This is first time I am working with service broker to debug the issue rather than developing. But the issue is that some of the conversation_handle in sys.conversation_endpoints which are still marked as CO and when Service Broker sends messages from the initiator queue to target queue it uses the conversation_handle for the corresponding @@SPID but the problem is the target queue is unable to initiate the conversation_handle to receive the message.

I see my message still sits in the sys.transmission_queue and see that all messages sent by the same conversation_handle did not make it to the target queue, but interesting is that all the conversation_handle are still marked as CO in sys.conversation_endpoints.

I ran profiler too and I selected all broker events and that is how I came to know the conversation_handle on the target queue is not initiated as soon as message is sent.

I'm using SQL Server 2005 SP2.

How do I reclaim reserved space in the sysjobstepslogs table

Posted: 08 Aug 2013 05:20 PM PDT

I have a 36GB msdb database in my SQL Server 2008 R2 database server. From what I have read this is past the point of being gigantic. When I look at the tables in the msdb database the sysjobstepslogs table is using 97% of the space in the database. I've done enough research to find out that the cause was several jobs had steps that had the Log to table option set + long running transactions + snapshot isolation on the msdb database. This option has been disabled on those jobs.

I have tried to reduce its size by doing a truncate table, reindex, reorganize, check table, shrink file, shrink database (pretty much in that order) but with 0 rows, the sysjobstepslogs table still indicates that is has 35+GB reserved and used.

It has been suggested that I disable then reenable snapshot isolation to try to free up the space but according to this MSDN forum discussion you cannot disable snapshot isolation on the msdb database.

I have contemplated dropping and recreating the table but how do I recreate the table as a system table and even if I did, is such a thing wise?

Any ideas how to reclaim this space?

how to explain indexes

Posted: 08 Aug 2013 11:21 AM PDT

This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database.

Books? What, on paper? Index cards in drawers?

The youngsters don't really know what I'm talking about, so how do we best explain it nowadays?

(Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)

Does the Binary Data column shown in SQL Profiler contain useful information?

Posted: 08 Aug 2013 10:06 AM PDT

Many of the events that SQL Profiler captures include a Binary Data column. The documentation for these event classes merely repeats the fact that this column contains "Binary value dependent on the event class captured in the trace."

I'm particularly interested in the RPC:Starting and RPC:Completed events; I notice that the binary data for each event appears the same whenever the same query is executed, although the Starting and Completed events have different binary data. But other events also display information here that I would be interested in decoding. This assumes, of course, that the data would be even relevant or meaningful.

Is the content of these fields documented anywhere, and is it ever meaningful to a developer/administrator?

Select date from another table [migrated]

Posted: 08 Aug 2013 12:30 PM PDT

I really need your help. Please help me out to get this to work.

I have these two tables, the relation between them is the user_id. I accomplished to select who I follow and to display their images into my wall. However, I have this strange issue: the photos are sorted by DESC as i want, but they are sorted by the user_id. This means that whoever I follow first, their image will be sorted first by DESC.

I tried almost every possible way to get the photos to be sorted base on DESC newest photo as the top, but I couldn't. Here are my tables and i'll show you every possible thing I tried:

CREATE TABLE IF NOT EXISTS `photos` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `img` varchar(255) NOT NULL,    `about` text NOT NULL,    `date` varchar(222) NOT NULL,    `user_id` varchar(255) NOT NULL,    `likes` int(255) NOT NULL,    `down` int(255) NOT NULL,    PRIMARY KEY (`id`)  ) ;    CREATE TABLE IF NOT EXISTS `follow` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `followers` varchar(255) NOT NULL,    `following` varchar(255) NOT NULL,    PRIMARY KEY (`id`)  }

One

followers_photo.php, where I retrieve the following id from:

$the_user = mysql_query("SELECT * FROM users WHERE username = '$getuser'");  $tuser=mysql_fetch_array($the_user);  $tu_id = $tuser['id'];    $followers = mysql_query("SELECT distinct follow.*       FROM follow JOIN photos ON (follow.followers  = $tu_id)  order by photos.date DESC");  $f_s_count = mysql_num_rows($followers);
  • index.php which i display the images here.

while($uim = mysql_fetch_assoc($followers)){ $i_id = $uim['following']; $followers_image = mysql_query("SELECT distinct photos.* FROM photos JOIN follow ON (photos.user_id = $i_id) GROUP BY RAND() order by date DESC");

The above is working, but as I mentioned it sorts the images based on date DESC and the user_id which I don't want. I want it to stop sorting the image based on the user_id

Two

followers_photo.php 

    $the_user = mysql_query("SELECT * FROM users WHERE username = '$getuser'");  $tuser=mysql_fetch_array($the_user);  $isuser = mysql_num_rows($the_user);    $tu_id = $tuser['id'];  $tu_name =$tuser ['username'];  ////users whos following Me  $followers = mysql_query(" SELECT distinct follow.* FROM photos join  follow on  follow.followers = photos.user_id WHERE follow.followers = $tu_id order by photos.date DESC");  //  $f_s_count = mysql_num_rows($followers);

index.php 

    while($uim = mysql_fetch_assoc($followers)){  $i_id = $uim['following'];  $followers_image = mysql_query("SELECT * FROM photos  WHERE user_id = '$i_id' order by date DESC ");

The above does the same as in the first step. Could anyone point me to the right way? Sort the photos from the people I follow based on date DESC, who posts last comes first. Thanks guys, I appreciate your help a lot. And sorry for my bad English.

Update

now with the solution that vegatripy give , images are duplicating and not order as wanted. on left is what is showing now. what i want is to sort as the right image. any ideas? http://i.stack.imgur.com/Cod6z.jpg

Solved

    $followers = mysql_query("SELECT PHOTOs.IMG, follow.followers, follow.following, photos.date,photos.img,photos.likes,photos.down,photos.id,photos.about  FROM FOLLOW  JOIN PHOTOs ON ( PHOTOs.USER_ID = FOLLOW.FOLLOWING )   WHERE FOLLOW.FOLLOWERS =31  ORDER BY PHOTOs.DATE DESC");  $f_s_count = mysql_num_rows($followers);  while($fimage = mysql_fetch_assoc($followers)){  $i_id = $fimage['following'];  $disimg = $fimage['img'];  $disid = $fimage['id'];  $distime = $fimage['date'];  $i_like=$fimage['likes'];  $i_unlike=$fimage['down'];

SQL Server DR, mirroring or virtualise and mirror

Posted: 08 Aug 2013 10:43 AM PDT

I have a client with a small business server 2008 Premium installation. This obviously includes SQL Server 2008 standard on a separate 2008 server. On the SQL we run several line of business applications.

The client has got the the size where if we were to have a failure it would cripple the business. We have ample backups protecting the data, but it would obviously take time to get a new server up and running etc.

What we want is a stand by server in a remote office, but I need to know the best way of replicating the SQL server. I see there are different ways, snap shot or transactional etc, but I haven't found a decent article describing the pros and cons of them.

I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates. I don't want the primary server waiting on the secondary server to update before it commits a write.

Also the table structure and views change fairly regularly as new features/updates are added to the LOB applications.

I'm wondering if anyone has any opinion on what method may help.

Also wondering if we would be better off virtualising the server and mirroring this, which I believe is quite good with the new hyper V.

edit (OP, once you have associated your accounts, please incorporate this further information to make a single, cohesive question.)

sorry for the delay.

I suppose in terms of data loss, i suppose an hour wouldnt be the end of the world.

Hardware, wouldnt be identical as its currently on an hp ml350 g6

i was talking about hyper-v 2012s replication to a completely seperate server.

I guess i am concerned about disk io as one of the lob dbs is pretty hefty and slow, although we got the developers to re-write some of the worst bit to make it better.

I guess my biggest concern is if when writing data it has to wait for both the local and remote server to update. Does log shipping need do this?

The remote server will be on remote ADSL connection, and i would like to avoid having to replicate it twice.

Also the SQL server is part of Small business server 2008 premium, but was brought as OEM, are we allowed to either move the second windows server license onto another machine (the main server will remain on the origional hw) or can we move just the SQL onto a new windows 2012 box? as this would mean we can move the sql onto better hw, and use the old one as the DR box.

How to avoid timeouts during big DML operations

Posted: 08 Aug 2013 05:21 PM PDT

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again.

The problem I have is that this runs for about 4 hours or so, and during that time the system that uses this database gets really slow and timeout a lot.

I would not care if my INSERT is slower but it shouldn't impact other users.

I was thinking in doing some batch of let's say 500 rows and use the WAITFOR, but was wondering if there could be a better option for doing this.

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

Posted: 08 Aug 2013 10:07 AM PDT

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey.

I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date: 

Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]),   Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY])))

This works well until you throw Requisition Status into the mix. I have rows for each requisition for every day in 2013. For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date. When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved.
Pivot Table

Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table.

The Requisition Status table looks like this: Requisition Status

Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job. Sometimes it is 1, sometimes is is more than 1. It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.

Stored procedures vs. inline SQL

Posted: 08 Aug 2013 05:23 PM PDT

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.

I'd like to know the technical reasoning for this (in a way that I can explain it to someone later).

Can anyone help me formulate a good answer?

High CPU usage on SQL server - Slow queries [closed]

Posted: 08 Aug 2013 05:22 PM PDT

Our MS SQL Server is using about 95% of the CPU-power.

After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.

When it's over 80%, every query is extremely slow.

Our website is dealing with alot of big queries, so some of them takes 45-60 seconds. After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.

How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?

There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.

Most of them are already pretty well optimized.

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

SQL: Microsoft SQL Server 11.0.2100.60  OS: Windows Server 2012 x64  Processor: 2.30 GHz  RAM: 4.00 GB

mysqldump freezing on a specific table

Posted: 08 Aug 2013 02:57 PM PDT

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command:

mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data > /home/sys_data.sql

When the dump was blocked:

show processlist;  5306612 | root | 192.168.1.161:57180 | sys_data      | Query  | 23955 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_trade_376`

On the other hand I can dump the table tb_trade_376 successfully if I just dump the table only.

mysqldump -uxx -pxx -h192.168.1.xxx \    --single-transcation sys_data tb_trade_376 > /home/tb_trade_376.sql

This works well and quickly! The table tb_trade_376 has about 700,000-800,000 rows.

What is the next step in investigating why I can't dump the whole database? How can I make it work?

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

Posted: 08 Aug 2013 01:56 PM PDT

On Windows, I upgraded from 5.1 to 5.5 no problem.

  1. Copied my 5.1 data folder into my 5.5 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

  1. Copied 5.5 data folder to 5.6 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade  Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe  Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe  Running 'mysqlcheck' with connection arguments: "--port=3306"  Running 'mysqlcheck' with connection arguments: "--port=3306"  mysql.user_info                                    OK  Running 'mysql_fix_privilege_tables'...  Running 'mysqlcheck' with connection arguments: "--port=3306"  C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect  FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

  • Has anyone seen this or know what is going on?
  • During the "upgrade" the user table gets erased and when it tries to connect it can't?

Thanks.

SSRS 2008 R2 setup issue

Posted: 08 Aug 2013 04:57 PM PDT

I have installed SSRS 2008 R2 on my desktop and server. When I hit the reports link on my desktop

http://mypc/Reports_mypc/Pages/Folder.aspx

all I get to see is this home page of the desktop ssrs instance

I cant create a new folder or data source or anything of the sort

On the server where I am attempting to set up SSRS 2008 R2, all I get is a white screen that shows the virtual folder name in large fonts, followed by the version of the reporting services server on the next line. This is not leaving me any clues as to what needs to be fixed. On both pcs I am using the credentials of the local admin. Any clues on what needs to be fixed?

What is the difference between int(8) and int(5) in mysql?

Posted: 08 Aug 2013 11:56 AM PDT

I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5)

in both cases the maximum value is

−2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1

or do i miss something?

I found this Question: http://dba.stackexchange.com/a/370/12923

The (5) represents the display width of the field. From the manual, it states:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The display width, from what I can tell, can be used to left-pad numbers that are less than the defined width. So 00322, instead of 322. TBH, I've never used it.

But it doesn't affect the storage size of the column. An int will take up more space than a smallint.

so there seems to be no difference then.

MySQL table relations, inheritance or not?

Posted: 08 Aug 2013 05:57 PM PDT

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping.

I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content stored in database. Blog page would have page_url, but no page_content. Blog would have Posts, Categories...

Lets say I have route like this:

/{pageurl}

This is page, with page url that can be home, or news, or blog... That page can be either Static page, and then I would joust print page_content. But it can also be Blog Page, and then I would print latest posts as content.

How should I relate these Static Page and Blog Page tables? Is this inheritance, since both are pages, with their URL, but they have different content? Should I use inheritance, so that both Static and Blog page extends Page that would have page_url? Or should I made another table page_types and there store information about available page types?

Error Authenticating Proxy Account when Executing SSIS job

Posted: 08 Aug 2013 01:53 PM PDT

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.

These packages have been running for years and the associated steps run via a Proxy Account (PackageExecutor). PackageExecuter is associated with a SQL Credential that was a former domain admin account.

Soon, the domain associated with this admin account is going to be shutdown. I have to use a new account, on a new domain, as the admin account associated with my roxy, PackageExecutor. When I created a new Credential for the new Admin account and associated it with PackageExecutor, I started to get the following error when I tried to run one of my SQL jobs as a test:

Unable to start execution of step 1 (reason: Error authenticating  proxy *Domain\Admin_Account*@*fully.qualified.domain.com*, system  error: Logon failure: unknown user name or bad password.).  The step  failed.

If I'm understanding this reasonably explicit error, what it's telling me is that the Credential accounts, associated with my proxy is in correct. How do I validate this?

I know that this account is legitimate-- I've already associated it with every associated server group, I've made it a sysadmin user on the server.

What could be causing this problem?

To be clear, I haven't mis-typed the account name or the password associated with the Proxy Credential. However, when I entered the account name Domain\Admin_Account and clicked the Check Names button, SQL Server automatically transformed the User ID to the fully-qualified version. I'm not sure if this has anything to do with this problem.

I'm at a bit of a loss. I've given my credential account full access to everything that I can think of. What might I need to do to get this to work?

UPDATE

Sorry, one more quick mention. I've found this MSDN kb article. Resolution method #1 is what I've been doing for years. The others don't seem to apply, or I'm missing something. Any tips or clarification would be beneficial.

Posted by at No comments:

[MS SQL Server] Possible Corruption

[MS SQL Server] Possible Corruption

Possible Corruption

Posted: 08 Aug 2013 04:05 AM PDT

We had a disk drop and come back today that holds filestream files. I ran a DBCC CheckDB and it reported no allocation errors and no consistency errors in the database, but I am unable to do a log backup or differential backup. I get the errors...Msg 3634, Level 16, State 1, Line 1The operating system returned the error '21(The device is not ready.)' while attempting 'FsFileHeader::Read' on ''.Msg 5172, Level 16, State 70, Line 1The header for file '' is not a valid database file header. The File Header property is incorrect.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.I am able to open the database and return query results from it. Anyone come across this before?Thanks,Josh

Is it possible to configure a trace to only keep X days worth of trace files?

Posted: 08 Aug 2013 02:10 AM PDT

The reason I ask is, my employer requires setting up a trace to record certain events (they follow the DOD STIG guidelines...) and they require that the trace files be kept "online" for at least 30 days.The problem is, the various SQL servers generate *wildly* varying numbers of records in these traces. One server might generate a couple hundred a day, not rolling over to a new file for weeks, while the Sharehog / I mean Sharepoint server generates 50k+ in less than an hour.So, currently, every so often I have to log into a server and delete old trace files. I'd set up a scheduled Windows task to do the deletes, but they've also got Group Policies in place that prevent saving credentials, so I'd still need to manually run the job...I could probably do an Agent Job with a CMDExec step and a proxy (which are also forbidden without a good reason,) but then I'd have to deal with the hassle of getting the OK for a proxy...If it isn't possible (and from what I've found, it's not) to set a trace to keep X days old of files, then maybe this would be a feature MS could look into adding to SQL Server at some point...

Moving Files between FileGroups

Posted: 08 Aug 2013 12:22 AM PDT

Books Online isn't very clear on this subject and Google-Fu suggests that what I want to do should be possible, but I can't find any examples to assist me, so I'm hoping people here can help.I want to move a file in a Secondary file group back to the Primary file group. This is cleanup. The database in question is getting upgraded and moved to a better server with a single drive, so we don't need multiple files & file groups in multiple locations. I know it's an ALTER DATABASE statement, but MODIFY FILE only allows me to modify the name and file size, not the file group it's hiding in (that I can see). And trying DBCC SHRINKFILE with EMPTYFILE fails because there are no other files in the Secondary file group that the data can be moved to.Can someone point me to a link or give be a better search term than "moving data between filegroups"?

difference b/w installation of SQL Server 2005 and SQL server2008R2.

Posted: 08 Aug 2013 02:16 AM PDT

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

Install failed SP2 for 2008R2 SP2 on one node in a 2 node SQL Cluster Environment although build number updated

Posted: 07 Aug 2013 09:33 PM PDT

I have installed SQL2008R2 Service Pack 2 on our Passive node succesfully. We rebooted that, failed over the cluster so the now Active node was the SQL Server just upgraded. The install on what was the active/now passive node failed. From the Summary.txt file we have:Overall summary: Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Exit code (Decimal): [b]1209309228[/b] Exit message: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Start time: 2013-08-08 07:47:41 End time: 2013-08-08 08:16:12 Requested action: PatchInstance MSSQLSERVER overall summary: Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Exit code (Decimal): [b]1209309228[/b] Exit message: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Start time: 2013-08-08 07:53:29 End time: 2013-08-08 08:16:06 Requested action: PatchI have examined the details.txt file but all I can find is:2013-08-08 07:53:29 Slp: Sco: Returning service status Running2013-08-08 07:53:29 Slp: The following NT service was in a running state prior to patch action: ReportServer2013-08-08 07:53:29 Slp: Attempting to run patch request for instance: MSSQLSERVER2013-08-08 08:16:12 Slp: Error: Failed to run patch request for instance: MSSQLSERVER (exit code: 1209309228)2013-08-08 08:16:12 Slp: Sco: Attempting to get service start mode for service SQLBrowser2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API for buffer size2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API with AllocHGlobal buffer2013-08-08 08:16:12 Slp: Sco: Returning service start mode Automatic2013-08-08 08:16:12 Slp: Sco: Attempting to get service start mode for service SQLBrowser2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API for buffer size2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API with AllocHGlobal buffer2013-08-08 08:16:12 Slp: Sco: Returning service start mode Automatic2013-08-08 08:16:12 Slp: Restoring the following NT service to a running state after patch action: SQLBrowser2013-08-08 08:16:12 Slp: Sco: Attempting to start service SQLBrowser2013-08-08 08:16:12 Slp: Sco: Attempting to start service SQLBrowser, start parameters 2013-08-08 08:16:12 Slp: Sco: Attempting to start service SQLBrowser2013-08-08 08:16:12 Slp: Sco: Service SQLBrowser already running or pending start2013-08-08 08:16:12 Slp: Sco: Attempting to get service start mode for service SQLWriter2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API for buffer size2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API with AllocHGlobal buffer2013-08-08 08:16:12 Slp: Sco: Returning service start mode Automatic2013-08-08 08:16:12 Slp: Sco: Attempting to get service start mode for service SQLWriter2013-08-08 08:16:12 Slp: Invoking QueryServiceConfig Win32 API for buffer sizeI have uploaded both the summary.txt and details.txt file to skydrive at: [b][url=http://sdrv.ms/1cMnPSa]http://sdrv.ms/1cMnPSa[/url][/b]The engine build number of SQL Server was updated and the @@Version displays the correct details: [b]10.50.4000.0[/b].A few hours on google has not brought up anything specific about error code [b]1209309228[/b].Any assistance would be greatly appreciated.

sql server licencing for demonstration purpose

Posted: 07 Aug 2013 06:26 PM PDT

Hi,If I would like to demonstrate my software that runs off sql server 2008 r2 what edition of sql server would I need?If I had multiple demonstrators would I need multiple licences?Documentation on this would be great but ill take any advise.

cluster setup

Posted: 07 Aug 2013 07:06 PM PDT

I have 3 VM servers.I need to setup 3 node cluster using these 3 VM servers.Can someone provide the procedure to setup cluster.Thanks.
Posted by at No comments:

[Articles] Balloons and Data

[Articles] Balloons and Data

Balloons and Data

Posted: 07 Aug 2013 11:00 PM PDT

A common misconception for users is how tempdb, and data files in general work.

Posted by at No comments:

[SQL 2012] Speccing new hardware

[SQL 2012] Speccing new hardware

Speccing new hardware

Posted: 07 Aug 2013 08:05 PM PDT

Hi allAre there any good articles out there to spec out a new SQL server machine for an existing application? Including things like how to judge how much CPU and memory the SQL server actually requires?Thanks

deleting duplicate

Posted: 07 Aug 2013 11:40 PM PDT

Hi all,can anyone please tell me why its not workingtable duplicate col1 col2 1 abc 2 abc 1 abc 1 bcdresult(in oracle) col1 col2 1 abc 2 abc 1 bcd result(in sql server) col1 col2 2 abc 1 bcd below query is deleting duplicate records in oracle .but in sql server instead its deleting both the recordsoracle:DELETE FROM duplicate d1 WHERE 1<(select count(1) from duplicate d2 WHERE d1.col1=d2.col1 and 1.col2=d2.col2)sqlserver:DELETE d1 FROM duplicate d1 WHERE 1<(select count(1) FROM duplicate d2 WHERE d1.col1=d2.col1 and d1.col2=d2.col2)

Exception has been thrown by the target of an invocation error while executing a table valued function

Posted: 07 Aug 2013 11:04 PM PDT

Friends,We are getting the error mentioned below whenever we are executing a function in the DB. Failed to create AppDomain "CAC_HL7_1.dbo[runtime].28871". Exception has been thrown by the target of an invocation.The same function is present in another SQL Server Instance and it is getting executed without any issues. Please help me in identifying what exactly the issue is and the fix for the same.

about exam 70-457 books

Posted: 07 Aug 2013 10:59 AM PDT

I plan to take 70-457 soon and search books about it.I download "Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012_PDF.pdf" from microsoft website. There are 2 parts in this book.Are these part1 for exam 70-457 and part2 for 70-458?I just read part1, but I saw somebody said there is no reference book for these exams, so I want to make sure.Thanks a lot.

Upgrading from SQL Server 2005 to 2012

Posted: 07 Aug 2013 07:08 PM PDT

HI We are planning to upgrade our sql servers 2005 to 2012.its not just an upgrade, we are installing a fresh copy of windows and then install 2012.Backup to take:DB backups, logins, jobs, maintenance plans.so my question is:is there anything else I need to take backup of other than above?what is the process/procedure to follow to take complete backup of Server settings and configurations ?

Just Beginning ...

Posted: 07 Aug 2013 11:48 AM PDT

I have done a lot w/ Excel Databases, but needed to expand to more power. I have installed SQL Server 2012, but I don't have a clue how to start this thing. No Icon was placed on my desktop. The Sql Server 2012 folders created don't make much sense to me. I only have a book that tells me to choose Management Studio from the main menu. Know idea how to even get to a main menu. Can anyone help me get started:hehe:thxf

Database in Availability Group Issue

Posted: 07 Aug 2013 04:46 AM PDT

I have a database that currently believes its in an Availability Group that it is not in. When I check the sys.dm_hadr_database_replica_states view on the primary replica I see 2 rows one for a primary replica and one for a secondary replica. When I run the same query on the secondary replica I don't see any records I also do not have that database on the secondary replica. Back on the primary replica I attempt to remove it from the AG and get the following errorDatabase 'X' cannot be removed from availability group 'AG'. The database is not joined to the specified availability group. Verify that the database name and the availability group name are correct, then retry the operation.Then I attempt to add it even though it says its there already and I get the following errorDatabase 'X' cannot be added to availability group 'AG'. The database is already joined to the specified availability group. Verify that the database name is correct and that the database is not joined to an availability group, then retry the operation.Because this database thinks its in an AG I can not delete it or take it offline.
Posted by at No comments:

[T-SQL] Pivot columns

[T-SQL] Pivot columns

Pivot columns

Posted: 07 Aug 2013 08:46 AM PDT

I have a table structure as ID Name1 A2 B3 C4 B5 E6 F7 G8 H9 I10 J11 K12 L13 M14 N15 O16 P17 Q18 R19 S20 T and so on...Can anyone help me to get the results either for adding Static columsn around 6 to 7 or Dynamic columns which can accomdate the number of records in the table by count 6 as mentioned below.ID COL1 COL2 COL3 COL4 COL5 COL61 A G M S Y AE2 B H N T Z AF3 C I O U AA AG4 D J P V AB AG5 E K Q W AC AI6 F L R X AD AJThanks in AdvanceSrikanth Reddy

Updating target table with source data

Posted: 07 Aug 2013 12:09 PM PDT

It's almost 2AM here so obviously I'm not thinking very well at the moment.I have a source (orange border) and target table (green border)[img]http://i.imgur.com/VEbT4jZ.jpg[/img]What I need to do is add the records from the source table into the target table but the to_date in the target table needs to be updated with the from_date - 1 from the source table. I just can't figure out how to do this.If anyone can offer suggestions I would be most grateful.Sample data below:[code="sql"]create table source_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)insert into source_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)select 6705290000, 14673893000, 22898762212, 3475, '2013-07-22', NULL UNION ALLselect 6705290000, 14673284000, 22573281212, 2893, '2013-07-19', '2013-07-21' union allselect 100381173, 14664965000, 22815438212, 17607, '2013-07-18', NULLcreate table target_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)insert into target_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)select 6705290000, 11045507000, 15243111182, 3319, '2010-04-01', NULL union allselect 100381173, 14528777000, 22500073212, 16727, '2013-04-05', NULL union allselect 100381173, 9245349000, 11945621182, 17607, '2010-04-01', '2013-04-04'[/code]

Query Help - To delete old files

Posted: 07 Aug 2013 11:27 PM PDT

Hi All,Looking for assistance to develop T-SQL codeRequirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldfThanks in advance for any help.

Query help - summing between dates ranges and calculations...

Posted: 07 Aug 2013 08:58 PM PDT

Hi all,Looking for assistance in a SQL query to use in a report.Finance people need to show "price variance" to our group owners and are currently spending a lot of time in Excel. I'm thinking/hoping I can get the data via a [b]single SQL query[/b].I can get the data out of only three tables -Customers, Invoices and Invoiceitems - but it's the grouping and summing I'm not sure how to do.The output would be:Customer Number. From "Customers" Customer Name. From "Customers" and will join to "Invoices"Part Number. From "Invoiceditems" - which can join to "Invoices" - as is all of the data below…"Last year's Price." The sum of the qty * price within a date range"This year's price" The sum of the qty * price within a different date rangeQty shipped last year. Total qty of the part shipped grouped by customer within a date range Qty shipped this year. Total qty of the part shipped grouped by customer within a date range "A value for last year." Last year's price / by total qty within a date range "A Value for this year." This year's price / by total qty within a date rangeExample Output.Last year customer "A" bought part "ABC" 100 times on ten different orders and paid two different prices.This year customer "A" bought part "ABC" 75 times on 20 different orders and paid four different prices.I need it for ALL customers though. A nine column output for maybe a thousand customers and around 500 separate part numbers.Now, as I think about this and I have already discussed with colleagues, I see that the questions(s) are, er, not the ones necessarily I would want to ask. However, he who pays the piper, etc.Apologies in advance if I've put this in the wrong place and/or gone into too much detail.

Find number in String and Return INT

Posted: 07 Aug 2013 05:33 PM PDT

Hi All,I have a series of strings in a variable @buyer3, RRRRR4, SSSSS5, DDDDDDDDDDD6, AAAA7, PPPPPP8, UUUUUU9, MMMMMM10, LLLLLL11, IIIII12, EEEEEand I only want to sub string the number then convert it to an INT, so i can use it in a where clause.[code="sql"]select *from companywhere company_id = CAST(SUBSTRING(@buyer,1,LEN(@buyer) - CHARINDEX(',',@buyer,0)) AS INT)order by company_id[/code]substring (@buyer, start at position 1,find length of @buyer minus the index position of the comma in @buyer) then cast int. but it doesn't work. say conversion failed when converting value 3,RRRRR.I think it should be pretty simple, but I can't see it.Thanks for any help.

Query exchange 2013 calendars from SQL 2008 R2

Posted: 07 Aug 2013 10:32 AM PDT

Hi,I have been asked to report on exchange 2013 calendars from within SQL server.The exchange 2013 server is a different server to the SQL server, but in the same domain.I've had a look at some older technology using ExOLEDB, but that appears to require that the exchange and SQL instances reside on the same server.Is there anyone who knows what I need to do in order to be able to set up maybe a linked server (or equivalent) that will enable me to query exchange calendars from SQL?Many thanks,Craig

Help with the query and index.

Posted: 07 Aug 2013 02:03 AM PDT

I've a table which has about 15 columns, I'll just put 3 columns here in the script that we would be using in the script.CREATE TABLE "schema"."tblTest"( "TestId" uniqueidentifier NOT NULL , "StartDateTime" datetime2 NOT NULL , "EndDateTime" datetime2 NULL )TestId is PK with a clustered index. Also, there is 1 nonclustered index on StartDateTime. No index is present on Enddatetime.The query I need to rewrite\optimise is like this:SELECT TOP (500) test.[TestId]FROM tblTest as testWHERE COALESCE(test.EndDateTime, test.StartDateTime) < @Date;The table will have about 1 million records, and about 1/16th (about 6-7%) of that would be part of the output at any given time. Right now this query does clustered index scan. I would like it to do seeks instead.Solutions I tried:1) Add a computed column with definition: (case when EndDateTime is not null then EndDateTime Else StartDateTime End) and have an index on this. This works perfect but I cannot use this because we use microsoft sync framework that does not like computed columns.2) Indexed view with computed column. Again, we cannot use this as we need to support SQLExpress.3) Completely re-write the query by splitting it into 2 queries and union the 2 results. Create 2 separate indexes on enddatetime and startdatetime. This seems little overkill maybe. ;with cte as (SELECT test.[TestId] FROM schema.tblTest as test WHERE test.EndDateTime < @Date union all SELECT test.[TestId] FROM schema.tblTest as test WHERE test.EndDateTime is null and test.StartDateTime < @Date) select top 500 testid from cteAny help on finding a better solution would be appreciated.Thanks in advance.

Logic Behind Try . . . Catch

Posted: 07 Aug 2013 06:18 AM PDT

Hello All,I have a question behind the logic of Try and Catch. I'm using Try Catch in a complex statement with a While Loop. If I put the Try . . . Catch outside of the while loop and say the loop fails the 3rd time around, will the entire statement be caught and no transaction committed? By this I mean will the previous two loops be negated? I'm assuming that this is the case.Thanks again!

Parse data Stored in a varchar Column as XML

Posted: 07 Aug 2013 05:50 AM PDT

AllI have a column that stores data in a varchar column. The data is easily converted to xml but it has a namespace embedded within the data . I used the value method but I keep geting null for the results for the column when I try to extract the account number. I've tried the namespace option as well.Data Example:<p322:ViewStatement xmlns:p322="http://proxy.ws.ejb.mhg.com"><p322:XMLRequest><?xml version="1.0" encoding="utf-8" ?><Request><AccountNumber>123456789</AccountNumber><InternetScreenName>mhg.com.Statements</InternetScreenName><StatementDate>05/20/2013</StatementDate></Request></p322:XMLRequest></p322:ViewStatement>SQL ExampleSELECT TOP 1000 [RequestID] ,cast([Request]as xml) as test ,cast( [Request] as xml).value('(Request/@AccountNumber)[1]', 'Varchar(9)') as AccountNumber FROM [log]Any Suggestions?

delete rows

Posted: 07 Aug 2013 06:24 AM PDT

hihere is my queryCREATE TABLE [dbo].[CustomCode]([ID] [int] NULL,[pcondition] [varchar](50) NULL,[Pinten] [int] NULL,[scondition] [varchar](50) NULL,[sinten] [int] NULL) ON [PRIMARY]insert into dbo.CustomCodeselect 1,'hf',1,NULL,NULLUNION ALLSELECT 1,'CAD',1,NULL,NULLUNION ALLSELECT 2,'CA',1,null,nullunion ALLselect 2,'ast',1,null,nullunion ALL SELECT 3,'HF',2 ,null,nullunion ALL SELECT 3,'HF',2,NULL,NULLunion ALL select 3,'HF',2,NULL,NULLUNION ALL select 3,'HF',2,NULL,NULLunion ALL SELECT 5 ,'hf',3 ,null,nullunion ALL SELECT 5, null ,null, 'CA',2UNION ALL SELECT 5, 'HF',2,NULL,NULLUNION ALL SELECT 5,NULL,NULL, 'CAD',2union ALL select 5,'HF',2,NULL,NULLunion all select 6,'AF',3,NULL,NULLunion all select 6,'AST',3,NULL,NULLUNION ALL select 6,'AST',3,NULL,NULLunion all select 7, 'AF',1,NULL,NULLunion all select 7,'AF',1,NULL,NULLunion all select 7, 'AST',1,NULL,NULLAfter executing store porc as belowcreate procedure [dbo].[customecode]asbegincreate table #SortSomething(pcondition varchar(3),SortOrder int)insert #SortSomething(SortOrder, pcondition)select 1, 'AF' union allselect 2, 'CA' union allselect 3, 'CAD' union allselect 4, 'AST' union allselect 5, 'hf';with duplicateprimarycondition as(select cc.*, s.SortOrder, ROW_NUMBER() over(partition by ID, cc.pcondition,cc.pinten order by s.SortOrder) as RowNumfrom CustomCode ccINNER join #SortSomething s on cc.pcondition = s.pcondition)select * from duplicateprimarycondition endresult will be ID pconditionPinten sconditionsinten SortOrderRowNum1 CAD 1NULL NULL3 11 hf 1NULL NULL5 12 ast 1NULL NULL4 12 CA 1NULL NULL2 13 HF 2NULL NULL5 13 HF 2NULL NULL5 23 HF 2NULL NULL5 33 HF 2NULL NULL5 45 HF 2NULL NULL5 15 HF 2NULL NULL5 25 hf 3NULL NULL5 16 AF 3NULL NULL1 16 AST 3NULL NULL4 16 AST 3NULL NULL4 27 AF 1NULL NULL1 17 AF 1NULL NULL1 27 AST 1NULL NULL4 1i need to modify my stored proc so that if for that particular ID, for that particular pcondition and pinten if sorder order comes 1 as two time,it should delete 1 row,example, here7 AF 1 NULL NULL 1 17 AF 1 NULL NULL 1 2it should delete 1 row as two times 1(sortorder) is coming , one row should remain in table out of twoI want delete statement after my statementplz help

How to made a stored procedure for already date exists ?

Posted: 07 Aug 2013 02:00 AM PDT

Hai friends ,i ve the table like travel_requestcreate table travel_request( request_id int identity, user_id varchar(20), travel_purpose varchar(20))insert into travel_request (user_id,travel_purpose) values ('012','Market Visit')journey create table journey (request_id int references travel_request(request_id),departuredate datetime,from varchar(20),to varchar(20))now my requirement is when user enter the date if it is table ll show already exists........how to make a code?
Posted by at No comments:

[SQL Server 2008 issues] How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

[SQL Server 2008 issues] How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

Posted: 07 Aug 2013 07:03 PM PDT

Hi everyone, I need to connect an access database to a SQL server 2008 R2 instance, via SSL, how can I do it? when I try to connect the db, using access, by clicking ODBC database and I select the path of the server in which runs sql server, nothing happens, no credentials request, no error, nothing. When I try to reach the same server via rdp, credentials are required, which is correct.I found no help or guide about this, can someone help?thanks

sys.dm_os_performance_counters not accurate?

Posted: 07 Aug 2013 06:52 PM PDT

I've started collecting data from here now & on our busiest system I am seeing 450 transactions/secSurely this is too high to be accurate? My data is like this:[code]TRANSACTIONS_BASE TRANSACTIONS_DIFF6945125076 1546686945527571 402495[/code]So 6945527571 - 6945125076 = 402495 transactions over a 15 minute period if this is the correct way to calculate it?402495 / 60 (seconds) / 15 (minutes) = 447 transactions/sec :sAnyone had transaction counts this high? There hasn't been any news about the tran/sec data being inaccurate has there?

Performance Impact due to different collations?

Posted: 06 Aug 2013 10:00 PM PDT

Hi all,We've just 'upgraded' one of our 3rd party apps which required a new version of the vendors database (Using SQL 2008 R2 SP1 Standard Edition running on Windows Server 2008 R2 Enterprise edition). Upon familiarising myself with the new database (no they didn't bring me in on the initial testing and implementation!) I discovered that the collation is set to 'Latin1_General_100_CI_AI' which is different to the instances TempDB collation of 'SQL_Latin1_General_CP1_CI_AS'. The app is running into performance problems (speed issues mostly when logging in and with certain transactions), my question is two-fold: 1. Could this be related to the difference in collations if the app uses the TempDB?2. Should I recommend that the collation on the app db be amended by the vendor to reflect that of TempDB?Thanks for any suggestions.M

Transactions in SQL

Posted: 07 Aug 2013 03:31 PM PDT

I have some doubts related to transactions in SQL.Connection 1 open for DB1 databaseBegin transaction for Connection 1Run the commands on Connection 1Connection 2 open for DB2 databaseBegin transaction for Connection 2Run the commands on Connection 2Commit transaction for Connection 2Commit transaction for Connection 1If commit transaction on Connection 1 fails(rollback) then transaction on connection 2 should rollback.Is it possible? How it can be done?

SSIS - Pull data from two different servers

Posted: 07 Aug 2013 06:31 AM PDT

Here is my situation. I have to pull data from two different servers that are not linked together. For that, I will have to use SSIS to create a package to pull down the data. In server A, there is a staging table and that is where I will pull the data from two different servers into. Here is what I have so far in SSIS package. In Control flow, I added an Execute SQL Task to get the necessary information from multiple tables in Server A and put it into the staging table in Server A. Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.I am fairly new to SSIS and I think the only way to do this is to use Script Task as that is the only task that can connect to two different servers at the same time. Do you have a better suggestions or ideas?

denormalizing the table

Posted: 07 Aug 2013 03:28 PM PDT

Hi Experts,I need to denormalizing the below table:[code]CREATE TABLE #temp( Id int ,category int ,Parent_id int ,Lvl tinyint ,Name varchar(20))insert into #tempSELECT 1,1,null,1,'World' UNION ALLSELECT 2,1,1,2,'Asia' UNION ALLSELECT 3,1,2,3,'India' UNION ALLSELECT 4,2,null,1,'a' UNION ALLSELECT 5,2,null,1,'d' UNION ALLSELECT 6,2,5,2,'dd' UNION ALLSELECT 7,2,4,2,'aa' UNION ALLSELECT 8,2,7,3,'aaa' UNION ALLSELECT 9,2,6,3,'ddd' UNION ALLSELECT 10,2,9,4,'dddd' UNION ALLSELECT 11,2,8,4,'aaaa' UNION ALLSELECT 12,2,8,4,'bbbb' UNION ALLSELECT 13,2,8,4,'cccc' UNION ALLSELECT 14,1,2,3,'china' --Expected Output:Id category Level1_id Level1_Name Level2_id Level2_Name Level3_id Level3_Name Level4_id Level4_Name Level5_id Level5_Name1 1 1 World 2 Asia 14 china NULL NULL NULL NULL2 1 1 World 2 Asia 3 India NULL NULL NULL NULL3 2 4 a 7 aa 8 aaa 11 aaaa NULL NULL4 2 4 a 7 aa 8 aaa 12 bbbb NULL NULL5 2 4 a 7 aa 8 aaa 13 cccc NULL NULL6 2 5 d 6 dd 9 ddd 10 dddd NULL NULL[/code]Currenlty I am achieving this with joins .. but there could be a better way to do this. Thanks!

What is dbo?

Posted: 07 Aug 2013 03:41 AM PDT

Why do some do this Database..Table instead of Database.dbo.Table. Which one is a good practice?What is dbo?Thanks

Using CTE - Does this help the optimiser

Posted: 07 Aug 2013 06:30 AM PDT

If I use CTE, I can write a more readable SQL statement. But does this affect what the optimiser does?One of the reasons I use temp tables or table variables is to break down the queries into simpler queries that the optimser can work out more easily.Do I get the same effect with CTE? E.g if I have a query that joins 6 tables, but break it down into two CTEs that join 3 and then a final join of the two CTE results, will I get a more consistent plan?I'm assuming that I can structure the CTE in asensible way that makes good use of the indexes.ThanksTim

Update in Procedure doesn't work (but it does outside the SP)

Posted: 07 Aug 2013 06:57 AM PDT

Hi guys,I came back to check on my new stats gathering but found an issue..all the steps of the stored procedure run except for an update. A summarised version of the procedure is below with the full update statement. When I look at the results (running every 15 minutes since 4pm) the columns that should have had a numerical value are all null. If I run it manually it sets them all to 0 (since at this point the entries are already in the main table).[code]-- 1. cursor of linked servers-- 2. truncate dbo.PERF_COUNTERS_TEMP-- 3.0 <loop each linked server>-- 3.1 insert perfmon values into dbo.PERF_COUNTERS_TEMP-- 3.2 </loop>-- 4. insert perfmon values for this repository db into dbo.PERF_COUNTERS_TEMP-- 5. run the update belowUPDATE dbo.PERF_COUNTERS_TEMP SET TRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE), LOGINS_DIFF = (a.LOGINS_BASE - b.LOGINS_BASE), LAZYWRITES_DIFF = (a.LAZYWRITES_BASE-b.LAZYWRITES_BASE), LATCHES_DIFF = (a.LATCHES_BASE-b.LATCHES_BASE), FULLSCANS_DIFF = (a.FULLSCANS_BASE-b.FULLSCANS_BASE), RANGESCANS_DIFF = (a.RANGESCANS_BASE-b.RANGESCANS_BASE)FROM PERF_COUNTERS_TEMP a INNER JOIN ( SELECT row_number() over (partition by instance, db_name order by coll_date desc) rnk, instance, db_name, COLL_DATE, TRANSACTIONS_BASE, LOGINS_BASE, LAZYWRITES_BASE, LATCHES_BASE,FULLSCANS_BASE,RANGESCANS_BASE from dbo.PERF_COUNTERS) b on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME and b.rnk=1;-- 6. insert all data from dbo.PERF_COUNTERS_TEMP into dbo.PERF_COUNTERS-- 7. insert all data from dbo.PERF_COUNTERS_TEMP into an Oracle database[/code]Steps 6 & 7 work fine but the problem is it doesnt have the "_DIFF" values I was hoping for :( Instead they remain null from the inserts inside the loop. Do I need some GO statements inside my SP to make the update work or something? Why does it work when i run it separate to the rest of the code? :s

SQl Job running an SSIS package in 2005 server failing while trying to connect 2008R2 server

Posted: 07 Aug 2013 12:29 AM PDT

"Access to the remote server is denied because no login-mapping exists"This is the error message i am receiving while trying to run a sql server agent job running an SSIS package from 2005 server installed ssis package.The ssis Package data sources are pointing to a sql 2008 R2 server.The sql agent is running under sql server agent proxy account this is an AD account and have sa access on both the 2005 and 2008r2 server.can some one please help me out in figuring out the issue.When i run the the the job is throwiung this message.Executed as user: XXX_XXDev. ...sts.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOperational [2800] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Access to the remote server is denied because no login-mapping exists.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOperational [2800] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Access to the remote server is denied because no login-mapping exists.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOpera... The package execution fa... The step failed.

Metaphone 3

Posted: 07 Aug 2013 03:51 AM PDT

We are currently using Ray Cardillo's "Double Metaphone" for phonetic name matching, and would like to upgrade to the newly released "Metaphone 3" (http://amorphics.com/buy_metaphone3.html). Unfortunately, the code is written in PL/SQL and we are operating in a T-SQL environment. Is anyone aware if "Metaphone 3" was translated to T-SQL, or if there is a simple way to translate the PL/SQL code to T-SQL?

Index Rebuild/Reorg

Posted: 07 Aug 2013 04:49 AM PDT

How to load Index Rebuild/Reorg results in table? For e.g. the results we see when we run in query analyzer. I would like to capture the results in table with date it ran against server name,frag % etc.Thanks

All Identity Column values in replicated database jumped to over 1,500,000

Posted: 07 Aug 2013 04:37 AM PDT

I'm not a db admin. I'm a developer trying to keep the SQL Server 2008 R2 replicated database running. The database was not being used because we didn't have any projects so things lapsed. I now have 2 projects and I'm trying to work with the database and all the identity column values jumped from in the thousands to the millions seamingly overnight. This change is blowing out the application I use to access the database. I'm getting "overflow" on everything. The question is why did it jump. I looked at the article properties for a few of the tables where I added records to a table and the identity column value was in the millions. I looked at the Identity Range Management for the table and I see that all my identity column range values have jumped into the millions. I tried dbcc checkident to reseed a table back into the thousands but on a subsequent add I received an error saying the addition failed because the identity column was not within the approriate range. So now I can't add to the table. It's clear that I need to change the identity ranges at the publicher but I am uncertain how to do it. I keep reading that I should use sp_adjustpublisheridentityrange to fix this but the parameters for the sp does not allow for a range so I havn't tried it. Can anyone tell me why this has happend and make some suggestions on how to fix it.Thanks

Integrated Security and AD Group

Posted: 07 Aug 2013 01:05 AM PDT

This is probably a basic question but I've not thought about it much until now. If a user has a domain login to a server with one set of rights and is part of an AD group login that has a second set of rights do the rights get merged or does one login take precedence over the other?Regards,Erin

Increase Max workers Threads

Posted: 07 Aug 2013 02:15 AM PDT

I have a SCCM database that while reports are and pushes are running together in the morning I get threadpool waits. I run the below with the results listed. Would increasing my max worker threads to 720 help this issue. More cpus is not an option and I have extra RAM availabe on the server. Currently max worker threads is set to 0 and with 12 vcpu that give me 640. most the time my count is around 660 workers. -not option to edit the SP's these jobs run and I have anaylzed indexes (frag, added, etc)select COUNT(*) from sys.dm_os_workers - 655sp_configure "max worker threads"name minimum maximum config_value run_valuemax worker threads 128 32767 0 0select max_workers_count from sys.dm_os_sys_info; - 640

Using extended events to capture sort warnings and hash warnings

Posted: 01 Aug 2013 02:48 AM PDT

HiI have just downloaded the extended events gui for sql2008.Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?

select ident_current('table name')

Posted: 13 Sep 2011 03:54 AM PDT

I use ident_current to get ID which just inserted. 1) The code below is working in current database:select ident_current('history')2) The code below is not working in another server:select ident_current('ServerA.live.dbo.history')How to make code in 2) working?

How to make this a SQL Syntax!

Posted: 06 Aug 2013 10:48 PM PDT

I have a script but not in a SQL language: if ({act_type.act_type_sc} = ["ADD INFO INTERN","ADD INFO P/C"] and instr({act_reg.act_rmk},"closed")>0) then 1else 0I was search and i think it was CASE but i dont get a good syntax.. Can somebody helping me out.Tnx

Tempdb tables two months old

Posted: 01 Aug 2013 02:06 AM PDT

Hi AllI run the following query to get all tempdb tables:[code="sql"]SELECT *FROM tempdb.sys.sysobjectsWHERE name LIKE '#%'ORDER BY crdate[/code]I can see around 50 tables that are between one and two months old, and another 70 that are between one week and one month old.I'm guessing these are created by application components that maintain a constant connection, and are never dropped or deallocated. Is there a way I can find out what procedure, application, or even connection is creating these tables? I'd like to go into whatever is creating them and add the DROP or SET = NOTHING.Thank you

UPDATE comparing tables (most recent row only)

Posted: 06 Aug 2013 10:25 PM PDT

Hi guys,I was gonna put this in the TSQL sub-section but the little intro says it's for discussing "changes" to TSL in 10.0~I'm looking at sys.dm_os_performance_counters and attempting to derive values from their accumulating columns (e.g. transactions/sec). My plan is as follows:2 tables (main & temp) with columns TRANSACTIONS_BASE and TRANSACTIONS_DIFF.Data is inserted into the BASE then an update statement should update the DIFF value (from null). The problem is the main table may have hundreds of records for each database & I only want the most recent base value for each database. Having seen that guy's (sorry forgot your name) RBAR signature repeatedly I wanted to try & avoid doing a looping update but my SQL attempts arent getting close :fIt will update the columns but it looks at every row in the main table so in the example below it would do 4 updates instead of the 2 it should (overwriting values):[code]UPDATE dbo.PERF_COUNTERS_TEMP SETTRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE)/60/10FROM PERF_COUNTERS_TEMP a INNER JOIN ( SELECT instance, db_name, COLL_DATE, TRANSACTIONS_BASE from dbo.PERF_COUNTERS c) b on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME[/code]Anyone know how to only take into account the most recent entries in perf_counters for comparisson? Below is some code if anyone is willing to help & wants to replicate the scenario[code]CREATE TABLE perf_counters ( instance varchar(50), db_name varchar(50), coll_date smalldatetime, transactions_base bigint, transactions_diff smallint);GOCREATE TABLE perf_counters_temp ( instance varchar(50), db_name varchar(50), coll_date smalldatetime, transactions_base bigint, transactions_diff smallint);GOINSERT INTO perf_counters values ('SRV01','DB01',getdate(),1000,null);GOINSERT INTO perf_counters values ('SRV01','DB02',getdate(),4000,null);GO-- wait a whileINSERT INTO perf_counters values ('SRV01','DB01',getdate(),1200,null);GOINSERT INTO perf_counters values ('SRV01','DB02',getdate(),4150,null);GO-- wait a whileINSERT INTO perf_counters_temp values ('SRV01','DB01',getdate(),1250,null);GOINSERT INTO perf_counters_temp values ('SRV01','DB02',getdate(),4270,null);GO[/code]Edit: It's easiest to remove the /60/10 for testing since the time intervals won't be there (so it's not always 0).So with the example above the DML would update the perf_counters temp rows to be:SRV01, DB01, *date*, 1250, 50SRV01, DB02, *date*, 4270, 120Since it would ignore the 1000/4000 rows as they are older.

invisible views

Posted: 06 Aug 2013 11:14 PM PDT

I'm looking after a SQL 2008 R2 box and several users have suddenly encountered a problem in a particular database, all views under a particular schema are now invisible however they previously were visible to these users. Strangely they can still select from these views if they can remember the names of the views. They can still see all views under the 'dbo' schema though fine.After going through their permissions on the accounts, they all have 'data reader' permissions and no 'deny' permissions. I've created a test user and given it only 'data reader' permissions and logged in to the server using that account, as suspected it can see all the views and all works perfectly.I'm at a loss as to why this is happening, I could have understood if the test user had the same problem but its fine :-)I'm sure its probably something daft in front of my eyes but has anyone got a clue as to what I'm missing? This is bangin my head now!! :w00t:

Server side trace to a table

Posted: 11 Jan 2012 12:58 AM PST

The link below helps me to create a server side trace to a file. I'd like to send the results to a table. Could some please tell me what change I need to make in the script to send the results out to a table?http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_HowAnd also, I was told that server side tracing to a table in SQL 2000/2005 is different from SQL 2008. Is it?

SSIS - FTP Task and SQL Agent Job

Posted: 06 Aug 2013 08:56 PM PDT

Hi everyoneI have created a SSIS Package that gets a list of files on a FTP site, receives and then deletes each file using a ForEach enumerator.It is working 100% when run using BIDS, but when I schedule this as a job, it fails:Description: The object in the variable "User::FileList" does not contain an enumeratorBut when I run the BIDS version directly after this error, it pulls down the files?What would cause the Job not to get the file list? Its a plain Script Task using a FTP Connection.Any ideas?

Sql cluster8 r2 with out msdtc

Posted: 06 Aug 2013 10:07 PM PDT

One of our client want dba to install sql cluster 2008 r2 on single cluster node.(maybe in the future a second node will be add to the cluster).Can any one clarify the below things.IS it possible to configure msdtc with out secodary node.? Is it possible to install sql cluster on 2008R2 on sinlge node with out configuring MSDTC? orcan we create MSDTC after adding the 2nd node to the existing single node cluster?Advance thanks,

can not create temp directory

Posted: 06 Aug 2013 09:37 PM PDT

Hi All,I am getting below error.can not create temp directoryPlease advice on this.

Trigging a Rport manager report from the agent

Posted: 06 Aug 2013 08:11 PM PDT

Hello!To deliver statistics to the business controllers in our company, I use both report manager for built reports that they trigger themselves by logging in, filling in the forms and retrieve the data in the way they want - PDF, excel or just viewing the data. Another way is the scheduled jobs in the agent that runs and uses the msdb..sp_send_dbmail to e-mail the results. The problem with this second option is that the output is plain text, very ugly and hard to use. The solution I am looking for is to be able to trigger a Report Manager report from a stored procedure (that I schedule in the agent) and get the result from that report in excel format mailed to a specified recipient.Any ideas?

SQLServer Error: 15404

Posted: 06 Aug 2013 07:34 PM PDT

It appears to me that SQL Server do not support hostname over 8 characters. When I enter my hostname to schedule an SQL maintenance plan and save it, it will automatically rename my account from: SOLOMON2009\Administrator to SOLOMON2\Administrator. Backup will fail with the below error log:[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'SOLOMON2\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog