Friday, March 29, 2013

[how to] get rank in marks database in mysql

[how to] get rank in marks database in mysql

get rank in marks database in mysql

Posted: 29 Mar 2013 06:55 PM PDT

I have a Student database having one table EXAM. 

id    1  2  3  4  5  marks 7 10  7 10  9

Question is how to give rank like

+------+  | rank |  +------+  |    3 |  |    1 |  |    3 |  |    1 |  |    2 |  +------+

I have used a Query:- 

SELECT id, marks, FIND_IN_SET( marks, (  SELECT GROUP_CONCAT( marks  ORDER BY marks DESC )   FROM exam )  ) AS rank  FROM exam;

but it gives rank 

+------+  | rank |  +------+  |    4 |  |    1 |  |    4 |  |    1 |  |    3 |  +------+

What should I do??

Recreate Indexes on 1 billion record table

Posted: 29 Mar 2013 03:55 PM PDT

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partiton this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow incase of index rebuild ( with tempdb OFF). Please help me with an efficent way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficent and less resource consuming way ( OR recommend any other approach)

1.

  • Drop existing CIX and drop all N-CIX's
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes on PScheme

2.

  • Drop only CIX
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks

MySQL not allowing text columns?

Posted: 29 Mar 2013 01:30 PM PDT

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.

How to create view in SQLite using INSERT INTO?

Posted: 29 Mar 2013 12:33 PM PDT

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly in view. I tried to create view using following query:

CREATE VIEW summary AS  INSERT INTO (SELECT Name, SUM(Area) FROM table1 GROUP BY Name)  SELECT *  FROM (SELECT Name, SUM(Area) FROM table2 GROUP BY Name)

But I get the error: SQL error: near "INSERT": syntax error. Actually I tried different querys involving INSERT INTO and it seems that CREATE VIEW will not work with INSERT INTO, and INSERT INTO does not accept subquery - only existing table (even temporal) would do.

How to rewrite CREATE VIEW statement to achieve my goal?

Role of Database Administrators

Posted: 29 Mar 2013 10:46 AM PDT

I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the creation and maintenence of this database.

I want to understand what role the DBA normally plays in this type of scenario. It seems the DBAs consider everything to be the responsibility of the application team. I designed the schema and indexes, generated the DDL, and am currently in the process of performance testing the database. Are these tasks normally performed by development?

Also, it has been suggested that development should be responsible for the creation and maintenence of the database user accounts. We would be responsible for periodically changing the passwords on the production databases. Is this the norm? It seems to me that it should be done by the DBAs.

How to migrate SQL Server to MySQL

Posted: 29 Mar 2013 03:42 PM PDT

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

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

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

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

Anyone have suggestions?

SSIS Visual Studio 2008 reference variable scope in expression builder

Posted: 29 Mar 2013 09:10 AM PDT

When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the expression for an object that is within the same scope of the variable that you are referencing.

Is it possible to reference the SCOPE of the variable such as @[User:PackageScope:FilePath] or @[User:SQLTaskScope:FilePath] for example so that I can call a variable that is in a different scope then the object I am building the expression for?

How can I check for SQL Server performance problems using T-SQL? [closed]

Posted: 29 Mar 2013 08:59 AM PDT

I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems using T-SQL?

How to create column

Posted: 29 Mar 2013 09:16 AM PDT

I am new to ssrs report designing can any one help me in this

I have two question

1) How to create a column to input user data and after that I need to perform some operations on data enter by the user which i need to display in another column

2)how to make round a decimal to nearest integer ex 6.03 to 6 and 6.52 to 7

SQL Server 2005 Unexpected Disk Usage during Backups

Posted: 29 Mar 2013 10:30 AM PDT

On our SQL Server 2005 SP4 server we have a maintenance plan in place which performs a full backup of selected databases on a nightly basis. The databases are configured in Simple recovery mode and the maintenance plan cleans up backups that are older than 3 days.

The maintenance plan specifies that the backups be stored on the J:\ drive of our server and everything appears to work properly.

Our issue is that around the time the backup step of the maintenance plan finishes we have noticed that the K:\ drive on our server loses free disk space. This will continue until the drive is almost completely full and then the next backup will free up 50-100gb and it will repeat the cycle of taking up disk space and then freeing up the space.

We have used WinDirStat many times to try and find the file that are taking up the space on our K: drive and are unable to locate them. The disk usage appears to be directly associated with the SQL Server maintenance plan that runs.

Is there a way to verify that the backups are not accessing the K:\drive? I can't find any references to the K:\ drive in the SQL Server logs.

Thank you in advance for any assistance you can provide.

Josh

How to undo DROP TABLE statement?

Posted: 29 Mar 2013 01:06 PM PDT

I've accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.

I know that SQL Server can't rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.

How to use array variable in query in PostgreSQL

Posted: 29 Mar 2013 10:02 AM PDT

Create table t1 ( xcheck varchar[], name text );    CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])    RETURNS record AS    DECLARE xrc as record;      execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc;   return xrc;  END;

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?

event scheduler not called

Posted: 29 Mar 2013 07:22 PM PDT

I had created one event scheduler which looks like this

mysql> show create event event1      -> ;  +--------+----------+-----------+--------------------------------------------------------------------------------------------+  | Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+    | event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN   update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------  -----------+  1 row in set (0.00 sec)

This events has not called on 1st of month. So i tried show processlist\g to find it is runnung or not; it showed following o/p

mysql> show processlist\g;  +-----+-----------------+-----------------+---------------+---------+------+---     | Id  | User            | Host            | db            | Command | Time | State                       | Info             |  +-----+-----------------+-----------------+---------------+---------+------+---  | 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |

so NULL in db col means that no DB is assigned to it?

Please help me to solve it.

Automatic database file creation based on database file size in SQL Server 2008 R2

Posted: 29 Mar 2013 07:53 AM PDT

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options available in SQL Server 2008 R2?.

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

Posted: 29 Mar 2013 07:18 PM PDT

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool. Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.

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

Posted: 29 Mar 2013 09:57 AM PDT

Here is my scenario

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

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

Single slave - multiple master MySQL replication

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

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

Any help is appreciated.

Does fast bcp replicate in sybase sql statement replication?

Posted: 29 Mar 2013 11:57 AM PDT

Is it possible to replicate fast bcp using sql statement replication in sybase?

What are the performance implications of creating an index in MySQL?

Posted: 29 Mar 2013 12:57 PM PDT

The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D

SQL server enterprise vs standard

Posted: 29 Mar 2013 07:57 AM PDT

I am studying the difference between SQL server enterprise and standard. The one feature that I find beneficial from this page is:

http://www.microsoft.com/sqlserver/en/us/editions.aspx

Advanced High Availability (Multiple, Active Secondaries; Multi-site, Geo-Clustering)

How exactly does it ensure advanced high availability? Does anybody have any details?

Trouble setting up Nagios to monitor Oracle services

Posted: 29 Mar 2013 10:57 AM PDT

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

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

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

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

innodb changing row format doesn't decrease table size?

Posted: 29 Mar 2013 07:57 PM PDT

We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone know the reason for this?

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

Posted: 29 Mar 2013 02:57 PM PDT

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

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

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

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

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

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

Database setup/design for multiple services

Posted: 29 Mar 2013 01:57 PM PDT

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

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

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

enter image description here

Automate daily backup of database

Posted: 29 Mar 2013 10:46 AM PDT

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

What is connection time?

Posted: 29 Mar 2013 08:10 PM PDT

We are currently trialling ManageEngine SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).

Can anyone tell me what Connection Time represents and/or what I could use this metric for?

We are monitoring a MS SQL Server 2005 database if that makes any difference.

Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

Posted: 29 Mar 2013 08:08 PM PDT

The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.

How can I measure transactions per second in SQL Server 2000?

oracle format specifiers: to_number vs to_char

Posted: 29 Mar 2013 07:21 PM PDT

SQL> select TO_NUMBER(123.56,'999.9') from dual;  select TO_NUMBER(123.56,'999.9') from dual                   *  ERROR at line 1:  ORA-01722: invalid number      SQL> select TO_CHAR(123.56,'999.9') from dual;    TO_CHA  ------   123.6    SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

How do I find the median value of a column in MySQL?

Posted: 29 Mar 2013 05:49 PM PDT

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.

EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow
Posted by at No comments:

[SQL Server] Need help with a .bat for a stored procedure, please.

[SQL Server] Need help with a .bat for a stored procedure, please.

Need help with a .bat for a stored procedure, please.

Posted: 29 Mar 2013 04:09 AM PDT

Hello all, I'm new to SQL and teaching myself as I go. I don't know what to do now though. I'm hoping someone can help or get me in the right direction. I have a script to do a bulk insert. I created a stored procedure for my bulk insert. I saved a copy of my stored procedure.sql in a folder. I have a program that can run an external program. It will only find .bat and .exe files. I need to save my stored procedure as a .bat so that I can have my other program trigger it. Here is my stored procedure:USE [EricaTraining]GO/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[LoadDailyAdjReport] AS Bulk Insert EricaTraining.dbo.cust_adj From 'C:\TEST\importformat.txt' With ( FieldTerminator= '|', Rowterminator= '' )Here is what I have tried in a .bat file:@echo offsqlcmd -S myservername\databasename -i C:\mypath\thestoredprocedure.sqlthat didn't work, so then i tried:@echo offsqlcmd -Smyservername\databasename -E -iE C:\mypath\thestoredprocedure.sqlThis doesn't seem to work either. The problem is... I don't really know how to use all the aliases or if the .bat file needs to have a closing command or if my stored procedure needs something else... If any of you could offer some advice, I'd really appreciate it. Thank you!

Problem with Subquery in Select Statement

Posted: 29 Mar 2013 12:45 AM PDT

This is my initial query that does work.SELECT VW_ChildrenCurrentStatus.FosterChildID, FosterChild.LegacyChildID, Person.FirstName, Person.LastName, Person.MiddleInitial, Person.DateOfBirth, DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age, VW_ChildrenCurrentStatus.StatusDate, VW_ChildrenCurrentStatus.ProgramDescription, VW_ChildrenCurrentStatus.HomeName, (SELECT MAX(ActivityStartDate) AS MaxActivityStartDate FROM ChildActivity WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate, VW_ChildrenCurrentStatus.HomeNumber, VW_ChildrenCurrentStatus.LOCSetting, VW_ChildrenCurrentStatus.FundingSource, VW_ChildrenCurrentStatus.LOCCode, VW_ChildrenCurrentStatus.LOCShortDesc, AgencyCase.CaseName, AgencyCase.WMSCaseName, AgencyCase.CaseNamePersonIDFROM FosterChild LEFT OUTER JOIN Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN CaseChild LEFT OUTER JOIN AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildIDWHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'ORDER BY Person.LastName, Person.FirstNameHere is a second query that I would like to add as another sub query to add the address columns. By itself, it works.The Address table can have more than one address for a personID, I need to return the fields associated with the most recent LastModified date.SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted]FROM [Options].[dbo].[Address] where PersonID =39785 order by LastModified desc I have replaced the "39785" with the "AgencyCase.CaseNamePersonID" This is the query I end up with.SELECT VW_ChildrenCurrentStatus.FosterChildID, FosterChild.LegacyChildID, Person.FirstName, Person.LastName, Person.MiddleInitial, Person.DateOfBirth, DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age, VW_ChildrenCurrentStatus.StatusDate, VW_ChildrenCurrentStatus.ProgramDescription, VW_ChildrenCurrentStatus.HomeName, (SELECT MAX(ActivityStartDate) AS MaxActivityStartDate FROM ChildActivity WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate, VW_ChildrenCurrentStatus.HomeNumber, VW_ChildrenCurrentStatus.LOCSetting, VW_ChildrenCurrentStatus.FundingSource, VW_ChildrenCurrentStatus.LOCCode, VW_ChildrenCurrentStatus.LOCShortDesc, AgencyCase.CaseName, AgencyCase.WMSCaseName, AgencyCase.CaseNamePersonID, (SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted] FROM [Options].[dbo].[Address] WHERE PersonID = AgencyCase.CaseNamePersonID ORDER BY LastModified desc) as AddressColumnsFROM FosterChild LEFT OUTER JOIN Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN CaseChild LEFT OUTER JOIN AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildIDWHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'ORDER BY Person.LastName, Person.FirstNameError Message : Msg 116, Level 16, State 1, Line 25Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.Any help or ideas or different ways to accomplish the same thing would be greatly appreciated.
Posted by at No comments:

[Articles] The Command Shell

[Articles] The Command Shell

The Command Shell

Posted: 28 Mar 2013 11:00 PM PDT

This Friday Steve Jones talks about xp_cmdshell and the security regarding its use. Do you have any holes that might exist if administrators are allowed to use this tool on their instances?

Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
Posted by at No comments:

[MS SQL Server] Restore Master Database

[MS SQL Server] Restore Master Database

Restore Master Database

Posted: 29 Mar 2013 05:00 AM PDT

I'm testing what to do in scenario where the Master database is corrupted beyond the ability for SQL Service to launch or the Master DB drive is lost. In this scenario we do have system database backups but you need to have the instance running to restore backups, and to do that, you need Master db that allows the instance to start.The SQL version is 2008 R2 SP2. I stopped the service and renamed master.dbf to master.old. I tried starting SQL service and got an error as expected.Based on my reading I did the following:1) Open a command line window and go to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release2) Run this command:setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<account> /SAPWD=<sa password.This ends with an error, with the following from summary.txt: Exception summary:The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indentedException type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.I found one thread where someone thought this was dues tot he instance initially installed as EVAL version sunsequently being upgraded to full liscense. This is also true of the instance I am using. What else might it be?

Call powershell in SQL agent job

Posted: 29 Mar 2013 03:04 AM PDT

I use SQL server agent job to call a powershell script. I am using Type operating system(Cmdexec)In the job command window, I type:powershell.exe "D:\PowershellScripts\Write-VolToDb.ps1 'MySQLserver\v2012' dba"OR powershell.exe "& D:\PowershellScripts\Write-VolToDb.ps1 MySQLserver\v2012 dba"It is not working. The scipt self is OK, but I just cannot figure out what is the correct syntax to call it.The 'MySQLserver\v2012' is my server name\instanceName, dba is the database name. They are two parameter of the powershell.What is correct syntax of it, Thanks--------------------------------------------------------------------------------

3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question

Posted: 28 Mar 2013 06:48 AM PDT

I searched hard and many people have a similar question, but I cannot find a clear answer. I have a 3-node Windows Server 2008 R2 cluster, with 3 Named Instances of SQL Server 2012 w/SP1. All 64-bit. I only used static IP addresses for everything, including the SQL Instances. I thought the 3 SQL Instances would use Port 1433 because there are 3 separate named instances, with 3 separate static IP addresses. But no: each Instance has a dynamically assigned port, and therefore won't work because we have firewalls. Shouldn't each Instance just be listening on Port 1433? Can I make each Instance listen on Port 1433?Here are all the details for one of the Instances:In Cluster Manager, under Services and Applications, I click on InstanceA, then underneath the Server Name there is the static IP address I assigned when installing this Instance. Good so far.Under SQL Server Configuration Manager, under SQL Native Client 11.0 Configuration (32-bit), under Client Protocols, under TCP/IP the Default Port is 1433. Also good.Under SQL Server Network Configuration I see my 3 Instances. I click on Protocols for InstanceA, then under TCP/IP there are a lot of IP addresses. Under the IP address that corresponds to the one under Cluster Manager, TCP Dynamic Ports = 0, and under TCP Port it is blank. Uh oh! Also, all the way at the bottom under IPAll it says TCP Dynamic Ports: 61024, and TCP Port again is blank. This is where I thought I would just see Port 1433.Now in SQL Server Management Studio, under SQL Server Logs, there is an entry (I hid the IP address, but it is the same as in Cluster Manager): [b]Server is listening on [ 000.000.000.000 <ipv4> 61024].[/b]So there is that same non-standard, dynamically-assigned port.So again, why isn't this Instance using Port 1433? Shouln't it be? How can I make all 3 Instances listen on Port 1433?Thanks in advance!BTW, I read all of these, none seems helpful:[url=http://support.microsoft.com/kb/823938]http://support.microsoft.com/kb/823938[/url][url=http://support.microsoft.com/kb/318432]http://support.microsoft.com/kb/318432[/url][url=http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx]http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx[/url]
Posted by at No comments:

[SQL 2012] Master key issue with SSISDB and AlwaysOn Availablity Group

[SQL 2012] Master key issue with SSISDB and AlwaysOn Availablity Group

Master key issue with SSISDB and AlwaysOn Availablity Group

Posted: 29 Mar 2013 12:44 AM PDT

Hi Security Guru,I am trying to put every thing together from this below blog post so that we can run SSIS packages when a failover occurs to 2nd Replica that automatically becomes our new primary replica. The encrypted password is stored in lookup table.http://blogs.msdn.com/b/mattm/archive/2012/09/19/ssis-with-alwayson.aspxHere is my script:-----------------------------------------------------------------------------------------------------USE TEST; DECLARE @last_role TINYINT;SET @last_role = (SELECT TOP 1 [replica_role] FROM [TEST].[dbo].[lookup_replica_role]); DECLARE @current_role TINYINT;SET @current_role = ( SELECT ROLE FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1); IF (@last_role = 2 AND @current_role = 1) -- Last time it was secondary, -- currently it is primary: need re-encrypt -- the database master key BEGIN USE SSISDB; PRINT 'Opening the key'OPEN SYMMETRIC KEY ssisdb_key DECRYPTION BY CERTIFICATE ssisdb_cert;DECLARE @pwd NVARCHAR(MAX);SET @pwd = (SELECT TOP 1 CONVERT(NVARCHAR,DecryptByKey(ssisdb_pwd)) FROM TEST.dbo.lookup_replica_role);DECLARE @sqlString NVARCHAR(1024);SET @sqlString = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @pwd +'''';USE SSISDB;SELECT @sqlStringEXECUTE sp_executesql @sqlString; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYEND USE TEST;UPDATE dbo.[lookup_replica_role] SET [replica_role] = @current_role;--------------------------------------------------------------------------------------------------------Here is the issue:Please create a master key in the database or open the master key in the session before performing this operationHowever, I don't have any issues opening master key by running only below script with same password to both nodes and run SSIS packages. I can failover to any node and run this below script and it works just fine.USE SSISDB　　　　　　 OPEN master Key decryption by password = 'xxxxxxxxxx' ALTER Master Key ADD encryption by Service Master KeyWhat I am missing here?Thanks much,Attopeu

Complex data to join rows from the same table

Posted: 28 Mar 2013 09:08 PM PDT

Hi AllI had a huge situations and at the moment only revolve by VB and XML, but is not a fast and easy way to force my users to do.Real world I receive data from several and diverse equipment and each of one insert on central table the following information.[b]Date/Time Insert Tag Device Value [/b]------------------- ---------- ----------- ----------03-28-2013 09:05 1305 125 1251,6903-28-2013 09:05 1305 126 1261,6903-28-2013 09:05 1305 127 1271,6903-28-2013 09:05 1305 128 1281,6903-28-2013 09:05 1305 129 1291,6903-28-2013 09:05 1305 130 1301,6903-28-2013 09:15 1305 125 1252,6903-28-2013 09:15 1305 126 1262,6903-28-2013 09:15 1305 127 1272,6903-28-2013 09:15 1305 128 1282,6903-28-2013 09:15 1305 129 1292,6903-28-2013 09:15 1305 130 1302,69I try several Joing {Inner / All / Out} but not with no sucess., using also a temporary table , but with VB I need a single output from data above decribe like this:[b]Data/Time D125 D126 D127 D128 D129 D130[/b]------------------ ---------- ---------- ---------- ---------- ---------- ------------03-28-2013 09:00 1251,69 1261,69 1271,69 1281,69 1291,69 1301,6903-28-2013 09:15 1252,69 1262,69 1272,69 1282,69 1292,69 1302,69I now is not so easy ask, but I thought I can bring this query directly from SQL Server.Any one can give some ideia to do this.Thanks Paulo Afonso:cool:

Question about new behavior of database selection combobox in SSMS 2012

Posted: 10 Sep 2012 05:08 AM PDT

Hey all,I have some questions about the new behavior of the database selection combobox in SQL 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL 2008 R2, clicking in the text of the database selected (as opposed to the dropdown arrow) selects the text in the combobox rather than activating the dropdown function.I find this sort of a nuisance, since the new intellisense isn't active in the combobox. I don't actually want to type out the name of the database since most of the databases have the same prefix in their name; that would be a lot of unnecessary typing.As such, I would prefer if clicking the text of the combobox would activate the dropdown rather than have it be available for text input. Does anyone know how I can do this?Also, if I use the Control + U keyboard shortcut to put the focus on the combobox, is there any key I can press to activate the dropdown so I can navgiate the available databases that way? I know pressing up and down will switch from one to another, but is there anyway to actually bring up the list?Can anyone recommend any options to alter the behavior of the combo box to make it more useful? I was considering using SSMSBoost; does anyone have any experience with it?I would appreciate any advice anyone can provide. Thanks.
Posted by at No comments:

[T-SQL] Get sum of averages - a challenge

[T-SQL] Get sum of averages - a challenge

Get sum of averages - a challenge

Posted: 29 Mar 2013 12:41 AM PDT

I have a need for a query that groups data, returns averages for the groups, but also sums those averages. This has been stumping me...A simplified version of the table isCREATE TABLE table_1 (Curr_date DATE, Action CHAR(15), Duration FLOAT)Data is likeINSERT Table_1 VALUES ('2013-03-29','SignOn',1000.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',450.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',900.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',300.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',1100.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',475.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',950.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',320.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',800.1234);I can get the average action durations like thisSELECT Curr_date, Action, AVG(Duration)/1000 as Avg_DurationFROM Table_1WHERE Curr_date = CAST(GETDATE() as DATE) GROUP BY Curr_date, Action[font="Courier New"]Curr_date Action Avg_Duration2013-03-29 GetBal 0.38637342013-03-29 SignOn 0.9501234[/font]But I also need to sum those averages for both actions together. So for SignOn and GetBal together the sum is about 1.34. Anyone have any good suggestions for that? Thanks!

How to show Carriage Return Char(13) in XML as real CR instead of tag.

Posted: 28 Mar 2013 08:22 AM PDT

Hi,I'm using a neat query to concatenate strings. It is using XML to do.I had 200.000 rows to be concatenated and the standard SQL was stalling.This statement is doing it in 0.4 seconds!! Amazing!One downside is dat the carriage return CHAR(13) is added as a tag being [b] [/b].I'm looking for a way to have it as a real CR.Anyone knows a way to do this?This is my test code:[code="sql"]DECLARE @TABLE TABLE (ID INT IDENTITY(1,1), LINE VARCHAR(100))DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)DECLARE @OUTPUT VARCHAR(MAX) = ''INSERT @TABLEVALUES ('ONE'),('TWO'),('THREE'),('FOUR'),('FIVE'),('SIX')SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))SELECT @OUTPUT[/code]
Posted by at No comments:

Thursday, March 28, 2013

[SQL Server] Query join

[SQL Server] Query join

Query join

Posted: 28 Mar 2013 07:05 AM PDT

Hi there, hope in your help.In my DB I've two tables: TABLE_LONG and TABLE_SHORT.The two tables are equal but in TABLE_LONG a recording long events and in TABLE_SHORT a recording short events.If count number of long events, I've this output:[code]SELECT COALESCE (idDGIG, 'Tot') AS sGIG, `NUMBER`FROM ( SELECT LEFT (idDGIG, 2) AS idDGIG, COUNT(idDGIG) AS NUMBER FROM TABLE_LONG WHERE 1 AND ( LEFT (idDGIG, 2) LIKE '%QM%' OR LEFT (idDGIG, 2) LIKE '%QI%' OR LEFT (idDGIG, 2) LIKE '%QO%' OR LEFT (idDGIG, 2) LIKE '%QS%' ) AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) GROUP BY LEFT (idDGIG, 2) WITH ROLLUP ) AS Q;+--------+--------+| sGIG | NUMBER |+--------+--------+| QI | 9 || QM | 2 || QO | 6 || QS | 5 || Tot | 22 |+--------+--------+5 rows in set[/code]If count number of short events, I've this output:[code]SELECT COALESCE (idDGIG, 'Tot') AS sGIG, NUMBERFROM ( SELECT LEFT (idDGIG, 2) AS sGIG, COUNT(idDGIG) AS NUMBER FROM TABLE_SHORT WHERE 1 AND ( LEFT (idDGIG, 2) LIKE '%QM%' OR LEFT (idDGIG, 2) LIKE '%QI%' OR LEFT (idDGIG, 2) LIKE '%QO%' OR LEFT (idDGIG, 2) LIKE '%QS%' ) AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) GROUP BY LEFT (idDGIG, 2) WITH ROLLUP ) AS Z;+--------+-----------+| sGIG | NUMBER |+--------+-----------+| QI | 2 || QM | 2 || QO | 16 || QS | 6 || Tot | 26 |+--------+-----------+5 rows in set[/code]Now I need tried join two tables with this query; I think in output total events ( long + short ): 22+26 = 48.Instead I've this wrong output (1144), why? Can you help me.Thanks in advance.[code]SELECT DATE_START, COALESCE (idDGIG, 'Tot') AS sGIG, `NUMBER`FROM ( SELECT CA.DATE_START AS DATE_START, LEFT (CA.idDGIG, 2) AS sGIG, COUNT(CA.idDGIG) + COUNT(A.EVENT) AS NUMBER FROM TABLE_LONG CA JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START WHERE CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY) AND ( LEFT (CA.idDGIG, 2) LIKE '%QM%' OR LEFT (CA.idDGIG, 2) LIKE '%QI%' OR LEFT (CA.idDGIG, 2) LIKE '%QO%' OR LEFT (CA.idDGIG, 2) LIKE '%QS%' ) GROUP BY LEFT (CA.idDGIG, 2) WITH ROLLUP ) AS SSS;+-------------+--------+--------+| DATE_START | sGIG | NUMBER |+-------------+--------+--------+| 2013-03-27 | QI | 468 || 2013-03-27 | QM | 104 || 2013-03-27 | QO | 312 || 2013-03-27 | QS | 260 || 2013-03-27 | Tot | 1144 |+-------------+--------+--------+5 rows in set[/code]

SQL Logical errors

Posted: 27 Mar 2013 04:57 PM PDT

Hi All,I need some one help me identify why am getting logical errors in the output of my query below. Am new to SQL.Thanks. use MSIU;select DISTINCT [dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE AS IssuanceDate,[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE,[dbo].[tbl_SALES_TEAM_MASTER].[str_SALES_TEAM_NAME],[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE As VouchersSold,[dbo].[tbl_VOUCHER_CAPTURE].dt_VOUCHER_ISSUE_DATE AS SalesDate,[dbo].[tbl_SALES_EXECUTIVE_MASTER].str_SALES_EXECUTIVE_NAME AS BCCName,[dbo].[tbl_DISTRIBUTOR_MASTER].[str_DISTRIBUTOR_NAME] AS CBDName,[dbo].[tbl_DISTRICT_MASTER].str_DISTRICT_NAME CBD_DistrictFROM[dbo].[tbl_VOUCHER_ISSUE]Left Join [dbo].[tbl_VOUCHER_ISSUE_DETAIL]ON [dbo].[tbl_VOUCHER_ISSUE].int_VOUCHER_ISSUE_ID = [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_IDLeft Join [dbo].[tbl_SALES_TEAM_MASTER]ON [dbo].[tbl_VOUCHER_ISSUE].int_SALES_TEAM_ID = [dbo].[tbl_SALES_TEAM_MASTER].int_SALES_TEAM_IDLeft Join [dbo].[tbl_VOUCHER_CAPTURE_DETAIL]ON [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID = [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_ISSUE_DETAIL_IDLeft Join [dbo].[tbl_VOUCHER_CAPTURE]ON [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_CAPTURE_ID = [dbo].[tbl_VOUCHER_CAPTURE].int_VOUCHER_CAPTURE_IDLeft Join [dbo].[tbl_SALES_EXECUTIVE_MASTER]ON [dbo].[tbl_VOUCHER_CAPTURE].int_SALES_EXECUTIVE_ID = [dbo].[tbl_SALES_EXECUTIVE_MASTER].int_SALES_EXECUTIVE_IDLeft Join [dbo].[tbl_DISTRIBUTOR_MASTER]ON [dbo].[tbl_VOUCHER_CAPTURE].[int_DISTRIBUTOR_ID] = [dbo].[tbl_DISTRIBUTOR_MASTER].[int_DISTRIBUTOR_ID]Left Join [dbo].[tbl_DISTRICT_MASTER]ON [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRICT_ID = [dbo].[tbl_DISTRICT_MASTER].int_DISTRICT_IDWHERE[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE LIKE 'FPUG%' AND[dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE between '2012-06-01' and '2012-06-30' AND[dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_STATUS !=5

Columns to be included in Index

Posted: 27 Mar 2013 04:26 PM PDT

Dear AllI have one table with millions of rows, with columns as Id, stringcol1,stringcol2,Bigintcol3,numericcol4Primary key is Id.Index created on stringcol1When I query this table ,select sum(numericcol4), Bigintcol3from abcwhere stringcol1 = @stringcol1 and stringcol2 = @stringcol2and Bigintcol3 in not nullgroup by Bigintcol3 It shows me to create index with columns as stringcol1,stringcol3,Bigintcol3 include numericcol4If I exclude any column from the recommended index and check Estimated Plan it shows as index scan on Primary keyDoes it means that in sql 2008 R2 we need to create indexes to include all the column in the "where clause" and also add filed of the select statement in the include column list ? Regards Krishna1

Error authenticating proxy

Posted: 27 Mar 2013 08:52 PM PDT

Hi,I've got a SQL Server 2008 job which ran ok yesterday but has fail today with an error message ' Error authenticating proxy'. The only thing that has changed bewteen the two runs is my Windows AD password. I thought the report had been set up to use a generic password we have that doesn't change but I must be wrong.Question 1. I need to get this report to run but I don't know where to look in SSMS to correct the password.Question 2. How do I amend the job so that it uses the generice password and I don't get this problem next month.Many thanks for any helpStuart.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog