Wednesday, June 12, 2013

[SQL Server 2008 issues] SSRS Memory utilisation

[SQL Server 2008 issues] SSRS Memory utilisation

SSRS Memory utilisation

Posted: 11 Jun 2013 01:19 AM PDT

Hi Team,Reporting Services CPU ulilisationPlease suggest

SQL Server Corporate Standards - Automated Compliance Checking

Posted: 21 May 2013 08:43 AM PDT

Does anyone know of a flexible automated SQL Syntax checker?I want to be able to analyse scripts before they are deployed to our test systems to check for certain things such as the fact that each statement has an appropriate error check after it.I want to make sure each statement is commented.I want to spot syntax that would be legal in an on premise SQL Server but not in Azure.I am expecting to configure my own rules.Does such a thing exist? I have started to write one but clearly its complicated because I am effectively having to start to simulate SQLs own parser so I can break down and analyse statements.Any feedback will be much appreciated (positive or negative).ThanksTim

hide databses for special user

Posted: 11 Jun 2013 05:36 PM PDT

Dear friend,i want define a login in server who are dbowner of x database and can not see other databases in this server.how can do this?best regards,zohreh

Moving databases to new server. NEED HELP please please

Posted: 11 Jun 2013 04:07 AM PDT

Please help me here;I have to rebuild the clusters so the plan is i have to move the sql databases to a new temporary server and rebuild these servers from scratch. The system engineer is saying that he will shut down the existing servers and present the storage to new stand alone server. If they do that will the databases still exist and when you connect to instance the databases will pop up or do I still have to restore on new server? will the process damage the databases? Please help me. I need a quick help.

Isolation levels

Posted: 11 Jun 2013 04:37 PM PDT

Hi all,what is the default isolation level for sql server. Does it changed in newer versions. what is the use of it. If we have to change, how to change that level. i have done some googling .. but i am unable to understand the very purpose of it. why is that much important.

Find Schedule based on business rules

Posted: 11 Jun 2013 11:31 AM PDT

Hi Experts,I need your help on the case defined below. I need to find available schedule based on below defined rulesSchedule is defined for a Camera Room. But a Camera Room may have multiple schedules. Weekly schedule is stored on week day basis, whereas onetime schedule is stored as actual date time.• Weeklyo There can be weekly schedules for a Specific Camera Room, or ANY (-1), In this case -1 overrides• Onetimeo There can be multiple weekly schedules for a resource.• Onetime Overrides Weekly schedules.o Available o UnavailableScript is attached to create schema and insert some sample data. I also have attached a sheet where I have tried to explain what should be the actual outcome should be. The green ones should come as is, but the white ones should be overridden with the values as specified in side.(Override column)Please let me know if I have missed something or for more information.Thanks everybody in advance for help.

How do you drag a table to view diagram Pane

Posted: 11 Jun 2013 10:35 AM PDT

There must be a way to drag a table into the design diagram pane of a view. What is the configurate setting to allow this. currently in my SQL Server 2008 R2 install this option is not set up.

Seeking Feedback/Recomendations on Auditing, both Simple and Detailed

Posted: 11 Jun 2013 09:45 AM PDT

[b]SQL PLATFORM:[/b] 2008r2 Enterprise 64Bit[b]SIMPLE AUDITING =[/b] Capture the name and date/time for each row that is inserted or updated in a table. This is just INSERT & UPDATE since a DELETE action would leave no row to store the info.[b]DETAIL AUDITING = [/b]Capturing the Name of the user, the date/Time of the command (UPDATE, INSERT and DELETE)As of current I can implement Simple Auditing and Detail Auditing via the use of table triggers and additional audit tables (tables that store info from the DELETED table anytime an UPDATE or DELETE DML statement is executed). However I'm looking for a better, more robust solution, ideally one that's native to SQL Server if 2008R2 has such a thing.My company has been using SQL Server 2000 and more recently 2005 for management of our primary accounting database which is form a third party vendor that specializes in vertical accounting markets. We recently moved over to 2008R2 and I'm hoping that this will open up some options for us like better auditing.Does anyone know if SQL 2008R2 natively has table level auditing that stores the info either in the table where the change has been made (SIMPLE AUDITING ) or in a backup or secondary audit table when you want to capture not only who and when but the "what changed" as well? From what I've read about SQL SERVER AUDIT you don't have the option to save this info to a table and that's important for us. In fact I don't know why someone would prefer to use windows logs except for when it comes to performance and trying to avoid adding to the DB Server workload and or size by storing the audit info within the DB. I had hoped that SQL 2008 R2 might natively offer a new data type that when you set a column to it, the thing would auto-record the login info of the user account/login that the DML statement is being executed under. Then again maybe I'm the odd one for thinking windows logs are not the best pace to store audit info. I just seems logical to me that you'd want to store that info a normalized fashion so you can use T-SQL to qeury/mine the audit info.In any event, if you know of a third party product (perhaps something by Red-Gate) that can do this please share that in a post.Thanks

Using the same partition function and scheme for multiple tables

Posted: 04 Jun 2013 02:58 PM PDT

I have a database where most of the tables have an integer field say "SetId" denoting a batch of data.If a new SetId is created all these tables will get data related to the new SetId.SetId governs data retention, purging, it also forms part of filtering in many user queries on these tables.I am thinking of partitioning all these tables on the SetID column, which I am sure would be beneficial for performance and data maintenance.My question is, do I use a common partition function and partition scheme to partition all the tables on SetId, or is it more advisable to use separate partition functions and schemes for each tables?

Finding a word\acronym in a field

Posted: 11 Jun 2013 07:45 AM PDT

Hi Guys,How would I go about finding a specific "word" in a field?What is the best way of returning records containing only the word "LC" and where LC stands on its own and is not part of another word \ acronym?ie LC,AMBLCAND, CMBABC, LCLCWelcomeSo the records returned would be:LC,AMBABC, LCLCThanksDon

Need A Help in Encyption

Posted: 11 Jun 2013 07:16 AM PDT

Hi Friends,I want to know how we can encrypt the specific Objects(table) in database!!Can we do it on field level in Object(table)?what is overhead actully mean in encyption?thanks.

Converting DateDiff to HH:MM:SS

Posted: 01 Nov 2012 04:27 AM PDT

Evening All,We're having some issues with some data and i will do my best to explain,The question relates to vehicle TrackingSo, we have Vehicle ID, Journey Start TIme, Journey End Time, Journey Duration.Vehicle ID is IntJourney Start Time and End Time is DateTime (YYYY:MM:DD hh:mm:ss)Journey Duration is (hh:mm:ss)So, to calculate Journey duration we have applied the followingConvert(varchar, Start_Time - End_Time, 108) which gives us our journey duration for a single journey.Now the issue we have is we would like to know the total journey duration for a vehicle and we are having some issues in obtaining the sum of the journey duration,What we are trying to do:Select Distinct(ID), Sum(Convert(varchar, Start_Time - End_Time, 108)), but we are always ending up with errors,so we tried a datediff between start time and end time and mangage to get the journey time in seconds,Now what we'd like to do is sum the number of seconds and then convert this to hh:mm:ss but it doesnt like us!!!I hope you can help this has been an extremely unproductive and frustrating dayMany ThanksWaqqas.Zia

Production / Dev Design

Posted: 11 Jun 2013 05:48 AM PDT

There doesn't seem to be a good place to ask this in the 2008 section... so I am placing it here..If you have a production system that has a number of SQL Servers, each holding an instance, and each instance holding a number of databases. Applications use data from outside of their database, often quite a few.So how do you set-up and maintain a development enviroment? Everytime a programmer starts a project they currently have to check with everyone else and update a number of databases from production... this seems like a lot of work, and there should be a better way to keep the two in sync. I have thought about installing one Dev instance per developer.. and have them release work into a QA system... but this also seems like a lot of overkill, and overhead, for our non-production server.

ErrorMessage as Output parameter of a stored procedure

Posted: 11 Jun 2013 01:14 AM PDT

I want to create a stored procedure to insert data into the database.If for any reason the insertion fails eg: primary key violation or invalid datatype,I want my stored procedure to return the relevant error message as output parameter.storedproc(inputparameters inp1,inp2,inp3,inp4 Outputparameter errorMessage)(inp1+inp2 is the primary key)Can some one please help me write such a stored procedure. Thanks!

DMV sys.dm_db_missing_index_group_stats returns no rows

Posted: 05 Oct 2011 10:31 AM PDT

Hi All,I am using a script to review missing indexes on various SQL Servers.Most of the time, it works just fine.Sometimes, however, certain DMVs that are part of the script, just don't return any rows.Usually, the main issue is sys.dm_db_missing_index_group_stats.1. I know that restarting the service clears DMVs - that's not it :-)2. I know I need the VIEW SERVER STATE permission - that's not it either :-)3. I know I need actual missing indexes:select * from sys.dm_db_missing_index_detailsreturns 573 rows, yet select TOP 1 * from sys.dm_db_missing_index_group_stats returns 0 rows.This renders all the various missing index scripts useless, because the JOIN doesn't work, and so the script doesn't return any results.I keep running into servers with this issue, and so far, only restarting the service has resolved it. Data collection starts from scratch, and within just a few minutes, I get data in the sys.dm_db_missing_index_group_stats. Check back a few days, weeks or sometimes months later, and the DMV again returns no results - same user, same DB, etc.I have Googled the heck out of this issue and can't find a solution. It is frustrating, since missing indexes are often a major issue of the application I am troubleshooting on many different SQL Servers.Any help would be greatly appreciated :-)

Cursor Logic causing Blocking ?

Posted: 11 Jun 2013 01:54 AM PDT

I'm trying to troubleshoot some inherited code that uses cursors and seems to be causing blocking.The cursor gets loaded from a select statement, and eventually does an insert into a production table. I think it loops around and does 1 insert per record in the cursor.Is a lock held on the table being inserted until the cursor loop completes ? Or is there a lock-release-lock-release for each loop in the cursor ?Other users are trying to issue updates against the same table but seem to be blocked for long periods of time.Or am I being to vague to get an answer ?tia

Managing One-Time Report Subscriptions from SharePoint

Posted: 11 Jun 2013 03:00 AM PDT

We have many jobs created on our report database server from users running one-time reports in SharePoint. If we delete the job from the agent, it will be recreated by SharePoint. However, SharePoint does not delete these jobs (at least not by default) once they are complete. Anyone know the preferred way to clean these up?

First day of april in a given year

Posted: 11 Jun 2013 12:56 AM PDT

Hi All,Could any one please let me know the first Monday of april provided the year is given.Say year=2013, how to find first Monday of april in year 2013?Any help in this pls.Thanks!

creating logon & logoff triggers in MSSQL

Posted: 03 Jun 2013 01:02 AM PDT

We have several database that we track user logons for, until now all these databases resided in Oracle. For Oracle I created a sys trigger than logs [i]os user, database user, program used[/i], etc., as well as logon time & logoff time that writes to a table I called "audlog" then I query the table every monthThis is what I created in Oracle:[code="sql"]CREATE TABLE audlog.session_audit (user_name VARCHAR2(30), log_date DATE, session_id VARCHAR2(30), ip_addr VARCHAR2(30), hostname VARCHAR2(30), auth_type VARCHAR2(30), os_user VARCHAR2(30), event VARCHAR2(8), program VARCHAR2(30)) tablespace users;Create Or Replace Trigger Trg_Logon_Info After Logon On DatabaseDeclareProgram Varchar2(30);BeginSelect program into program From v$session Where sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG ON' -- Event , Program );End;/Create Or Replace Trigger Trg_Logoff_Infobefore Logoff On DatabaseDeclareProgram Varchar2(30);BeginSelect Program Into Program From V$Session Where Sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG OFF' -- Event , Program );End;[/code]I would like to create something in MSSQL that basically shows the same information, so I can keep the reports consistent looking. Can I duplicate this trigger in MSSQL?

Backup job tied to user account fails.

Posted: 10 Jun 2013 11:05 PM PDT

What's the best practice for setting up maintenance plans? We had a former admin who had set up our backup maintenance plans. The backups stopped occurring when her Active Directory account was disabled.Her account was the "owner" of the SQL Server agent job.Should we create a service account for the purpose of setting up backups?How do you handle this?

Need some clarification on Initial Size and Autogrowth?

Posted: 10 Jun 2013 11:56 PM PDT

Is the Initial size(MB) under db properties/Files grows as database grows?As per my understanding, initial size grows as per the autogrowth set?I recently had a tempdb issue, however, the initial size shows 8MB(data) and 1MB(log) but the actual size of the tempdb was about 29GB.Can someone clear this doubts for me?Thanks,SueTons.

System Databases / msdb / System Tables / dbo.backup*

Posted: 10 Jun 2013 11:10 PM PDT

Looking at the tables within the MSDB Database -I can see that the dbo.backup* tables have been populated since the server was first commissioned.These tables contain backup set information and details of the backup including filesize ad location ( possibly useful to some )currently my files sit at 144,462 records and 72,231.Question :Can these files OR should these files be cleared down at least purge some records ?This is not due to lack of space on the server, just a housekeeping question.Thanks

can anyone give me name of the forum which helps me to give me testing tips of a database

Posted: 10 Jun 2013 08:58 PM PDT

Can any give me name of the forum which helps me for testing of database

How to connect with SQL Server using http based API

Posted: 10 Jun 2013 11:01 PM PDT

Hi,I am new to this. I want to connect SQL SERVER 2008 with a CRM called Salesforce (without using any existing tools).Some expert suggested me that it can be done using [b]http based API of SQL Server 2008[/b].I want to know whether http based API of SQL Server is available and how it can be used.Or is there any other way by which i can connect with SQL Server (by coding)[b]Please help.[/b]Thanks

Plan in cache

Posted: 10 Jun 2013 08:01 PM PDT

Why does the plan remove from cache?I mean what is the cause of removing the plan from cache?I thought Update statistics causes it but I ran a query with sp_executesql (parameter query)the plan was in cache I checked with this query:SELECT ( SELECT query_plan FROM sys.dm_exec_query_plan(qs.plan_handle)),est.text,creation_time,last_execution_timeFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) estORDER BY creation_timeBut afterUPDATE STATISTICS [dbo].[Table]WITH FULLSCANthe plan was still in cache .

SQL Date Issue

Posted: 09 Jun 2013 08:04 PM PDT

Hi,I wish to create a column 'GoalBehindFlag' which will work like a flag providing me 0s and 1s if the datetime saved in another column is greate than or less than system date time.Can anyone please correct me where I am going wrong in this:select a.casecode, a.[Goal_Weighing],b.ddate, (b.ddate < sysdatetime) as GoalBehindFlagfrom [dbo].[VRep_Calc_1] as a inner join [dbo].[VRep_Goals] as b on a.casecode = b.casecode

0xc0202009 Primary Key Error

Posted: 10 Jun 2013 08:38 PM PDT

I keep receiving the error below when trying to import data from a Flat File source (CSV file) in SQL 2008 R2 Express edition.Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_UoSW_STAGING_TABLE'. Cannot insert duplicate key in object 'dbo.UoSW_STAGING_TABLE'. The duplicate key value is (00492FF894C64BEFAC9C3D1866773CE2).". (SQL Server Import and Export Wizard)I have checked the import source for the primary key SQL says is causing the error but when I find that primary key in the CSV file only one entity exists.I thought perhaps it was due to that particular record so removed this and ran the import wizard again but the same error occurs with a different field. I cannot see how I can get around this when the primary key isn't being duplicated at all.I have also checked other tables within the database to make sure I haven't accidentally imported the CSV to the wrong location.Any help would be tremendous at this stage as this has driven me insane for hours!

Division problem in SQL

Posted: 06 Jun 2013 08:38 PM PDT

Hello All,Can anyone please explain why do we always get 0 when we divide 1 by any other larger number.select 1/2 gives me 0.Thank you for your help

TPR : Time Persistant Repository

Posted: 10 Jun 2013 11:22 PM PDT

Let me ask my question 1st, then explain: (sorry for the length)Solution for : Massive amount of daily data purges.VM : 8 core, 64g, Windows 2008r2 sp2; SQL 2008 r2 sp2 EnterpriseScope : 30+ data sources (phase 1 has 5) range from flat files to SQL/Oracle/Postgress databases. Each SQL Server destination database has 10 to 400 tables. Basic concept is to keep 15-30 rolling days of data with Month End days kept for 7 years. Some data loaded daily, some loaded weekly. In the larger destination databases, there are some tables that don't ever get purged of data.I have a table with a row for each : database, table, month end type, number of days kept, last load date, next load date.I have 2 VM's built testing 2 solutions, both work; both are not meeting the timeline set to do the purges. In both cases, a cur (current) and me (month end) schema are created to keep the 7 years of month end data.1) partitioning all rolling tables. the # partitions = # rolling days; merge new, drop old2) Tables : cluster index on date of load : purge data : drop index : create index : load dataThe smallest destination database fully loaded with 3 month ends is around 10g. The largest with 2 month end is 700g. Both have 100% of all data and indexes compressed.Has any one done this? Am missing an over all solution?Wanted to throw this out there; before I report we can't meet the deadlines given.

Data Cube / SQL Server 2012 / BI

Posted: 10 Jun 2013 10:02 PM PDT

Hi Folks,I am a newbie to this forum and relatively new to SQL especially SQL Server.My first query in this area:I have got 2 tables with one common field on which i can make a join.A/ I have been asked to convert this data into a cube.All i know is that a cube is more then 2 dimensional data. Would love your input on where i can learn about cubes and how do i achieve this in sql server 2012 as i already have a license for it.B/ The objective of this exercise i believe, is to keep ready as many reports in as many permutation combinations as possible so that when a user asks for this data, its readily available and very fast to execute.Wouldn't a view just work for this or is that too simple and time consuming to modify every time there is a different request?C/ We also would like to use the BI part of SQL Server 2012. End result is for users to use Excel to interact and play with the data/reports.Can users directly through excel leverage the BI tools of SQL Server 2012Thanks in advance..Regds, Ross

Large import with Replication

Posted: 10 Jun 2013 08:59 PM PDT

Hi allWe are about to do a large import of data through an application (Bulk Logged would be of no advantage) on tables that are also replicated.Are there any pit falls I should be aware of? At present I am just testing how big the log would get but how would I test the performance of replication for the 7million records that are being imported?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog