[SQL Server 2008 issues] Disk_Queue_Length is CRITICAL or high

---

• Disk_Queue_Length is CRITICAL or high
• Are my statistics being used?
• count(*) vs count(1) or count(id)
• why there are no sql server services listed in configuration manager
• display specific string and that positions in sql server
• Trouble with SQL Server Agent 2008
• Ports and Firewall changes for named Instance
• 2008 Central Management Server - Schedule Multi Server Query?
• How many packet errors are acceptable before I worry?
• MCM Free Videos - Demo Scripts from Paul/Kim/Brent/Ben
• Save all DTS pakges to location
• Odd Error Message
• Table Partitioning Scenario question.
• Assigning a fieldname with rollup
• SQL query to XML ???
• Does linking tables to Access change the size of SQL Server database?
• Unable to restore the database DatabaseName=*** INCOMPLETE ***
• RSClintPrint
• Checking for index in another database
• copying jobs history
• Blocking
• display specific string and display that position in sql server
• Restoring SQl SERVER 2008 R2
• Peer-to-peer replication
• Negative SPID -4
• Shrinking the log file does not reduce size
• SSIS - Excel 2010 - Provider=Microsoft.ACE.OLEDB.12.0
• Use of rownum function in a select
• Installing SQL Server Express 2008 & Studio on Windows 8
• SQL Script To Find Non Basic Latin
• Generating a counter table in SQL
• VIEW vs. DynamicSQL
• Discriminator Field

Disk_Queue_Length is CRITICAL or high Posted: 25 Apr 2013 04:58 AM PDT This is regarding the Cluster servers 723/722/721 3 Nodes (Active/Active/Passive). At around Fri Mar 29 11:05:42 EDT 2013, We have got alert from monitoring agent Nagios that "Disk_Queue_Length is CRITICAL" and during that time SQL stopped responding for a couple minutes and this happened about 3 times.Since that time I disabled the Nagios alert on that particular event.Storage Team/Backup Team/Wintel Team - saying that no issues found from their end.Windows Administration team says its everything normal on their side. SQL Team - Saying that when this issue happened that time there was no jobs were running on this cluster and no issues. Can any one suggest here where exactly issue is and how to resolve this issue.Thanks a million in advance.

Are my statistics being used? Posted: 30 Apr 2013 07:07 AM PDT Hi,I have a system with a lot of blocking. From the last occurrence I found a statistics update causing a lot of the blocking:[code="sql"]SELECT StatMan([SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], step_direction([SC0]) over (order by NULL) AS [SB0000] ...[/code]I went to the table in question and found a lot of statistics with the dta prefix meaning that someone had let DTA auto create a lot of statistics at some point.I want to verify that these are actually being used. If I have a bunch of unused statistics which are resulting in blocking whenever auto stats update runs, I'd like to drop the stats.Unfortunately I don't know how to check if my statistics are being used. Any ideas?

count(*) vs count(1) or count(id) Posted: 30 Apr 2013 05:01 PM PDT Dear Experts,I want to know what is difference between count(*), count(id) and count(1) base on performance issues.We have some quires with count(*) that sometimes throws timeout and we have many Transactions on these tables in our other queries.we guess that if we change it to count(id) or count(1) it will be better.Is this change effective? Any information will be helpful.Faithfully Yours,Ashkan

why there are no sql server services listed in configuration manager Posted: 30 Apr 2013 10:00 AM PDT In installed another named instance of same edition and version sql server 2008 r2 on a server.Now i can t see any services related sqlserver.and also the instance status button is white in color. I know it shows green when the server is running and red when the server is stopped..Any inputs please ?

display specific string and that positions in sql server Posted: 30 Apr 2013 02:32 PM PDT hi friend i have small doubt in sql server plz tell me how to solve thisi want display only specific string in based on table data suppose to disply 'm' string based on table and that positionnamesas programermms programermy name is ravikumariam sas programeriam good in sasbased on this i want output like this name , string , positionsas programer , m , 11mms programer , m , 1mms programer , m , 2mms programer , m , 11my name is ravikumar , m , 1my name is ravikumar , m , 6my name is ravikumar , m , 18iam sas programer , m , 3iam sas programer , m , 15iam good in sas , m , 3plz tell me how to wirte query in sql server to solve this issue.

Trouble with SQL Server Agent 2008 Posted: 30 Apr 2013 01:45 AM PDT Hello all. This is on a Windows 7 Pro machine. As I mentioned in an earlier post today, I am being thrown back into the IT world after being gone for many years. Much catching up to do on my part!! Anyways....I am trying to install and run on SQL Server 2008 Express and SQL Server 2008 Management Studio. I recently installed SQL Server 2008 Express (today) and we already had SQL Server Management Studio installed for some time now on the same machine. Management Studio is being used to connect to a remove SQL Server and is working fine.As I understand it, SQL Server Express installs and uses a couple of services called SQL Server Agent and SQL Server Browser. For some reason, SQL Server Agent cannot be started. Here's a screenshot of the SQL Server Configuration Manager:[img]http://www.justdrumsonline.com/Assets/images/image1.png[/img]When I look in Windows Event Viewer, I see this error recorded:[img]http://www.justdrumsonline.com/Assets/images/image2.png[/img]Finally, I've noticed that when I use and run SQL Server Management Studio 2008, SQL Server 2008 Express is not listed as a Server Type....[img]http://www.justdrumsonline.com/Assets/images/image3.png[/img]....nor is my local copy of SQL Server 2008 Express shown. The newly installed instance is called "SQLEXPRESSJD"...which if I'm not mistaken, should be showing up in this list.[img]http://www.justdrumsonline.com/Assets/images/image4.png[/img]One thing that does concern me is that in the picture above, the one that shows the Server Types, it shows SQL Server Compact Edition, not Express. I never installed "Compact" so I'm not sure why this is shown. I'm hesitant to remove it or uninstall it from the Control Panel because another vital programs like Quick Books or Point of Sale (Intuit programs) might need it.I would appreciate any help anyone can offer! I'll send $5 via PayPal to anyone who can get this working! ;-)

Ports and Firewall changes for named Instance Posted: 30 Apr 2013 09:34 AM PDT Hi All,What all ports should be opened for a named instance, which is listeneing on 1477.The named instance name is PDCZV\CZV01When security opened port number 1477, the developers are able to connect to this named instance using PDCZV,1477 but not PDCZV\CZV01Can someone please let me know this.Thank you

2008 Central Management Server - Schedule Multi Server Query? Posted: 08 Oct 2010 12:38 AM PDT Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL servers. I'm wondering if there's a way to do this via the command line or a job so the queries can be automated and rolled up into reporting services reports. Any suggestions?

How many packet errors are acceptable before I worry? Posted: 30 Apr 2013 12:17 PM PDT We collect network information and report on it daily. We show packets sent, received, and errors. Typically there are no errors and sent and received in the thousands. Yesterday I saw 3-5 packet errors total. How do I know when to consider this an issue with networking?

MCM Free Videos - Demo Scripts from Paul/Kim/Brent/Ben Posted: 17 May 2012 10:04 PM PDT http://www.sqlskills.com/T_MCMVideos.aspI read a blog post from 01/2011 from Paul saying the scripts would be released through blog articles."[url=http://www.sqlskills.com/BLOGS/PAUL/post/All-MCM-free-training-videos-now-available.aspx]PS Some people have asked for the demo scripts from the videos - these will be blogged by us through the year[/url]."[b]Is there one file or a set of files I can download from sqlskills.com that have all the demo scripts used in the videos?[/b]I'm currently reading on the Resource Governor and creating custom resource pools. I didn't find a blog post on sqlskills that show the scripts Paul used. Are these scripts available? Googling site specific to sqkskills didn't reveal anything. Widening my search didn't either. I'm getting up there in age, so if any of you youngsters can help and old man, I'd appreciate it. ;-)

Save all DTS pakges to location Posted: 30 Apr 2013 08:51 AM PDT Hi I found this script for saving all DTS pkges.Can someone help me in sacving all the DTS t0 H:\DTS location--------------------------------------------------------------if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[s_SavePackages]GOCreate procedure s_SavePackages@Path varchar(128)as/**/ set nocount ondeclare @objPackage intdeclare @PackageName varchar(128)declare @rc intdeclare @ServerName varchar(128)declare @FileName varchar(128)declare @FilePath varchar(128)declare @cmd varchar(2000) select @ServerName = @@ServerName , @FilePath = @Path if right(@Path,1) <> '\' begin select @Path = @Path + '\' end -- create output directory - will fail if already exists but ... select @cmd = 'mkdir ' + @FilePath exec master..xp_cmdshell @cmd create table #packages (PackageName varchar(128)) insert #packages (PackageName) select distinct name from msdb..sysdtspackages select @PackageName = '' while @PackageName < (select max(PackageName) from #packages) begin select @PackageName = min(PackageName) from #packages where PackageName > @PackageName select @FileName = @FilePath + @PackageName + '.dts' exec @rc = sp_OACreate 'DTS.Package', @objPackage output if @rc <> 0 begin raiserror('failed to create package rc = %d', 16, -1, @rc) return end exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName if @rc <> 0 begin raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end -- delete old file select @cmd = 'del ' + @FileName exec master..xp_cmdshell @cmd, no_output exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName if @rc <> 0 begin raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end exec @rc = sp_OADestroy @objPackage endgo

Odd Error Message Posted: 07 Mar 2012 07:32 PM PST MorningJust going throught my 2012 SSMS to a 2008r2 server, and I got the following error message, which I've never seen before.[b]Cannot display policy health state at the server level, because the user doesn't have permission. Permission to access the msdb database is required for this feature to work correctly.[/b]which is a bit odd as I'm the sysadmin on the box. Once I've clicked through I seem to be able to do all I need to do on the box.I can find nothing online, and when clicking the link for more help, it comes back with no information in the Knowledge base at this time.Anyone got any ideas?Tapete

Table Partitioning Scenario question. Posted: 30 Apr 2013 06:05 AM PDT Hi Experts,Migrating warehouse from Oracle to SQL Server : Status: In processIn Oracle : 6 SchemasIn SQL Server: Created a database with 6 Schemas.-Every Schema has 3 Paritioned and 3 non partitioned tables.-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.-Only parent table has date column and non other table has date column for partitioning.-all the 5 tables in each schema has primary key.My Question is can I partition parent table with date column and child tables with Primary key column ?If so please can someone advise the partitioning method for a parent table and child tables.sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..Thanks for all your time.

Assigning a fieldname with rollup Posted: 16 Apr 2013 04:26 AM PDT I have two tables that have racing data, and I need to add up the number of cancelled races and make it my aliased field name (in this case as you see the query below numCancelled, meaning number of races cancelled):(select a.eid, count(*) as numCancelled from(select ev4.eid,f.nvid, f.perfdate, f.racenumber from Finish f (nolock)join event ev4 (nolock) on ev4.eid = f.eid wheref.NoRace = 'true' and f.PerfDate = '2013-04-11') a group by a.eid with rollup)....the resultset here just lists all the eids (event ids) then the rollup total (in this case it is referred to a NULL in that eid column), but I need my numCancelled field to contain that grand total 7, as right now it comes back as 1 when I run this subquery in my stored procedure.??Z

SQL query to XML ??? Posted: 30 Apr 2013 12:43 AM PDT Hi,I',m new to XML and here is what I need to do...I want to create a SQL query to pull data and update a field on a table with current date and another field an "X"I need the data in the select to be written to an XML.I then need the XML file emailed,Once emailed I need to run another query to update the 'X" to 'Y' showing its been sent...I assume I can do this from some job steps?I really don't know where to start??ThanksJoe

Does linking tables to Access change the size of SQL Server database? Posted: 30 Apr 2013 05:09 AM PDT I noticed that the size of db copy with linked tables is 6 times higher than the original one. Copy has the same tables with the same records as the production db. Is this because of Access linking? Size (Mb) Space Available (Mb)Production Db 341 197Copy with linked table 1952 5ThanksVal

Unable to restore the database DatabaseName=*** INCOMPLETE *** Posted: 30 Apr 2013 02:27 AM PDT Dear expert,I did lots of googlings and most suggestions pointing to a corrupted database, but I still have some doubt about if the database is really being corrupted. A client created a backup of their database in SQL server 2005 and shipped to us from another state. When I tried to restore on SQL server 2008 R2 (I also tried on SQL server 2005 as well). Restoration failed. When I run the following query against the backup file, it returned databasename as "*** INCOMPLETE ***" and the rest of the fields are NULLs, except Position has a value of 1.[code="sql"]RESTORE HEADERONLY FROM DISK='C:\Data\Database\SQLBackup\ClientDBName.bak'GO[/code]When I ran the following query,[code="sql"]RESTORE VERIFYONLY FROM DISK = 'C:\Data\Database\SQLBackup\ClientDBName.bak'GO[/code]I got this[code="plain"]Msg 3242, Level 16, State 2, Line 2The file on device 'C:\Data\Database\SQLBackup\ClientDBName.bak' is not a valid Microsoft Tape Format backup set.Msg 3013, Level 16, State 1, Line 2VERIFY DATABASE is terminating abnormally.[/code] I asked client resending me the backup copy with CHECKSUM option turned on but still no luck. I tried to restore with CONTINUE_AFTER_ERROR and still unsuccessful.Thanks in advance for any clue.

RSClintPrint Posted: 07 Aug 2012 07:05 AM PDT So we are in the process of converting over from Crystal to SSRS. Things seem to be going fine; however, we realize an issue today. Whenever the users try to print the first time, it tells them that you need to install SQL Server Reporting Services, which when you do, allows you to print just fine. The problem is, it's a pain because we have to log in as administrator, install this, log out and then let the user attempt to print the report again. I want to be able to just push this out to the whole network, as we continue to grow our SSRS, all users will need to print reports. I've done some research, but haven't really found a good solution to pushing this out. Please help, as I have converted a few departments over to the new reports, prior to realizing this was an issue.Thanks,Jordon

Checking for index in another database Posted: 30 Apr 2013 01:28 AM PDT Folks:How can i check for the presence of an index in another database ?Lets say I am in DATABASE A and i want to check for an object in database B. The following syntax does not work. I tried it.To make it even easier how can I check for the sys.indexes view in another database ?USE DATAEXCH;GOIF EXISTS ( SELECT name FROM sys.indexes WHERE name = N'inx_patient_encounter1' AND object_id = OBJECT_ID(N'NGPROD.dbo.patient_encounter')) Begin Print 'Index Found'EndelseBegin Print 'Index Not Found'End

copying jobs history Posted: 30 Apr 2013 02:45 AM PDT Is it ok to copy sysjobhistory table from one server to another or is there any pitfalls

Blocking Posted: 30 Apr 2013 01:21 AM PDT Hi,We are running one job which runs SELECT query from a view from another linked server. We are getting blocking in the linked server where SELECT query from View is causing the same. The view is written with NOLOCK. So I am confused, how SELECT query can be culprit blocker when using NOLOCK option?Please help.

display specific string and display that position in sql server Posted: 30 Apr 2013 01:47 AM PDT hi firends i have small doubt in sql server plz tell me how to solve to find the specific charter and that posittion. here i want find sas string .which position its there and diplay with sas and position in below tabletable data contains likenamesas programersas programermy name is raviiam sas programeriam good in sasbased on this i want out put like thispositionid name1 sas1 sas0 sas5 sas13 sas and i try this like select charindex('sas',name)from table .that time i find only postion of sas string but i want display name sas and that positionhow to solve this in sql server.plz tell me the query

Restoring SQl SERVER 2008 R2 Posted: 30 Apr 2013 12:52 AM PDT Hi Guys:Any guide how to restore sql server 2008 r2 backup on sql server 2000thanxsymun

Peer-to-peer replication Posted: 30 Apr 2013 01:23 AM PDT Question. if I insert a row on one server in the peer-to-peer topology, is it possible that an update is attempted on that same row on another server before that insert has been replicated over? Meaning is it synchronous or asynchronous?

Negative SPID -4 Posted: 30 Apr 2013 01:14 AM PDT Hello All,Can someone share some information on -4 SPID? I am aware about -2 and -3 but have never seen -4 and cant find any online resources regarding this. One of our client's server showed blocking by spid -4. They restarted the machine to resolve the issue so I dont have much info. However they had captured sysprocesses details which shows some processes were blocked by -4. I've asked for more details. Apparently they run some linked queries but as per my understanding that would result in -2 spid.Pls see attached.

Shrinking the log file does not reduce size Posted: 29 Apr 2013 10:23 PM PDT I have a database which had mdf size of 350 MB and ldf size 4.9 GBWhen i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. The recovery model is set to FULL.Then i followed some steps:When i run dbcc SQLPerf(logspace) i found that logsize is 4932 MB and Log space used is 98.76%So large amount of (98%) of log is using now.Then i run this command use <databasename> dbcc loginfoNow almost all VLF has status 2 which means all are in use.then I tried to take log backup. After log backup also shrinking didnt reduce the size. Then i changed recovery model to simple and then tried shrinking.But this also didn't help. Also i ran dbcc opentran (database)and found that no transaction is open now. So what is making the database which does not allow me to shrink the log size.How can i solve this?

SSIS - Excel 2010 - Provider=Microsoft.ACE.OLEDB.12.0 Posted: 30 Apr 2013 12:21 AM PDT I am requesting that the Microsoft Access Database Engine 2010 Redistributable be installed on my SQL 2008R2 Enterprise SP1 running on Windows Server Enterprise 2008R2 SP1 but am getting denied the request because in the page to download the drivers (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255) it states that:[i]The Access Database Engine 2010 Redistributable is not intended: 4.To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.[/i]The requirement is to be able to run an SSIS package that interrogates various MS Excel 2010 workbooks before converting them to csv. The conversion to csv is just a side line so please do not be distracted by that, rather focus on the need to be able to look at the excel tables and other content. The package should run on a SQL Agent job through a proxy and credential with the credential running under the context of a service account that itself will have very limited privilege however it maybe that the package will reside on the server as a physical file and executed directly from a front end under a regular user.Now because of this 'not intended' wording on the MS site my WINTEL support guys are denying my request but I have not been able to get any further information from them or in fact any where else as to why MS state this or what the issues might be. I just want a driver to be able to not just import from Excel but to dig deeper into the Excel file.Does anyone know why this driver set should not be installed or why MS make this statement.Thanks...

Use of rownum function in a select Posted: 29 Apr 2013 08:37 PM PDT HiI need to get the first row from a subselect. Have read a couple posts on this topic, but not sure I get it.Please find below the code.What I need is that the route subselect only returns the first record for every route. SELECT (SELECT OM.ord_route, [color=#F90A0A]rownum r[/color] FROM Trans T, Order OM WHERE T."From license number." = T1."From license number." AND T."Transaction type." = 014 AND OM.ord_key = T.ORD_KEY AND (T."Date record was created." > T1."Date record was created." and T."Date record was created." < T1."Date record was created." + 1) [color=#F90A0A]and r = 1[/color] ) as route, T1.TO_ITEM, T1.FROM_LOCATION, T1.TO_LOCATION,...............FROM Trans as T1 WHERE T1."From license number." like '4%'Any ideas?BRDan

Installing SQL Server Express 2008 & Studio on Windows 8 Posted: 29 Apr 2013 11:46 PM PDT Hello all. My first post here. My question probably will seem amateur as I'm sure I is. I've been hired for a job based on my very old, rusty SQL Server skills. I have quiet a bit of experience using and developing with SQL Server 2000. Yep. 13 years ago.I'm trying to brush up on my skills and hit the books hard. I've purchased a new laptop running Windows 8 (ugggh...don't like it!!) and my intention was to install SQL Server 2008 Express and SQL Server Management Studio 2008 on this laptop so I can practice along with the chapters I'm reading. I'm using 2008 since that's what the project I'll soon be working on uses.Last night I was able to install SQL Server Express on this laptop (SP3 I think) but had no luck installing SQL Server 2008 Management Studio.Has anyone been able to use these two products on Windows 8?Or would you recommend I go to the SQL Server 2012 products?Thanks for helping out an old dog learn new tricks!:-)

SQL Script To Find Non Basic Latin Posted: 29 Apr 2013 11:27 PM PDT I'm having issues with Invalid Unicode charters in my 2008 DB, caused by people using copy and paste into the program front end and picking up carriage returns.I'm still learning sql and this is a little beyond me.Any ideas for a script that would search entire Database and locate any 'non basic Latin' characters and there locations so I can remove them.Many thanks in advance.CheersKev

Generating a counter table in SQL Posted: 29 Apr 2013 09:54 PM PDT Hi All,I am currently looking at a project that requires acounter table - I have done this very easilly in Excel with a VBScript - but want to do it with a SQL Script.Any thoughts ?[b]Sub updatevalues()Dim TotalDim CounterDim rowDim RecordNumberRecordNumber = 1Total = 1Counter = 1row = 2For Total = 1 To 300 For Counter = 1 To Total Sheets("Sheet1").Cells(row, 1).Value = RecordNumber Sheets("Sheet1").Cells(row, 2).Value = Total Sheets("Sheet1").Cells(row, 3).Value = Counter row = row + 1 RecordNumber = RecordNumber + 1 Next CounterNext TotalEnd Sub[/b]

VIEW vs. DynamicSQL Posted: 29 Apr 2013 08:04 PM PDT Hello,I have few questions, I would like remove DSQL, but our design table is not good, but redesign table is for future, because few programs ( korean source, sometimes we dont have source... long story) still use direct statement in source.So now we have separate table, but there is column line_code I dont know who designed this - Korean :Dso my questions is for someone who understand about query optimazing ...here is declare of one table , there is different just end of table for example CPM01, CPM02, according to help by opc.three I added check constraint for line code [quote]USE [MCS_MESDB]GO/****** Object: Table [dbo].[TB_MASTER_CPM01] Script Date: 4/30/2013 10:39:11 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TB_MASTER_CPM01]( [ORDER_DATE] [char](8) NOT NULL, [COMMIT_NO] [char](4) NOT NULL, [BODY_NO] [varchar](12) NOT NULL, [CAR_CODE] [varchar](3) NOT NULL, [LINE_CODE] [char](5) NOT NULL, [STATION_ID] [char](5) NOT NULL, [WORK_SEQ] [int] NOT NULL, [POINT_SEQ] [int] NOT NULL, [POINT_SER] [int] NOT NULL, [WORK_TYPE] [varchar](2) NOT NULL, [ITEM_CODE] [char](5) NULL, [ALC_CODE] [varchar](4) NULL, [WORK_CODE] [varchar](6) NOT NULL, [WORK_POS] [char](1) NOT NULL, [WORK_QTY] [int] NOT NULL, [LIMITV_LOW] [decimal](5, 2) NULL, [LIMITV_HIGH] [decimal](5, 2) NULL, [OPTION_VALUE] [varchar](20) NULL, [MASTER_DESC] [varchar](50) NULL, CONSTRAINT [PK_TB_MASTER_CPM01] PRIMARY KEY CLUSTERED ( [ORDER_DATE] ASC, [COMMIT_NO] ASC, [BODY_NO] ASC, [CAR_CODE] ASC, [LINE_CODE] ASC, [STATION_ID] ASC, [WORK_SEQ] ASC)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]GOALTER TABLE [dbo].[TB_MASTER_CPM01] WITH CHECK ADD CONSTRAINT [check_CPM01] CHECK (([LINE_CODE]='CPM01'))GOALTER TABLE [dbo].[TB_MASTER_CPM01] CHECK CONSTRAINT [check_CPM01]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM01', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'GO[/quote]here is view [quote]USE [MCS_MESDB]GO/****** Object: View [dbo].[VIEW_MASTER] Script Date: 4/30/2013 10:55:50 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VIEW_MASTER] WITH SCHEMABINDING AS SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVENUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFAUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVRCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPSB1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_ENG01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX02UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FEM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_RCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRFE3UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRSB1GO[/quote]And here I have few testing queries [quote]DECLARE @LINE_CODE VARCHAR(5)='CPM01'DECLARE @COMMIT_NO varchar(4)='0042'DECLARE @ORDER_DATE varchar(8)='20130301'DECLARE @STATION_ID varchar(5)='CP-08'DECLARE @sql varchar(2000)DECLARE @nsql nvarchar(2000)DECLARE @StartTime datetimeSET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID OPTION(RECOMPILE);SELECT ExecutionTimeInMSVieWVariableOPt = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWVariable = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE='CPM01' AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWwithoutVar = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQLView = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQL = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @sql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE='''+@ORDER_DATE+''' AND COMMIT_NO='''+@COMMIT_NO+''' AND STATION_ID='''+@STATION_ID+''''execute (@sql)SELECT ExecutionTimeInMSDynamic = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()select TOP 1 * from TB_MASTER_CPM01 WHERE ORDER_DATE=@ORDER_DATE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_IDSELECT ExecutionTimeInMSQuery = DATEDIFF(millisecond, @StartTime, getdate())[/quote]FOR TEST you can add to table just one row with parameteres of variable, another columns you can add random..And next I used this command for better result [quote]CHECKPOINT; GO DBCC DROPCLEANBUFFERS; DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGOselect plan_handle, creation_time, last_execution_time, execution_count, qt.textFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt[/quote]but the background of this I dont understand very well.very thank you for response

Discriminator Field Posted: 29 Apr 2013 07:39 PM PDT Hi could anyone tell me whya discriminator column would be added to a table?what is a discriminator column? what is it used for?thanks in advance

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Store proceure with multiple result sets

---

• Store proceure with multiple result sets
• Copy certain tables,stored procedures and views from one Database to another
• Random Number Generator
• Rollback in trigger
• Updated muliple rows into one column? Why is it hard? HELP

Store proceure with multiple result sets Posted: 29 Apr 2013 11:54 PM PDT Dear AllI have one procedure PROC1 wich resturn multiple result sets. No of coulmn and Datatype of the result set are not same.I want to access the result of PROC1 from another proceure PROC2. How do i do it?RegardsKrishna

Copy certain tables,stored procedures and views from one Database to another Posted: 30 Apr 2013 04:05 AM PDT Hello,I am new sorry, hope i can explain this right, I have an old database called database1, which now we have a new database called database2 (these are not the real names lol), database1 and database2 have the same table names, stored procedures etc, but database1 had foreign keys etc, what I want to know is there a way to copy over only certain database tables from one database to another, not copying the entire database but only the tables needed and restore them onto database2? is there a way for doing the same for certain stored procedures and/or views and not all only certain ones? I have SSIS but so far i can only see for data purposes...Thanks in advance

Random Number Generator Posted: 30 Apr 2013 12:11 AM PDT I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes with the only criteria being that each lane number cannot be within 5 of any lane within 3 spaces of it. So what I mean is if lane 4 has a number of 30 then lane 1-2-3 and lane 5-6-7 cannot be within 5 of lane 4's number. Hope this makes sense and any help would be sincerely appreciated....

Rollback in trigger Posted: 30 Apr 2013 12:38 AM PDT Dear AllI have read that if there is a rollback in the trigger then it rollback the transaction in the trigger plus the original dml statemt to which the trigger is attached.I tried this with folloing codedrop table agocreate table a( i int)gocreate trigger a_inserton afor insertasbegin declare @a as varchar(40) set @a = 'count of transaction' + CONVERT(varchar(10), @@TRANCOUNT) print @a rollbackendgoinsert into a (i) values (2)REsult of the above insert is as followscount of transaction1Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.why is it giving the error message?. How to avoid it?RegardsKrishna

Updated muliple rows into one column? Why is it hard? HELP Posted: 29 Apr 2013 10:05 PM PDT Trying to update a table using integer number in 3 columns and a signing texts name to results in the second table TEST2 It only updated that last update statament which was Volunteer. It should upadte with all the other columns from TEST1.I even try using update with subquery but I got a error to do with multipe values. And I got no idea how to do the[b] if else [/b]part of the sql if any other number come up.Thanks in advance!!Actual Results by using SQL Query below-[b]Event_Role[/b]VolunteerWanted Results-[b]Event_Role[/b]Speaker Speaker Speaker Speaker coordinatorcoordinatorcoordinatorcoordinatorVolunteerVolunteerVolunteerVolunteerOtherOtherOther[code="sql"]CREATE TABLE [dbo].[TEST1]( [Ispeaker] [int] NOT NULL, [Iscoordinator] [varchar](35) NULL, [Volunteer] [int] NULL,) ON [PRIMARY] GOInsert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);GO--create empty column in table2Select CAST(NULL AS VARCHAR(20)) AS Event_Roleinto dbo.TEST2UPDATE [dbo].[TEST2] SET Event_Role = 'Speaker'FROM dbo.TEST1Where Ispeaker = -1 UPDATE [dbo].[TEST2] SET event_role = 'Coordinator' FROM dbo.TEST1WHERE IsCoordinator = -1UPDATE [dbo].[TEST2] SET event_role = 'Volunteer' SELECT ID FROM DBTEAM.azeez.Participants WHERE IsVolunteer = -1 ---not sure how to create if else statment into the sql queryUPDATE [dbo].[TEST2] SET event_role = 'Other' SELECT ID FROM dbo.TEST1WHERE ? = >2 ------try subquery but does not work because of multipe values errorsUPDATE [dbo].[TEST2]tSET event_role = 'Speaker'WHERE t.id IN (SELECT ID FROM dbo.TEST1 WHERE IsVolunteer = -1) AND event_role IS NULL[/code]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] Lck_m_s suspended queries after running stored proc

---

• Lck_m_s suspended queries after running stored proc
• OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)
• PostgreSQL datasource URL?
• Deleted a file accidentally, I need to remove it's entry, I don't need it's data because it contains only indexes
• Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?
• How does OPTION (FAST 1) actually interact with a client?
• Are shards distributed randomly across machines in MongoDB or are they in order?
• UPDATE table based on the same table
• Publishing stored proc execution in transactional replication
• SQl server indexes file damaged, was on ramdisk!
• Multithreading caching in SQL CLR
• Identical subquery optimisation in an update
• PostgreSQL stored function that returns arbitrary resultset
• Restore SQL Server 2008 R2 Express from Description
• Using dynamic sql inside Oracle stored procedure
• Difference between accessing SQL Server by instance name and cluster name [closed]
• SQL Server: Worse performance on a new server
• Issues converting MyISAM table to InnoDB (auto column issue)
• Master updates Slave but not Master itself
• InnoDB tuning with 1G of ram limit
• Sharded key-value store using MongoDB
• How can I set Timeout by View, User or Role?
• mysql optimize table crash
• Is it possible to have extra tables in a Slave with MySQL Replication
• Replication issue - CREATE SELECT alternative?
• Unable to change engine to MyISAM in MySQL
• How to drop a DB2 instance when the instance owner was removed
• How to Convert Horizontal to Vertical Array?
• How to script out push subscription creation at the subscriber?
• Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress?

Lck_m_s suspended queries after running stored proc Posted: 30 Apr 2013 08:01 PM PDT Hi all can anyone advise if I am running cursor using a stores proc which do select and update. Then if I am running a process which at the same time may call the same stored proc multiple times causing this lck_m_s lock suspended queries issue. How do I tune the stored proc to resolve this issue? We tried removing the cursor and using simple select and update query also face the same issue. Please advise thanks

OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql) Posted: 30 Apr 2013 07:07 PM PDT I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash recovery? Also, if I were to omit the temp tablespace from the base backup, would that prevent proper backup recovery?

PostgreSQL datasource URL? Posted: 30 Apr 2013 03:27 PM PDT My first attempt to use PostgreSQL: I have the elephant icon on the top right: running on port 5432. I want to use LibreOffice as an interface: Connect to an existing database: PostgreSQL Datasource URL: ??? What should I put here? The database will be on the same computer where LibreOffice is running. Tried with localhost:5432 with no success. By the way, I did not create the database, yet. Can I do it from LibreOffice? Thank you. Platform: Mac OSX 10.7 - PostgreSQL 9.2.2 - LibreOffice 4.0

Deleted a file accidentally, I need to remove it's entry, I don't need it's data because it contains only indexes Posted: 30 Apr 2013 05:29 PM PDT I have a C# program to deal with a big database, I need the databas not to be the buttle-nick of my program, in the database, to increase performance, I have created a filegroup with a single data file located on a ramdisk, I accidentally changed the size of the ramdisk, the file deleted, I thought that if I have removed all of the objects in that file group, I can rebuild those indexes, but when I try to remove the file group or the file or even when trying to add any file or file group, the current message will appear: Microsoft SQL Server Management Studio Alter failed for Database 'xxxxxx'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The operating system returned error 21(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x0000002c668000 in file 'R:\DBIndexes.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 823) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476 *UPDATE* When I detached the database, I couldn't re-attache it, it now saying: Unable to open the physical file "R:\DBIndexes.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". It was working fine before detaching it, but the small problem was in deleting that orphan file!!!

Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)? Posted: 30 Apr 2013 04:43 PM PDT This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question). Our legacy ERP was running on SQL Server 2000 EE and now it's on 2008 R2 EE. We're noticing many more blocked commands in the 2008R2 environment. The 2008R2 environment is running with trace flag 4199 and 1119. I noticed a difference between the MSDN documentation on hints in 2000) vs. 2008R2 where the FAST hint is expanded to note that the result will continue to be processed once the specified n rows had been returned. Is this just a more robust document, is this a change in behavior for this hint through the years, or something different?

How does OPTION (FAST 1) actually interact with a client? Posted: 30 Apr 2013 05:02 PM PDT This is question 1 of 2 related to OPTION (FAST 1); We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it down to what I believe to be the offending statement in the vendor's code which is: SELECT MAX(column) FROM [table] WHERE <condition> OPTION (FAST 1); The spid leaves an open transaction and takes a lock on the table, blocking all other clients. However, the calling client no longer seems to be interacting with the server to tell the server that it's received the data to end the session. Reading the documentation on Query Hints, I saw this statement FAST number_rows Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. This makes me wonder if the client has somehow broken communication, would the server keep the transaction open, processing the full result set after the first n rows are returned and leave the transaction open? The process is an internal process so I can't really watch an end-user execute the session to do it, and this is not something that happens every time the internal process occurs. However, it is only ever used by the internal process. Having read Remus' answer on SO it seems like it is overkill for the simplicity of the query. Looking at the query, if they're receiving more that one result from an ungrouped MAX then something's very fishy. So, as I prepare to work with the vendor, I was wondering if I could begin to accurately pin our blocking issues on the fact that this query hint is being used. Please feel free to edit/request edits as I know this may actually be unclear.

Are shards distributed randomly across machines in MongoDB or are they in order? Posted: 30 Apr 2013 02:17 PM PDT Are shards distributed randomly across machines in MongoDB or are they in order? For example, if I had a MongoDB cluster that was sharded across two machines and my shard key was a person's name, would names starting with 'a-m' be on the first machine (or replica set) and names starting with 'n-z' be on the second machine? Thanks so much!

UPDATE table based on the same table Posted: 30 Apr 2013 02:02 PM PDT I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same product_id where the language_id = 1. So far I've tried the following query, but I'm getting errors indicating that MySQL doesn't want to update a table where the table's also being used in the subquery. UPDATE products_description AS pd SET pd.products_seo = (SELECT pd2.products_seo FROM products_description AS pd2 WHERE pd2.language_id = 1 AND pd2.products_id = pd.products_id) WHERE pd.language_id <> 1 Is there a "simple" way around this limitation in MySQL? Or any "tricks"? I'm a little surprised that my query doesn't work, as it seems logical.

Publishing stored proc execution in transactional replication Posted: 30 Apr 2013 02:29 PM PDT In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored procedure, wrap the update statement inside the stored proc and add this stored procedure for the replication? By doing this when stored proc executed it will replicate the execution of the stored procedure. My question is how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?

SQl server indexes file damaged, was on ramdisk! Posted: 30 Apr 2013 03:17 PM PDT I have put some of my indexes into a file-group that contains one file, that file is on the ramdisk, the performance goes fire!! but the problem is that the file has deleted by wrong (The file contains only index information), I tried to delete that file from database properties, but an error occured: Microsoft SQL Server Management Studio Alter failed for Database 'xxxxxx'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The operating system returned error 21(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x0000002c668000 in file 'R:\DBIndexes.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 823) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476 I have no problem to re-build the indxes, but I need to fix the database!! I deleted all of the indexes that stores in that file, And tried to delete the file group, but the same error occured *UPDATE* When I detached the database, I couldn't re-attache it, it now saying: Unable to open the physical file "R:\DBIndexes.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". It was working fine before detaching it, but the small problem was in deleting that orphan file!!!

Multithreading caching in SQL CLR Posted: 30 Apr 2013 12:40 PM PDT Is there any mechanism available to a SQLCLR assembly for caching that will still work when the assembly is registered as "safe"? The cache needs to be thread-safe, so it can be shared by multiple SQL Server pids. The cache needs to be held in memory for performance - tempdb is not fast enough. The assembly can't be registered as "unsafe". See this post on StackOverflow for further details. (Sorry for the cross-post, not sure how else to rephrase the question for this site).

Identical subquery optimisation in an update Posted: 30 Apr 2013 11:46 AM PDT update activitybooking set `submitted`='1' where id='958' and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool1_count) < 10 and (select SUM(pool2_count) from (select pool2_count from `activitybooking` where `abt`='12' and (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool2_count) < 5 and (select SUM(pool3_count) from (select pool3_count from `activitybooking` where `abt`='12' and (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool3_count) < 20 The statement above has three identical subqueries and seems to execute them independently. How can I best rewrite the update to perform the subquery only once? (The idea of this query is to only perform the update if doing so would not cause the sum of pool1_count to exceed the maximum allowed pool1_count and the same for pool2, pool3 ....)

PostgreSQL stored function that returns arbitrary resultset Posted: 30 Apr 2013 03:10 PM PDT I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and have it return that resultset without having to specify the format of the output and change that definition every time I update the query. Is this even possible in PostgreSQL ? I've tried the following using PostgreSQL 9.2: CREATE OR REPLACE FUNCTION test() RETURNS SETOF record Which gives me the following error: ERROR: a column definition list is required for functions returning "record" I've also tried: CREATE OR REPLACE FUNCTION test() RETURNS table () but apparently that's invalid syntax.

Restore SQL Server 2008 R2 Express from Description Posted: 30 Apr 2013 06:09 PM PDT When backing up SQL Server we have the opportunity to input a description of the backup. Yet non of the Microsoft tools will show that description for restoring. Is there a way for me to review the description of the backup to select that one? Thanks.

Using dynamic sql inside Oracle stored procedure Posted: 30 Apr 2013 09:38 AM PDT Assuming I have the following procedure CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type, table1_val IN TABLE1.table1_value%type) AS SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2'; BEGIN --..... --1 : EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id; --2 : UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id; END; Beside the style/readability, is it any performance penalty for using dynamic query (1) compared to (2) in such cases (I mean when it's absolutely avoidable) ? Thank you.

Difference between accessing SQL Server by instance name and cluster name [closed] Posted: 30 Apr 2013 05:12 PM PDT I have installed failover clustering feature on two servers with Windows Server 2012 and then I installed SQL Server 2012 on both of them. I also enabled SQL Server 2012 AlwaysOn feature. Now I can access each node by instance name. I can also connect using failover cluster name. What is the difference between accessing SQL Server by instance name and cluster name?

SQL Server: Worse performance on a new server Posted: 30 Apr 2013 02:46 PM PDT We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the new server is slightly worse than the old, slower server. In testing, our ASP.NET application runs 10 - 20% slower. Running individual expensive queries with STATISTICS IO and STATISTICS TIME shows 10 - 20% greater elapsed time on the new server. SQL Query Profile shows higher CPU usage on expensive queries. Task Manager on the new server shows sqlserver.exe is consuming 22 GB of RAM, but the CPU values always stay very low. I've updated all statistics, rebuilt or reorganized indexes, etc. Execution plans should be stored on the new server at this point, given the amount of testing I've done. If there are any missing indexes (I don't think there are) they affect the old and new servers equally. New has a restored backup of the same data on the old. I'd expected that the performance on the new server would be better, but of more concern is load. If the old server is performing better even under load, then what will happen when this new, slightly worse server has to take that load? What else could I be missing here? EDIT: MAXDOP set to 6. Old server has OS, databases, and tempdb's on the same physical drives (RAID 10). Total of 4 15k 3 Gb/s 3.5 inch SAS. New server has three drive sets: OS on RAID 1, database on RAID 10, tempdb on RAID 5. Total of 9 15K 6 Gb/s 2.5 Inch SAS. Old server has 1 x Intel Xeon E5620 2.40 GHz Quad-Core 8 Threads (w H/T). New server has 2 x Intel Xeon E5-2640 2.5 GHz Six -Core 12 Threads (w H/T).

Issues converting MyISAM table to InnoDB (auto column issue) Posted: 30 Apr 2013 09:51 AM PDT I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `companies` ( `uid` int(20) NOT NULL, `cid` int(20) NOT NULL AUTO_INCREMENT, `cname` varchar(500) NOT NULL, `rfc` varchar(20) NOT NULL, `address` varchar(1000) NOT NULL, `dbUseExternal` tinyint(1) NOT NULL DEFAULT '0', `dbHost` varchar(50) NOT NULL, `dbPort` varchar(50) NOT NULL, `dbUser` varchar(50) NOT NULL, `dbPass` varchar(50) NOT NULL, `dbSSL` varchar(50) NOT NULL, `dbDriver` varchar(50) NOT NULL, `dbName` varchar(50) NOT NULL, `status` int(10) NOT NULL, PRIMARY KEY (`uid`,`cid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; It works as MyISAM. But, if I try to convert it to InnoDB (or if I try editing this dump to insert it on the command line as a sql file), I get the following error: Incorrect table definition; there can be only one auto column and it must be defined as a key I understand that error - or at least I thought I did. I'm not really using more than one AUTO_INCREMENT column and it is defined as primary key. Also, the information I've found regarding the error is always because of an obvious missing key or a duplicate AUTO_INCREMENT definition. One more thing I see generally commented is that the same is true for MyISAM and InnoDB. So, why does it work for MyISAM and not for InnoDB? Thanks in advance for any comments. Francisco

Master updates Slave but not Master itself Posted: 30 Apr 2013 12:46 PM PDT I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color. After reboot I see the master went for recovery then it came up and application started to use the Master normally but still the err msg exist in Front indication panel. But now the slave had stuck out due to primary key constraint for a table. The problem I face is master has a table data until what exist in binlog of master. But slave has the table data what is not in master binlog. Below is the table details when compared to Master table and Slave table. Last info in binlog timestamp is 2013-04-29 02:13:11 System shut down timestamp 2013-04-29 02:54 mysql> select * from audit where id=11298907; ------------------------------------------------------------ | id | ipaddress | dated | msisdn | +----------+----------------+------------------------------- |11298907 | 82.25.226.183 | 2013-04-29 02:13:11 | 998282821| ----------------------------------------------------------- mysql> select * from audit where id > 11298907 limit 1; ----------------------------------------------------------- | id | ipaddress | dated |msisdn | +----------+----------------+------------------------------ |11298908 | 82.25.226.183 | 2013-04-29 04:31:13 | 992828111| ------------------------------------------------------------ On Slave ========== mysql> select * from audit where id=11298907; ------------------------------------------------------------ | id | ipaddress | dated | msisdn | +----------+----------------+------------------------------- |11298907 | 82.25.226.183 | 2013-04-29 02:13:11 | 998282821| ----------------------------------------------------------- mysql> select * from audit where id > 11298907 limit 1; ----------------------------------------------------------- | id | ipaddress | dated |msisdn | +--------+----------------+--------------------------------- |11298908 | 82.25.226.183 | 2013-04-29 02:13:12 | 762616173| ------------------------------------------------------------ Both the servers are ntp synched with GST. And they remain same after Master reboot too. It took almost 2 hrs to bring up Master Server. How come only slave can have the data that Master is not aware of? Please have someone come accross such a thing?

InnoDB tuning with 1G of ram limit Posted: 30 Apr 2013 09:42 AM PDT I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit the query based on input or have multiple queries if needed. X = 1st moving average term (N day moving average. Any number 1-N) Y = 2nd moving average term. (N day moving average. Any number 1-N) Z = Amount of days back from present to search for the occurance of: option = Over/Under: (> or <. X passing over Y, or X passing Under Y) X day moving average passing over OR under Y day moving average within the past Z days. My database is structured: daily_data id stock_id date adj_close And: stocks stock_id symbol I have a btree index on: daily_data(stock_id, date, adj_close) stock_id We are creating an open stock analytic system where users can perform trend analysis. I have a database containing 3500 stocks and their price histories going back to 1970. This query will be running every day in order to find stocks that match certain criteria for example: 10 day moving average crossing over 20 day moving average within 5 days 20 day crossing UNDER 10 day moving average within 5 days 55 day crossing UNDER 22 day moving average within 100 days But each user may be interested in a different analysis so I cannot just store the moving average with each row, it must be calculated. The following query has yet to return anything to me after MANY hours of being run: SET @X:=5; SET @Y:=3; set @Z:=10; set @option:='under'; select stock_id from ( SELECT stock_id, datediff(current_date(), date) days_ago, adj_close, ( SELECT AVG(adj_close) AS moving_average FROM daily_data T2 WHERE ( SELECT COUNT(*) FROM daily_data T3 WHERE date BETWEEN T2.date AND T1.date ) BETWEEN 1 AND @X ) move_av_1, ( SELECT AVG(adj_close) AS moving_average FROM daily_data T2 WHERE ( SELECT COUNT(*) FROM daily_data T3 WHERE date BETWEEN T2.date AND T1.date ) BETWEEN 1 AND @Y ) move_av_2 FROM daily_data T1 where datediff(current_date(), date) <= @z ) x where case when @option ='over' and move_av_1 > move_av_2 then 1 else 0 end + case when @option ='under' and move_av_2 > move_av_1 then 1 else 0 end > 0 order by stock_id, days_ago I am currnetly running Mysql - Innodb but am open to other engines (Postgres/Oracle maybe?) if there would be a significant speed increase. The main problem is I don't have much hardware available for this. Currently I am trying to run it on a 1gig ram, virtual private server running Ubuntu. I have my own desktop I could try and run this on, 8 core CPU/16gig ram/ssd but its Windows only. Any guidance on how to improve the query / what to run it on would be very helpful.

Sharded key-value store using MongoDB Posted: 30 Apr 2013 10:51 AM PDT Would like to set up a key-value store that is sharded across multiple machines. We are currently using MongoDB, is there a reason why we shouldn't use MongoDB for this purpose? We also use Redis, however for this use case, we would like to use the hard drive and Redis is in-RAM only.

How can I set Timeout by View, User or Role? Posted: 30 Apr 2013 11:54 AM PDT For SQL 2008 R2, data views. Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query. I have been unable to find timeout controls by View, User or Role using SSMS. There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits). I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this http://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client" Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application. I have control over what is in the Data View Query, and any SQL settings that do not impact the main application. I have control over the User Permissions that can access the data view I do not have control over external report application(s) Snapshot isolation is not an option (application considerations) The hints NOLOCK & READPAST are not viable solutions in SQL 2008 R2 http://stackoverflow.com/questions/15770402/sql-2008-nolock-vs-readpast-considerations-for-reporting-accuracy

mysql optimize table crash Posted: 30 Apr 2013 12:52 PM PDT When I try OPTIMIZE TABLE `table` (MyISAM) on a table which is about 300MB, then it is crashed and must be repaired. What could cause this problem? The same problem occurs on other tables over 300MB.

Is it possible to have extra tables in a Slave with MySQL Replication Posted: 30 Apr 2013 01:04 PM PDT As my title mention I have a Master and a Slave database. Master if for operations data and my slave mainly for reporting stuff. The issue is that I need to create extra tables on reporting that can't be on the master, but the way my replication is set (the simplest one mentioned by the official doc) at the moment, this breaks the replication system. How could I add tables on the Slave without Master caring about it ? Is it even possible ?

Replication issue - CREATE SELECT alternative? Posted: 30 Apr 2013 02:04 PM PDT I've an MySQL 5.1 slave for our BI team. They need to make some CREATE SELECT with big select queries (several million lines). As CREATE SELECT is a DDL, if the replication attempts to update some rows in same tables than the SELECT statement, replication is blocked until the freeing of the CREATE SELECT. Do you now a good non-blocking alternative to thoses CREATE SELECT statements? I thought to an SELECT INTO OUTPUT FILE then LOAD DATA INFILE but they will fill out our disks as BI guys like to do... :) Max.

Unable to change engine to MyISAM in MySQL Posted: 30 Apr 2013 09:58 AM PDT I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine. I have checked following engines are supported in MySQL: show engines\G *************************** 1. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 9. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO I want to change it to MyISAM, so I have changed the setting in my.cnf: [mysqld] default-storage-engine=MyISAM After that I restarted MySQL from the system preferences. But if I check check the engines again and create a table, it is still being created with InnoDB, not MyISAM. Can anyone tell me how would I change it to MyISAM? Here is my my.cnf file content: # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] default-storage-engine=MyISAM # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # sql_mode=STRICT_TRANS_TABLES

How to drop a DB2 instance when the instance owner was removed Posted: 30 Apr 2013 10:04 AM PDT This is a real sticky situation. I was handed over a machine (running an AIX 7.1), and my first task was to re-install DB2 server on it. But someone before me had conveniently removed an instance owner account, and probably recreated it. Now, the problem is this: 1) When I try to uninstall DB2, it says the instance is active and has to be dropped first. 2) When I try to drop this instance, DB2 says there is no such instance. I am quite new to DB2 administration. Not sure how to proceed here. Any help is appreciated Thanks

How to Convert Horizontal to Vertical Array? Posted: 30 Apr 2013 11:04 AM PDT I need to create a query (suitable for Standard Edition) that has data from multiple columns (Columns 1-6 with corresponding Date Started and Date Completed data) displayed vertically, but also has the column name in the preceding column to identify it, along with other data (Record Number, Status). Sample data: +--------------+--------+------------+-------------+---------------+ | RecordNumber | Status | ColumnName | DateStarted | DateCompleted | +--------------+--------+------------+-------------+---------------+ | 1 | Open | Column 1 | 2012-01-01 | 2012-02-01 | | 2 | Hold | Column 2 | 2012-01-03 | 2012-03-01 | | 1 | Open | Column 3 | 2012-02-05 | 2012-04-06 | | 3 | Closed | Column 4 | 2012-05-10 | 2012-07-25 | | 2 | Hold | Column 5 | 2012-03-09 | 2012-04-01 | | 1 | Open | Column 6 | 2012-10-10 | 2012-12-12 | +--------------+--------+------------+-------------+---------------+ DECLARE @Data AS TABLE ( RecordNumber integer NOT NULL, [Status] varchar(10) NOT NULL, ColumnName varchar(10) NOT NULL, DateStarted date NOT NULL, DateCompleted date NOT NULL ); INSERT @Data ( RecordNumber, [Status], ColumnName, DateStarted, DateCompleted ) VALUES (1, 'Open', 'Column 1', '20120101', '20120201'), (2, 'Hold', 'Column 2', '20120103', '20120301'), (1, 'Open', 'Column 3', '20120205', '20120406'), (3, 'Closed', 'Column 4', '20120510', '20120725'), (2, 'Hold', 'Column 5', '20120309', '20120401'), (1, 'Open', 'Column 6', '20121010', '20121212');

How to script out push subscription creation at the subscriber? Posted: 30 Apr 2013 04:05 PM PDT I'm trying to set up a push subscription to a SQL Server publication from the subscriber. I could set up the subscription at the publisher using the Replication Wizard in Management Studio. However, I would prefer to script the process relative to the subscriber so I can automate the deployment of a new SQL Server subscriber instance. Initially, I'm happy to prompt for the name of the publisher before deployment. If I can get this working, I will look for a way to inject the correct value for my environment automatically. What is a simple way to do this for a SQL Server instance that has to create multiple subscriptions at different publishers? I'm open to using any supported SQL Server scripting solution: SMO, RMO, Sqlcmd, WMI, PSDrive, even pure T-SQL. I've attempted to solve this problem in two ways. The first is a complete solution using T-SQL, but it involves some manual steps. Using T-SQL I have a manual solution in T-SQL. The solution is based on the output of the Management Studio Replication Script Generator output. Using Management Studio, I run the following script to generate a T-SQL script that I can run at the publisher: PRINT N' EXECUTE MyDatabase.dbo.sp_addsubscription @publication = N''MyPublication'', @subscriber = ''' + CAST(SERVERPROPERTY('ServerName') AS SYSNAME) + ''', @destination_db = ''SubscriberDatabase'', @subscription_type = N''Push'', @sync_type = N''automatic'', @article = N''all'', @update_mode = N''read only'', @subscriber_type = 0; EXECUTE MyDatabase.dbo.sp_addpushsubscription_agent @publication = N''MyPublication'', @subscriber = ''' + CAST(SERVERPROPERTY('ServerName') AS SYSNAME) + ''', @subscriber_db = ''SubscriberDatabase'', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N''Distributor'';'; On the MYSUBSCRIBER instance, the output would look like this: EXECUTE MyDatabase.dbo.sp_addsubscription @publication = N'MyPublication', @subscriber = 'MYSUBSCRIBER', @destination_db = 'SubscriberDatabase', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0; EXECUTE MyDatabase.dbo.sp_addpushsubscription_agent @publication = N'MyPublication', @subscriber = 'MYSUBSCRIBER', @subscriber_db = 'SubscriberDatabase', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'; I copy the output and execute the script at the publisher instance to set up the subscription. I think I can't automate this in pure T-SQL without editing the script before running it, because T-SQL by design does not handle user input. Using PowerShell and RMO PowerShell has simple ways to process user input, so this seems like a good way to prototype the automation process. MSDN has an eight-step guide to set up a push subscription using the .NET Replication Management Objects (RMO). Here are the first two steps: Create a connection to the Publisher by using the ServerConnection class. Create an instance of the TransPublication class by using the Publisher connection from step 1. Specify Name, DatabaseName, and ConnectionContext. I'm trying to translate these steps into a PowerShell script, but I can't get past step 2. In the following code examples, I use fictional object names. I believe this does not affect the answerability of the question because the error message is identical when I use the real object names. First attempt: setting the properties My first attempt is to create the TransReplication object then set its properties. The code looks like this: Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; $Publisher = New-Object Microsoft.SqlServer.Management.Common.ServerConnection MyServer $Publication = New-Object Microsoft.SqlServer.Replication.TransPublication $Publication.Name = 'MyPublication' $Publication.DatabaseName = 'MyDatabase' $Publication.ConnectionContext = $Publisher When I execute this script, I see the following error: Exception setting "ConnectionContext": "Cannot convert the "server='(local)';Trusted_Connection=true;multipleactiveresultsets=false" value of type "Microsoft.SqlServer.Management.Common.ServerConnection" to type "Microsoft.SqlServer.Management.Common.ServerConnection"." At line:8 char:14 + $Publication. <<<< ConnectionContext = $Publisher + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyAssignmentException It looks like it's failing becuase it can't convert the type ServerConnection to the type ServerConnection. I don't understand how this could fail for the stated reason, because the value is already of the required type. Second attempt: overloading the constructor My second attempt is to specify the property values of the TransReplication object in the constructor. The code looks like this: Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; $Publisher = New-Object Microsoft.SqlServer.Management.Common.ServerConnection MyServer $Publication = New-Object Microsoft.SqlServer.Replication.TransPublication 'MyPublication', 'MyDatabase', $Publisher When I execute this script, I see the following error: New-Object : Cannot find an overload for "TransPublication" and the argument count: "3". At line:5 char:26 + $Publication = New-Object <<<< -TypeName Microsoft.SqlServer.Replication.TransPublication 'MyPublication', 'MyDatabase', $Publisher + CategoryInfo : InvalidOperation: (:) [New-Object], MethodException + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand It looks like the New-Object cmdlet can't find the three-argument constructor documented by MSDN: public TransPublication( string name, string databaseName, ServerConnection connectionContext ) Parameters name Type: System.String A String value that specifies the name of publication. databaseName Type: System.String A String value that specifies the name of the publication database. connectionContext Type: Microsoft.SqlServer.Management.Common.ServerConnection A ServerConnection object that represents a connection to the Publisher or to the Distributor for a non-SQL Server Publisher. As far as I can tell, I'm overloading the constructor correctly. Am I doing something wrong? Is there something unusual about my environment? Am I better off using another solution?

Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress? Posted: 30 Apr 2013 03:04 PM PDT I'm trying to monitor the progress of a sqlite3 command importing a large .sql file into a database using zenity --progress. I've tried the following which will import the file, however progress is not shown: sqlite3 DATABASE < import_file.sql | zenity --progress --percentage=0 --auto-close I know I need to provide zenity a numeric source for the progress, but don't really know how to obtain the number. Can anyone help me?

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL server issues] Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]

Hello,
Is this been taken care? If so, please share the latest data for DimDate. I'm working on some POC which requires dates in between 2008 and 2010 as well. If it is not already done, can someone let me know is there is any way I can generate the data for those missing dates in that table?

.

sqlserversamples.codeplex.com

[SQL server issues] Created Issue: New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed [17881]

description

New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed..
how the OLTP data has been extracted to AdventureworksDW..datawarehouse system and need some design documents....what to know how the DIM tables are created...and everything

Sign in to add a comment or to set email notifications

.

sqlserversamples.codeplex.com

[SQL server issues] Created Issue: SQL Server 2008 Developer edition sample database install error [18174]

An unexpected error has occured.

description

I'm trying to install Adventureworks sample dB and i get an error.
I successfully downloaded the mdf file however when attach, add and click OK from SSMS I get the error:
 
Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

.

sqlserversamples.codeplex.com