Wednesday, September 25, 2013

[SQL Server 2008 issues] Indexes missing after restoring a database to a different server

[SQL Server 2008 issues] Indexes missing after restoring a database to a different server

Indexes missing after restoring a database to a different server

Posted: 24 Sep 2013 07:20 PM PDT

Hello,I am a bit befuddled by a problem I have encountered.The scenario is, On Server A, database is backed up (full backup) , then transaction log backups every 15 minutes. The backup file is then copied to Server B and restored.The result is the database is apparently successfully restored to Server B, yet when digging a little deeper, it is apparent that some of the indexes that exist in User Tables in the original database (On Server A) do not exist in the restored version of the database (On Server B)If the same backup is restored to a new database on Server A, there iis no problem and all the indexes exist.Both servers are Microsoft SQL Server Standard Edition (64-bit), version 10.0.5500.0

TempDB - same files

Posted: 24 Sep 2013 09:18 AM PDT

Friends,I want to know if my datafiles (mdf and ldf) should be the same disk or disks distinct.ex:Example: 1 DISK T tempdb.mdf tempdbdev2.ndf tempdbdev3.ndf ... tempdb.ldfORExample 2: DISK T tempdb.mdf tempdbdev2.ndf tempdbdev3.ndf ... ANOTER DISK DISK K tempdb.ldfThanks.

Urgent Help Guys for this Scenario!!!

Posted: 24 Sep 2013 12:27 AM PDT

Hi, I have a table say emp which has two columns empid and empskills.I need a query to retrieve the empid which has both C and CPP.Input:Empid Empskills1 C1 CPP1 VB2 C2 CPP3 C4 CPPFrom the above input ,I need output like this(otherwise only empid)Empid Empskills1 C,CPP,VB2 C,CPPIf I use the below query,i ll get all the emp id.But I need only the empid which has both the given skills .Select distinct empid from empwhere empskills in ('C','CPP')Thanks in advanceThanks,Gopinath.

SQL Server downtimes

Posted: 24 Sep 2013 11:59 AM PDT

Hello team,Sorry if duplicated, is it possible to get ride of how many times an instance has been restarted? I need this for an Up-time report, so in a month I need to validate how many days or hours an instance was up.I know is very easy to get the time when the instance was last restarted, I can also check with the Xp_readerrorlog the details when an instance was shutdown, but I can't figure out how to get the report, is probably tricky.Any help or comments will be really appreciated.Thanks,

An INSERT EXEC statement cannot be nested

Posted: 24 Sep 2013 07:04 PM PDT

Hi All,i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.[b]Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461An INSERT EXEC statement cannot be nested.Msg 213, Level 16, State 7, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 793Column name or number of supplied values does not match table definition.[/b]Ex:insert into #Result(ID,Name,Count)exec [Rep_Accuracy_Achievement_Analysis_Report]

Max Memory

Posted: 11 Sep 2013 10:12 AM PDT

I have set Max Memory to 6 GB. But SQL Server is using only 2.9 GBQuery used:SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (kB)'So why SQL Server is not using all allocated Max memory i.e 6GB? Is this normal? SQL Server should use all allocated Max memory right?Thanks,Gary

32 BIt ODBC SQL driver

Posted: 24 Sep 2013 05:42 PM PDT

I need to insatll 32 bit SQL odbc driver in Linux environment , can any one pls help on this of providing the link

how to move ssis packages from one server to another

Posted: 24 Sep 2013 04:07 AM PDT

server name: AServer name :bssis project to be moved ..I am try ing getting lot of errors in the package.

Combine CASE for Boolean (is NULL) and Expression

Posted: 24 Sep 2013 03:13 PM PDT

Hi,I just thinking how to make my code more readable, I need to check C1 for is NULL and for nothing '', I learned that syntax is differentFor now I came up with nested CASE[code="sql"]CASE when C1 is NULL then 'Alpha' else CASE C1 when '' then 'Bravo' C1 when 'c' then 'Charlie' else 'Whiskey' end end[/code]Is there any other way to make it more structural.Tx and BestMario

Creating a recordset from 1 table without a cursor

Posted: 24 Sep 2013 08:52 AM PDT

Hello, I have a questionUsing TSQL without a cursor how do I separate this table into a recordset Director, Managers, Sales Reps and trainees based on the contents ? Sean Murray Is director, the next 4 are managers , next 4 are sales reps and the last 2 are trainees. [b]RepID SalesForceName ReportsToID[/b]1 Sean Murray 12 Johnny Smith 13 Pete Rodin 14 Josh Turner 15 Harry Sykes 16 Jimmy Rud 37 Paddy Giles 38 Jimmy Young 39 Paul Connell 310 Noleen Yates 711 Trish Gates 7

Need to fill the Gaps with previous value

Posted: 06 Oct 2012 03:22 PM PDT

Hi experts,I have a scenario to fill in the GAPS between the dates with previousdate+1 day.here is the table DDL ,sample data and expected output CREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float)INSERT INTO #SAMPLETABLESELECT '08/09/2012',8.2 UNION ALLSELECT '08/10/2012',9.4 UNION ALLSELECT '08/14/2012',10 UNION ALLSELECT '08/15/2012',9.6 UNION ALLSELECT '08/16/2012',9.3 UNION ALLSELECT '08/19/2012',9.7 SELECT * FROM #SAMPLETABLEORDER BY DATECOLDATECOL WEIGHTS2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 9.42012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 9.32012-08-19 00:00:00.000 9.7What i need is to fill in the GAPS between the dates with previousdate+1 day and weights is same value as previous record values. -- Expected OutPut2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 [b]9.4[/b][b]2012-08-11 00:00:00.000 9.42012-08-12 00:00:00.000 9.42012-08-13 00:00:00.000 9.4[/b]2012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 [b]9.3[/b][b]2012-08-17 00:00:00.000 9.32012-08-18 00:00:00.000 9.3[/b]2012-08-19 00:00:00.000 9.7Please help me.Thanks,

Works in Management Studio but not from C#

Posted: 24 Sep 2013 01:11 AM PDT

I have a simple stored procedure that takes one nvarchar parameter as input. The parm is compared to a column defined as uniqueidentifier (column SubjectId, below). When I run the sproc in Management Studio it works. When I call it from c# it does not fail but it returns 0 rows.I have checked the spelling of the sproc and of the parm (and their case). All aok. I have converted the sproc call to Command.Text. No change. I have hardcoded the parameter value. No change. I have put a breakpoint in just before filling the dataset and grabbed the query and executed it in Mangement Studio and it works, then I let C# continue execution but no rows are returned. I have changed the dataadapter to a datareader, no change.Here's my code:[code="plain"][dbo].[GetBySubjectId] (@SubjectIdString nvarchar(550) = null) asbeginset nocount onSelect GroupId, GroupName from dbo.MyTable where filetypename = 'Enrollment' and SubjectId = @SubjectIdString[/code]Here's an example of the sproc call with the parm hardcoded in as an experiment:[code="plain"]sqlCommand.Parameters.Add("@SubjectIdString", SqlDbType.NVarChar).Value = "FBBE20B1-55F1-E111-BF73-00155D062F00";[/code]Anybody see the problem?

sql server replace query?

Posted: 24 Sep 2013 07:42 AM PDT

I have a column in sql server table in which there are integer values, now i want to replace a range e.g values> 50 with a string 'good' and values<50 replace with 'bad' in only one column in that table, I have tried thisSELECT t.ColA, CASE WHEN t.ColA >= 50 THEN 'Normal value' WHEN t.ColA < 50 THEN 'abnormal value' ENDFROM MySchema.MyTable AS tbut it only selects, not replaced the column values>50 with 'normal value' string. any help please?

Shift week to Wed - Tues

Posted: 24 Sep 2013 03:26 AM PDT

Hello. I need to be able to group data based on not only the date, but also the "week of". However, the "week" is defined as Wed - Tues. Basically, I think what I need is for the code to convert a date to the preceding Wednesday (not the Wed of last week). For example, 9/23/2013 would convert to 9/18/2013, but 9/27/2013 would convert to 9/25/2013. I can find some code to find a date in the previous week, but nothing like this (so far).I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.Thanks,PK

SQL cluster installation failed with below error

Posted: 24 Sep 2013 03:42 AM PDT

Hi,SQl cluster 2008 r2 failed with below error.Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068119551 Exit facility code: 1211 Exit error code: 1 Exit message: Failed: see details below Start time: 2013-09-24 18:02:40 End time: 2013-09-24 18:34:10 Requested action: InstallFailoverCluster Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0xA60E3551%400xD3BEBD98%401211%401Machine Properties: Machine name: xxxxxxxxxxxA Machine processor count: 24 OS version: Windows Server 2008 R2 OS service pack: Service Pack 1 OS region: United States OS language: English (United States) OS architecture: x64 Process architecture: 64 Bit OS clustered: YesProduct features discovered: Product Instance Instance ID Feature Language Edition Version Clustered Configuration file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\ConfigurationFile.iniDetailed results: Feature: Database Engine Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: SQL Client Connectivity SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: SQL Server Replication Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Full-Text Search Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Integration Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools Connectivity Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Management Tools - Complete Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Management Tools - Basic Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools SDK Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools Backwards Compatibility Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txtRules with failures:Global rules:There are no scenario-specific rules.Rules report file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\SystemConfigurationCheck_Report.htmKindly help me to find out the error.........

Monitoring the SQL Server Agent

Posted: 24 Sep 2013 02:57 AM PDT

We had an issue recently, where after patching the OS and rebooting, the SQL Agent hung:To clarify: All the jobs in the agent would start, and then run continuously without finishing.Is there a way to monitor the SQL Agent and send out an alert of there is hanging activity (for instance long running jobs - which would normally be monitoring by a job IN the Agent).I'm brainstorming here guys...Thanks,Rude Dog

select query not working

Posted: 24 Sep 2013 12:11 AM PDT

Here is my table script:USE [MyDB]GO/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_MAC_MAPPING]') AND type in (N'U'))DROP TABLE [dbo].[USER_MAC_MAPPING]GOUSE [MyDB]GO/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[USER_MAC_MAPPING]( [MAPPINGID] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NOT NULL, [mac_id] [nvarchar](50) NOT NULL, [created_by] [int] NULL, [created_on] [datetime] NULL, [modified_by] [int] NULL, [modified_on] [datetime] NULL, CONSTRAINT [PK_USER_MAC_MAPPING] PRIMARY KEY CLUSTERED ( [MAPPINGID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [constraint_name] UNIQUE NONCLUSTERED ( [userid] ASC, [mac_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[b]my issue is : when I run select query it keeps on showing message "Executing Query" and never ends.This is a newly created table .....there are no data .....or may be maximum 1 record I tried also select count to print number of records .....this also does not work....it keeps on showing message "Executing Query" and never ends..what is the issue ? how to fix it ?[/b]

Query Execution Slow and Fast find the difference

Posted: 23 Sep 2013 11:15 PM PDT

Hi, I'm desesperated and I need some help.I've a problem with a simple query. If I put a where clause to a particular field it takes a long but only depending the type of where. Let's say if I put: where field is not null it runs fastBut If I put: where field > 1 it runs so slow.This is the query (the most isolated version with the problem) with the > 1 clause.--------------------------------------------------------------------SELECT cabe.CodigoEpisodioFROM dbo.CabecerasFacturas AS cabe RIGHT OUTER JOIN dbo.Historias AS hist INNER JOIN dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria INNER JOIN dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro INNER JOIN dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa AND hist.CodigoEmpresa = emp.CodigoEmpresa ON cabe.CodigoCentro = dbo.Centros.CodigoCentro AND cabe.CodigoCentro = epis.CodigoCentro AND cabe.CodigoEpisodio = epis.CodigoEpisodioWHERE (cabe.SwEstadoCabeceras > 1)---------------------------------------------I include the execution plan for the slow and fast versions. Please, help me I really need to fix this as soon as possible.A lot of thanks in advance.http://www.comoflipas.com/slowPlan.xmlhttp://www.comoflipas.com/fastPlan.xml

Building Oracle forms frontend application with a sqlserver backend (bypassing oracle database)

Posted: 24 Sep 2013 12:55 AM PDT

We are trying to migrate from an oracle database to sqlserver. We can use the Sqlserver's Migration Assistant to migrate the data. However, we do not have enough time to convert/rewrite the code. Is it possible to have the existing oracle forms frontend to work with a sqlserver database backend (without an oracle database)? and how?

SQL 2008 Developer Edition

Posted: 23 Sep 2013 11:03 PM PDT

Help! I need to obtain a copy of SQL 2008 Dev edition, but Amazon and Microsoft and Provantage don't have any copies available. I already have an R2 version, but I need the non-R2 version for some testing.Anyone have any ideas where a copy of this is still for sale somewhere? (I have both 32bit & 64bit machines).

SSMS closes without warning during query run

Posted: 19 Sep 2013 01:01 AM PDT

Does anyone know why SSMS would close without warning during the middle of a query run?It's done this to me twice this morning. I do have Statistics_IO and Statistics_Time and Include Actual Execution Plan on (which I usually have off), but it seems odd to me that that would be the only reason why SSMS shuts down.No error BTW. The window just vanishes. I haven't checked the logs yet. Doing that after I finish posting. Would love to know if anyone else has seen this problem before.

Script to find all filestream databases with list of Tables (contain filestream column)

Posted: 18 Sep 2013 10:56 PM PDT

Hello,Do you have a script to find all filestream databases with list of tables containing Filestream Column ?I would like a result like this :Name of database, Name of TableThanks,Eric

Security restriction to a SP

Posted: 23 Sep 2013 08:54 PM PDT

I have a little application which allows a user to execute a store procedure with a database restore command in it. The user has to enter a user name and password but I have to give them higher rights than I would like too. Can I restrict this user to only being able to execute this SP?Many thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog