Monday, September 23, 2013

[T-SQL] job history check

[T-SQL] job history check

job history check

Posted: 22 Sep 2013 11:34 PM PDT

Hi friends,I have around 120 jobs in a SQL instance. Some of the jobs are scheduled on hourly basis and some of the jobs are scheduled on daily basis and some of the jobs are scheduled on weekly basis. Is there a way to get the failed job details in such a way that, it explains that this is hourly job this is daily job this is weekly job.Thanks in advance.

Aggregate minutes from varchar datatype.

Posted: 23 Sep 2013 12:49 AM PDT

Hi I have a field in a table that represents the status time spent on activity per employee. The field is stored as a varchar and is in the formatt '000:00:00' I would like to aggregate the minutes, here is a small sample size of the column; however when I try to sum this field I'm receiving this error and cannot cast to a number to aggregate.Msg 241, Level 16, State 1, Line 27Conversion failed when converting date and/or time from character string.Warning: Null value is eliminated by an aggregate or other SET operation.Any suggestions would be appreciated![code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#sum_minutes') IS NOT NULL DROP TABLE #sum_minutes--===== Create the test table with CREATE TABLE #sum_minutes ( STATUSTRACKING VARCHAR(50) )INSERT INTO #sum_minutes --(STATUSTRACKING)SELECT '00:00:01' UNION ALLSELECT NULL UNION ALLSELECT '03:20:01' UNION ALLSELECT '00:00:53' UNION ALLSELECT NULL UNION ALLSELECT '00:08:25' UNION ALLSELECT '00:12:18' UNION ALLSELECT '00:12:18' UNION ALLSELECT '76:03:43' UNION ALLSELECT '00:01:05' UNION ALLSELECT '00:20:24' UNION ALLSELECT '03:58:01' UNION ALLSELECT '00:07:47' SELECT SUM(DATEPART(MINUTE, STATUSTRACKING)) FROM #sum_minutes[/code]

Repeating update or replace statement for column

Posted: 22 Sep 2013 02:21 AM PDT

I want to create an update or replace statement which replaces Event_Code column with repeating values instead for example B100B102B103.......all the way to 10 and repeat again to the end of the table see results wanted SQL scrip example. Is there a way to do this? Thanks for the help.[code="sql"]Create table table1(Participant_ID int not null,Supporter_ID int not null,Event_Code varchar (50),Event_Role varchar (100) null)INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LELEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');[/code]========================================================================Wanted results table sample --Create table resultswanted(Participant_ID int not null,Supporter_ID int not null,Event_Code varchar (50),Event_Role varchar (100) null)INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B100','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B102','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B103','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B104','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B105','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B106','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B107','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B108','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B109','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B110','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','B100','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B100','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B102','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B103','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B104','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B105','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B106','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','B107','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','B108','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','B109','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','B110','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','B100','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','B100','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','B102','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','B103','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','B104','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','B105','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','B106','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','B107','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','B108','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','B109','IsVolunteer');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','B110','IsVolunteer');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','B100','IsVolunteer'); Thanks again
Posted by at No comments:

[SQL Server 2008 issues] How to port no on Sql server

[SQL Server 2008 issues] How to port no on Sql server

How to port no on Sql server

Posted: 17 Sep 2013 07:59 PM PDT

Can any one know where to find port id in sql server.I am new to sql server dba ...please help.Thanks & Regardsshiva

How to get exact values for a constraint in SQL Server??

Posted: 22 Sep 2013 04:40 PM PDT

Is there a way to get the exact values for a constraint on a column? I need to display these values in a list of options to choose from in a webpage, but do not want to parse the expression as the result of the following query:select sys.check_constraints.definitionfrom sys.check_constraintsinner join sys.columns on sys.check_constraints.parent_object_id = sys.columns.object_idinner join sys.tables on sys.check_constraints.parent_object_id = sys.tables.object_idwhere sys.tables.name = 'myTable'and sys.columns.name = 'myColumn'and sys.columns.column_id = sys.check_constraints.parent_column_idParsing the expression will be error-prone and very cumbersome. There must be a way to get the exact values of '1D', '2D', '3D', '4D', for example when constraint is created using these values:alter table myTable add constraint CK_myColumn_Values check (myColumn in ('1D', '2D', '3D', '4D'))thanks for the help.

Reporting Services scheduling- hourly between 8 and 5 impossible?

Posted: 04 Jun 2012 05:14 AM PDT

Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something? If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?

SQL Server 2008 cluster node going down unexpectedly

Posted: 01 May 2013 04:17 AM PDT

Last night our primary SQL Server node went down and failed over to the secondary node.I was actually on the server at the moment having just launched a trace to troubleshoot a particular query when suddenly I lost all connectivity to SQL Server.Our setup is:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 2 Node Active/Passive Cluster.Here is what I found in the Administrative Log :[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation.We have SQL Server and SQL Server agent are running under designated network accounts.SQL Server Browser is running under a Local account.Never had that issue before in 2 years we've been using the server.The SQL Server error log did not reveal much. The very last event in the error log before the node went down is:2013-04-30 20:06:48.970 spid133 SQL Trace ID 2 was started by login "sa". Thank you for your help

Rolling 3 month average cost help

Posted: 22 Sep 2013 03:16 PM PDT

Hi guys,I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out for a couple days now, as well as get help, but to no avail.What I'm trying to do is replicate a 3 month average cost metric that I have in excel to a sql query, so that I can use it in SSRS as a data set. In excel I take the avergae of the sum of the cost and divide it by the count of members.I have two tables that derive the data. Below is example of my tables:Table name: AddmissionContract Admissiondate SumofCost 0606 200701 8639.380607 200702 22895.940608 200703 123752.28null 200704 61378.49Table name: MembersContract Admissiondate CountofMembers0606 200701 860607 200702 1020608 200703 90null 200704 120

Passing Type Table as parameters into Function

Posted: 22 Sep 2013 06:00 AM PDT

Hi All,I am having an experiment with passing tables as parameters into Functions. I have a table type set-up as followsCREATE TYPE MainProd AS TABLE( Year VARCHAR(4), ID 1 INT, ID 2 INT, hours INT)GOI have a function that uses the MainProd as a read only type.When I try to use the function I find I have to first declare a variable with this type, an exampleDECLARE @P MainProdThen call the function in SQL using the @P variable after inserting table values into @PSELECT * FROM [dbo].[MyBestProd] (@P) AS XCan I simply not write the SQL in the function parameter instead of having to use the @P parameter?I triedSELECT * FROM [dbo].[MyBestProd] ([my sql statement here]) AS Xbut it did not like it.ThanksEliza

Stored procedure invoking Elastic Search through HTTP Post. How to increase the 8K limit?

Posted: 22 Sep 2013 11:44 AM PDT

Hi,On one hand, I would like to use SQL Server database to store all the data and use this database for all type of editing.On the other hand, I would like to use Elastic Search to run queries.Therefore I need both "databases" to systematically be synchronized.Since both "systems" are independent from each other, in order to make sure that both are in sync, I thought of the following way of proceeding:I would use a stored procedure with 2 transactions:Transaction B would apply all necessary modifications to the data, stored in SQL server databaseTransaction A would contain Transaction B but also calls to Elastic Search via HTTP POST. If this HTTP Post returns 200 (OK), then Transaction A is committed.This would give something like:BEGIN TRANS A BEGIN TRANS B ... do everything linked to SQL Server database COMMIT TRANS B -- Invoke ElasticSearch through HTTP COMMIT TRANS AIn order to invoke Elastic Search via HTTP, from SQL Server Stored Procedure, I found the following piece of code:DECLARE @URI varchar(8000), @output_XML varchar(8000), @result int, @object int set @URI = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT' EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUTIF @result <> 0 BEGIN RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1) RETURN END EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @URI, false IF @result <>0 BEGIN RAISERROR('sp_OAMethod Open failed', 16,1) RETURN END EXEC @result = sp_OAMethod @object, SEND, NULL, '' IF @result <>0 BEGIN RAISERROR('sp_OAMethod SEND failed', 16,1) RETURN ENDEXEC @result = sp_OAGetProperty @object, 'responseText', @output_XML OUTPUT IF @result <>0 BEGIN RAISERROR('sp_OAGetProperty responseText failed', 16,1) RETURN END SELECT @output_XML StockQouteXMLEXEC @result = sp_OADestroy @object IF @result <>0 BEGIN RAISERROR('sp_OAGetProperty responseText failed', 16,1) RETURN END Everything looks fine EXCEPT that ServerXMLHTTP has a limit of 8K for the POST and I need to POST much more than 8K ...Does anyone have a solution to suggest?Many thanks in advance,

Stored Procedure calling other stored procedure

Posted: 22 Sep 2013 04:36 AM PDT

Hi All,Can someone help me on how to tackle this.I have a stored procedure on my server called usp_RPT_HSG_JobType. This procedure returns the job type code and name that I want to use in another procedure. I have this other procedure which I called usp_RPT_HSG_Repairs by declaring a table variable as follows:CREATE PROC usp_RPT_HSG_RepairsASBEGINDECLARE @Repairs TABLE Jobnumber INT , Jobtype nvarchar (20)INSERT INTO @RepairsSELECT r.jobnumber re.codeFROM repheader rINNER JOIN repairtype re ON re.code = r.code-- Output for detailsSELECT * FROM @RepairsWHERE Jobtype IN (How do I then call the other procedure usp_RPT_HSG_JobType here?) ENDThanks for answeringEO

How to Move Table with Non-Clustered Index?

Posted: 22 Sep 2013 12:48 AM PDT

I am moving tables with clustered and non-clustered indexes to a different filegroup. The clustered index moved the table and data as expected. I tried moving a non-clustered index through Management Studio thinking it would move the table but it didn't. I see the index on the new filegroup but the table/data is still on the primary. I also noticed there is now an empty new table created on the filegroup. Can someone explain how to move tables with non-clustered indexes?
Posted by at No comments:

Sunday, September 22, 2013

[how to] Easy replication for SQL Server: Can it be set up like mongodb or Oracle?

[how to] Easy replication for SQL Server: Can it be set up like mongodb or Oracle?

Easy replication for SQL Server: Can it be set up like mongodb or Oracle?

Posted: 22 Sep 2013 08:41 PM PDT

This might look like a noob question... but it isn't. (I have 18 years of SQL Server experience, and three years of MongoDB experience.)

The basic scenario is:

  • -- Data Center has big, primary SQL Server instance
  • -- Need a warm spare off site

("Warm Spare": Can be up to 5 or 10 minutes behind. While the primary is live, the spare is not used operationally in any way. Is there, and automatically is within 5-10 mins of current. Manual cutover is needed to bring it on line and use it.)

With SQL Server, to set up a backup server, I need to go through a lot of steps, to get log shipping set up:

  • -- networking (open firewalls for multiple ports for windows file share)
  • -- file system shares
  • -- OS users with rights
  • -- SQL Agent
  • -- Jobs to create tlogs, and retrieve and apply tlogs
  • -- initial setup tasks (restoring backup and first kit of logs)

I often use redgate sql backup which helps many of these tasks.

But it is still quite a bit of setup work. Further, in some environments (like the Amazon RDS SQL Server service), I have %100 access to SQL Server, but zero access to the OS. Without OS access, I can't even begin to set up the backup server.

I was talking to a colleague (who is more Oracle centric), who said, "Either you have sipped too much Microsoft Coolaid, and sadly think that the limitations you describe are normal, or there is an easier way."

I think (hopefully correctly!) that SQL Server is world class.

So the question: Is there an easier way to set up an off site, warm spare SQL Server?

On Mongodb, it is REALLY EASY, and zero access to the OS is needed:

  1. Make sure a single port is open between two servers
  2. Tweak a conf file (which can be done via Mongo commands)
  3. rs.initiate() (Mongo command)
  4. rs.add("backup-db-server") (mongo command)

Done!

My colleague indicates that Oracle has a similar mechanism.

So my SQL friends: Can SQL Server match Mongo, or get in sight of Mongo, for simplicity of replication setup?

What database does Yahoo, Bing, and Ask use? [on hold]

Posted: 22 Sep 2013 03:52 PM PDT

I'm trying to find information on popular search engines, and I wanted to know what databases they use. I've been searching everywhere, and I've only found little information. I know Google uses BigTable, but I'm not sure about Yahoo, Bing, or Ask.

Can I delete SQL Server 2008 R2 "audittrace" (.trc) files

Posted: 22 Sep 2013 05:04 PM PDT

This is precaution question :)

When I was configuring SQL Server 2008 R2 database engine, I checked "Enable C2 audits" which I don't really need. From that time it created a lot of files like audittrace20130918235148.trc. I disabled C2 auditing in SQL Server Management Studio by unchecking checkbox.

Are those files really from C2 audit? If so, can I safely remove them without worring about data stored in databases? Again: I do not need audit logs.

Mapping and Inserting Values into a Specialized table

Posted: 22 Sep 2013 03:27 PM PDT

For a system i'm designing,i have one parent entity,Computer

Computer(computer_id,computer_processor,computer_hddrive,computer_memory);

A computer can be of two types,Desktop and Laptop,each having their own specific attributes.

Laptop(laptop_id(Foreign key of computer_id),laptop_color,laptop_battery);

and similarly a table for Desktop.

Now the entries to these tables is via a end-user form,my main question is, what would the exact INSERT queries be for adding say to the inventory,a laptop if i were to have 3 tables,one superclass and two subclass tables? I understand i would first how to make an entry to the computer table and then laptop,but how will this be carried out automatically?

How can I return column names in dot notation in MySQL?

Posted: 22 Sep 2013 12:56 PM PDT

I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'.

I did:

INSERT INTO columns(name)   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS   WHERE table_name='contacts' or table_name='companies';

But as expected I get id from the first table showing up with the same field name.

How do I get it in the form: contacts.id and companies.id?

How to create root user after running mysql_install_db?

Posted: 22 Sep 2013 01:49 PM PDT

I'm installing MySQL on ubuntu and I changed the data directory. I created new data files using mysql_install_db command.

So now I need to access my database using root but it won't let me in. I tried starting the database by skipping the grant tables:

mysqld --skip-grant-tables --skip-networking &

Now I'm in, but the mysql.user table doesn't have a single row, which is logical after creating the data files from scratch, or isn't it ??

So I tried:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';

But it won't run because I'm running with --skip-grant-tables !

  • So how can I create a root user from that point ?
  • And how can I stop the started mysql instance I just started with --skip-grant-tables because it seems I always have to killall mysqld to do it !

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

Posted: 22 Sep 2013 01:22 PM PDT

I have recently upgraded SQL Server 2000 database to 2008 R2.

What I did was:

  1. Shutdown SQL Server 2000 (express) service on old machine,
  2. Move datafiles (mydatabase.mdf and mydatabase.ldf) to new machine,
  3. Run SQL Server Management Studio 2008,
  4. Connect to local database engine,
  5. Attach datafiles to database.
  6. Change the compatibility level of database to SQL 2008 (100).

Question: What else should I do to make migration complete?

I want to:

  1. use new features like checksumming and full recovery model,
  2. make this database to be exactly as it was created in SQL 2008 R2,
  3. make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine.

In other words: I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up.

Thank you very much for your time!

Saelic

Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Posted: 22 Sep 2013 08:46 AM PDT

They have explained the root of the problem very well and even advised to use max aggregate as a solution. But, lets take the example

group_id    item_id     comment  --------------------------------  1       abc     "blah-blah"  1       def     "tral-la-lah"  1       ghi  2       jkl  2       mno  2       pqr

I understand the solution: I must take an item to represent the group by max(item_id). But how do I take the comment of the selected item? How does it help me to take the corresponding comment?

edit

Everything is like in that question. The only complication that I address here is the extra comment column that I want to select along with the max(item_id) group by group_id.

I am using this beast, http://meta.stackoverflow.com/a/52063/201017

Nested with - Incorrect syntax near the keyword 'WITH'

Posted: 22 Sep 2013 07:16 AM PDT

With inside with

This is ok

    WITH CS AS (        SELECT      'ab' as a      )      SELECT * from cs

http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question

But, as soon as I wrap it with a second with, 

with PS as (          WITH CS AS (            SELECT      'ab' as a          )          SELECT * from cs          ) select * from PS

I get this error,

Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

With inside select

The following is ok

select ab.a from (    select a from (SELECT 'ab' as a) b              ) ab

This is also runs

  with cs as (SELECT 'ab' as a)    select a from cs

But, wrapping expression with another select, as this one proposes, 

  select a from (      with cs as (SELECT 'ab' as a)      select a from cs    ) ab

fails again with the same error.

I do it in data.stackexchange and they report that they use MSSQL

"The FILE SIZE property is incorrect" error while trying to attach "Adventure Works for SQL Server 2012" sample databases

Posted: 22 Sep 2013 08:26 AM PDT

I am trying to attach the sample databases, for example, AdventureWorks2012_Data.mdf from codeplex "Adventure Works for SQL Server 2012" into a MS SQL Server 2012 (default) instances on a a virtual machines under MS Windows Server 2008 and MS Windows 7.

Each time I am getting the error:

The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect. (.Net SqlClient Data Provider)

------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

What can be done to attach the MS SQL Server 2012 sample databases?

Tried to install these sample databases ак the script(s), also always getting the error, the different one, about which I shall ask in a separate question.

Multiple filters with joins

Posted: 22 Sep 2013 11:35 AM PDT

I just read this qestion: SQL Multiple Filter - Optimal Way

I tried this with Northwind. I filter like this:

declare @supplier int = 1  declare @category int = 1  select * from products p     WHERE (@supplier  = '' or SupplierId = @supplier)    AND (@category = '' or CategoryId = @category)

This seems to work as expected, I get two hits: Chai and Chang. Now I want to add filter ShipPostalCode:

declare @supplier int = 1  declare @category int = 1  declare @shippostal nvarchar(50) = '01307'  select * from products p  inner join [Order Details] od on p.ProductId = od.ProductId  inner join Orders o on o.OrderId = od.OrderId  AND (@category = '' or CategoryId = @category)  AND (@shippostal = '' or ShipPostalCode = @shippostal)

Because of the join I now have multiple rows of Chai and Chang. These filters are users who makes selection in dropdown lists, so even if shippostalcode is chosen, I still need one hit. 

distinct productName

will give me one row per product, but that's not the way to solve it, is it?

EDIT: Thank you guys, Yes, supplier is an int.

So If there be would another many to many relation from the Products table I can just add another AND, I guess.

Sorry, this is a very sad example, but if I add OrderDetails2 and Orders2 where OrdersDetails2 will be a junction table between Products and Orders2, I can just add add another AND (and keep on filtering), like this:

declare @supplier int = 1  declare @category int = 1  declare @shippostal nvarchar(50) = '01307'  declare @city nvarchar(50) = 'boston'  select * from products p   WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)  AND ProductId in  (  Select od.ProductId from [Order Details] od    inner join Orders o on o.OrderId = od.OrderId  Where (@shippostal = '' or ShipPostalCode = @shippostal)  )  AND ProductId in  (  Select od.ProductId from OrderDetails2 od    inner join Orders2 o on o.OrderId = od.OrderId  Where (@city = '' or City = @city)  )

As you can see, the Order2 table has a column 'City' What do you think?

How to give permissions to 90+ SQL Servers without tweaking in each of them?

Posted: 21 Sep 2013 10:28 PM PDT

There are over 90+ MS SQL Servers (SQL Server 2005, 2008 and 2012). All in the same Windows domain (but physically all over the world).

How to give a domain user (administrative, or other) access/permissions to its instances and databases without tweaking (creating, configuring) users/accounts and permissions for such user (or group) in each of 90+ servers, (and even more) their instances and databases?

Detabase Tuning Advisor about Indexing

Posted: 22 Sep 2013 08:40 PM PDT

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation.

       Like        (1) creating trace file using SQl Profiler       (2) Use trace that trace file in DTA       (3) Than look for recommendation       (4) Follow the recommendation & finally see the results through Query Execution Plan.

So i just wanted to know if it is there any way to create a predictable index.

What am I doing wrong in mysql database?

Posted: 22 Sep 2013 02:01 PM PDT

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message:

MYSQL ERROR: Can't update table 'player_viplist' in stored function/trigger because it is already used by statement which invoked this stored function/trigge

What do I need to do for this trigger to work?

DROP TRIGGER dele;    DELIMITER |    CREATE DEFINER = 'root'@'localhost' TRIGGER dele    AFTER INSERT    ON player_viplist    FOR EACH ROW  BEGIN DELETE FROM player_viplist ;     END|    DELIMITER ;

Advice for SQL Server 2000 replication

Posted: 22 Sep 2013 07:38 PM PDT

I have software that runs on SQL Server 2000.

Can you tell me if its possible to synchronize a branch database with its server without human intervention, i.e. programmatically? If so, where do I start?

MySQL row does not persist, but primary key with auto-increment is incremented

Posted: 22 Sep 2013 05:22 PM PDT

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, the row vanishes (despite no other queries being executed).

This table has an integer ID as a primary key, with auto-increment set, and the primary key is auto-incremented as expected for new rows. This leads me to believe there is not some kind of transactional rollback (and my table is MyISAM, so that shouldn't be possible anyways) or anything else that is somehow reverting the database to a previous state.

What logs should I be checking to see what is going on here? The contents of my '/var/log/mysql/error.log' are below, but I don't see anything unusual. 

120815 21:01:01 [Note] Plugin 'FEDERATED' is disabled.  120815 21:01:02  InnoDB: Initializing buffer pool, size = 8.0M  120815 21:01:02  InnoDB: Completed initialization of buffer pool  120815 21:01:03  InnoDB: Started; log sequence number 0 44233  120815 21:01:03 [Note] Event Scheduler: Loaded 0 events  120815 21:01:03 [Note] /usr/sbin/mysqld: ready for connections.  Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'      port: 3306  (Ubuntu)  120815 21:01:04 [ERROR] /usr/sbin/mysqld: Table './appname/users' is marked as crashed and should be repaired  120815 21:01:04 [Warning] Checking table:   './appname/users'  120815 21:10:34 [Note] /usr/sbin/mysqld: Normal shutdown    120815 21:10:34 [Note] Event Scheduler: Purging the queue. 0 events  120815 21:10:34  InnoDB: Starting shutdown...  120815 21:10:39  InnoDB: Shutdown completed; log sequence number 0 44233  120815 21:10:39 [Note] /usr/sbin/mysqld: Shutdown complete

I noted the 'crashed' mark on the appname/users table, but mysqlcheck suggests the table is OK.

Any thoughts?

Fixing wildcard expansion resulting in too many terms error (DRG-51030)

Posted: 22 Sep 2013 04:22 PM PDT

How can I resolve the wildcard_maxterm problem in Oracle 10g?

My index creation syntax is:

begin      ctx_ddl.drop_preference('SUBSTRING_PREF');  end;  /    begin      ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');      ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');  end;  /    begin      ctx_ddl.drop_preference('bus_obj1_lexer');  end;  /    begin      ctx_ddl.create_preference('bus_obj1_lexer','BASIC_LEXER');      ctx_ddl.set_attribute('bus_obj1_lexer', 'base_letter', 'YES');      ctx_ddl.set_attribute('bus_obj1_lexer', 'mixed_case', 'YES' );      ctx_ddl.set_attribute('bus_obj1_lexer','printjoins', ';,@_');        ctx_ddl.set_attribute('bus_obj1_lexer','skipjoins', '-');   end;  /    create index BUS_OBJ_FTS_Name on BUSINESS_OBJECTS1(name)    indextype is ctxsys.context     parameters ('wordlist SUBSTRING_PREF                  MEMORY 128M                  DATASTORE CTXSYS.DEFAULT_DATASTORE                 SECTION GROUP CTXSYS.AUTO_SECTION_GROUP                 STOPLIST CTXSYS.EMPTY_STOPLIST                                          LEXER bus_obj1_lexer                                          SYNC (ON COMMIT)');

And my query is:

select * from BUSINESS_OBJECTS1 where contains(name,'%WIN%')>0 and rownum<=100;

There are 15 million rows in that table, and more than 50000 rows match that query. I have set wildcard_maxterm=50000 and default=20000 but I'm still getting this error:

DRG-51030: wildcard query expansion resulted in too many terms.

Can anybody hep me how to solve this error?

How to split data files across multiple hard drives?

Posted: 22 Sep 2013 03:22 AM PDT

I'm asking for advice concerning point when MySQL becomes a large (maybe medium for someone) system. We have MySQL database (InnoDB engine) that grows in gigabytes. It's already some hundreds of gigs and we definitely know it will grow. What would you advice to do when the size of data exceeds the drive space? I'm pretty sure data will grow out one terabyte, two, and so one later. I've found two different solutions to go with:

1) either use 'innodb_file_per_table' option and run table optimization ofter; Question for point 1: At some point anyway all those different idb files could come to a large size of disk, so is there a way (other than symlink) to move some files to other drive?

2) or add more drives to list in 'innodb_data_file_path' option. Question for point 2: Is that good enough solution?

I would appreciate if you could share any other solutions that worked for you in large systems: some sort of sharding or else?

UPDATED: Title of question updated - hopefully new one is more concrete.

export (mysqldump) databases from MySQL version 4.0?

Posted: 22 Sep 2013 10:22 AM PDT

Goal: export the schema and data from a MySQL version 4.0.16 DB.

Background:

I was asked to migrate a database from MySQL version 4.0.16. The database was installed on a Windows XP machine, and I just could not find mysqldump tool on that machine.

I then putty into a linux machine that has mysqldump. I ran the command, but got an error:

-bash-4.1$ mysqldump --databases db_2_dump --host='myhost' --port='6012' -u <user> -p >db-export.sql  Enter password:  mysqldump: Error: 'You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILE' when trying to dump tablespaces

I looked up mysql reference. INFORMATION_SCHEMA is something new to version 5 and above.

This means the mysqldump on the linux box is incompatible with the production MySQL server which is on v4.0.16.

I tried to download Mysql 4.0 server and install it on a windows machine so that I can get hold of a compatible version, but searching on Mysql website found nothing older than 5.0.

(I also tried Mysql workbench to connect to this DB. Failed. "Unknown character set utf8")

So how can I export the schema and data at all from this legacy mysql DB???...

Optimizing mysql queries running on 50 million rows

Posted: 22 Sep 2013 07:39 PM PDT

Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip  FROM SystemEventsR  WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND   SysLogTagflag=1 AND   username !='')  GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,  COUNT(id) AS Records,   explanation, action   FROM SystemEventsR,oa_syslogtags  WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND   flagid = 1 AND DATE(timestamp) = DATE(NOW()))  GROUP BY SystemEventsR.SysLogTag   ORDER BY Records DESC

Any help would be appreciated.

Dropping Hypothetical Indexes

Posted: 22 Sep 2013 01:22 PM PDT

In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.

I have a database that is full of DTA remnants that I would like to cleanup; however, when I try to drop the object I always receive an error telling me that I cannot drop the object "because it does not exist or you do not have permission". I am a full sysadmin on the server so would expect to have rights to do anything.

I've tried this with both DROP STATS and DROP INDEX statements but both give me the same error.

Has anyone deleted these before and is there a trick I'm missing?

Addendum

Poking around in this, I just noticed that if I R-Click on the object, both the 'Script As' and 'DELETE' options are greyed out.

Avoiding performance hit from GROUP BY during FULLTEXT search?

Posted: 22 Sep 2013 11:22 AM PDT

Is there any clever way to avoid the performance hit from using group by during fulltext search? 

SELECT p.topic_id, min(p.post_id)   FROM forum_posts AS p   WHERE MATCH (p.post_text) AGAINST ('baby shoes' IN BOOLEAN MODE)  GROUP BY p.topic_id  LIMIT 20;

In this example it's fetching the lowest post_id for unique topic_ids that match the text.

With the group by to find the min, it's taking 600ms in a million row database, with about 50K rows examined.

If I remove the MIN but leave the GROUP BY, it's the same slowness, so it's the GROUP hit.

I suspect this is because it can only use one index, the fulltext ?

key: post_text | Using where; Using temporary; Using filesort    Query_time: 0.584685  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 57751  Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No  Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0

Without the GROUP BY it's 1ms so this has to be filesort speed?

(I've removed ORDER BY and everything else to isolate where the hit is)

Thanks for any insight and ideas.

(using MyISAM under mariadb if it matters)

AWS performance of RDS with provisioned IOPS vs EC2

Posted: 22 Sep 2013 12:22 PM PDT

Has anyone done a performance comparison of AWS RDS with the new provisioned IOPS vs EC2? I've found plenty of non-high IOPS RDS vs EC2 but nothing with the new high IOPS feature in RDS.

sp_startpublication_snapshot Parameter(s)

Posted: 22 Sep 2013 07:22 PM PDT

I am creating a stored procedure that:

  1. Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
  2. (If specified in the SP's parameter) Creates three merge replication publications
  3. (What I need help doing) Generating the snapshots for the three publications using sp_startpublication_snapshot

Here is my new brick wall... On this DB server, I have a 'shell' db that they will be running the SP from, that has a history table so I can keep track of who created/deleted databases using my SP's... The only parameter for sp_startpublication_snapshot is @publication... I can give it the publication name, but since I am not running it from the publishing database, how do I specify the publishing database?

i.e.: the publication shows up as:

[WC48_Database1]: upb_Inspection_PrimaryArticles

but I am running the script from the database [WC_QACatalog]

Any ideas about how to accomplish this?

Thank you, Wes

How do I determine if FORCE LOGGING is on for the entire database?

Posted: 21 Sep 2013 10:32 PM PDT

How can I determine if force logging is on, at the database level. (Not asking about finding out if force logging is on for particular tablespaces.)

What rights are needed to perform the check of the above. (I have SELECT_CATALOG_ROLE, but other than that, pretty limited rights.)

I've tried to find out how to query from the Reference and the Administrator's Guide manuals.

Version:

SQL> select * from v$version;    BANNER  ----------------------------------------------------------------  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi  PL/SQL Release 10.2.0.4.0 - Production  CORE    10.2.0.4.0  Production  TNS for Solaris: Version 10.2.0.4.0 - Production  NLSRTL Version 10.2.0.4.0 - Production

How to show the column names of a table with a simple sql query?

Posted: 22 Sep 2013 04:56 AM PDT

The case is simple: You have an mysql database where you have only sql query interface and you want to know the database structure with queries. You can list tables with show tables; command but how do you see the individual column names?

(SELECT statement shows Empty set if no data is present and can NOT be thus used.)

What are the main differences between InnoDB and MyISAM?

Posted: 21 Sep 2013 10:40 PM PDT

What are the main differences between InnoDB and MyISAM?
Posted by at No comments:

[SQL Server] Search Using Stored Procedure

[SQL Server] Search Using Stored Procedure

Search Using Stored Procedure

Posted: 22 Sep 2013 12:51 PM PDT

Hi to all :-)I want to have a result like this ---- SELECT * FROM TestMyView WHERE firstname = 'test5'but my code will produce like this ---- @SqlQuery firstname = @firstnamewhat is wrong with this??...use BiographyDeclare @firstname varchar(50),@middlename varchar(50),@lastname varchar(50), @sex varchar(50),@status varchar(50),@SqlQuery varchar(max),@bioID int, @SqlQueryFirstName varchar(max) SET @bioID = 13 SET @firstname = 'test5' SET @middlename = 'test' SET @lastname = 'tes' SET @sex = 'Female' SET @status = 'single' SET @SqlQuery = '' SET @SqlQueryFirstName = '' -- SELECT * FROM TestMyView-- WHERE firstname = @firstname AND middlename = @middlename AND lastname = @lastname AND sex = @sex AND status = @status AND bioID = @bioID SET @SqlQuery = 'SELECT * FROM TestMyView WHERE ' SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname' print(@SqlQueryFirstName) thanks :-)

Insert date in yyyy-mm format

Posted: 22 Sep 2013 07:57 AM PDT

Hi,I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column.Haw can I do it? is it possible at all? I know that I can sore the full (yyyy-mm-dd) and then to select with: convert(char(7),column_a,121) but this is not the way I'm looking for .Thanks
Posted by at No comments:

[SQL 2012] Adhoc queries and DB id

[SQL 2012] Adhoc queries and DB id

Adhoc queries and DB id

Posted: 21 Sep 2013 05:42 PM PDT

Dear AllI want to get last executed queries on a DB. Using dm_exec_query_plan , dm_exec_query_stats ,dm_exec_sql_text gives this information. But it shows null values for adhoc queries.How to get DB id for adhoc queries?Regards

Unable to upgrade SQL Server 2008 r2 cluster to SQL Server 2012 SP1, failes with Failed to retrieve data for this request

Posted: 21 Sep 2013 06:49 AM PDT

I don't really understand why it would fail. Sometimes when I start the setup process it is fine, next time it doesn't work. I'm not comfortable continuing with the upgrade it being flaky like this. I'm just wondering what could cause this? Here's the output from the logs:(01) 2013-09-21 13:40:18 Slp: Running Action: RunRemoteDiscoveryAction(01) 2013-09-21 13:40:18 Slp: Running discovery on local machine(01) 2013-09-21 13:40:20 Slp: Discovery on local machine is complete(01) 2013-09-21 13:40:20 Slp: Running discovery on remote machine: PC1N1(01) 2013-09-21 13:41:31 Slp: Discovery on PC1N1 failed due to exception(01) 2013-09-21 13:41:31 Slp: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The handle is invalid. at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess) at Microsoft.SqlServer.Configuration.Sco.SqlRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess) at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess) at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetInstanceHiveForSqk2kInstance(String machineName, String instanceName, RegistryView regView) at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView) at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName) at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent) at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.ProcessDTbl(DataTable dt, Int32 level) at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectSqlDiscoveryData(String machineName) at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectDiscoveryData(String machineName) at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.LoadData(IEnumerable`1 machineNames, String discoveryDocRootPath, String clusterDiscoveryDocRootPath)(01) 2013-09-21 13:41:31 Slp: Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction" threw an exception during execution.(01) 2013-09-21 13:41:31 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The handle is invalid.(01) 2013-09-21 13:41:31 Slp: Exception type: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException(01) 2013-09-21 13:41:31 Slp: Message: (01) 2013-09-21 13:41:31 Slp: Failed to retrieve data for this request.(01) 2013-09-21 13:41:31 Slp: HResult : 0x80131500(01) 2013-09-21 13:41:31 Slp: Data: (01) 2013-09-21 13:41:31 Slp: HelpLink.ProdName = Microsoft SQL Server(01) 2013-09-21 13:41:31 Slp: HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink(01) 2013-09-21 13:41:31 Slp: HelpLink.LinkId = 20476(01) 2013-09-21 13:41:31 Slp: Stack: (01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.ProcessDTbl(DataTable dt, Int32 level)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectSqlDiscoveryData(String machineName)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectDiscoveryData(String machineName)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.LoadData(IEnumerable`1 machineNames, String discoveryDocRootPath, String clusterDiscoveryDocRootPath)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction.ExecuteAction(String actionId)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)(01) 2013-09-21 13:41:31 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)(01) 2013-09-21 13:41:31 Slp: Inner exception type: Microsoft.SqlServer.Configuration.Sco.SqlRegistryException(01) 2013-09-21 13:41:31 Slp: Message: (01) 2013-09-21 13:41:31 Slp: The handle is invalid.(01) 2013-09-21 13:41:31 Slp: (01) 2013-09-21 13:41:31 Slp: HResult : 0x84d10006(01) 2013-09-21 13:41:31 Slp: FacilityCode : 1233 (4d1)(01) 2013-09-21 13:41:31 Slp: ErrorCode : 6 (0006)(01) 2013-09-21 13:41:31 Slp: Data: (01) 2013-09-21 13:41:31 Slp: WatsonData = Microsoft.SqlServer.Configuration.Sco.SqlRegistryException@Win32ErrorOne interesting fact is, setups always works without an issue when I run it on an active node.
Posted by at No comments:

[T-SQL] T-SQl help...

[T-SQL] T-SQl help...

T-SQl help...

Posted: 21 Sep 2013 04:41 AM PDT

Hi, I have the sql sample code and the below image has the ouput needed.. thanks in advance...I`m working with RANKING functions but need some expertise...[code="sql"]IF OBJECT_ID('Tempdb..#tTable') IS NOT NULLDROP TABLE #tTableCREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))INSERT INTO #tTable(CID, CDate, Dept)VALUES(111, '2012-10-05 00:00:00.000','A'),(111, '2012-10-18 00:00:00.000','C'),(111, '2012-11-01 00:00:00.000','B'), (111, '2012-11-01 00:00:00.000','C'), (111, '2012-11-20 00:00:00.000','C'), (111, '2012-12-09 00:00:00.000','C'), (111, '2012-12-11 00:00:00.000','A'), (111, '2013-02-21 00:00:00.000','B'), (111, '2013-03-22 00:00:00.000','B'), (111, '2013-03-22 00:00:00.000','C'), (111, '2013-04-12 00:00:00.000','C'), (111, '2013-04-26 00:00:00.000','B'), (111, '2013-04-26 00:00:00.000','C'),(222, '2012-02-13 00:00:00.000','C'), (222, '2012-03-02 00:00:00.000','B'), (222, '2012-06-16 00:00:00.000','C'), (222, '2012-07-12 00:00:00.000','C'), (222, '2013-04-26 00:00:00.000','B'),(222, '2013-05-23 00:00:00.000','C'), (222, '2013-07-11 00:00:00.000','C'), (222, '2013-09-19 00:00:00.000','C'),(222, '2013-09-20 00:00:00.000','A')SELECT * FROM #tTableDROP TABLE #tTable[/code]The Groups are created based on CDate and Dept.For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.[img]http://www.sqlservercentral.com/Forums/Attachment14358.aspx[/img]
Posted by at No comments:

[SQL Server 2008 issues] SHA2 or SHA256

[SQL Server 2008 issues] SHA2 or SHA256

SHA2 or SHA256

Posted: 18 Oct 2010 06:47 AM PDT

Good Day,Whether SQL Server supports SHA2 or SHA256 in any of the available versions?If not what is the alternate?Thanks,RJ
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog