Thursday, April 25, 2013

[SQL Server 2008 issues] Fragmentation

[SQL Server 2008 issues] Fragmentation

Fragmentation

Posted: 24 Apr 2013 06:16 PM PDT

Hi all. I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.

to find specific position string

Posted: 24 Apr 2013 07:26 PM PDT

Hi friends i have small doubt in sql server , table data contains like id , name 1 , srinivas 2 , ravikumar 3 , jaipal 4 , ravisekhar 5 , ramugopal 6 , harikumabased on this table data i want display 3rd positon charcter only output like id , name 1 , v 2 , m 3 , p 4 , h 5 , p 6 ,uactualy i try query like select id,substring(name,3,1) from emp but its not give exactely result.plese tell me how to write query go get this result.

Database Mail

Posted: 24 Apr 2013 01:19 AM PDT

I am trying to get my database to send emails but I can not get it to work. From everything I have seen everything seems to be enabled. when I send a test email from database email I never get it and there is nothing in the database mail log. I am using gmail on port 587 like it shows to set it up. Any suggestions

Indexes on a Query with a pivot function

Posted: 24 Apr 2013 06:32 PM PDT

Hi all,Many thanks to the poster that helped me clear up a misconception that I was having with indexes a week ago but now I have a question as to whether indexes are possible to create on a pivoted view. Say we have two tables:People:PeopleIdFirstNameLastNameAgeAddressCountryProfession.... some other fields .....Employee:EmployeeIdEmQualifier - this is the string name for the column fieldEmData - this is the data valueand in order to access data from the Employee table we have a pivoted view, call it Employee_Pivot_View:Select EmployeeId, LastName, FirstName, Department, Company, EmploymentStatusFrom(Select EmployeeId, EmQualifier, EmData From Employee) as EPIVOT(MAX(EmData) For EmQualifier IN (LastName, FirstName, Department, Company, EmploymentStatus)) As E_PivotIf I were to write a query:SELECT P.FirstName, P.LastName, P.Age, P.Address, EPV.Company, EPV.DepartmentFROM People P INNER JOIN Employee_Pivot_View EPV ON (P.LastName = EPV.LastName AND P.FirstName = EPV.FirstName)Where EPV.EmploymentStatus = 'Employed'I know that I can't use an indexed view on a PIVOT but is it possible to generate an index on the Employee table, in order to improve performance? Please ignore the percentage of hits the WHERE condition can generate, let's say it's under 5 % so that an index seek can be used.

Trouble shoot

Posted: 24 Apr 2013 06:00 PM PDT

what are the ways to trouble shoot replication :how to resolve primary _key violation in transactional replication ?

Error converting varchar to numeric only in where clause

Posted: 24 Apr 2013 12:10 AM PDT

Hi experts...I have a problem with one of my SQL Server query's...First of all, we are in SQL Server 2008 R2 Enterprise Edition (64-bit), version 10.50.4000.0 on a server with OS : Microsoft Windows NT 6.0 (6002)I have a table with following columns:DataId intPathId intValue varchar(4000)The column 'Value' can contain all kind of data, from string to int to decimal to dates...But, depending on the PathId, it always has the same type of data.For example, for PathId's 30 and 31, the Value column contains always an amount --> numeric dataFollowing query runs without any problem, returning almost 30.000 records :SELECT DataId, PathId, , CONVERT(numeric(14,2),Value) as AmountFROM dbo.TestTableWHERE PathId IN (30,31)Following query proves to me that all records do have numeric data in the table :SELECT DataId, PathId, , ISNUMERIC(Value) as Test_Value_Numeric, CONVERT(numeric(14,2),Value) as Amount, ISNUMERIC(CONVERT(numeric(14,2),Value)) as Test_Amount_NumericFROM dbo.TestTableWHERE PathId IN (30,31)Both columns Test_Value_Numeric and Test_Amount_Numeric always return 1.Now, when I try to leave out the records which have an amount = 0, my query returns me an error : 'Msg 8114, Level 16, State 5, Line 1; Error converting data type varchar to numeric.'Just add following line to the query to see what's happening : "AND CONVERT(numeric(14,2),Value) > 0"(or use a CTE to create a dataset with amount, and then in your result-query, retrieve data from the cte where amount > 0, same issue)To test, I used the last query which works, and did a "select into" to a 2nd testTable, and the column 'Amount' was created as numeric(14,2) in the new testTable.I really hope someone can point out my issue, I am searching for it now for hours, have tried 3 experts, but nobody can give an explanation.Is this a bug ???PS : I have attached in an Excel the real data, so you can test it yourself with my data...

sql query

Posted: 24 Apr 2013 02:50 PM PDT

Hi friends i have small doubt in sql server ,how to write an sql query for getting employee names for the employees whose count is greater than 3 in a city? table data contains likeid name location deptno1 a hyd 102 b hyd 203 s hyd 104 c bang 105 h bang 106 ja chen 207 ka chen 308 kl hyd 509 kled hyd 2010 fed hyd 4011 lm bang 4012 lge bang 30actualy i try query like thisselect location,COUNT(name) from employees group by location having count(*)>3that time its display location (count)bang 4hyd 6but i want only display names detailshow to solve this issuse.plese tell me the query

MCTS 70-448 expiring in July 2013

Posted: 27 Feb 2013 05:39 PM PST

hi,I want to gain a certification of entry level. I'm working as a BI developer for around 5 years and found 70-448 BI development and maintainance SQL2008 to be the best one to start.However, this certification is expiring in July 2013, which makes me think whether it is a wise decision to go for a cerftification which is valid only for few months.I may be missing on few other important aspects and hence need some enlightment.Please help

Compare data between rows of same table

Posted: 24 Apr 2013 03:38 PM PDT

create table ##Temp1 ( myid int identity (1, 1), mytab1col1 int, mytab1col2 varchar(10), mytab1col3 smalldatetime)insert into ##Temp1 (mytab1col1, mytab1col2, mytab1col3)select 1, 'name1', '2010-01-01'union allselect 1, 'name2', '2010-01-01'union allselect 1, 'name3', '2010-01-02'union allselect 1, 'name4', '2010-01-01'create table ##Temp2 ( myid int identity (1, 1), mytab1col1 int, mytab1col2 varchar(10), mytab1col3 smalldatetime)insert into ##Temp2 (mytab1col1, mytab1col2, mytab1col3)select 2, 'name1', '2010-01-01'union allselect 2, 'name2', '2010-01-01'union allselect 2, 'name3', '2010-01-02'union allselect 2, 'name5', '2010-01-01'union allselect 3, 'name1', '2010-01-01'union allselect 3, 'name2', '2010-01-01'union allselect 3, 'name3', '2010-01-02'union allselect 4, 'name1', '2010-01-02'union allselect 4, 'name2', '2010-01-01'union allselect 4, 'name3', '2010-01-02'union allselect 4, 'name4', '2010-01-01'--Result should beSelect 1, 2union allSelect NULL, 2union allSelect 1, 3union allSelect 1, 4drop table ##Temp1drop table ##Temp2goDear friends,I have a requirement n project to compare data of a table for reporting purpose. The above sample code will give you an idea about what i need. My table contains an identity column and a foreign key. I will get a foreign key as a source id and this id may have multiple records in the table. I take this source records (in above case records in ##Temp1) in a temporary table and i have to compare this source records with other records in original table (in above case records in ##Temp2). In the sample I gave, mentioned the result I am expecting.The first record in result is generated because source table have 'name4' with date and which is not available in target table for id 2.The second record in result is generated because target table have 'name5' and which is not available in source table.The third record in result is generated because source table have 'name4' and which is not available in target table for id 3.The fourth record in result is generated because source table have 'name1' with date '2010-01-02' and which is mismatched with record in source table.Could you help me to create a script for generating this output? Please give me an idea. Right now I have is a very complex script contains left join which will not give the exact result.

How to update order status based on detail information

Posted: 24 Apr 2013 02:52 PM PDT

I have 2 tables ( orderheader and ordershipmentdetail). The order can be closed when all products and their requested quantities are received.A product in a specific order can be shipped partially( for example, the customer orders 10 bulbs, first shipment sends out 6 bulbs, the second shipment 4 bulbs totally the requested quantity of 10).This is sample data where the expected result is to set status=20 on headerid=4 because all its shipments are fulfilled. The other headers will have status=10. Can you suggest how I can find the set of headerids that have all requested shipments fulfilled?Thank you!-- status 10 means open-- status 20 means closedcreate table #header( headerid int, status int) -- status 1 means Requested-- status 2 means Received create table #details (detailid int, headerid int, product int, QuantityRequested int, QuantityReceived int, status int) insert into #header values (1,null), (2,null), (3,null), (4,null) insert into #details values (10, 1, 1000, 100,50,2), (11, 1, 1000, 100,25,2), (12, 1, 1100, 8,8,2), (13, 2, 5000, 100,25,2), (14, 2, 5000, 100,75,2), (15, 3, 6000, 12,null,1), (16, 4, 7000, 12,6,2), (17, 4, 7000, 12,6,2), (18, 4, 8000, 15,5,2), (19, 4, 8000, 15,5,2), (20, 4, 8000, 15,5,2), (21, 4, 9000, 3,3,2) select * from #header select * from #details select headerid, QuantityRequested, sum(QuantityReceived) as QuantityReceived ,product, status from #details d group by headerid,QuantityRequested, product, status drop table #details drop table #header

queries on Logins

Posted: 23 Apr 2013 08:20 PM PDT

HiIf I execute the stored procedure multiple times on same day, the first time category should not repeat for the same dayHow i can achieve this by using DMV

Cannot create an account in my SQL

Posted: 24 Apr 2013 04:10 AM PDT

I have a sql server 2008R2 in domain A, I have a domain trust (full forest trust) between domain A & B, can create users of Domain A no problem, but get the error while adding domain B,Error is as,Windows NT user or group not found. Check the name again. (Microsoft Server, Error: 15401)

Implicit Conversions in fulltext indexing?

Posted: 24 Apr 2013 10:21 AM PDT

I periodically run Jonathan Kehayias' script to find implicit conversions in the plan cache for the SQL server that supports our line of business databases. I'm usually able to find these in code and get the data types lined up so that implicit conversions don't have to occur.However, there's multiple instances where an implicit conversion appears to occur when a table which has a full text index is updated. That particular table has an int-type primary key on the account number, yet the full text index is processing it as a bigint data type as far as I can tell. One of the expressions in our plan cache shows the following:Scalar Operator(CASE WHEN CASE WHEN [Expr1068] IS NOT NULL THEN (1) ELSE (4) END=(4) THEN CONVERT_IMPLICIT(bigint,[S**********].[dbo].[tblMainAcc********].[AccountKey],0) ELSE [S******].[sys].[fulltext_index_docidstatus_415508384].[docid] END)If anybody has any ideas on what might cause this, I'd very much appreciate the feedback. It's as much a matter of intellectual curiosity as anything else right now.Sincerely,Andre RanieriPS: Here's Jonathan's implicit conversion query code:/****************************************************************************** Presentation: Performance Tuning with the Plan Cache * FileName: 3.2 - Implicit Column Side Conversions.sql** Summary: Demonstrates how to find column side implicit conversions and the * statements that generated it by parsing XML Plans stored in the * plan cache.** Date: October 16, 2010 ** SQL Server Versions:* 2005, 2008, 2008 R2* ******************************************************************************* Copyright (C) 2010 Jonathan M. Kehayias* All rights reserved. ** For more scripts and sample code, check out * http://sqlblog.com/blogs/jonathan_kehayias** You may alter this code for your own *non-commercial* purposes. You may* republish altered code as long as you include this copyright and give * due credit. *** THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF * ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A* PARTICULAR PURPOSE. *******************************************************************************//*-- This demo requires that the appropriate problem be available for it to work.-- If you run this demo and it returns no results, the following code will -- create an example of the problem.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @dbname SYSNAME SET @dbname = QUOTENAME(DB_NAME()); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT top 50 stmt.value('(@StatementText)[1]', 'varchar(max)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, t.value('(@Length)[1]', 'int') AS ConvertToLength, query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

Convertir texto a fecha en sql server

Posted: 24 Apr 2013 05:59 AM PDT

Estimados, :-)quisiera realizar una consulta acerca de como convertir un formato extraño de fecha, tengo un archivo txt que viene con el campo fecha de la siguiente manera :Nombre fecha_asistPedro 41388Ana 41389Juan 41390al subirlo a la tabla de mi base de datos me percate que la fechas habian sido guardadas como texto o general en excel; a un txt delimitado por tabulaciones. Mi pregunta es como podria convertirlo en sql a fecha, de la misma manera que excel convierte a fecha para que la tabla quede asiNombre Fecha OriginalPedro 24/04/2013Ana 25/04/2013Juan 26/04/2013de antemano gracias por sus respuestas o sugerencias.:cool:

SQL Client Access Licence requirements

Posted: 14 Feb 2012 04:55 AM PST

HiSQL 2008R2 Std Ed.We are having an internal debate on what does (does not) require a SQL CAL for the end user.We only use SQL for reporting..data being sourced from external (non SQL) dbsThe majority of our users only require static non updatable reports and therefore, where we can, we would like to reduce our CAL licensing costsIf there are any licensing "experts" prepared to comment on what we believe below, then it will be appreciatedScenariosMS Excel pivot table accessing a SSAS Cube....CAL requiredMS Excel directly querying SQL via MS Query...CAL requiredMS Access ADP linked to SQL...CAL required User accessing a static Excel file created thro TSQL/SSIS .....CAL not requireduser receiving an email generated via TSQL/SSIS ....CAL not required.Any access to SSRS...CAL required.Thanks.

Virtual CPU limit for SS2008 Standard edition

Posted: 24 Apr 2013 06:44 AM PDT

If running SQL Server 2008 Standard edition on a VMWare host, am I limited to 4 virtual CPUs?

SSIS Error : SSIS Error :Option "<?xml" is not valid. The command line parameters are invalid.

Posted: 24 Apr 2013 05:45 AM PDT

any workaround for this SSIS error: Option "<?xml" is not valid. The command line parameters are invalid. The step failed.Getting error when executing from SQL Agent job.

MERGE Statement assistance

Posted: 23 Apr 2013 11:45 PM PDT

Hi all,I am working on an update routine, whereby I have 2 SQL tables that are in different Databases, but need to updated if different data exists, and added if non-exist.quite a simple task .... but am struggling.MY Script is .[size="4"]MERGE dbo.AdmFormData AS TargetUSING dbo.AdmFormData AS SourceON ( Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName )WHEN MATCHED and Target.AlphaValue <> Source.AlphaValue OR Target.NumericValue <> Source.NumericValue THEN UPDATE SET Target.AlphaValue = Source.AlphaValue, Target.NumericValue = Source.NumericValueWHEN NOT MATCHED BY TARGET THEN INSERT (padm.FormType, padm.KeyField, padm.FieldName, padm.AlphaValue) VALUES (gadm.FormType, gadm.KeyField, gadm.FieldName, gadm.AlphaValue) OUTPUT $action, inserted.*, deleted.*[b][/b][/size]I am getting this error message when parsed;Msg 102, Level 15, State 1, Line 3Incorrect syntax near '.'.

This instance of SQL Server has been using a process ID... Log Message

Posted: 24 Apr 2013 03:23 AM PDT

Is there any was to disable the informational message that SQL Server spits out to the log every day..."This instance of SQL Server has been using a process ID..."?I cannot really see any useful purpose for seeing this message every day.I've done a little digging and cannot find a way to turn this one off.Anybody know how to do this?Thanks... Scott

127rows and I get timeouts?

Posted: 24 Apr 2013 01:03 AM PDT

I have a table with 127 rows that keeps timing out.the table is used to determine the ROLE of the logon user.it has 2 fieldsid, roleif a user (99%) of all users is 'just a plain' user then no rows are returned.if a user is a manager or sr_manager we return a M or S from the role field.There is a clustered unique index on the ID.So simple right?Here is the problem, I am getting timeouts on this table!!SELECT ROLE FROM dbo.Mgr_List_Role WITH (nolock) WHERE id = 'xxx15119' I get timeout expired. And again 99% of the time, no rows are returned.I have spent a huge amount of time looking at every other query we run to insure optimization. this one has got me stumped.How is that possible?My server has 8 processors and a 16gig of RAM, Windows 2008 Enterprise server and has been running for 4 years now so I am sure it is not configuration problem.

PGP encryption/decryption fails as a sql server agent job

Posted: 24 Apr 2013 01:59 AM PDT

Hi All,I am trying to encrypt the text file before putting it on the SFTP. The package runs fine with BIDS and when i run after importing to the integrations services it executes perfectly fine. It executes even when ran from the cmd line. But the problem arises when it is run from SQL server agent. i am running as a admin and that has all the rights to the folders. what else am i missing?ThanksRK

sys.dm_os_memory_clerks Descriptions

Posted: 24 Apr 2013 01:49 AM PDT

Hi,On one of our Sql Server 2008 R2 instances I'm executing the following query:select top 20 left([name], 20) [Name] ,[type] [Type] ,sum([single_pages_kb]) [SinglePageMemoryAllocation_KB]from sys.dm_os_memory_clerksgroup by [type], left([name], 20)order by sum([single_pages_kb]) desc;I have been searching Google and other online sources without any luck trying to find descriptions for the output [Type] column from the query above. Ideally I'd like to find a list of all of the possible types so I can have a good read. Alternatively, just being able to find information on the following types would be a good starting point:USERSTORE_SCHEMAMGRMEMORYCLERK_SQLSTORENGUSERSTORE_OBJPERMUSERSTORE_DBMETADATAUSERSTORE_SCHEMAMGRMEMORYCLERK_SQLSTORENGUSERSTORE_OBJPERMOBJECTSTORE_SNI_PACKETCACHESTORE_SYSTEMROWSETMEMORYCLERK_SQLOPTIMIZERMEMORYCLERK_SQLCONNECTIONPOOLMEMORYCLERK_SQLCLRMEMORYCLERK_XEMEMORYCLERK_SQLQUERYEXECOBJECTSTORE_SERVICE_BROKERMEMORYCLERK_HOSTThanks in advance.

mirroring

Posted: 24 Apr 2013 01:48 AM PDT

we configured mirroring on the databases in our production environment ,as part of testing the application team will shutdown the production server , how can we perform this ?

Using SET IDENTITY_INSERT with trigger

Posted: 24 Apr 2013 01:04 AM PDT

Hi,I need to use SET IDENTITY_INSERT ON within a trigger query:---------------------------------------------------SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER [dbo].[tr_HEADER]ON [dbo].[HEADER]INSTEAD OF INSERT--SET IDENTITY_INSERT [HEADER] ONASDELETE FROM HEADERFROM HEADER P JOIN INSERTED I ON P.[TRADE_CD] = I.[TRADE_CD] INSERT INTO HEADERSELECT * FROM INSERTEDSET IDENTITY_INSERT [HEADER] OFF------------------------------------------------I have tried putting 'SET IDENTITY_INSERT [HEADER] ON' after 'INSTEAD OF INSERT' but it gives an error. Thanks.

Index containing Where Clause

Posted: 24 Apr 2013 01:01 AM PDT

Can an Index with a where clause be used to eliminate NULL values in the column and make the index more unique and effective.

SQL 2008R2 on Windows 2012 Std

Posted: 24 Apr 2013 01:10 AM PDT

Hi We are planing to have SQL Server 2008R2 SP1 on Windows 2012 Standard (cluster) edt for our production serverany one think this is a good idea? what are the disadvantages over this?all comments are welcome

Error while taking backup for a user not having sysadmin permission

Posted: 24 Apr 2013 12:52 AM PDT

I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login(not windows authentication) In user roles it has all permission for almost all database.For some database it has only datareader.Now when i try to take backup of database using SSMS and when try to select backup destination by clicking button i get error like"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BackupCannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."Even though I have db_owner and all permissions i am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.So why does it happen? I want this user to take backup without these error messages.(But i can't give sysadmin for the user).How can I solve it?(I already some article stating that use sql query and sqlcmd to take backup.but i want it to work using SSMS itself) [url=http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a0ada2bb-f66e-4af1-a1d7-3057bd5a7c53]Also see my detailed discussion on this problem in msdn forum by me here[/url]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog