Friday, September 6, 2013

[SQL Server 2008 issues] Moving a database log file question

[SQL Server 2008 issues] Moving a database log file question

Moving a database log file question

Posted: 05 Sep 2013 06:20 PM PDT

SQL Service was down, our storage engineer created a new LUN and then moved the log file to the new location ...Database is therefore unaccessible ...after running a Alter database script to point the log file location, The only way to get the DB online is to restart SQL Service??? this will in effect take the whole instance DB's down ...Is there another way to just get that 1 DB online???I know the sequence of events is not exactly best practice but is there a way around this scenario???

script needed

Posted: 05 Sep 2013 06:29 PM PDT

Hi ,Can any one send me the script of this scenario I would like you to develop a script that creates a SQL user and sets the password (to be set as part of script, maybe in a parameter). The script will also create schema and the schema will be bound to the sql account being created and provide read only access to the table objects Thanks.

Cumulative total column wise

Posted: 05 Sep 2013 02:12 AM PDT

I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.I have currently dataset abosulate total) and I need the one in second line: Opco Region Country Jan Feb MarAbsolute total OP1 EMEA GB 0.3 1.3 0.7Cumulative total OP1 EMEA gb 0.3 1.6 2.3RegardsMeghna

backupset table in msdb database not updating backupfile name in the column name

Posted: 05 Sep 2013 06:05 PM PDT

Hello,My SQLserver version is SQlserver 2008 R2 Sp2.Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)I just identified that if I take backups using scripts from SQLserver management studio, the backupfile name in[msdb].[dbo].[backupset] table is not getting updated.However if I execute the same backup using the maintenance plan I don't see this issue.Is that a bug? Thank you.

How to find preceding 7th date of any given date.

Posted: 05 Sep 2013 05:43 PM PDT

Hi, My requirement is,I want preceding 7th date of any given date.For example,1)Declare @AsonDate As datetime = '2013-08-31'Then my output should be '2013-08-07'2)Declare @AsonDate As datetime = '2013-09-06'Then my output should be '2013-08-07'Kindly help me!!Thanks in Advance!!

sp_UpdateStats missing in database

Posted: 05 Sep 2013 05:48 PM PDT

HiWe recently installed Lync at our company.Lync creates 4 SQL instances and I have set up maintenance tasks for these instances.Strange thing is that sp_UpdateStats does not exist in any of the databases created by the Lync installation.If I create a new database on the server, I can run sp_UpdateStats with no problem.I have not found one article out there about anyone having this problem. Strange. Can anyone help?Server OS: Windows 2012 DataCenterSQL: 2012 SP1Please, please can someone help.Thanks,TDP

Database structure question

Posted: 05 Sep 2013 03:40 PM PDT

I don't know the best way to structure my database.I have a data feed that comes in a CSV format, its financial data about 65,000 different mutual funds with daily prices. In each CSV i get, each mutual fund is listed once with the price for the current day.What i do first is i import the CSV. But then what i want is a table for each mutual fund with prices and dates inside each table. this means i have to create 65,000 tables in a separate database, then every day read from the imported CSV file and write to the tables. The problem is this ajax query takes almost an hour each time (for each imported CSV or in other words each day)The logic behind this is when i want to forecast data on an individual fund later, i can just go to that fund's table, query the data out, and do what i want with the prices for that fund over a range of time which is the whole point.The alternative approach i think is instead of creating 65,000 tables, just leave the imported CSV tables and draw data from there on demand. To do that though i would have to join together all my imported CSV with each query before grabbing fund data, because i'd want data over a course of many months even years for a particular fund.Which approach is the most efficient at runtime? The way i have it now with the 65,000 tables i have an index on the date column in each case. I'm thinking if i did the other option and just queried the imports, i'd need some sort of dynamic way to index them once they're joined so i can use the date column effectively. I would need that i think. I'd need to have them all indexed by fund name and then by date in order to efficiently grab data from a query that would literally be billions or maybe trillions of rows. Is there a dynamic way to do indexes when you join tables?Your thoughts are much appreciated. Thanks.

Maintenance Plan Help!!!!!!

Posted: 05 Sep 2013 11:10 AM PDT

Dear SQL experts,I am planning to setup some Maintenance Plan including System database backup, users databases backups and Maintenance clean up tasks.my question is:1. Should I creating one Maintenance Plan or separate such as 1 plan for system dbs and another users db? 2. Should I also create check integrity 3. How should I create Maintenance clean up tasks for all DBs?would someone plz help me with easy steps to setup sysDBs and UsersDB?Thank you

Cannot login to Analysis Services 2008 from SQL Server Management Studio

Posted: 27 Jul 2010 02:23 AM PDT

Last week I installed SQL Server 2008 developer edition with service pack 1 on windows 7 Home Premium. Before install the SQL Server, I already installed .net framework 3.5 service pack 1. But when I login to the Analysis Services, following error is coming.A connection cannot be made. Ensure that the server is running (Microsoft.AnalysisServices.AdomdClient) No Connection could be made because the target machine actively refused it 127.0.0.1:2383This is my personal laptop and I tried with local system and network service. SQL Server Browser is also running.But I can login to Database Engine and Integration Services. But Analysis Services and Reporting Services are not working. Please help me to solve this problem.

Update Stats Job causing Dropped Connections and Cursor not Declared errors

Posted: 05 Sep 2013 08:27 AM PDT

Has anyone ran across or have seen dropped connections from TIBCO and getting a 'cursor not declared' error message when running an Update Stats job.The issue started about 4 weeks ago and only occurred when our weekly maintenance job was running which 'Update Statistics' on most of our tables. The SQL job runs fine with no errors but we are dropping connections and receiving the following type of error message from the drivers.ErrorCode: BW-JDBC-100014, ErrorMessage: "JDBC error reported: (SQLState = HY000) - java.sql.SQLException: [tibcosoftwareinc][SQLServer JDBC Driver][SQLServer]The cursor was not declared."It only happens with the SQL job runs. Any suggestions?Thanks,Daniel

Duplicate column names not allowed in result sets obtained through OPENQUERY and OPENROWSET

Posted: 05 Sep 2013 07:34 AM PDT

Hi All,Please I need someone to help me with this problem. I have a table variable which I need to insert data into. The data I want to insert comes from a query for example SELECT r.decode, b.decode FROM Sales. employee. The insert is not working because there's duplicate column name in the select list.The error is this : "Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "decode" is a duplicate.Please I need helpEO

Change Data Capture as a long-term change-tracking solution

Posted: 05 Sep 2013 03:28 AM PDT

Has anyone used CDC as a long-term solution for change-tracking? It seems to me as though this was not intended as a platform to be used to capture and retain changes for longer than a few days. I'm very concerned that it would start becoming unmanageable/unsearchable before too long, unless you were regularly flushing the data out to a user table.

SQL Server Link to Oracle Inconsistent metadata problem

Posted: 03 Dec 2012 12:35 AM PST

HiI have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can query the Oracle database using OPENQUERY but when I try a simple SELECT COUNT(*) from LK_ORA..USR.TAB1 I get an error:Msg 7356, Level 16, State 1, Line 1The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LK_ORA" supplied inconsistent metadata for a column. The column "COL_CODE" (compile-time ordinal 1) of object ""USR"."TAB1"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.I've found an article that says when SQL Server retrieves metadata from Oracle in order to compile the query, Oracle reports the data type to be varchar(255), but when it actually returns the data, the data type is in fact nvarchar(255). (But it may not be called nvarchar in Oracle.)Anyone know how to get around this? I've tried various options on my linked server connection in SQL with no luck so farMany Thanks

Query to Return each field that is Not Null

Posted: 05 Sep 2013 02:57 AM PDT

Hello everyone,I have a database (third party application) that has a table with 30 or so "free fields" that can be customized for our use. I have created some forms that use these free fields and I want to create a query that would show each record where one of the free fields is not NULL. In fact I would like to show all of them so if a record had all 30 free fields as Not Null I would like to have 30 records in the dataset returned. I thought of using a For Loop of some sort but perhaps I am making this more complicated that it needs to be. Basically for every one of these free fields I am using a trigger to create a new task for a user. Any advice or suggestions are greatly appreciated.Paul

[TDE] Creating master key causes "fatal windows error"

Posted: 05 Sep 2013 02:55 AM PDT

We have a SQL Server 2008 R2 database in a very locked down (govt) environment. I am unsure what features have been disabled in Windows or SQL Server, and that is probably next on my list to continue investigation but here is the general gist of the issue:We are trying to enable transparent database encryption. In doing so we are attempting to create the master key:[code="sql"]create master key encryption by password ='PUT THE PASSWORD HERE';[/code]This appears to create the master key HOWEVER it also causes Windows to pop up a message to the gist of "Windows has encountered a Severe error and will shutdown." About 60 seconds later the system is rebooting. The windows application and system event logs don't really seem to shed any light on this issue.We are able to drop the key without crashing after the restart, however I am afraid that if this crash does occur when creating it something isn't happening correctly and I would be worried about actually encrypting each of our databases. My initial thought was something in the Windows Data Protection API that is encrypting the master key is causing the error, either because it is locked down to prevent usage or something to that affect.

Cumulative total column wise

Posted: 05 Sep 2013 02:08 AM PDT

I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.I have currently dataset abosulate total) and I need the one in second line: Opco Region Country Jan Feb MarAbsolute total OP1 EMEA GB 0.3 1.3 0.7Cumulative total OP1 EMEA gb 0.3 1.6 2.3RegardsMeghna

Problem with grouping in SSRS 2008

Posted: 05 Sep 2013 01:31 AM PDT

When I create a report in SSRS 2008 and I add a group, the group appears in the object as it should and it shows as a group in the Properties Dropdown but nothing appears in the Grouping Pane. I have tried reinstalling SQL Server and nothing changes.Joe Wolfe

xp_regread execute permission denied

Posted: 16 Sep 2011 05:18 AM PDT

Hello All,I am having the issue described here:https://connect.microsoft.com/SQLServer/feedback/details/515132/management-studio-generates-scripts-that-use-unsupported-undocumented-proceduresMore specifically the user right-clicks on a table and gets 'EXECUTE permission denied on object xp_regread, database amaster, owner dbo'The version of management studio is 2008 R2 SP1. The user is attempting to manage a 2000 SP4 database.Can someone help me analyze what the proposed solution does?When the poster says 'This is the code that needs to be modified to remove the need of xp_instance_regread' do they mean:1) something in the clients SSMS install needs to be changed; or2) something server side needs to be changedIf the former how do I do this?If the latter it looks like the only thing the code does is exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUTWouldn't xp_instance_regread only read from the registry? How does that accomplish anything?Thanks!

Knowing which tables to drop - HouseKeeping

Posted: 04 Sep 2013 08:26 PM PDT

Hi,Over the years our DWarehouse has expanded and there are a few tables on the Databases that we think are no longer required but the question is how do we know which tables these are?I could look at the table sys.dm_db_index_usage_stats to see when a user or ssis package has accessed this last and then take a guess from this which table is not required. I was then thinking of renaming this table by adding "To_be_Deleted" at the end of the table.But I'm a bit scared if this table is required by a ssis package and then something else would fail.Has anyone any good ideas or scripts that would show me a league table of some sort of which table is popular and which is not?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog